MySql 数据库综合练习01
- 创建表
- 数据表格列名:
- 以下题目均以以上数据作为查询目标:
- 2、查询平均成绩大于60 分的同学的学号和平均成绩;
- 3、查询所有同学的学号、姓名、选课数、总成绩;
- 4、查询姓“刘”的老师的个数;
- 5、查询没学过“谌燕”老师课的同学的学号、姓名;
- 6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名;
- 7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名;
- 8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名;
- 9、查询所有课程成绩小于60 分的同学的学号、姓名;
- 10、查询没有学全所有课的同学的学号、姓名;
- 11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名;
- 12、查询至少学过学号为“s001”同学所有课的其他同学学号和姓名;
- 13、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩;
- 14、查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名;
- 15、删除学习“谌燕”老师课的SC 表记录;
- 16、向SC 表中插入一些记录,这些记录要求符合以下条件:没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩;
- 17、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
- 18、按各科平均成绩从低到高和及格率的百分数从高到低顺序
- 19、查询不同老师所教不同课程平均分从高到低显示
- 20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
- 21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
- 22、查询每门课程被选修的学生数
- 23、查询出只选修了一门课程的全部学生的学号和姓名
- 24、查询男生、女生人数
- 25、查询姓“张”的学生名单
- 26、查询同名同性学生名单,并统计同名人数
- 27、1981 年出生的学生名单(注:Student 表中Sage 列的类型是int)
- 28、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
- 在这里插入图片描述
- 29、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩
- 30、查询课程名称为“数据库”,且分数低于60 的学生姓名和分数
- 31、查询所有学生的选课情况;
- 32、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数;
- 33、查询不及格的课程,并按课程号从大到小排列
- 34、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名;
- 35、求选了课程的学生人数
- 36、查询选修“谌燕”老师所授课程的学生中,成绩最高的学生姓名及其成绩
- 37、查询各个课程及相应的选修人数
- 38、查询不同课程成绩相同的学生的学号、课程号、学生成绩
- 39、查询每门功课成绩最好的前两名
- 40、统计每门课程的学生选修人数(超过10 人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
- 41、检索至少选修两门课程的学生学号
- 42、查询全部学生都选修的课程的课程号和课程名
- 43、查询没学过“谌燕”老师讲授的任一门课程的学生姓名
- 44、查询两门以上不及格课程的同学的学号及其平均成绩
- 45、检索“c004”课程分数小于60,按分数降序排列的同学学号
- 46、删除“s002”同学的“c001”课程的成绩
创建表
注意:
部分数据不满足查询条件,请自行修改验证结果!
以下为已有的表和数据:
drop table if exists student;
drop table if exists teacher;
drop table if exists course;
drop table if exists sc;
create table student(
sno varchar(10) primary key,
sname varchar(20),
sage int,
ssex varchar(5)
);
create table teacher(
tno varchar(10) primary key,
tname varchar(20)
);
create table course(
cno varchar(10),
cname varchar(20),
tno varchar(20),
constraint pk_course primary key (cno,tno)
);
create table sc(
sno varchar(10),
cno varchar(10),
score decimal(8,2),
constraint pk_sc primary key (sno,cno)
);
/*******初始化学生表的数据******/
insert into student values ('s001','张三',23,'男');
insert into student values ('s002','李四',23,'男');
insert into student values ('s003','吴鹏',25,'男');
insert into student values ('s004','琴沁',20,'女');
insert into student values ('s005','王丽',20,'女');
insert into student values ('s006','李波',21,'男');
insert into student values ('s007','刘玉',21,'男');
insert into student values ('s008','萧蓉',21,'女');
insert into student values ('s009','陈萧晓',23,'女');
insert into student values ('s010','陈美',22,'女');
/******************初始化教师表***********************/
insert into teacher values ('t001', '刘阳');
insert into teacher values ('t002', '谌燕');
insert into teacher values ('t003', '胡明星');
/***************初始化课程表****************************/
insert into course values ('c001','J2SE','t002');
insert into course values ('c002','Java Web','t002');
insert into course values ('c003','SSH','t001');
insert into course values ('c004','Oracle','t001');
insert into course values ('c005','SQL SERVER 2005','t003');
insert into course values ('c006','C#','t003');
insert into course values ('c007','JavaScript','t002');
insert into course values ('c008','DIV+CSS','t001');
insert into course values ('c009','PHP','t003');
insert into course values ('c010','EJB3.0','t002');
/***************初始化成绩表***********************/
insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);
insert into sc values ('s003','c001',81.9);
insert into sc values ('s004','c001',60.9);
insert into sc values ('s001','c002',82.9);
insert into sc values ('s002','c002',72.9);
insert into sc values ('s003','c002',81.9);
insert into sc values ('s001','c003',59);
insert into sc values ('s001','c004',78.9);
insert into sc values ('s002','c004',80.9);
insert into sc values ('s003','c005',81.9);
insert into sc values ('s004','c005',60.9);
insert into sc values ('s001','c006',82.9);
insert into sc values ('s002','c006',72.9);
insert into sc values ('s003','c007',81.9);
insert into sc values ('s001','c007',59);
insert into sc values ('s005','c001',80.9);
insert into sc values ('s006','c001',81.9);
insert into sc values ('s007','c001',90.9);
insert into sc values ('s004','c002',75);
insert into sc values ('s005','c002',71.2);
insert into sc values ('s006','c002',85.9);
insert into sc values ('s007','c003',59);
insert into sc values ('s004','c004',77.9);
insert into sc values ('s005','c004',84.9);
insert into sc values ('s006','c005',82.9);
insert into sc values ('s007','c005',67.9);
insert into sc values ('s004','c006',62.9);
insert into sc values ('s006','c006',92.9);
insert into sc values ('s007','c007',35.9);
insert into sc values ('s008','c007',69);
数据表格列名:
表名 | 列名1 | 列名2 | 列名3 | 列名4 | 列名5 | 列名6 |
---|---|---|---|---|---|---|
course | cno | cname | tno | |||
sc | sno | cno | score | |||
student | sno | sname | sage | ssex | ||
teacher | tno | tname |
以下题目均以以上数据作为查询目标:
1、查询“c001”课程比“c002”课程成绩高的所有学生的学号;
SELECT sno AS '课程c001成绩大于 课程 c002 的学号'
-- sc 的别名是为了让我们能后分辨子查询中的成绩信息
FROM sc AS S
GROUP BY sno
HAVING (SELECT score FROM sc WHERE cno = 'c001' AND sno = S.sno) > (SELECT score FROM sc WHERE cno = 'c002' AND sno = S.sno)
2、查询平均成绩大于60 分的同学的学号和平均成绩;
SELECT sno,AVG(score)
FROM sc
GROUP BY sno
HAVING AVG(score) > 60;
3、查询所有同学的学号、姓名、选课数、总成绩;
SELECT Stu.sno AS '学号',Stu.sname AS '姓名',COUNT(Sc.cno) AS '选课数',SUM(Sc.score) AS '总成绩'
FROM sc Sc
JOIN student Stu ON (Stu.sno = Sc.sno)
GROUP BY Sc.sno;
4、查询姓“刘”的老师的个数;
SELECT COUNT(tname) AS "姓刘老师的人数"
FROM teacher
WHERE tname LIKE '刘%';
5、查询没学过“谌燕”老师课的同学的学号、姓名;
-- 存在考试信息的学生及学号
SELECT
student.sno,
student.sname
FROM
student
WHERE
student.sno NOT IN (
SELECT
student.sno
FROM
student
JOIN sc ON ( sc.sno = student.sno )
WHERE
sc.cno IN ( SELECT cno FROM course JOIN teacher ON ( teacher.tno = course.tno ) WHERE teacher.tname = '谌燕' )
GROUP BY
student.sno
)
6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名;
SELECT S.sno AS 学号 ,S.sname AS 姓名
FROM student S
JOIN sc ON (S.sno = sc.sno)
WHERE sc.cno IN ('c001','c002')
GROUP BY sc.sno
HAVING COUNT(cno)>=2
7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名;
SELECT student.sno,student.sname
FROM student
WHERE student.sno IN (
SELECT sc.sno
FROM sc
JOIN course ON (sc.cno = course.cno)
JOIN teacher ON (teacher.tno = course.tno)
WHERE teacher.tname = '谌燕'
GROUP BY sc.sno
)
8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名;
-- 8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名;
SELECT S.sno,S.sname
FROM student S
JOIN sc ON (S.sno = sc.sno)
WHERE (SELECT score FROM sc WHERE sc.sno = S.sno AND sc.cno ='c002') < (SELECT score FROM sc WHERE sc.sno = S.sno AND sc.cno ='c001')
GROUP BY S.sno
9、查询所有课程成绩小于60 分的同学的学号、姓名;
-- 9、查询所有课程成绩小于60 分的同学的学号、姓名;
SELECT
S.sno,
S.sname
FROM
student AS S
JOIN sc SCC ON ( S.sno = SCC.sno )
WHERE
( SELECT COUNT(*) FROM sc WHERE score < 60 AND sno = S.sno GROUP BY sc.sno ) = (
SELECT
COUNT( cno )
FROM
sc
WHERE
sno = S.sno
GROUP BY
sc.sno
)
GROUP BY S.sno
10、查询没有学全所有课的同学的学号、姓名;
-- 10、查询没有学全所有课的同学的学号、姓名;
SELECT S.sno,S.sname
FROM student S
JOIN sc ON (S.sno = sc.sno)
GROUP BY sc.sno
HAVING COUNT(cno) <> (SELECT COUNT(cno) FROM course)
11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名;
-- 11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名;
SELECT S.sno,S.sname
FROM student S
JOIN sc ON (S.sno = sc.sno)
WHERE sc.cno IN (SELECT cno FROM sc WHERE sno = 's001') AND S.sno <> 's001'
GROUP BY S.sno
12、查询至少学过学号为“s001”同学所有课的其他同学学号和姓名;
-- 12、查询至少学过学号为“s001”同学所有课的其他同学学号和姓名;
SELECT
SS.sno,
SS.sname
FROM
student SS
JOIN sc ON ( SS.sno = sc.sno )
WHERE
SS.sno <> 's001' AND
(
SELECT
COUNT( sc.cno )
FROM
student S
JOIN sc ON ( S.sno = sc.sno )
WHERE
sc.cno IN ( SELECT cno FROM sc WHERE sno = 's001' )
AND S.sno = SS.sno
GROUP BY
S.sno
) = ( SELECT COUNT( cno ) FROM sc WHERE sno = 's001' GROUP BY sno )
GROUP BY
SS.sno
13、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩;
-- 13、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩;
UPDATE sc Sc
SET score = (
SELECT
AVG( Sc.score )
FROM
course C
JOIN teacher T ON ( T.tno = C.tno )
WHERE
T.tname = '谌燕'
AND C.cno = Sc.cno
GROUP BY
Sc.cno
)
WHERE
cno IN (
SELECT
cno
FROM
course
JOIN teacher ON ( teacher.tno = course.tno )
WHERE
teacher.tname = '谌燕'
GROUP BY
cno)
14、查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名;
-- 14、查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名;
SELECT
SS.sno,
SS.sname
FROM
student SS
JOIN sc ON ( SS.sno = sc.sno )
WHERE
SS.sno <> 's001'
AND (
SELECT
COUNT( sc.cno )
FROM
student S
JOIN sc ON ( S.sno = sc.sno )
WHERE
sc.cno IN ( SELECT cno FROM sc WHERE sno = 's001' )
AND S.sno = SS.sno
GROUP BY
S.sno
) = ( SELECT COUNT( cno ) FROM sc WHERE sno = 's001' GROUP BY sno )
AND (
SELECT
COUNT( sc.cno )
FROM
student S
JOIN sc ON ( S.sno = sc.sno )
WHERE
S.sno = SS.sno
GROUP BY
S.sno
) = ( SELECT COUNT( cno ) FROM sc WHERE sno = 's001' GROUP BY sno )
GROUP BY
SS.sno
15、删除学习“谌燕”老师课的SC 表记录;
-- 15、删除学习“谌燕”老师课的SC 表记录;
DELETE
FROM
sc
WHERE
sc.cno IN (
SELECT
course.cno
FROM
course
JOIN teacher ON ( course.tno = teacher.tno )
WHERE
teacher.tname = '谌燕')
16、向SC 表中插入一些记录,这些记录要求符合以下条件:没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩;
-- 16、向SC 表中插入一些记录,这些记录要求符合以下条件:没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩;
-- 获取到学号
SELECT
student.sno
FROM
student
WHERE
student.sno NOT IN (
SELECT
student.sno
FROM
student
JOIN sc ON ( student.sno = sc.sno )
WHERE
sc.cno = 'c002'
GROUP BY
student.sno
)
-- 获取到对应的平均成绩
SELECT AVG(score)
FROM sc
WHERE cno = 'c002'
17、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
-- 17、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
SELECT sc.cno AS '课程ID' ,MAX(sc.score) AS '最高分',MIN(sc.score) AS '最低分'
FROM sc
GROUP BY sc.cno
18、按各科平均成绩从低到高和及格率的百分数从高到低顺序
-- 18、按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT ST.cno,course.cname,AVG(ST.score),(SELECT COUNT(score) FROM sc WHERE sc.cno = ST.cno AND sc.score>=60)/COUNT(score) AS 及格率
FROM sc ST
JOIN course ON (ST.cno = course.cno)
GROUP BY ST.cno,course.cname
ORDER BY AVG(ST.score) ASC,及格率 DESC
19、查询不同老师所教不同课程平均分从高到低显示
-- 19、查询不同老师所教不同课程平均分从高到低显示
SELECT AVG(K.score) AS avgScore,C.cname,T.tname,C.cno
FROM sc K
JOIN course C ON (K.cno = C.cno)
JOIN teacher T ON (T.tno = C.tno)
GROUP BY C.cno,C.cname,T.tname
ORDER BY avgScore DESC
20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
-- 20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
SELECT COUNT( score ),
( SELECT COUNT( score ) FROM sc WHERE sc.cno = O.cno AND score BETWEEN 86 AND 100 ) AS '100-86',
(SELECT COUNT(score) FROM sc WHERE sc.cno = O.cno AND score BETWEEN 71 AND 85) AS '85-71',
(SELECT COUNT(score) FROM sc WHERE sc.cno = O.cno AND score BETWEEN 60 AND 70) AS '70-60',
(SELECT COUNT(score) FROM sc WHERE sc.cno = O.cno AND score <60 ) AS '<60',
O.cno,C.cname
FROM
sc O
JOIN course C ON ( C.cno = O.cno )
GROUP BY
O.cno,C.cname
-- 20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
SELECT score,
( SELECT COUNT( score ) FROM sc WHERE sc.cno = O.cno AND score BETWEEN 86 AND 100 ) AS '100-86',
(SELECT COUNT(score) FROM sc WHERE sc.cno = O.cno AND score BETWEEN 71 AND 85) AS '85-71',
(SELECT COUNT(score) FROM sc WHERE sc.cno = O.cno AND score BETWEEN 60 AND 70) AS '70-60',
(SELECT COUNT(score) FROM sc WHERE sc.cno = O.cno AND score <60 ) AS '<60',
O.cno,C.cname
FROM
sc O
LEFT JOIN course C ON ( C.cno = O.cno )
GROUP BY
O.cno,C.cname,O.score
21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
-- 21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
SELECT score,sc.cno,course.cno,course.cname
FROM sc
LEFT JOIN course ON (sc.cno = course.cno)
GROUP BY sc.cno,sc.score,course.cname
ORDER BY course.cname,sc.score DESC
22、查询每门课程被选修的学生数
-- 22、查询每门课程被选修的学生数
SELECT COUNT(cno) AS '选修人数',cno AS '选修课程ID'
FROM sc
GROUP BY sc.cno
23、查询出只选修了一门课程的全部学生的学号和姓名
-- 23、查询出只选修了一门课程的全部学生的学号和姓名
SELECT S.sno,S.sname
FROM student S
JOIN sc ON (S.sno = sc.sno)
GROUP BY sno
HAVING COUNT(score) = 1;
24、查询男生、女生人数
-- 24、查询男生、女生人数
SELECT COUNT(sno),ssex
FROM student
GROUP BY ssex
25、查询姓“张”的学生名单
-- 25、查询姓“张”的学生名单
SELECT *
FROM student
WHERE sname LIKE '张%'
26、查询同名同性学生名单,并统计同名人数
-- 26、查询同名同性学生名单,并统计同名人数
SELECT *
FROM student AS A,student AS B
WHERE A.sname = B.sname AND A.sno <> B.sno
GROUP BY A.sno,B.sno;
-- 26、查询同名同性学生名单,并统计同名人数
SELECT COUNT(*) AS '人数',A.sname AS '姓名'
FROM student AS A,student AS B
WHERE A.sname = B.sname AND A.sno <> B.sno
GROUP BY A.sname,B.sname;
27、1981 年出生的学生名单(注:Student 表中Sage 列的类型是int)
题目要求和数据不符,以下结果为2000年出生的学生信息
-- 27、1981 年出生的学生名单(注:Student 表中Sage 列的类型是int)
SELECT *
FROM student
WHERE student.sage = (SELECT TIMESTAMPDIFF(YEAR,'2000-01-01',CURRENT_DATE))
28、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
-- 28、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
SELECT AVG(score) AS AV,sc.cno
FROM sc
GROUP BY sc.cno
ORDER BY AV ASC,cno DESC
29、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩
-- 29、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩
SELECT S.sno,S.sname,AVG(score) AS AV
FROM sc
JOIN student S ON (S.sno = sc.sno)
GROUP BY sno
HAVING AV > 85
30、查询课程名称为“数据库”,且分数低于60 的学生姓名和分数
表中无数据库课程信息,故此以J2SE作为查询结果
-- 30、查询课程名称为“数据库”,且分数低于60 的学生姓名和分数
SELECT S.sname,sc.score
FROM sc
JOIN course C ON (C.cno = sc.cno)
JOIN student S ON(S.sno = sc.sno)
WHERE C.cname = 'J2SE' AND sc.score <60
31、查询所有学生的选课情况;
-- 31、查询所有学生的选课情况;
SELECT C.cno,C.cname,S.sno,S.sname
FROM student S
JOIN sc ON (S.sno = sc.sno)
JOIN course C ON (sc.cno = C.cno)
GROUP BY sc.sno,sc.cno,C.cname
32、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数;
-- 32、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数;
SELECT S.sno,S.sname,C.cname,sc.score
FROM sc
JOIN student S ON (S.sno = sc.sno)
JOIN course C ON (C.cno = sc.cno)
WHERE sc.score >70
33、查询不及格的课程,并按课程号从大到小排列
-- 33、查询不及格的课程,并按课程号从大到小排列
SELECT cno
FROM sc
WHERE score <60
GROUP BY cno
ORDER BY cno DESC
34、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名;
-- 34、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名;
SELECT S.sno,S.sname
FROM sc
JOIN student S ON (S.sno = sc.sno)
WHERE sc.score >80 AND sc.cno = 'c001'
35、求选了课程的学生人数
题目不完整
-- 35、求选了课程的学生人数
SELECT COUNT(sc.sno),C.cname
FROM sc
JOIN course C ON (C.cno = sc.cno)
JOIN teacher T ON (T.tno = C.tno)
WHERE T.tname = '刘阳'
GROUP BY sc.cno,C.cname
36、查询选修“谌燕”老师所授课程的学生中,成绩最高的学生姓名及其成绩
-- 36、查询选修“谌燕”老师所授课程的学生中,成绩最高的学生姓名及其成绩
SELECT sc.score,S.sname,C.cname
FROM sc
JOIN course C ON (sc.cno = C.cno)
JOIN teacher T ON (T.tno = C.tno)
JOIN student S ON (S.sno = sc.sno)
WHERE T.tname = '谌燕' AND sc.score = (SELECT MAX(score) FROM sc WHERE cno = C.cno)
GROUP BY C.cname,sc.cno,S.sno
37、查询各个课程及相应的选修人数
-- 37、查询各个课程及相应的选修人数
SELECT COUNT(sno),cno
FROM sc
GROUP BY sc.cno
38、查询不同课程成绩相同的学生的学号、课程号、学生成绩
描述不清晰,我认为应以一名学生的不同课程的成绩来判断
-- 38、查询不同课程成绩相同的学生的学号、课程号、学生成绩
SELECT *
FROM sc F
JOIN student S ON (S.sno = F.sno)
WHERE F.score IN (SELECT score FROM sc WHERE sc.sno = S.sno AND sc.cno <> F.cno )
39、查询每门功课成绩最好的前两名
无法限制条件
-- 39、查询每门功课成绩最好的前两名
SELECT S.score,S.cno
FROM sc S
WHERE S.score IN (
SELECT G.score
FROM S AS T,
(SELECT score
FROM sc
WHERE cno = T.cno
ORDER BY score DESC
LIMIT 2) AS G)
GROUP BY S.cno,S.score
40、统计每门课程的学生选修人数(超过10 人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
-- 40、统计每门课程的学生选修人数(超过10 人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
SELECT cno,COUNT(sno) AS CN
FROM sc
GROUP BY cno
HAVING CN > 5
ORDER BY CN DESC,cno ASC
41、检索至少选修两门课程的学生学号
-- 41、检索至少选修两门课程的学生学号
SELECT COUNT(cno) AS '选修数目',sno
FROM sc
GROUP BY sno
HAVING 选修数目 >2
42、查询全部学生都选修的课程的课程号和课程名
-- 42、查询全部学生都选修的课程的课程号和课程名
SELECT DISTINCT C.cno,C.cname
FROM sc
JOIN course C ON (C.cno = sc.cno)
-- GROUP BY C.cno,C.cname
43、查询没学过“谌燕”老师讲授的任一门课程的学生姓名
-- 43、查询没学过“谌燕”老师讲授的任一门课程的学生姓名
SELECT DISTINCT
sno,
sname
FROM
student
WHERE
sno NOT IN (
SELECT
S.sno
FROM
student S
JOIN sc ON ( S.sno = sc.sno )
WHERE
sc.cno IN (
SELECT
sc.cno
FROM
sc
JOIN course C ON ( C.cno = sc.cno )
JOIN teacher T ON ( T.tno = C.tno )
WHERE
T.tname = '谌燕'
GROUP BY
cno
)
GROUP BY
S.sno
)
44、查询两门以上不及格课程的同学的学号及其平均成绩
-- 44、查询两门以上不及格课程的同学的学号及其平均成绩
SELECT COUNT(S.score),S.sno,(SELECT AVG( score)FROM sc WHERE sno = S.sno)
FROM sc S
WHERE S.score <60
GROUP BY sno
HAVING COUNT(S.score) >=2
45、检索“c004”课程分数小于60,按分数降序排列的同学学号
-- 45、检索“c004”课程分数小于60,按分数降序排列的同学学号
SELECT sno,score
FROM sc
WHERE cno= 'c004' AND score <60
GROUP BY sno
ORDER BY score DESC
46、删除“s002”同学的“c001”课程的成绩
-- 46、删除“s002”同学的“c001”课程的成绩
DELETE FROM sc WHERE sno='s002' AND cno='c001';