sql语句练习题及答案（01课程比02课程成绩高）

表结构如图：

表结构：

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `Cid` varchar(10) DEFAULT NULL,
  `Cname` varchar(10) DEFAULT NULL,
  `Tid` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('01', '语文', '02');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `SCid` varchar(10) DEFAULT NULL,
  `Cid` varchar(10) DEFAULT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('01', '01', '80');
INSERT INTO `sc` VALUES ('01', '02', '90');
INSERT INTO `sc` VALUES ('01', '03', '99');
INSERT INTO `sc` VALUES ('02', '01', '70');
INSERT INTO `sc` VALUES ('02', '02', '60');
INSERT INTO `sc` VALUES ('02', '03', '80');
INSERT INTO `sc` VALUES ('03', '01', '80');
INSERT INTO `sc` VALUES ('03', '02', '80');
INSERT INTO `sc` VALUES ('03', '03', '80');
INSERT INTO `sc` VALUES ('04', '01', '50');
INSERT INTO `sc` VALUES ('04', '02', '30');
INSERT INTO `sc` VALUES ('04', '03', '20');
INSERT INTO `sc` VALUES ('05', '01', '76');
INSERT INTO `sc` VALUES ('05', '02', '87');
INSERT INTO `sc` VALUES ('06', '01', '31');
INSERT INTO `sc` VALUES ('06', '03', '34');
INSERT INTO `sc` VALUES ('07', '02', '89');
INSERT INTO `sc` VALUES ('07', '03', '98');

-- ----------------------------
-- Table structure for stu
-- ----------------------------
DROP TABLE IF EXISTS `stu`;
CREATE TABLE `stu` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `kecheng` varchar(255) DEFAULT NULL,
  `score` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of stu
-- ----------------------------
INSERT INTO `stu` VALUES ('1', '张三', '语文', '81');
INSERT INTO `stu` VALUES ('2', '张三', '数学', '75');
INSERT INTO `stu` VALUES ('3', '李四', '语文', '76');
INSERT INTO `stu` VALUES ('4', '李四', '数学', '90');
INSERT INTO `stu` VALUES ('5', '王五', '语文', '81');
INSERT INTO `stu` VALUES ('7', '王五', '数学', '100');
INSERT INTO `stu` VALUES ('8', '王五', '数学', '81');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `Sid` varchar(10) DEFAULT NULL,
  `Sname` varchar(10) DEFAULT NULL,
  `Sage` date DEFAULT NULL,
  `Ssex` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('01', '赵雷', '1990-01-01', '男');
INSERT INTO `student` VALUES ('02', '钱电', '1990-12-21', '男');
INSERT INTO `student` VALUES ('03', '孙风', '1990-05-20', '男');
INSERT INTO `student` VALUES ('04', '李云', '1990-08-06', '男');
INSERT INTO `student` VALUES ('05', '周梅', '1991-12-01', '女');
INSERT INTO `student` VALUES ('06', '吴兰', '1992-03-01', '女');
INSERT INTO `student` VALUES ('07', '郑竹', '1989-07-01', '女');
INSERT INTO `student` VALUES ('08', '王菊', '1990-01-20', '女');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `Tid` varchar(10) DEFAULT NULL,
  `Tname` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('01', '张三');
INSERT INTO `teacher` VALUES ('02', '李四');
INSERT INTO `teacher` VALUES ('03', '王五');

 

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

 

SELECT stu.*,c1.score '01课程',c2.score '02课程',c3.score '03课程' from (SELECT score,Scid from sc

WHERE cid=01) c1,(SELECT score,Scid from sc

WHERE Cid=02) c2,student stu,(SELECT score,Scid from sc

WHERE Cid=02) c3

WHERE c1.Scid=c2.scid and stu.Sid=c1.scid and c3.scid=c2.scid and

c1.score>c2.score

 

--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

 

SELECT stu.*,c1.score '01课程',c2.score '02课程',c3.score '03课程' from (SELECT score,Scid from sc

WHERE cid=01) c1,(SELECT score,Scid from sc

WHERE Cid=02) c2,student stu,(SELECT score,Scid from sc

WHERE Cid=02) c3

WHERE c1.Scid=c2.scid and stu.Sid=c1.scid and c3.scid=c2.scid and

c1.score<c2.score

 

--3、查询平均成绩大于等于70分的同学的学生编号和学生姓名和平均成绩

SELECT student.sid,student.sname,AVG(sc.score)

FROM student,sc

WHERE student.sid=sc.scid GROUP BY student.sid

HAVING AVG(sc.score)>=70

 

 

--4、查询平均成绩小于70分的同学的学生编号和学生姓名和平均成绩

 

SELECT student.sid,student.sname,AVG(sc.score)

FROM student,sc

WHERE student.sid=sc.scid GROUP BY student.sid

HAVING AVG(sc.score)<70

 

--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

 

SELECT student.sid,student.sname,COUNT(sc.cid),sum(sc.score)

FROM student,sc

WHERE student.sid=sc.scid GROUP BY student.sid

 

--6、查询"李"姓老师的数量

 

SELECT count(tname) FROM teacher where tname like "李%"

 

 

--7、查询学过"张三"老师授课的同学的信息

 

SELECT student.* from student,teacher,sc

WHERE student.sid=sc.scid and teacher.tid=sc.cid and teacher.tname ="张三"

group BY student.sname

 

--8、查询没学过"张三"老师授课的同学的信息

 

SELECT student.* from student,teacher,sc

WHERE teacher.tid=sc.cid

and student.sname not in (SELECT student.sname from student,teacher,sc

WHERE student.sid=sc.scid and teacher.tid=sc.cid and teacher.tname ="张三")

group BY student.sname

 

 

--9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

 

SELECT * FROM student WHERE sid in (SELECT s1.scid FROM (SELECT scid

FROM sc WHERE cid='01') s1,(SELECT scid FROM sc WHERE cid='02') s2

WHERE s1.scid=s2.scid)

 

 

--10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

 

SELECT student.* FROM student

where sid =(SELECT scid from sc where scid not in (SELECT scid FROM sc WHERE cid='02') GROUP BY scid )

 

--11、查询没有学全所有课程的同学的信息 ok

select stu.*
from student stu LEFT JOIN sc 
on sc.scid = stu.sid 
GROUP BY sc.scid having COUNT(sc.cid) < (select COUNT(*) from course)

--12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息  ok

SELECT student.* FROM student,sc 
where sc.scid=student.sid
and sc.cid in (SELECT cid FROM sc WHERE sc.scid =01)
and not student.sid=01
GROUP BY student.sid

--13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 ok

SELECT * from (SELECT student.* from
(select Count(cid) cc,scid from sc where scid not in (01) GROUP BY scid) A 
left JOIN student on A.scid=student.sid and A.cc=(SELECT count(cid) from sc where sc.scid=01)) d
where sid is not null

--14、查询没学过"张三"老师讲授的任一门课程的学生姓名   ok

SELECT student.sname from student,teacher,sc
WHERE teacher.tid=sc.cid 
and student.sname not in (SELECT student.sname from student,teacher,sc
WHERE student.sid=sc.scid and teacher.tid=sc.cid and teacher.tname ="张三")
group BY student.sname

--15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩  ok

select stu.sid,stu.sname,b.ss
from student stu 
right JOIN (
    SELECT scid,avg(score) as ss from sc where score <60
GROUP BY scid HAVING count(score)>=2) b
on b.scid = stu.sid 

--16、检索"01"课程分数小于60，按分数降序排列的学生信息  ok

SELECT s.*,sc.score from student s,sc
where s.sid=sc.scid and score<60 and sc.cid=01 

--17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩  ok

SELECT student.sid,b.01,b.02,b.03,b.平均成绩 
FROM student 
LEFT JOIN 
(SELECT scid,max(CASE cid when '01' THEN score ELSE 0 END) '01',
max(CASE cid when '02' THEN score ELSE 0 END) '02',
max(CASE cid when '03' THEN score ELSE 0 END) '03',
avg(score) '平均成绩'
from sc GROUP BY scid) B
on student.sid=b.scid
ORDER BY b.平均成绩 desc

--18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为>=60，中等为：-80，优良为：-90，优秀为：>=90   ok

select distinct c.Cid,c.Cname,
       B.`平均分`,B.`最低分`,B.`最高分`,
       b.`及格率`
  from Course c 
  left join (
   select avg(sc.score) `平均分`,max(sc.score) `最高分`,
       min(sc.score) `最低分`,cid,
CONCAT(round(sum(case when score>=60 then 1 else 0 end)/count(*)*100,2),'%') as `及格率`
  from SC 
 group by sc.Cid
) B
on c.cid=B.Cid

--19、按各科成绩进行排序，并显示排名

SELECT A.sname,B.score,B.cid,(SELECT count(C.score)+1 from (SELECT C.score,count(C.score) from sc C GROUP BY C.score ORDER BY c.score desc) C
WHERE C.score>B.score) as paiming
from (SELECT sname,sid from student) A LEFT JOIN sc B
on B.scid=A.sid 
WHERE b.score >0
ORDER BY b.cid,b.score desc

--20、查询学生的总成绩并进行排名，总分重复时保留名次空缺

SELECT student.sname,sum(sc.score) 
from student LEFT JOIN sc
on sc.scid=student.sid
GROUP BY student.sid
ORDER BY SUM(sc.score) desc
 

21-25略

--26、查询每门课程被选修的学生数

SELECT cid,COUNT(scid) 学生数 from sc GROUP BY cid

--27、查询出只有两门课程的全部学生的学号和姓名

SELECT sid,sname from student
where sid in (SELECT scid from (SELECT scid,count(cid) 课程数 FROM sc 
GROUP BY scid) A where A.课程数=2)

--28、查询男生、女生人数

SELECT ssex,COUNT(ssex) 人数 from student GROUP BY ssex

--29、查询名字中含有"风"字的学生信息

SELECT * from student where sname like '%风%'

--30、查询同名同性学生名单，并统计同名人数

SELECT A.*,B.同名人数 from student A
LEFT JOIN
(SELECT sname,ssex,COUNT(*) 同名人数 from student GROUP BY sname,ssex) B
on A.sname=B.sname and a.ssex=b.ssex
where b.同名人数>1
 
--31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime) 

SELECT * FROM student where year(sage)=1990

--32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

SELECT cid,avg(score) 平均成绩 from sc GROUP BY cid ORDER BY 平均成绩 desc,cid

--33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

SELECT A.sid,a.sname,b.平均成绩 FROM student a
LEFT JOIN
(SELECT scid,avg(score) 平均成绩 from sc GROUP BY scid) B 
on A.sid=B.scid
WHERE b.平均成绩>85

--34、查询课程名称为"数学"，且分数低于 60 的学生姓名和分数

SELECT b.sname,a.score from (SELECT * from sc where score<60 
and cid=(SELECT cid from course WHERE cname='数学')) A
LEFT JOIN student B 
on A.scid=b.sid

--35、查询所有学生的课程及分数情况；

SELECT a.sid,b.cid,b.score FROM student a LEFT JOIN sc b 
on a.sid=b.scid

--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

SELECT a.sname,d.cname,d.score FROM
(SELECT b.*,c.cname from (SELECT * from sc where score>70) b LEFT JOIN course C
on b.cid=c.cid) d
LEFT JOIN student a on d.scid=a.sid

--37、查询不及格的课程

SELECT * FROM sc where score<60

--38、查询课程编号为 01 且课程成绩在70分以上的学生的学号和姓名；

SELECT a.sid,b.sname 
from (SELECT * from sc where score>70) 

39-45略

--46、查询各学生的年龄

SELECT *,(case when convert(int,'1'+substring(convert(varchar(10),sage,112),
5,8))
<convert(int,'1'+substring(convert(varchar(10),getDate(),112)，5，8))
then datediff(yy,sage,getdate())
else datediff(yy,sage,getdate())-1
end) age
from student

47-50略