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:

  1. Query for the following information:
    1. List all the customers from Ada, MI.
    2. List all the customers in which the sales representative is from another city.
    3. List all the orders for customer 124.
    4. List all the parts orders by customer 124.
    5. For each sales representative, identify the number of customers, total sales of the customers and the number of parts ordered by the customers.
  2. 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.
  3. 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.
  4. Get information from 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.
  5. Create a query using the IN keyword with a sub-query. State the purpose of the query; show the query and the output.
  6. 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.
  7. 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.


Related Questions in computer science category


Disclaimer
The ready solutions purchased from Library are already used solutions. Please do not submit them directly as it may lead to plagiarism. Once paid, the solution file download link will be sent to your provided email. Please either use them for learning purpose or re-write them in your own language. In case if you haven't get the email, do let us know via chat support.