-- 创建数据库 CREATE DATABASE testing; -- 连接数据库 USE testing; -- 创建学生表 CREATE TABLE student (sid VARCHAR(10) PRIMARY KEY, sname VARCHAR(10) NOT NULL, sbirth DATE, ssex CHAR(2) ); -- 创建教师表 CREATE TABLE teacher (tid VARCHAR(10) PRIMARY KEY, tname VARCHAR(10) NOT NULL ); -- 创建课程表 CREATE TABLE course (cid VARCHAR(10) PRIMARY KEY, tname VARCHAR(10) NOT NULL, tid VARCHAR(10) NOT NULL, FOREIGN KEY(tid) REFERENCES teacher(tid) ); -- 创建成绩表 CREATE TABLE sc (sid VARCHAR(10) NOT NULL, cid VARCHAR(10) NOT NULL, score FLOAT(3,1), PRIMARY KEY(sid,cid), FOREIGN KEY(sid) REFERENCES student(sid), FOREIGN KEY(cid) REFERENCES course(cid) ); -- 在学生表中插入数据 INSERT INTO student VALUES ('S01', '赵雷', '1990-01-01', '男'), ('S02', '钱电', '1990-12-21', '男'), ('S03', '孙风', '1990-05-20', '男'), ('S04', '李云', '1990-08-06', '男'), ('S05', '周梅', '1991-12-01', '女'), ('S06', '吴兰', '1992-03-01', '女'), ('S07', '郑竹', '1989-07-01', '女'), ('S08', '王菊', '1990-01-20', '女'), ('S09', '李云', '1990-01-20', '男'), ('S10', '张楠', '1991-05-29', '女'), ('S11', '张飞', '1992-06-10', '男'), ('S12', '廖云', '1992-06-06', '男'); -- 在教师表中插入数据 INSERT INTO teacher VALUES ('T01', '张珊'), ('T02', '李雨'), ('T03', '王晟'); -- 在课程表中插入数据 INSERT INTO course VALUES ('C01', 'PS', 'T02'), ('C02', 'C语言', 'T01'), ('C03', 'JAVA', 'T03'); -- 在成绩表中插入数据 INSERT INTO sc VALUES ('S01', 'C01', 80.0), ('S01', 'C02', 90.0), ('S01', 'C03', 99.0), ('S02', 'C01', 70.0), ('S02', 'C02', 60.0), ('S02', 'C03', 80.0), ('S03', 'C01', 80.5), ('S03', 'C02', 80.0), ('S03', 'C03', 80.0), ('S04', 'C01', 50.0), ('S04', 'C02', 30.0), ('S04', 'C03', 20.0), ('S05', 'C01', 76.0), ('S05', 'C02', 87.0), ('S06', 'C01', 31.0), ('S06', 'C03', 34.0), ('S07', 'C02', 89.5), ('S07', 'C03', 98.0), ('S08', 'C01', 75.0), ('S08', 'C02', 69.0), ('S08', 'C03', 92.0); -- 2.查询平均成绩大于60分的同学的学号和平均成绩； SELECT AVG(score),sid FROM sc WHERE AVG(score)>60; -- 10.查询没有学全所有课的同学的学号、姓名；

SELECT s.sid, s.sname FROM student s WHERE NOT EXISTS (SELECT c.cid FROM course c WHERE NOT EXISTS (SELECT sc.cid FROM sc WHERE sc.sid = s.sid AND sc.cid = c.cid));