Aggregate Function
computer science
Description
In this Assigmment, you will continue to use the data from the Week 2 Assignment to create a database.
To complete this Assignment:
- Query for the following information:
- List all the customers from Ada, MI.
- List all the customers in which the sales representative is from another city.
- List all the orders for customer 124.
- List all the parts orders by customer 124.
- For each sales representative, identify the number of customers, total sales of the customers and the number of parts ordered by the customers.
- Query one table and use WHERE to filter the results. The SELECT clause should have a column list, not an asterisk (*). State the purpose of the query; show the query and the output.
- Get information from at least 3 tables in one statement, and provide the output using the Join operator. Use ANSI Join syntax. State the purpose of the query; show the query and the output. Add a screen shot of SS Management Studio showing the query and results.
- Get information from 2 tables in one statement, and provide the output using the Left Outer Join operator. State the purpose of the query; show the query and the output. The outer join should be designed to retrieve information from the left table that has no matches in the right table. If that is not possible for your database, explain why.
- Create a query using the IN keyword with a sub-query. State the purpose of the query; show the query and the output.
- Create a query using an aggregate function (i.e., min, max, avg, sum, count) and the GROUP BY command. State the purpose of the query; show the query and the output.
- Create a query using an aggregate function (i.e., min, max, avg, sum, count) and the GROUP BY command using the HAVING clause to filter the aggregate results. State the purpose of the query; show the query and the output.
