SQL WEEK2 Assignment

1. 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.

SELECT distinct Extended_step FROM salary_range_by_job_classification

错误原因:没看清楚题目，以为是在找一切column的distinct，后面发现，只要根据格式添加到最后FROM 什么table即可

--------------- | Extended_Step | --------------- | 0 | | 11 | | 6 | | 2 | ---------------

2. 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 on to the last line of code to get the correct answer.

Select min(Biweekly_high_Rate) From salary_range_by_job_classification WHERE Biweekly_high_Rate <> '$0.00' ORDER BY Biweekly_High_Rate ASC;

错误的原因排除$0.00需要用到 <>'$0.00' ，最后用ORDER BY找到最小值进行排序

You are selecting the min Biweekly_high_Rate FROM the table Using WHERE <> '$0.00' you are excluding values that are equal to zero.

Then you are ORDERING by the Biweekly_High_Rate to get the first value correct.

3.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 on to the last line of code to get the correct answer.

SELECT Max(Biweekly_high_Rate) FROM salary_range_by_job_classification ORDER BY Biweekly_High_Rate ASC;

解析：记住SELECT-FROM，最后用ORDER BY ... ASC找到答案

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.

SELECT Max(Biweekly_high_Rate) FROM salary_range_by_job_classification ORDER BY Biweekly_High_Rate ASC;

解析：ASC和DEC的格式就是ORDER BY ... ASC/DEC

5. What is the pay type for all the job codes that start with '03'?

Select job_code, pay_type FROM salary_range_by_job_classification where Job_Code like '%'

分析：如果你想搜索start with 03的东西，就是where ... like "%...%" 这里有两个百分比符号

6.Run a query to find the Effective Date (eff_date) or Salary End Date (sal_end_date) for grade Q90H0?

Select grade, eff_date, sal_end_date From salary_range_by_job_classification WHERE grade = 'Q90H0'

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

分析:题目需要什么？select出来，再用where定位找

7.Write and run a query, with no starter code to answer this question: What Step are Job Codes 0110-0400?

select Step ,Job_Code from salary_range_by_job_classification where Job_Code between '0100' and '0400'

分析:题目要求是 0110-0400的数据需要between... and... 格式写

8. 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?

select job_code ,Biweekly_High_Rate ,Biweekly_Low_Rate ,(Biweekly_High_Rate - Biweekly_Low_Rate ) as DIFFERENCE_RATE from salary_range_by_job_classification where job_code = '0170'

分析：第一次使用加减乘除，首先需要自己创建一个column，A B, A-B,A*B,A/B之后 AS ...

最后可以看到新的column就在最后一列

---------- -------------------- ------------------- ----------------- | Job_Code | Biweekly_High_Rate | Biweekly_Low_Rate | DIFFERENCE_RATE | ---------- -------------------- ------------------- ----------------- | 0170 | $4142.00 &nbp;       | $4142.00          |               0 | +----------+--------------------+-------------------+-----------------+  

9. 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? 

SELECT Pay_Type,
Extended_Step 
from salary_range_by_job_classification
where Pay_Type IN ('M','H','D')

解析：同时需要M H D三个pay type的数据，就用in