Create a new database with four tables: DEPARTMENTS, EMPLOYEES, PROJECTS, and, WORKS_ON. The fields for each table are:
DEPARTMENTS: ID, Name, Description
EMPLOYEES: ID, Fname, Lname, SSN, Salary, Department_ID
PROJECTS: ID, Name, Department_ID
WORKS_ON: Employee_ID, Project_ID
You will be given INSERT queries to run to add data to the tables. In addition to posting your completed database, you must write and record the results of the following queries on a separate MS Word document:
1. For each department retrieve the department number, the number of employees in the department, and the average salary for each department
2. For each project retrieve the project number, project name and number of employees
3. For each project on which more than two employees retrieve the project number, the project name and the number of employees who work on the project
4. For each project, retrieve the project number, project name, number of employees from department 1003 who work on the project
5. For each department having more than 5 employees, retrieve the department number and number of employees making over $40,000
6. Select all employees currently assigned to projects, present in alphabetical order by last name. Employees should be listed once.
7. List in last name alphabetical order all employees not currently assigned to projects. Employees should be listed once.
8. List in last name alphabetical order, all employees currently assigned to more than one project. Employees should be listed once.
Hints: Several queries need sub-queries and the IN/NOT IN clause should be used instead of the EXISTS/NOT EXISTS clause.
Please note for the week five database, no changes are to be made to the database’s structure as designed other than adding keys (primary and foreign). Field and table names should not be changed. No additional fields or tables are necessary. I have the solution queries and all I should have to do is copy and paste the solution into the query window for your database and see results. No additional data should be added to the database other than the INSERT queries provided. Points will be deducted for altering the structure of the database, adding additional data to your database, or for not properly creating your primary and foreign keys.
Create a backup of your database in SQL Server which will create a “.bak” backup file. Additionally, create a MS Word document that includes the SQL code used to create your tables, add data to your tables, and select data from your tables. Your document should include screen shots of the SQL query tab from SQL Server Management Studio for each SQL statement that shows the “Results” pane of the executed SQL code. Please submit the two files to your Assignments tab. Only the SQL used to create your database, tables, and keys, insert the required data, and the eight queries above should be included in your MS Word document. All aspects of the SQL code used should be clearly labeled followed by a screen shot of the executed SQL code. For example:
Query Number/Description of the query.
SQL Code
Screen shot.
If a query cannot be identified, no credit will be earned for that query. Partial credit will be earned for SQL code written but incorrect results of the queries execution.
Please run the INSERT SQL commands for each table together. There are four tables in this database, so your MS Word document should include four descriptions and screen shots. The INSERT SQL code should not be included in your MS Word document as it was provided.
No Union queries should be used for any queries for this class.
