5. COMPANY DATABASE VTU DBMS LAB | Direct SQL Code
VTU DBMS LAB Program 5
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)
VTU DBMS LAB – Create Table SQL Commands for COMPANY
Create the tables for all of the above attributes
VTU DBMS LAB – Create Table for EMPLOYEE
CREATE TABLE EMPLOYEE (SSN NUMBER(9),
NAME VARCHAR(20),
ADDRESS VARCHAR(25),
SEX CHAR,
SALARY NUMBER(10,2),
SUPERSSN NUMBER(9),
DNO NUMBER(2),
CONSTRAINT PA PRIMARY KEY(SSN),
CONSTRAINT PB FOREIGN KEY(SUPERSSN) REFERENCES EMPLOYEE(SSN) ON DELETE CASCADE);
Table created
DESC EMPLOYEE;
Name Null? Type ------------------------------- -------- ------------ SSN NOT NULL NUMBER(9) NAME VARCHAR2(20) ADDRESS VARCHAR2(25) SEX CHAR(1) SALARY NUMBER(10,2) SUPERSSN NUMBER(9) DNO NUMBER(2)
Create Tabel for DEPARTMENT
CREATE TABLE DEPARTMENT (DNO NUMBER(2),
DNAME VARCHAR(15),
MGRSSN NUMBER(9),
MGRSTARTDATE DATE,
CONSTRAINT PC PRIMARY KEY(DNO),
CONSTRAINT PKD FOREIGN KEY(MGRSSN) REFERENCES EMPLOYEE(SSN) ON DELETE CASCADE);
Table created
DESC DEPARTMENT;
Name Null? Type ------------------------------- -------- ---------------------- DNO NOT NULL NUMBER(2) DNAME VARCHAR2(15) MGRSSN NUMBER(9) MGRSTARTDATE DATE
ALTER TABLE EMPLOYEE ADD CONSTRAINT PE FOREIGN KEY (DNO) REFERENCES DEPARTMENT (DNO) ON DELETE CASCADE;
Table altered
Create Tabel for DLOCATION
CREATE TABLE DLOCATION (DNO NUMBER(2),
DLOC VARCHAR(15),
CONSTRAINT CPK PRIMARY KEY (DNO,DLOC),
CONSTRAINT FK_D1 FOREIGN KEY (DNO) REFERENCES DEPARTMENT (DNO) ON DELETE CASCADE);
Table created
DESC DLOCATION;
Name Null? Type ------ -------- ----------------- DNO NOT NULL NUMBER(2) DLOC NOT NULL VARCHAR2(15)
Create Table for PROJECT
CREATE TABLE PROJECT (PNO NUMBER(2),
PNAME VARCHAR(15),
PLOCATION VARCHAR(15),
DNO NUMBER(2),
CONSTRAINT PKP PRIMARY KEY(PNO),
CONSTRAINT FKD FOREIGN KEY (DNO) REFERENCES DEPARTMENT (DNO) ON DELETE CASCADE);
Table created
DESC PROJECT;
Name Null? Type ------------ -------- ---------------------------- PNO NOT NULL NUMBER(2) PNAME VARCHAR2(15) PLOCATION VARCHAR2(15) DNO NUMBER(2)
Create Table for WORKS_ON
CREATE TABLE WORKS_ON (SSN NUMBER(9),
PNO NUMBER(2),
HOURS NUMBER(3),
CONSTRAINT SP PRIMARY KEY (SSN,PNO),
CONSTRAINT FKS FOREIGN KEY (SSN) REFERENCES EMPLOYEE (SSN) ON DELETE CASCADE,
CONSTRAINT FKP FOREIGN KEY (PNO) REFERENCES PROJECT (PNO) ON DELETE CASCADE);
Table created
DESC WORKS_ON;
Name Null? Type ------- -------- ---------------------------- SSN NOT NULL NUMBER(9) PNO NOT NULL NUMBER(2) HOURS NUMBER(3)
VTU DBMS LAB – SQL Insertion Commands for COMPANY
Enter ‘ / ‘ to continue to insert the values
VTU DBMS LAB – Insert Values into EMPLOYEE
Insert Command
INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES (&SSN,'&NAME','&ADDRESS','&SEX',&SALARY );
Enter the input values
Enter value for ssn: 123456789 Enter value for name: ASHA Enter value for address: YELAHANKA Enter value for sex: F Enter value for salary: 500000 old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA) new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(123456789,'ASHA','YELAHANKA','F',5)
row-1 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for ssn: 234567891 Enter value for name: SHEELA Enter value for address: JAKPUR Enter value for sex: F Enter value for salary: 700000 old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA) new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(234567891,'SHEELA','JAKPUR','F',70)
row-2 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for ssn: 345678912 Enter value for name: PALLAVI Enter value for address: NEWTOWN Enter value for sex: F Enter value for salary: 700000 old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA) new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(345678912,'PALLAVI','NEWTOWN','F')
row-3 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for ssn: 456789123 Enter value for name: SHREYAS Enter value for address: BASAWESWAR NAGAR Enter value for sex: M Enter value for salary: 750000 old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA) new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(456789123,'SHREYAS','BASAWESWAR NA)
row-4 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for ssn: 567891234 Enter value for name: MOHAN Enter value for address: TUMKUR Enter value for sex: M Enter value for salary: 350000 old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA) new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(567891234,'MOHAN','TUMKUR','M',350)
row-5 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for ssn: 678912345 Enter value for name: SCOTT Enter value for address: NEWYORK Enter value for sex: M Enter value for salary: 1000000 old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA) new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(678912345,'SCOTT','NEWYORK','M',10)
row-6 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for ssn: 678912345 Enter value for name: SCOTT Enter value for address: NEWYORK Enter value for sex: M Enter value for salary: 1000000 old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA) new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(678912345,'SCOTT','NEWYORK','M',10)
row-7 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for ssn: 891234567 Enter value for name: SAPNA Enter value for address: VIDYARANYAPURA Enter value for sex: F Enter value for salary: 350000 old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA) new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(891234567,'SAPNA','VIDYARANYAPURA')
row-8 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for ssn: 912345678 Enter value for name: REVAN Enter value for address: MADIWALA Enter value for sex: M Enter value for salary: 450000 old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA) new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(912345678,'REVAN','MADIWALA','M',4)
row-9 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for ssn: 112233445 Enter value for name: SAVITHA Enter value for address: DBSANDRA Enter value for sex: F Enter value for salary: 700000 old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA) new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(112233445,'SAVITHA','DBSANDRA','F')
row-10 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for ssn: 223344556 Enter value for name: VIJAY Enter value for address: VIJAYPURA Enter value for sex: M Enter value for salary: 600000 old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA) new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(223344556,'VIJAY','VIJAYPURA','M')
row-11 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for ssn: 334455667 Enter value for name: MANDHAR Enter value for address: JAYMAHAL Enter value for sex: M Enter value for salary: 500000 old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA) new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(334455667,'MANDHAR','JAYMAHAL','M')
row-12 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for ssn: 445566778 Enter value for name: RAGHAV Enter value for address: YELAHANKA Enter value for sex: M Enter value for salary: 600000 old 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(&SSN,'&NAME','&ADDRESS','&SEX',&SA) new 1: INSERT INTO EMPLOYEE(SSN,NAME,ADDRESS,SEX,SALARY) VALUES(445566778,'RAGHAV','YELAHANKA','M')
row-13 created
Command to select all 13 rows of EMPLOYEE
13-rows selected
Insert Values into DEPARTMENT
Insert Command
INSERT INTO DEPARTMENT VALUES(&DNO,'&DNAME',&MGRSSN,'&MGRSTARTDATE');
Enter the input values
Enter value for dno: 1 Enter value for dname: CSE Enter value for mgrssn: 234567891 Enter value for mgrstartdate: 01-JAN-10 old 1: INSERT INTO DEPARTMENT VALUES(&DNO,'&DNAME',&MGRSSN,'&MGRSTARTDATE') new 1: INSERT INTO DEPARTMENT VALUES(1,'CSE',234567891,'01-JAN-10')
row-1 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for dno: 2 Enter value for dname: ISE Enter value for mgrssn: 345678912 Enter value for mgrstartdate: 15-FEB-11 old 1: INSERT INTO DEPARTMENT VALUES(&DNO,'&DNAME',&MGRSSN,'&MGRSTARTDATE') new 1: INSERT INTO DEPARTMENT VALUES(2,'ISE',345678912,'15-FEB-11')
row-2 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for dno: 3 Enter value for dname: ECE Enter value for mgrssn: 456789123 Enter value for mgrstartdate: 01-MAR-12 old 1: INSERT INTO DEPARTMENT VALUES(&DNO,'&DNAME',&MGRSSN,'&MGRSTARTDATE') new 1: INSERT INTO DEPARTMENT VALUES(3,'ECE',456789123,'01-MAR-12')
row-3 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for dno: 4 Enter value for dname: ACCOUNTS Enter value for mgrssn: 678912345 Enter value for mgrstartdate: 15-APR-13 old 1: INSERT INTO DEPARTMENT VALUES(&DNO,'&DNAME',&MGRSSN,'&MGRSTARTDATE') new 1: INSERT INTO DEPARTMENT VALUES(4,'ACCOUNTS',678912345,'15-APR-13')
row-4 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for dno: 5 Enter value for dname: TCE Enter value for mgrssn: 123456789 Enter value for mgrstartdate: 02-MAY-14 old 1: INSERT INTO DEPARTMENT VALUES(&DNO,'&DNAME',&MGRSSN,'&MGRSTARTDATE') new 1: INSERT INTO DEPARTMENT VALUES(5,'TCE',123456789,'02-MAY-14')
row-5 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for dno: 6 Enter value for dname: ECE Enter value for mgrssn: 567891234 Enter value for mgrstartdate: 15-JUN-15 old 1: INSERT INTO DEPARTMENT VALUES(&DNO,'&DNAME',&MGRSSN,'&MGRSTARTDATE') new 1: INSERT INTO DEPARTMENT VALUES(6,'ECE',567891234,'15-JUN-15')
row-6 created
Command to select all 6 rows of DEPARTMENT
SELECT * FROM DEPARTMENT;
DNO DNAME MGRSSN MGRSTARTDATE
---------- --------------- ---------- ---------
1 CSE 234567891 01-JAN-10
2 ISE 345678912 15-FEB-11
3 ECE 456789123 01-MAR-12
4 ACCOUNTS 678912345 15-APR-13
5 TCE 123456789 02-MAY-14
6 ECE 567891234 15-JUN-15
6-rows selected
Update Employee Details
Update Command
UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN;
Enter the input values
Enter value for superssn: 234567891 Enter value for dno: 1 Enter value for ssn: 123456789 old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN new 1: UPDATE EMPLOYEE SET SUPERSSN=234567891,DNO=1 WHERE SSN=123456789
row-1 updated
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for superssn: 678912345 Enter value for dno: 2 Enter value for ssn: 234567891 old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN new 1: UPDATE EMPLOYEE SET SUPERSSN=678912345,DNO=2 WHERE SSN=234567891
row-2 updated
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for superssn: 234567891 Enter value for dno: 3 Enter value for ssn: 345678912 old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN new 1: UPDATE EMPLOYEE SET SUPERSSN=234567891,DNO=3 WHERE SSN=345678912
row-3 updated
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for superssn: 234567891 Enter value for dno: 4 Enter value for ssn: 456789123 old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN new 1: UPDATE EMPLOYEE SET SUPERSSN=234567891,DNO=4 WHERE SSN=456789123
row-4 updated
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for superssn: 678912345 Enter value for dno: 5 Enter value for ssn: 567891234 old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN new 1: UPDATE EMPLOYEE SET SUPERSSN=678912345,DNO=5 WHERE SSN=567891234
row-5 updated
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for superssn: NULL Enter value for dno: 6 Enter value for ssn: 678912345 old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN new 1: UPDATE EMPLOYEE SET SUPERSSN=NULL,DNO=6 WHERE SSN=678912345
row-6 updated
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for superssn: 223344556 Enter value for dno: 2 Enter value for ssn: 789123456 old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN new 1: UPDATE EMPLOYEE SET SUPERSSN=223344556,DNO=2 WHERE SSN=789123456
row-7 updated
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for superssn: 112233445 Enter value for dno: 2 Enter value for ssn: 891234567 old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN new 1: UPDATE EMPLOYEE SET SUPERSSN=112233445,DNO=2 WHERE SSN=891234567
row-8 updated
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for superssn: 123456789 Enter value for dno: 2 Enter value for ssn: 912345678 old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN new 1: UPDATE EMPLOYEE SET SUPERSSN=123456789,DNO=2 WHERE SSN=912345678
row-9 updated
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for superssn: 234567891 Enter value for dno: 2 Enter value for ssn: 112233445 old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN new 1: UPDATE EMPLOYEE SET SUPERSSN=234567891,DNO=2 WHERE SSN=112233445
row-10 updated
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for superssn: 345678912 Enter value for dno: 2 Enter value for ssn: 223344556 old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN new 1: UPDATE EMPLOYEE SET SUPERSSN=345678912,DNO=2 WHERE SSN=223344556
row-11 updated
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for superssn: 456789123 Enter value for dno: 4 Enter value for ssn: 334455667 old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN new 1: UPDATE EMPLOYEE SET SUPERSSN=456789123,DNO=4 WHERE SSN=334455667
row-12 updated
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for superssn: 345678912 Enter value for dno: 4 Enter value for ssn: 445566778 old 1: UPDATE EMPLOYEE SET SUPERSSN=&SUPERSSN,DNO=&DNO WHERE SSN=&SSN new 1: UPDATE EMPLOYEE SET SUPERSSN=345678912,DNO=4 WHERE SSN=445566778
row-13 updated
Command to select all 13 rows of updated EMPLOYEE
SELECT *FROM EMPLOYEE;
SSN NAME ADDRESS SEX SALARY SUPERSSN DNO --------- ------ ------- ----- -------- --- ---- ------ 123456789 ASHA YELAHANKA F 500000 234567891 1 234567891 SHEELA JAKPUR F 700000 678912345 2 345678912 PALLAVI NEWTOWN F 700000 234567891 3 456789123 SHREYAS BASAWESWAR NAGAR M 750000 234567891 4 567891234 MOHAN TUMKUR M 350000 678912345 5 678912345 SCOTT NEWYORK M 1000000 678912345 6 789123456 DIVYA HUNSMARENHALLI F 350000 223344556 2 891234567 SAPNA VIDYARANYAPURA F 350000 112233445 2 912345678 REVAN MADIWALA M 450000 123456789 2 112233445 SAVITHA DBSANDRA F 700000 234567891 2 223344556 VIJAY VIJAYPURA M 600000 345678912 2 334455667 MANDHAR JAYMAHAL M 500000 456789123 4 445566778 RAGHAV YELAHANKA M 600000 345678912 4
Updated 13-rows selected
Insert Values into DLOCATION
Insert Command
INSERT INTO DLOCATION VALUES(&DNO,'&DLOC');
Enter the input values
Enter value for dno: 1 Enter value for dloc: NB1 old 1: INSERT INTO DLOCATION VALUES(&DNO,'&DLOC') new 1: INSERT INTO DLOCATION VALUES(1,'NB1')
row-1 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for dno: 2 Enter value for dloc: NB0 old 1: INSERT INTO DLOCATION VALUES(&DNO,'&DLOC') new 1: INSERT INTO DLOCATION VALUES(2,'NB0')
row-2 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for dno: 3 Enter value for dloc: NB2 old 1: INSERT INTO DLOCATION VALUES(&DNO,'&DLOC') new 1: INSERT INTO DLOCATION VALUES(3,'NB2')
row-3 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for dno: 4 Enter value for dloc: NB1 old 1: INSERT INTO DLOCATION VALUES(&DNO,'&DLOC') new 1: INSERT INTO DLOCATION VALUES(4,'NB1')
row-4 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for dno: 5 Enter value for dloc: NB3 old 1: INSERT INTO DLOCATION VALUES(&DNO,'&DLOC') new 1: INSERT INTO DLOCATION VALUES(5,'NB3')
row-5 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for dno: 6 Enter value for dloc: EEE2 old 1: INSERT INTO DLOCATION VALUES(&DNO,'&DLOC') new 1: INSERT INTO DLOCATION VALUES(6,'EEE2')
row-6 created
Command to select all 6 rows of DLOCATION
SELECT * FROM DLOCATION;
DNO DLOC
--------- ---------------
1 NB1
2 NB0
3 NB2
4 NB1
5 NB3
6 EEE2
6-rows selected
Insert Values into PROJECT
Insert Command
INSERT INTO PROJECT VALUES(&PNO,'&PNAME','&PLOCATION',&DNO);
Enter the input values
Enter value for pno: 11 Enter value for pname: JAVA Enter value for plocation: MARATHAHALLI Enter value for dno: 1 old 1: INSERT INTO PROJECT VALUES(&PNO,'&PNAME','&PLOCATION',&DNO) new 1: INSERT INTO PROJECT VALUES(11,'JAVA','MARATHAHALLI',1)
row-1 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for pno: 22 Enter value for pname: DOTNET Enter value for plocation: HEBBAL Enter value for dno: 2 old 1: INSERT INTO PROJECT VALUES(&PNO,'&PNAME','&PLOCATION',&DNO) new 1: INSERT INTO PROJECT VALUES(22,'DOTNET','HEBBAL',2)
row-2 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for pno: 33 Enter value for pname: IOT Enter value for plocation: MANYATA Enter value for dno: 3 old 1: INSERT INTO PROJECT VALUES(&PNO,'&PNAME','&PLOCATION',&DNO) new 1: INSERT INTO PROJECT VALUES(33,'IOT','MANYATA',3)
row-3 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for pno: 44 Enter value for pname: ANDROID Enter value for plocation: YELAHANKA Enter value for dno: 4 old 1: INSERT INTO PROJECT VALUES(&PNO,'&PNAME','&PLOCATION',&DNO) new 1: INSERT INTO PROJECT VALUES(44,'ANDROID','YELAHANKA',4)
row-4 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for pno: 55 Enter value for pname: BIGDATA Enter value for plocation: KR PURAM Enter value for dno: 5 old 1: INSERT INTO PROJECT VALUES(&PNO,'&PNAME','&PLOCATION',&DNO) new 1: INSERT INTO PROJECT VALUES(55,'BIGDATA','KR PURAM',5)
row-5 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for pno: 66 Enter value for pname: WEB Enter value for plocation: ELECTRONIC CITY Enter value for dno: 6 old 1: INSERT INTO PROJECT VALUES(&PNO,'&PNAME','&PLOCATION',&DNO) new 1: INSERT INTO PROJECT VALUES(66,'WEB','ELECTRONIC CITY',6)
row-6 created
Command to select all 6 rows of PROJECT
SELECT * FROM PROJECT;
PNO PNAME PLOCATION DNO ----- -------- --------------- ---------- 11 JAVA MARATHAHALLI 1 22 DOTNET HEBBAL 2 33 IOT MANYATA 3 44 ANDROID YELAHANKA 4 55 BIGDATA KR PURAM 5 66 WEB ELECTRONIC CITY 6
6-rows selected
Insert Values into WORKS_ON
Insert Command
INSERT INTO WORKS_ON VALUES(&SSN,&PNO,&HOURS);
Enter the input values
Enter value for ssn: 678912345 Enter value for pno: 11 Enter value for hours: 25 old 1: INSERT INTO WORKS_ON VALUES(&SSN,&PNO,&HOURS) new 1: INSERT INTO WORKS_ON VALUES(678912345,11,25)
row-1 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for ssn: 123456789 Enter value for pno: 22 Enter value for hours: 30 old 1: INSERT INTO WORKS_ON VALUES(&SSN,&PNO,&HOURS) new 1: INSERT INTO WORKS_ON VALUES(123456789,22,30)
row-2 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for ssn: 234567891 Enter value for pno: 33 Enter value for hours: 40 old 1: INSERT INTO WORKS_ON VALUES(&SSN,&PNO,&HOURS) new 1: INSERT INTO WORKS_ON VALUES(234567891,33,40)
row-3 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for ssn: 678912345 Enter value for pno: 44 Enter value for hours: 20 old 1: INSERT INTO WORKS_ON VALUES(&SSN,&PNO,&HOURS) new 1: INSERT INTO WORKS_ON VALUES(678912345,44,20)
row-4 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for ssn: 345678912 Enter value for pno: 55 Enter value for hours: 50 old 1: INSERT INTO WORKS_ON VALUES(&SSN,&PNO,&HOURS) new 1: INSERT INTO WORKS_ON VALUES(345678912,55,50)
row-5 created
Command to continue insertion of the values into next row
/
Enter the input values
Enter value for ssn: 456789123 Enter value for pno: 66 Enter value for hours: 60 old 1: INSERT INTO WORKS_ON VALUES(&SSN,&PNO,&HOURS) new 1: INSERT INTO WORKS_ON VALUES(456789123,66,60)
row-6 created
Command to select all 6 rows of WORKS_ON
SELECT * FROM WORKS_ON;
SSN PNO HOURS ---------- ---------- ---------- 678912345 11 25 123456789 22 30 234567891 33 40 678912345 44 20 345678912 55 50 456789123 66 60
6-rows selected
VTU DBMS LAB – SQL Queries for COMPANY
1] Make a list of all project members for projects that involve an employee whose 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 P.DNO=D.DNO AND D.MGRSSN=E.SSN AND E.NAME='SCOTT')
UNION
(SELECT DISTINCT P.PNO
FROM PROJECT P,WORKS_ON W,EMPLOYEE E
WHERE P.PNO=W.PNO AND W.SSN=E.SSN AND E.NAME='SCOTT');
Output
PNO
----------
11
44
2] Show the resulting salry for employee working on IOT project is given a 10% raise
SELECT E.NAME,1.1*E.SALARY AS HIKE_SALARY FROM EMPLOYEE E,WORKS_ON W,PROJECT P WHERE E.SSN=W.SSN AND P.PNO=W.PNO AND P.PNAME='IOT';
Output
NAME HIKE_SALARY -------------------- ----------- SHEELA 770000
3] Find the sum of salaries of all employees of ‘ACCOUNTS’ department as well as the MAX(SAL),MIN(SAL),AVG(SAL) in this department
SELECT SUM(E.SALARY) AS SUM_SAL,MAX(E.SALARY) AS MAX_SAL,MIN(E.SALARY) AS MIN_SAL,AVG(E.SALARY) AS AVG_SAL FROM EMPLOYEE E,DEPARTMENT D WHERE E.DNO=D.DNO AND D.DNAME='ACCOUNTS';
Output
SUM_SAL MAX_SAL MIN_SAL AVG_SAL ---------- ---------- ---------- ---------- 1850000 750000 500000 616666.667
4] Retrieve the name of each employee who works on all the projects controlled by the department no. 5. (use NOT EXISTS ) operator
SELECT E.NAME FROM EMPLOYEE E
WHERE NOT EXISTS((SELECT P.PNO FROM PROJECT P WHERE P.DNO=5)
MINUS (SELECT W.PNO FROM WORKS_ON W WHERE E.SSN=W.SSN));
NAME -------------------- PALLAVI
5]For each department that has more than 5 employees retrieve the dno and no. of its employees who are making more than 6,00,000
SELECT DNO,COUNT(*) AS NO_OF_EMP
FROM EMPLOYEE
WHERE SALARY>600000 AND DNO IN(SELECT DNO
FROM EMPLOYEE
GROUP BY(DNO)
HAVING COUNT(*)>5)
GROUP BY DNO;
Output
DNO NO_OF_EMP
---------- ----------
2 2
