--************************************************************************************************************************

--*

--************************************************************************************************************************

--STEP 1) Run the "CS120Exam_DB_Script.sql" file that creates 3 tables - CS120Exam_Employee, CS120Exam_EmployeeDepartmentHistory and CS120Exam_SalesOrderDetail.

SELECT * FROM CS120Exam_Employee

SELECT * FROM CS120Exam_EmployeeDepartmentHistory

SELECT * FROM CS120Exam_SalesOrderDetail

--************************************************************************************************************************

--*

--************************************************************************************************************************

--STEP 2) Solve the following questions. Include your queries in the [dbo].[spSubmitFinalExam] stored procedure below. Your queries MUST be organized; hence, SQL readers can follow your steps easily. This is Due on Dec 12.

CREATE PROCEDURE [dbo].[spSubmitFinalExam]

AS

BEGIN

--*******************************************************************

--*

--*******************************************************************

--QUESTION 1) 3 Points: Use the CS120Exam_SalesOrderDetail table.

--Purpose: Find the 20 most popular product items in term of Quantity sold in the past. There are 266 Products are sold:

--*******************************************************************

SELECT TOP(20) ProductID, SUM(OrderQty) AS Quantity

FROM CS120Exam_SalesOrderDetail

GROUP BY ProductID

ORDER BY SUM(OrderQty) DESC;

^^^(I think I got this part right, but feel free to correct me)^^^

--*******************************************************************

-- QUESTION 2) 7 Points: Use the CS120Exam_SalesOrderDetail table.

-- Purpose: Add Total Amount Column as "TotalAmt"

-- Update "TotalAmt" column with OrderQty * (UnitPrice - UnitPriceDiscount)

SELECT TotalAmt, *

FROM [CS120Exam_SalesOrderDetail]

ORDER BY SalesOrderID

--*******************************************************************

--

--*******************************************************************

--QUESTION 3) 10 Points: Use the CS120Exam_Employee table.

--Purpose: Create yahoo email addresses for employees and place them in the EmailAddress column. The email address should be comprised of + <_> + +<_> + + <@Yahoo.com>.

--Here are sample email addresses:K_Snchez_1@Yahoo.com, T_Duffy_2@Yahoo.com

SELECT *

FROM [CS120Exam_Employee] order by [BusinessEntityID]

GO

--*******************************************************************

--*

--*******************************************************************

--QUESTION 4) 20 Points: Use the CS120Exam_Employee and CS120Exam_EmployeeDepartmentHistory tables.

--Purpose: A web application will pass zip code to database and the stored procedure will return number of emplyees residing in the area.

-- A) Create a stored procedure - Get_ListOfEmployeeInZip - that returns employees' data in the particular zip.

-- B) The stored procedures returns - FirstName, LastName, JobTile, Department (A LATEST Dept from CS120Exam_EmployeeDepartmentHistory table), PhoneNumber and Location (This should be "US" or "Other Country" based on the CountryRegionName).

-- C) Name the stored procedure as Get_ListOfEmployeeInZip

EXEC dbo.Get_ListOfEmployeeInZip '98055'

EXEC dbo.Get_ListOfEmployeeInZip '55402' --Hint: What is the LATEST dept for "Rob Walters"? It should be "Tool Design" NOT "Engineering" dept.