Answer to Question #255979 in Databases | SQL | Oracle | MS Access for RaselNeel

Question

a) Write SQL commands

 1. To create the above table structure with proper constraints.

 2. Select all the PROG type published by BPB from Library. 

 3. Display a list of all books with Price more than 130 and sorted by Qty.

 4. Display all the books sorted by Price in Ascending Order. 

 5. Display a report. Listing Book No. current value and misplacement charges for each book in the above 

table. Calculate the misplacement charges for all books Price*1.25

 6. Count the number of books published by PHI 

 7. Insert a new book in the library. 

 8. Count the no of books in each Type. 

 9. Add one more attribute “No_of_copies” in the above table. 

 10. Update the new attribute value with 2 for each book.

 11. Count the total number of Publishers.

Answer 1

a) Write SQL commands

 1. To create the above table structure with proper constraints.

ANSWER.

 CREATE TABLE Library(

  No int NOT NULL UNIQUE,

  Title varchar(255) NOT NULL,

  Author varchar(255) NOT NULL,,

  Type varchar(255) NOT NULL,,

  Pub varchar(255) NOT NULL,,

  Qty int NOT NULL,

  Price int NOT NULL,

 );

 2. Select all the PROG type published by BPB from Library. 

  ANSWER.

select * from Library

where type = ‘PROG’ and Pub = ‘BPB’ ;

 3. Display a list of all books with Price more than 130 and sorted by Qty.

  ANSWER.

select * from Library

where Price >130

order by Qty;

 4. Display all the books sorted by Price in Ascending Order.

   ANSWER.

select * from library

order by price ASC ; 

 5. Display a report. Listing Book No. current value and misplacement charges for each book in the above 

 table. Calculate the misplacement charges for all books Price*1.25

ANSWER.

SELECT No, Price, Price*1.25 FROM Library; 

 6. Count the number of books published by PHI 

ANSWER.

SELECT COUNT(No) FROM Library WHERE Pub = 'PHI';

 7. Insert a new book in the library. 

ANSWER.

INSERT INTO Library (Title, Author, Type, Pub, Qty, Price)

VALUES ('Squid game', 'Professor. Erichsen', 'PROG', 'PHI', 7, 315);

 8. Count the no of books in each Type. 

ANSWER.

SELECT Type,

    COUNT(*) AS `num`

    FROM Library

    GROUP BY Type

 9. Add one more attribute “No_of_copies” in the above table. 

ANSWER.

ALTER TABLE Library

ADD No_of_copies varchar(255);

 10. Update the new attribute value with 2 for each book.

    ANSWER.

UPDATE Customers

SET No_of_copies = 2

 11. Count the total number of Publishers.

  ANSWER.

SELECT Pub,

    COUNT(*) AS `num`

    FROM Library

    GROUP BY Pub