1. List products (names only) with their categories and manufacturer name of san
ID: 3747353 • Letter: 1
Question
1. List products (names only) with their categories and manufacturer name of sandals, sneakers, and casual shoes which are manufactured by 'Buyers Picks' or by 'Radii Footwear'.
2. List without duplicates the cities and states of customers who have purchased boots in January. Sort by state. Only show cities and states.
3. List the names of products purchased by customers from Montana, Utah, and Wyoming. Only show product names.
4. List all information about customers who have bought products whose list price is over $150. Use aliases on table names. Only show customer information.
5. List Names and States for each Customer who has bought products with list prices between $130 and $150 inclusive. Only show customer first name, last name and states.
6. List names of manufacurers whose products have been purchased during February.
7. List all the July sales of white sneakers (color='White') made by Nike. Use alias names. (Colors are capitalized in the database.) Show only sales information, but show all sales columns.
8. Give an alphabetical list of customers (last name first) who have purchased black shoes made by Keds. Sort on full name, with last name being the primary sort. Only show customer names.
9. List the states of manufacturers who have made sneakers purchased by customers living in Kansas. Use alias names. Only show states.
10. Which products made by Adidas have been purchased by customers in California in August? Only show product names.
2.4s Join Queries on Tables 2 For each information request below, formulate a single SQL query to produce the required information. In each case, you should display only the columns requested. Be sure that your queries do not produce duplicate records unless otherwise directed A description of the database schema used for this assignment is shown below 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 Customer CustomerID FirstName LastName StreetAddress Sale SaleID SaleDate CustomerlD ProductID ItemSize Quantity SalePrice Product ProductID ProductName ManufacturerlD Category Color Price Description Manufacturer ManufacturerlD ManufacturerName Address State PostalCode Phone State PostalCode Country PhoneExplanation / Answer
from Product p, Manufacturer m
where (m.ManufacturerName = ‘Buyers Picks’ OR m.ManufacturerName = ‘Radii Footwear’) AND (p.Category = ‘sandals’ OR p.Category = ‘casual shoes’ OR p.Category = ‘sneakers’);
2. Select DISTINCT c.City, c.State from Customer c, Sale s
where (c .CustomerID = s .CustomerID) AND (s. SaleDate = ‘January’)
ORDER BY c.State;
3. Select DISTINCT p.ProductName from Product p, Customer c, Sale s
where c.City = ‘Montana’ OR c.City = ‘Utah’ OR c.City = ‘Wyoming’;
4. Select DISTINCT c.* from Customer c, Product p, Sale s
where (c.CustomerID = s.CustomerID) AND (s.ProductID = p.ProductID) AND p.Price > 150;
5. Select DISTINCT c.FirstName, c.LastName, c.State from Customer c, Product p, Sale s
where (c.CustomerID = s.CustomerID) AND (s.ProductID = p.ProductID) AND p.Price > 130 AND p.Price < 150 ;
6. Select DISTINCT m.ManufacturerName from Sale s, Product p, Manufacturer m
where (s.ProductID = p.ProductID) AND (p.ManufacturerID = m.ManufacturerID) AND
(s. SaleDate = ‘February’);
7. Select s.* from Sale s, Product p, Manufacturer m
where (s.ProductID = p.ProductID) AND (p.ManufacturerID = m.ManufacturerID) AND
(s. SaleDate = ‘July’) AND p.color = ‘WHITE’ AND p.Category = ‘Sneakers’ (m.ManufacturerName = ‘Nike’);
8. Select DISTINCT LastName, FirstName from Customer
where p.color = ‘BLACK’ AND p.Category = ‘shoes’ AND (m.ManufacturerName = ‘Keds’)
ORDER BY LastName ASC;
9. Select DISTINCT m.State from Manufacturer m, Customer c, Product p
where (p.ManufacturerID = m.ManufacturerID) AND c.City = ‘Kansas’ AND p.Category = ‘Sneakers’;
10. Select DISTINCT p.ProductName from Sale s, Product p, Manufacturer m
where (m.ManufacturerName = ‘Adidas’) AND c.State = ‘California’;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.