Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

MIS 2113 My Educator HW#1 -- Essentials of SQL

memorize.ai (lvl 286)

Section 1

Preview this deck

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

Front

1 / 10

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 1, 2020

Cards (10)

Section 1

(10 cards)

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

Front

SELECT Count(*) AS "Record_Count" FROM SaleItem

Back

Give an alphabetical list of customers (first name then last name) who live in CA or OK and the town they live in starts with A. Sort the list alphabetically by city. (Product names are not capitalized in the database.) Return customer names only.

Front

SELECT FirstName, LastName FROM Customer WHERE State IN('CA','OK') and City Like 'A%' ORDER BY City

Back

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.

Front

SELECT Category, SUM(ListPrice*Quantity) AS "Revenue" FROM Sale S JOIN SaleItem SI on S.SaleID = SI.SaleID JOIN PRoduct P on SI >>> .ProductID = P.ProductID WHERE Year(SaleDate) = 2014 GROUP BY Category

Back

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

Front

SELECT DISTINCT ProductName, Category, ManufacturerName FROM Product P JOIN Manufacturer M ON P.ManufacturerID = M.ManufacturerID WHERE (Category = 'sandals' OR Category = 'sneakers' OR Category = 'Casual shoes') AND (ManufacturerName = 'Buyers Picks' OR ManufacturerName = 'Radii Footwear')

Back

List names of all products that are blue or green. (Colors are capitalized in the database, i.e. "Blue".). Return product names only.

Front

1)SELECT Distinct ProductName 2)FROM Product 3)WHERE Color = 'Blue' or Color = 'Green'

Back

Create an alphabetical list of names of all sold products whose price is more than $120. Return product names only.

Front

1)SELECT Distinct ProductName 2)FROM Product 3)WHERE ListPrice > 120 4)ORDER BY ProductName

Back

List the names of all employees that were hired in 2008. Show first name, last name, City, and State. Order descending by last name

Front

SELECT FirstName, LastName, City, State FROM Employee WHERE Year(HireDate) = 2008 ORDER BY LastName DESC;

Back

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

Front

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 ManufacturerName = 'Nike' >>>AND Category = 'Sneakers' >>>AND Color = 'White'

Back

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.

Front

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

Back

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

Front

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

Back