¡Database Management Systems Post University

CIS420 – Database Management Systems Unit 5 Lab Due Date: 11:59 pm EST, Sunday of Unit 5 Points: 100 Overview: All the operations you can do with data follow the CRUD acronym. CRUD is the four primary functions we perform when we query a database: Create, Read, Update, and Delete. We CREATE information in the database, READ/Retrieve that information from the database, UPDATE/manipulate it, and if we want, we can DELETE it. In Lab 5, you will work with SQL queries. Instructions: Q1: Generate a listing of all purchases made by the customers, using the output shown in Figure 5.1 as your guide. Sort the results by customer code, invoice number, and product description. Figure 5.1. List of Customer Purchases © 2022 Post University, Waterbury, CT ALL RIGHTS RESERVED Q2: Using the output shown in Figure 5.2 as your guide, generate a list of customer purchases, including the subtotals for each invoice line number. The subtotal is a derived attribute calculated by multiplying LINE_UNITS by LINE_PRICE. Sort the output by customer code, invoice number, and product description. Be sure to use the column aliases as shown in the figure. Figure 5.2. Summary of Customer Purchases with Subtotals Q3: Write a query to display each customer’s code, balance, and total purchases. The total purchase is calculated by summing each customer’s line subtotals (as calculated in Problem 12). Sort the results by customer code, and use aliases as shown in Figure 5.3. Figure 5.3. Customer Purchase Summary Q4: Modify the query in Problem 13 to include the number of individual product purchases made by each customer. (In other words, if the customer’s invoice is based on three products, one per LINE_NUMBER, you count three product purchases. In the © 2022 Post University, Waterbury, CT ALL RIGHTS RESERVED original invoice data, customer 10011 generated three invoices, which contained six lines, each representing a product purchase.) Your output values must match those shown in Figure 5.4, sorted by customer code. Figure 5.4. Customer Total Purchase Amounts and Number of Purchases Q5: Use a query to compute the total of all purchases, the number, and the average purchase amount made by each customer. Your output values must match those shown in Figure 5.5. Sort the results by customer code. Figure 5.5. Average Purchase Amount by Customer Q6: Create a query to produce the total purchase per invoice, generating the results shown in Figure 5.6, sorted by invoice number. The invoice total is the sum of the product purchases in the LINE that corresponds to the INVOICE. © 2022 Post University, Waterbury, CT ALL RIGHTS RESERVED Figure 5.6. Invoice Totals Q7: Use a query to show the invoices and invoice totals in Figure 5.7. Sort the results by customer code and then by invoice number. Figure 5.7. Invoice Totals by Customer Q8: Write a query to produce the number of invoices and the total purchase amounts by the customer, using the output shown in Figure 5.8 as your guide. Note the results are sorted by customer code. (Compare this summary to the results shown in 7.) Figure 5.8 Number of Invoices and Total Purchase Amounts by Customer © 2022 Post University, Waterbury, CT ALL RIGHTS RESERVED Q9: Write a query to generate the total number of invoices, the invoice total for all invoices, the smallest of the customer purchase amounts, the largest of the customer purchase amounts, and the average of all the customer purchase amounts. Your output must match Figure 5.9. Figure 5.9. Number of Invoices, Invoice Totals, Minimum, Maximum, and Average Sales Q10: List the balances of customers who have made purchases during the current invoice cycle—for those who appear in the INVOICE table. The results of this query are shown in Figure 5.10, sorted by customer code. Figure 5.10. Balances for Customers Who Made Purchases Requirements: • Submit a Word document. Be sure to read the criteria below by which your work will be evaluated before you write and again after you write. © 2022 Post University, Waterbury, CT ALL RIGHTS RESERVED Evaluation Rubric for Unit 5 Lab CRITERIA Accuracy Completeness Deficient 0 – 44 points 0-69% accurately answers the questions. 0 – 14 points Lab is not very complete. Answering a few questions. © 2022 Post University, Waterbury, CT ALL RIGHTS RESERVED Needs Improvement 45 – 59 points 60-79% accurately answers some questions. 15 – 19 points Lab is somewhat complete. Answering some (at least 70%) questions. Proficient Exemplary 60 – 74 points 80-99% accurately answers most questions. 75 points 100% accurate accurately answers all questions. 20 – 24 points Lab is mostly complete. Answering most (at least 90%) questions. 25 points Lab is complete. Answering all (100%) questions.