cs186-proj1笔记

网站：Your Tasks - CS186 Projects

1,SQL note: You should compute slg properly as a floating point number---you'll need to figure out how to convince SQL to do this!    

  SELECT playerid, yearid, AB, (H + H2B + 2*H3B + 3*HR + 0.0)/(AB + 0.0)
  FROM batting

2，

SELECT CAST(3.14 AS INT);

向下取整，若值为3.80 ，结果为3

SELECT ROUND(3.5);

四舍五入，返回为4

SELECT birthyear, ROUND(avgheight, 4), count FROM q1iii;

avgheight取小数点后四位四舍五入

3，q4ii的测试指令

SELECT * FROM q4ii WHERE binid <> 9;
WITH max_salary AS (SELECT MAX(salary) AS salary FROM salaries)
        SELECT binid, low,
            ((CASE WHEN high >= salary THEN '' ELSE 'not ' END) ||
                    'at least ' || salary) AS high, count
        FROM q4ii, max_salary WHERE binid = 9;

        binid是按照工资划分的10个范围的序号，从0到9。当binid=9的工资上限大于最大薪水，答案测试会返回 at least ’salary‘这种情况。

   WITH 是 SQL 中的一个关键字，用于定义一个或多个临时命名的结果集，通常称为Common Table Expression（CTE，通用表达式）。同时注意CASE和’||‘的用法。