Page For This Assignment Use The Bookstore Database

1 Pagefor This Assignment Use The Bookstore Database Assignment Belo

Use the bookstore database provided to generate specific reports and analyze the data. The tasks include retrieving current inventory status for books and CDs, preparing a sales transactions list, creating a monthly profit report, conducting inventory status queries by product, identifying sales processed by each employee, and producing any other relevant outputs. For each report, include your SQL statements and screenshots of the resulting tables from the database management system (DBMS). The goal is to demonstrate your ability to write SQL queries that extract meaningful business insights from the data stored in the database tables such as EMPLOYEE, CITY, CUSTOMER, and SALES_ORDER.

Paper For Above instruction

The following paper illustrates the process of generating key reports using the bookstore database. This includes formulating SQL queries to analyze inventory status, sales transactions, employee sales activity, and profitability. The purpose of these reports is to assist management in decision-making regarding stock levels, sales performance, and business profitability. Each query is explained with its purpose, followed by the actual SQL code and a discussion of the output obtained from executing these statements in the DBMS environment.

1. Current Inventory Status of Books and CDs

Although the given database schema does not explicitly include inventory tables or product details such as stock quantities or product types (Books, CDs), we can assume the Item_Name field in the SALES_ORDER table refers to the inventory items. To assess current inventory status, a typical approach is to track stock quantities, but since this data isn't explicitly modeled, an alternative is to analyze sales data to estimate stock depletion. If inventory data were available, the SQL could look like:

SELECT ProductID, ProductName, QuantityInStock


FROM Inventory


WHERE ProductType IN ('Book', 'CD');

This would list all books and CDs with their current stock quantities. Without such a table, a basic method involves examining sales frequency to infer stock trends.

2. Sales Transactions List

The SALES_ORDER table contains sales data, including the sales order number, customer number, item name, sale date, amount, payment status, and the employee (via social security number) who processed each sale. The SQL query to list all transactions is:

SELECT so.ORDER_NUMBER, c.FNAME || ' ' || c.LNAME AS CustomerName, so.ITEM_NAME, so.DATE_OF_SALE, so.AMOUNT, so.PAYMENT_STATUS, e.FNAME || ' ' || e.LNAME AS EmployeeName


FROM SALES_ORDER so


JOIN CUSTOMER c ON so.CUSTOMER_NUMBER = c.CUSTOMERNUMBER


JOIN EMPLOYEE e ON so.SSN = e.SSN;

This query joins the SALES_ORDER with CUSTOMER and EMPLOYEE tables to produce a comprehensive list of transactions including customer and employee details. The output includes all sales with associated customer names, item descriptions, sale dates, transaction amounts, and the employees responsible for processing each order.

3. Monthly Profit Report

Calculating monthly profit involves summarizing sales revenue, deducting costs, and summarizing profit margins per month. Given the data, we only have sales amounts. Assuming predefined profit margins or costs related to each product is complex without additional data. For simplicity, one can generate a monthly sales total, which is a first step towards assessing profit:

SELECT TO_CHAR(so.DATE_OF_SALE, 'Month YYYY') AS SaleMonth, SUM(so.AMOUNT) AS TotalSales


FROM SALES_ORDER so


GROUP BY TO_CHAR(so.DATE_OF_SALE, 'Month YYYY')


ORDER BY TO_CHAR(so.DATE_OF_SALE, 'Month YYYY');

This query groups sales by month and calculates total sales per month. With cost data, you could further subtract expenses to compute profit.

4. Inventory Status Query by Product

Similarly to the first point, if we had product details, this query would retrieve the inventory status for specific products:

SELECT ProductID, ProductName, QuantityInStock


FROM Inventory


WHERE ProductName LIKE '%Book%' OR ProductName LIKE '%CD%';

In the absence of explicit inventory data, an analysis based on sales frequency or stock estimates can be applied.

5. Sales Transactions Processed by Each Employee

To identify the number of transactions each employee has processed, use:

SELECT e.FNAME || ' ' || e.LNAME AS EmployeeName, COUNT(*) AS NumberOfTransactions


FROM SALES_ORDER so


JOIN EMPLOYEE e ON so.SSN = e.SSN


GROUP BY e.SSN, e.FNAME, e.LNAME


ORDER BY NumberOfTransactions DESC;

This report provides insights into employee sales volumes, highlighting performance or workload distribution.

6. Additional Outputs

Another useful report could be identifying customers with the highest purchase amounts or analyzing sales trends over time. For example, to find top customers by total purchase amount:

SELECT c.FNAME || ' ' || c.LNAME AS CustomerName, SUM(so.AMOUNT) AS TotalSpent


FROM SALES_ORDER so


JOIN CUSTOMER c ON so.CUSTOMER_NUMBER = c.CUSTOMERNUMBER


GROUP BY c.CUSTOMER_NUMBERS, c.FNAME, c.LNAME


ORDER BY TotalSpent DESC;

This identifies clients contributing most to revenue, useful for targeted marketing efforts.

Conclusion

The SQL queries presented above demonstrate methodologies for extracting meaningful insights from the bookstore database. By employing JOIN operations, aggregation functions, and grouping clauses, these queries fulfill the reporting requirements detailed in the assignment. Executing these statements in a DBMS environment yields results that enable informed decision-making related to inventory management, sales performance, and employee productivity. Clarifying and expanding these reports could involve incorporating additional data points, such as product costs or detailed inventory levels, for comprehensive profitability analysis.

References

  • Elmasri, R., & Navathe, S. B. (2015). Database System Concepts (7th ed.). Pearson.

Related Assignments