project 3

Task 1: Use one and only one SQL Statement to get results for each of the following items.

Query #                                     Description

1.      List all customers showing the customer ID, customer name, and state, sorted by state, with customer names in alphabetical order within each state.

2.      List the Customer ID, Customer Name, Order ID, Order Date, and Total Order Amount (use alias) for all customers, sorted by Total Order amount (the highest amount first).

3.      List the name of the customer(s) who ordered the products that have the word “alarm” in it, together with the Product Name, Unit Price, Order Quantity, sorted by customer name in alphabetical order.

4.      List the ID, name and average unit cost of the product(s) that has more than one supplier, sorted by the number of suppliers in descending order (the highest number of suppliers first).

5.      List the ID and name of the product(s) that didn’t get ordered. Use a subquery.

Note: When asked to look up by the name (e.g. product name), don’t look up the id (e.g. productID) manually in their original table (e.g. Product_T) and then use it in your SQL – of course that’s easier but that’s cheating. Instead you should consider using joins and conditions to look up with given criteria.

What to include in your project report:

·        Provide the following in one page for each query in the order given above:

o   Query # AND Description (copy them directly from this requirement document). Screenshots without Query # and description will not be graded. Even if you don't have a solution to submit, still include the query# and description and leave the rest of the page blank.

o   A screenshot for each query, showing the SQL statement used and the results. Make sure that COMPLETE SQL statement and COMPLETE result set are visible in your screenshot(s). Make sure to capture the number of results returned in the bottom right corner.

o   You can take multiple screenshots if you have to in order to capture the entire result set, but make sure to crop each screenshot, put them together and labeled so they are readable as a whole. Missing or incomplete screenshots will result in deduction.

·        Make each screenshot is big enough to be readable.

·        In all screenshots, show your SQL server name and all table names in left explorer pane in Management Studio.