SQL 练习1
SQL 练习1
一、 建立各种表
1.学生表 Student(SId,Sname,Sage,Ssex)
–SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
create table student(Sid integer,Sname varchar(20),Sage integer,Ssex varchar(20)) charset=utf8;
insert into student(Sid, Sname,Sage,Ssex) values(1,‘张三’,18,‘男’);
insert into student(Sid, Sname,Sage,Ssex) values(2,‘李四’,19,‘男’);
insert into student(Sid, Sname,Sage,Ssex) values(3,‘王五’,21,‘男’);
insert into student(Sid, Sname,Sage,Ssex) values(4,‘马六’,18,‘男’);
insert into student(Sid, Sname,Sage,Ssex) values(5,‘黛博拉’,18,‘女’);
insert into student(Sid, Sname,Sage,Ssex) values(6,‘伊芙’,20,‘女’);
2.课程表 Course(CId,Cname,TId) --CId --课程编号,Cname 课程名称,TId 教师编号
create table Course(Cid varchar(20),Cname varchar(20),Tid varchar(20)) charset=utf8;
insert into Course values(‘01’,‘语文’,‘02’);
insert into Course values(‘02’,‘数学’,‘01’);
insert into Course values(‘03’,‘英语’,‘03’);
3.教师表 Teacher(TId,Tname) --TId 教师编号,Tname 教师姓名
create table Teacher(Tid varchar(20),Tname varchar(20)) charset=utf8;
insert into Teacher values(‘01’,‘张三’);
insert into Teacher values(‘02’,‘李四’);
insert into Teacher values(‘03’,‘王五’);
4.成绩表 SC(SId,CId,score) --SId 学生编号,CId 课程编号,score 分数
create table SC(Sid varchar(10),Cid varchar(10),score decimal(18,1)) charset=utf8;
//score decimal(18,1)表示最多可以存储18位数字（整数部分加小数部分），1表示小数部分的位数。
insert into SC values(‘01’ , ‘01’ , 80);
insert into SC values(1,‘02’,81);
insert into SC values(1,‘03’,82);
insert into SC values(2,‘01’,83);
insert into SC values(2,‘02’,84);
insert into SC values(2,‘03’,85);
insert into SC values(3,‘01’,86);
insert into SC values(3,‘02’,87);
insert into SC values(3,‘03’,88);
insert into SC values(4,‘01’,89);
insert into SC values(4,‘02’,81);
insert into SC values(4,‘03’,82);
insert into SC values(5,‘01’,83);
insert into SC values(5,‘02’,84);
insert into SC values(5,‘03’,85);
insert into SC values(6,‘01’,86);
insert into SC values(6,‘02’,87);
insert into SC values(6,‘03’,88);
具体题目
1.查询" 01 “课程比” 02 “课程成绩高的学生的信息及课程分数 1.1 查询同时存在” 01 “课程和” 02 “课程的情况 1.2 查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null ) 1.3 查询不存在” 01 “课程但存在” 02 "课程的情况
1.1查询" 01 “课程比” 02 “课程成绩高的学生的信息及课程分数
select * from
(select SC.SId, SC.score from SC where SC.CId= ‘01’) as t1 inner join
(select SC.SId, SC.score from SC where SC.CId= ‘02’) as t2 on t1.SId = t2.SId;
1.2查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
select * from
(select SC.Sid,SC.score from SC where SC.Cid=‘01’)as t1 left join
(select SC.Sid,SC.score from SC where SC.Cid=‘02’) as t2 on t1.Sid=t2.Sid;
1.3 查询不存在’01’课程但存在’02’课程的情况
select *from SC
where Sid not in (select Sid from sc where Cid=‘01’) and Cid=‘02’；
2.查询平均成绩大于等于60分的同学编号和学生姓名和平均成绩
select t1.sid, t1.sname, t2.avgscore
from student as t1 inner join (
select sc.sid, avg(sc.score) as avgscore from sc group by sc.sid
having avgscore >= 60) as t2
on t1.sid = t2.sid;
3.查询在SC 表存在成绩的学生信息 //distinct去重
select DISTINCT student .*
from student, sc
where student.sid=sc.sid;
SQL 练习1
SQL 练习1
一、 建立各种表
1.学生表 Student(SId,Sname,Sage,Ssex)
–SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
create table student(Sid integer,Sname varchar(20),Sage integer,Ssex varchar(20)) charset=utf8;
insert into student(Sid, Sname,Sage,Ssex) values(1,‘张三’,18,‘男’);
insert into student(Sid, Sname,Sage,Ssex) values(2,‘李四’,19,‘男’);
insert into student(Sid, Sname,Sage,Ssex) values(3,‘王五’,21,‘男’);
insert into student(Sid, Sname,Sage,Ssex) values(4,‘马六’,18,‘男’);
insert into student(Sid, Sname,Sage,Ssex) values(5,‘黛博拉’,18,‘女’);
insert into student(Sid, Sname,Sage,Ssex) values(6,‘伊芙’,20,‘女’);
2.课程表 Course(CId,Cname,TId) --CId --课程编号,Cname 课程名称,TId 教师编号
create table Course(Cid varchar(20),Cname varchar(20),Tid varchar(20)) charset=utf8;
insert into Course values(‘01’,‘语文’,‘02’);
insert into Course values(‘02’,‘数学’,‘01’);
insert into Course values(‘03’,‘英语’,‘03’);
3.教师表 Teacher(TId,Tname) --TId 教师编号,Tname 教师姓名
create table Teacher(Tid varchar(20),Tname varchar(20)) charset=utf8;
insert into Teacher values(‘01’,‘张三’);
insert into Teacher values(‘02’,‘李四’);
insert into Teacher values(‘03’,‘王五’);
4.成绩表 SC(SId,CId,score) --SId 学生编号,CId 课程编号,score 分数
create table SC(Sid varchar(10),Cid varchar(10),score decimal(18,1)) charset=utf8;
//score decimal(18,1)表示最多可以存储18位数字（整数部分加小数部分），1表示小数部分的位数。
insert into SC values(‘01’ , ‘01’ , 80);
insert into SC values(1,‘02’,81);
insert into SC values(1,‘03’,82);
insert into SC values(2,‘01’,83);
insert into SC values(2,‘02’,84);
insert into SC values(2,‘03’,85);
insert into SC values(3,‘01’,86);
insert into SC values(3,‘02’,87);
insert into SC values(3,‘03’,88);
insert into SC values(4,‘01’,89);
insert into SC values(4,‘02’,81);
insert into SC values(4,‘03’,82);
insert into SC values(5,‘01’,83);
insert into SC values(5,‘02’,84);
insert into SC values(5,‘03’,85);
insert into SC values(6,‘01’,86);
insert into SC values(6,‘02’,87);
insert into SC values(6,‘03’,88);
具体题目
1.查询" 01 “课程比” 02 “课程成绩高的学生的信息及课程分数 1.1 查询同时存在” 01 “课程和” 02 “课程的情况 1.2 查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null ) 1.3 查询不存在” 01 “课程但存在” 02 "课程的情况
1.1查询" 01 “课程比” 02 “课程成绩高的学生的信息及课程分数
select * from
(select SC.SId, SC.score from SC where SC.CId= ‘01’) as t1 inner join
(select SC.SId, SC.score from SC where SC.CId= ‘02’) as t2 on t1.SId = t2.SId;
1.2查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
select * from
(select SC.Sid,SC.score from SC where SC.Cid=‘01’)as t1 left join
(select SC.Sid,SC.score from SC where SC.Cid=‘02’) as t2 on t1.Sid=t2.Sid;
1.3 查询不存在’01’课程但存在’02’课程的情况
select *from SC
where Sid not in (select Sid from sc where Cid=‘01’) and Cid=‘02’；
2.查询平均成绩大于等于60分的同学编号和学生姓名和平均成绩
select t1.sid, t1.sname, t2.avgscore
from student as t1 inner join (
select sc.sid, avg(sc.score) as avgscore from sc group by sc.sid
having avgscore >= 60) as t2
on t1.sid = t2.sid;
3.查询在SC 表存在成绩的学生信息 //distinct去重
select DISTINCT student .*
from student, sc
where student.sid=sc.sid;