DrZjCd5m

1program 1
2
31. Retrieve details of all books in the library – id, title, name of publisher, authors, number of copies in each branch, etc.
4SELECT B.BOOK_ID, B.TITLE, B.PUBLISHER_NAME, A.AUTHOR_NAME, C.NO_OF_COPIES, L.BRANCH_ID
5FROM BOOK B, BOOK_AUTHORS A, BOOK_COPIES C, LIBRARY_BRANCH L
6WHERE B.BOOK_ID=A.BOOK_ID
7AND B.BOOK_ID=C.BOOK_ID
8AND L.BRANCH_ID=C.BRANCH_ID;
9
10
111. Get the particulars of borrowers who have borrowed more than 3 books, but from Jan 2017 to Jun 2017.
12SELECT CARD_NO
13FROM BOOK_LENDING
14WHERE DATE_OUT BETWEEN ‘01-JAN-2017‘ AND ‘01-JUL-2017‘
15GROUP BY CARD_NO
16HAVING COUNT (*)>3;
17
18
192. Delete a book in BOOK table. Update the contents of other tables to reflect this data manipulation operation.
20DELETE FROM BOOK
21WHERE BOOK_ID=3;
22
233. Partition the BOOK table based on year of publication. Demonstrate its working with a simple query.
24CREATE VIEW V_PUBLICATION AS
25SELECT PUB_YEAR
26FROM BOOK;
27
284. Create a view of all books and its number of copies that are currently available in the Library.
29CREATE VIEW V_BOOKS AS
30SELECT B.BOOK_ID, B.TITLE, C.NO_OF_COPIES
31FROM BOOK B, BOOK_COPIES C, LIBRARY_BRANCH L
32WHERE B.BOOK_ID=C.BOOK_ID
33AND C.BRANCH_ID=L.BRANCH_ID;
34
35
36program 2 
37
381. Count the customers with grades above Bangalore’s average.
39SELECT GRADE, COUNT (DISTINCT CUSTOMER_ID)
40FROM CUSTOMER1
41GROUP BY GRADE
42HAVING GRADE > (SELECT AVG(GRADE)
43FROM CUSTOMER1
44WHERE CITY='BANGALORE');
45
46
472. Find the name and numbers of all salesmen who had more than one customer.
48SELECT SALESMAN_ID, NAME
49FROM SALESMAN A
50WHERE 1 < (SELECT COUNT (*)
51FROM CUSTOMER1
52WHERE SALESMAN_ID=A.SALESMAN_ID);
53
54
553. List all salesmen and indicate those who have and don’t have customers in their cities (Use UNION operation.)
56SELECT SALESMAN.SALESMAN_ID, NAME, CUST_NAME, COMMISSION
57FROM SALESMAN, CUSTOMER1
58WHERE SALESMAN.CITY = CUSTOMER1.CITY
59UNION
60SELECT SALESMAN_ID, NAME, 'NO MATCH', COMMISSION
61FROM SALESMAN
62WHERE NOT CITY = ANY
63(SELECT CITY
64FROM CUSTOMER1)
65ORDER BY 2 DESC;
66
67
684. Create a view that finds the salesman who has the customer with the highest order of a day.
69CREATE VIEW ELITSALESMAN AS
70SELECT B.ORD_DATE, A.SALESMAN_ID, A.NAME
71FROM SALESMAN A, ORDERS B
72WHERE A.SALESMAN_ID = B.SALESMAN_ID
73AND B.PURCHASE_AMT=(SELECT MAX (PURCHASE_AMT)
74FROM ORDERS C
75WHERE C.ORD_DATE = B.ORD_DATE);
76
775. Demonstrate the DELETE operation by removing salesman with id 1000. All his orders must also be deleted.
78Use ON DELETE CASCADE at the end of foreign key definitions while creating child table orders and then execute the following:
79Use ON DELETE SET NULL at the end of foreign key definitions while creating child table customers and then executes the following:
80DELETE FROM SALESMAN
81WHERE SALESMAN_ID=1000;
82
83
84program 3
85
86
871. List the titles of all movies directed by ‘Hitchcock’.
88SELECT MOV_TITLE
89FROM MOVIES
90WHERE DIR_ID IN (SELECT DIR_ID
91FROM DIRECTOR
92WHERE DIR_NAME = ‗HITCHCOCK‘);
93
94
952. Find the movie names where one or more actors acted in two or more movies.
96SELECT MOV_TITLE
97FROM MOVIES M, MOVIE_CAST MV
98WHERE M.MOV_ID=MV.MOV_ID AND ACT_ID IN (SELECT ACT_ID
99FROM MOVIE_CAST GROUP BY ACT_ID
100HAVING COUNT (ACT_ID)>1)
101GROUP BY MOV_TITLE
102HAVING COUNT (*)>1;
103
104
1053. List all actors who acted in a movie before 2000 and also in a movie after 2015 (use JOIN operation).
106SELECT ACT_NAME, MOV_TITLE, MOV_YEAR
107FROM ACTOR A
108JOIN MOVIE_CAST C
109ON A.ACT_ID=C.ACT_ID
110JOIN MOVIES M
111ON C.MOV_ID=M.MOV_ID
112WHERE M.MOV_YEAR NOT BETWEEN 2000 AND 2015;
113
114OR
115
116SELECT A.ACT_NAME, A.ACT_NAME, C.MOV_TITLE, C.MOV_YEAR
117FROM ACTOR A, MOVIE_CAST B, MOVIES C
118WHERE A.ACT_ID=B.ACT_ID
119AND B.MOV_ID=C.MOV_ID
120AND C.MOV_YEAR NOT BETWEEN 2000 AND 2015;
121
122
1234. Find the title of movies and number of stars for each movie that has at least one rating and find the highest number of stars that movie received. Sort the result by movie title.
124SELECT MOV_TITLE, MAX (REV_STARS)
125FROM MOVIES
126INNER JOIN RATING USING (MOV_ID)
127GROUP BY MOV_TITLE
128HAVING MAX (REV_STARS)>0
129ORDER BY MOV_TITLE;
130
131
1325. Update rating of all movies directed by ‘Steven Spielberg’ to 5
133KL
134UPDATE RATING
135SET REV_STARS=5
136WHERE MOV_ID IN (SELECT MOV_ID FROM MOVIES
137WHERE DIR_ID IN (SELECT DIR_ID
138FROM DIRECTOR
139WHERE DIR_NAME = ‗STEVEN SPIELBERG‘));
140
141
142
143program 4
144
1451. List all the student details studying in fourth semester ‘C’ section.
146SELECT S.*, SS.SEM, SS.SEC
147FROM STUDENT S, SEMSEC SS, CLASS C
148WHERE S.USN = C.USN AND
149SS.SSID = C.SSID AND
150SS.SEM = 4 AND SS.SEc=‘C‘;
151
152
1532. Compute the total number of male and female students in each semester and in each section.
154SELECT SS.SEM, SS.SEC, S.GENDER, COUNT (S.GENDER) AS COUNT
155FROM STUDENT S, SEMSEC SS, CLASS C
156WHERES.USN = C.USN AND
157SS.SSID = C.SSID
158GROUP BY SS.SEM, SS.SEC, S.GENDER
159ORDER BY SEM;
160
161
162
1633. Create a view of Test1 marks of student USN ‘1BI15CS101’ in all subjects.
164CREATE VIEW STU_TEST1_MARKS_VIEW
165AS
166SELECT TEST1, SUBCODE
167FROM IAMARKS
168WHERE USN = '1RN13CS091';
169
1705. Categorize students based on the following criterion:
171If FinalIA = 17 to 20 then CAT = ‘Outstanding’
172If FinalIA = 12 to 16 then CAT = ‘Average’
173If FinalIA< 12 then CAT = ‘Weak’
174Give these details only for 8th semester A, B, and C section students.
175SELECT S.USN,S.SNAME,S.ADDRESS,S.PHONE,S.GENDER,
176(CASE
177WHEN IA.FINALIA BETWEEN 17 AND 20 THEN 'OUTSTANDING'
178WHEN IA.FINALIA BETWEEN 12 AND 16 THEN 'AVERAGE'
179ELSE 'WEAK'
180END) AS CAT
181FROM STUDENT S, SEMSEC SS, IAMARKS IA, SUBJECT SUB
182WHERE S.USN = IA.USN AND
183SS.SSID = IA.SSID AND
184SUB.SUBCODE = IA.SUBCODE AND
185SUB.SEM = 8;
186
187
188
189program 5
190
1911. 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.
192(SELECT DISTINCT P.PNO
193FROM PROJECT P, DEPARTMENT D, EMPLOYEE E
194WHERE E.DNO=D.DNO
195AND D.MGRSSN=E.SSN
196AND E.LNAME=‘SCOTT‘)
197UNION
198(SELECT DISTINCT P1.PNO
199FROM PROJECT P1, WORKS_ON W, EMPLOYEE E1
200WHERE P1.PNO=W.PNO
201AND E1.SSN=W.SSN
202AND E1.LNAME=‘SCOTT‘);
203
204
2052. Show the resulting salaries if every employee working on the ‘IoT’ project is given a 10 percent raise.
206SELECT E.FNAME, E.LNAME, 1.1*E.SALARY AS INCR_SAL
207FROM EMPLOYEE E, WORKS_ON W, PROJECT P
208WHERE E.SSN=W.SSN
209AND W.PNO=P.PNO
210AND P.PNAME=‘IOT‘;
211
212
2133. 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
214SELECT SUM (E.SALARY), MAX (E.SALARY), MIN (E.SALARY), AVG (E.SALARY)
215FROM EMPLOYEE E, DEPARTMENT D
216WHERE E.DNO=D.DNO
217AND D.DNAME=‘ACCOUNTS‘;
218
219
2204. Retrieve the name of each employee who works on all the projects Controlled by department number 5 (use NOT EXISTS operator).
221SELECT E.FNAME, E.LNAME
222FROM EMPLOYEE E
223WHERE NOT EXISTS((SELECT PNO
224FROM PROJECT
225WHERE DNO=‘5‘)
226MINUS (SELECT PNO
227FROM WORKS_ON
228WHERE E.SSN=SSN));
229
230
2315. 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.
232SELECT D.DNO, COUNT (*)
233FROM DEPARTMENT D, EMPLOYEE E
234WHERE D.DNO=E.DNO
235AND E.SALARY>600000
236AND D.DNO IN (SELECT E1.DNO
237FROM EMPLOYEE E1
238GROUP BY E1.DNO
239HAVING COUNT (*)>5)
240GROUP BY D.DNO;