记录一下无聊的数据库作业

database

题目如下:

1.查询sC表中的全部数据。
2. 查询计算机系学生的姓名和年龄
3.查询成绩在70~80分的学生的学号、课程号和成绩
4.查询计算机系年龄在18~20岁的男生姓名和年龄
s.查询C001课程的最高分
6.查询计算机系学生的最大年龄和最小年龄
7.统计每个系的学生人数
8.统计每]课程的选课人数和最高成绩。
9.统计每个学生的选课门数和考试总成绩,并按选课]数升序显示结果。
10.列出总成绩超过200的学生的学号和总成绩
11.查询选了C002课程的学生姓名和所在系
12.查询考试成绩80分以上的学生姓名、课程号和成绩,并按成绩降序排列结果
13.查询与VB在同一学期开设的课程的课程名和开课学期
14.查询与李勇年龄相同的学生的姓名、所在系和年龄
15.查询哪些课程没有学生选修,列出课程号和课程名
16.查询每个学生的选课情况,包括未选课的学生,列出学生的学号、姓名、选的课程号
17.查询计算机系哪些学生没有选课,列出学生姓名
18.查询计算机系年龄最大的三个学生的姓名和年龄
19.列出“VB"课程考试成绩前三名的学生的学号、姓名、所在系和VB成绩
20.查询选课门]数最多的前2位学生,列出学号和选课门数

代码如下:

-- 1

SELECT*

FROM SC;

-- 2

SELECT s.Sname, s.Sage

FROM Student s

WHERE s.Sdept = N"计算机系";

-- 3

SELECT sc.Sno, sc.Cno, sc.Grade

FROM SC sc

WHERE sc.Grade BETWEEN70and80;

-- 4

SELECT s.Sname, s.Sage

FROM Student s

WHERE s.Sdept = N"计算机系"

AND s.Sage in (18, 20)

AND s.Ssex = N"";

-- 5

SELECTMAX(sc.Grade) AS max_grade

FROM SC sc

GROUPBY sc.Cno

HAVING sc.Cno ="C001";

-- 6

SELECTMAX(s.Sage) AS max_age, MIN(s.Sage) AS min_age

FROM Student s

GROUPBY s.Sdept

having s.Sdept ="计算机系";

-- 7

SELECT CONCAT(s.Sdept, " : ", COUNT(s.Sno)) AS stu_nums

FROM Student s

GROUPBY s.Sdept;

-- 8

SELECT sc.Cno AS Cno, COUNT(sc.Sno) as c_nums, MAX(sc.Grade) as max_grade

FROM SC sc

GROUPBY sc.Cno;

-- 9

SELECTCOUNT(sc.Cno) as c_nums, SUM(sc.Grade) as sum_grades

FROM SC sc

GROUPBY sc.Sno

ORDERBY c_nums;

-- 10

SELECT sc.Sno, SUM(sc.Grade) AS sum_grades

FROM SC sc

GROUPBY sc.Sno

HavingSUM(sc.Grade) >200;

-- 11

SELECT s.sname, s.Sdept

FROM SC sc

innerjoin Student s

on sc.Cno ="C002";

-- 12

SELECT s.Sname, sc.Cno, sc.Grade

FROM SC sc

INNERJOIN Student s on sc.Sno = s.Sno

GROUPBY s.Sname, sc.Cno, sc.Grade

HAVING sc.Grade >80

ORDERBY sc.Grade DESC;

-- 13

SELECT c.Cno, c.Semester

FROM Course c

WHERE c.Semester = (SELECT Semester FROM Course WHERE Cname ="VB")

AND c.Cname <>"VB";

-- 14

SELECT s.Sname, s.Sdept, s.Sage

FROM Student s

WHERE s.Sage = (SELECT Sage FROM Student WHERE Sname = N"李勇")

AND s.Sname <> N"李勇";

-- 15

SELECT c.Cno, c.Cname

FROM Course c

WHERE c.Cno notin (SELECT sc.Cno FROM SC sc);

--16

SELECT s.Sno,

s.Sname,

cno=STUFF((

SELECT","+ TRIM(c.Cno)

FROM Course c,

SC sc1

WHERE s.Sno = sc1.Sno

AND sc1.Cno = c.Cno

FOR XML PATH ("")), 1, 1, "")

FROM SC sc

RIGHTJOIN Student S on sc.Sno = S.Sno

GROUPBY s.Sno, s.Sname;

-- 17

SELECT s.Sname

FROM Student s

WHERE s.Sno notin (SELECT sc.Sno FROM SC sc);

-- 18

SELECT

TOP3

s.sname

,

s.Sage

FROM Student s

WHERE s.Sdept = N"计算机系"

ORDERBY s.Sage;

-- 19

SELECT

TOP3

s.sno

,

s.sname

,

s.Sdept

,

sc.Grade

FROM Course c

INNERJOIN SC sc ON c.Cno = sc.Cno

INNERJOIN Student s on sc.Sno = s.Sno

WHERE c.Cname ="VB";

--20

SELECT

TOP2

sc.Sno

,

COUNT(sc.Cno) AS course_nums

FROM SC sc

GROUPBY sc.Sno;

 

以上是 记录一下无聊的数据库作业 的全部内容, 来源链接: utcz.com/z/533047.html

回到顶部