Lab 1

Table Creation

CREATE TABLE PUBLISHER

(NAME VARCHAR2 (20) PRIMARY KEY,
PHONE INTEGER,
ADDRESS VARCHAR2 (20));

CREATE TABLE BOOK

(BOOK_ID INTEGER PRIMARY KEY,
TITLE VARCHAR2 (20),
PUB_YEAR VARCHAR2 (20),
PUBLISHER_NAME REFERENCES PUBLISHER (NAME) ON DELETE CASCADE);

CREATE TABLE BOOK_AUTHORS

(AUTHOR_NAME VARCHAR2 (20),
BOOK_ID REFERENCES BOOK (BOOK_ID) ON DELETE CASCADE,
PRIMARY KEY (BOOK_ID, AUTHOR_NAME));

CREATE TABLE LIBRARY_PGM

(PGM_ID INTEGER PRIMARY KEY,
PGM_NAME VARCHAR2 (50),
ADDRESS VARCHAR2 (50));

CREATE TABLE BOOK_COPIES

(NO_OF_COPIES INTEGER,
BOOK_ID REFERENCES BOOK (BOOK_ID) ON DELETE CASCADE,
PGM_ID REFERENCES LIBRARY_PGM (PGM_ID) ON DELETE CASCADE,
PRIMARY KEY (BOOK_ID, PGM_ID));

CREATE TABLE CARD

(CARD_NO INTEGER PRIMARY KEY);

CREATE TABLE BOOK_LENDING

(DATE_OUT DATE,
DUE_DATE DATE,
BOOK_ID REFERENCES BOOK (BOOK_ID) ON DELETE CASCADE,
PGM_ID REFERENCES LIBRARY_PGM (PGM_ID) ON DELETE CASCADE,
CARD_NO REFERENCES CARD (CARD_NO) ON DELETE CASCADE,
PRIMARY KEY (BOOK_ID, PGM_ID, CARD_NO));

Insert
INSERT INTO PUBLISHER VALUES (’$value’);

INSERT INTO PUBLISHER VALUES (’MCGRAW-HILL’, 9989076587, ’BANGALORE’);

INSERT INTO PUBLISHER VALUES (’PEARSON’, 9889076565, ’NEWDELHI’);
INSERT INTO PUBLISHER VALUES (’RANDOM HOUSE’, 7455679345, ’HYDRABAD’);
INSERT INTO PUBLISHER VALUES (’HACHETTE LIVRE’, 8970862340, ’CHENAI’);
INSERT INTO PUBLISHER VALUES (’GRUPO PLANETA’, 7756120238, ’BANGALORE’);

INSERT INTO BOOK VALUES (1,’DBMS’,’JAN-2017’, ’MCGRAW-HILL’);

INSERT INTO BOOK VALUES (2,’ADBMS’,’JUN-2016’, ’MCGRAW-HILL’);
INSERT INTO BOOK VALUES (3,’CN’,’SEP-2016’, ’PEARSON’);
INSERT INTO BOOK VALUES (4,’CG’,’SEP-2015’, ’GRUPO PLANETA’);
INSERT INTO BOOK VALUES (5,’OS’,’MAY-2016’, ’PEARSON’);

INSERT INTO BOOK_AUTHORS VALUES (’NAVATHE’, 1);

INSERT INTO BOOK_AUTHORS VALUES (’NAVATHE’, 2);
INSERT INTO BOOK_AUTHORS VALUES (’TANENBAUM’, 3);
INSERT INTO BOOK_AUTHORS VALUES (’EDWARD ANGEL’, 4);
INSERT INTO BOOK_AUTHORS VALUES (’GALVIN’, 5);

INSERT INTO LIBRARY_PGM VALUES (10,’RR NAGAR’,’BANGALORE’);

INSERT INTO LIBRARY_PGM VALUES (11,’RNSIT’,’BANGALORE’);
INSERT INTO LIBRARY_PGM VALUES (12,’RAJAJI NAGAR’, ’BANGALORE’);
INSERT INTO LIBRARY_PGM VALUES (13,’NITTE’,’MANGALORE’);
INSERT INTO LIBRARY_PGM VALUES (14,’MANIPAL’,’UDUPI’);

INSERT INTO BOOK_COPIES VALUES (10, 1, 10);

INSERT INTO BOOK_COPIES VALUES (5, 1, 11);
INSERT INTO BOOK_COPIES VALUES (2, 2, 12);
INSERT INTO BOOK_COPIES VALUES (5, 2, 13);
INSERT INTO BOOK_COPIES VALUES (7, 3, 14);
INSERT INTO BOOK_COPIES VALUES (1, 5, 10);
INSERT INTO BOOK_COPIES VALUES (3, 4, 11);

INSERT INTO CARD VALUES (100);

INSERT INTO CARD VALUES (101);
INSERT INTO CARD VALUES (102);
INSERT INTO CARD VALUES (103);
INSERT INTO CARD VALUES (104);

INSERT INTO BOOK_LENDING VALUES (’01-JAN-17’,’01-JUN-17’, 1, 10, 101);

INSERT INTO BOOK_LENDING VALUES (’11-JAN-17’,’11-MAR-17’, 3, 14, 101);
INSERT INTO BOOK_LENDING VALUES (’21-FEB-17’,’21-APR-17’, 2, 13, 101);
INSERT INTO BOOK_LENDING VALUES (’15-MAR-17’,’15-JUL-17’, 4, 11, 101);
INSERT INTO BOOK_LENDING VALUES (’12-APR-17’,’12-MAY-17’, 1, 11, 104);
Query

1)

SELECT B.BOOK_ID, B.TITLE, B.PUBLISHER_NAME, A.AUTHOR_NAME,

C.NO_OF_COPIES, L.PGM_ID
FROM BOOK B, BOOK_AUTHORS A, BOOK_COPIES C, LIBRARY_PGM L
WHERE B.BOOK_ID=A.BOOK_ID
AND B.BOOK_ID=C.BOOK_ID
AND L.PGM_ID=C.PGM_ID;

2)

SELECT CARD_NO
FROM BOOK_LENDING
WHERE DATE_OUT BETWEEN ’01-JAN-2017’ AND ’01-JUL-2017’
GROUP BY CARD_NO
HAVING COUNT (*)>3;

3)

DELETE FROM BOOK

WHERE BOOK_ID=3;

4)

CREATE VIEW V_PUBLICATION AS

SELECT PUB_YEAR
FROM BOOK;

5)

CREATE VIEW V_BOOKS AS

SELECT B.BOOK_ID, B.TITLE, C.NO_OF_COPIES
FROM BOOK B, BOOK_COPIES C, LIBRARY_PGM L
WHERE B.BOOK_ID=C.BOOK_ID
AND C.PGM_ID=L.PGM_ID;
Lab 2

Table Creation

CREATE TABLE SALESMAN

(SALESMAN_ID NUMBER (4),
NAME VARCHAR2 (20),
CITY VARCHAR2 (20),
COMMISSION VARCHAR2 (20),
PRIMARY KEY (SALESMAN_ID));

CREATE TABLE CUSTOMER

(CUSTOMER_ID NUMBER (4),
CUST_NAME VARCHAR2 (20),
CITY VARCHAR2 (20),
GRADE NUMBER (3),
PRIMARY KEY (CUSTOMER_ID),
SALESMAN_ID REFERENCES SALESMAN (SALESMAN_ID) ON DELETE SET NULL);

CREATE TABLE ORDERS

(ORD_NO NUMBER (5),
PURCHASE_AMT NUMBER (10, 2),
ORD_DATE DATE,
PRIMARY KEY (ORD_NO),
CUSTOMER_ID REFERENCES CUSTOMER1 (CUSTOMER_ID) ON DELETE CASCADE,
SALESMAN_ID REFERENCES SALESMAN (SALESMAN_ID) ON DELETE CASCADE);

Insert

INSERT INTO PUBLISHER VALUES (’$value’);

INSERT INTO SALESMAN VALUES (1000, ’JOHN’,’BANGALORE’,’25 %’);

INSERT INTO SALESMAN VALUES (2000, ’RAVI’,’BANGALORE’,’20 %’);
INSERT INTO SALESMAN VALUES (3000, ’KUMAR’,’MYSORE’,’15 %’);
INSERT INTO SALESMAN VALUES (4000, ’SMITH’,’DELHI’,’30 %’);
INSERT INTO SALESMAN VALUES (5000, ’HARSHA’,’HYDRABAD’,’15 %’);

INSERT INTO CUSTOMER VALUES (10, ’PREETHI’,’BANGALORE’, 100, 1000);

INSERT INTO CUSTOMER VALUES (11, ’VIVEK’,’MANGALORE’, 300, 1000);
INSERT INTO CUSTOMER VALUES (12, ’BHASKAR’,’CHENNAI’, 400, 2000);
INSERT INTO CUSTOMER VALUES (13, ’CHETHAN’,’BANGALORE’, 200, 2000);
INSERT INTO CUSTOMER VALUES (14, ’MAMATHA’,’BANGALORE’, 400, 3000);

INSERT INTO ORDERS VALUES (50, 5000, ’04-MAY-17’, 10, 1000);

INSERT INTO ORDERS VALUES (51, 450, ’20-JAN-17’, 10, 2000);
INSERT INTO ORDERS VALUES (52, 1000,’24-FEB-17’, 13, 2000);
INSERT INTO ORDERS VALUES (53, 3500,’13-APR-17’, 14, 3000);
INSERT INTO ORDERS VALUES (54, 550, ’09-MAR-17’, 12, 2000);

Query

1)

SELECT GRADE, COUNT (DISTINCT CUSTOMER_ID)

FROM CUSTOMER1
GROUP BY GRADE
HAVING GRADE > (SELECT AVG(GRADE)
FROM CUSTOMER1
WHERE CITY=’BANGALORE’);

2)

SELECT SALESMAN_ID, NAME

FROM SALESMAN A
WHERE 1 < (SELECT COUNT (*)
FROM CUSTOMER1
WHERE SALESMAN_ID=A.SALESMAN_ID);

3)

SELECT SALESMAN.SALESMAN_ID, NAME, CUST_NAME, COMMISSION

FROM SALESMAN, CUSTOMER1
WHERE SALESMAN.CITY = CUSTOMER1.CITY
UNION
SELECT SALESMAN_ID, NAME, ’NO MATCH’, COMMISSION
FROM SALESMAN
WHERE NOT CITY = ANY
(SELECT CITY
FROM CUSTOMER1)
ORDER BY 2 DESC;

4)

CREATE VIEW ELITSALESMAN AS

SELECT B.ORD_DATE, A.SALESMAN_ID, A.NAME
FROM SALESMAN A, ORDERS B
WHERE A.SALESMAN_ID = B.SALESMAN_ID
AND B.PURCHASE_AMT=(SELECT MAX (PURCHASE_AMT)
FROM ORDERS C
WHERE C.ORD_DATE = B.ORD_DATE);

5)

DELETE FROM SALESMAN

WHERE SALESMAN_ID=1000;
Lab 3

Table Creation

CREATE TABLE ACTOR (

ACT_ID NUMBER (3),
ACT_NAME VARCHAR (20),
ACT_GENDER CHAR (1),
PRIMARY KEY (ACT_ID));

CREATE TABLE DIRECTOR (

DIR_ID NUMBER (3),
DIR_NAME VARCHAR (20),
DIR_PHONE NUMBER (10),
PRIMARY KEY (DIR_ID));

CREATE TABLE MOVIES (

MOV_ID NUMBER (4),
MOV_TITLE VARCHAR (25),
MOV_YEAR NUMBER (4),
MOV_LANG VARCHAR (12),
DIR_ID NUMBER (3),
PRIMARY KEY (MOV_ID),
FOREIGN KEY (DIR_ID) REFERENCES DIRECTOR (DIR_ID));

CREATE TABLE MOVIE_CAST (

ACT_ID NUMBER (3),
MOV_ID NUMBER (4),
ROLE VARCHAR(10),
PRIMARY KEY (ACT_ID, MOV_ID),
FOREIGN KEY (ACT_ID) REFERENCES ACTOR (ACT_ID),
FOREIGN KEY (MOV_ID) REFERENCES MOVIES (MOV_ID));

CREATE TABLE RATING (

MOV_ID NUMBER (4),
REV_STARS VARCHAR (25),
PRIMARY KEY (MOV_ID),
FOREIGN KEY (MOV_ID) REFERENCES MOVIES (MOV_ID));

Insert

INSERT INTO PUBLISHER VALUES (’$value’);

INSERT INTO ACTOR VALUES (301,’ANUSHKA’,’F’);

INSERT INTO ACTOR VALUES (302,’PRABHAS’,’M’);
INSERT INTO ACTOR VALUES (303,’PUNITH’,’M’);
INSERT INTO ACTOR VALUES (304,’JERMY’,’M’);
INSERT INTO DIRECTOR VALUES (60,’RAJAMOULI’, 8751611001);
INSERT INTO DIRECTOR VALUES (61,’HITCHCOCK’, 7766138911);
INSERT INTO DIRECTOR VALUES (62,’FARAN’, 9986776531);
INSERT INTO DIRECTOR VALUES (63,’STEVEN SPIELBERG’, 8989776530);

INSERT INTO MOVIES VALUES (1001,’BAHUBALI-2’, 2017, ‗TELAGU', 60);

INSERT INTO MOVIES VALUES (1002,'BAHUBALI-1', 2015, ‗TELAGU', 60);
INSERT INTO MOVIES VALUES (1003,'AKASH', 2008, ‗KANNADA', 61);
INSERT INTO MOVIES VALUES (1004,'WAR HORSE', 2011, ‗ENGLISH', 63);

INSERT INTO MOVIE_CAST VALUES (301, 1002, ‗HEROINE');

INSERT INTO MOVIE_CAST VALUES (301, 1001, ‗HEROINE');
INSERT INTO MOVIE_CAST VALUES (303, 1003, ‗HERO');
INSERT INTO MOVIE_CAST VALUES (303, 1002, ‗GUEST');
INSERT INTO MOVIE_CAST VALUES (304, 1004, ‗HERO');

INSERT INTO RATING VALUES (1001, 4);

INSERT INTO RATING VALUES (1002, 2);
INSERT INTO RATING VALUES (1003, 5);
INSERT INTO RATING VALUES (1004, 4);

Query

1)

SELECT MOV_TITLE
FROM MOVIES
WHERE DIR_ID IN (SELECT DIR_ID
FROM DIRECTOR
WHERE DIR_NAME = ‗HITCHCOCK');

2)

SELECT MOV_TITLE
FROM MOVIES M, MOVIE_CAST MV
WHERE M.MOV_ID=MV.MOV_ID AND ACT_ID IN (SELECT ACT_ID
FROM MOVIE_CAST GROUP BY ACT_ID
HAVING COUNT (ACT_ID)>1)
GROUP BY MOV_TITLE
HAVING COUNT (*)>1;

3)

SELECT ACT_NAME, MOV_TITLE, MOV_YEAR

FROM ACTOR A
JOIN MOVIE_CAST C
ON A.ACT_ID=C.ACT_ID
JOIN MOVIES M
ON C.MOV_ID=M.MOV_ID
WHERE M.MOV_YEAR NOT BETWEEN 2000 AND 2015;
OR
SELECT A.ACT_NAME, A.ACT_NAME, C.MOV_TITLE, C.MOV_YEAR
FROM ACTOR A, MOVIE_CAST B, MOVIES C
WHERE A.ACT_ID=B.ACT_ID
AND B.MOV_ID=C.MOV_ID
AND C.MOV_YEAR NOT BETWEEN 2000 AND 2015;

4)

SELECT MOV_TITLE, MAX (REV_STARS)

FROM MOVIES
INNER JOIN RATING USING (MOV_ID)
GROUP BY MOV_TITLE
HAVING MAX (REV_STARS)>0
ORDER BY MOV_TITLE;

5)

UPDATE RATING
SET REV_STARS=5
WHERE MOV_ID IN (SELECT MOV_ID FROM MOVIES
WHERE DIR_ID IN (SELECT DIR_ID
FROM DIRECTOR
WHERE DIR_NAME = ‗STEVEN
SPIELBERG'));
LAB 4:

CREATE TABLE STUDENT (

USN VARCHAR (10) PRIMARY KEY,
SNAME VARCHAR (25),
ADDRESS VARCHAR (25),
PHONE NUMBER (10),
GENDER CHAR (1));

CREATE TABLE SEMSEC (

SSID VARCHAR (5) PRIMARY KEY,
SEM NUMBER (2),
SEC CHAR (1));

CREATE TABLE CLASS (

USN VARCHAR (10),
SSID VARCHAR (5),
PRIMARY KEY (USN, SSID),
FOREIGN KEY (USN) REFERENCES STUDENT (USN),
FOREIGN KEY (SSID) REFERENCES SEMSEC (SSID));

CREATE TABLE SUBJECT (

SUBCODE VARCHAR (8),
TITLE VARCHAR (20),
SEM NUMBER (2),
CREDITS NUMBER (2),
PRIMARY KEY (SUBCODE));

CREATE TABLE IAMARKS (

USN VARCHAR (10),
SUBCODE VARCHAR (8),
SSID VARCHAR (5),
TEST1 NUMBER (2),
TEST2 NUMBER (2),
TEST3 NUMBER (2),
FINALIA NUMBER (2),
PRIMARY KEY (USN, SUBCODE, SSID),
FOREIGN KEY (USN) REFERENCES STUDENT (USN),
FOREIGN KEY (SUBCODE) REFERENCES SUBJECT (SUBCODE),
FOREIGN KEY (SSID) REFERENCES SEMSEC (SSID));

INSERT INTO STUDENT VALUES (’1KS13CS020’,’AKSHAY’,’BELAGAVI’,

8877881122,’M’);

INSERT INTO STUDENT VALUES (’1KS13CS062’,’SANDHYA’,’BENGALURU’,

7722829912,’F’);

INSERT INTO STUDENT VALUES (’1KS13CS091’,’TEESHA’,’BENGALURU’,

7712312312,’F’);

INSERT INTO STUDENT VALUES (’1KS13CS066’,’SUPRIYA’,’MANGALURU’,

8877881122,’F’);
INSERT INTO STUDENT VALUES (’1KS14CS010’,’ABHAY’,’BENGALURU’,
9900211201,’M’);

INSERT INTO STUDENT VALUES (’1KS14CS032’,’BHASKAR’,’BENGALURU’,

9923211099,’M’);

INSERT INTO STUDENT VALUES (’1KS14CS025’,’ASMI’,’BENGALURU’, 7894737377,’F’);

INSERT INTO STUDENT VALUES (’1KS15CS011’,’AJAY’,’TUMKUR’, 9845091341,’M’);

INSERT INTO STUDENT VALUES (’1KS15CS029’,’CHITRA’,’DAVANGERE’,

7696772121,’F’);

INSERT INTO STUDENT VALUES (’1KS15CS045’,’JEEVA’,’BELLARY’, 9944850121,’M’);

INSERT INTO STUDENT VALUES (’1KS15CS091’,’SANTOSH’,’MANGALURU’,

8812332201,’M’);

INSERT INTO STUDENT VALUES (’1KS16CS045’,’ISMAIL’,’KALBURGI’,

9900232201,’M’);

INSERT INTO STUDENT VALUES (’1KS16CS088’,’SAMEERA’,’SHIMOGA’,

9905542212,’F’);

INSERT INTO STUDENT VALUES (’1KS16CS122’,’VINAYAKA’,’CHIKAMAGALUR’,

8800880011,’M’);

SQL> select * from student;

USN SNAME ADDRESS PHONE G

---------- ------------------------- ------------------------- ---------- -
1KS13CS020 AKSHAY BELAGAVI 8877881122 M
1KS13CS062 SANDHYA BENGALURU 7722829912 F
1KS13CS091 TEESHA BENGALURU 7712312312 F
1KS13CS066 SUPRIYA MANGALURU 8877881122 F
1KS14CS010 ABHAY BENGALURU 9900211201 M
1KS14CS032 BHASKAR BENGALURU 9923211099 M
1KS14CS025 ASMI BENGALURU 7894737377 F
1KS15CS011 AJAY TUMKUR 9845091341 M
1KS15CS029 CHITRA DAVANGERE 7696772121 F
1KS15CS045 JEEVA BELLARY 9944850121 M
1KS15CS091 SANTOSH MANGALURU 8812332201 M

USN SNAME ADDRESS PHONE G

---------- ------------------------- ------------------------- ---------- -
1KS16CS045 ISMAIL KALBURGI 9900232201 M
1KS16CS088 SAMEERA SHIMOGA 9905542212 F

13 rows selected.

INSERT INTO SEMSEC VALUES (’CSE8A’, 8,’A’);

INSERT INTO SEMSEC VALUES (’CSE8B’, 8,’B’);

INSERT INTO SEMSEC VALUES (’CSE8C’, 8,’C’);

INSERT INTO SEMSEC VALUES (’CSE7A’, 7,’A’);

INSERT INTO SEMSEC VALUES (’CSE7B’, 7,’B’);

INSERT INTO SEMSEC VALUES (’CSE7C’, 7,’C’);

INSERT INTO SEMSEC VALUES (’CSE6A’, 6,’A’);

INSERT INTO SEMSEC VALUES (’CSE6B’, 6,’B’);

INSERT INTO SEMSEC VALUES (’CSE6C’, 6,’C’);

INSERT INTO SEMSEC VALUES (’CSE5A’, 5,’A’);

INSERT INTO SEMSEC VALUES (’CSE5B’, 5,’B’);

INSERT INTO SEMSEC VALUES (’CSE5C’, 5,’C’);

INSERT INTO SEMSEC VALUES (’CSE4A’, 4,’A’);

INSERT INTO SEMSEC VALUES (’CSE4B’, 4,’B’);

INSERT INTO SEMSEC VALUES (’CSE4C’, 4,’C’);

SQL> select * from semsec;

SSID SEM S
----- ---------- -
CSE8A 8A
CSE8B 8B
CSE8C 8C
CSE7A 7A
CSE7B 7B
CSE7C 7C
CSE6A 6A
CSE6B 6B
CSE6C 6C
CSE5A 5A
CSE5B 5B

SSID SEM S
----- ---------- -
CSE5C 5C
CSE4A 4A
CSE4B 4B
CSE4C 4C

15 rows selected.

INSERT INTO CLASS VALUES (’1KS13CS020’,’CSE8A’);

INSERT INTO CLASS VALUES (’1KS13CS062’,’CSE8A’);

INSERT INTO CLASS VALUES (’1KS13CS066’,’CSE8B’);

INSERT INTO CLASS VALUES (’1KS13CS091’,’CSE8C’);

INSERT INTO CLASS VALUES (’1KS14CS010’,’CSE7A’);

INSERT INTO CLASS VALUES (’1KS14CS025’,’CSE7A’);

INSERT INTO CLASS VALUES (’1KS14CS032’,’CSE7A’);

INSERT INTO CLASS VALUES (’1KS15CS011’,’CSE4A’);

INSERT INTO CLASS VALUES (’1KS15CS029’,’CSE4A’);

INSERT INTO CLASS VALUES (’1KS15CS045’,’CSE4B’);

INSERT INTO CLASS VALUES (’1KS15CS091’,’CSE4C’);

SQL> select * from class;

USN SSID
---------- -----
1KS13CS020 CSE8A
1KS13CS062 CSE8A
1KS13CS066 CSE8B
1KS13CS091 CSE8C
1KS14CS010 CSE7A
1KS14CS025 CSE7A
1KS14CS032 CSE7A
1KS15CS011 CSE4A
1KS15CS029 CSE4A
1KS15CS045 CSE4B
1KS15CS091 CSE4C

11 rows selected.

INSERT INTO SUBJECT VALUES (’10CS71’,’OOAD’, 7, 4);

INSERT INTO SUBJECT VALUES (’10CS72’,’ECS’, 7, 4);

INSERT INTO SUBJECT VALUES (’10CS73’,’PTW’, 7, 4);

INSERT INTO SUBJECT VALUES (’10CS74’,’DWDM’, 7, 4);

INSERT INTO SUBJECT VALUES (’10CS75’,’JAVA’, 7, 4);

INSERT INTO SUBJECT VALUES (’10CS76’,’SAN’, 7, 4);

INSERT INTO SUBJECT VALUES (’15CS51’, ’ME’, 5, 4);

INSERT INTO SUBJECT VALUES (’15CS52’,’CN’, 5, 4);

INSERT INTO SUBJECT VALUES (’15CS53’,’DBMS’, 5, 4);

INSERT INTO SUBJECT VALUES (’15CS54’,’ATC’, 5, 4);

INSERT INTO SUBJECT VALUES (’15CS55’,’JAVA’, 5, 3);

INSERT INTO SUBJECT VALUES (’15CS56’,’AI’, 5, 3);

SQL> select * from subject;

SUBCODE TITLE SEM CREDITS

-------- -------------------- ---------- ----------
10CS71 OOAD 7 4
10CS72 ECS 7 4
10CS73 PTW 7 4
10CS74 DWDM 7 4
10CS75 JAVA 7 4
10CS76 SAN 7 4
15CS51 ME 5 4
15CS52 CN 5 4
15CS53 DBMS 5 4
15CS54 ATC 5 4
15CS55 JAVA 5 3

SUBCODE TITLE SEM CREDITS

-------- -------------------- ---------- ----------
15CS56 AI 5 3

12 rows selected.

INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3)

values(’1KS13CS091’,’10CS72’,’CSE8C’, 15, 16, 18);

INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES
(’1KS13CS091’,’10CS71’,’CSE8C’, 12, 19, 14);

INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES
(’1KS13CS091’,’10CS74’,’CSE8C’, 19, 15, 20);

INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES
(’1KS13CS091’,’10CS76’,’CSE8C’, 20, 16, 19);

INSERT INTO IAMARKS (USN, SUBCODE, SSID, TEST1, TEST2, TEST3) VALUES
(’1KS13CS091’,’10CS851’,’CSE8C’, 15, 15, 12);

SQL> select * from IAMARKs;

USN SUBCODE SSID TEST1 TEST2 TEST3 FINALIA

---------- -------- ----- ---------- ---------- ---------- ----------
1KS13CS091 10CS71 CSE8C 12 19 14
1KS13CS091 10CS72 CSE8C 15 16 18
1KS13CS091 10CS74 CSE8C 19 15 20
1KS13CS091 10CS76 CSE8C 20 16 19

1.
SELECT S.*, SS.SEM, SS.SEC
FROM STUDENT S, SEMSEC SS, CLASS
C WHERE S.USN = C.USN AND
SS.SSID = C.SSID
AND SS.SEM = 4 AND SS.SEC=’C’;

USN SNAME ADDRESS PHONE G SEM S

---------- ------------------------- ------------------------- ----------
1KS15CS091 SANTOSH MANGALURU 8812332201 M 4C

2.

SELECT SS.SEM, SS.SEC, S.GENDER, COUNT (S.GENDER) AS

COUNT FROM STUDENT S, SEMSEC SS, CLASS C WHERE S.USN =
C.USN AND SS.SSID = C.SSID
GROUP BY SS.SEM, SS.SEC, S.GENDER
ORDER BY SEM;

SEM S G COUNT
---------- - - ----------
4AF 1
4AM 1
4BM 1
4CM 1
7AF 1
7AM 2
8AF 1
8AM 1
8BF 1
8CF 1

10 rows selected.

3.

CREATE VIEW
STU_TEST1_MARKS_VIEW AS
SELECT TEST1, SUBCODE
FROM IAMARKS
WHERE USN = ’1BI17CS101’;

View created.

4.

CREATE VIEW finalia AS

(SELECT usn, subcode,
GREATEST(AVG(test1 + test2) / 2,AVG(test1 + test3) / 2,AVG(test2 + test3) / 2) AS finalia
FROM iamarks
GROUP BY usn , subcode);

UPDATE iamarks i set finalia=(SELECT finalia from finalia f where i.usn=f.usn and i.subcode=f.subcode);
4 rows updated.

SQL> select * from iamarks;

USN SUBCODE SSID TEST1 TEST2 TEST3 FINALIA

---------- -------- ----- ---------- ---------- ---------- ----------
1KS13CS091 10CS71 CSE8C 12 19 14 17
1KS13CS091 10CS72 CSE8C 15 16 18 17
1KS13CS091 10CS74 CSE8C 19 15 20 20
1KS13CS091 10CS76 CSE8C 20 16 19 20

5.

SELECT S.USN,S.SNAME,S.ADDRESS,S.PHONE,S.GENDER,
(CASE
WHEN IA.FINALIA BETWEEN 17 AND 20 THEN
’OUTSTANDING’ WHEN IA.FINALIA BETWEEN 12 AND 16
THEN ’AVERAGE’ ELSE ’WEAK’
END) AS CAT
FROM STUDENT S, SEMSEC SS, IAMARKS IA, SUBJECT SUB
WHERE S.USN = IA.USN AND
SS.SSID = IA.SSID AND
SUB.SUBCODE = IA.SUBCODE AND
SUB.SEM = 7;

no rows selected;
LAB 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

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
2. Show the resulting salaries if every employee working on the ‘IoT’ project is given a 10 percent raise
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
4. Retrieve the name of each employee who works on all the projects controlled by department number 5
(use NOT EXISTS operator).
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

CREATE TABLE DEPARTMENT

(DNO VARCHAR2 (20) PRIMARY KEY,
DNAME VARCHAR2 (20),
MGRSTARTDATE DATE);

Table created.

SQL> CREATE TABLE EMPLOYEE

(SSN VARCHAR2 (20) PRIMARY KEY,
FNAME VARCHAR2 (20),
LNAME VARCHAR2 (20),
ADDRESS VARCHAR2 (20),
SEX CHAR (1),
SALARY INTEGER,
SUPERSSN REFERENCES EMPLOYEE (SSN),
DNO REFERENCES DEPARTMENT (DNO)); 2 3 4 5 6 7 8 9

Table created.

ALTER TABLE DEPARTMENT ADD MGRSSN REFERENCES EMPLOYEE (SSN);

Table altered.

CREATE TABLE DLOCATION

(DLOC VARCHAR2 (20),
DNO REFERENCES DEPARTMENT (DNO),
PRIMARY KEY (DNO, DLOC));

Table created.

SQL> CREATE TABLE PROJECT

(PNO INTEGER PRIMARY KEY,
PNAME VARCHAR2 (20),
PLOCATION VARCHAR2 (20),
DNO REFERENCES DEPARTMENT (DNO));

Table created.

CREATE TABLE WORKS_ON

(HOURS NUMBER (2),
SSN REFERENCES EMPLOYEE (SSN),
PNO REFERENCES PROJECT(PNO),
PRIMARY KEY (SSN, PNO));

Table created.

INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)

VALUES ( ’ksit01’,’JOHN’,’SCOTT’,’CHICKB’,’M’, 450000);

INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)

VALUES ( ’ksit02’,’JAMES’,’SMITH’,’CHICKBALLAPUR’,’M’, 500000);

INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)

VALUES (’ksit03’,’HEARN’,’BAKER’,’CHICKBALLAPUR’,’M’, 700000);

INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)

VALUES ( ’ksit04’,’EDWARD’,’SCOTT’,’MYSORE’,’M’, 500000);

INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)

VALUES ( ’ksit05’,’PAVAN’,’HEGDE’,’MANGALORE’,’M’, 650000);

INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY)

VALUES ( ’ksit06’,’GIRISH’,’MALYA’,’MYSORE’,’M’, 450000);

SQL> select * from employee;

SSN FNAME LNAME ADDRESS S SALARY SUPERSSN

-------------------- -------------------- -------------------- -------------------- - ---------- --------------------
ksit01 JOHN SCOTT CHICKB M 450000
ksit02 JAMES SMITH CHICKBALLAPUR M 500000
ksit04 EDWARD SCOTT MYSORE M 500000
ksit03 HEARN BAKER CHICKBALLAPUR M 700000
ksit05 PAVAN HEGDE MANGALORE M 650000
ksit06 GIRISH MALYA MYSORE M 450000

6 rows selected.

INSERT INTO DEPARTMENT VALUES ( 1,’ACCOUNTS’,’01-JAN-01’,’ksit01’);

INSERT INTO DEPARTMENT VALUES ( 2,’IT’,’01-AUG-16’,’ksit02’);

INSERT INTO DEPARTMENT VALUES ( 3,’ECE’,’01-JUN-08’,’ksit03’);

INSERT INTO DEPARTMENT VALUES ( 4,’ISE’,’01-AUG-15’,’ksit04’);

INSERT INTO DEPARTMENT VALUES ( 5,’CSE’,’01-JUN-02’,’ksit05’);

SQL> select * from department;

DNO DNAME MGRSTART MGRSSN

-------------------- -------------------- -------- --------------------
1 ACCOUNTS 01-01-01 ksit01
2 IT 01-08-16 ksit02
3 ECE 01-06-08 ksit03
4 ISE 01-08-15 ksit04
5 CSE 01-06-02 ksit05

UPDATE EMPLOYEE SET

SUPERSSN=NULL, DNO=’3’
WHERE SSN=’ksit02’;

UPDATE EMPLOYEE SET

SUPERSSN=’ksit02’, DNO=’5’
WHERE SSN=’ksit01’;

UPDATE EMPLOYEE SET

SUPERSSN=’ksit01’, DNO=’5’
WHERE SSN=’ksit01’;

select * from employee;

SSN FNAME LNAME ADDRESS S SALARY SUPERSSN DNO

-------------------- -------------------- -------------------- -------------------- - ---------- -------------------- ----------
ksit01 JOHN SCOTT CHICKB M 450000 ksit01 5
ksit02 JAMES SMITH CHICKBALLAPUR M 500000 3
ksit04 EDWARD SCOTT MYSORE M 500000
ksit03 HEARN BAKER CHICKBALLAPUR M 700000
ksit05 PAVAN HEGDE MANGALORE M 650000 ksit01 5
ksit06 GIRISH MALYA MYSORE M 450000

6 rows selected.

INSERT INTO DLOCATION VALUES (’CHICKBALLAPUR’, ’1’);

INSERT INTO DLOCATION VALUES (’CHICKBALLAPUR’, ’2’);
INSERT INTO DLOCATION VALUES (’CHICKBALLAPUR’, ’3’);
INSERT INTO DLOCATION VALUES (’MANGALORE’, ’4’);
INSERT INTO DLOCATION VALUES (’MANGALORE’, ’5’);

SQL> select * from dlocation;

DLOC DNO
-------------------- --------------------
CHICKBALLAPUR 1
CHICKBALLAPUR 2
CHICKBALLAPUR 3
MANGALORE 4
MANGALORE 5

INSERT INTO PROJECT VALUES (100,’IOT’,’CHICKBALLAPUR’,’5’);

INSERT INTO PROJECT VALUES (101,’CLOUD’,’CHICKBALLAPUR’,’5’);

INSERT INTO PROJECT VALUES (102,’BIGDATA’,’CHICKBALLAPUR’,’5’);

INSERT INTO PROJECT VALUES (103,’SENSORS’,’CHICKBALLAPUR’,’3’);

INSERT INTO PROJECT VALUES (104,’BANK MANAGEMENT’,’CHICKBALLAPUR’,’1’);

INSERT INTO PROJECT VALUES (105,’SALARY MANAGEMENT’,’CHICKBALLAPUR’,’1’);

INSERT INTO PROJECT VALUES (106,’OPENSTACK’,’CHICKBALLAPUR’,’4’);

INSERT INTO PROJECT VALUES (107,’SMART CITY’,’CHICKBALLAPUR’,’2’);

SQL> select * from project;

PNO PNAME PLOCATION DNO

---------- -------------------- -------------------- --------------------
100 IOT CHICKBALLAPUR 5
101 CLOUD CHICKBALLAPUR 5
102 BIGDATA CHICKBALLAPUR 5
103 SENSORS CHICKBALLAPUR 3
104 BANK MANAGEMENT CHICKBALLAPUR 1
105 SALARY MANAGEMENT CHICKBALLAPUR 1
106 OPENSTACK CHICKBALLAPUR 4
107 SMART CITY CHICKBALLAPUR 2

8 rows selected.

INSERT INTO WORKS_ON VALUES (4, ’ksit01’, 100);

INSERT INTO WORKS_ON VALUES (6, ’ksit01’, 101);

INSERT INTO WORKS_ON VALUES (8, ’ksit01’, 102);

INSERT INTO WORKS_ON VALUES (10, ’ksit02’, 100);

SQL> select * from works_on;

HOURS SSN PNO

---------- -------------------- ----------
4 ksit01 100
6 ksit01 101
8 ksit01 102
10 ksit02 100

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 P1, WORKS_ON W, EMPLOYEE E1
WHERE P1.PNO=W.PNO
AND E1.SSN=W.SSN
AND E1.LNAME=’SCOTT’);

PNO
----------
100
101
102

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’;

FNAME LNAME INCR_SAL

-------------------- -------------------- ----------
JOHN SCOTT 495000
JAMES SMITH 550000

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 (E.SALARY)
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DNO=D.DNO
AND D.DNAME=’ACCOUNTS’;

SUM(E.SALARY) MAX(E.SALARY) MIN(E.SALARY) AVG(E.SALARY)

------------- ------------- ------------- -------------

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=’5’)
MINUS (SELECT PNO
FROM WORKS_ON
WHERE E.SSN=SSN));

FNAME LNAME
-------------------- --------------------
JOHN SCOTT

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 E1
GROUP BY E1.DNO
HAVING COUNT (*)>5)
GROUP BY D.DNO;

no rows selected