SQL练习解答及深入解析

### SQL 练习解答及深入解析 #### 一、逻辑表达式等价性证明 通过真值表可以证明两个表达式在逻辑上是等价的。如下真值表： | P | Q | NOT(P) | NOT(Q) | NOT(P) OR NOT(Q) | | --- | --- | --- | --- | --- | | TRUE | TRUE | FALSE | FALSE | FALSE | | TRUE | FALSE | FALSE | TRUE | TRUE | | TRUE | UNK | FALSE | UNK | UNK | | FALSE | TRUE | TRUE | FALSE | TRUE | | FALSE | FALSE | TRUE | TRUE | TRUE | | FALSE | UNK | TRUE | UNK | TRUE | | UNK | TRUE | UNK | FALSE | UNK | | UNK | FALSE | UNK | TRUE | UNK | | UNK | UNK | UNK | UNK | UNK | 可以看到两个真值表的最后一列是相同的，这就证明了两个表达式逻辑等价。 #### 二、第 5 章练习解答 1. **提供所有员工的姓氏和首字母** - 操作步骤：使用以下 SQL 语句从 `employees` 表中查询员工的全名。 ```sql SQL> select ename ||', '||init 2 as full_name 3 from employees; ``` - 结果示例： ``` FULL_NAME --------------- SMITH, N ALLEN, JAM WARD, TF ... ``` 2. **列出所有员工的姓氏和出生日期** - 操作步骤：使用 `to_char` 函数将 `bdate` 列转换为指定格式，然后从 `employees` 表中查询。 ```sql SQL> select ename 2 , to_char(bdate,'fmMonth ddth, yyyy') 3 from employees; ``` - 结果示例： ``` ENAME TO_CHAR(BDATE,'FMMON -------- -------------------- SMITH December 17th, 1965 ALLEN February 20th, 1961 ... ``` - 注意事项：可以使用 `NLS_LANGUAGE` 参数设置语言来显示不同语言的月份名称，例如： ```sql SQL> alter session set nls_language=dutch; ``` 3. **计算特定日期 10000 天后的日期及星期几** - **计算 10000 天后的日期** - 操作步骤：使用以下 SQL 语句计算 `1954 - 08 - 11` 日期 10000 天后的日期。 ```sql SQL> select date '1954-08-11' + 10000 2 as "10,000 days" 3 from dual; ``` - 结果： ``` 10,000 days ----------- 27 - DEC - 1981 ``` - **计算该日期是星期几** - 操作步骤：使用 `to_char` 函数将日期转换为星期几的格式。 ```sql SQL> select to_char(date '1954-08-11' + 10000,'Day') 2 as "On a:" 3 from dual; ``` - 结果： ``` On a: --------- Sunday ``` 4. **使用 `NVL2` 函数重写示例** - 操作步骤：使用 `NVL2` 函数计算员工的年薪，从 `employees` 表中查询满足条件的员工。 ```sql SQL> select ename, msal, comm 2 , nvl2(comm,12*msal+comm,12*msal) as yearsal 3 from employees 4 where ename like '%T%'; ``` - 结果示例： ``` ENAME MSAL COMM YEARSAL -------- -------- -------- -------- SMITH 800 9600 MARTIN 1250 1400 16400 ... ``` 5. **使用 `CASE` 表达式重写示例** - 操作步骤：在 `SELECT` 子句和 `ORDER BY` 子句中使用 `CASE` 表达式替换 `DECODE` 函数，从 `employees` 表中查询满足条件的员工。 ```sql SQL> select job, ename 2 , case 3 when msal <= 2500 4 then 'cheap' 5 else 'expensive' 6 end as class 7 from employees 8 where bdate < date '1964-01-01' 9 order by case job 10 when 'DIRECTOR' then 1 11 when 'MANAGER' then 2 12 else 3 13 end; ``` - 结果示例： ``` JOB ENAME CLASS -------- -------- --------- DIRECTOR KING expensive MANAGER BLAKE expensive ... ``` 6. **使用 `DATE` 和 `INTERVAL` 常量重写示例** - 操作步骤：使用 `DATE` 和 `INTERVAL` 常量进行日期计算，注意非闰年的日期问题。 ```sql SQL> select date '1996-01-29' + interval '1' month as col_1 2 , date '1997-01-28' + interval '1' month as col_2 3 , date '1997-08-11' - interval '3' month as col_3 4 from dual; ``` - 结果示例： ``` COL_1 COL_2 COL_3 ----------- ----------- --------- 29 - FEB - 1996 28 - FEB - 1997 11 - MAY - 1997 ``` 7. **研究日期格式 `WW` 和 `IW` 的区别** - 操作步骤：使用以下 SQL 语句查询指定日期的 `WW` 和 `IW` 格式的周数。 ```sql SQL> 1 select date '2005-01-01' as input_date 2 , to_char(date '2005-01-01', 'ww') as ww 3 , to_char(date '2005-01-01', 'iw') as iw 4* from dual ``` - 结果示例： ``` INPUT_DATE WW IW ----------- -- -- 01 - JAN - 2005 06 07 ``` - 区别解释：`WW` 格式从 1 月 1 日开始为第 1 周，无论这一天是星期几；而 ISO 标准的 `IW` 格式，ISO 周总是从周一开始。在新年时，如果 1 月 1 日是星期五、星期六或星期日，该周属于上一年；否则，该周完全属于新的一年。 8. **使用 `REGEXP_LIKE` 重写查询** - **第一种解决方案** - 操作步骤：使用 `REGEXP_LIKE` 函数从 `history` 表中查询满足条件的 `comments`。 ```sql SQL> select comments 2 from history 3 where regexp_like(comments, '([^ ]+ ){8,}'); ``` - **第二种解决方案（使用字符类）** - 操作步骤：使用字符类改进查询，使查询更易读。 ```sql SQL> select comments 2 from history 3 where regexp_like(comments, '([[:alnum:]+[:punct:]]+[[:space:]]+){8,}'); ``` - 结果示例： ``` COMMENTS ------------------------------------------------------------ Not a great trainer; let's try the sales department! Sales also turns out to be not a success... ... ``` #### 三、第 7 章练习解答 1. **重新表述约束条件** - **解决方案 1** ```sql check ((job = 'SALESREP' and comm is not null) or (job <>'SALESREP' and comm is null) ) ``` - **解决方案 2** ```sql check ((job = 'SALESREP' or comm is null) and not (job = 'SALESREP' and comm is null) ) ``` 2. **关于 `E_DEPT_FK` 约束使用 `ALTER TABLE` 命令的原因** 由于存在“鸡和蛋”的问题，外键约束只能引用已存在的表，而 `EMPLOYEES` 和 `DEPARTM