SQL_学生-课程数据库(下)

最新推荐文章于 2024-03-19 15:14:26 发布

原创最新推荐文章于 2024-03-19 15:14:26 发布 · 1.9k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql #join #null

本文介绍了在SQL中查询学生-课程数据库的各种高级操作，包括筛选特定条件的学生，如年龄、成绩和所属系别；使用JOIN操作获取学生选课详细信息；以及统计分析，如平均成绩、选课人数等。示例涵盖LIKE、BETWEEN、IN、JOIN、GROUP BY和HAVING等关键SQL语法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

第二部分

1.所有男生的姓名、年龄:

Select Sname，Sage

From student;

2.所有年龄大于20，计算机科学系学生名单:

SELECT Sname

FROM Student

WHERE Sage>20 AND Sdept='CS';

3.成绩大于60的学生学号:

SELECT Sno

FROM SC

WHERE Grade>60;

4.成绩在70到80之间的学生学号:

SELECT Sno

FROM SC

WHERE Grade>70 AND Grade<80;

SELECT Sno 学号

FROM SC

WHERE Grade BETWEEN 70 AND 80;

5.1985到1989年出生的学生姓名:

SELECT Sname 姓名

FROM Student

WHERE 2009-Sage BETWEEN 1985 AND 1989;

SELECT Sname

FROM Student

WHERE 2009-Sage>1985 AND 2009-Sage<1989;

6.查询成绩不在70到85的及格学生名单:

SELECT DISTINCT Sname

FROM Student,SC

WHERE Student.Sno=SC.Sno AND Grade NOT BETWEEN 70 AND 80 AND Grade>60;

7.查询成绩是70、80、90的学生学号:

SELECT Sno

FROM SC

WHERE Grade IN(70,80,90);

8.查询1985、1989年的学生学号和姓名:

SELECT Sno,Sname

FROM Student

WHERE 2009-Sage IN('1985','1989');

9.查询学分不是3、4、5的课号:

SELECT Cno

FROM Course

WHERE Ccredit NOT IN(3,4,5);

10.查询2002级所有学生情况:

SELECT *

FROM Student

WHERE Sno LIKE '2002%';

SELECT *

FROM Student

WHERE Sno='2002%';

11.查询姓刘'CS'系的姓名和性别:

SELECT Sname,Ssex

FROM Student

WHERE Sdept='CS' AND Sname LIKE '刘%';

12.查询姓刘、张、李的学生名单:

SELECT Sname

FROM Student

WHERE Sname LIKE '[刘张李]%';

13.查询课程名含有数据的课程名称和学分:

SELECT Cname,Ccredit

FROM Course

WHERE Cname LIKE '%数据%';

14.查询没有参加考试,课程号为001的学生的学号:

SELECT Sno

FROM SC

WHERE Cno LIKE '001' AND Grade IS NULL;

15.所有参加了课程号为002的学生学号，成绩。

SELECT Sno,Grade

FROM SC

WHERE Cno LIKE '%2';

16.查询所有2002级男生和MA系的学生学号和姓名:

SELECT Sno,Sname

FROM Student

WHERE Sno LIKE '2002%' AND Ssex LIKE '男' OR Sdept LIKE 'MA';

17.查询所有4学分和先行课为6的课程号和课程名:

SELECT Cno,Cname

FROM Course

WHERE Ccredit LIKE '4' AND Cpno LIKE '6';

18.查询'CS'系学生名单,并按年龄降序,按性别升序:

SELECT Sname,Sage,Ssex

FROM Student

WHERE Sdept LIKE 'CS'

ORDER BY Sage DESC,Ssex ASC;

19.查询200215121选课情况按成绩降,课程号升序:

SELECT Sno,Cno,Grade

FROM SC

WHERE Sno LIKE '200215121'

ORDER BY Grade DESC,Cno;

20.统计选课002的总人数:

SELECT COUNT(*)选课人数

FROM SC

WHERE Cno LIKE '%2';

21.CS系,男生的总人数:

SELECT COUNT(*)CS系男生人数

FROM Student

WHERE Sdept LIKE 'CS' AND Ssex LIKE '男';

?22.统计选课002且成绩大于平均成绩的人数和平均成绩:

SELECT COUNT(*),AVG(Grade)

FROM SC

WHERE Cno LIKE '%2' AND Grade>AVG(Grade);

23.求各系的学生的平均年龄:

SELECT AVG(Sage)各系别平均成绩

FROM Student

GROUP BY Sdept;

24.求各门课程的平均成绩:

Select COUNT(*),AVG(Grade)平均成绩

From SC

Group by Cno;

?25.求各成绩大于其平均成绩的学号,课程号和成绩。

SELECT Sno,Cno,Grade,AVG(Grade)

FROM SC

GROUP BY Cno

HAVING Grade<AVG(Grade);

26.查询各系男生总人数及平均年龄，按系平均年龄降序:

SELECT COUNT(*),AVG(Sage)

FROM Student

WHERE Ssex LIKE '男'

GROUP BY Sdept

ORDER BY AVG(Sage) DESC;

27.查询所有及格学生各课总成绩,平均成绩,总人数:

SELECT COUNT(DISTINCT Sno),SUM(Grade),AVG(Grade)

FROM SC

WHERE Grade>=60

GROUP BY Cno;

28.查询成绩大于75的学生的学号,姓名,性别,课程号和成绩:

SELECT Student.Sno,Sname,Ssex,Cno,Grade

FROM Student,SC

WHERE Student.Sno=SC.Sno AND Grade>75;

29.查询CS系的学生的姓名,学号和成绩:

SELECT Student.Sno,Sname,Grade

FROM Student,SC

WHERE Student.Sno=SC.Sno AND Sdept LIKE 'CS';

30.查询CS系的男女生的成绩总和:

SELECT SUM(Grade)总成绩

FROM Student,SC

WHERE Student.Sno=SC.Sno AND Sdept='CS'

GROUP BY Ssex ;

31.查询及格学生名单,并按成绩降序排列:

SELECT Sname,Grade,Cno,Sdept

FROM Student,SC

WHERE Student.Sno=SC.Sno AND Grade>=60

ORDER BY Grade DESC;

?32.查询选3门以上课程的学生名单,学号,姓名,选课门数:

SELECT Student.Sno,Sname,COUNT(SC.Sno) AS "选课门数"

FROM Student,SC

WHERE Student.Sno=SC.Sno

GROUP BY SC.Sno

HAVING COUNT(SC.Sno)>=3;

33.查询CS系学生名单和成绩:

SELECT Student.Sno,Sname,Grade

FROM Student INNER JOIN SC ON Student.Sno=SC.Sno

WHERE Sdept LIKE 'CS';

34.查询选修2课程和所有男生的名单及成绩,学号,按成绩降序排列:

SELECT Student.Sno,Sname,Grade

FROM Student INNER JOIN SC ON Student.Sno=SC.Sno

WHERE Ssex LIKE '男' OR Cno LIKE '2'

ORDER BY Grade DESC;