MySQL——Student、Course、Teacher、SC SQL查询练习题

题目

练习数据
数据表

1. 学生表 Student(SId,Sname,Sage,Ssex)
   SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

2. 课程表 Course(CId,Cname,TId)
   CId 课程编号,Cname 课程名称,TId 教师编号

3. 教师表 Teacher(TId,Tname)
   TId 教师编号,Tname 教师姓名

4. 成绩表 SC(SId,CId,score)
   SId 学生编号,CId 课程编号,score 分数

创建测试数据

学生表 Student
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));

insert into Student values(‘01’ , ‘赵雷’ , ‘1990-01-01’ , ‘男’);
insert into Student values(‘02’ , ‘钱电’ , ‘1990-12-21’ , ‘男’);
insert into Student values(‘03’ , ‘孙风’ , ‘1990-05-20’ , ‘男’);
insert into Student values(‘04’ , ‘李云’ , ‘1990-08-06’ , ‘男’);
insert into Student values(‘05’ , ‘周梅’ , ‘1991-12-01’ , ‘女’);
insert into Student values(‘06’ , ‘吴兰’ , ‘1992-03-01’ , ‘女’);
insert into Student values(‘07’ , ‘郑竹’ , ‘1989-07-01’ , ‘女’);
insert into Student values(‘09’ , ‘张三’ , ‘2017-12-20’ , ‘女’);
insert into Student values(‘10’ , ‘李四’ , ‘2017-12-25’ , ‘女’);
insert into Student values(‘11’ , ‘李四’ , ‘2017-12-30’ , ‘女’);
insert into Student values(‘12’ , ‘赵六’ , ‘2017-01-01’ , ‘女’);
insert into Student values(‘13’ , ‘孙七’ , ‘2018-01-01’ , ‘女’);

科目表 Course
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10))

insert into Course values(‘01’ , ‘语文’ , ‘02’);
insert into Course values(‘02’ , ‘数学’ , ‘01’);
insert into Course values(‘03’ , ‘英语’ , ‘03’);

教师表 Teacher
create table Teacher(TId varchar(10),Tname varchar(10))

insert into Teacher values(‘01’ , ‘张三’);
insert into Teacher values(‘02’ , ‘李四’);
insert into Teacher values(‘03’ , ‘王五’);

成绩表 SC
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1))

insert into SC values(‘01’ , ‘01’ , 80);
insert into SC values(‘01’ , ‘02’ , 90);
insert into SC values(‘01’ , ‘03’ , 99);
insert into SC values(‘02’ , ‘01’ , 70);
insert into SC values(‘02’ , ‘02’ , 60);
insert into SC values(‘02’ , ‘03’ , 80);
insert into SC values(‘03’ , ‘01’ , 80);
insert into SC values(‘03’ , ‘02’ , 80);
insert into SC values(‘03’ , ‘03’ , 80);
insert into SC values(‘04’ , ‘01’ , 50);
insert into SC values(‘04’ , ‘02’ , 30);
insert into SC values(‘04’ , ‘03’ , 20);
insert into SC values(‘05’ , ‘01’ , 76);
insert into SC values(‘05’ , ‘02’ , 87);
insert into SC values(‘06’ , ‘01’ , 31);
insert into SC values(‘06’ , ‘03’ , 34);
insert into SC values(‘07’ , ‘02’ , 89);
insert into SC values(‘07’ , ‘03’ , 98);

练习题目

1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数

2. 查询同时存在"01"课程和"02"课程的情况和不存在"01"课程但存在"02"课程的情况

3. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

4. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

5. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

6. 查询"李"姓老师的数量

7. 查询学过"张三"老师授课的同学的信息

8. 查询没学过"张三"老师授课的同学的信息

9. 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

10. 查询没学过"张三"老师讲授的任一门课程的学生姓名

11. 查询和"01"号的同学学习的课程完全相同的其他同学的信息

答案

-- 创建库
CREATE DATABASE school;
-- 创建学生表
CREATE TABLE Student(
Sid VARCHAR(10) PRIMARY KEY, -- 学生编号
Sname VARCHAR(10),-- 学生姓名
Sage  DATETIME,-- 学生出生年月
Ssex  VARCHAR(10)-- 学生性别
)DEFAULT CHARSET = utf8;
INSERT INTO Student VALUES('01' , '赵雷' , '1990-01-01' , '男');
INSERT INTO Student VALUES('02' , '钱电' , '1990-12-21' , '男');
INSERT INTO Student VALUES('03' , '孙风' , '1990-05-20' , '男');
INSERT INTO Student VALUES('04' , '李云' , '1990-08-06' , '男');
INSERT INTO Student VALUES('05' , '周梅' , '1991-12-01' , '女');
INSERT INTO Student VALUES('06' , '吴兰' , '1992-03-01' , '女');
INSERT INTO Student VALUES('07' , '郑竹' , '1989-07-01' , '女');
INSERT INTO Student VALUES('09' , '张三' , '2017-12-20' , '女');
INSERT INTO Student VALUES('10' , '李四' , '2017-12-25' , '女');
INSERT INTO Student VALUES('11' , '李四' , '2017-12-30' , '女');
INSERT INTO Student VALUES('12' , '赵六' , '2017-01-01' , '女');
INSERT INTO Student VALUES('13' , '孙七' , '2018-01-01' , '女');

SELECT * FROM Student;

-- 创建教师表
CREATE TABLE Teacher(
Tid VARCHAR(10) PRIMARY KEY,-- 教师编号
Tname VARCHAR(10) -- 教师姓名
)DEFAULT CHARSET = utf8;
INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');

SELECT * FROM Teacher;

-- 创建课程表
CREATE TABLE Course(
Cid VARCHAR(10) PRIMARY KEY,-- 课程编号
Cname NVARCHAR(10),-- 课程名称
Tid  VARCHAR(10), -- 教师编号
FOREIGN KEY (Tid) REFERENCES Teacher(Tid)-- 外键约束
)DEFAULT CHARSET = utf8;
INSERT INTO Course VALUES('01' , '语文' , '02');
INSERT INTO Course VALUES('02' , '数学' , '01');
INSERT INTO Course VALUES('03' , '英语' , '03');
 SELECT * FROM Course;
 
-- 创建成绩表
CREATE TABLE SC(
Sid VARCHAR(10), -- 学生编号
Cid VARCHAR(10),-- 课程编号
score DECIMAL(18,1),-- 学生成绩
FOREIGN KEY (Sid) REFERENCES Student(Sid),-- 外键约束
FOREIGN KEY (Cid) REFERENCES Course(Cid) 
)DEFAULT CHARSET = utf8;
INSERT INTO SC VALUES('01' , '01' , 80);
INSERT INTO SC VALUES('01' , '02' , 90);
INSERT INTO SC VALUES('01' , '03' , 99);
INSERT INTO SC VALUES('02' , '01' , 70);
INSERT INTO SC VALUES('02' , '02' , 60);
INSERT INTO SC VALUES('02' , '03' , 80);
INSERT INTO SC VALUES('03' , '01' , 80);
INSERT INTO SC VALUES('03' , '02' , 80);
INSERT INTO SC VALUES('03' , '03' , 80);
INSERT INTO SC VALUES('04' , '01' , 50);
INSERT INTO SC VALUES('04' , '02' , 30);
INSERT INTO SC VALUES('04' , '03' , 20);
INSERT INTO SC VALUES('05' , '01' , 76);
INSERT INTO SC VALUES('05' , '02' , 87);
INSERT INTO SC VALUES('06' , '01' , 31);
INSERT INTO SC VALUES('06' , '03' , 34);
INSERT INTO SC VALUES('07' , '02' , 89);
INSERT INTO SC VALUES('07' , '03' , 98);

SELECT * FROM SC;

-- 1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数
-- 首先是使用inner join查出所有有01课程的学生信息以及成绩信息
-- 以上查出来的就是一个虚拟表 ，已经是全部的01课程的学生的成绩信息和个人信息了。
-- 再以这个虚拟表联合成绩表，查出其中有02课程的学生的成绩信息
-- 再进行比较
SELECT  S.*,b.score AS 01score,c.score AS 02score FROM Student S 
INNER JOIN SC b ON S.Sid=b.Sid  AND b.Cid='01'
LEFT JOIN SC c ON s.sid = c.Sid AND c.Cid='02'
WHERE b.score > c.score;

-- 2.查询同时存在"01"课程和"02"课程的情况和不存在"01"课程但存在"02"课程的情况
-- 第一题
-- 第一种方法
SELECT S.* FROM SC b
INNER JOIN Student S ON S.Sid=b.Sid 
WHERE b.Cid='01' AND b.Sid IN(SELECT a.Sid FROM SC a WHERE a.Cid='02');
-- 第二种方法
SELECT S.*
FROM Student S,SC a,SC b
WHERE S.Sid=a.Sid AND S.Sid=b.Sid
      AND a.Cid='01'
      AND b.Cid='02';
-- 第二题
-- 第一种方法
SELECT S.* FROM SC a INNER JOIN Student S ON S.Sid = a.Sid
WHERE a.Cid = '02'
AND a.Sid NOT IN(SELECT b.Sid FROM SC b WHERE b.Cid = '01');
-- 第二种方法
SELECT S.* FROM Student S
WHERE S.Sid IN (SELECT Sid FROM SC WHERE Cid='02')
AND S.Sid NOT IN (SELECT Sid FROM SC WHERE Cid='01');
      
-- 3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT S.Sid,S.Sname,AVG(a.score) AS average
FROM SC a,Student S
WHERE a.Sid=S.sid
GROUP BY a.Sid
HAVING average>=60;

-- 4.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
SELECT S.Sid,S.Sname,AVG(a.score) AS average
FROM SC a,Student S
WHERE a.Sid = S.Sid
GROUP BY a.Sid
HAVING average<60;

-- 5.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT S.Sid,S.Sname,COUNT(a.Cid) AS 选课总数,SUM(a.score) AS 总成绩
FROM Student S LEFT JOIN SC a ON S.Sid = a.Sid GROUP BY S.Sid;

-- 6.查询"李"姓老师的数量 
SELECT COUNT(Tname) FROM Teacher WHERE Tname LIKE '李%';

-- 7.查询学过"张三"老师授课的同学的信息
SELECT S.* FROM Student S 
WHERE S.Sid IN(SELECT a.Sid FROM SC a 
	    WHERE a.Cid IN(SELECT c.Cid FROM course c 
	    WHERE C.Tid=(SELECT t.Tid FROM Teacher t WHERE t.Tname='张三'))
GROUP BY a.Sid);

-- 8.查询没学过"张三"老师授课的同学的信息
SELECT S.* FROM Student S 
WHERE S.Sid NOT IN(SELECT a.Sid FROM SC a 
	    WHERE a.Cid IN(SELECT c.Cid FROM course c 
	    WHERE C.Tid=(SELECT t.Tid FROM Teacher t WHERE t.Tname='张三'))
GROUP BY a.Sid);

-- 9.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT S.* FROM SC a
INNER JOIN Student S ON S.Sid=a.Sid 
WHERE a.Cid='01' AND a.Sid IN(SELECT b.Sid FROM SC b WHERE b.Cid='02');

-- 10.查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT S.* FROM Student S 
WHERE S.Sid NOT IN(SELECT a.Sid FROM SC a 
	    WHERE a.Cid IN(SELECT c.Cid FROM course c 
	    WHERE C.Tid=(SELECT t.Tid FROM Teacher t WHERE t.Tname='张三'))
GROUP BY a.Sid);

-- 11.查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT S.* FROM SC a 
INNER JOIN Student S ON S.Sid = a.Sid
WHERE a.Sid  IN 
	(SELECT Sid FROM SC WHERE Cid IN(SELECT Cid FROM SC WHERE Sid='01'))
GROUP BY a.Sid
HAVING a.Sid != '01' AND COUNT(*)=(SELECT COUNT(*) FROM SC WHERE Sid = '01');