实验三：数据库的SQL基本操作

总共50题

(1) 查询全体学生的学号和姓名

SELECT sno, sname
FROM student

(2) 查询选修了课程名为’数据库原理’ 的学生的学号和姓名

SELECT student.sno, sname
FROM student, sc, course
WHERE student.sno = sc.sno and sc.cno = course.cno and cname = '数据库原理'

(3) 查询全体学生的姓名, 出生年份,和所在系, 并用小写字母表示所有系名，并给各列指定列名。

SELECT sname, 2021 - sage birth, Lower(sdept) sdept
FROM student

(4) 查询有多少名学生的数据库课程成绩不及格

SELECT COUNT(*) 不及格
FROM sc, course
WHERE sc.cno = course.cno and cname = '数据库原理' and grade < 60

(5) 查找所有姓’李’的学生的姓名, 学号和性别

SELECT sname, sno, ssex
FROM student
WHERE sname LIKE '李%'

(6) 求没有选修数学课程的学生学号

SELECT sno
FROM student
WHERE sno not in(
		SELECT sno
		FROM sc, course
		WHERE sc.cno = course.cno and cname = '数学'
)

(7) 查询选修了课程的学生的学号

SELECT DISTINCT sno
FROM sc

(8) 计算1号课程的学生的平均成绩, 最高分和最低分

SELECT AVG(grade) avggrade, MAX(grade) maxgrade, MIN(grade) mingrade
FROM sc
WHERE cno = '1'

(9) 查询数学系和信息系的学生的信息;

SELECT *
FROM student
WHERE sdept = 'CS' OR sdept = 'MA'

(10) 将年龄为19岁的学生的成绩置零

UPDATE sc
SET grade = 0
WHERE sno IN
				(SELECT sno
				FROM student
				WHERE sage = 19
			)

(11) 查询所有选修了1号课程的学生姓名

SELECT sname
FROM student, sc
WHERE student.sno = sc.sno and cno = '1'

(12) 对每一个性别，求学生的平均年龄，并把结果存入数据库先创建表，再插入数据

CREATE TABLE data
				(sex char(8),
				avgage int
				)
INSERT 
INTO data
SELECT ssex, AVG(sage)
FROM student
GROUP BY ssex

(13) 查询每个学生已获得的学分

SELECT sno, SUM(credit) getcredit
FROM sc, course
WHERE sc.cno = course.cno
GROUP BY sno

(14) 将所有女生的记录定义为一个视图

CREATE VIEW femal
AS
SELECT *
FROM student
WHERE ssex = '女'
(15) 查询没有选修了1号课程的学生姓名
SELECT sname
FROM student, sc
WHERE student.sno = sc.sno and student.sno NOT IN(
			SELECT sno
			FROM sc
			WHERE cno = '1'	)

(16) 将所有选修了数据库课程的学生的成绩加5分

UPDATE sc
SET grade = grade + 5
WHERE grade IN
			(SELECT grade 
			FROM sc, course
			WHERE sc.cno = course.cno and cname = '数据库原理'
		)

(17) 查询各系的男女生学生总数, 并按系升序排列, 女生排在前

SELECT sdept, ssex, COUNT(*) peoplenum
FROM student
GROUP BY sdept, ssex
ORDER BY sdept ASC, ssex DESC

(18) 查询’信息系’(IS)学生”数据结构”课程的平均成绩

SELECT AVG(grade)
FROM student, sc, course
WHERE student.sno = sc.sno and sc. cno = course.cno and sdept = 'IS' and cname = '数据结构'

(19) 创建一个反映学生出生年份的视图

CREATE VIEW birth(age, num)
AS
SELECT 2021 - sage, COUNT(*)
FROM student
GROUP BY sage

(20) 查询与’王田’在同一个系学习的学生的信息

SELECT *
FROM student
WHERE sname NOT LIKE '王田' and sdept IN(
			SELECT sdept
			FROM student
			WHERE sname = '王田'
		)

(21) 查询年龄在20岁以下的学生的姓名及其年龄

SELECT sname, sage
FROM student
WHERE sage < 20

(22) 查询当前至少选修数据库和信息系统其中一门课的学生的学号

SELECT sc.sno
FROM course, sc
WHERE sc.cno = course.cno and (cname = '数据库原理' OR cname = '信息系统')

(23) 查询每个学生的学号, 姓名, 选修的课程名和成绩:

SELECT sc.sno, sname, cname, grade
FROM course, sc, student
WHERE sc.cno = course.cno and sc.sno = student.sno

(24) 查找名字中包括“俊”的学生的姓名, 学号，选课课程和成绩

SELECT sname, sc.sno, cname, grade
FROM course, sc, student
WHERE sc.cno = course.cno and sc.sno = student.sno and sname LIKE '%俊%'

(25) 查询学分大于8的学生，输出学生的学号和学分

SELECT sno, SUM(credit) getcredit
FROM sc, course
WHERE sc.cno = course.cno
GROUP BY sno
HAVING SUM(credit) > 8

(26) 查询IS,CS,MA系的所有学生的姓名和性别

SELECT sname, ssex
FROM student
WHERE sdept = 'IS' or sdept = 'CS' or sdept = 'MA'

(27) 查询至少选修了2门课程的学生的平均成绩

SELECT sc.sno, AVG(grade) avggrade
FROM student, sc
WHERE student.sno = sc.sno
GROUP BY sc.sno
HAVING COUNT(*) > = 2

(28) 查询每个学生所选课程的平均成绩, 最高分, 最低分,和选课门数

SELECT sno, AVG(grade) avggrade, MAX(grade) maxgrade, MIN(grade) mingrade, COUNT(*) num
FROM sc
GROUP BY sno

(29) 删除年龄大于21岁所有学生的选课记录

DELETE 
FROM sc
WHERE sno IN(
		SELECT sno
		FROM student
		WHERE sage > 21
	)

(30) 查询没有先行课的课程的课程号cno和课程名cname

SELECT cno cname
FROM course
WHERE pcno is NULL

(31) 创建信息系学生信息的视图

CREATE VIEW IS_Student
AS
SELECT sno, sname, ssex, sage
FROM student
WHERE sdept = 'IS'

(32) 在信息系的学生视图中查询年龄小于20岁的学生

SELECT *
FROM IS_Student
WHERE sage < 20

(33) 删除马朝阳同学的所有选课记录

DELETE
FROM sc
WHERE sno IN(
	SELECT sno 
	FROM student
	WHERE sname = '马朝阳'
)

(34) 查询选修了3号课程的学生的学号和成绩, 并按分数降序排列

SELECT sno, grade
FROM sc
WHERE cno = '3'
ORDER BY grade DESC

(35) 查询数据库课程成绩不及格的学生，输入其学号，姓名和成绩

SELECT sc.sno, sname, grade
FROM sc, student, course
WHERE course.cno = sc.cno and student.sno = sc.sno and cname = '数据库原理' and grade < 60

(36) 查询全体学生的情况,查询结果按所在系号升序排列, 同一系中的学生按年龄降序排列

SELECT *
FROM student
ORDER BY sdept ASC, sage DESC

(37) 查询每个学生及其选修课程的情况

SELECT student.sno, sname, cname
FROM student, sc, course
WHERE student.sno = sc.sno and sc. cno = course.cno 

(38) 查询每一门课程的间接先行课

SELECT A.cname 课程, C.cname 间接先修课
FROM course A, course B, course C
WHERE A.pcno = B.cno and B.pcno = C.cno

(39) 查询选修1号课程且成绩在85分以上的所有学生的学号、姓名

SELECT sc.sno, sname
FROM student, sc
WHERE student.sno = sc.sno and cno = '1' and grade > 85

(40) 查询全体学生的所有信息

SELECT student.*, cname, grade
FROM student, sc, course
WHERE student.sno = sc.sno and sc.cno = course.cno

(41) 查询选修了课程’1’和课程’2’的学生的学号

SELECT A.sno
FROM sc A, sc B
WHERE A.sno = B.sno and A.cno = '1' and B.cno = '2'

(42) 创建信息系选修了1号课程的学生的视图

CREATE VIEW SC_cno1
AS
SELECT student.*
FROM student, sc
WHERE student.sno = sc.sno and sdept = 'IS' and cno = '1'

(43) 建立信息系选修了1号课程且成绩在90分以上的学生的视图

CREATE VIEW SC_cno1_90
AS
SELECT student.*
FROM student, sc
WHERE student.sno = sc.sno and sdept = 'IS' and cno = '1' and grade > 90

(44) 查询修课总学分在10学分以下的学生姓名

SELECT sname
FROM student
WHERE sno IN(
		SELECT sc.sno
		FROM student, sc, course
		WHERE student.sno = sc.sno and sc.cno = course.cno 
		GROUP BY sc.sno
		HAVING SUM(credit)  < 10
		)

(45) 查询比’刘晨’年龄小的所有学生的信息

SELECT *
FROM student
WHERE sage <(
		SELECT sage
		FROM student
		WHERE sname = '刘晨'
	)

(46) 查询所有选修了2号课程的学生的姓名

SELECT sname
FROM student, SC
WHERE student.sno = sc.sno and cno = '2'

(47) 查询其他系中比信息系(IS)某一学生年龄小的学生姓名和年龄

SELECT sname, sage
FROM student
WHERE sdept not IN ('IS') and sage <(
		SELECT MAX(sage)
		FROM student
	)

(48) 查询学生2号课程的成绩，并按照成绩由高到低输出

SELECT sname, grade
FROM student, sc
WHERE student.sno = sc.sno and cno = '2'
ORDER BY grade DESC

(49) 查询考试成绩有不及格的学生的学号

SELECT sc.sno
FROM student, sc
WHERE student.sno = sc.sno and grade < 60

(50) 查询其他系中比信息系(IS)学生年龄都小的学生姓名和年龄

SELECT sname, sage
FROM student
WHERE sdept not IN ('IS') and sage <(
		SELECT MIN(sage)
		FROM student
	)