search

bm9avan / data

0 stars 0 forks source link

a1 #10

Open raj605006 opened 9 months ago

raj605006 commented 9 months ago

BOOK(Book_id, Title, Publisher_Name, Pub_Year) BOOK_AUTHORS(Book_id, Author_Name) PUBLISHER(Name, Address, Phone) BOOK_COPIES(Book_id, Branch_id, No_of_Copies) BOOK_LENDING(Book_id, Branch_id, Card_No, Date_Out, Due_Date) LIBRARY_BRANCH(Branch_id, Branch_Name, Address) CREATE TABLE PUBLISHER (NAME VARCHAR2 (20), PHONE INTEGER, ADDRESS VARCHAR2 (20), CONSTRAINT PKP PRIMARY KEY(NAME)); CREATE TABLE BOOK (BOOK_ID VARCHAR(8), TITLE VARCHAR2 (20), PUBLISHER_NAME VARCHAR(20), PUB_YEAR INTEGER, CONSTRAINT PKB PRIMARY KEY(BOOK_ID), CONSTRAINT FKB FOREIGN KEY(PUBLISHER_NAME) REFERENCES PUBLISHER(NAME)); CREATE TABLE BOOK_AUTHORS (BOOK_ID VARCHAR(8), AUTHOR_NAME VARCHAR2 (20), CONSTRAINT PKBA PRIMARY KEY (BOOK_ID,AUTHOR_NAME), CONSTRAINT FKBA FOREIGN KEY(BOOK_ID) REFERENCES BOOK(BOOK_ID)ON DELETE CASCADE); CREATE TABLE LIBRARY_BRANCH (BRANCH_ID VARCHAR(6), BRANCH_NAME VARCHAR2 (20), ADDRESS VARCHAR2 (20), CONSTRAINT PKLB PRIMARY KEY(BRANCH_ID)); CREATE TABLE BOOK_COPIES (BOOK_ID VARCHAR(8), BRANCH_ID VARCHAR2(6), NO_OF_COPIES INTEGER, CONSTRAINT PKBC PRIMARY KEY(BOOK_ID, BRANCH_ID), CONSTRAINT FKBC FOREIGN KEY(BOOK_ID) REFERENCES BOOK(BOOK_ID)ON DELETE CASCADE, CONSTRAINT FKBB FOREIGN KEY(BRANCH_ID) REFERENCES LIBRARY_BRANCH(BRANCH_ID)); CREATE TABLE BOOK_LENDING (BOOK_ID VARCHAR(8), BRANCH_ID VARCHAR2(6), CARD_NO INTEGER, DATE_OUT DATE, DUE_DATE DATE, CONSTRAINT PKBL PRIMARY KEY(BOOK_ID, BRANCH_ID,CARD_NO), CONSTRAINT FKBL FOREIGN KEY(BOOK_ID) REFERENCES BOOK(BOOK_ID)ON DELETE CASCADE); 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'); SQL> SELECT * FROM PUBLISHER; NAME PHONE ADDRESS

MCGRAW-HILL 9989076587 BANGALORE PEARSON 9889076565 NEWDELHI RANDOM HOUSE 7455679345 HYDRABAD HACHETTE LIVRE 8970862340 CHENAI GRUPO PLANETA 7756120238 BANGALORE INSERT INTO BOOK VALUES ('1','DBMS', 'MCGRAW-HILL',2017); INSERT INTO BOOK VALUES ('2','ADBMS', 'MCGRAW-HILL',2016); INSERT INTO BOOK VALUES ('3','CN', 'PEARSON',2016); INSERT INTO BOOK VALUES ('4','CG', 'GRUPO PLANETA',2015); INSERT INTO BOOK VALUES ('5','OS', 'PEARSON',2016); SQL> SELECT * FROM BOOK;

INSERT INTO BOOK_AUTHORS VALUES ('1','NAVATHE'); INSERT INTO BOOK_AUTHORS VALUES ('2','NAVATHE'); INSERT INTO BOOK_AUTHORS VALUES ('3','TANENBAUM'); INSERT INTO BOOK_AUTHORS VALUES ('4','EDWARD ANGEL'); INSERT INTO BOOK_AUTHORS VALUES ('5','GALVIN'); SQL> SELECT * FROM BOOK_AUTHORS ; BOOK_ID AUTHOR_NAME

1 NAVATHE 2 NAVATHE 3 TANENBAUM 4 EDWARD ANGEL 5 GALVIN INSERT INTO LIBRARY_BRANCH VALUES ('10','VV PURAM','BANGALORE'); INSERT INTO LIBRARY_BRANCH VALUES ('11','BIT','BANGALORE'); INSERT INTO LIBRARY_BRANCH VALUES ('12','RAJAJI NAGAR', 'BANGALORE'); INSERT INTO LIBRARY_BRANCH VALUES ('13','JP NAGAR','BANGALORE'); INSERT INTO LIBRARY_BRANCH VALUES ('14','JAYANAGAR','BANGALORE'); SQL> SELECT * FROM LIBRARY_BRANCH; BRANCH BRANCH_NAME ADDRESS

10 VV PURAM BANGALORE 11 BIT BANGALORE 12 RAJAJI NAGAR BANGALORE 13 JP NAGAR BANGALORE 14 JAYANAGAR BANGALORE INSERT INTO BOOK_COPIES VALUES ( '1','10', 10); INSERT INTO BOOK_COPIES VALUES ( '1','11', 5); INSERT INTO BOOK_COPIES VALUES ( '2','12', 2); INSERT INTO BOOK_COPIES VALUES ( '2','13', 5); INSERT INTO BOOK_COPIES VALUES ( '3','14', 7); INSERT INTO BOOK_COPIES VALUES ( '5','10', 1); INSERT INTO BOOK_COPIES VALUES ( '4','11', 3); SQL> SELECT * FROM BOOK_COPIES;

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

SQL> SELECT FROM BOOK_LENDING; SELECT B.BOOK_ID, B.TITLE, B.PUBLISHER_NAME, A.AUTHOR_NAME, C.NO_OF_COPIES, L.BRANCH_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.BRANCH_ID; SELECT CARD_NO FROM BOOK_LENDING WHERE DATE_OUT BETWEEN '01-JAN-2017' AND '01-JUL-2017' GROUP BY CARD_NO HAVING COUNT ()>3; SQL> SELECT FROM BOOK; SQL> SELECT FROM BOOK_COPIES;

CREATE TABLE BOOKPART PARTITION BY RANGE (PUB_YEAR) (PARTITION P1 VALUES LESS THAN(2016), PARTITION P2 VALUES LESS THAN (MAXVALUE)) AS SELECT * FROM BOOK;

CREATE VIEW BC AS SELECT B.BOOK_ID,C.TITLE,B.BRANCH_ID, (B.NO_OF_COPIES-(SELECT COUNT(*) FROM BOOK_LENDING WHERE B.BOOK_ID=BOOK_ID AND B.BRANCH_ID=BRANCH_ID)) AS NO_COPY FROM BOOK_COPIES B,BOOK C WHERE B.BOOK_ID=C.BOOK_ID;

raj605006 commented 9 months ago

SALESMAN(Salesman_id, Name, City, Commission) CUSTOMER(Customer_id, Cust_Name, City, Grade, Salesman_id) ORDERS(Ord_No, Purchase_Amt, Ord_Date, Customer_id, Salesman_id) CREATE TABLE SALESMAN(SALESMAN_ID VARCHAR(8), NAME VARCHAR(20), CITY VARCHAR(20), COMMISSION VARCHAR2(10), CONSTRAINT PKS PRIMARY KEY(SALESMAN_ID)); CREATE TABLE CUSTOMER(CUSTOMER_ID VARCHAR(8), CUST_NAME VARCHAR2 (20), CITY VARCHAR2 (20), GRADE NUMBER (3), SALESMAN_ID VARCHAR(8), CONSTRAINT PKC PRIMARY KEY(CUSTOMER_ID), CONSTRAINT FKC FOREIGN KEY(SALESMAN_ID) REFERENCES SALESMAN(SALESMAN_ID) ON DELETE SET NULL); CREATE TABLE ORDERS (ORD_NO VARCHAR(8), PURCHASE_AMT NUMBER(10, 2), ORD_DATE DATE, CUSTOMER_ID VARCHAR(8), SALESMAN_ID VARCHAR(8), CONSTRAINT PKO PRIMARY KEY (ORD_NO), CONSTRAINT FKOC FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE, CONSTRAINT FKOS FOREIGN KEY (SALESMAN_ID) REFERENCES SALESMAN (SALESMAN_ID) ON DELETE CASCADE); 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 ('C1', 'PREETHI','BANGALORE', 100, '1000'); INSERT INTO CUSTOMER VALUES ('C2', 'VIVEK','MANGALORE', 300, '1000'); INSERT INTO CUSTOMER VALUES ('C3', 'BHASKAR','CHENNAI', 400, '2000'); INSERT INTO CUSTOMER VALUES ('C4', 'CHETHAN','BANGALORE', 200, '2000'); INSERT INTO CUSTOMER VALUES ('C5', 'MAMATHA','BANGALORE', 400, '3000'); INSERT INTO ORDERS VALUES ('O1', 5000, '04-MAY-17', 'C1', '1000'); INSERT INTO ORDERS VALUES ('O2', 6000, '04-MAY-17', 'C1', '1000'); INSERT INTO ORDERS VALUES ('O3', 7000, '04-MAY-17', 'C2', '1000'); INSERT INTO ORDERS VALUES ('O4', 450, '20-JAN-17', 'C1', '2000'); INSERT INTO ORDERS VALUES ('O5', 1000, '24-FEB-17', 'C2','2000'); INSERT INTO ORDERS VALUES ('O6', 3500, '13-APR-17', 'C3', '3000'); INSERT INTO ORDERS VALUES ('O7', 550, '09-MAR-17', 'C4', 2000); INSERT INTO ORDERS VALUES ('O8', 6500, '04-MAY-17', 'C5', 1000); INSERT INTO ORDERS VALUES ('O9', 7500, '09-MAR-17', 'C2', 2000); SELECT * FROM SALESMAN;

SELECT * FROM CUSTOMER;

SELECT * FROM ORDERS;

SELECT GRADE, COUNT (DISTINCT CUSTOMER_ID) AS NO_OF_CUSTOMER FROM CUSTOMER GROUP BY GRADE HAVING GRADE > (SELECT AVG(GRADE) FROM CUSTOMER WHERE CITY='BANGALORE');

SELECT SALESMAN_ID, NAME FROM SALESMAN S WHERE ((SELECT COUNT (*) FROM CUSTOMER WHERE SALESMAN_ID=S.SALESMAN_ID)>1);

SELECT S.SALESMAN_ID, S.CITY FROM SALESMAN S WHERE EXISTS (SELECT CITY FROM CUSTOMER WHERE S.CITY=CITY AND S.SALESMAN_ID=SALESMAN_ID) UNION SELECT SALESMAN_ID,'NO MATCH OF CITIES' FROM SALESMAN S WHERE NOT EXISTS (SELECT CITY FROM CUSTOMER WHERE S.CITY=CITY AND S.SALESMAN_ID=SALESMAN_ID)

SELECT DISTINCT S.SALESMAN_ID,S.ORD_DATE FROM ORDERS S WHERE (SELECT SUM(PURCHASE_AMT) FROM ORDERS WHERE SALESMAN_ID=S.SALESMAN_ID AND ORD_DATE=S.ORD_DATE AND S.CUSTOMER_ID=CUSTOMER_ID) =(SELECT MAX(SUM(PURCHASE_AMT)) FROM ORDERS S1 WHERE S1.ORD_DATE=S.ORD_DATE GROUP BY S1.ORD_DATE,S1.SALESMAN_ID,S1.CUSTOMER_ID);

DELETE FROM SALESMAN WHERE SALESMAN_ID=1000; SQL> SELECT FROM SALESMAN; SQL> SELECT FROM CUSTOMER; SQL> SELECT * FROM ORDERS;

raj605006 commented 9 months ago

ACTOR (Act_id, Act_Name, Act_Gender) DIRECTOR (Dir_id, Dir_Name, Dir_Phone) MOVIES (Mov_id, Mov_Title, Mov_Year, Mov_Lang, Dir_id) MOVIE_CAST (Act_id, Mov_id, Role) RATING (Mov_id, Rev_Stars) CREATE TABLE ACTOR (ACT_ID NUMBER (3), ACT_NAME VARCHAR (20), ACT_GENDER CHAR (1), CONSTRAINT PKAC PRIMARY KEY(ACT_ID)); CREATE TABLE DIRECTOR( DIR_ID NUMBER (3), DIR_NAME VARCHAR (20), DIR_PHONE NUMBER (10), CONSTRAINT PKDI 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), CONSTRAINT PKMV PRIMARY KEY(MOV_ID), CONSTRAINT FKMV FOREIGN KEY(DIR_ID) REFERENCES DIRECTOR(DIR_ID)); CREATE TABLE MOVIE_CAST ( ACT_ID NUMBER (3), MOV_ID NUMBER (4), ROLE VARCHAR (10), CONSTRAINT PKMC PRIMARY KEY(ACT_ID, MOV_ID), CONSTRAINT FKMC FOREIGN KEY(ACT_ID) REFERENCES ACTOR(ACT_ID), CONSTRAINT FKMCC FOREIGN KEY(MOV_ID) REFERENCES MOVIES(MOV_ID)); CREATE TABLE RATING ( MOV_ID NUMBER (4), REV_STARS INTEGER, CONSTRAINT FKRA FOREIGN KEY(MOV_ID) REFERENCES MOVIES(MOV_ID)); INSERT INTO ACTOR VALUES (301,'ANUSHKA','F'); INSERT INTO ACTOR VALUES (302,'PRABHAS','M'); INSERT INTO ACTOR VALUES (303,'ARAVIND','M'); INSERT INTO ACTOR VALUES (304,'JERMY','M'); INSERT INTO ACTOR VALUES (305,'KIM NEWMEN','M'); SQL> SELECT * FROM ACTOR;

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 DIRECTOR VALUES (64,'MAHESH', 8989776539); SQL> SELECT * FROM DIRECTOR;

SQL> SELECT * FROM MOVIES;

INSERT INTO MOVIE_CAST VALUES (301, 1002, 'HEROINE'); INSERT INTO MOVIE_CAST VALUES (301, 1001, 'HEROINE'); INSERT INTO MOVIE_CAST VALUES (303, 1005, 'HERO'); INSERT INTO MOVIE_CAST VALUES (302, 1002, 'HERO'); INSERT INTO MOVIE_CAST VALUES (302, 1001, 'HERO'); INSERT INTO MOVIE_CAST VALUES (304, 1004, 'HERO'); INSERT INTO MOVIE_CAST VALUES (305, 1005, 'HERO'); INSERT INTO MOVIE_CAST VALUES (305, 1007, 'HERO'); SQL> SELECT * FROM MOVIE_CAST;

INSERT INTO RATING VALUES (1001, 4); INSERT INTO RATING VALUES (1002, 2); INSERT INTO RATING VALUES (1003, 5); INSERT INTO RATING VALUES (1004, 4); INSERT INTO RATING VALUES (1005, 3); INSERT INTO RATING VALUES (1006, 8); INSERT INTO RATING VALUES (1007, 0); INSERT INTO RATING VALUES (1001, 2); INSERT INTO RATING VALUES (1002, 5); SQL> SELECT * FROM RATING;

SELECT M.MOV_TITLE FROM MOVIES M,DIRECTOR D WHERE M.DIR_ID=D.DIR_ID AND D.DIR_NAME = 'HITCHCOCK';

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;

SELECT A.ACT_NAME 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;

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;

UPDATE RATING SET REV_STARS=5 WHERE MOV_ID IN (SELECT M.MOV_ID FROM MOVIES M,DIRECTOR D WHERE M.DIR_ID=D.DIR_ID AND D.DIR_NAME = 'STEVEN SPIELBERG');