进阶6 连接查询（sql92内连接)测试题

-------------------------------------（1）测试题---------------------------------------

一、显示员工表的最大工资，工资平均值

SELECT MAX(salary), AVG(salary)
FROM employees;

二、查询员工表的employee_id，job_id，last_name。按department_id降序，salary升序

SELECT employee_id, job_id, last_name
FROM employees
ORDER BY department_id DESC, salary ASC;

三、查询员工表的job_id中包含 a和e的，并且a在e的前面

SELECT job_id
FROM employees
WHERE job_id LIKE ‘%a%e%’;

四、已知表student，里面有id（学号），name，gradeid（年级编号）

已知表grade，里面有id（年级编号），name（年纪名）

已知表result，里面有id，score，studentNo（学号）

要求：查询姓名、年级名、成绩

SELECT s.name, g.name, r.score
FROM student s, grade g, result r
WHERE s.gradeid = g.id
AND s.id = r.studentNo;

五、显示当前日期。以及去前后空格，截取子字符串的函数

（1）去前后空格

trim(’’)：去前后空格

trim(字符 from ‘’)：去指定的前后字符

（2）截取子字符串（起始索引从1开始）

substr(str，起始索引) -------------- 截取“起始索引到最后”的字符

substr(str, 起始索引， 长度) -------------- 截取“从起始索引开始，指定长度”的字符

SELECT CURDATE() 当前日期（仅包含年月日）;
SELECT NOW() 当前日期（年月日和时间）;

-------------------------------------（2）作业---------------------------------------

（1）显示所有员工的姓名，部门号和部门名称

SELECT last_name, d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

（2）查询90号部门员工的job_id和90号部门的location_id

SELECT job_id, location_id
FROM employees e, departments d
WHERE d.department_id = e.department_id
AND d.department_id = 90;

（3）选择所有有奖金的员工的 last_name，department_name，location_id，city

SELECT last_name, department_name, l.location_id, city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND commission_pct IS NOT NULL;

（4）选择city在Toronto工作的员工的 last_name，job_id，department_id，department_name

SELECT last_name, job_id, d.department_id, department_name
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND l.location_id = d.location_id
AND city = ‘Toronto’;

（5）查询每个工种、每个部门的部门名、工种名和最低的工资

SELECT department_name, job_title, MIN(salary)
FROM departments d, jobs j, employees e
WHERE e.job_id = j.job_id
AND e.department_id = d.department_id
GROUP BY job_title, department_name;

（6）查询每个国家下的部门个数大于2的国家编号

step1:有下述结果可知。

（1）两个表连接后，department_id没有重复的，即可用count(*)统计表行数的方法来统计部门数

（2）先执行from表名，最后执行的select后的字段

分析：

（1）因为本来departments这张表中的department_id就是没有重复的，所以合并后也是没有重复的

（2）但是location_id本来就是重复的，所以合并后也是重复的。

简单解释：

（1）count(*)是查在按照国家分组后，每一组有多少行，而每一行代表一个部门（不重复）。所以间接相等

SELECT *
FROM departments d, locations l
WHERE d.location_id = l.location_id;

step2：分组时，1对多

SELECT country_id, COUNT(*) 部门个数
FROM departments d, locations l
WHERE d.location_id = l.location_id
GROUP BY country_id;

step3：最终结果

SELECT country_id, COUNT() 部门个数 # step5
FROM locations l, departments d # step1
WHERE l.location_id = d.location_id # step2
GROUP BY country_id # step3
HAVING COUNT() > 2; # step4（order by放在最后，limit除外）

（7）选择指定员工的姓名、员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

employees Emp manager Mgr

kochhar 101 king 100

SELECT e.last_name, e.employee_id, m.last_name, m.employee_id
FROM employees e, employees m
WHERE e.manager_id = m.employee_id
AND e.last_name = ‘kochhar’;