综合题目 一

案例表格 emp

案例表格 dept

题目1:

查出至少有一个员工的部门，显示部门编号、部门名称、部门位置、部门人数。

SELECT d.deptno,d.dname,d.loc,COUNT(*) AS '员工数量' FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno GROUP BY deptno HAVING COUNT(*)>=1 SELECT d.deptno,d.dname,d.loc,COUNT(*) AS '员工数量' FROM emp e ,dept d WHERE e.deptno=d.deptno GROUP BY deptno HAVING COUNT(*)>=1

题目2:

列出受雇日期早于直接上级的所有员工，显示员工编号、员工姓名、员工职位,部门名称。

分析 :

显示的列: e1.empno, e1.ename,e1.job,d.dname查询的表: emp e1,emp e2,dept d连接条件: e1.mgr=e2.empno AND e1.deptno=d.deptno筛选条件: e1.hiredate<e2.hiredate SELECT e1.empno, e1.ename,e1.job,d.dname FROM emp e1,emp e2,dept d WHERE e1.mgr=e2.empno AND e1.deptno=d.deptno AND e1.hiredate<e2.hiredate

题目3:

查询员工表中薪资最高的员工信息

select name, max(sal) from emp; – 错误写法 查询不全面,结果只有一个

select name,sal from emp order by sal desc limit 0,1; --

多行函数和是否分组有关，如果查询结果中的数据没有经过分组，默认整个查询结果是一个组，多行函数就会默认统计当前这一个组的数据。产生的结果只有一个。

正确解法 实际表中有两个最高工资

根据最高薪资到emp表中查询, 该薪资对应的员工信息

SELECT * FROM emp WHERE sal =(SELECT MAX(sal) FROM emp)

题目4:

查询人员的部门名称和人员名称

多表查询，dname部门名称在dept表，ename人员名称在emp表 万能连接：left join

错误写法: 因为返回结果 只有两边表都有的记录会返回

SELECT e.ename,d.dname FROM emp e , dept d WHERE e.deptno=d.deptno

一般写法:写法固定无法优化

SELECT e.ename,d.dname FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno

优化写法 缩小中间查询范围,以后可以按业务需求灵活变通

SELECT e.ename, d.dname FROM ( SELECT deptno,ename FROM emp ) e LEFT JOIN ( SELECT deptno,dname FROM dept ) d ON e.deptno=d.deptno

测试题目 二

四张表 students ,teachers, courses, scores

students

teachers courses scores

题目 1

查询 至少有两名男生的班号

select class ,COUNT(*) 查询结果集当中 如果出现了,聚合函数以外的非聚和字段,一定要用分组 SELECT class,COUNT(*) FROM students WHERE ssex='男' GROUP BY class HAVING COUNT(sname)>=2

题目 2

查询scores表中的最高分的学生学号和课程号

SELECT sno,cno,degree FROM scores WHERE degree =(SELECT MAX(degree) FROM scores)

题目 3

查询‘3-105’号课程的平均分

SELECT AVG(degree) FROM scores WHERE cno='3-105' SELECT AVG(degree) FROM scores GROUP BY cno HAVING cno='3-105'

题目 4

查询各科的平均分

SELECT cno,ROUND(AVG(degree),2) FROM scores GROUP BY cno

题目 5

查询最低分大于70，最高分小于90的sno列

SELECT sno FROM scores GROUP BY sno HAVING MIN(degree)>70 AND MAX(degree)<90

题目 6

查询存在有85分以上成绩的课程cno

SELECT cno FROM scores GROUP BY cno HAVING MAX(degree)>85

题目 7

查询所有教师和同学的name、sex和birthday

正确答案

SELECT tname AS NAME,tsex AS sex,tbirthday AS birthday FROM teachers UNION SELECT sname,ssex,sbirthday FROM students

错误写法 : 会排列组合 笛卡尔查询

笛卡尔积查询：所谓笛卡尔积查询就是指，查询两张表，其中一张表有m条记录，另一张表有n条记录，查询的结果是m*n条。

SELECT t.tname ,t.tsex,t.tbirthday ,s.sname,s.ssex,s.sbirthday FROM teachers t,students s

题目 8

查询所有任课教师的tname和depart

任课老师 即是老师 且在 course表中 有 tno 讲课的老师

SELECT tname,depart FROM teachers WHERE tno IN (SELECT tno FROM courses) SELECT tname ,depart FROM teachers t INNER JOIN courses c ON t.tno=c.tno

题目 9

查询同名的同学记录

怎么知道名字重复呢？ count计数大于1

正确答案

SELECT * FROM students WHERE sname IN ( SELECT sname FROM students GROUP BY sname HAVING COUNT(*)>1 )

错误写法 :分组之后查询结果 一组只能显示一行,即分组字段或者聚合函数

SELECT *,COUNT(*) FROM students GROUP BY sname HAVING COUNT(*)>1

题目 10

查询学生的姓名和年龄

年龄=当前年份-出生年份

SELECT sname,sbirthday,YEAR(NOW())-YEAR(sbirthday) AS age FROM students

题目 11

查询男教师及其所上的课程

SELECT t.tname,c.cname FROM teachers t LEFT JOIN courses c ON t.tno=c.tno WHERE t.tsex='男'

优化查法

SELECT t.tname,c.cname FROM (SELECT * FROM teachers WHERE tsex='男') t LEFT JOIN (SELECT * FROM courses) c ON t.tno = c.tno

题目 12

查询各门课程的最高分同学的信息

错误写法

SELECT * FROM scores GROUP BY cno HAVING MAX(degree)

正确写法

SELECT s.sno,s.cno,s.degree FROM (SELECT cno,MAX(degree) dmax FROM scores GROUP BY cno) d left join (SELECT sno,cno,degree FROM scores) s ON s.cno=d.cno AND s.degree=d.dmax

另:用where in 也可以

SELECT sno,cno,degree FROM scores WHERE degree IN (SELECT MAX(degree) FROM scores GROUP BY cno

详细信息

SELECT s.sname,s.sno ,s.class ,c.cno ,c.degree FROM (SELECT sno,cno,degree FROM scores WHERE degree IN (SELECT MAX(degree) FROM scores GROUP BY cno )) c LEFT JOIN (SELECT sname ,sno ,class FROM students) s ON c.sno=s.sno

题目 13

查询课程对应的老师姓名、职称、所属系

SELECT t.tname,t.prof,t.depart FROM (SELECT * FROM teachers) t LEFT JOIN (SELECT * FROM courses) c ON t.tno = c.tno