--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 分数
2、数据引入
-- 学生表 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-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
Student.SId 学号, Sname 姓名, timestampdiff(YEAR, Sage, now()) 年龄, Ssex 性别,
SCtmp.c1sc "课程01成绩", SCtmp.c2sc "课程02成绩"
from Student
natural join
(select
c1.SId, c1.score c1sc,
c2.score c2sc
from SC c1
left join SC c2
on
c1.Sid = c2.Sid
where
c1.score > c2.score
and c1.Cid = '01'
and c2.Cid = '02') SCtmp;
2.查询同时存在” 01 “课程和” 02 “课程的情况
select
Student.SId 学号, Sname 姓名, Ssex 性别,
SCtmp.c1sc "课程01成绩", SCtmp.c2sc "课程02成绩"
from Student
natural join
(select
c1.SId, c1.score c1sc,
c2.score c2sc
from SC c1
left join SC c2
on
c1.Sid = c2.Sid
where
c1.Cid = '01'
and c2.Cid = '02') SCtmp;
3.查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null)
select c1.SId 学号, c1sc "课程01成绩", c2sc "课程02成绩"
from
(select SId, score c1sc from SC where CId = '01') c1
left join (select SId, score c2sc from SC where CId = '02') c2
on c1.SId = c2.SId;
4.查询不存在” 01 “课程但存在” 02 “课程的情况
select c2.SId 学号, c1sc "课程01成绩", c2sc "课程02成绩"
from
(select SId, score c1sc from SC where CId = '01') c1
right join (select SId, score c2sc from SC where CId = '02') c2
on c1.SId = c2.SId;
5.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select SC.SId 学生编号, Sname 学生姓名, avg(score) 平均成绩
from SC
natural join Student
group by SC.SId
having avg(score) >= 60;
6.查询在 SC 表存在成绩的学生信息
select Student.SId 学生编号, Sname 姓名, timestampdiff(YEAR, Sage, now()) 年龄, Ssex 性别
from Student
natural join (select SId from SC group by SId) tmp;
原创文章 53获赞 13访问量 3821
关注
私信
展开阅读全文
作者:God-Excious