We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
You are on page 1/ 7
18CSL58-DBMS Laboratory with Mini Project
5.Consider the schema for Company Database:
EMPLOYEE (SSN, Name, Address, Sex, Salary, SuperSSN, DNo)
DEPARTMENT (DNo, DName, MgrSSN, MgrStartDate)
DLOCATION (DNo,DLoc)
PROJECT (PNo, PName, PLocation, DNo)
WORKS_ON (SSN, PNo, Hours)
Write SQL queries to
11. Make a list of all project numbers for projects that involve an employee whose last
name is ‘Scott’, either as a worker or as a manager of the department that controls
the project.
12, Show the resulting salaries if every employee working on the ‘IoT” project is given a
10 percent raise.
13. 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
14, Retrieve the name of each employee who works on all the projects controlled by
department number 5 (use NOT EXISTS operator). For each department that has
more than five employees, retrieve the department number and the number of its
employees who are making more than Rs. 6,00,000.
=/\ ~~ eS
a we >
SVCE , Bengaluru Dept. of ISE18CSL58-DBMS Laboratory with Mini Project
Schema Diagram
Employee
Tname
‘SSN ]Fname
“Address | Sex | Salary | SuperSSN | DNO
TT
Depar ment
a
DNO| Dname | MgrSSN | MgrStartDate
e_
DLocation
‘BNO | BLOC
LJ
Project
|»[ BNO] PName | PLocation [ DNO
Work_on |
PNO | Hours
Table Creation
CREATE TABLE DEPARTMENT
(DNO VARCHAR? (20) PRIMARY KEY,
DNAME VARCHAR? (20),
MGRSTARTDATE DATE);
CREATE TABLE EMPLOYEE
(SSN VARCHAR? (20) PRIMARY KEY,
FNAME VARCHAR? (20),
LNAME VARCHAR? (20),
ADDRESS VARCHAR? (20),
SEX CHAR (1),
SALARY INTEGER,
SUPERSSN REFERENCES EMPLOYEE (SSN),
DNO REFERENCES DEPARTMENT (DNO));
SVCE , Bengaluru
Dept. of ISE18CSL58-DBMS Laboratory with Mini Project
NOTE: Once DEPARTMENT and EMPLOYEE tables are created we must alter department
table to add foreign constraint MGRSSN using sql command
ALTER TABLE DEPARTMENT
ADD MGRSSN REFERENCES EMPLOYEE (SSN);
CREATE TABLE DLOCATION
(DLOC VARCHAR? (20),
DNO REFERENCES DEPARTMENT (DNO),
PRIMARY KEY (DNO, DLOC));
CREATE TABLE PROJECT
(PNO INTEGER PRIMARY KEY,
PNAME VARCHAR? (20),
PLOCATION VARCHAR? (20),
DNO REFERENCES DEPARTMENT (DNO));
CREATE TABLE WORKS_0
(HOURS NUMBER (2),
SSN REFERENCES EMPLOYEE (SSN),
PNO REFERENCES PROJECT(PNO),
PRIMARY KEY (SSN, PNO));
Table Descriptions
DESC EMPLOYEE;
DESC DEPARTMENT;
DESC DLOCATION;
DESC PROJECT;
DESC WORKS_ON;
Insertion of values to tabl
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(SVCEECE01‘,"JOHN’,‘SCOTT*,;BANGALORE*,“M*, 450000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(SVCECSE01,\JAMES‘,‘SMITH*,"BANGALORE*,"M‘, 500000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(SVCECSE02",‘HEARN*,‘BAKER‘,‘BANGALORE:,"M‘, 700000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(SVCECSE03*,EDWARD',‘SCOTT:,‘MYSORE',*M‘, 500000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRES
'S, SEX, SALARY) VALUES
SVCE , Bengaluru Dept. of ISE18CSL58-DBMS Laboratory with Mini Project
(SVCECSE04*,‘PAVAN‘,‘HEGDE',MANGALORE*,'M*, 650000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(SVCECSE0S*,‘GIRISH*,"MALYA‘,‘MYSORE*,"M*, 450000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADD}
(SVCECSE06*,‘NEHA‘,‘SN‘,"BANGALORE: ‘F*, 800000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRES
(SVCEACCOI',AHANA‘,*K*,*MANGALORE’,‘F*, 350000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(SVCECC02*,‘SANTHOSH*,‘KUMAR*,‘MANGALORE*,‘M*, 300000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(SVCEISEOI*,*VEENA‘,M‘,‘MYSORE*,"M‘, 600000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(SVCEITOI‘,‘NAGESH‘,“HR*,‘BANGALORE*,*M‘, 500000);
S, SEX, SALARY) VALUES
'S, SEX, SALARY) VALUES
INSERT INTO DEPARTMENT VALUES (_1*,,AC
INSERT INTO DEPARTMENT VALUES (2°,
INSERT INTO DEPARTMENT VALUES (_3°,"E
INSERT INTO DEPARTMENT VALUES (_4*,‘IS!
INSERT INTO DEPARTMENT VALUES (_5*,"CSE*,
‘OUNTS*,“01-JAN-01',‘SVCECC02');
‘01-AUG-16',‘SVCEITOI);
.“01-JUN-08:,SVCEECE01');
11-AUG-15*,‘SVCEISEO1*);
‘01-JUN-02',‘SVCECSE05');
Note: update entries of employee table to fill missing fields SUPERSSN and DNO
UPDATE EMPLOYEE SI
SUPERSSN=NULL, DNO='
WHERE SSN-‘SVCI
Ms
UPDATE EMPLOYEE SET
SUPERSSN="SVCECSE02",
DNO="5‘WHERE
SSN='SVCECSEO;
UPDATE EMPLOYEE SET
SUPERSSN="SVCECSE03*,
DNO="5‘WHERE,
SSN="SVCECSE02*;
UPDATE EMPLOYEE SET
SUPERSSN="SVCECSE04*,
DNO="5‘WHERE,
SVCE , Bengaluru Dept. of ISE18CSL58-DBMS Laboratory with Mini Project
SSN="SVCECSE03*;
UPDATE EMPLOYEE SET
DNO="5‘,
SUPERSSN="SVCECSE0S* WHE]
SSN='SVCECSE04*;
UPDATE EMPLOYEE SET
DNO="5*,
SUPERSSN="SVCECSE06* WHERE
SSN='SVCECSE0S*;
UPDATE EMPLOYEE SET
DNO="5*, SUPERSSN=-NULL
WHERE SSN="SVCECSE06";
UPDATE EMPLOYEE,
DNO="I',
SUPERSSN="SVCEACC02* WHERE
SSN="SVCEACCOL';
ET.
UPDATE EMPLOYEE SET
DNO='I‘, SUPERSSN=NULL
WHERE SSN="SVCEACC02°;
INSERT INTO DLOCATION VALUES (‘BANGALORE'
INSERT INTO DLOCATION VALUES (‘BANGALORE'
INSERT INTO DLOCATION VALUES (‘BANGALORE'
a)
ah
3h
INSERT INTO DLOCATION VALUES (‘MANGALORE*, 4‘);
INSERT INTO DLOCATION VALUES (‘MANGALORE:, _5*);
INSERT INTO PROJECT VALUES (100,"IOT*,*BANGALORE’,'S*);
INSERT INTO PROJECT VALUES (101,"CLOUD*,‘BANGALORE*,‘5');
INSERT INTO PROJECT VALUES (10:
BIGDATA‘,‘BANGALORE*,‘5*);
SVCE , Bengaluru
Dept. of ISE18CSL58-DBMS Laboratory with Mini Project
INSERT INTO PROJECT VALUES (103,‘SENSORS‘,*BANGALORE‘,‘3°);
INSERT INTO PROJECT VALUES (104,"BANK MANAGEMENT*,‘ BANGALORE‘, ‘1*);
INSERT INTO PROJECT VALUES (105,‘SALARY MANAGEMENT*,‘BANGALORE;, I‘);
INSERT INTO PROJECT VALUES (106,‘OPENSTACK‘,‘BANGALORE*
INSERT INTO PROJECT VALUES (107,‘SMART CITY*,“-BANGALORI
INSERT INTO WORKS_ON VALUES (4, SVCECSEO1‘, 100);
INSERT INTO WORKS_ON VALUES (6, SVCECSEOI*, 101);
INSERT INTO WORKS_ON VALUES (8, SVCECSE0I‘, 102);
INSERT INTO WORKS_ON VALUES (10, SVCECSE02", 100);
INSERT INTO WORKS_ON VALUES (3, SVCECSE04*, 100);
INSERT INTO WORKS_ON VALUES (4, SVCECSE05‘, 101);
INSERT INTO WORKS_ON VALUES (5, SVCECSE06*, 102);
INSERT INTO WORKS_ON VALUES (6, SVCECSE03*, 102);
INSERT INTO WORKS_ON VALUES (7, SVCEECE01*, 103);
INSERT INTO WORKS_ON VALUES (5, SVCEACCO1*, 104);
INSERT INTO WORKS_ON VALUES (6, SVCEACCO2*, 105);
INSERT INTO WORKS_ON VALUES (4, SVCEISE01*, 106);
INSERT INTO WORKS_ON VALUES (10, SVCEITO1*, 107);
SELECT * FROM EMPLOYEE;
SELECT * FROM DEPARTMENT;
SELECT * FROM DLOCATION;
SELECT * FROM PROJEC"
SELECT * FROM WORKS_ON;
1. Make a list of all project numbers for projects that involve an employee whose last
name is ‘Scott’, either as a worker or as a manager of the department that controls the
Project.
(SELECT DISTINCT P.PNO
FROM PROJECT P, DEPARTMENT D, EMPLOYEE E
WHERE E.DNO-D.DNO
AND D.MGRSSN-E.SSN
AND E.LNAME=‘SCOTT")
UNION
(SELECT DISTINCT P1.PNO
FROM PROJECT PI, WORKS_ON W,
WHERE P1.PNO=W.PNO
AND ELSSN=W.SSN
AND EI.LNAME=‘SCOTT');
MPLOYEE El
SVCE , Bengaluru Dept. of ISE18CSL58-DBMS Laboratory with Mini Project
2. Show the resulting salaries if every employee working on the ‘IoT” project is given a 10
percent raise.
SELECT E.FNAME, E.LNAME, 1.1*E.SALARY AS INCR_SAL
FROM EMPLOYEE E, WORKS_ON W, PROJECT P
WHERE E.SSN-W.SSN
AND W.PNO=P.PNO-
AND P.PNAME=IOT‘;
3. 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
SELECT SUM (E.SALARY), MAX (E.SALARY), MIN (E.SALARY), AVG
(ESALARY)
FROM EMPLOYEE E, DEPARTMENT D.
WHERE E.DNO-D.DNO
AND D.DNAME="ACCOUNTS*;
4. Retrieve the name of each employee who works on all the projects Controlled by
department number 5 (use NOT EXISTS operator).
SELECT E.FNAME, E.LNAME
FROM EMPLOYEE E
WHERE NOT EXISTS((SELECT PNO
FROM PROJECT
WHERE DNO-‘S*)
MINUS (SELECT PNO
FROM WORKS_ON
WHERE E.SSN-SSN));
5. For each department that has more than five employees, retrieve the department
number and the number of its employees who are making more than Rs. 6, 00,000.
SELECT D.DNO, COUNT (*)
FROM DEPARTMENT D, EMPLOYEE E.
WHERE D.DNO-E.DNO
AND E.SALARY>600000
AND D.DNO IN (SELECT E1.DNO
FROM EMPLOYEE El
GROUP BY E1.DNO
HAVING COUNT (*)>5)
GROUP BY D.DNO;
SVCE , Bengaluru Dept. of ISE