1、查询’CS’系的学生的成绩信息,包括学号,课程号,成绩
SELECT Sno,cno,grade
FROM scxym
WHERE Sno in (SELECT Sno FROM stuxym WHERE Sdept='cs')
2、查询李勇所选修的课程的课程名
SELECT cname
FROM coursexym
WHERE cno in
(SELECT cno
FROM scxym
WHERE Sno in
(SELECT Sno
FROM stuxym
WHERE Sname='李勇'))
3、查询既选了1号课程又选了2号课程的学生学号
SELECT distinct Sno
FROM scxym
WHERE Sno in
(SELECT Sno
FROM scxym
WHERE cno='1')
and
Sno in
(SELECT Sno
FROM scxym
WHERE cno='2')
4、查询没有选修课程的学生的名单
SELECT Sname
FROM stuxym
WHERE Sno not in
(SELECT Sno
FROM scxym)
5、查询选修“数据库”课的全部学生的总成绩
select sum(grade) as 数据库总成绩
from scxym
where cno in
(
select cno
from coursexym
where cname='数据库'
)
6、统计学生选修表,显示学号为"201215121"的学生在其各科成绩中,最高分成绩所对应的课程号和成绩
select sum(grade) as 数据库总成绩
from scxym
where cno in
(
select cno
from coursexym
where cname='数据库')
and Sno in
(
select Sno
from stuxym
where sdept='ma'
)
8、查询数学系"张三"同学的"数据库"成绩。
select grade
from scxym
where cno in
(
select cno
from coursexym
where cname='数据库'
)
and Sno in
(
select Sno
from stuxym
where sdept='ma'
and Sname='张三'
)
9、查询学号为’2021021512’的学生没选的课程的课程号和课程名
SELECT cno,cname
FROM coursexym
WHERE cno not in
(SELECT cno
FROM scxym
WHERE sno='20210215121')
10、检索至少选修了两门课程的学生的信息,包括学号,姓名和所在系
SELECT Sno,sname,sdept
FROM stuxym
WHERE Sno in
(SELECT Sno
FROM scxym
Group by Sno
Having count(cno)>=2 )