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

Leave a Reply

Your email address will not be published. Required fields are marked *

WhatsApp Icon Join For Job Alerts