笔试题 - 《mySQL》

数据表介绍
—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 分数

学生表

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-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-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 “课程成绩高的学生的信息及课程分数。
SELECT A.,B.CId,B.score FROM(SELECT FROM sc WHERE CId=’1’)A
LEFT JOIN(SELECT FROM sc WHERE CId=’2’)B
ON A.SId=B.SId
WHERE A.score>B.score
2、查询同时存在” 01 “课程和” 02 “课程的情况。
SELECT FROM (SELECT FROM sc WHERE CId=’1’)A
LEFT JOIN(SELECTFROM sc WHERE CId=’02’)B
ON A.SId=B.SId
WHERE B.SId IS NOT NULL

3、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩。
SELECT A.SId,B.Sname,A.dc FROM(SELECT SId,AVG(score)dc FROM sc GROUP BY SId)A
LEFT JOIN student B
ON A.SId=B.SId
WHERE A.dc>=60
4、检索” 01 “课程分数小于 60，按分数降序排列的学生信息。
SELECT SId,score FROM sc WHERE CId=’1’ AND score<60 ORDER BY score DESC
5、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩。
SELECT SId,MAX(CASE CId WHEN’1’ THEN score ELSE 0 END)’1’,
MAX(CASE CId WHEN ‘2’ then score ELSE 0 END)’2’,
MAX(CASE CId WHEN ‘3’ then score ELSE 0 END)’3’,
AVG(score)平均分 FROM sc
GROUP BY SId ORDER BY 平均分 DESC
6、查询出只选修两门课程的学生学号和姓名。
SELECT SId,Sname FROM student
where SId in(SELECT SId FROM(SELECT SId,COUNT(CId)课程数 FROM sc GROUP BY SId)A WHERE A.课程数=2）
7、查询男生、女生人数。
SELECT Ssex,COUNT(Ssex)人数 FROM student GROUP BY Ssex
8、查询名字中含有「风」字的学生信息。
SELECT FROM student WHERE Sname LIKE ‘%风%’
9、查询 1990 年出生的学生名单。
SELECT FROM student WHERE YEAR(Sage)=1990
10、查询课程名称为「数学」，且分数低于 60 的学生姓名和分数。
SELECT B.Sname,A.score FROM(SELECT * FROM sc WHERE score<60 and CId=(SELECT CId FROM course WHERE Cname='数学'))A
LEFT JOIN student B
ON A.SId=B.SId