My SQL project
First, make or download a new copy of the original database. You can download it from the link on the Assignments page. Then use SQL to create, run, and save with the specified name each of the 15 queries described below. Submit your database with all 15 examples to the link on the Week 6 Assignments page with your name and the word Final as the filename (e.g. JonesFinal.accdb).
(The number in the parenthesis is how many rows you should get, except for #7 where it is the answer.)
|
1. Produce a sorted list of the customer cities in ascending order. Include the cust_city, cust_name, and the cust_email columns. Save this query as 1-CustomerCitiesSorted. |
(8) |
|
2. Produce a list from the orderitems table where the quantity is greater than or equal to 25. Include all columns. Name it 2‑Quantity25OrMore. |
(9) |
|
3. Produce a list of customers who are not in CA, IL, MA, NY, or FL. Save it as 3-CustomersNotIn5States. |
(3) |
|
4. Produce a list of products where the letters “sea” occur anywhere within their name. Save it as 4-ContainingSEA. |
(2) |
|
5. Prices will be going up 7%. Create a list of all product names and current prices, along with what will be the new prices. (Hint: To get a price that is 7% higher, multiply the existing price by 1.07.) Name the calculated column “NewPrice.” Save the query as 5‑NewPrices. Extra Credit : For 2 extra points, format the NewPrice column with dollar signs, commas, and 2 decimal places. |
(15) |
|
6. Use the DAY( ) function to get a list of all orders that were placed on the first day of any month. Include all fields. Save it as 6‑Day1Orders. |
(4) |
|
7. Obtain the average quantity of items ordered from the orderitems table. Save it as 7-AverageQuantity. |
(44.235) |
|
8. Count the number of vendors grouped by country. Name the column containing the count “CountryCount.” Save it as 8‑VendorCountryCount. |
(1, 1, 4) |
|
9. Produce a list of the products that do not have the word “inch” anywhere in their description. Include the name and description fields. Save the query as9-NotContainingInch. |
(4) |
|
10. Join vendors and products to get a list of all products supplied by USA only vendors. Include both names, the price, and the country. Sort by the vendor name and then the product name. Save the query as 10-USAVendorProducts. |
(11) |
|
11. Join customers, orders, orderitems, and products. List the customer name and city, the order number and order date, the product name, and the quantity and item price for all ordered items. Save the query as 11-4TableJoin. |
(17) |
|
12. Create an outer join with the customers and orders tables to list all customers whether they have placed any orders or not. Include the name and order number columns and sort by customer name. Save this query as 12-AllCustomersWithOrWithoutOrders. |
(9) |
|
13. Create a UNION query to list in a single result the name of every city in the customers and vendors tables in alphabetical order. Also include the state, zip, and country fields. Save the query as 13‑AllCities. |
(14) |
|
14. Make a list of customers for whom there is no contact name. Include the customer name, contact name, and email fields. Save the query as14‑NoContactName. |
(5) |
|
15. Produce a list of order items where the vendor and customer are from the same country (Figure 1). Include the six fields shown. Save the query as 15-CustomerSameCountryAsVendor. |
(7) |