Urgent 1

Scenario

Design a database system using ER diagrams and normalization. Create the database in a Relational Database Management System using SQL and create queries to retrieve data from the database.

Module 6: Complex queries

Objectives

· To create complex queries

· Use aggregate functions

· Create subqueries

· Create a view

Steps

The structure and contents of the database are shown in the following figure. Use this database to answer all questions in this course project.

Write a query to count the number of invoices.

Problem 2

Generate a listing of all purchases made by the customers, using the output shown in the following as your guide. Sort the results by customer code, invoice number, and product description. You will need to join INVOICE, LINE, PRODUCT

Problem 3

Create a query to produce the total purchase per invoice, generating the results shown in the following Figure, sorted by invoice number. The invoice total is the sum of the product purchases in the LINE that corresponds to the INVOICE. 

Problem 4

List the balances of customers who have made purchases during the current invoice cycle—that is, for the customers who appear in the INVOICE table. Sort the results by customer code, as shown in the following Figure. Note: you will need to use the DISTINCT keyword and join the INVOICE and CUSTOMER tables

Problem 5

Create a query to find the balance characteristics for all customers (ie sum, min, max, and average). The results of this query are shown in the following Figure.

Problem 6

Using the output shown in the following Figure as your guide, generate a list of customer purchases, including the subtotals for each of the invoice line numbers. 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 certain to use the column aliases as shown in the figure. You will need to join INVOICE, LINE, PRODUCT

Problem 7

Write a query to display the customer code, balance, and total purchases for each customer. Total purchase is calculated by summing the line subtotals (as calculated in Problem 4) for each customer. Sort the results by customer code, and use aliases as shown in the following Figure. You will need to use the code SUM(LINE_UNITS * LINE_PRICE) and will need to GROUP BY CUSTOMER.CUS_CODE, CUS_BALANCE. You will also need to join CUSTOMER, INVOICE, and LINE

Problem 8

Find the listing of customers who did not make purchases during the invoicing period. Sort the results by customer code. Your output must match the output shown in the following Figure. Use a subquery to retrieve the cus_code from invoice. Then select all customers not in that subquery.

Problem 9

Find the customer balance summary for all customers who have not made purchases during the current invoicing period. The results are shown in the following Figure.

Problem 10

Create a view named PRODUCT_INVENTORY that summarizes the value of products currently in inventory. Note that the value of each product is a result of multiplying the units currently in inventory by the unit price. Sort the results in descending order by subtotal, as shown in the following Figure. To display all values in the view, use the code: SELECT * FROM PRODUCT_INVENTORY;

Problem 11

Write a query using a left join to join all records from the vendor table and those that match from the product table.

Problem 12

Write a query using subquery to list InvoiceNumber, InvoiceDate for those products with price bigger than 100.

Deliverables

· Complete the Course Project Deliverable with screenshots

To submit your project, copy each query and take screen print of the each running output, pasting both into a single word document. Here is an example:

SELECT *

FROM EMPLOYEE

WHERE JOB_CODE > 500;

Refer to the grading rubric below to ensure you incorporate the essential elements into your project.

MIS582 Course Project Grading Rubric