SQL Single Table Queries For each information request below, formulate a single
ID: 3753585 • Letter: S
Question
SQL Single Table Queries
For each information request below, formulate a single SQL query to produce the required information. Read the problems carefully and in each case, you should display only the columns requested. Be sure that your queries do not produce duplicate records unless otherwise directed.
The interface below may not remember queries you have written if you have to leave the page for any reason before you submit, so be sure to keep a copy of your queries in case you need to enter them again.
1. Show all of the fields for all of the products in the database.
2. Create a list showing every customer's last name, city, and state. Sort the results by state alphabetically.
3. Create a list showing which colors of shoes are in the database. Show each color only once. Sort the colors alphabetically. Hint: Your query results should only have one column: Color.
4. On which SaleID and dates did we sell products between sizes 10-12. Use the BETWEEN keyword. Sort the results by date from most recent to oldest. Hint: This query should have two columns, SaleID and Saledate.
5. Show the product name and manufacturer ID for all pink heels. Hint: Remember that all colors have their first letter capitalized. Hint: Remember that all categories are all lowercase.
6. What ProductIDs were sold in December 2014? Show each productID only once. Hint: Your query should only have one column: ProductID.
7. What is the price rounded to the nearest dollar of all boots in the database? Show product name, color, and rounded price (give this column an alias of RoundedPrice). Hint: Remember that all categories are all lowercase.
8. Show the name, city, state, phone number, and first two digits of the postal code for manufacturers in Oregon (OR) and Washington (WA). Give the last column an alias of ZipPrefix.
9.. Show the name, color, and ManufacturerID for all sneakers that DO have a color listed.
10. Show the last name, state, and phone number for customers with customerIDs between 17700 and 17800. In the phone number column, change all of the dashes to periods. Give this column an alias of PhoneFormatted. Hint: Your query results should have 3 columns: LastName, State, and PhoneFormatted.
Explanation / Answer
If you have any doubts, please give me comment...
-- 1)
SELECT * FROM Products;
-- 2)
SELECT CustLname, city, state
FROM Customer
ORDER BY state;
-- 3)
SELECT DISTINCT Color
FROM Shoes;
-- 4)
SELECT SaleID, SaleDate
FROM Sales
WHERE size BETWEEN 10 AND 12
ORDER BY SaleDate DESC;
-- 5)
SELECT productName, manufacturerID
FROM Products
WHERE color = 'Pink';
-- 6)
SELECT DISTINCT ProductID
FROM Sales
WHERE SaleDate LIKE '2014-12%';
-- 7)
SELECT productName, color, ROUND(price) RoundedPrice
FROM Products;
-- 8)
SELECT name, city, state, phoneNumber, SUBSTR(postalCode, 0,2) ZipPrefix
FROM Manufacturers
WHERE state IN ('OR', 'WA');
-- 9)
SELECT productName, color, manufacturerID
FROM Product
WHERE productName = 'sneakers';
-- 10)
SELECT LastName, State, CONCAT(SUBSTR(phoneNumber, 0,3), "-", SUBSTR(phoneNumber, 3,6), "-", SUBSTR(phoneNumber, 7)) AS PhoneFormatted
FROM Customers
WHERE CustomerID BETWEEN 17700 AND 17800;
Note: Provide schema for better results...
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.