Chapter 2 Database questions
List all the information for each sale made to customer Stephen Liddle
Select * from simplifiedsales where Firstname = 'Stephen' and Lastname = 'Liddle'
Give all data for sales of more than $130.
Select * from simplifiedsales where price > 130
Show all sales information for sales equal to $140 and up to and including $160. Use Between.
Select * from simplifiedsales where price between 140.00 and 160.00
Show sales information for sneakers whose color is not Black or Blue.
SELECT * FROM SimplifiedSales WHERE Category = 'sneakers' and NOT (Color = 'Black' OR Color = 'Blue')
Are there any sales where the sales clerk forgot to enter the shoe size—in other words, where there is no data in the ItemSize field?
SELECT * FROM SimplifiedSales WHERE ItemSize is NULL
Show all sales information plus an additional column for sales tax for sales made on January first (of all years). Sales tax is Price times 6 percent. Name the new column SalesTax.
SELECT *, Price * 0.06 as SalesTax FROM SimplifiedSales WHERE DAY (SaleDate) = 1 and Month(SaleDate) = 1
Show all sales information plus two additional columns for sales made during January 2015. Name the first new column SalesTax and calculate it as Price times 6 percent. Name the second new column TotalAmt and make it the sum of Price and SalesTax. Sales tax is Price times 6 percent. Include sales made during January 2015.
SELECT *, Price * 0.06 as SalesTax, Price + Price*0.06 as TotalAmt FROM SimplifiedSalesWHERE Year (SaleDate) = 2015 and Month(SaleDate) = 1
List sales of shoes that have 'Yellow' as the first color listed in the color column.
SELECT *FROM SimplifiedSaleswhere color like 'Yellow%'
List categories of shoes, excluding sneakers, boots, and sandals. Use IN.
SELECT distinct categoryFROM SimplifiedSalesWHERE Category NOT IN ('sneakers', 'sandals', 'boots')
List Product name, Category, Manufacturer name, and price of shoes that have the word 'Leather' in the product name and that are priced at $100 or more. Be sure to eliminate duplicates.
SELECT distinct ProductName, Category, ManufacturerName, Price FROM SimplifiedSales WHERE ProductName like '%Leather%' and price >= 100
List Products (Product Name) and their colors of shoes that are yellow or pink or orange. Use IN. Be sure to eliminate duplicate records.
SELECT distinct ProductName, Color FROM SimplifiedSales WHERE Color IN ( 'Yellow', 'Pink', 'Orange')
What were the sales prices of the product named hampton boots that have been sold? (Product names are not capitalized in the database.) Return only the sales prices.
Select Price from simplifiedsales where ProductName = 'hampton boots'
Create an alphabetical list of names of all sold products whose price is more than $120. Return product names only.
Select distinct ProductName from SimplifiedSales where price > 120 order by ProductName
List the categories of shoes excluding sneakers and boots.
Select distinct category from SimplifiedSalesWhere Not (Category = 'sneakers' or category = 'boots')
What were the category, productname, color, and item size of products manufactured by Converse and sold in 2014?
Select distinct category, productname, color, itemsize from simplifiedsales where manufacturername = 'Converse' and year(SaleDate) = 2014
What are the colors of sold products whose price is more than $100? Show only colors.
Select distinct color from simplifiedsales where price > 100
What are the prices of blue sandals and green sandals that have been sold? Show the various prices by color sorted with blue sandals first and then by descending price. (Colors are capitalized in the database.) Show only colors and prices.
Select distinct color, price from simplifiedsales where category = 'sandals' and (color = 'blue' or color = 'green') order by color, price desc Keyword \"distinct' eliminates duplicates. Outside parentheses used to combine color test apart from category.
Which customers have purchased green sneakers (category) or products from Bearpaw? (Categories are not capitalized; manufacturer names are capitalized.) Show customer first and last names.
Select distinct firstname, lastname from simplifiedsales where (color = 'green' and category = 'sneakers') or ManufacturerName = 'Bearpaw'
What are the names (first and last) of customers who have made purchases in June or December of 2015?
Select distinct firstname, lastname from simplifiedsales where (month(SaleDate) = 6 or month(SaleDate) = 12) and year(SaleDate) = 2015 The keyword \"distinct\" eliminates duplicates. The outside parentheses around the dates are required so that both apply to the year.
Give first and last names of customers who have made purchases costing $120 or more but less than $130. Only show customer names. Do not use BETWEEN for this query.
Select distinct firstname, lastname from simplifiedsales where price >= 120 and price < 130
Give first and last names of customers who have made purchases costing $120 or more but less than $130. Only show customer names.
Select distinct firstname, lastname from simplifiedsales where price between 120.00 and 129.99
Give a list of customers' last and first names with the product names who have purchased boots priced $10 and under. (Category names are not capitalized.)
Select distinct lastname, firstname, productname from simplifiedsales where category = 'boots' and price <= 10
List the manufacturers of products with a category of sandals or slippers that sold in June of 2015. (Categories are not capitalized in the database.) Only show manufacturer names.
Select distinct manufacturername from simplifiedsales where (category = 'sandals' or category = 'slippers') and month(SaleDate) = 6 and Year(SaleDate) = 2015 The parentheses are required so that both sandals and slippers are in June of 2015. The keyword \"distinct\" eliminates duplicates.
What manufacturers have had products sold in June of 2015? Only show manufacturer names. NOTE: Use date functions of "month" and "year" for your query.
Select distinct manufacturername from simplifiedsales where month(SaleDate) = 6 and year(SaleDate) = 2015
List names of all sold products that are blue or green. (Colors are capitalized in the database, i.e., "Blue".) Return product names only.
Select distinct productname from SimplifiedSales where color = 'Blue' or color ='Green'
Which products were sold on the second of January of 2014? Only show product names.
Select distinct productname from simplifiedsales where SaleDate = '2014-1-2'
What are the product names of boots size 12 and above sold in 2014? (Category names are not capitalized.)
Select distinct productname from simplifiedsales where category = 'boots' and itemsize >= 12 and Year(SaleDate) = 2014
List all the sold products and their prices of the products that are red or orange and cost $60 or more. (Colors are capitalized in the database.) Show product names and prices.
Select distinct productname, price from simplifiedsales where (color = 'Red' or color = 'Orange') and price >= 60
Show all of the prices at which products manufactured by Keds have been sold. Sort in descending order by price. (Manufacturer names are capitalized in the database.) Only show product names and prices.
Select distinct productname, price from simplifiedsales where manufacturername = 'Keds' order by price desc
Give an alphabetical list of customers (first name then last name) who have purchased the product "mckinley boots" in 2014. Sort the list alphabetically by last name. (Product names are not capitalized in the database.) Return customer names only.
select distinct firstname, lastname from simplifiedsales where productname = 'mckinley boots' and year(saledate) = 2014 order by lastname
Give an alphabetical list of manufacturers who have had products sold from June 1, 2015 through June 30, 2015 inclusive. Only show manufacturer names. NOTE: Do NOT use date functions. Use date literals to test for the range of dates.
select distinct manufacturernamefrom SimplifiedSaleswhere saledate BETWEEN '2015-06-01' AND '2015-06-30' order by manufacturername select distinct manufacturernamefrom SimplifiedSaleswhere saledate >= '2015-06-01' AND saledate <= '2015-06-30' order by manufacturername The keyword \"distinct\" is required to eliminate duplicates.
