Task 1: Using SQL to select records a) Select all records and fie...

Task 1: Using SQL to select records
a) Select all records and fields from the Categories table and sort descending by CategoryName.

b) Select all records from the Categories table for only the CategoryName field.

c) Select all fields from the Employees table, only for those employees with a LastName that begins with one of the following letters: “A”, “B”, or “C”.

d) Select all fields from the Employees table, only for those employees where Notes includes the word “University” anywhere in the field.

e) Select the ProductName and Price fields from the Products table, only for those products where Price is less than $5 or more than $100.

f) Select the ProductName, Unit, and Price fields from the Products table, only for those products where ProductName starts with the letter “S” or “T” and the Price is more than $10.

g) Use a query to identify all unique cities in the Customer table. In other words, each city should only be in one record of your results.

Task 2: Use SQL to summarize records

a) The OrderDetails table gives the Quantity of each product within an order. Create a query that totals all records to find the overall average product quantity ordered, the minimum and maximum product quantities ordered, and the total number of records. Hint: Your results should be only one row of data. Use aliases in your query to give the results more descriptive names as shown below:

b) Use the OrderDetails table to determine the number of products ordered for each OrderID. For example, three products (ProductID’s 11, 42, and 72) were ordered in OrderID 10248. You should exclude ProductID 12 from your calculations. Use an alias in your query to give the results a more descriptive name as shown below:

c) Use the Customers table to determine the number of customers in each Country. Use criteria so that your query only returns those countries with more than 5 customers. Exclude Germany from your query results.

Task 3: Use SQL to select records from two tables

a) Use an inner join on the OrderDetails and Products tables so that your query returns the OrderID, ProductID, Quantity, and Price fields for all records.

b) Use an inner join on the Products and Suppliers tables so that your query returns the ProductName and SupplierName fields for all records. Sort your query results ascending by SupplierName, and then ProductName.

Task 4: Use SQL to summarize records from two tables

a) Use an inner join on the Products and Suppliers tables so that your query returns information about the number of products that each supplier sells. You should eliminate bottled and jarred products from your calculations (i.e., eliminate products that include the words “bottle” and/or “jar” anywhere in the Unit field). Also, keep only those records for suppliers that sell at least 3 products.

b) You’ve included a new field in a query that uses the OrderDetails table. The new field is called ProductOrderCost, which is calculated as Quantity * Price for each product in an order. Below I’ve given you the syntax and a screenshot of what the first few records of the query results look like.

Your task is to create a new totals query that returns the total order cost for each OrderID. The total order cost is the sum of ProductOrderCost for all products in each OrderID. Use an alias in your query to give your field a name of Total Order Cost and round it to the nearest integer. Keep only those records where the Total Order Cost is more than $4,500. Sort your results so that orders with larger costs are displayed first and smaller costs are displayed last. You can use the SQL code above as a starting point if you’d like.

Task 5: Challenge Problem - Use SQL to summarize records from many tables

a) Challenge Problem: You’ve been asked to calculate the Total Quantity and Total Order Cost (as defined in the previous problem) by Country of the customers who made the orders (i.e., you need one record for each Country). In your results, you only want to include orders for customers who are located in countries outside of the United States and have a Total Order Cost greater than $10,000.

To make your results easier to read, you should sort by the largest Total Order Cost first, and then by the largest Total Quantity. For the Total Order Cost, you should round to the nearest integer.

The challenging part of this question is that you will need to join together several tables to get the necessary information for your query. Rather than telling you exactly how to join the tables, I will leave it up to you to decide which tables are needed. Use inner joins for all necessary joins. You might find it helpful to sketch out the table relationships (with linking fields) on paper before writing your code.