· 7 years ago · Dec 03, 2018, 02:18 AM
1Pg-1
2Queries:
31. Retrieve details of all books in the library – id, title, name of publisher, authors,
4number of copies in each branch, etc.
5SELECT B.BOOK_ID, B.TITLE, B.PUBLISHER_NAME, A.AUTHOR_NAME,
6C.NO_OF_COPIES, L.BRANCH_ID
7FROM BOOK B, BOOK_AUTHORS A, BOOK_COPIES C, LIBRARY_BRANCH L
8WHERE B.BOOK_ID=A.BOOK_ID
9AND B.BOOK_ID=C.BOOK_ID
10AND L.BRANCH_ID=C.BRANCH_ID;
11
122. Get the particulars of borrowers who have borrowed more than 3 books, but from
13Jan 2017 to Jun 2017.
14SELECT CARD_NO
15FROM BOOK_LENDING
16WHERE DATE_OUT BETWEEN ‘01-JAN-2017‘ AND ‘01-JUL-2017‘
17GROUP BY CARD_NO
18HAVING COUNT (*)>3;
19
203. Delete a book in BOOK table. Update the contents of other tables to reflect this data
21manipulation operation.
22DELETE FROM BOOK
23WHERE BOOK_ID=3;
24
254. Partition the BOOK table based on year of publication. Demonstrate its working with a
26simple query.
27CREATE VIEW V_PUBLICATION AS
28SELECT PUB_YEAR
29FROM BOOK;
30
315. Create a view of all books and its number of copies that are currently available in the
32Library.
33CREATE VIEW V_BOOKS AS
34SELECT B.BOOK_ID, B.TITLE, C.NO_OF_COPIES
35FROM BOOK B, BOOK_COPIES C, LIBRARY_BRANCH L
36WHERE B.BOOK_ID=C.BOOK_ID
37AND C.BRANCH_ID=L.BRANCH_ID;
38---------------------------
39Pg-2
401. Count the customers with grades above Bangalore’s average.
41SELECT GRADE, COUNT (DISTINCT CUSTOMER_ID)
42FROM CUSTOMER1
43GROUP BY GRADE
44HAVING GRADE > (SELECT AVG(GRADE)
45FROM CUSTOMER1
46WHERE CITY='BANGALORE');
47
482. Find the name and numbers of all salesmen who had more than one customer.
49SELECT SALESMAN_ID, NAME
50FROM SALESMAN A
51WHERE 1 < (SELECT COUNT (*)
52FROM CUSTOMER1
53WHERE SALESMAN_ID=A.SALESMAN_ID);
54
553. List all salesmen and indicate those who have and don’t have customers in their
56cities (Use UNION operation.)
57SELECT SALESMAN.SALESMAN_ID, NAME, CUST_NAME, COMMISSION
58FROM SALESMAN, CUSTOMER1
59WHERE SALESMAN.CITY = CUSTOMER1.CITY
60UNION
61SELECT SALESMAN_ID, NAME, 'NO MATCH', COMMISSION
62FROM SALESMAN
63WHERE NOT CITY = ANY
64(SELECT CITY
65 FROM CUSTOMER1)
66ORDER BY 2 DESC;
67
68
694. Create a view that finds the salesman who has the customer with the highest order
70of a day.
71CREATE VIEW ELITSALESMAN AS
72SELECT B.ORD_DATE, A.SALESMAN_ID, A.NAME
73FROM SALESMAN A, ORDERS B
74WHERE A.SALESMAN_ID = B.SALESMAN_ID
75AND B.PURCHASE_AMT=(SELECT MAX (PURCHASE_AMT)
76FROM ORDERS C
77WHERE C.ORD_DATE = B.ORD_DATE);
78
795. Demonstrate the DELETE operation by removing salesman with id 1000. All his orders
80must also be deleted.
81Use ON DELETE CASCADE at the end of foreign key definitions while creating child table
82orders and then execute the following:
83Use ON DELETE SET NULL at the end of foreign key definitions while creating child table
84customers and then executes the following:
85DELETE FROM SALESMAN
86WHERE SALESMAN_ID=1000;
87-------------------------
88Pg-3
89Queries:
901. List the titles of all movies directed by ‘Hitchcock’.
91SELECT MOV_TITLE
92FROM MOVIES
93WHERE DIR_ID IN (SELECT DIR_ID
94FROM DIRECTOR
95WHERE DIR_NAME = ‗HITCHCOCK‘);
96
972. Find the movie names where one or more actors acted in two or more movies.
98SELECT MOV_TITLE
99FROM MOVIES M, MOVIE_CAST MV
100WHERE M.MOV_ID=MV.MOV_ID AND ACT_ID IN (SELECT ACT_ID
101FROM MOVIE_CAST GROUP BY ACT_ID
102HAVING COUNT (ACT_ID)>1)
103GROUP BY MOV_TITLE
104HAVING COUNT (*)>1;
105
1063. List all actors who acted in a movie before 2000 and also in a movie after 2015 (use
107JOIN operation).
108SELECT ACT_NAME, MOV_TITLE, MOV_YEAR
109FROM ACTOR A
110JOIN MOVIE_CAST C
111ON A.ACT_ID=C.ACT_ID
112JOIN MOVIES M
113ON C.MOV_ID=M.MOV_ID
114WHERE M.MOV_YEAR NOT BETWEEN 2000 AND 2015;
115
1164. Find the title of movies and number of stars for each movie that has at least one
117rating and find the highest number of stars that movie received. Sort the result by
118movie title.
119SELECT MOV_TITLE, MAX (REV_STARS)
120FROM MOVIES
121INNER JOIN RATING USING (MOV_ID)
122GROUP BY MOV_TITLE
123HAVING MAX (REV_STARS)>0
124ORDER BY MOV_TITLE;
125
1265. Update rating of all movies directed by ‘Steven Spielberg’ to 5
127KL
128UPDATE RATING
129SET REV_STARS=5
130WHERE MOV_ID IN (SELECT MOV_ID FROM MOVIES
131WHERE DIR_ID IN (SELECT DIR_ID
132FROM DIRECTOR
133WHERE DIR_NAME = ‗STEVEN
134SPIELBERG‘));
135-------------------------
136Pg-4
1371. List all the student details studying in fourth semester ‘C’ section.
138SELECT S.*, SS.SEM, SS.SEC
139FROM STUDENT S, SEMSEC SS, CLASS C
140WHERE S.USN = C.USN AND
141SS.SSID = C.SSID AND
142SS.SEM = 4 AND SS.SEC='C';
143
1442. Compute the total number of male and female students in each semester and in each
145section.
146SELECT SS.SEM, SS.SEC, S.GENDER, COUNT (S.GENDER) AS COUNT
147FROM STUDENT S, SEMSEC SS, CLASS C
148WHERES.USN = C.USN AND
149SS.SSID = C.SSID
150GROUP BY SS.SEM, SS.SEC, S.GENDER
151ORDER BY SEM;
152
1533. Create a view of Test1 marks of student USN ‘1BI15CS101’ in all subjects.
154CREATE VIEW STU_TEST1_MARKS_VIEW
155AS
156SELECT TEST1, SUBCODE
157FROM IAMARKS
158WHERE USN = '1RN13CS091';
159
1604.Create the FinalA and update the corresponding table for all students
161
162>DELIMITED //
163CREATE PRODUCE UPDATE FINALA()
164BEGIN
165UPDATE IAMARKS
166SET FINALIA= (CASE
167WHEN GREATER(TEST1,TEST2)!=GREATER(TEST1,TEST3) THEN
168GREATER(TEST1,TEST2)+GREATER(TEST1,TEST3)
169ELSE
170GREATER(TEST1,TEST2)+GREATER(TEST3+TEST2)
171END)/2
172WHERE FINALIA IS NULL;
173END //
174DELIMITER;
175>CALL UPDATE FINALIA();
176>SELECT * FROM IAMARKS;
177
1785. Categorize students based on the following criterion:
179If FinalIA = 17 to 20 then CAT = ‘Outstanding’
180If FinalIA = 12 to 16 then CAT = ‘Average’
181If FinalIA< 12 then CAT = ‘Weak’
182Give these details only for 8th semester A, B, and C section students.
183SELECT S.USN,S.SNAME,S.ADDRESS,S.PHONE,S.GENDER,
184 (CASE
185 WHEN IA.FINALIA BETWEEN 17 AND 20 THEN 'OUTSTANDING'
186 WHEN IA.FINALIA BETWEEN 12 AND 16 THEN 'AVERAGE';
187 ELSE 'WEAK'
188 END) AS CAT
189FROM STUDENT S, SEMSEC SS, IAMARKS IA, SUBJECT SUB
190WHERE S.USN = IA.USN AND
191SS.SSID = IA.SSID AND
192SUB.SUBCODE = IA.SUBCODE AND
193SUB.SEM = 8;
194---------------------
195Pg-5
196
1971. Make a list of all project numbers for projects that involve an employee whose last
198name is ‘Scott’, either as a worker or as a manager of the department that controls the
199project.
200(SELECT DISTINCT P.PNO
201FROM PROJECT P, DEPARTMENT D, EMPLOYEE E
202WHERE E.DNO=D.DNO
203AND D.MGRSSN=E.SSN
204AND E.LNAME=‘SCOTT‘)
205UNION
206(SELECT DISTINCT P1.PNO
207FROM PROJECT P1, WORKS_ON W, EMPLOYEE E1
208WHERE P1.PNO=W.PNO
209AND E1.SSN=W.SSN
210AND E1.LNAME=‘SCOTT‘);
211
2122. Show the resulting salaries if every employee working on the ‘IoT’ project is given a 10
213percent raise.
214SELECT E.FNAME, E.LNAME, 1.1*E.SALARY AS INCR_SAL
215FROM EMPLOYEE E, WORKS_ON W, PROJECT P
216WHERE E.SSN=W.SSN
217AND W.PNO=P.PNO
218AND P.PNAME=‘IOT‘;
219
2203. Find the sum of the salaries of all employees of the ‘Accounts’ department, as well as
221the maximum salary, the minimum salary, and the average salary in this department
222SELECT SUM (E.SALARY), MAX (E.SALARY), MIN (E.SALARY), AVG
223(E.SALARY)
224FROM EMPLOYEE E, DEPARTMENT D
225WHERE E.DNO=D.DNO
226AND D.DNAME=‘ACCOUNTS‘;
227
2284. Retrieve the name of each employee who works on all the projects Controlled by
229department number 5 (use NOT EXISTS operator).
230SELECT E.FNAME, E.LNAME
231FROM EMPLOYEE E
232WHERE NOT EXISTS((SELECT PNO
233FROM PROJECT
234WHERE DNO=‘5‘)
235 MINUS (SELECT PNO
236 FROM WORKS_ON
237 WHERE E.SSN=SSN));
238
2395. For each department that has more than five employees, retrieve the department
240number and the number of its employees who are making more than Rs. 6, 00,000.
241SELECT D.DNO, COUNT (*)
242FROM DEPARTMENT D, EMPLOYEE E
243WHERE D.DNO=E.DNO
244AND E.SALARY>600000
245AND D.DNO IN (SELECT E1.DNO
246FROM EMPLOYEE E1
247GROUP BY E1.DNO
248HAVING COUNT (*)>5)
249GROUP BY D.DNO;