Description
1. Current inventory status of the books and CDs
SQL Query:
SELECT PRODUCT_NAME, CURRENT_INVENTORY
FROM INVENTORY_DATA;
Screen Shot:
2. Sales Transactions List
SQL Query:
SELECT *
FROM SALES_DATA;
Screen Shot:
3. Monthly Profit Report
SQL Query:
SELECT to_char(sal.SALE_DATE,’MM-YYYY’), SUM(sal.UNITS_SOLD * (100-inv.COST_AS_PER_OF_PRICE) * inv.UNIT_PRICE) AS PROFIT
FROM SALES_DATA sal JOIN INVENTORY_DATA inv
ON (sal.PRODUCT_ID = inv.PRODUCT_ID)
GROUP BY to_char(sal.SALE_DATE,’MM-YYYY’);
Screen Shot:
4. Inventory Status Query by Product
SQL Query:
SELECT inv.PRODUCT_ID, inv.CURRENT_INVENTORY AS Inventory_Status
FROM INVENTORY_DATA inv;
Screen Shot:
5. Sales Transaction that each employee has processed
SQL Query:
SELECT sal.CASHIER, SUM(inv.UNIT_PRICE) AS Total_Sale
FROM SALES_DATA sal JOIN INVENTORY_DATA inv
ON (sal.PRODUCT_ID = inv.PRODUCT_ID)
GROUP BYsal.CASHIER;
Screen Shot:
Developing a Database System: Query the Tables
Database Queries and Reports
For the same bookstore database, generate reports for the output specified in SLP 1:
1. Current inventory status of the books and CDs
2. Sales Transactions List
3. Monthly Profit Report
4. Inventory Status Query by Product
5. Sales Transaction that each employee has processed
6. Any other output you think appropriate
Write a 2- to 5-page paper that includes your SQL statements and screenshots of the result tables in DBMS.
SLP Assignment Expectations
1. Use SQL properly for data manipulation, mainly querying the tables to obtain required information.
2. Communicate effectively with your audience.
