Program 5 (QUERIES)

Queries (Program 5)

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,WORKS_ON  W, EMPLOYEE E

WHERE P.Pno=W.Pno ANDW.Ssn=E.Ssn AND E.Name like '%Scott')

UNION

(SELECTP.Pno                                                                 

FROM PROJECT P,EMPLOYEE E, DEPARTMENT D    

WHEREP.Dno=D.DnoANDD.Mgrssn=E.Ssn AND E.Name like '%Scott');

OR

(SELECT DISTINCT W. Pno

FROM WORKS_ON  W, EMPLOYEE E

WHERE W.Ssn=E.Ssn AND E.Name like '%Scott')

UNION

(SELECTP.Pno                                                                 

FROM PROJECT P,EMPLOYEE E, DEPARTMENT D    

WHEREP.Dno=D.DnoANDD.Mgrssn=E.Ssn AND E.Name like '%Scott');

2.   Show the resulting salaries if every employee working on the ‘IoT’ project is given a 10 percent raise.

SELECT Name, Salary*1.1 as New_Salary

FROM EMPLOYEE E, WORKS_ON W,PROJECT P

where E.Ssn=W.Ssn AND W.Pno=P.Pno AND P.Pname='IoT';

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(Salary),MAX(Salary),MIN(Salary),AVG(Salary)

FROM EMPLOYEE E,DEPARTMENT D

WHERE E.Dno=D.Dno AND D.Dname='Accounts';

4. Retrieve the name of each employee who works on all the projects controlled by department number 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

                                         FROMWORKS_ON W

                                    WHERE E.Ssn=W.Ssn));

OR(Alternate Query)

SELECT E.Name

FROM EMPLOYEE E

WHERE   NOT EXISTS ( SELECT  *

                                    FROM WORKS_ON B

                                           WHERE (B.Pno IN (  SELECT  P.Pno

                                                                        FROM PROJECT P

                                                                        WHERE P.Dno=5) AND

NOT EXISTS( SELECT *

                                                                                    FROM WORKS_ON C

                                                                        WHERE C.Ssn=E.Ssn ANDC.Pno=B.Pno)));

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(*) as NO_OF_EMP

FROM DEPARTMENT D,EMPLOYEE E

WHERE D.Dno=E.Dno AND E.Salary>600000 AND E.Dno IN(SELECTE.Dno

                                                                                    FROM EMPLOYEE E

                                                                                    GROUP BY E.Dno

                                                                                    HAVING COUNT(*)>5)

GROUP BY D.Dno;  

OR

SELECT Dno,COUNT(*) as NO_OF_EMP

FROM EMPLOYEE E

WHERE Salary>600000 AND Dno IN(SELECT Dno

                                                    FROM EMPLOYEE

                                                    GROUP BY Dno

                                                     HAVING COUNT(*)>5)

GROUP BY Dno;

Program 4 (QUERIES)

Queries (Program 4)

1.List all the student details studying in fourth semester ‘C’ section.

SELECT *

FROM STUDENT

WHERE Usn IN(SELECT Usn

                                 FROM CLASS

                                 WHERE Ssid IN(SELECT Ssid

                                                                  FROM SEMSEC

                                                                  WHERE Sem=4 AND Sec='C'));

                                              OR

SELECT S.*,Sem,Sec 

FROM STUDENT S,CLASS C,SEMSEC SS

WHERE S.Usn=C.Usn AND C.Ssid=SS.Ssid AND Sem=4 AND Sec='C';

OR

SELECT S.*,Ssid

FROM STUDENT S,CLASS C

WHERE S.Usn=C.Usn AND Ssid='4c';

2.  Compute the total number of male and female students in each semester and in each section.

SELECT Sem,Sec,Gender,count(s.Gender) as COUNT

FROM STUDENT S,CLASS C,SEMSEC SS

WHERE S.Usn=C.Usn AND C.Ssid=SS.Ssid

GROUP BY Sem,Sec,Gender

ORDER BY Sem;

3.  Create a view of Test1 marks of student USN ‘1BI15CS101’ in all subjects. (We have changed to 1KG13CS001).

CREATE VIEW STUD_TEST1_MARKS

AS SELECT Subcode,Test1

       FROM IAMARKS

       WHERE Usn='1KG13CS001';

4.  Calculate the FinalIA (average of best two test marks) and update the corresponding table for all students.

CREATE OR REPLACE PROCEDURE AVG_MARKS

IS

CURSOR C_IAMARKS IS

SELECT GREATEST(TEST1,TEST2), GREATEST(TEST1,TEST3), GREATEST(TEST2,TEST3)

FROM IAMARKS

WHERE FinalIA IS NULL

FOR UPDATE;

C_A number;

C_B number;

C_C number;

C_SM number;

C_AV number;

BEGIN

    OPEN C_IAMARKS;

LOOP

            FETCH C_IAMARKS INTO C_A,C_B,C_C;

            EXIT WHEN C_IAMARKS%NOTFOUND;

           

IF(C_A!=C_B) THEN

C_SM:=C_A+C_B;

ELSE

C_SM:=C_A+C_C;

END IF;

C_AV:=C_SM/2;

UPDATE IAMARKS SET FinalIA=C_AV WHERE CURRENT OF C_IAMARKS;

END LOOP;

     CLOSE C_IAMARKS;

END;

5. Categorize students based on the following criterion: 

If FinalIA = 17 to 20 then CAT = ‘Outstanding’

If FinalIA = 12 to 16 then CAT = ‘Average’

If FinalIA< 12 then CAT = ‘Weak’

Give these details only for 8^th semester A, B, and C section students.

SELECT S.Usn,Sname,IA.Subcode,

            (CASE

                        WHEN FinalIA BETWEEN 17 AND 20 THEN 'OUTSTANDING'

                        WHEN FinalIA BETWEEN 12 AND 16 THEN 'AVERAGE'

                        ELSE 'WEAK'

            END) AS CATEGORY

FROM STUDENT S,IAMARKS IA,SUBJECT SUB,SEMSECSS

WHERE S.Usn=IA.Usn AND IA.Ssid=SS.Ssid AND IA.Subcode=SUB.Subcode AND SS.Sem=8;

Program 3 (QUERIES)

Queries (Program 3)

1.  List the titles of all movies directed by ‘Hitchcock’.

SELECT Mov_title

FROM MOVIES

WHERE Dir_id IN (SELECT Dir_id

                                    FROM DIRECTOR

                                 Where Dir_name='Hitchcock');

OR

SELECT Mov_title

FROM MOVIES M, DIRECTOR D

WHERE M.Dir_id=D.Dir_id and Dir_name=’Hitchcock’;

2.  Find the movie names where one or more actors acted in two or more movies.

SELECT Mov_title

FROM MOVIES M,MOVIE_CAST MC

WHERE M.Mov_id=MC.Mov_id AND Act_id IN ( SELECT Act_id

                                                                                    FROM MOVIE_CAST

                                                                                    GROUP BY Act_id

                                                                                    HAVING COUNT(ACT_ID)>1);

3.  List all actors who acted in a movie before 2000 and also in a movie after 2015 (use JOIN operation).

SELECT A.Act_name

FROM ACTOR A

            INNER JOIN MOVIE_CAST MC1 ON MC1.Act_id=A.Act_id

            INNER JOIN MOVIE_CAST MC2 ON MC2.Act_id=A.Act_id

            INNER JOIN MOVIES M1 ON MC1.Mov_id=M1.Mov_id

            INNER JOIN MOVIES M2 ON MC2.Mov_id=M2.Mov_id

WHERE M1.Mov_year<2000 AND M2.Mov_Year>2015;

OR

SELECT A.Act_name

FROM ACTOR A

            JOIN MOVIE_CAST MC1 ON MC1.Act_id=A.Act_id

            JOIN MOVIE_CAST MC2 ON MC2.Act_id=A.Act_id

            JOIN MOVIES M1 ON MC1.Mov_id=M1.Mov_id

            JOIN MOVIES M2 ON MC2.Mov_id=M2.Mov_id

WHERE M1.Mov_year<2000 AND M2.Mov_Year>2015;

4.  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.

SELECT Mov_title,MAX(Rev_stars),SUM(Rev_stars)

FROM MOVIES M,RATING R

WHERE M.Mov_id=R.Mov_id

GROUP BY Mov_title

ORDER BY Mov_title;

OR

SELECT Mov_title,Rev_stars

FROM MOVIES M,RATING R

WHERE M.Mov_id=R.Mov_id

Order by Mov_title;

5.  Update rating of all movies directed by ‘Steven Spielberg’ to 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'));

OR

UPDATE RATING

SET Rev_stars=5

WHERE Mov_id IN(SELECT Mov_id

        FROM MOVIES M,DIRECTOR D

         WHERE M.Dir_id =D. Dir_id  and  Dir_name='Steven Spielberg');

Program 2 (QUERIES)

Queries (Program 2)

1.  Count the customers with grades above Bengaluru’s average.

SELECT Grade, COUNT(customer_id)

FROM  CUSTOMER

GROUP BY Grade

HAVING Grade > (SELECT AVG(Grade)

                             FROM CUSTOMER

                             WHERE City='Bengaluru');

2.  Find the name and numbers of all salesman who had more than one customer.

SELECT S.Salesman_id,Name

FROM SALESMAN S

WHERE 1 < (SELECT COUNT(*)

                        FROM CUSTOMER C               

                        WHERE C.Salesman_id=S.Salesman_id);

OR

SELECT S.Salesman_id,Name

FROM SALESMAN S

WHERE (SELECT COUNT(*)

              FROM CUSTOMER C

            WHERE C.Salesman_id=S.Salesman_id)>1;

OR

SELECT s.salesman_id,name

FROM salesman s, customer c

WHERE s.salesman_id=c.salesman_id

GROUP BY s.salesman_id,name

HAVING count(customer_id)>1;

3.  List all the salesman and indicate those who have and don’t have customers in their cities (Use UNION operation).

SELECT S.Salesman_id,Name,Cust_name,S.City

FROM SALESMAN S,CUSTOMER C

WHERE S.City=C.City and S.Salesman_id=C.Salesman_id

UNION               

SELECT S.Salesman_id,Name,Cust_name,'no match'

FROM SALESMAN S,CUSTOMER C

WHERE S.city!=C.city and S.Salesman_id=C.Salesman_id

ORDER BY 1;

4.  Create  a  view  that  finds  the  salesman  who  has  the  customer  with  the  highest order of a day.

CREATE VIEW LUCKY_SALESMAN

AS SELECT Ord_date,S.Salesman_id,Name

FROM SALESMAN S,ORDERS O

WHERE S.Salesman_id=O.Salesman_id AND O.Purchase_amt=

(SELECT MAX(Purchase_amt)

                                                                                                  FROM ORDERS O2

                                                                                                WHERE O2.Ord_date=O.Ord_date);

                                                   OR    

CREATE VIEW LUCKY_SALESMAN

AS SELECT Ord_date,S.Salesman_id,Name,purchase_amt

FROM SALESMAN S,ORDERS O

WHERE S.Salesman_id=O.Salesman_id AND O.Purchase_amt=

(SELECT MAX(Purchase_amt)

                                                                                                  FROM ORDERS O2

                                                                                                WHERE O2.Ord_date=O.Ord_date);

5.  Demonstrate the DELETE operation by removing salesman with id 1000. All his orders must also be deleted.

DELETE

FROM SALESMAN

WHERE Salesman_id=1000;

Program 1 (QUERIES)

Queries (Program 1)

1. Retrieve details of all books in the library – id, title, name of publisher, authors, number of copies in each branch, etc.

SELECT B.Book_id, Title, Publisher_name, Author_name,Branch_id, No_of_copies

FROM BOOKB, BOOK_AUTHORS BA, BOOK_COPIES BC

WHERE B.Book_id=BA.Book_id AND B.Book_id=BC.Book_id;

2.  Get the particulars of borrowers who have borrowed more than 3 books, but from Jan 2017 to Jun 2017.

SELECT Card_no

FROM BOOK_LENDING

WHERE Date_out BETWEEN'01-Jan-2017'AND'30-Jun-2017'

GROUP BY Card_no

HAVING COUNT(*)>=3;

3. Delete a book in BOOK table. Update the contents of other tables to reflect this data manipulation operation.

DELETE

FROMBOOK

WHERE book_id=501;

4.  Partition the BOOK table based on year of publication. Demonstrate its working with a simple query.

SELECT count(Book_id), Pub_year

FROM BOOK

GROUP BY(Pub_year);

5.  Create a view of all books and its number of copies that are currently available in the Library.

CREATE VIEW BOOK_VIEW

ASSELECT B.Book_id,B.Title,LB.Branch_name,BC.No_of_copies

FROM BOOK B, BOOK_COPIES BC, LIBRARY_BRANCH LB

WHERE B.Book_id=BC.Book_id and BC.Branch_id=LB.Branch_id;

Program 5 (Create and Insert)

Creation of Tables

CREATE TABLE DEPARTMENT(Dno int PRIMARY KEY, Dname varchar(30), Mgrssn int,Mgrstartdate date );

CREATE TABLE EMPLOYEE(Ssn int PRIMARY KEY,Name varchar(15), Address varchar(15),Gender char,Salary number(8,2),Superssn int REFERENCES EMPLOYEE(Ssn),Dno int REFERENCES DEPARTMENT(Dno));

CREATE TABLE DLOCATION(Dno int REFERENCES DEPARTMENT(Dno),Dloc varchar(15),PRIMARY KEY(Dno,Dloc));

CREATE TABLE PROJECT(Pno int PRIMARY KEY,Pname varchar(15),Plocation varchar(15),Dno intREFERENCES DEPARTMENT(Dno));

CREATE TABLE WORKS_ON(Ssn int REFERENCES EMPLOYEE(Ssn), Pno int REFERENCES PROJECT(Pno),Hours int,PRIMARY KEY(Ssn,Pno));

Insertion of rows to the table

INSERT INTO DEPARTMENT VALUES('&Dno','&Dname',NULL,NULL);

INSERT  INTO  EMPLOYEE VALUES('&Ssn','&Name','&Address','&Gender','&Salary',NULL,'&Dno');

Before updating Superssn of EMPLOYEE Table:

SELECT * FROM EMPLOYEE;

Note1: Update Superssn of EMPLYOEE table

UPDATE EMPLOYEE SET Superssn=101 where Ssn in(102,103,104);

UPDATE EMPLOYEE SET Superssn=102 where Ssn in(105,106);

UPDATE EMPLOYEE SET Superssn=201 where Ssn=202;

UPDATE EMPLOYEE SET Superssn=301 where Ssn in(302,303,304,305,306);

After updating Superssn of EMPLOYEE table:

SELECT * FROM EMPLOYEE;

Note2: Alter the DEPARTMENT table to refer Mgrssn attribute to Ssn attribute of EMPLOYEE.

Alter table DEPARTMENT add constraint FR_KEY1 FOREIGN KEY(Mgrssn) REFERENCES EMPLOYEE(Ssn);

Before updating Mgrssn, Mgrstartdate of DEPARTMENT table.

SELECT * FROM DEPARTMENT;

Note3: Update DEPARTMENT table’s Mgrssn, Mgrstartdate attribute values.

UPDATE DEPARTMENT SET Mgrssn=101, Mgrstartdate='01-Feb-2014' where Dno=5;

UPDATE DEPARTMENT SET Mgrssn=201, Mgrstartdate='01-Jul-2010' where Dno=2;

UPDATE DEPARTMENT SET Mgrssn=301, Mgrstartdate='01-Jul-2010' where Dno=1;

After updating Mgrssn, Mgrstartdate of DEPARTMENT table.

SELECT * FROM DEPARTMENT;

INSERT INTO DLOCATION VALUES('&Dno','&Dloc');

INSERT INTO PROJECT VALUES('&Pno','&Pname','&Plocation','&Dno');

INSERT INTO WORKS_ON VALUES('&Ssn','&Pno','&Hours');