MIS 180 SQL Chapter 2-4

Give all data for sales of more than $130

Select * From SimplifiedSales Where Price > 130

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')

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 *, PriceASTERISK.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 *,PriceASTERISK.06 as SalesTax, Price + PriceASTERISK.06 as TotalAmt From SimplifiedSales Where Year(SaleDate) = 2015 and Month(SaleDate) = 1

What is the customerID for Sam Gill?

Select CustomerID From Customer Where firstname = 'Sam' And lastname = 'Gill'

Give Customer IDs and cities for customers who are located in Chicago, Saint Louis, Baltimore, or Newark. (Use the IN operator.)

Select CustomerID, City From Customer Where City IN ('Baltimore', 'Chicago', 'Saint Louis', 'Newark')

Give full names of customers that have a 'D' as the third character in their last names. Eliminate duplicates.

Select distinct firstname, lastname From Customer Where lastname like '__D%'

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.

Select distinct lastname, firstname from customer a join sale b on a.customerid = b.customerid join saleitem c on b.saleid = c.saleid join product d on d.productid = c.productid join manufacturer e on d.manufacturerid = e.manufacturerid where manufacturername = 'Keds' and d.color='black' order by lastname, firstname OR select distinct firstname, lastname from customer c join sale s on c.customerid = s.customerid join saleitem si on s.saleid = si.saleid join product p on si.productid = p.productid join manufacturer m on p.manufacturerid = m.manufacturerid where manufacturername = 'Keds' and color = 'black' order by lastname, firstname

List the data in the Salaried Employees table about Salaried Employees earning between $40000 and $50000. (use between)

Select * From SalaryEmployee Where Salary between 40000 and 50000

Give all the data for sales from January 21 of 2015 through the end of the month. (Use BETWEEN)

Select * From Sale Where SaleDate between '2015-01-21' and '2015-01-31'

Give the sales data for sales where sales tax was charged and total of tax and shipping is less than $15. (use the NOT operation.) Show all sales data.

Select * From Sale Where not tax = 0 And tax + shipping < 15

Give an alphabetical list of manufacturers who have had products sold in June of 2015? Only show manufacturer names.

SELECT Distinct ManufacturerName FROM simplifiedsales WHERE month(SaleDate) = 6 and year(saledate) = 2015 ORDER BY ManufacturerName

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

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

List Product name, Category, Manufacturerer name, and price of shoes that have the word 'Leather' in the product name and which is priced at $100 or more.

SELECT ProductName, Category, ManufacturerName, Price From SimplifiedSales Where ProductName like '%Leather%' and price >= 100

List the information of each sale item where 2 or more items were sold but only for sizes 5, 6, and 7 (Use the keyword IN.) Show all sale item information.

Select * From SaleItem Where Quantity >= 2 and ItemSize in (5,6,7)

List sales of shoes that have 'Yellow' as the first color listed in the color column.

Select * From SimplifiedSales Where Color like 'Yellow%'

List all the information for each sale made to customer Stephen Liddle.

Select * From SimplifiedSales Where Firstname = 'Stephen' And Lastname = 'Liddle'

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

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

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

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

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

What manufacturers have had products sold in June of 2015? Only show manufacturer names.

Select Distinct ManufacturerName From SimplifiedSales Where month(SaleDate) = 6 and Year(SaleDate) = 2015

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'

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 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 it 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 a list of customer's 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 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 = 'Green' Or Color = 'Blue')

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 Hourly (wage) Employees (first and last names), their hire dates and then the full name (first and last) of their manager. (Rename manager name columns as MgrFirstN, MgrLastN.)

Select E.FirstName, E.LastName, E.HireDate, M.FirstName as MgrFirstN, M.LastName as MgrLastN from Employee E join WageEmployee S on E.EmployeeID = S.EmployeeID join Employee M on E.ManagerID = M.employeeID OR select e.lastname, e.firstname, e.hiredate, m.firstname as MgrFirstN, m.lastname as MgrLastN from employee e join wageemployee w on e.employeeid = w.employeeid join employee m on e.managerid = m.employeeid

What are the first and last names and postal codes of customers living in California who made purchases in January 2015? (States appear in the database as standard abbreviations, e.g. Alaska appears as "AK"). Show customer first name, last name, and postal code.

Select FirstName, LastName, PostalCode From Customer C Join Sale S on C.CustomerID = S.CustomerID Where State = 'CA' And Month(SaleDate) = 1 And Year(SaleDate) = 2015

Make a list of all Manufacturers including ID, Name, Address1, Address2, City, State and PostalCode. Only include Manufacturers that have data in the Address2 field.

Select ManufacturerID, ManufacturerName, Address1, Address2, City, State, PostalCode From Manufacturer Where Address2 is Not Null

Give names of manufacturers and their cities that are located in cities with names starting with 'C'.

Select ManufacturerName, City From Manufacturer Where City like 'C%'

Give names of manufacturers and their cities that are located in cities with names ending with 'City'.

Select ManufacturerName, City From Manufacturer Where City like '%City'

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

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'

List Products (Product Name) and their colors of shoes that are yellow or pink or orange. Use IN.

Select ProductName, Color From SimplifiedSales Where Color In ('Yellow', 'Pink', 'Orange')

List the product name, category. and manufacturer name of sandals, sneakers, and casual shoes which are manufactured by 'Buyers Picks' or by 'Radii Footwear'.

Select ProductName, Category, ManufacturerName from Product P join Manufacturer M on P.ManufacturerID = M.ManufacturerID where Category IN ('sandals', 'sneakers', 'casual shoes') and (ManufacturerName = 'Buyers Picks' or ManufacturerName = 'Radii Footwear')

List the information in the SaleItem table concerning green sneakers. Use the first letter of each table name as alias names. (Note: Colors are capitalized while categories are not. ) Note: Your answer should dynamically include all current columns in the saleItem table and any future changes to columns.

Select S.* From SaleItem S Join Product P On S.ProductID = P.ProductID Where Category = 'sneakers' And Color = 'Green'

List all the July sales of white sneakers (color is white) made by Nike. Show all the Sales Item information plus the SaleDate.

Select SI.*, SaleDate From Sale S Join SaleItem SI On S.SaleID = SI.SaleId Join Product P On SI.ProductID = P.ProductID Join Manufacturer M on P.ManufacturerID = M.ManufacturerID where Month(SaleDate) = 7 and Category = 'sneakers' and Color = 'white' and ManufacturerName = 'Nike'

What are the sales dates and shipping amount of sales with over $55 of shipping charges?

Select SaleDate, Shipping From Sale Where Shipping > 55

Give date and the sum of tax and shipping for each sale from December 20th through December 25th of 2015. (use BETWEEN. Name the calculated column SUM)

Select SaleDate, Tax + Shipping as Sum From Sale Where SaleDate between '2015-12-20' and '2015-12-25'

List the information contained in the SaleItem table concerning the sales of slippers (whose category is slippers). Categories are not capitalized in the database. Note: Your answer should dynamically include all current columns and any future changes to columns.

Select SaleItem.* From SaleItem Join Product On SaleItem.ProductID = Product.ProductID Where Category = 'slippers'

List the information contained in the SaleItem table plus the product name and category concerning the sales of sneakers and the sales of casual shoes (categories) whose sale price is equal to $100. (Categories are not capitalized in the database.) Note: Your answer should dynamically include all current columns in SaleItem and any future changes to columns, plus the product name and category.

Select SaleItem.*, Productname, Category From SaleItem Join Product On SaleItem.ProductID = Product.ProductID Where (category = 'sneakers' Or Category = 'casual shoes') And SalePrice = 100

What is the total revenue attributable to each product category? Your query shouild produce two columns, one named category and one named revenue. There should only be one row for each category.

Select category, sum(price) as revenue from simplifiedsales group by category

What is the revenue attributable to each product category for 2014? Your query shouild produce two columns, one named category and one named revenue. There should only be one row for each category.

Select category, sum(price) as revenue from simplifiedsales where year(saledate)=2014 group by category

For 2014, What is the revenue attributable to each product category and how many pairs of shoes were sold in each category ? Your query shouild produce three columns, one named category, one named revenue, and one named sale_count. There should only be one row for each category.

Select category, sum(price) as revenue, count(*) as sale_count from simplifiedsales where year(saledate)=2014 group by category

How many pairs of boots were sold in 2015?. Your query should produce one column named boot_count with a single row showing the value.

Select count(*) as boot_count from simplifiedsales where category = 'boots'

How many records are in the simplifiedsales table? Your query should produce one column (named record_count) with one row showing the total number of records.

Select count(*) as record_count from simplifiedsales

List all information about customers who have bought products whose list price is over $150. Use aliases on table names. (For SaleItem use SI alias) Only show customer information.

Select distinct C.* From Customer C Join Sale S On C.CustomerID = S.CustomerID Join SaleItem SI On S.SaleID = SI.SaleID Join Product P On SI.ProductID = P.ProductID Where listprice > 150

Who are the customers (first and last names) that bought "casual shoes" from the manufacturer named Timberland? (Note category names are lower case, manufacturer names are capitalized in the database.)

Select distinct C.FirstName, C.LastName from Customer C join Sale S on C.CustomerID = S.CustomerID join SaleItem SI on S.SaleID = SI.SaleID join Product P on SI.ProductID = P.ProductID join Manufacturer M on P.ManufacturerID = M.ManufacturerID where Category = 'casual shoes' and ManufacturerName = 'Timberland'

What categories of shoes were have been sold whose SalePrice is over $100. Only show categories.

Select distinct Category From Product P Join SaleItem S On P.ProductID = S.ProductID Where SalePrice > 100

Which cities have manufacturers who make boots?

Select distinct City from Product P join Manufacturer M on P.ManufacturerID = M.ManufacturerID and Category = 'boots'

Considering only sales that happened in the month of January, List the cities and states of customers who have purchased boots made with leather (boots is a Category, leather is a word found in the Composition). Use LIKE. Sort by state. Only show cities and states.

Select distinct City, State From Customer C Join Sale S On C.CustomerID = S.CustomerID Join SaleItem SI On S.SaleID = SI.SaleID Join Product P On SI.ProductID = P.ProductID Where Category = 'boots' And Composition like '%leather%' And Month(SaleDate) = 1 Order by State

What are the names of customers who have bought products with a current list price of over $60 made by manufacturers in New Jersey? Show first and last names.

Select distinct FirstName, LastName from Customer C join Sale S on C.CustomerID = S.CustomerID join SaleItem SI on S.SaleID = SI.SaleID join Product P on SI.ProductID = P.ProductID join Manufacturer M on P.ManufacturerID = M.ManufacturerID where ListPrice > 60 and M.State = 'NJ'

Who are the customers from Illinois who have bought products with a list price of over $100 or who have bought flats? Show first and last name only.

Select distinct FirstName, LastName from Customer C join Sale S on S.CustomerID = C.CustomerID join SaleItem SI on SI.SaleID = S.SaleID join Product P on SI.ProductID = P.ProductID where State = 'IL' and ( ListPrice > 100 or Category = 'flats' )

List Names and States for each Customer who has bought products with sale prices between $130 and $150 inclusive. Only show customer first name, last name and states.

Select distinct FirstName, LastName, State From Customer C Join Sale S On C.CustomerID = S.CustomerID Join SaleItem SI On S.SaleID = SI.SaleID Where saleprice between 130 and 150

Who are all the customers in Washington who made purchases in 2015? Sort by LastName. Only show first and last names.

Select distinct FirstName, Lastname From Customer C Join Sale S on C.CustomerID = S.CustomerID Where State = 'WA' And Year(SaleDate) = 2015 Order by Lastname

What are the names of manufacturers of heels costing $50 or more? Only show manufacturer names.

Select distinct ManufacturerName From Manufacturer M Join Product P On M.ManufacturerID = P.ManufacturerID Where category = 'heels' And listprice >= 50

List names of manufacturers whose products have been purchased during February.

Select distinct ManufacturerName from Manufacturer M join Product P on M.ManufacturerID = P.ManufacturerID join SaleItem SI on P.ProductID = SI.ProductID join Sale S on S.SaleID = SI.SaleID where Month(SaleDate) = 2

Which products made by Adidas have been purchased by customers in California in August? Only show product names.

Select distinct ProductName from Product P join Manufacturer M on P.ManufacturerID = M.ManufacturerID join SaleItem SI on P.ProductID = SI.ProductID join Sale S on S.SaleID = SI.SaleID join Customer C on S.CustomerID = C.CustomerID where ManufacturerName = 'Adidas' and C.State = 'CA' and Month(SaleDate) = 8 OR (so it does not matter if which one is first after from) select distinct productname from manufacturer m join product p on p.manufacturerid = m.manufacturerid join saleitem si on p.productid = si.productid join sale s on si.saleid = s.saleid join customer c on s.customerid = c.customerid where manufacturername = 'Adidas' and c.state = 'CA' and month(saledate) = 8

What are the names of products from manufacturers in Washington state?

Select distinct ProductName from Product P join Manufacturer M on P.ManufacturerID = M.ManufacturerID where State = 'WA'

List the names of products purchased by customers from Montana, Utah, and Wyoming. Only show product names.

Select distinct ProductName from Product P join SaleItem SI on P.ProductID = SI.ProductID join Sale S on S.SaleID = SI.SaleID join Customer C on S.CustomerID = C.CustomerID where State in ('MT', 'UT', 'WY') OR (so apparently that order doesn't matter) select distinct productname from customer c join sale s on c.customerid = s.customerid join saleitem si on s.saleid = si.saleid join product p on si.productid = p.productid where state in ('MT', 'UT', 'WY')

What are the names and list prices of products that sold on Valentines Day 2014?

Select distinct ProductName, ListPrice from Product P join SaleItem SI on P.ProductID = SI.ProductId join Sale S on S.SaleID = SI.SaleID where SaleDate = '2014-02-14'

What are the names of products, their list prices, and their manufacturer name that have a list price under $50 and are made by manufacturers in Phoenix?

Select distinct ProductName, ListPrice, ManufacturerName from Product P join Manufacturer M on P.ManufacturerID = M.ManufacturerID where ListPrice < 50 and City = 'Phoenix'

List the states of customers who have purchases in January of 2015. Only show states and sort them alphabetically.

Select distinct State From Sale S Join Customer C On S.CustomerID = C.CustomerID Where Month(SaleDate) = 1 And Year(SaleDate) = 2015 Order by State

List the categories of shoes excluding sneakers and boots.

Select distinct category From SimplifiedSales Where Not (Category = 'sneakers' or Category = 'boots')

List the categories and colors of shoes manufactured by Puma. Sort the results by categoyr then by color. Show only category and color.

Select distinct category, color from Manufacturer M join Product P on M.ManufacturerID = P.ManufacturerID where ManufacturerName = 'Puma' order by color

Give a list of employees (as Emp_FirstName, Emp_LastName) and their hire dates (as Emp_HireDate) and their Managers (as Mgr_FirstName, Mgr_LastName). Also include the managers hiredates (as Mgr_HireDate) Name the columns as indicated. Only show names and hire dates.

Select e.firstname as emp_firstname, e.lastname as emp_lastname, e.hiredate as emp_hiredate, m.firstname as mgr_firstname, m.lastname as mgr_lastname, m.hiredate as mgr_hiredate from employee e join employee m on e.managerid = m.employeeid The E and M copies of Employee are joined by setting the EmployeeID in M equal to the ManagerID in E. Thus, each row in E has attached to it the row from M containing the information about the E row's manager. By selecting the Employee names from each row, we obtain the required list of employees paired with their managers.

What is the first and last name of customer 610?

Select firstname, lastname From Customer Where CustomerID = 610

Create an alphabetical list of names (first and last, alphabetized by last) of customers in Minnesota.

Select firstname, lastname From Customer Where State = 'MN' Order by lastname

List Employee names (first and last) of employees who manage themselves or have a null value as the manager id.

Select firstname, lastname From Employee Where EmployeeID = ManagerID Or ManagerID is NULL

What is the weekly pay of wage employees? Weekly pay is defiend as the Wage times the MaxHours. Show three columns: FirstName, LastName and WeeklyPay. Sort the results by WeeklyPay in descending order.

Select firstname, lastname, maxhours, wage, maxhours * wage as WeeklyPay from employee a join wageemployee b on a.employeeid = b.employeeid order by weeklypay desc OR Select firstname, lastname, maxhours, wage, maxhours * wage as WeeklyPay from employee e join wageemployee w on e.employeeid = w.employeeid order by weeklypay desc

What is the full name and phone number of employee 170?

Select firstname, lastname, phone From Employee Where employeeId = 170

List Salaried employees whos salary is greater than $40,000. Show Employee first name, last name, hiredate and salary. Sort the data by hire date.

Select firstname, lastname, hiredate, Salary From Employee E Join SalaryEmployee S on S.EmployeeID = E.EmployeeID Where Salary > 40000 Order By hiredate

For each manufacturer, what is the price of most expensive and least expensive shoes sold?

Select manufacturerName, max(price) as max_price, min(price) as min_price from simplifiedsales group by manufacturername

For the manufacturers named Nike, Adidas, Fila, Puma, or Converse, what is the highest and lowest prices of shoes in each category? Your query shoudl prodcued four columns named as follows: ManufacturerName, Categoty, max_price, min_price.

Select manufacturername, category, max(price) as max_price ,min(price) as min_price from simplifiedsales where manufacturername in ('Nike','Adidas','Fila','Puma','Converse') group by manufacturername, category

For each product manuractured by Fila, what is the largest shoe sold? Your query should have two columns, one named ProductName and one named max_size.

Select productname, max(itemsize) as max_size from simplifiedsales where manufacturername='fila' group by productname

What is the revenue recorded for sales of sneakers? Your query should produce one column named sneaker_revenue with a single row showing the value.

Select sum(price) as sneaker_revenue from simplifiedsales where category = 'sneakers'

What is the total revene recorded in the SimplifiedSales table? Total revenue is the sum of the price column. Your query should produce one column named total_revenue with one row showing the value.

Select sum(price) as total_revenue from simplifiedsales

List categories of shoes, excluding sneakers, boots and sandal. Use IN.

select distinct category From SimplifiedSales Where Category NOT IN ('sneakers', 'sandals', 'boots')

List the first and last names of customers who have purchased shoes manufacturered by "Cadillac Footwear". Sort the result by last name.

select distinct firstname, lastname from customer a join sale b on a.customerid = b.customerid join saleitem c on b.saleid = c.saleid join product d on d.productid = c.productid join manufacturer e on d.manufacturerid = e.manufacturerid where manufacturername = 'Cadillac Footwear' order by lastname OR select distinct firstname, lastname from customer c join sale s on c.customerid = s.customerid join saleitem si on s.saleid = si.saleid join product p on si.productid = p.productid join manufacturer m on p.manufacturerid = m.manufacturerid where manufacturername = 'Cadillac Footwear' order by lastname