--AJAY SINGH


--1 List the customers from California who bought red mountain bikes in September 2003. Use order date as date bought.
select c.CustomerID, c.LastName, c.FirstName, b.ModelType, p.ColorList, b.OrderDate, b.SaleState
from Customer c inner join Bicycle b on c.CustomerID = b.CustomerID
		inner join Paint p on b.PaintID = p.PaintID
where p.ColorList like '%Red%' and b.SaleState like 'CA'
		AND B.ModelType LIKE '%Mountain%' and (DATEPART(YYYY,b.OrderDate)=2003) and (DATEPART(MM,b.OrderDate)=9)

--2 List the employees who sold race bikes shipped to Wisconsin without the help of a retail store in 2001
Select E.EmployeeID, E.LastName, B.SaleState, B.ModelType, B.StoreID, B.OrderDate
from Bicycle B inner join Employee E on B.EmployeeID = E.EmployeeID
where B.ModelType like '%Race%' AND B.SaleState LIKE 'WI' and (DATEPART(YYYY,b.OrderDate)=2001)

--3 List all of the (distinct) rear derailleurs installed on road bikes sold in Florida in 2002.
SELECT DISTINCT C.ComponentID, M.ManufacturerName, C.ProductNumber
FROM Manufacturer M INNER JOIN Component C ON M.ManufacturerID = C.ManufacturerID
	 INNER JOIN BikeParts BP ON BP.ComponentID = C.ComponentID
	 INNER JOIN Bicycle B ON B.SerialNumber = BP.SerialNumber 
WHERE C.Category = 'Rear derailleur'
		and C.Road IS NOT NULL
		AND B.SaleState = 'FL' 
		AND (DATEPART(YYYY, B.OrderDate)=2002)

--4 Who bought the largest (frame size) full suspension mountain bike sold in Georgia in 2004?
SELECT C.CustomerID, C.LastName, C.FirstName, B.ModelType, B.SaleState, B.FrameSize, B.OrderDate
FROM Customer C INNER JOIN Bicycle B ON C.CustomerID = B.CustomerID
WHERE B.SaleState ='GA' AND (DATEPART(YYYY, B.OrderDate)=2004)
		AND B.ModelType = 'Mountain full'
		AND B.FRAMESIZE = (SELECT MAX(FRAMESIZE)
							FROM   BICYCLE
							WHERE  B.SALESTATE = 'GA' AND MODELTYPE = 'MOUNTAIN FULL'
					                                  AND YEAR(B.ORDERDATE) = 2004)
--5 Which manufacturer gave us the largest discount on an order in 2003?
select PO.ManufacturerID, M.ManufacturerName
from PurchaseOrder PO INNER JOIN Manufacturer M ON M.ManufacturerID = PO.ManufacturerID
WHERE YEAR(PO.OrderDate)=2003
		AND PO.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?
SELECT C.ComponentID, M.ManufacturerName, C.ProductNumber, C.Road, C.Category, C.ListPrice, C.QuantityOnHand
FROM Component C INNER JOIN Manufacturer M ON M.ManufacturerID = C.ManufacturerID
WHERE c.QuantityOnHand > 200 AND C.ListPrice = (SELECT MAX(ListPrice)
												FROM Component
												WHERE QuantityOnHand >200
												)
--7 Which inventory item represents the most money sitting on the shelf—based on estimated cost?
SELECT C.ComponentID, M.ManufacturerName, C.ProductNumber, C.Category, C.Year, C.EstimatedCost AS "VALUE"
FROM COMPONENT C INNER JOIN Manufacturer M ON C.ManufacturerID = M.ManufacturerID
WHERE C.EstimatedCost = (SELECT MAX(EstimatedCost)
						FROM Component
						)

--8 What is the greatest number of components ever installed in one day by one employee?
SELECT TOP 1 BP.EMPLOYEEID, E.LastName, BP.DateInstalled, COUNT(COMPONENTID) AS "COUNT OF COMPONENTS"
FROM BikeParts BP INNER JOIN EMPLOYEE E ON BP.EmployeeID = E.EmployeeID
WHERE BP.DATEINSTALLED IS NOT NULL
GROUP BY BP.EmployeeID, E.LastName, BP.DateInstalled
ORDER BY [COUNT OF COMPONENTS] DESC
							

--9 What was the most popular letter style on race bikes in 2003?
SELECT	TOP 1 LetterStyleID, COUNT(SERIALNUMBER) AS "COUNTOFSERIALNUMBER"
FROM	      BICYCLE
WHERE	      YEAR(ORDERDATE) = '2003' AND MODELTYPE = 'RACE'
GROUP BY      LetterStyleID
ORDER BY COUNT(SERIALNUMBER) DESC

--10 Which customer spent the most money with us and how many bicycles did that person buy in 2002?
SELECT TOP 1 C.CUSTOMERID, C.LASTNAME, C.FIRSTNAME, COUNT(B.SerialNumber) AS "NUMBEROFBIKES", SUM(CT.AMOUNT) AS "AMOUNT SPENT"
FROM         CustomerTransaction CT INNER JOIN CUSTOMER C ON CT.CustomerID = C.CustomerID 
							        INNER JOIN Bicycle  B ON B.CustomerID  = C.CustomerID
WHERE        YEAR(CT.TransactionDate) = 2002 AND AMOUNT > 0
GROUP BY     C.CustomerID, C.LastName, C.FirstName
ORDER BY     SUM(CT.AMOUNT) DESC
	
--11 Have the sales of mountain bikes (full suspension or hard tail) increased or decreased from 2000 to 2004 (by count not by value)? 
--You will list the number sold by year in descending order. 
SELECT	  YEAR(ORDERDATE) AS "SALE YEAR", COUNT(SERIALNUMBER) AS "COUNT OF SERIAL NUMBER"
FROM	  BICYCLE
WHERE     YEAR(ORDERDATE) >= 2000     AND 
		  YEAR(ORDERDATE) <= 2004     AND
		  (MODELTYPE LIKE 'MOUNTAIN%')
		   
GROUP BY  YEAR(ORDERDATE) 
ORDER BY "SALE YEAR" DESC

--12 Which component did the company spend the most money on in 2003?
SELECT C.ComponentID, M.ManufacturerName, C.ProductNumber, C.Category, P.PricePaid AS "VALUE"
FROM   PURCHASEORDER PO INNER JOIN PURCHASEITEM P ON PO.PurchaseID = P.PurchaseID
					    INNER JOIN Component C    ON P.ComponentID = C.ComponentID 
					    INNER JOIN Manufacturer M ON C.ManufacturerID = M.ManufacturerID
WHERE  YEAR(ORDERDATE) = 2003 AND 
	   P.PricePaid = (SELECT MAX(P.PRICEPAID)
					  FROM	 PurchaseItem P INNER JOIN PurchaseOrder PO 
					  ON     P.PurchaseID = PO.PurchaseID
					  WHERE	 YEAR(ORDERDATE) = 2003)

--13 Which employee painted the most red race bikes in May 2003?
SELECT E.EmployeeID, E.LASTNAME, COUNT(SERIALNUMBER) AS "NUMBER PAINTED"
FROM PAINT P INNER JOIN BICYCLE B ON P.PaintID = B.PaintID 
		     INNER JOIN EMPLOYEE E ON B.EmployeeID = E.EmployeeID
WHERE MODELTYPE = 'RACE' AND YEAR(ORDERDATE) = 2003 AND MONTH(ORDERDATE) = 5 AND P.ColorList = 'RED'
GROUP BY E.EmployeeID, E.LASTNAME
ORDER BY COUNT(SERIALNUMBER) DESC


--14 Which California bike shop helped sell the most bikes (by value) in 2003?
SELECT TOP 1 STOREID
FROM         Bicycle B INNER JOIN CustomerTransaction CT ON B.EMPLOYEEID = CT.EMPLOYEEID
WHERE	     YEAR(ORDERDATE) = 2003 AND SaleState = 'CA' 
GROUP BY     StoreID
ORDER BY     SUM(SALEPRICE) DESC


--15 What is the total weight of the components on bicycle 11356?
SELECT SUM(C.WEIGHT) AS "TOTAL WEIGHT"
FROM   BICYCLE B INNER JOIN BikeParts BP ON B.SerialNumber = BP.SerialNumber
				 INNER JOIN COMPONENT C  ON BP.ComponentID = C.ComponentID
WHERE  B.SerialNumber = '11356'

--16 What is the total list price of all items in the 2002 Campy Record group?
SELECT   G.GROUPNAME, SUM(C.LISTPRICE) AS "SUM OF LIST PRICE"
FROM     Groupo G INNER JOIN GroupComponents GC ON G.ComponentGroupID = GC.GROUPID 
			      INNER JOIN Component C ON GC.ComponentID = C.ComponentID
WHERE    G.GROUPNAME = 'CAMPY RECORD 2002'
GROUP BY G.GROUPNAME

--17 In 2003, were more race bikes built from carbon or titanium (based on the down tube)?
SELECT	TOP 1 TM.Material, COUNT(B.SERIALNUMBER) AS "COUNT OF SERIAL NUMBER"
FROM	 BICYCLE B INNER JOIN BicycleTubeUsage BT ON B.SerialNumber = BT.SerialNumber
		           INNER JOIN TubeMaterial TM ON TM.TubeID = BT.TubeID
WHERE    YEAR(STARTDATE) = 2003 AND
		 MODELTYPE = 'RACE' AND
		 (TM.MATERIAL = 'CARBON FIBER'  OR 
	     TM.MATERIAL = 'TITANIUM')
GROUP BY TM.Material
ORDER BY "COUNT OF SERIAL NUMBER" DESC

--18 What is the average price paid for the 2001 Shimano XTR rear derailleurs?
SELECT AVG(P.PRICEPAID) AS [AVERAGE OF PRICE PAID]
FROM   PURCHASEITEM P INNER JOIN COMPONENT C ON P.ComponentID = C.ComponentID 
					  INNER JOIN GroupComponents G ON G.COMPONENTID = C.COMPONENTID
					  INNER JOIN GROUPO O ON O.COMPONENTGROUPID = G.GROUPID
WHERE O.GROUPNAME = 'SHIMANO XTR 2001' AND C.CATEGORY = 'REAR DERAILLEUR'

--19 What is the average top tube length for a 54 cm (frame size) road bike built in 1999?
SELECT AVG(TOPTUBE) AS "AVERAGE TUBE LENGTH"
FROM   BICYCLE
WHERE  YEAR(SHIPDATE) = 1999 AND 
	   MODELTYPE = 'ROAD'     AND 
	   FRAMESIZE = '54'

--20 On average, which costs (list price) more: road tires or mountain bike tires?
SELECT TOP 1 ROAD, AVG(LISTPRICE) AS "AVERAGE LIST PRICE"
FROM         COMPONENT
WHERE        CATEGORY = 'TIRE' AND 
		     ROAD IN ('MTB','ROAD')
GROUP BY     ROAD
ORDER BY     AVG(LISTPRICE) DESC

--21 In May 2003, which employees sold road bikes that they also painted?
SELECT DISTINCT B.EMPLOYEEID, E.LastName
FROM   EMPLOYEE E INNER JOIN BICYCLE B ON E.EmployeeID = B.EmployeeID
WHERE  YEAR(ORDERDATE) = 2003 AND 
	   MONTH(ORDERDATE) = 5   AND 
	   PAINTER = B.EMPLOYEEID AND 
	   MODELTYPE = 'ROAD'

--22 In 2002, was the Old English letter style more popular with some paint jobs?
SELECT P.PAINTID, P.COLORNAME, COUNT(B.SERIALNUMBER) AS "NUMBER OF BIKES PAINTED"
FROM   PAINT P INNER JOIN BICYCLE B ON P.PaintID = B.PaintID 
		       INNER JOIN LetterStyle L ON B.LetterStyleID = L.LetterStyle 
WHERE  B.LETTERSTYLEID LIKE 'ENGLISH' AND YEAR(ORDERDATE) = 2002
GROUP BY P.PaintID, P.COLORNAME
ORDER BY "NUMBER OF BIKES PAINTED" DESC

--23 Which race bikes in 2003 sold for more than the average price of race bikes in 2002?
SELECT SERIALNUMBER, MODELTYPE, ORDERDATE, SALEPRICE
FROM BICYCLE
WHERE MODELTYPE = 'RACE' AND
	  YEAR(ORDERDATE) = 2003 AND
	  SALEPRICE > (SELECT AVG(SALEPRICE)
				   FROM   BICYCLE
				   WHERE  MODELTYPE = 'RACE'
				   AND YEAR(ORDERDATE) = 2002)

--24 Which component that had no sales (installations) in 2004 has the highest inventory value (cost basis)?
SELECT   TOP 1 M.MANUFACTURERNAME, C.PRODUCTNUMBER, C.CATEGORY, SUM(C.LISTPRICE * C.QUANTITYONHAND) AS [VALUE], BP.DATEINSTALLED 
FROM	     MANUFACTURER M INNER JOIN COMPONENT C  ON C.MANUFACTURERID = M.MANUFACTURERID 
			                INNER JOIN BIKEPARTS BP ON C.COMPONENTID = BP.COMPONENTID
WHERE        YEAR(BP.DATEINSTALLED) NOT LIKE 2004
GROUP BY     C.PRODUCTNUMBER, C.CATEGORY, M.MANUFACTURERNAME, BP.DATEINSTALLED 
ORDER BY     SUM(C.LISTPRICE * C.QUANTITYONHAND) DESC

--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
SELECT	S.STORENAME, S.PHONE 
FROM	RetailStoRE S INNER JOIN BICYCLE B ON B.STOREID = S.STOREID 
					  INNER JOIN CITY C ON C.CITYID = S.CityID
WHERE	YEAR(B.ORDERDATE) = 2004 AND (C.STATE = 'CA' OR B.SALESTATE = 'CA')
GROUP BY S.StoreName, S.PHONE

--26 List all of the employees who report to Venetiaan.
SELECT(	SELECT	LASTNAME
		FROM	EMPLOYEE
		WHERE	EMPLOYEEID =  (	SELECT	EMPLOYEEID
								FROM	EMPLOYEE
								WHERE	LASTNAME = 'VENETIAAN') )  AS [MANAGER NAME], EMPLOYEEID, FIRSTNAME, LASTNAME, TITLE
FROM	        EMPLOYEE
WHERE	        CURRENTMANAGER = (	SELECT	EMPLOYEEID
							        FROM	EMPLOYEE
							        WHERE	LASTNAME = 'VENETIAAN')

--27 List the components where the company purchased at least 25 percent more units than it used through June 30, 2000. 
--An item is used if it has an install date.
 SELECT C.COMPONENTID, M.MANUFACTURERNAME, C.PRODUCTNUMBER, C.CATEGORY, PI.QUANTITYRECEIVED AS "TOTAL RECEIVED", 
		((COUNT(DATEINSTALLED)-PI.QuantityReceived)*(C.LISTPRICE - PI.PRICEPAID)) AS "NET GAIN",
		 COUNT(P.DATEINSTALLED) AS "TOTAL USED", C.ListPrice, ((COUNT(DATEINSTALLED)-PI.QuantityReceived)/(C.LISTPRICE-PI.PRICEPAID)) AS "NETPCT"
 FROM COMPONENT C INNER JOIN MANUFACTURER M ON M.MANUFACTURERID = C.MANUFACTURERID 
				  INNER JOIN BIKEPARTS P ON P.COMPONENTID = C.COMPONENTID
				  INNER JOIN PURCHASEITEM PI ON PI.COMPONENTID = C.COMPONENTID
				  INNER JOIN PURCHASEORDER PO ON PO.PURCHASEID = PI.PURCHASEID
WHERE	PO.ReceiveDate <= '30-JUN-2000' 
GROUP BY C.ComponentID, M.ManufacturerName, C.ProductNumber, C.Category, PI.QuantityReceived, C.LISTPRICE, PI.PRICEPAID
HAVING PI.QuantityReceived >= 1.25 * COUNT(P.DATEINSTALLED)


--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.
SELECT YEAR(ORDERDATE) AS 'YEAR', AVG(DATEDIFF(DAY, ORDERDATE, SHIPDATE)) AS 'BUILD TIME'
FROM BICYCLE
GROUP BY YEAR(ORDERDATE)
HAVING AVG(DATEDIFF(DAY, ORDERDATE, SHIPDATE)) > (SELECT AVG(DATEDIFF(DAY, ORDERDATE, SHIPDATE)) AS 'TOTAL AVERAGE'
												  FROM	 BICYCLE
												  )
ORDER BY YEAR(ORDERDATE) ASC