Delhi Technological University
Department of Computer Science and Engineering
Database Management System
Practical File
DBMS LAB
Submitted To – Minal Tandekar
Submitted By - Roshan Kumar
Roll No - 2K21/EE/240
INDEX
S.No Experiment Page No. Date Sign
1. Implement DDL & DML commands in
SQL.
2. Draw ER Diagram
3. Implement Aggregate Functions used in
SQL.
4. Implement Joins in SQL.
Experiment - 1
Aim: Implement DDL & DML commands in SQL on given database
Code:
CREATE DATABSE & CREATE TABLE:
create database organization;
use organization;
CREATE TABLE EMPLOYEE (
Fname VARCHAR(15) NOT NULL,
Minit CHAR(1),
Lname VARCHAR(15) NOT NULL,
Ssn CHAR(9) PRIMARY KEY,
Bdate DATE,
Address VARCHAR(50),
Sex CHAR(1),
Salary INT,
Super_ssn CHAR(9),
Dno INT
);
desc employee;
Inserting data into the EMPLOYEE table:
INSERT INTO EMPLOYEE (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
VALUES
('John', 'B', 'Smith', '123456789', '1965-01-09', '731 Fondren, Houston, TX', 'M', 30000, '333445555', 5),
('Franklin', 'T', 'Wong', '333445555', '1955-12-08', '638 Voss, Houston, TX', 'M', 40000, '888665555', 5),
('Alicia', 'J', 'Zelaya', '999887777', '1968-01-19', '3321 Castle, Spring, TX', 'F', 25000, '987654321', 4),
('Jennifer', 'S', 'Wallace', '987654321', '1941-06-20', '291 Berry, Bellaire, TX', 'F', 43000, '888665555', 4),
('Ramesh', 'K', 'Narayan', '666884444', '1962-09-15', '975 Fire Oak, Humble, TX', 'M', 38000,
'333445555', 5),
('Joyce', 'A', 'English', '453453453', '1972-07-31', '5631 Rice, Houston, TX', 'F', 25000, '333445555', 5),
('Ahmad', 'V', 'Jabbar', '987987987', '1969-03-29', '980 Dallas, Houston, TX', 'M', 25000, '987654321', 4),
('James', 'E', 'Borg', '888665555', '1937-11-10', '450 Stone, Houston, TX', 'M', 55000, NULL, 1);
select * from employee;
Creating the DEPARTMENT TABLE:
CREATE TABLE DEPARTMENT (
Dname VARCHAR(20) ,
Dnumber INT PRIMARY KEY,
Mgr_ssn CHAR(9),
Mgr_start_date DATE,
FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn)
);
Inserting the values into department table
INSERT INTO DEPARTMENT VALUES ('Research', 5, '333445555', '1988-05-22');
INSERT INTO DEPARTMENT VALUES ('Administration', 4, '987654321', '1995-01-01');
INSERT INTO DEPARTMENT VALUES ('Headquarters', 1, '888665555', '1981-06-19');
Making the department location table
CREATE TABLE DEPT_LOCATIONS (
Dnumber INT NOT NULL,
Dlocation VARCHAR(20),
PRIMARY KEY (Dnumber, Dlocation),
FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber)
);
Inserting data into the DEPT_LOCATIONS table
INSERT INTO DEPT_LOCATIONS (Dnumber, Dlocation) VALUES
(1, 'Houston'),
(4, 'Stafford'),
(5, 'Bellaire'),
(5, 'Sugarland'),
(5, 'Houston');
Creating the PROJECT table
CREATE TABLE PROJECT (
Pname VARCHAR(20) NOT NULL,
Pnumber INT PRIMARY KEY,
Plocation VARCHAR(20),
Dnum INT,
FOREIGN KEY (Dnum) REFERENCES DEPARTMENT(Dnumber)
);
Inserting data into the PROJECT table
INSERT INTO PROJECT (Pname, Pnumber, Plocation, Dnum) VALUES
('ProductX', 1, 'Bellaire', 5),
('ProductY', 2, 'Sugarland', 5),
('ProductZ', 3, 'Houston', 5),
('Computerization', 10, 'Stafford', 4),
('Reorganization', 20, 'Houston', 1),
('Newbenefits', 30, 'Stafford', 4);
creating the WORKS_ON table
CREATE TABLE WORKS_ON (
Essn CHAR(9),
Pno INT,
Hours DECIMAL(5,2) NOT NULL,
PRIMARY KEY (Essn, Pno),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn),
FOREIGN KEY (Pno) REFERENCES PROJECT(Pnumber)
);
ALTER TABLE WORKS_ON MODIFY Hours DECIMAL(5,2);
Inserting data into the WORKS_ON table
INSERT INTO WORKS_ON (Essn, Pno, Hours) VALUES
('123456789', 1, 32.5),
('123456789', 2, 7.5),
('666884444', 3, 40.0),
('453453453', 1, 20.0),
('453453453', 2, 20.0),
('333445555', 2, 10.0),
('333445555', 3, 10.0),
('333445555', 10, 10.0),
('333445555', 20, 10.0),
('999887777', 30, 30.0),
('999887777', 10, 10.0),
('987987987', 10, 35.0),
('987987987', 30, 5.0),
('987654321', 30, 20.0),
('987654321', 20, 15.0),
('888665555', 20, NULL);
creating the Dependent table
CREATE TABLE DEPENDENT (
Essn CHAR(9),
Dependent_name VARCHAR(20) NOT NULL,
Sex CHAR(1),
Bdate DATE,
Relationship VARCHAR(15) NOT NULL,
PRIMARY KEY (Essn, Dependent_name),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn)
);
Inserting data into the DEPENDENT table
INSERT INTO DEPENDENT (Essn, Dependent_name, Sex, Bdate, Relationship) VALUES
('333445555', 'Alice', 'F', '1986-04-05', 'Daughter'),
('333445555', 'Theodore', 'M', '1983-10-25', 'Son'),
('333445555', 'Joy', 'F', '1958-05-03', 'Spouse'),
('987654321', 'Abner', 'M', '1942-02-28', 'Spouse'),
('123456789', 'Michael', 'M', '1988-01-04', 'Son'),
('123456789', 'Alice', 'F', '1988-12-30', 'Daughter'),
('123456789', 'Elizabeth', 'F', '1967-05-05', 'Spouse');
Code Output:
Experiment - 2
Aim: Draw the ER Diagram.
Output:
ER Diagram For Organisation Database :
Experiment - 3
Aim: Implement Aggregate Functions used in SQL
Queries:
1. Total number of employees
2. Average salary of all employees
3. Maximum hours worked on any project
4. Total hours worked across all projects
5. Number of employees in each department
6. Average salary by department
7. Total hours per project
8. Number of projects in each location
9. To determine the number of dependents each employee
10.To count the number of projects in each location
Code:
1. SELECT COUNT(*) AS TotalEmployees FROM EMPLOYEE;
2. SELECT AVG(Salary) AS AverageSalary FROM EMPLOYEE;
3. SELECT MAX(Hours) AS MaximumHoursWorked FROM WORKS_ON;
4. SELECT SUM(Hours) AS TotalHoursWorked FROM WORKS_ON;
5. SELECT D.Dname, COUNT(E.Ssn) AS NumEmployees
FROM DEPARTMENT D
JOIN EMPLOYEE E ON D.Dnumber = E.Dno
GROUP BY D.Dname;
6. SELECT D.Dname, AVG(E.Salary) AS AvgSalary
FROM DEPARTMENT D
JOIN EMPLOYEE E ON D.Dnumber = E.Dno
GROUP BY D.Dname;
7. SELECT P.Pname, SUM(W.Hours) AS TotalHours
FROM PROJECT P
JOIN WORKS_ON W ON P.Pnumber = W.Pno
GROUP BY P.Pname;
8. SELECT P.Plocation, COUNT(*) AS NumProjects
FROM PROJECT P
GROUP BY P.Plocation;
9. SELECT E.Fname, E.Lname, COUNT(D.Dependent_name) AS NumDependents
FROM EMPLOYEE E
LEFT JOIN DEPENDENT D ON E.Ssn = D.Essn
GROUP BY E.Ssn;
10. SELECT P.Plocation, COUNT(*) AS NumProjects
FROM PROJECT P
GROUP BY P.Plocation;
Experiment - 4
Aim: Implement Joins in SQL.
Queries:
1. List all employees along with their department names, including those without a
department.
2. List all possible combinations of employees and departments.
3. List all employees and all departments
4. List all departments along with employee names, including departments without any
employees
5. List employees and their supervisors
6. List departments along with the count of employees in each
7. List employees, their projects, and the hours worked on each project
8. List project names along with employee names who work on them
9. List of departments with the number of employees and the average salary where
average salary is above a certain threshold (e.g., 35000)
10.Number of dependents per employee with their total salary
11.Average hours worked on projects by department
12.Total salaries paid by each department
Code & Output:
1. SELECT E.Fname, D.Dname
FROM EMPLOYEE AS E
LEFT JOIN DEPARTMENT D ON E.Dno = D.Dnumber;
2. SELECT E.*, D.*
FROM EMPLOYEE E
CROSS JOIN DEPARTMENT D;
3. SELECT E.Fname, D.Dname
FROM EMPLOYEE E
NATURAL JOIN DEPARTMENT D;
4. SELECT D.*, E.Fname, E.Lname
FROM DEPARTMENT D
LEFT JOIN EMPLOYEE E ON D.Dnumber = E.Dno;
5. SELECT E.Fname, E.Lname, S.Fname AS Supervisor_Fname, S.Lname AS
Supervisor_Lname
FROM EMPLOYEE E
LEFT JOIN EMPLOYEE S ON E.Super_ssn = S.Ssn;
6. SELECT D.Dname, COUNT(E.Ssn) AS NumEmployees
FROM DEPARTMENT D
LEFT JOIN EMPLOYEE E ON D.Dnumber = E.Dno
GROUP BY D.Dname;
7. SELECT E.Fname, E.Lname, P.Pname, WO.Hours
FROM EMPLOYEE E
JOIN WORKS_ON WO ON E.Ssn = WO.Essn
JOIN PROJECT P ON WO.Pno = P.Pnumber;
8. SELECT P.Pname, E.Fname, E.Lname
FROM PROJECT P
JOIN WORKS_ON WO ON P.Pnumber = WO.Pno
JOIN EMPLOYEE E ON WO.Essn = E.Ssn;
9. SELECT D.Dname, COUNT(E.Ssn) AS NumEmployees, AVG(E.Salary) AS
AvgSalary
FROM DEPARTMENT D
LEFT JOIN EMPLOYEE E ON D.Dnumber = E.Dno
GROUP BY D.Dname
HAVING AVG(E.Salary) > 35000;
10. SELECT E.Fname, E.Lname, COUNT(D.Dependent_name) AS
NumDependents, SUM(E.Salary) AS TotalSalary
FROM EMPLOYEE E
LEFT JOIN DEPENDENT D ON E.Ssn = D.Essn
GROUP BY E.Ssn;
11. SELECT D.Dname, AVG(WO.Hours) AS AvgHoursWorked
FROM DEPARTMENT D
JOIN EMPLOYEE E ON D.Dnumber = E.Dno
JOIN WORKS_ON WO ON E.Ssn = WO.Essn
GROUP BY D.Dname;
12. SELECT D.Dname, SUM(E.Salary) AS TotalSalaries
FROM DEPARTMENT D
LEFT JOIN EMPLOYEE E ON D.Dnumber = E.Dno
GROUP BY D.Dname;
