Scribd
Upload
24 views2 pages

Lab Using SQL To Complete BookManagement

The document outlines tasks for creating a database named 'BOOKMANAGER' and includes various SQL queries for reporting and data manipulation. It covers customer and book information retrieval, counting, and calculations based on specific conditions. Additionally, it specifies updates to book prices and stock levels, as well as deletion of unpurchased books with zero stock.

Uploaded by

Jane Anderson
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
24 views2 pages

Lab Using SQL To Complete BookManagement

The document outlines tasks for creating a database named 'BOOKMANAGER' and includes various SQL queries for reporting and data manipulation. It covers customer and book information retrieval, counting, and calculations based on specific conditions. Additionally, it specifies updates to book prices and stock levels, as well as deletion of unpurchased books with zero stock.

Uploaded by

Jane Anderson
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

1. Using tool (not SQL) to create a database “ BOOKMANAGER” ,includes some tables.

2. Write queries (use SQL) :


1. Create a report that includes all customers.
2. Create a report that includes some customers (condition: CUSTID from 1 to 4).
3. Create a report that has Referrer = "NULL".
4. Retrieve information of customers with a suburb of "Cobbity" or "Ultimo".
5. Retrieve CUSTID and Name of customers living at "Open Road" and with an undefined referrer.
6. Count the number of customers.
7. Retrieve information of books with stock less than 10.
8. Count books with stock from 5 to 10.
9. Calculate the sum of prices for books with the topic "web general".
10. Retrieve ISBN, Title, and Total, knowing that Total = stock * price from Book A.
11. Retrieve ISBN, Title, and Total for books with the topic "OS", knowing that Total = stock * price.
12. Retrieve ISBN and Title of books purchased by a customer named "Simon Smith".
13. Retrieve information of books that have not been purchased by anyone.
14. Calculate the sum of stock for books that have not been purchased.
15. Retrieve information of customers who purchased the book "Dot Con".
16. Retrieve SaleID of purchases made one week ago (datediff(d, buydate, getdate()) = 7).
17. Retrieve SaleID, Total, knowing that Total = quantity * price.
18. Retrieve sales information of customer "Michael Peters" made on July 23, 2004.
19. Calculate the total of all books purchased by customer "Michael Peters".
20. Retrieve Name of customer, Title of Book, and BuyDate where quantity = stock.
1. Increase the price by 20% for books with the author "Cassidy".
2. Decrease the stock by 10% for books with stock > 0 or title "The Internet".
3. Delete information of books that have not been purchased and have a stock of 0.

You might also like