database 数据库 sql代写 BSYS602 Business Data Management

BSYS602 Business Data Management

Workshop Individual Assignment 2

Use the sales schema below to complete your assignment. Before you start writing queries, you need to run the sql script file “Week7_assignment_SQL_script.sql” to create tables and generate data for those tables. Familiarize yourself with the database structure including tables, primary keys, foreign keys and relationships.

Save your queries in a file (Yourname_assign2.sql). Submit the file to the dropbox under “Workshop materials and assignments” link.
(Due date: midnight of Saturday, 1^st September 2018)

Write SQL commands for the following queries.

• List invoice number and total amount for each invoice. The result should be ordered by invoice number with appropriate headings. The partial output is shown below.

• List invoice number, line number, line units, product code, product description, and product quantity on hand for each row of data in the LINE table. The result should be ordered by invoice number and line number.

• List all product attributes from all those products in the LINE table that have line units greater than or equal to 3.

• Write a query to list each invoice and its associated details including invoice number, invoice date. For each invoice line item, display product description, line units and line price. The result should be ordered by invoice number followed by product description. The partial output is shown below.

• Write a query to display invoice records for each customer. The output should have appropriate headings and list customer code, customer first name, customer last name, invoice number and total amount from each invoice. The output should be sorted by customer code and invoice number. For example, customer code 10012 has one invoice (#1003). In that invoice, there are 3 lines:

In this case, the total amount for this customer can be derived from (1*38.95) + (1*39.95) + (5*14.99) = 153.85.

The partial output is shown below.

• Write a query to display the total amount from all invoices for customers whose first name begins with A or M. The output should show customer code, customer first name, customer last name and the total amount for each customer. The output should be displayed in an alphabetical order by customer first name similar to the one shown below.

• Write a query to display product code, quantity on hand, the number of products ordered, and adjusted quantity on hand (quantity on hand – the number of products ordered). The output should be sorted by product code. For example, the product code “13-02/P2” was in invoice 1001 (1 unit), invoice 1003 (5 units), invoice 1007 (2 units). So the number of products ordered is 8. Since its quantity on hand is 32, the adjusted quantity on hand is 24. The output should be similar to the one shown below.

• Write a query to display invoice number, line number, the line amount, and the difference between the line amount and the average line amount. The output should have appropriate headings and two decimal places for numbers. The partial output is shown below.

• Create your own SELECT statement that joins data from at least two tables using two conditions in the WHERE clause. The query must include the GROUP BY clause. The output must be appropriately formatted. Your query has to make logical and business sense.

• Create your own SELECT statement that uses one of the subqueries (i.e., WHERE, IN, HAVING or attribute list subqueries). The query must include the ORDER BY clause. The output must be appropriately formatted. Your query has to make logical and business sense.