0% found this document useful (0 votes)
156 views7 pages

DBMS Prog 5

/.,mn
Copyright
© © All Rights Reserved
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
0% found this document useful (0 votes)
156 views7 pages

DBMS Prog 5

/.,mn
Copyright
© © All Rights Reserved
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 ISE 18CSL58-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 ISE 18CSL58-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 ISE 18CSL58-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 ISE 18CSL58-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 ISE 18CSL58-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 ISE 18CSL58-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

You might also like