Assignment Paper

1. Display the customer number, customer name, order number, and order date for each order. Sort the results by customer number.
2. Display the customer number, customer name, order number, and order date for all orders. Include all customers in the results. For customers that do not have orders, omit the order number and order date.
3. Create a view named TOYS that consists of the item number, description, units on hand, and unit price of each item in category TOY.
4. List customer number, customer name, rep name, rep number, and Number_Ordered.
5a. List item_num, description, total_value (derive it by multiplying num_ordered and quoted_price for each line, and then take total with sum. You will need to join order_line and item tables and employ group by command in your query.
5b. Now make the same report, but utilize Price from Item table for the total_value calculations (optional).

Note: We can add two types of variables on a SELECT statement. 1) Column_Names from tables, and if multiple tables are involved we use table_name.column_name to refer to them, e.g., PC.Emp_Number, 2) Column functions that may enclose column_names. Though Count(*) is also a column function without a column_name. Now the rules. When we display only column functions on a select statement, group by statement is not needed. But, when we have a mix of regular columns and column functions, we must use group by, and we must group by all the columns that are not inside column functions. The group by command must list the regular columns in the same sequence as they appear on a select statement. It’s better to first list the regular columns, and then the column functions on a select statement to minimize confusion.

Please complete following questions in the query, then put your queries and outputs in a word document to upload your work.
1. The TOYS view is no longer necessary, please try to delete it. Confirm the removal of TOYS view by using select view_name from user_view
2. For each order, list the order number and order date along with the number and name of the customer that placed the order.
3. Display item number and description for items that have not been ordered.
4. Display the customer number, customer name, order number, and order date for each order. Sort the results by order date by descending sequence.
5. For each order line, what is the amount of discount, and percent discount given. Discount is based on difference between price and quoted_price. Report order number, order date, discount amount, and discount percent. Percentage of discount is based on difference_ in_ price/price*100
6. For each order report order number, order date, and discount percent.