[SOLVED] 代写 SQL database ITEC-200 SQL Queries Homework Instructions

ITEC-200 SQL Queries Homework Instructions
Technical Learning Objectives
• Be able to create simple and complex queries using Structured Query Language (SQL) that answer important business questions.
Business Learning Objectives
• Understand the concept of querying (asking questions) of a database, an essential skill for business analysts.
Assignment Overview
This assignment uses a company’s database containing records from: Human Resources (employee names and job titles), Payroll (pay rates for given jobs), Work Codes (types of billable activities, e.g., Research & Development), and Timesheets (which employees billed time for which type of work). Start by downloading and renaming the Access file to: ITEC200-00X_ last name_first name_Homework1 where 00X = your section number. You should review slides and notes from the SQL Database Queries lab for help with table joins and aggregate functions.
Notes on Pay Bands
It is very common in businesses and governments to have pay bands or a range of salaries for a given title or position. These are often titles (e.g., Project Leader) with a qualifier that identifies the pay level within that title (e.g., a Project Leader 2 is paid more than a Project Leader 1). US Federal Government employees are referred as General Schedule (abbreviated GS) followed by a number (1-15) and then a “Step” (1-10) that identifies the salary within the payband. Looking at the partial table below, you can see that a GS-1 salary ranges from a low of $19,048 (Step 1) to a high of $23,236 (Step 10).
Table source: https://www.opm.gov/policy-data-oversight/pay-leave/salaries-wages/salary- tables/pdf/2019/GS.pdf
As a career note, in your own job search, you should understand what pay bands you can expect (some information is available at: https://www.glassdoor.com/index.htm) and how managers use them (see: https://www.payscale.com/compensation-today/2012/11/mangers- guide-to-pay-bands-and-pay-increases).
Database Design and Relationships
1. Create Primary Key(s) as appropriate for all tables. In TIMESHEETS, employees can only log time to a given WORKCODE once per day (e.g., if someone works on R&D for 2 hours in the morning and 2 hours in the afternoon, it’s entered on the timesheet as 4 hours for R&D).
2. Create relationships between tables and enforce referential integrity.
GS
Step 1
Step 2
Step 3
Step 4
Step 5
Step 6
Step 7
Step 8
Step 9
Step 10
1
19,048
19,686
20,320
20,949
21,583
21,953
22,579
23,211
23,236
23,827
2
21,417
21,927
22,636
23,236
23,497
24,188
24,879
25,570
26,261
26,952
3
23,368
24,147
24,926
25,705
26,484
27,263
28,042
28,821
29,600
30,379
1

ITEC-200 SQL Queries Homework Instructions
Queries
Create the following queries using the names shown (i.e., including the leading number):
1-NUMBER OF EMPLOYEES BY DEPARTMENT: answers the question, “How many employees work for each department?” Include fields: NUMBEROFEMPLOYEES, DEPARTMENT.
2-TOTALPAY BY DEPARTMENT: answers the question, “How much total salaries are paid to each department?” Include fields: DEPARTMENT and a calculated field called TOTALPAY defined as HOURS*PAYBYHOUR.
3-TOTALPAY BY EMPLOYEE: answers the question, “What are the salaries paid to each employee?” Include fields: EMPLOYEEID, DEPARTMENT, WORKDATE, WORKCODE, WORKCODES. WORKNAME, HOURS, PAYBYHOUR, and a calculated field called TOTALPAY defined as HOURS*PAYBYHOUR.
4-TOTALPAY BY JOBTITLE: answers the question, “How much total salaries are paid to each job title (e.g., Business Analyst, Business Consultant)?” Include fields: JOBTITLE and a calculated field called TOTALPAY defined as HOURS*PAYBYHOUR.
5-TOTALPAY BY PAYBAND: answers the question, “How much total salaries are paid for each pay band?” Include fields: PAYBAND and a calculated field called TOTALPAY defined as HOURS*PAYBYHOUR.
6-TOTALSALARIES: answers the question, “What is the total amount salaries paid”?
2