1. Consider the Company database with the following Schema
EMPLOYEE (FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNO)
DEPARTMENT(DNAME,DNUMBER,MGRSSN,MSRSTARTDATE)
DEPT_LOCATIONS (DNUMBER, DLOCATION)PROJECT (PNAME, PNUMBER, PLOCATION, DNUM)WORKS_ON(ESSN, PNO, HOURS)
DEPENDENT(ESSN,DEPENDENT_NAME,SEX,BDATE,RELATIONSHIP)
Delete an existing Database
-- Following statement will drop (delete) the database named "Company_DB_Reg_No"
drop database Company_DB_Reg_No;
Create the database “Company_DB_Reg_No”
-- SQL QUERY FOR CREATING THE DATABASE COMPANY_DB
CREATE DATABASE Company_DB_Reg_No;
Create the table “Department”
-- Use the following database to execute the queries
USE Company_DB_Reg_No;
GO
-- SQL QUERY FOR CREATING THE TABLE Department
CREATE TABLE DEPARTMENT
(
DNUMBER INT PRIMARY KEY,
DNAME VARCHAR(30) UNIQUE,
MGRSSN CHAR(9),
MGRSTARTDATE DATE
);
INSERTING A RECORD INTO THE TABLES: Department
-- Use the following database to execute the queries
USE Company_DB_Reg_No;
GO
INSERT INTO DEPARTMENT VALUES
(1,'HR',NULL,'2020-01-01'),
(2,'Accounts',NULL,'2020-02-01'),
(3,'Research',NULL,'2020-03-01'),
(4,'Sales',NULL,'2020-04-01'),
(5,'IT',NULL,'2020-05-01');
Select * from DEPARTMENT;
Create the table “Employee”
-- Use the following database to execute the queries
USE Company_DB_Reg_No;
GO
-- SQL QUERY FOR CREATING THE TABLE Employee
CREATE TABLE EMPLOYEE
(
SSN CHAR(9) PRIMARY KEY,
FNAME VARCHAR(20),
MINIT CHAR(1),
LNAME VARCHAR(20),
BDATE DATE,
ADDRESS VARCHAR(100),
SEX CHAR(1),
SALARY DECIMAL(10,2),
SUPERSSN CHAR(9),
DNO INT,
FOREIGN KEY (DNO)
REFERENCES DEPARTMENT(DNUMBER)
);
INSERTING A RECORD INTO THE TABLES: Employee
-- Use the following database to execute the queries
USE Company_DB_Reg_No;
GO
INSERT INTO EMPLOYEE VALUES
('ssn001','Rahul','K','Sharma','1992-03-10','Bangalore','M',45000,NULL,5),
('ssn002','Priya','R','Nair','1995-06-20','Kochi','F',38000,'ssn001',2),
('ssn003','Amit','S','Patel','1988-09-15','Ahmedabad','M',55000,'ssn001',3),
('ssn004','Sneha','P','Rao','1993-11-18','Mysore','F',42000,'ssn003',3),
('ssn005','Arjun','M','Reddy','1991-01-25','Hyderabad','M',60000,'ssn001',5),
('ssn006','Kiran','V','Kumar','1987-08-11','Chennai','M',39000,'ssn005',2),
('ssn007','Pooja','A','Joshi','1996-05-17','Pune','F',47000,'ssn003',3),
('ssn008','Deepak','N','Singh','1989-10-08','Delhi','M',52000,'ssn005',5),
('ssn009','Neha','T','Gupta','1994-07-12','Lucknow','F',36000,'ssn004',4),
('ssn010','Rohan','J','Das','1998-12-30','Kolkata','M',43000,'ssn008',5);
select * from EMPLOYEE;
Create the table “Dept_Locations”
-- Use the following database to execute the queries
USE Company_DB_Reg_No;
GO
-- SQL QUERY FOR CREATING THE TABLE DEPT_LOCATIONS
CREATE TABLE DEPT_LOCATIONS
(
DNUMBER INT,
DLOCATION VARCHAR(30),
PRIMARY KEY(DNUMBER,DLOCATION),
FOREIGN KEY(DNUMBER)
REFERENCES DEPARTMENT(DNUMBER)
);
INSERTING A RECORD INTO THE TABLES: DEPT_LOCATIONS
-- Use the following database to execute the queries
USE Company_DB_Reg_No;
GO
INSERT INTO DEPT_LOCATIONS VALUES
(1,'Bangalore'),
(2,'Mumbai'),
(3,'Delhi'),
(4,'Hyderabad'),
(5,'Pune');
Select * from DEPT_LOCATIONS;
Creating the table ” Project”
-- Use the following database to execute the queries
USE Company_DB_Reg_No;
GO
-- SQL QUERY FOR CREATING THE TABLE Project
CREATE TABLE PROJECT
(
PNUMBER INT PRIMARY KEY,
PNAME VARCHAR(30),
PLOCATION VARCHAR(30),
DNUM INT,
FOREIGN KEY(DNUM)
REFERENCES DEPARTMENT(DNUMBER)
);
INSERTING A RECORD INTO THE TABLES: PROJECT
-- Use the following database to execute the queries
USE Company_DB_Reg_No;
GO
INSERT INTO PROJECT VALUES
(101,'Payroll','Mumbai',2),
(102,'AI Research','Delhi',3),
(103,'Website','Pune',5),
(104,'Sales App','Hyderabad',4),
(105,'Recruitment','Bangalore',1);
Select * from PROJECT;
Creating the table ” Works_On”
-- Use the following database to execute the queries
USE Company_DB_Reg_No;
GO
-- SQL QUERY FOR CREATING THE TABLE Works_On
CREATE TABLE WORKS_ON
(
ESSN CHAR(9),
PNO INT,
HOURS DECIMAL(5,2),
PRIMARY KEY(ESSN,PNO),
FOREIGN KEY(ESSN)
REFERENCES EMPLOYEE(SSN),
FOREIGN KEY(PNO)
REFERENCES PROJECT(PNUMBER)
);
INSERTING A RECORD INTO THE TABLES: Works_On
-- Use the following database to execute the queries
USE Company_DB_Reg_No;
GO
INSERT INTO WORKS_ON VALUES
('ssn001',103,20),
('ssn003',102,35),
('ssn004',102,30),
('ssn007',102,25),
('ssn005',103,40),
('ssn008',103,25),
('ssn010',103,30),
('ssn002',101,40),
('ssn006',101,30),
('ssn009',104,40);
Select * from WORKS_ON;
Creating the table “Dependent”
-- Use the following database to execute the queries
USE Company_DB_Reg_No;
GO
-- SQL QUERY FOR CREATING THE TABLE Dependent
CREATE TABLE DEPENDENT
(
ESSN CHAR(9),
DEPENDENT_NAME VARCHAR(30),
SEX CHAR(1),
BDATE DATE,
RELATIONSHIP VARCHAR(20),
PRIMARY KEY(ESSN,DEPENDENT_NAME),
FOREIGN KEY(ESSN)
REFERENCES EMPLOYEE(SSN)
);
INSERTING A RECORD INTO THE TABLES: Dependent
-- Use the following database to execute the queries
USE Company_DB_Reg_No;
GO
INSERT INTO DEPENDENT VALUES
('ssn001','Ananya','F','2018-01-01','Daughter'),
('ssn002','Rajesh','M','1965-04-10','Father'),
('ssn003','Riya','F','2017-08-15','Daughter'),
('ssn005','Lakshmi','F','1994-11-12','Wife'),
('ssn008','Aarav','M','2020-07-01','Son');
Select * from DEPENDENT;
2. Viewing all databases, Viewing all tables in a database, Inserting/Updating/Deleting records in a table, Saving (Commit) and Undoing (rollback):
USE Company_DB_Reg_No;
GO
SELECT name FROM sys.databases;
SELECT * FROM INFORMATION_SCHEMA.TABLES;
-- Inserting values
USE Company_DB_Reg_No;
GO
INSERT INTO EMPLOYEE VALUES
('ssn011','Manoj','K','Verma',
'1997-09-09',
'Jaipur',
'M',
41000,
'ssn001',
5);
select * from employee
-- Update a record in a table
USE Company_DB_Reg_No;
GO
UPDATE EMPLOYEE
SET SALARY=50000
WHERE SSN='ssn002';
select * from employee
-- Delete a record from a table
USE Company_DB_Reg_No;
GO
DELETE
FROM EMPLOYEE
WHERE SSN='ssn011';
select * from employee
-- Commit statement demo
USE Company_DB_Reg_No;
GO
BEGIN TRANSACTION;
UPDATE EMPLOYEE
SET SALARY=SALARY+20000
WHERE DNO=5;
COMMIT;
Select * from EMPLOYEE;
-- Rollback statement demo
USE Company_DB_Reg_No;
GO
BEGIN TRANSACTION;
UPDATE EMPLOYEE
SET SALARY=SALARY+500000
WHERE DNO=5;
ROLLBACK;
select * from employee;
3. Altering a table, Dropping/Truncating/Renaming tables,backing up, Restoring a database:
-- ALTER table Example: varchar (20) to varchar(30)
USE Company_DB_Reg_No;
GO
ALTER TABLE dependent
ALTER COLUMN relationship VARCHAR(30);
-- ALTER table Example: Add a new column
ALTER TABLE dependent
ADD EMAIL VARCHAR(50);
-- ALTER table Example: Change table name
EXEC sp_rename 'DEPENDENT','DEPENDENTS';
select * from Dependents;
Backing up database:
BACKUP DATABASE Company_DB_Reg_No
TO DISK='C:\Backup_DB\CompanyDB.bak';
Truncate:
USE Company_DB_Reg_No;
GO
select * from DEPENDENTS;
TRUNCATE TABLE DEPENDENTS;
select * from DEPENDENTS;
Drop a table, database
USE Company_DB_Reg_No;
GO
drop table DEPENDENTS;
use master;
go
drop database Company_DB_Reg_No;
SELECT name FROM sys.databases;
Restore the database:
USE master;
GO
RESTORE DATABASE Company_DB_Reg_No
FROM DISK='C:\Backup_DB\CompanyDB.bak'
WITH REPLACE;
SELECT name FROM sys.databases;
4. Perform Simple Queries, Simple Queries with Aggregate functions, Queries with Aggregate functions (group by and having clause).
Simple Queries :
USE Company_DB_Reg_No;
GO
-- Display all details of the Employee table
SELECT * FROM EMPLOYEE;
-- Display employee names and salary
SELECT FNAME, LNAME, SALARY FROM EMPLOYEE;
-- Display employees working in Department x
SELECT * FROM EMPLOYEE WHERE DNO = 3;
-- Display projects located in a specific city
SELECT * FROM PROJECT WHERE PLOCATION = 'Bangalore';
-- Display employees earning more than 50000
SELECT FNAME, LNAME, SALARY FROM EMPLOYEE
WHERE SALARY > 50000;
-- Display department names
SELECT DNAME FROM DEPARTMENT;
-- Display employee names and department numbers
SELECT FNAME, LNAME, DNO FROM EMPLOYEE;
Simple Queries with Aggregate Functions
USE Company_DB_Reg_No;
GO
-- Count the total EMPLOYEE working
SELECT COUNT(*) AS Total_Employees FROM EMPLOYEE;
-- Find maximum salary
SELECT MAX(SALARY) AS Highest_Salary FROM EMPLOYEE;
-- Find minimum salary
SELECT MIN(SALARY) AS Lowest_Salary FROM EMPLOYEE;
--Find average salary
SELECT AVG(SALARY) AS Average_Salary FROM EMPLOYEE;
--Find total salary paid
SELECT SUM(SALARY) AS Total_Salary FROM EMPLOYEE;
-- Count total projects
SELECT COUNT(*) AS Total_Projects FROM PROJECT;
Aggregate Functions with GROUP BY :
USE Company_DB_Reg_No;
GO
--Count employees in each department
SELECT DNO,
COUNT(*) AS Employee_Count
FROM EMPLOYEE
GROUP BY DNO;
--Find average salary of each department
SELECT DNO,
AVG(SALARY) AS Average_Salary
FROM EMPLOYEE
GROUP BY DNO;
--Find total salary of each department
SELECT DNO,
SUM(SALARY) AS Total_Salary
FROM EMPLOYEE
GROUP BY DNO;
--Count projects handled by each department
SELECT DNUM,
COUNT(*) AS Total_Projects
FROM PROJECT
GROUP BY DNUM;
--Find total hours worked on each project
SELECT PNO,
SUM(HOURS) AS Total_Hours
FROM WORKS_ON
GROUP BY PNO;
Aggregate Functions with GROUP BY and HAVING Clause :
USE Company_DB_Reg_No;
GO
--Display departments having more than 2 employees
SELECT DNO,
COUNT(*) AS Employee_Count
FROM EMPLOYEE
GROUP BY DNO
HAVING COUNT(*) > 2;
--Display departments whose average salary is greater than 60000
SELECT DNO,
AVG(SALARY) AS Average_Salary
FROM EMPLOYEE
GROUP BY DNO
HAVING AVG(SALARY) > 60000;
--Display projects where total working hours exceed 40
SELECT PNO,
SUM(HOURS) AS Total_Hours
FROM WORKS_ON
GROUP BY PNO
HAVING SUM(HOURS) > 40;
--Display departments whose total salary exceeds 200000
SELECT DNO,
SUM(SALARY) AS Total_Salary
FROM EMPLOYEE
GROUP BY DNO
HAVING SUM(SALARY) > 200000;
5. Execute the following queries
(a.) How the resulting salaries if every employee working on the ‘Research’ Department is given a 10% raise?
(b.) Find the sum of the salaries of all employees of the’Accounts’ department, as well as the maximum salary, the minimum salary, and the average salary in this department.
-- The resulting salaries if every employee working on the 'Research' Department is given a 10% raise
USE Company_DB_Reg_No;
GO
UPDATE EMPLOYEE
SET SALARY = SALARY * 1.10
WHERE DNO IN
(
SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME = 'Research'
);
Select * from Employee;
-- sum of the salaries of all employees of the'Accounts' department, as well as the maximum salary, the minimum salary, and the average salary in this department.
USE Company_DB_Reg_No;
GO
SELECT
SUM(SALARY) AS Total_Salary,
MAX(SALARY) AS Maximum_Salary,
MIN(SALARY) AS Minimum_Salary,
AVG(SALARY) AS Average_Salary
FROM EMPLOYEE
WHERE DNO =
(
SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME = 'Accounts'
);
6. Execute the following queries
(a.) Retrieve the name of each employee controlled by Department number 5 (use EXISTS operator).
SELECT E.FNAME, E.LNAME
FROM EMPLOYEE E
WHERE EXISTS
(
SELECT 1
FROM DEPARTMENT D
WHERE D.DNUMBER = 5
AND E.DNO = D.DNUMBER
);
(b.) Retrieve the name of each dept and number of employees working in each Department which has at least 2 employees.
SELECT D.DNAME, COUNT(E.SSN) AS EMPLOYEE_COUNT
FROM DEPARTMENT D
JOIN EMPLOYEE E
ON D.DNUMBER = E.DNO
GROUP BY D.DNUMBER, D.DNAME
HAVING COUNT(E.SSN) >= 2;
7. Execute the following queries
(a.) For each project, retrieve the project number, the project name, and the number of employees who work on that project. (use GROUP BY)
SELECT P.PNUMBER,
P.PNAME,
COUNT(W.ESSN) AS EMPLOYEE_COUNT
FROM PROJECT P
LEFT JOIN WORKS_ON W
ON P.PNUMBER = W.PNO
GROUP BY P.PNUMBER, P.PNAME;
(b.) Retrieve the name of employees who born in the year 1990’s.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE BDATE >= '1990-01-01'
AND BDATE < '2000-01-01';
8. For each Department that has more than x number of employees, retrieve the department number and number of employees who are making salary more than 40000.
SELECT D.DNUMBER,
COUNT(CASE WHEN E.SALARY > 40000 THEN 1 END)
AS HIGH_SALARY_EMPLOYEES
FROM DEPARTMENT D
JOIN EMPLOYEE E
ON D.DNUMBER = E.DNO
GROUP BY D.DNUMBER
HAVING COUNT(E.SSN) > 2;
9. For each project on which more than two employees work, retrieve the project number, project name and the number of employees who work on that project.
SELECT P.PNUMBER,
P.PNAME,
COUNT(W.ESSN) AS EMPLOYEE_COUNT
FROM PROJECT P
JOIN WORKS_ON W
ON P.PNUMBER = W.PNO
GROUP BY P.PNUMBER, P.PNAME
HAVING COUNT(W.ESSN) > 2;
10. For a given set of relation tables, perform the following:
Creating Views (with and without check option), Selecting from a view, Dropping views.
(a.) Create a view without WITH CHECK OPTION. (For example, create a view containing employees from department 5)
CREATE VIEW DEPT5_EMPLOYEES AS
SELECT SSN, FNAME, LNAME, SALARY, DNO
FROM EMPLOYEE
WHERE DNO = 5;
Select from the view
SELECT *
FROM DEPT5_EMPLOYEES;
SELECT FNAME, LNAME, SALARY
FROM DEPT5_EMPLOYEES;
(b.) Create a view with CHECK OPTION
CREATE VIEW DEPT5_EMPLOYEES_CHECK AS
SELECT SSN, FNAME, LNAME, SALARY, DNO
FROM EMPLOYEE
WHERE DNO = 5
WITH CHECK OPTION;
The WITH CHECK OPTION prevents updates/inserts through the view that would result in rows not satisfying DNO = 5.
The following update will be rejected because the modified row would no longer satisfy the view condition DNO = 5.
UPDATE DEPT5_EMPLOYEES_CHECK
SET DNO = 3
WHERE SSN = '123456789';
(c.) DROP a view
DROP VIEW DEPT5_EMPLOYEES;
DROP VIEW DEPT5_EMPLOYEES_CHECK;
.