SQL Queries Company DB example - Srinivas Narasegouda

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'
);