mysql练习题_MySQL练习题

在网上找到的流传较广的50道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

# 创建学生表格与插入学生student信息
create table Student(
                        Sid nvarchar(10) NOT NULL,
                        Sname nvarchar(20) NOT NULL default '',
                        Sage nvarchar(10) NOT NUll default '',
                        Ssex nvarchar(10) NOT NULL default '',
                        PRIMARY KEY (Sid)
                    );
insert into Student values('01' , '赵雷' , '19900101' , '男');
insert into Student values('02' , '钱电' , '19901221' , '男');
insert into Student values('03' , '孙风' , '19901220' , '男');
insert into Student values('04' , '李云' , '19901206' , '男');
insert into Student values('05' , '周梅' , '19911201' , '女');
insert into Student values('06' , '吴兰' , '19920101' , '女');
insert into Student values('07' , '郑竹' , '19890101' , '女');
insert into Student values('09' , '张三' , '20171220' , '女');
insert into Student values('10' , '李四' , '20171225' , '女');
insert into Student values('11' , '李四' , '20120606' , '女');
insert into Student values('12' , '赵六' , '20130613' , '女');
insert into Student values('13' , '孙七' , '20140601' , '女');

科目表 Course

# 创建科目信息表格与插入科目信息
create table Course(
                      Cid nvarchar(10) NOT NULL,
                      Cname nvarchar(20) NOT NULL default '',
                      Tid nvarchar(10) NOT NULL default '',
                      primary key(Cid)
                    );
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

教师表 Teacher

# 创建教师表格与插入教师信息
create table Teacher(
                      Tid nvarchar(10) NOT NULL,
                      Tname nvarchar(20) NOT NULL default '',
                      primary key(Tid)
                    );
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

成绩表 SC

# 创建成绩表格与插入成绩信息
create table SC(
                    Sid nvarchar(10) NOT NULL,
                    Cid nvarchar(10) NOT NULL default '',
                    score decimal(3,1) NOT NULL default 0,
                    primary key(Sid, Cid),
                    foreign key(Sid) references student(Sid),
                    foreign key(Cid) references course(Cid)
                );
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 value