Given the table information above, if you were asked to create an Access query that showed the
Question:
Given the table information above, if you were asked to create an Access query that showed the Contact Name and Country for all customers who placed an order with a quantity greater than 50, what tables would you need and how would you link them together? For the requested query above, what, if any, criteria would you use and on what field(s)? Suppose you wanted to change the query above to also only show freight amounts more than $100. What would you change? Given the table information above, create the following queries using SQL:
Query 1: Find all the product IDs and product names of products selling with a unit price of $18.00 or greater. Only show product ID and product name columns.
Query 2: Find all the product names of products with more than 20 units currently in stock selling with a unit price of more than $9.10 and less than $18.00.
Query 3: Find which customer IDs placed orders during the period of July 8 through August 8 of 1996. Show customer ID and order date.
Query 4: Find which supplier IDs carry products with "hockey" as part of the product name. Show the product name and supplier ID columns.
Query 5: Find one copy of each type of contact title in the customers table. Show contact title column.
Query 6: Find all the orders that have shipped to the United Kingdom. Show all columns.
Query 7: Find all the customers who are located in Portugal or in the cities of London, Madrid, Vancouver, or Paris. Show customer ID, city, and country. Query 8: Find out how many employees are located in the United States. This query should produce a single number, and the column head should be labeled “US Employees”.
Query 9: Using the products table, find the supplier ID for each supplier who supplies more than 2 products. Show supplier ID and the number of products supplied, labeled 'Number of Products.
Query 10: Calculate the total price for each completed order (in this case consider the price for each order line to be quantity multiplied by the price per unit). Show the order ID column and the total price column labeled as “Total Price”.
Query 11: Find the category names of products that the customer company named “Alfreds Futterkiste” ordered in March 1998. In this case, you do not know the ID of the customer, only the company name.
Query 12: Count how many employees report to each manager. Show the reports to the column and the number of managed employees in a column labeled “Managed Employees”.