sql随笔-面试题（一）

作者：董存阔（本博客均为原创文章，转载请注明出处）

先把测试环境搭上（本sql测试平台为 Mysql 5.5）

 

create table `s` (
	`sno` double ,
	`sname` varchar (60)
); 
insert into `s` (`sno`, `sname`) values('1','董存阔');
insert into `s` (`sno`, `sname`) values('2','董存涛');
insert into `s` (`sno`, `sname`) values('3','董存博');
---------------------
create table `c` (
	`cno` double ,
	`cname` varchar (765),
	`cteacher` varchar (765)
); 
insert into `c` (`cno`, `cname`, `cteacher`) values('1','语文','李明');
insert into `c` (`cno`, `cname`, `cteacher`) values('2','数学','大四');
insert into `c` (`cno`, `cname`, `cteacher`) values('3','英语','大喜子');
-------------------------
create table `sc` (
	`cno` double ,
	`sno` double ,
	`score` double 
); 
insert into `sc` (`cno`, `sno`, `score`) values('1','1','30');
insert into `sc` (`cno`, `sno`, `score`) values('1','2','10');
insert into `sc` (`cno`, `sno`, `score`) values('2','1','20');
insert into `sc` (`cno`, `sno`, `score`) values('2','3','100');
insert into `sc` (`cno`, `sno`, `score`) values('3','3','86');
insert into `sc` (`cno`, `sno`, `score`) values('3','1','10');

 sql语句：-- 找出没有选修过“李明”老师讲授课程的所有学生姓名

SELECT sname FROM  s WHERE sno NOT IN (
SELECT sno FROM 
(SELECT cno FROM c WHERE cteacher='李明') c
JOIN sc ON sc.cno = c.cno
)
-- 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩 
SELECT s.sname ,hx.score  FROM 
( SELECT sno, AVG(score) AS score FROM sc WHERE score <60   GROUP BY sno HAVING COUNT(sno)>=2
)hx JOIN s 
ON   hx.sno=s.sno
-- 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名
SELECT sname FROM  s JOIN (
SELECT sno FROM sc WHERE (cno=1 OR cno=2) GROUP BY sno HAVING COUNT(sno)=2
)t WHERE s.sno=t.sno 
-- 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号
SELECT t1.sno FROM 
(SELECT sno,score FROM sc WHERE cno=1) t1 JOIN (SELECT sno,score FROM sc WHERE cno=2) t2
WHERE t1.score>t2.score
-- 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩
SELECT t1.sno,t1.score FROM 
(SELECT sno,score FROM sc WHERE cno=1) t1 JOIN (SELECT sno,score FROM sc WHERE cno=2) t2
WHERE t1.score>t2.score