DEPARTMENT DATA EMPLOYEE DATA EmployeeNumber FirstName Mary Rosalie Richard George Alan 3 4 5 7 8...

 
 

Transcribed Image Text:

DEPARTMENT DATA EMPLOYEE DATA EmployeeNumber FirstName Mary Rosalie Richard George Alan 3 4 5 7 8 9 855555ES 12 13 14 15 16 17 Create the database tables in SQL or ACCESS: 18 19 20 PROJECT DATA Ken Mary Tom Heather 1000 1100 1200 1300 1400 1500 1600 Ken Linda James Rick Mike Jason Mary Tom George Jula Sam DepartmentName Accounting Administration Finance Human Resources InfoSystems Legal Production Research and Development Sales and Marketing LastName Jacobs Jackson Bandalone Legal Smith Adams Evans Brown Nguyen Sleeman Department Administration Administration Abernathy Caruthers Accounting Jones Accounting Numoto Sales and Marketing Granger Sales and Marketing Nestor Smith Jackson Jones ProjectID ProjectName Human Resources Human Resources Finance Finance Production Production Production Hayakawa Production Stewart Production InfoSystems InfoSystems Research and Development Research and Development 2019 Q3 Production Plan 2019 Q3 Marketing Plan 2019 Q3 Portfolio Analysis 2019 Q3 Tax Preparation 2019 Q4 Production Plan 2019 Q4 Marketing Plan 2019 Q4 Portfolio Analysis BudgetCode OfficeNumber BC-500-10 BLDG01-120 BC-100-10 BLDG01-210 BC-400-10 BLDG01-110 BC-300-10 BLDG01-230 BC-700-10 BLDG02-210 BC-200-10 BLDG01-220 BC-900-10 BLDG02-110 BC-800-10 BLDG02-250 BC-600-10 BLDG01-250 360-285-8500 Position CEO Admin Assistant Attorney HR3 HR1 CFO FA3 FA2 FA2 SM3 SM2 CIO IS2 CTO RD3 OPS3 OPS2 OPS2 OPS1 OPS1 Department Production Sales and Marketing Finance Accounting Production Sales and Marketing Finance Supervisor NULL 1 1 1 4 1 6 6 1 10 1 12 1 14 1 16 17 17 17 100.00 135.00 120.00 145.00 100.00 135.00 140.00 DepartmentPhone 360-285-8405 360-285-8100 360-285-8400 HULL OfficePhone Email Address 360-285-8110 Mary.Jacobs@WP.com 360-285-8120 Rosale.Jackson@WP.com 360-285-8210 Richard.Bandalone@WP.com 360-285-8310 George.Smith@WP.com 360-285-8320 Alan.Adams@WP.com 360-285-8410 Ken.Evans@WP.com 360-285-8420 Mary Abernathy@WP.com 360-285-8430 Tom.Caruthers@WP.com 360-285-8440 Heather.Jones@WP.com 360-285-8510 Ken. Numoto@WP.com 360-285-8520 Linda. Granger@WP.com 360-285-8610 James Nestor@WP.com Rick.Brown@WP.com 360-285-8710 Mike.Nguyen@WP.com 360-285-8720 Jason.Sleeman@WP.com 360-285-8810 Mary.Smith@WP.com 360-285-8820 Tom.Jackson@WP.com 360-285-8830 George.Jones@WP.com Julia.Hayakawa@WP.com Sam.Stewart@WP.com HULL HULL 360-285-8300 360-285-8600 360-285-8200 360-285-8800 360-285-8700 MaxHours StartDate EndDate 2019-05-10 2019-06-15 2019-05-10 2019-06-15 2019-07-05 2019-07-25 2019-08-10 2019-10-15 2019-08-10 2019-09-15 2019-08-10 2019-09-15 2019-10-05 NULL ASSIGNMENT DATA ProjectID EmployeeNumber 1000 1000 1000 1000 1000 1100 1100 1100 1100 1200 1200 1200 1200 1300 1300 1300 1300 1400 1400 1400 1400 1400 1500 1500 1500 1500 1600 1600 1600 1600 WOVO WES5591 6 10 16 17 6 10 11 3 3 6 8 9 1 6 10 16 17 1 6 10 11 3678 HoursWorked 30.00 50.00 50.00 75.00 75.00 30.00 75.00 55.00 55.00 20.00 40.00 45.00 45.00 25.00 40.00 50.00 50.00 30.00 50.00 50.00 75.00 75.00 30.00 75.00 55.00 55.00 20.00 40.00 45.00 45.00 1. Using access or SQL DBMS of your choice, create and run queries to answer the following questions: B. C. D. E. F. G. H. 1. J. K A. What projects are in the PROJECT table? Show all the information for each project. What are the ProjectID. ProjectName, StartDate, and EndDate values of projects in the PROJECT table? What projects in the PROJECT table started before August 1, 2019? Show all the information for each project. What projects in the PROJECT table have not been completed? Show all the information for each project. (WHERE EndDate IS NULL) Who are the employees assigned to each project? Show ProjectID EmployeeNumber. LastName, FirstName, and Office Phone. Who are the employees assigned to each project? Show ProjectID. ProjectName. and Department. Show EmployeeNumber. LastName, FirstName, and Office Phone Who are the employees assigned to each project? Show ProjectID. ProjectName. Department, and DepartmentPhone. Show EmployeeNumber, LastName, FirstName, and OfficePhone. Sort by ProjectID in ascending order. Who are the employees assigned to projects run by the Sales and Marketing Department? Show ProjectID. ProjectName, Department, and DepartmentPhone. Show EmployeeNumber. LastName, FirstName, and OfficePhone. Sort by ProjectID in ascending order. How many projects are being run by the Sales and 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 Sales and 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. DEPARTMENT DATA EMPLOYEE DATA EmployeeNumber FirstName Mary Rosalie Richard George Alan 3 4 5 7 8 9 855555ES 12 13 14 15 16 17 Create the database tables in SQL or ACCESS: 18 19 20 PROJECT DATA Ken Mary Tom Heather 1000 1100 1200 1300 1400 1500 1600 Ken Linda James Rick Mike Jason Mary Tom George Jula Sam DepartmentName Accounting Administration Finance Human Resources InfoSystems Legal Production Research and Development Sales and Marketing LastName Jacobs Jackson Bandalone Legal Smith Adams Evans Brown Nguyen Sleeman Department Administration Administration Abernathy Caruthers Accounting Jones Accounting Numoto Sales and Marketing Granger Sales and Marketing Nestor Smith Jackson Jones ProjectID ProjectName Human Resources Human Resources Finance Finance Production Production Production Hayakawa Production Stewart Production InfoSystems InfoSystems Research and Development Research and Development 2019 Q3 Production Plan 2019 Q3 Marketing Plan 2019 Q3 Portfolio Analysis 2019 Q3 Tax Preparation 2019 Q4 Production Plan 2019 Q4 Marketing Plan 2019 Q4 Portfolio Analysis BudgetCode OfficeNumber BC-500-10 BLDG01-120 BC-100-10 BLDG01-210 BC-400-10 BLDG01-110 BC-300-10 BLDG01-230 BC-700-10 BLDG02-210 BC-200-10 BLDG01-220 BC-900-10 BLDG02-110 BC-800-10 BLDG02-250 BC-600-10 BLDG01-250 360-285-8500 Position CEO Admin Assistant Attorney HR3 HR1 CFO FA3 FA2 FA2 SM3 SM2 CIO IS2 CTO RD3 OPS3 OPS2 OPS2 OPS1 OPS1 Department Production Sales and Marketing Finance Accounting Production Sales and Marketing Finance Supervisor NULL 1 1 1 4 1 6 6 1 10 1 12 1 14 1 16 17 17 17 100.00 135.00 120.00 145.00 100.00 135.00 140.00 DepartmentPhone 360-285-8405 360-285-8100 360-285-8400 HULL OfficePhone Email Address 360-285-8110 Mary.Jacobs@WP.com 360-285-8120 Rosale.Jackson@WP.com 360-285-8210 Richard.Bandalone@WP.com 360-285-8310 George.Smith@WP.com 360-285-8320 Alan.Adams@WP.com 360-285-8410 Ken.Evans@WP.com 360-285-8420 Mary Abernathy@WP.com 360-285-8430 Tom.Caruthers@WP.com 360-285-8440 Heather.Jones@WP.com 360-285-8510 Ken. Numoto@WP.com 360-285-8520 Linda. Granger@WP.com 360-285-8610 James Nestor@WP.com Rick.Brown@WP.com 360-285-8710 Mike.Nguyen@WP.com 360-285-8720 Jason.Sleeman@WP.com 360-285-8810 Mary.Smith@WP.com 360-285-8820 Tom.Jackson@WP.com 360-285-8830 George.Jones@WP.com Julia.Hayakawa@WP.com Sam.Stewart@WP.com HULL HULL 360-285-8300 360-285-8600 360-285-8200 360-285-8800 360-285-8700 MaxHours StartDate EndDate 2019-05-10 2019-06-15 2019-05-10 2019-06-15 2019-07-05 2019-07-25 2019-08-10 2019-10-15 2019-08-10 2019-09-15 2019-08-10 2019-09-15 2019-10-05 NULL ASSIGNMENT DATA ProjectID EmployeeNumber 1000 1000 1000 1000 1000 1100 1100 1100 1100 1200 1200 1200 1200 1300 1300 1300 1300 1400 1400 1400 1400 1400 1500 1500 1500 1500 1600 1600 1600 1600 WOVO WES5591 6 10 16 17 6 10 11 3 3 6 8 9 1 6 10 16 17 1 6 10 11 3678 HoursWorked 30.00 50.00 50.00 75.00 75.00 30.00 75.00 55.00 55.00 20.00 40.00 45.00 45.00 25.00 40.00 50.00 50.00 30.00 50.00 50.00 75.00 75.00 30.00 75.00 55.00 55.00 20.00 40.00 45.00 45.00 1. Using access or SQL DBMS of your choice, create and run queries to answer the following questions: B. C. D. E. F. G. H. 1. J. K A. What projects are in the PROJECT table? Show all the information for each project. What are the ProjectID. ProjectName, StartDate, and EndDate values of projects in the PROJECT table? What projects in the PROJECT table started before August 1, 2019? Show all the information for each project. What projects in the PROJECT table have not been completed? Show all the information for each project. (WHERE EndDate IS NULL) Who are the employees assigned to each project? Show ProjectID EmployeeNumber. LastName, FirstName, and Office Phone. Who are the employees assigned to each project? Show ProjectID. ProjectName. and Department. Show EmployeeNumber. LastName, FirstName, and Office Phone Who are the employees assigned to each project? Show ProjectID. ProjectName. Department, and DepartmentPhone. Show EmployeeNumber, LastName, FirstName, and OfficePhone. Sort by ProjectID in ascending order. Who are the employees assigned to projects run by the Sales and Marketing Department? Show ProjectID. ProjectName, Department, and DepartmentPhone. Show EmployeeNumber. LastName, FirstName, and OfficePhone. Sort by ProjectID in ascending order. How many projects are being run by the Sales and 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 Sales and 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.

Answer rating: 100% (QA)

To create the database tables in SQL you would execute a series of CREATE TABLE statements based on the structure outlined in your images Here Ill provide SQL statements to create tables and sample qu... View the full answer