MS SQL SERVER MANAGEMENT STUDIO QUERIES

Department Data

Develop SQL queries in MS SQL SERVER (in one script) to answer the following questions A through L:

What projects are in the PROJECT table? Show all information for each project.

What are the ProjectID, Name, StartDate, and EndDate values of projects in the PROJECT table?

What projects in the PROJECT table started before August 1, 2008? Show all the information for each project.

What projects in the PROJECT table have not been completed? Show all the information for each project.

Who are the employees assigned to each project? Show ProjectID, Employee-Number, LastName, FirstName, and Phone.

Who are the employees assigned to each project? Show the ProjectID, Name, and Department. Show EmployeeNumber, LastName, FirstName, and Phone.

Who are the employees assigned to each project? Show ProjectID, Name, Department, and Department Phone. Show EmployeeNumber, LastName, FirstName, and Employee Phone. Sort by ProjectID in ascending order.

Who are the employees assigned to projects run by the marketing department? Show ProjectID, Name, Department, and Department Phone. Show EmployeeNumber, LastName, FirstName, and Employee Phone. Sort by ProjectID in ascending order.

How many projects are being run by the marketing department? Be sure to assign an appropriate column name to the computed results.

What is the total MaxHours of projects being run by the marketing department? Be sure to assign an appropriate column name to the computed results.

What is the average MaxHours of projects being run by the marketing department? Be sure to assign an appropriate column name to the computed results.

How many projects are being run by each department? Be sure to display each DepartmentName and to assign an appropriate column name to the computed results.

PROJECT Column Name ProjectID Key Required Remarks Type bigint nvarchar(50) No nvarchar(35) Foreign Key Yes float Primary Key Yes Long Integer Name Yes Department MaxHours No Yes Double StartDate datetime 0 EndDate datetime No No ASSIGNMENT Column Name Type Key Required Remarks ProjectlD Primary Key, Yes Foreign Key bigint Long Integer Primary Key, Yes Foreign Key EmployeeNumber bigint Long Integer HoursWorked float No Double