Manpreet Sandhu
MS AIT 524
Database Management Systems
M4_Assignment_BasicSQL
1. Display a list of all data contained in the BOOKS table.
Select * from Books;
Select Sysdate, ‘Manpreet Sandhu’ from Dual;
Manpreet Sandhu
MS AIT 524
Database Management Systems
2. List the title only of all books available in inventory, using the
BOOKS table.
Select Title from Books;
Select Sysdate, ‘Manpreet Sandhu’ from Dual;
Manpreet Sandhu
MS AIT 524
Database Management Systems
3. List the title and publication date for each book in the BOOKS
table. Use the column heading “Publication Date” for the Pubdate
field.
Select Title, PubDate as “Publication Date” from Books;
Select Sysdate, ‘Manpreet Sandhu’ from Dual;
Manpreet Sandhu
MS AIT 524
Database Management Systems
4. List the customer number for each customer in the
CUSTOMERS table, along with the city and state in which the
customer lives.
Select Customer#, City, State from Customers;
Select Sysdate, ‘Manpreet Sandhu’ from Dual;
Manpreet Sandhu
MS AIT 524
Database Management Systems
5. Create a list containing the publisher’s name, the person
usually contacted, and the publisher’s telephone number.
Rename the contact column “Contact Person” in the displayed
results. (Hint: Use the PUBLISHER table.)
Select Name, Contact as “Contact Person”, Phone from
Publisher;
Select Sysdate, ‘Manpreet Sandhu’ from Dual;
Manpreet Sandhu
MS AIT 524
Database Management Systems
6. Determine which categories are represented in the current
book inventory. List each category only once.
Select Unique Category from Books;
Select Sysdate, ‘Manpreet Sandhu’ from Dual;
Manpreet Sandhu
MS AIT 524
Database Management Systems
7. List the customer number from the ORDERS table for each
customer who has placed an order with the bookstore. List each
customer number only once.
Select Unique Customer# from Orders;
Select Sysdate, ‘Manpreet Sandhu’ from Dual;
Manpreet Sandhu
MS AIT 524
Database Management Systems
8. Create a list of each book title stored in the BOOKS table and
the category in which each book belongs. Reverse the sequence
of the columns so that the category of each book is listed first.
Select Category, Title from Books;
Select Sysdate, ‘Manpreet Sandhu’ from Dual;
Manpreet Sandhu
MS AIT 524
Database Management Systems
9. Create a list of authors that displays the last name followed by
the first name for each author. The last names and first names
should be separated by a comma and a blank space.
Select Concat(Concat(LName, ‘ ‘), FName) as Author from
Author;
Select Sysdate, ‘Manpreet Sandhu’ from Dual;
Manpreet Sandhu
MS AIT 524
Database Management Systems
10. List all information for each order item. Include an item total,
which can be calculated by multiplying the Quantity and Paideach
columns. Use a column alias for the calculated value to show the
heading “Item Total” in the output.
Select Sysdate, ‘Manpreet Sandhu’ from Dual;
Select Order#, Item#, ISBN, Quantty, PaidEach, Quantty * PaidEach
as ItemTotal from OrderItems;
