Module2_Practice_Quiz

#1. 

For all the questions in this practice set, you will be using the Salary by Job Range Table. This is a single table titled: salary_range_by_job_classification. This table contains the following columns:

SetID
Job_Code
Eff_Date Sal_End_Date
Salary_setID
Sal_Plan
Grade
Step
Biweekly_High_Rate
Biweekly_Low_Rate
Union_Code
Extended_Step
Pay_Type



Please refer to this information to write queries to answer the questions. Are you ready to get started?

Yes, I am ready to begin.



#2. 
Find the distinct values for the extended step. The code has been started for you, but you will need to program the third line yourself before running the query.

SQL Query:
SELECT 
DISTINCT Extended_step
FROM salary_range_by_job_classification

SQL Output:
+---------------+
| Extended_Step |
+---------------+
| 0             |
| 11            |
| 6             |
| 2             |
+---------------+

5 is not a distinct value in Extended_Step



#3. 
Excluding $0.00, what is the minimum bi-weekly high rate of pay 
(please include the dollar sign and decimal point in your answer)? 
The code has been started for you, but you will need to add onto the last line of code to get the correct answer.

SQL Query:
Select 
min(Biweekly_high_Rate)
From salary_range_by_job_classification
WHERE Biweekly_high_Rate <> '$0.00'

SQL Output:
+-------------------------+
| min(Biweekly_high_Rate) |
+-------------------------+
| $100.00                 |
+-------------------------+

$100.00 



#4.
What is the maximum biweekly high rate of pay 
(please include the dollar sign and decimal point in your answer)? 
The code has been started for you, 
but you will need to add onto the last line of code to get the correct answer.

SQL Query:
SELECT 
Max(Biweekly_high_Rate)
FROM salary_range_by_job_classification

SQL Output:
+-------------------------+
| Max(Biweekly_high_Rate) |
+-------------------------+
| $9726.38                |
+-------------------------+

$9726.38  



#5.
What is the pay type for all the job codes that start with '03'? 
The code has been started for you, 
but you will need to program the fourth and fifth lines yourself 
before running the query.

SQL Query:
Select
job_code,
pay_type
FROM salary_range_by_job_classification
WHERE job_code LIKE '03%'

SQL Output:
+----------+----------+
| Job_Code | Pay_Type |
+----------+----------+
| 0380     | B        |
| 0381     | B        |
| 0382     | B        |
| 0390     | B        |
| 0395     | B        |
| 0380     | B        |
| 0381     | B        |
| 0382     | B        |
+----------+----------+

B



#6.
Run a query to find the Effective Date (eff_date) or Salary End Date (sal_end_date) for grade Q90H0?
The code has been started for you, 
but you will need to program the third through the sixth lines yourself 
before running the query.

SQL Query:
Select
grade
, eff_date
, sal_end_date
FROM
salary_range_by_job_classification
WHERE grade='Q90H0'

SQL Output:
+-------+------------------------+------------------------+
| Grade | Eff_Date               | Sal_End_Date           |
+-------+------------------------+------------------------+
| Q90H0 | 12/26/2009 12:00:00 AM | 06/30/2010 12:00:00 AM |
+-------+------------------------+------------------------+

06/30/2010



#7.



#8.
Write and run a query, with no starter code to answer this question: 
What Step are Job Codes 0110-0400? Hint: there are 6 lines to run this query.

SQL Query:
SELECT Step
FROM salary_range_by_job_classification
WHERE Job_Code BETWEEN '0110' AND  '0400'

SQL Output:
+------+
| Step |
+------+
| 1    |
| 1    |
| 1    |
| 1    |
| 1    |
| 1    |
| 1    |
| 1    |
| 1    |
| 1    |
| 1    |
| 1    |
| 1    |
| 1    |
| 1    |
| 1    |
| 1    |
| 1    |
| 1    |
| 1    |
| 1    |
| 1    |
| 1    |
| 1    |
+------+

1



#9.
Write and run a query, with no starter code or hints to answer this question: 
What is the Biweekly High Rate minus the Biweekly Low Rate for job Code 0170?

SQL Query:
SELECT (Biweekly_High_Rate-Biweekly_Low_Rate) AS Diff
FROM salary_range_by_job_classification
WHERE Job_Code = '0170'

SQL Output:
+------+
| Diff |
+------+
|    0 |
+------+

0



#10.
Write and run a query, with no starter code or hints to answer this question: 
What is the Extended Step for Pay Types M, H, and D?

SQL Query:
SELECT Extended_Step
FROM salary_range_by_job_classification
WHERE Pay_Type IN ('M','H', 'D')

SQL Output:
重置
+---------------+
| Extended_Step |
+---------------+
| 0             |
| 0             |
| 0             |
| 0             |
| 0             |
| 0             |
| 0             |
| 0             |
| 0             |
| 0             |
| 0             |
| 0             |
| 0             |
| 0             |
| 0             |
+---------------+

0



#11.
Write and run a query, with no starter code or hints to answer this question: 
What is the step for Union Code 990 and a Set ID of SFMTA or COMMN?

SQL Query:
SELECT Step
FROM salary_range_by_job_classification
WHERE Union_Code='990' AND (SetID='SFMTA' OR SetID='COMMN')

SQL Output:
+------+
| Step |
+------+
| 1    |
+------+