×
Database Design
This course was CIS 310. It provides a solid and practical foundation for the design and implementation of database systems. Emphasis is on relational database models, normalization, E-R modeling, locking, SQL, distributed databases, and security. Tools like SQL Server were used in hands-on labs.
-- Group Work SQL
/*
STUDENT NAME: Jrew Simpson, Bryce Cooper, Charles Degboe, Cora Alward
STUDENT ID: 5470008, 5443598, 5491133, 5492390
Which Questions did you answer? e.g. 1-5, 10, 12....
*/
-- 1, 2, 5, 9, 12-16, 19-23, 26
/*
Note: your query should be based on the actual data. It is good to gain familiarity with table contents before starting to query.
Query should use as few tables as possible if a JOIN is required. Sometimes a single table may contain all the attribute you need.
*/
USE BIKE;
/*
Grading:
No Credit- If you provide no query, or ONLY the query without explanation, even if is fully correct.
Full Credit 1 - Provide your working/thinking process via comments
Your intermediate steps/queries while building the query,
And the fully correct query for the query
Full Credit 2 - If you cannot arrive at a fully correct query,
Still, provide your working/thinking process via comments, which tables have you checked, which may contain data, which do not
Your intermediate steps/queries while attemptiong to build the query, and at least 3+ relevant, trying queries
*they don't need to execute properly, but should be relevant, and demonstrate your thinking progression. Simple queries such
as Select * from table do NOT count
*/
--====================================================Exercise Questions =====================================================
--==========================================& Expected output table columns ===================================================
--1. List the customers from California who bought red mountain bikes in September 2003.
--Use order date as date bought. Multi-color bikes with red are considered red bikes.
SELECT C.CustomerID, C.LastName, C.FirstName, B.ModelType, P.ColorList, B.OrderDate, B.SaleState -- Selecting all necessary attributes from
tables Bicycle, Customer, and Paint
FROM CUSTOMER C
JOIN Bicycle B ON C.CustomerID = B.CustomerID -- Joining all three tables together
JOIN Paint P ON B.PaintID = P.PaintID
WHERE B.PaintID IN (5, 6, 13, 14) -- All these PaintIDs contain RED
AND SaleState='CA' -- Only state
AND ModelType LIKE '%Mountain%' -- Only type
AND CONVERT(VARCHAR, b.OrderDate, 120) LIKE '%2003-09%'; -- Needs to be during September 2003, had to convert
OrderDate to VARCHAR for LIKE operator to work correctly
--CustomerID
LastName
FirstName
ModelType
ColorList
OrderDate
SaleState
--2. List the employees who sold race bikes shipped to Wisconsin without the help of a retail store in 2001,
--Without help of retail store means rders completed without the help of a retail store are walk-in or direct sales.
--EmployeeID
LastName
SaleState
ModelType
StoreID
OrderDate
SELECT E.EMPLOYEEID, E.LASTNAME, B.SALESTATE, B.MODELTYPE, RS.STOREID, B.ORDERDATE
FROM EMPLOYEE E INNER JOIN BICYCLE B ON E.EMPLOYEEID = B.EMPLOYEEID
INNER JOIN RETAILSTORE RS ON RS.STOREID = B.STOREID
WHERE B.MODELTYPE = 'RACE'
AND
B.SALESTATE = 'WI'
AND
(RS.STORENAME LIKE '%WALK-IN%' OR RS.STORENAME LIKE '%DIRECTSALES%')
AND
YEAR(B.ORDERDATE) = 2001;
--3. List all of the (distinct) rear derailleurs installed on road bikes sold in Florida in 2002.
--ComponentID
ManufacturerName
ProductNumber
--4. Who bought the largest (frame size) full suspension mountain bike sold in Georgia in 2004?
--CustomerID
LastName
FirstName
ModelType
SaleState
FrameSize
OrderDate
--5. Which manufacturer gave us the largest discount on an order in 2003?
--ManufacturerID
ManufacturerName
SELECT M.MANUFACTURERID, M.MANUFACTURERNAME
FROM MANUFACTURER M INNER JOIN PURCHASEORDER PO ON M.MANUFACTURERID = PO.MANUFACTURERID
WHERE YEAR(ORDERDATE) = 2003
AND
DISCOUNT = (SELECT MAX(DISCOUNT)
FROM PURCHASEORDER
WHERE YEAR(ORDERDATE) = 2003);
--6. What is the most expensive road bike component we stock that has a quantity on hand greater than 200 units?
--ComponentID
ManufacturerName
ProductNumber
Road
Category
ListPrice
QuantityOnHand
--7. Which inventory item represents the most money sitting on the shelf—based on estimated cost?
--Inventory value is defined as [EstimatedCost]*[QuantityOnHand] in Component.
--ComponentID
ManufacturerName
ProductNumber
Category
Year
Value
--8. What is the greatest number of components ever installed in one day by one employee?
--EmployeeID
LastName
DateInstalled
CountOfComponentID
--9. What was the most popular letter style on race bikes in 2003?
--LetterStyleID
CountOfSerialNumber
SELECT TOP 1 LetterStyleID, COUNT(SerialNumber) AS COUNTOFSERIALNUMBER -- Selecting LetterStyleID and Counting amount of SNs,
FROM Bicycle
WHERE CONVERT(VARCHAR, OrderDate, 120) LIKE '%2003%' -- Has to be year 2003
AND ModelType = 'Race' -- Has to be a race bike
GROUP BY LetterStyleID
ORDER BY COUNT(*) Desc; -- This along with the Top 1 shows causes the query to show most popular letter style
--10. Which customer spent the most money with us and how many bicycles did that person buy in 2002?
--Use 2002 for both amount spent and number of bikes bought. Use OrderDate when determining year. Use SalePrice as amount spent.
--CustomerID
LastName
FirstName
Number of Bikes
Amount Spent
--11. Have the sales of mountain bikes (full suspension or hard tail) increased or decreased from 2000 to 2004 (by count not by value)?
--We are looking for any bike with a model type that starts with the word Mountain.
--SaleYear
CountOfSerialNumber
--12. Which component did the company spend the most money on in 2003?
--The amount spent on a component is the sum of purchase price * quantity. Note the same component may have been purchased multiple times in a
time period.
--ComponentID
ManufacturerName
ProductNumber
Category
Value
SELECT TOP 1 C.ComponentID, M.ManufacturerName, C.ProductNumber, C.Category, SUM(Pi.PricePaid*Pi.Quantity) AS Value
FROM PurchaseItem Pi
JOIN Component C ON Pi.ComponentID = C.ComponentID
JOIN Manufacturer M ON C.ManufacturerID = M.ManufacturerID -- Joining all three necessary tables
GROUP BY C.ComponentID, M.ManufacturerName, C.ProductNumber, C.Category
ORDER BY VALUE DESC; -- Sorts by highest to lowest values, along with Top 1 gives the highest value
--13. Which employee painted the most red race bikes in May 2003?
--EmployeeID
LastName
Number Painted
SELECT TOP 1 E.EmployeeID, E.LastName, COUNT(Painter) AS Number_Painted
FROM Bicycle B JOIN Employee E ON B.EmployeeID = E.EmployeeID -- Query requires table joins
WHERE B.PaintID IN (5, 6, 13, 14) -- Red Paint IDs
AND CONVERT(VARCHAR, b.OrderDate, 120) LIKE '%2003-05%' -- Filters to May 2003
GROUP BY E.EmployeeID, E.LastName
ORDER BY Number_Painted DESC; -- Orders by highest number painted, top 1 shows the most paints
--14. Which California bike shop helped sell the most bikes (by value) in 2003?
--StoreID
StoreName
City
SumOfSalePrice
SELECT TOP 1 RS.STOREID, RS.STORENAME, C.CITY, SUM(B.SALEPRICE) AS SUMOFSALEPRICE
FROM RETAILSTORE RS INNER JOIN BICYCLE B ON RS.STOREID = B.STOREID
INNER JOIN CITY C ON C.CITYID = RS.CITYID
WHERE C.STATE = 'CA'
AND
YEAR(B.ORDERDATE) = 2003
GROUP BY RS.STOREID, RS.STORENAME, C.CITY
ORDER BY SUMOFSALEPRICE DESC;
--15. What is the total weight of the components on bicycle 11356?
--TotalWeight
SELECT SUM(C.WEIGHT) AS TOTALWEIGHT
FROM COMPONENT C INNER JOIN BIKEPARTS B ON B.COMPONENTID = C.COMPONENTID
WHERE B.SERIALNUMBER = '11356';
--16. What is the total list price of all items in the 2002 Campy Record groupo?
--GroupName
SumOfListPrice
SELECT G.GroupName, SUM(ListPrice) AS SumOfListPrice -- Selecting group name from Groupo and Sum of listprices from Component
FROM Component C
JOIN GroupComponents GC ON C.ComponentID = GC.ComponentID -- Joining Groupo, GroupComponent, and Component
JOIN Groupo G ON GC.GroupID = G.ComponentGroupID -- Although nothing is selected or filtered from GroupComponent, it's
necessary because it connects the two tables
WHERE GroupName = 'Campy Record 2002' -- List price sum from Campy Record 2002
GROUP BY G.GroupName; -- Needed for query to print out
--17. In 2003, were more race bikes built from carbon or titanium (based on the down tube)?
--As output you may show the number of bikes for both materials. Use OrderDate.
--Material
CountOfSerialNumber
--18. What is the average price paid for the 2001 Shimano XTR rear derailleurs?
--AvgOfPricePaid
--19. What is the average top tube length for a 54 cm (frame size) road bike built in 1999?
--AvgOfTopTube
SELECT AVG(TopTube) AS AvgOfTopTube -- Average TopTube length
FROM Bicycle -- Single table Bicycle
WHERE FrameSize = 54 -- 54 cm Frame size
AND CONVERT(VARCHAR, StartDate, 120) LIKE '%1999%'; -- Filter to 1999 date
--20. On average, which costs (list price) more: road tires or mountain bike tires?
--Road
AvgOfListPrice
SELECT Road, AVG(ListPrice) AS AvgOfListPrice -- Selected tire road type and average list prices
FROM Component -- Single table query from Component
WHERE Road='Road' -- Filters to Road and MTB, No NULL values
OR Road='MTB'
GROUP BY Road
ORDER BY AvgOfListPrice DESC; -- Shows the Average List Prices with highest on top
--21. In May 2003, which employees sold road bikes that they also painted?
--EmployeeID
LastName
SELECT E.EmployeeID, E.LastName -- Selected Last name and employee IDs
FROM Bicycle B JOIN Employee E ON B.EmployeeID = E.EmployeeID -- Join Bicycle and Employee tables
WHERE E.EMPLOYEEID = B.Painter; -- If Employee ID and Painter have the same values then they sold the bikes that they also
painted
-- Had to select painter before finishing query to make sure the IDs matched
--22. In 2002, was the Old English letter style more popular with some paint jobs?
--PaintID
ColorName
Number of Bikes Painted
SELECT B. PAINTID, P. COLORNAME, COUNT (B. SERIALNUMBER) AS [NUMBER OF BIKES PAINTED]
FROM BICYCLE B INNER JOIN PAINT P ON B. PAINTID = P.PAINTID
WHERE B. LETTERSTYLEID = 'ENGLISH'
AND
YEAR(B.ORDERDATE) = '2002'
GROUP BY P.COLORNAME, B. PAINTID
ORDER BY COUNT(B. SERIALNUMBER) DESC;
--23. Which race bikes in 2003 sold for more than the average price of race bikes in 2002?
--SerialNumber
ModelType
OrderDate
SalePrice
SELECT SerialNumber, ModelType, OrderDate, SalePrice
FROM Bicycle
WHERE SalePrice > (SELECT AVG(SalePrice) -- Nested query to find average price of bikes in 2002
FROM Bicycle
WHERE CONVERT(VARCHAR, OrderDate, 120) LIKE '%2002%' --Filter to year 2002 in
nested query
AND ModelType='Race') -- Filter to Race models
GROUP BY SerialNumber, ModelType, OrderDate, SalePrice -- Group by function so I can use HAVING clause
HAVING CONVERT(VARCHAR, OrderDate, 120) LIKE '%2003%' -- Filter to year 2003
AND ModelType='Race'; -- Filter to Race models
--24. Which component that had no sales (installations) in 2004 has the highest inventory value (cost basis)?
--ManufacturerName
ProductNumber
Category
Value
ComponentID
--25. Create a vendor contacts list of all manufacturers and retail stores in California.
--Include only the columns for VendorName and Phone.
--The retail stores should only include stores that participated in the sale of at least one bicycle in 2004
--Store Name Or Manufacturer Name
Phone
--26. List all of the employees who report to Venetiaan.
--LastName
EmployeeID
LastName
FirstName
Title
SELECT LastName, FirstName, EmployeeID, Title, CurrentManager
FROM Employee
WHERE CurrentManager = '15293';
--27. List the components where the company purchased at least 25 percent more units than it used through June 30, 2000.
--ComponentID
ManufacturerName
ProductNumber
Category
TotalReceived
TotalUsed
NetGain
NetPct
ListPrice
--28. In which years did the average build time for the year exceed the overall average build time for all years?
--The build time is the difference between order date and ship date.
--Use the difference between OrderDate and ShipDate.
--Year
BuildTime
