RETURN; --Homework #3b Querying Multiple Tables --Your Name:

/*-------------------------------------------------------------------------------------- Instructions:

You will be using the Chicago Salary table but you will following the questions to normalize the data in order to provide a table structure to test your JOIN abilities.

You can use the original summary table to double check any answers.

Answer each question as best as possible. Show your work if you need to take multiple steps to answer a problem. Partial answers will count. --------------------------------------------------------------------------------------*/

/* Q1. (0.5 point) Write the syntax to drop and build a table called dbo.Employee. Create an EmployeeID field (IDENTITY PK), a Name field and a Salary field for the Employee table. Populate the Employee table with unique Name and Salary information from the dbo.ChicagoSalary table. */

/* Q1. Syntax*/

/* Q2. (0.5 point) Write the syntax to drop and build a table called dbo.Department. Create an DepartmentID field (IDENTITY PK), and a Name field for the Department Table. Populate the Department table with unique Department Names. */

/* Q2. Syntax */

/* Q3. (0.5 point) Write the syntax to drop and build a table called dbo.Position. Create an PositionID field (IDENTITY PK), and a Name field for the Position table. Populate the Position table with unique PositionTitles (call the field Title). */

/* Q3. Syntax */

/* Run the following query to populate a Employment table to help build the relationship between the above three tables. */

/* IF OBJECT_ID('dbo.Employment','U') IS NOT NULL DROP TABLE dbo.Employment;

SELECT DISTINCT IDENTITY(INT,1,1) EmploymentID , EmployeeID , PositionID , DepartmentID INTO dbo.Employment FROM dbo.ChicagoSalary CS INNER JOIN dbo.Employee E on CS.Name = E.Name and CS.Salary = E.Salary INNER JOIN dbo.Position P on P.Title = CS.PositionTitle INNER JOIN dbo.Department D on D.Name = CS.Department; */

/* Q4. (0.5 point) Display the same output as the dbo.ChicagoSalary table but use the new 4 tables you created. */

/* Q4. Syntax*/

/* Q5. (1 point) Using the new tables and JOINs to display Number of Employees and Average Salary in the Police department. */

/*Q5. Syntax*/

/* Q6. (1 point) Using the new tables and JOINs to provide the Number of Employees and Total Salary of Each Department. Sort the output by Department A->Z. */

/*Q6. Syntax*/

/* Q7. (1 point) Using the new table(s) and subqueries to list the name(s) and salary of employee(s) whose last name is Aaron and work for the POLICE department. */

/*Q7. Syntax*/

/*Q7. Answer: Name= Salary= */

/* Q8. (1 point) Display the name(s) of the people who have the longest name(s) */

/* Q8. Syntax */

/* Q8.Answer:

*/