Assignment 3: Using Databases For each store, display the store’s address, city,
ID: 3738491 • Letter: A
Question
Assignment 3: Using Databases
For each store, display the store’s address, city, state, zip, the manager’s first and last name, and manger’s phone number
Display the names and prices of products with a price over $40, ordered so that the most expensive item is at the top and the least expensive item is at the bottom
Display the inventory for the Memphis store. Display the city, product, price and quantity. List each item alphabetically with the price and quantity in stock. If you cannot get the entire list in one screenshot that is OK – just take a screenshot that shows the first 10 items.
Display the name and price of all product with the word “water” anywhere in the product name.
Associate the employee created in #7 with the Tucson store. Start date is 3/21/2018. (No Screenshot needed)
You are changing management styles! Make the employee you created in #7 the manager for all stores in Mississippi (MS) – do this using one SQL command (No Screenshot needed)
For each store, display the address, city, state, zip, manager’s first and last name, and manger’s phone number (Do this AFTER steps 7-9)
How would i write these in Microsoft Access SQL?
Explanation / Answer
Answer#1
SELECT address, city, state, zip, f_name, l_name, phone FROM TableA UNION ALL
SELECT address, city, state, zip, f_name, l_name, phone FROM TableB UNION ALL
SELECT address, city, state, zip, f_name, l_name, phone FROM TableC UNION ALL
SELECT address, city, state, zip, f_name, l_name, phone FROM TableD;
(put correct column names as per the table. Replace TableA, TableB,.. with appropriate table names)
Answer#2
SELECT product, price, quantity, city from inventory ORDER BY product ASC;
Answer#3
SELECT product, price from inventory WHERE product LIKE 'water%';