Scribd
Upload
13 views3 pages

SQL Database Constraints and Operations

The document outlines the creation of a database named 'constraintlab4' with two tables: 'department' and 'employee', including their respective fields and constraints. It includes SQL commands for inserting valid records into both tables, as well as examples of invalid insertions and queries to find employees in an unknown department. Additionally, it demonstrates how to delete a specific department from the database.

Uploaded by

Krishna Raj Gautam
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
13 views3 pages

SQL Database Constraints and Operations

The document outlines the creation of a database named 'constraintlab4' with two tables: 'department' and 'employee', including their respective fields and constraints. It includes SQL commands for inserting valid records into both tables, as well as examples of invalid insertions and queries to find employees in an unknown department. Additionally, it demonstrates how to delete a specific department from the database.

Uploaded by

Krishna Raj Gautam
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

LAB 4: Use of Constraint

CREATE DATABASE constraintlab4


CREATE TABLE department(
did int primary key,
dname varchar(20) NOT NULL,
location varchar(20) default 'head office'
)
CREATE TABLE employee(
eid int identity(1,1) primary key,
fname varchar(20) NOT NULL,
mname varchar(20),
lname varchar(20) NOT NULL,
Email varchar(20) unique,
salary decimal(10,2) check(salary >= 18000),
did int , foreign key(did) references department(did),
joindate date default getdate()
)

select*
from department

select *
from employee
1) Insert 10/10 valid records to each of the table
INSERT INTO department (did, dname, location) VALUES
(1, 'Human Resources', 'Kathmandu'),
(2, 'Finance', 'Pokhara'),
(3, 'IT', 'Lalitpur'),
(4, 'Marketing', 'Biratnagar'),
(5, 'Sales', 'Butwal'),
(6, 'Operations', 'Dharan'),
(7, 'Legal', 'Bhaktapur'),
(8, 'Research', 'Janakpur'),
(9, 'Customer Service', 'Nepalgunj'),
(10, 'Administration', 'head office');

INSERT INTO employee (fname, mname, lname, Email, salary, did, joindate)
VALUES
('Rajan', 'Kumar', 'Joshi', '[Link]@[Link]', 24000.00, 1, '2024-05-10'),
('Sneha', NULL, 'Tamang', '[Link]@[Link]', 22000.00, 2, '2023-10-01'),
('Bibek', 'Prasad', 'Shah', '[Link]@[Link]', 26000.00, 3, '2022-08-15'),
('Laxmi', NULL, 'Malla', '[Link]@[Link]', 20000.00, 4, '2025-02-20'),
('Kamal', 'Raj', 'Thapa', '[Link]@[Link]', 23000.00, 3, '2023-06-30'),
('Nabin', NULL, 'Rai', 'nabin.rai123@[Link]', 21000.00, 6, '2024-07-12'),
('Pooja', 'Devi', 'Shrestha', '[Link]@[Link]', 25000.00, 1, '2022-11-05'),
('Suman', NULL, 'Lama', 'suman.lama12@[Link]', 19500.00, 8, '2023-03-18'),
('Asha', 'Kumari', 'Yadav', 'asha.yadav12@[Link]', 21500.00, 9, '2024-09-25'),
('Manoj', NULL, 'Karki', '[Link]@[Link]', 27000.00, 2, '2025-01-01');
2) Insert any two employees of email same
INSERT INTO employee(fname, mname, lname, Email, salary, did)
VALUES('uncle','kumar','jha','adsss@[Link]',15000,1)

3) Insert any two employees without their join date


INSERT INTO employee(fname, mname, lname, Email, salary, did)
VALUES('abd','kumar','jha','abd@[Link]',150000,1),
('bad','kumar','jha','abd@[Link]',150000,1);

4) Find all employees of department unknown.


SELECT *
from employee e
LEFT JOIN department d
ON [Link] = [Link]
WHERE [Link] IS NULL;

5) Delete IT department
DELETE from department
WHERE dname = 'IT'

You might also like