Get SQL Assignment 2 Query 's Answer

 Download SQL Assignment 2 Lab all Answer 


 password : smitguide


Download ans file



1)CREATE TABLE BOOK_DETAILS
(BCODE NUMBER(2) CONSTRAINT PK_BCODE PRIMARY KEY,
BNAME VARCHAR2(25) CONSTRAINT UN_BNAME UNIQUE,
AUTHER VARCHAR2(20),

PUBLISHER VARCHAR(20),
RELEASE_DATE DATE,
PRICE NUMBER(6,2) CONSTRAINT NN_PRICE NOT NULL,
DISCOUNT NUMBER(5,2),
CATEGORY_CODE NUMBER(4),
CONSTRAINT FK_C_CODE FOREIGN KEY(CATEGORY_CODE) REFERENCES BOOK_CATEGORY);

2)DESC BOOK_DETAILS;

3)SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION FROM USER_CONSTRAINTS
WHERE TABLE_NAME='BOOK_DETAILS'; 


4)INSERT INTO BOOK_DETAILS
VALUES(1,'INTERMEDIATE ENGLISH GRAMMER','RAYMOND MURPHY',NULL,'15-MAY-02',200,40,2);

INSERT INTO BOOK_DETAILS
VALUES(2,'Programming in ANSI C','BALAGURUSAMI','TMH','10-JAN-95',150,15,1);


ALTER TABLE BOOK_DETAILS
MODIFY(BNAME VARCHAR2(35));

INSERT INTO BOOK_DETAILS
VALUES(3,'PC Software for Windows','R K Taxali','TMH','01-JUN-95',250,40,1);

INSERT INTO BOOK_DETAILS
VALUES(4,'PC Software for Windows','R K Taxali','TMH','01-JUN-95',250,40,1);

INSERT INTO BOOK_DETAILS
VALUES(5,'JAVA complete reference','H Schildt','Prantice Hall','15-oct-97',425,75,1);

INSERT INTO BOOK_DETAILS
VALUES(6,'Differential Calculas','Shantinarayan',null,null,165,null,5);

INSERT INTO BOOK_DETAILS
VALUES(7,'Programming in C++','Kanitkar','bpb','10-oct-98',200,30,1);

ALTER TABLE BOOK_DETAILS
MODIFY(BNAME VARCHAR2(35));

INSERT INTO BOOK_DETAILS
VALUES(8,'Fundamentals of Computer Science','Balagurusamy','tmh','16-apr-06',200,null,1);

INSERT INTO BOOK_DETAILS
VALUES(9,'Fundamentals of ','Rob & Coronel','bpb','25-jan-05',410,80,1);

INSERT INTO BOOK_DETAILS
VALUES(10,'DHTML in action','Michele Petrovsky','pearson','05-jun -09',175,0,1);

5)select * from book_details;

6)SELECT BCODE,BNAME,PRICE
FROM BOOK_DETAILS
WHERE PRICE>300;

7)SELECT BNAME
FROM BOOK_DETAILS
WHERE PRICE<200 AND PRICE>400;

8)SELECT BCODE,BNAME,AUTHOR,PRICE
FROM BOOK_DETAILS
WHERE PRICE BETWEEN 150 AND 350;

9)SELECT * FROM BOOK_DETAILS
WHERE PRICE IN(175,200,250);

10)SELECT BCODE,BNAME,AUTHOR,PUBLISHER
FROM BOOK_DETAILS
WHERE PUBLISHER='TMH' OR PUBLISHER='BPB';

11)SELECT BNAME
FROM BOOK_DETAILS
WHERE BNAME NOT LIKE 'B%' AND BNAME NOT LIKE 'D%';

12)SELECT BNAME
FROM BOOK_DETAILS
WHERE BNAME LIKE 'P%' OR BNAME LIKE 'F%';


13)SELECT BCODE,BNAME,AUTHOR,PRICE
FROM BOOK_DETAILS
WHERE PRICE<270 AND AUTHOR='Balagurusamy';

14)SELECT * FROM BOOK_DETAILS
WHERE DISCOUNT IS NULL;

15)SELECT BCODE,BNAME,RELEASE_DATE
FROM BOOK_DETAILS
WHERE RELEASE_DATE IS NOT NULL;

16)SELECT BCODE,BNAME,AUTHOR,PUBLISHER
FROM BOOK_DETAILS
WHERE BNAME NOT LIKE 'F%' AND PUBLISHER='bpb';

17)SELECT *
FROM BOOK_DETAILS
WHERE PUBLISHER='bpb' OR PUBLISHER='TMH' AND PRICE>200;

18)CREATE TABLE COMPUTER_BOOKS(BCODE,BNAME,AUTHOR,PUBLISHER,CATEGORY_CODE)
AS SELECT BCODE,BNAME,AUTHOR,PUBLISHER,CATEGORY_CODE
FROM BOOK_DETAILS
WHERE CATEGORY_CODE=1;

19)ALTER TABLE COMPUTER_BOOKS RENAME COLUMN BCODE TO BOOK_CODE;

20)INSERT INTO COMPUTER_BOOKS(BOOK_CODE,BNAME,AUTHOR,PUBLISHER,CATEGORY_CODE)
SELECT  BCODE,BNAME,AUTHOR,PUBLISHER,CATEGORY_CODE FROM BOOK_DETAILS 
WHERE CATEGORY_CODE=5;

21) CREATE TABLE COMPUTER_BOOKS_NEW
AS SELECT * FROM BOOK_DETAILS
WHERE BCODE IS NULL;

22) INSERT INTO COMPUTER_BOOKS_NEW
SELECT  * FROM BOOK_DETAILS 
WHERE PUBLISHER='Prantice Hall' OR AUTHOR='Balagurusamy';

23) UPDATE COMPUTER_BOOKS_NEW SET PRICE=PRICE+25;

24) UPDATE COMPUTER_BOOKS SET PUBLISHER='PEARSON' WHERE AUTHOR='Balagurusamy';

25) DELETE FROM COMPUTER_BOOKS_NEW;

26) DELETE FROM COMPUTER_BOOKS WHERE PUBLISHER='TMH';

27) SELECT BNAME,AUTHOR,PRICE,DISCOUNT,PRICE-DISCOUNT AS "FINAL PRICE" FROM BOOK_DETAILS;

28) SELECT 'BOOK '||BNAME||' IS WRITTEN BY '||AUTHOR||' AND PUBLISHED BY '||PUBLISHER FROM BOOK_DETAILS;

29) SELECT BNAME,AUTHOR,PRICE,DISCOUNT,PRICE-(PRICE*20/100) AS "FINAL PRICE" FROM BOOK_DETAILS;

30) SELECT DISTINCT PUBLISHER FROM BOOK_DETAILS;

31) INSERT INTO COMPUTER_BOOKS(BOOK_CODE,BNAME,AUTHOR,PUBLISHER,CATEGORY_CODE)
SELECT  BCODE,BNAME,AUTHOR,PUBLISHER,CATEGORY_CODE FROM BOOK_DETAILS 
WHERE PUBLISHER='bpb';

32) SELECT DISTINCT * FROM COMPUTER_BOOKS;

33) SELECT BNAME FROM BOOK_DETAILS
ORDER BY BNAME;

34) SELECT BNAME,PRICE FROM BOOK_DETAILS
ORDER BY PRICE DESC ;