CS$2443$ SQL Server

Assignment $7$

Total Points: $100$

Write the code for each of the following and submit a textfile with the scripts in the

DropBox for the assignment in Moodle.

Questions $1-5$ are each worth $8$ points and Questions $6-11$ are each worth $10$ points.

COMPANY database

The Company databse stores data about its employees, their departments and assignments

and projects in the following tables.

Employee

empSSN

empLName

empFName

empGender

empAddress

empDOB

empSalary

empDeptNum

empSuperSSN

Assignment

workempSSN

workProjNumber

workHours

workHoursplanned

Project

projNumber

projName

projLocation

projdeptNum

Department

deptnum deptName

deptMgrSSN

deptMgr$\_$startdate

Dept$\_$Location

Deptnum

deptlocation

The Company is organized in separate departments. Each department has a unique

deptnum and has a department name. Each department has a manager and the date on

which the manager started managing the department. The manager$$s social security

number is stored in the department table and it provides a foreign key link to the specific

employee that manages a department.

Each department may have multiple city locations within the company. The primary key

of the dept$\_$location table is the composite of deptnum and the deptlocation. The deptnum

also serves as a foreign key link back to the departments table.

The Employee table stores employee information. Each employee is identified by

empSSN. Each employee works in a department. Some employees also mange

departments. A department can have $0,1$ or more assigned employees. Through the

empDeptNum we have a foreign key relationship with the Department table. Each

department has $1$ manager and so an employee may have a manager $.$ Some employees

maybe manager themselves.

The Project table contains details about each project that the company has. Projects for

the company are controlled by departments. Each project has a project number

$($projNumber$)$ and they are kept track of by project name $($projName$)$ and by location. A

department may have $0,1$ or more projects, and a project belongs to $1$ and only one

department.

The Assignment table keeps track of project assignments. Each employee is assigned to

work on $0,1$ or more projects. It associates the employee and project tables. The primary

key is a composite of the primary key from the employee table combined with the

primary key from the project table.

Using the Company database, write SQL scripts for the following cases:

$1.$ The Department table stores information about departments within the company.

The deptMgr$\_$startdate column stores the start date on which an employee started

working as a department manager.Write a query to display the date for the

manager that has worked the longest as a department manager. Label the output

column as Longest Working Manager.

$2.$ Accountants working on the company$$s annual budgeting process needs to know

the average salary for employees and the sum of all employee salaries. All

information is in the Employee table.

$3.$ The company$$s VP for Project Management needs to know the number of

projects each department is working on based on the information stored in the

Project table.

$4.$ The Company$$s VP for Project Management wants a list of projects located in

Oklahoma or supervised by the $$Production$$ department.

$5.$ The Assignment table stores data about the hours that employees are working on

specific projects. A senior project manager desires a list of employees$($last and

first names$)$ who are currently working on project numbers $10,20$ or $30.$Use a

subquery approach.

$6.$ Management is concerned about work productivity. Write a query that produces a

listing of each employee who has not worked on a sinlge project till date. The

employees name $($first and last names$)$ and their department names are to be

displayed.

$7.$ The company$$s senior project manager needs to access information about

departments that manage projects for a specific set of projects, namely those

located in Oklahoma or Texas. Create a view named department$\_$projects that

includes the department number, department name $,$ project name and location.

Write a Select statement that displays all rows that are accessible through the

view.

$8.$ Demonstrate the use of the view named department$\_$projects$($created above$)$ for

the senior manager by writing a Select statement to query the view to display all

row information for projects belonging to department $3.$

$9.$ Create a table named sales$\_$order. The table should have an identity column

named so$\_$number. There are $2$ other columns $$ the sales order value$($so$\_$value$)$

and the SSN for the employee who enters the sales order information

$($so$\_$empSSN$).$ Using INSERT statements insert $3$ rows into the sales$\_$order table.

Write a SELECT statement that displays all the information in the sales$\_$order

table.

$10$ A$.$ Write a stored procedure named replace$\_$work$\_$hours that updates the

workHours column in the Assignment table. The proce