CREATE TABLE BOOK ( BOOK_ID INT (10) PRIMARY KEY, TITLE VARCHAR (20), PUB_YEAR VARCHAR (20), PUBLISHER_NAME VARCHAR (20), FOREIGN KEY (PUBLISHER_NAME) REFERENCES PUBLISHER (NAME) ON DELETE CASCADE); CREATE TABLE BOOK_AUTHORS ( AUTHOR_NAME VARCHAR (20), BOOK_ID INT (10), PRIMARY KEY (BOOK_ID, AUTHOR_NAME), FOREIGN KEY (BOOK_ID) REFERENCES BOOK (BOOK_ID) ON DELETE CASCADE); CREATE TABLE PUBLISHER ( NAME VARCHAR (20) PRIMARY KEY, PHONE BIGINT (20), ADDRESS VARCHAR (100)); CREATE TABLE BOOK_COPIES ( NO_OF_COPIES INT (5), BOOK_ID INT (10), BRANCH _ID INT (10), PRIMARY KEY (BOOK_ID, BRANCH _ID), FOREIGN KEY (BOOK_ID) REFERENCES BOOK (BOOK_ID) ON DELETE CASCADE, FOREIGN KEY (BRANCH_ID) REFERENCES LIBRARY_ BRANCH (BRANCH _ID) ON DELETE CASCADE); CREATE TABLE BOOK_LENDING ( DATE_OUT DATE, DUE_DATE DATE, BOOK_ID INT (10), BRANCH_ID INT (10), CARD_NO INT (10), PRIMARY KEY (BOOK_ID,BRANCH_ID, CARD_NO), FOREIGN KEY (BOOK_ID) REFERENCES BOOK (BOOK_ID) ON DELETE CASCADE, FOREIGN KEY (BRANCH_ID) REFERENCES LIBRARY_BRANCH(BRANCH_ID) ON DELETE CASCADE, FOREIGN KEY (CARD_NO) REFERENCES CARD (CARD_NO) ON DELETE CASCADE); CREATE TABLE CARD (CARD_NO INT (10) PRIMARY KEY); CREATE TABLE LIBRARY_BRANCH ( BRANCH_ID INT (10) PRIMARY KEY, BRANCH_NAME VARCHAR (50), ADDRESS VARCHAR (100))

CREATE TABLE SALESMAN ( SALESMAN_ID INT (4) PRIMARY KEY, NAME VARCHAR (20), CITY VARCHAR (20), COMMISSION VARCHAR (20)); CREATE TABLE CUSTOMER ( CUSTOMER_ID INT (5) PRIMARY KEY, CUST_NAME VARCHAR (20), CITY VARCHAR (20), GRADE INT (4), SALESMAN_ID INT (6), FOREIGN KEY (SALESMAN_ID) REFERENCES SALESMAN (SALESMAN_ID) ON DELETE SET NULL); CREATE TABLE ORDERS ( ORD_NO INT (5) PRIMARY KEY, PURCHASE_AMT DECIMAL (10, 2), ORD_DATE DATE, CUSTOMER_ID INT (4), SALESMAN_ID INT (4), FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE, FOREIGN KEY (SALESMAN_ID) REFERENCES SALESMAN (SALESMAN_ID) ON DELETE CASCADE);

CREATE TABLE ACTOR ( ACT_ID INT (5) PRIMARY KEY, ACT_NAME VARCHAR (20), ACT_GENDER CHAR (1)); CREATE TABLE DIRECTOR ( DIR_ID INT (5) PRIMARY KEY, DIR_NAME VARCHAR (20), DIR_PHONE BIGINT); CREATE TABLE MOVIES (MOV_ID INT (4) PRIMARY KEY, MOV_TITLE VARCHAR (50), MOV_YEAR INT (4), MOV_LANG VARCHAR (20), DIR_ID INT (5), FOREIGN KEY (DIR_ID) REFERENCES DIRECTOR(DIR_ID)); CREATE TABLE MOVIES_CAST ( ACT_ID INT (5), MOV_ID INT (5), ROLE VARCHAR (20), 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 INT (5) PRIMARY KEY, REV_STARS VARCHAR (25), FOREIGN KEY (MOV_ID) REFERENCES MOVIES (MOV_ID));

SELECT MOV_TITLE FROM MOVIES M, DIRECTOR D WHERE M.DIR_ID = D.DIR_ID AND DIR_NAME = 'HITCHCOCK'; SELECT MOV_TITLE FROM MOVIES M, MOVIES_CAST MV WHERE M.MOV_ID = MV.MOV_ID AND ACT_ID IN (SELECT ACT_ID FROM MOVIES_CAST GROUP BY ACT_ID HAVING COUNT(ACT_ID) > 1) GROUP BY MOV_TITLE HAVING COUNT(*) > 1; SELECT ACT_NAME, MOV_TITLE, MOV_YEAR FROM ACTOR A JOIN MOVIE_CAST C ON A.ACT_ID = C.ACT_ID INNER JOIN MOVIES M ON C.MOV_ID = M.MOV_ID WHERE M.MOV_YEAR NOT BETWEEN 2000 AND 2015; FROM MOVIES M, RATING R WHERE M.MOV_ID = R.MOV_ID GROUP BY MOV_TITLE HAVING MAX(REV_STARS) > 0 ORDER BY MOV_TITLE; 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' ) );

SELECT GRADE, COUNT(CUSTOMER_ID) FROM CUSTOMER GROUP BY GRADE HAVING GRADE > (SELECT AVG(GRADE) FROM CUSTOMER WHERE CITY='BANGALORE'); SELECT GRADE, COUNT(CUSTOMER_ID) FROM CUSTOMER GROUP BY GRADE HAVING GRADE > (SELECT AVG(GRADE) FROM CUSTOMER WHERE CITY='BANGALORE'); SELECT S.SALESMAN_ID, NAME, CUST_NAME, COMMISSION FROM SALESMAN S, CUSTOMER C WHERE S.CITY = C.CITY UNION SELECT SALESMAN_ID, NAME, 'NO MATCH' AS CUST_NAME, COMMISSION FROM SALESMAN WHERE NOT CITY = ANY (SELECT CITY FROM CUSTOMER) ORDER BY 2 DESC; CREATE VIEW VW_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 ); SELECT * FROM VW_ELITSALESMAN; DELETE FROM SALESMAN WHERE SALESMAN_ID=101;

SELECT B.BOOK_ID, B.TITLE, B.PUBLISHER_NAME, A.AUTHOR_NAME, C.NO_OF_COPIES, L.PROGRAMME_ID FROM BOOK B, BOOK_AUTHORS A, BOOK_COPIES C, LIBRARY_BRANCH L WHERE B.BOOK_ID = A.BOOK_ID AND B.BOOK_ID = C.BOOK_ID AND L.BRANCH_ID = C.PROGRAMME_ID; SELECT CARD_NO FROM BOOK_LENDING WHERE DATE_OUT BETWEEN '2017-01-01' AND '2017-07-01' GROUP BY CARD_NO HAVING COUNT(*) > 3; DELETE FROM BOOK WHERE BOOK_ID = 3; CREATE VIEW VW_PUBLICATION AS SELECT PUB_YEAR FROM BOOK; CREATE VIEW VW_BOOKS AS SELECT B.BOOK_ID, B.TITLE, C.NO_OF_COPIES FROM BOOK B, BOOK_COPIES C, LIBRARY_BRANCH L WHERE B.BOOK_ID = C.BOOK_ID AND C.BRANCH_ID = L.BRANCH_ID; SELECT * FROM VW_BOOKS;