经典sql50题

首先必须要吐槽一下，网上流传的sql50题！！！

类似下面

Course(C#,Cname,T#) 课程表

CREATE TABLE course (

  cid varchar(10) NOT NULL,

  cName varchar(10) DEFAULT NULL,

  tid int(20) DEFAULT NULL,

  PRIMARY KEY (cid)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

通过上面的表创建语句可以看出，在course表中的字段有cid，cName，tid这三个字段，但是网上流传的经典sql50题中，在做查询的时候竟然使用C#以及T#等等这些在表中根本就不存在的字段进行查询，也是醉了~~~

对于Course(C#,Cname,T#) ，我的理解是对表中字段的解释，例如C#代表，表Course中的字段cid，并且是主键，若在查询的时候使用C#，就会报错，因为C#在表Course中根本不存在！！！

所以还是自己整理吧~~~~

sql50题会逐步更新~~

创建表语句

表student：

CREATE TABLE `student` (
  `sid` varchar(10) NOT NULL,
  `sName` varchar(20) DEFAULT NULL,
  `sAge` datetime DEFAULT '1980-10-12 23:12:36',
  `sSex` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`sid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表teacher：

CREATE TABLE `teacher` (
  `tid` int(10) DEFAULT NULL,
  `tName` varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

课程表course：

CREATE TABLE `course` (
  `cid` varchar(10) NOT NULL,
  `cName` varchar(10) DEFAULT NULL,
  `tid` int(20) DEFAULT NULL,
  PRIMARY KEY (`cid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

成绩表sc：

CREATE TABLE `sc` (
  `sid` varchar(10) DEFAULT NULL,
  `cid` varchar(10) DEFAULT NULL,
  `score` int(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO `teacher` (`tid`, `tName`) VALUES ('1', '田青风');
INSERT INTO `teacher` (`tid`, `tName`) VALUES ('2', '赵德林');
INSERT INTO `teacher` (`tid`, `tName`) VALUES ('3', '马老师');


INSERT INTO `test`.`student` (`sid`, `sName`, `sAge`, `sSex`) VALUES ('1', '张三丰', '17', '男');
INSERT INTO `test`.`student` (`sid`, `sName`, `sAge`, `sSex`) VALUES ('2', '张无极', '18', '男');
INSERT INTO `test`.`student` (`sid`, `sName`, `sAge`, `sSex`) VALUES ('3', '李奎', '19', '女');
INSERT INTO `test`.`student` (`sid`, `sName`, `sAge`, `sSex`) VALUES ('4', '李元宝', '20', '女');


INSERT INTO `sc` (`sid`, `cid`, `score`) VALUES ('1', '001', '80');
INSERT INTO `sc` (`sid`, `cid`, `score`) VALUES ('1', '002', '60');
INSERT INTO `sc` (`sid`, `cid`, `score`) VALUES ('1', '003', '75');
INSERT INTO `sc` (`sid`, `cid`, `score`) VALUES ('2', '001', '85');
INSERT INTO `sc` (`sid`, `cid`, `score`) VALUES ('2', '002', '70');
INSERT INTO `sc` (`sid`, `cid`, `score`) VALUES ('3', '004', '100');
INSERT INTO `sc` (`sid`, `cid`, `score`) VALUES ('3', '001', '90');
INSERT INTO `sc` (`sid`, `cid`, `score`) VALUES ('3', '002', '55');
INSERT INTO `sc` (`sid`, `cid`, `score`) VALUES ('4', '002', '65');
INSERT INTO `sc` (`sid`, `cid`, `score`) VALUES ('4', '003', '60');


INSERT INTO `course` (`cid`, `cName`, `tid`) VALUES ('001', '企业管理', '3');
INSERT INTO `course` (`cid`, `cName`, `tid`) VALUES ('002', '马克思', '3');
INSERT INTO `course` (`cid`, `cName`, `tid`) VALUES ('003', 'UML', '2');
INSERT INTO `course` (`cid`, `cName`, `tid`) VALUES ('004', '数据库', '1');

INSERT INTO `course` (`cid`, `cName`, `tid`) VALUES ('005', '英语', '1');

练习：

1.查询“某1”课程比“某2”课程成绩高的所有学生的学号；

#课程001的所有学生和成绩
(select sid,score from sc where cid = '001') a
#课程002的所有学生和成绩
(select sid,score from sc where cid = '002') b

#查询的条件(针对的是同一个学生)
where a.score > b.score AND a.sid = b.sid

#查询的是课程001比002分数高的所有学生的编号
SELECT a.sid

#整个的查询语句

SELECT a.sid from (SELECT sid,score from sc where sc.cid ='001') a,(SELECT sid,score from sc where sc.cid = '002') b where a.score > b.score AND a.sid = b.sid;

2.查询平均成绩大于60分的同学的学号和平均成绩；2.查询平均成绩大于60分的同学的学号和平均成绩；

#查询条件：平均成绩大于60分,但需要根据学号进行分组
GROUP BY sid HAVING avg(score) > 60 
#查询的是满足条件的所有学生的学号和平均成绩
SELECT sid,avg(score) from sc

#整个查询语句

SELECT sid,avg(score) from sc GROUP BY sid HAVING avg(score) > 60

3.查询所有同学的学号、姓名、选课数、总成绩

#两表关联的查询，涉及到的表有student,sc

SELECT s.sid,s.sName,COUNT(sc.cid),SUM(sc.score) from student s LEFT OUTER JOIN sc on s.sid = sc.sid GROUP BY s.sid

4.查询姓“李”的老师的个数；

#模糊查询

select COUNT(0) from teacher where tName LIKE '李%'

5.查询没学过“叶平”老师课的同学的学号、姓名；
#涉及到的是三表关联，查询田青风老师的学生

SELECT DISTINCT(sc.sid ) from sc,course,teacher where course.tid=teacher.tid and course.cid=sc.cid AND teacher.tName ='田青风'

#查询学生表中不是田青风老师的学生

SELECT student.sid,student.sName from student where sid not in（。。。）

#整条sql语句

SELECT student.sid,student.sName from student where sid not in(SELECT DISTINCT(sc.sid ) from sc,course,teacher where course.tid=teacher.tid and course.cid=sc.cid AND teacher.tName ='田青风')

6.查询学过“企业管理”并且也学过编号“数据库”课程的同学的学号、姓名；
#查询课程“企业管理”的所有学生和编号
select student.sid,student.sName from student,course,sc where cName='企业管理' and student.sid=sc.sid and course.cid=sc.cid

#查询课程“数据库”所有学生和编号
select student.sid,student.sName from student,course,sc where cName="数据库" and student.sid=sc.sid and course.cid=sc.cid

#整条查询语句

select a.sid,a.Sname from (select student.sid,student.sName from student,course,sc where cName='企业管理' and student.sid=sc.sid and course.cid=sc.cid) a,(select student.sid,student.sName from student,course,sc where cName="数据库" and student.sid=sc.sid and course.cid=sc.cid) b where a.sid=b.sid

7.查询课程编号“”的成绩比课程编号“”课程低的所有同学的学号、姓名；

#查询课程企业管理的所有同学的学号，姓名

select student.sid,student.sName from student,sc,course where sc.cid='001' and student.sid=sc.sid and sc.cid=course.cid

#查询课程UML的所有同学的学号，姓名
select student.sid,student.sName FROM student,sc,course where sc.cid='002' AND student.sid=sc.sid and sc.cid=course.cid

#整条查询语句 
select a.sid,a.sName from (select student.sid,student.sName,sc.score from student,sc where student.sid=sc.sid AND sc.cid = '001'

) a,(select student.sid,student.sName,sc.score FROM student,sc where student.sid=sc.sid AND sc.cid= '002') b where a.score > b.score and a.sid = b.sid

8.查询学过“叶平”老师所教的所有课的同学的学号、姓名

#这条语句就稍微复杂一点，语句中有两个条件，是对查询结果的再次筛选，主要是为了满足“老师所教的所有课”

SELECT sid,sName from student where sid in(SELECT sid from course,sc,teacher where teacher.tName ='田青风' and course.cid=sc.cid and course.tid=teacher.tid GROUP BY sid HAVING COUNT(sc.cid)=(SELECT count(cid) from course,teacher where teacher.tName='田青风' and teacher.tid=course.tid))

9.查询所有课程成绩小于分的同学的学号、姓名；

select sid,sName from student WHERE sid not in(SELECT student.sid from student,sc where student.sid=sc.sid and sc.score>90);

10.查询没有学全所有课的同学的学号、姓名

select sid,sName from student where sid not in (select student.sid from student,sc WHERE student.sid=sc.sid GROUP BY student.sid HAVING COUNT(sc.cid) < 5)