【MySQL篇】数据库小测试1

检索所学课程包含S3所学课程的学生学号。

题目

1.设教学数据库中有3 个关系

学生关系S(SNO,SNAME,AGE,SEX)

学习关系SC(SNO,CNO,GRADE)

课程关系C(CNO,CNAME,TEACHER)

下面用关系代数表达式表达每个查询语句。

（1）检索学习课程号为C2的学生学号与成绩。

（2）检索学习课程号为C2的学生学号与姓名

（3）检索选修课程名为MATHS的学生学号与姓名。

（4）检索选修课程号为C2或C4的学生学号。

（5）检索至少选修课程号为C2和C4的学生学号。

（6）检索不学C2 课的学生姓名与年龄。

（7）检索学习全部课程的学生姓名。

（8）检索所学课程包含S3所学课程的学生学号。

表的建立

建立S表

CREATE TABLE S (
    SNO varchar(20) PRIMARY KEY,
    SNAME varchar(20),
    AGE INT,
    SEX char(2)
)

建立C表

CREATE TABLE C (
    CNO VARCHAR(20) PRIMARY KEY,
    CNAME VARCHAR(20),
    TEACHER VARCHAR(20)
)

建立SC表

CREATE TABLE SC (
    SNO VARCHAR(20),
    CNO VARCHAR(20),
    GRADE INT,
    FOREIGN KEY (SNO) REFERENCES s (SNO),
    FOREIGN KEY (CNO) REFERENCES c (CNO),
    PRIMARY KEY (SNO, CNO)
)

（1）检索学习课程号为C2的学生学号与成绩。

SELECT SNO, GRADE
FROM sc
WHERE CNO = 'c2';

（2）检索学习课程号为C2的学生学号与姓名

SELECT DISTINCT sc.SNO, s.SNAME
FROM sc, s
WHERE sc.CNO = 'c2'
	AND sc.SNO = s.SNO;

（3）检索选修课程名为MATHS的学生学号与姓名。

SELECT s.SNO, s.SNAME
FROM sc, s, c
WHERE sc.CNO = c.CNO
	AND c.CNAME = 'MATHS'
	AND s.SNO = sc.SNO;

（4）检索选修课程号为C2或C4的学生学号。

SELECT sc.SNO
FROM sc, c
WHERE sc.CNO = c.CNO
	AND sc.CNO IN ('c2','c4');

（5）检索至少选修课程号为C1和C3的学生学号。

SELECT DISTINCT sc.SNO
FROM sc
WHERE sc.SNO IN (
		SELECT sc.SNO
		FROM sc
		WHERE CNO = 'c1'
	)
	AND sc.SNO IN (
		SELECT sc.SNO
		FROM sc
		WHERE CNO = 'c3'
	)

（6）检索不学C2 课的学生姓名与年龄。

SELECT DISTINCT sc.SNO, s.SNAME, s.AGE
FROM sc,s
WHERE sc.SNO NOT IN (
		SELECT sc.SNO
		FROM sc
		WHERE CNO = 'c2'
	)
	AND s.SNO = sc.SNO;

（7）检索学习全部课程的学生姓名。

SELECT DISTINCT s.SNAME
FROM sc, s, c
WHERE s.SNO IN (
    SELECT SNO
    FROM sc
    GROUP BY sc.SNO
    HAVING count(*) = (
        SELECT COUNT(*)
        FROM c
    )
);

（8）检索所学课程包含S3所学课程的学生学号。

SELECT DISTINCT SNO
FROM sc x
WHERE NOT EXISTS (
	SELECT *
	FROM sc y
	WHERE y.SNO = 's003'
		AND NOT EXISTS (
			SELECT *
			FROM sc z
			WHERE z.SNO = x.SNO
				AND z.CNO = y.CNO
		)
);