备注：以下语句都已经改成了oracle支持的语句。
不熟的sql：
15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
？？？是不是有having的时候就不能用distinct而只能用group by呢？？？貌似是的
说明：以下五十个语句都按照测试数据进行过测试，最好每次只单独运行一个语句。
问题及描述：
--1.学生表
Student(Sno,Sname,Sage,Ssex) --Sno 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--2.课程表
Course(Cno,Cname,Tno) --Cno --课程编号,Cname 课程名称,Tno 教师编号
--3.教师表
Teacher(Tno,Tname) --Tno 教师编号,Tname 教师姓名
--4.成绩表
SC(Sno,Cno,score) --Sno 学生编号,Cno 课程编号,score 分数
*/
--创建测试数据
create table kxsh.student(
sno char(10) primary key not null,
sname varchar2(10) not null,
ssex char(2) check(ssex in ('男','女')) not null,
sage date
);
insert into kxsh.student values('2008111001','赵雷','男','1990-01-01');
insert into kxsh.student values('2008111002','钱电','男','1990-12-21');
insert into kxsh.student values('2008111003','孙风','男','1990-05-20');
insert into kxsh.student values('2008111004','李云','男','1990-08-06');
insert into kxsh.student values('2008111005','周梅','oracle decimal类型女','1991-12-01');
insert into kxsh.student values('2008111006','吴兰','女','1992-03-01');
insert into kxsh.student values('2008111007','郑竹','女','1989-07-01');
insert into kxsh.student values('2008111008','王菊','女','1990-01-20');
补充1：
date包含年月日时分秒，oracle默认格式为’dd-mon月-yy’(dd日子、mon月份、yy 2位的年，如’06-4月-99’ 1999年4月6日)。
修改日期的默认格式（临时修改，数据库重启之后仍未默认。永久修改需要修改注册表）：
alter session set nls_date_format = ‘yyyy-mm-dd’;
修改之后，我们可以用熟悉的格式添加日期类型
create urce(
cno char(2) primary key not null,
cname varchar(10) not null,
tno char(3) acher(tno)
);
insert urce values('01','语文','002');
insert urce values('02','数学','001');
insert urce values('03','英语','003');
create acher(
tno char(3) primary key not null,
tname varchar2(10) not null
);
insert acher values('001','张三');
insert acher values('002','李四');
insert acher values('003','王五');
create table kxsh.sc(
sno char(10) not null references kxsh.student(sno),
cno char(2) not null urce(cno),
score decimal(18,2),
primary key(sno,cno)
);
decimal(a,b)：a代表精度，b代表小数位数。精度是指总的数字位数（小数点左边和右边位数的总和），小数位数是指小数点右边的位数。精度和小数位数的默认值分别为18和0。如果在decimal类型中不指定这两个值，Oracle将截断数字的小数部分，而不会产生错误。
insert into kxsh.sc values('2008111001','01',80);
insert into kxsh.sc values('2008111001','02',90);
insert into kxsh.sc values('2008111001','03',99);
insert into kxsh.sc values('2008111002','01',70);
insert into kxsh.sc values('2008111002','02',60);
insert into kxsh.sc values('2008111002','03',80);
insert into kxsh.sc values('2008111003','01',80);
insert into kxsh.sc values('2008111003','02',80);
insert into kxsh.sc values('2008111003','03',80);
insert into kxsh.sc values('2008111004','01',50);
insert into kxsh.sc values('2008111004','02',30);
insert into kxsh.sc values('2008111004','03',20);
insert into kxsh.sc values('2008111005','01',76);
insert into kxsh.sc values('2008111005','02',87);
insert into kxsh.sc values('2008111006','01',31);
insert into kxsh.sc values('2008111006','03',34);
insert into kxsh.sc values('2008111007','02',89);
insert into kxsh.sc values('2008111007','03',98);
--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
--1.1、查询同时存在"01"课程和"02"课程的情况
Oracle中必须用双引号！！
select a.* , b.score [课程'01'的分数],c.score "[课程'02'的分数]"from Student a , SC b , SC c
where a.Sno = b.Sno and a.Sno = c.Sno and b.Cno = '01' and c.Cno = '02' and
b.score > c.score
--1.2、查询同时存在"01"课程和"02"课程的情况和存在"01"课程但可能不存在"02"课程的情况(不存在时显示为null)(以下存在相同内容时不再解释)
select a.* , b.score [课程"01"的分数],c.score [课程"02"的分数]
from Student a
left join SC b on a.Sno = b.Sno and b.Cno = '01'
left join SC c on a.Sno = c.Sno and c.Cno = '02'
where b.score > nvl(c.score,0)
补充：
nvl函数的作用：如果c.score为null，则用0来代替。
--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
--2.1、查询同时存在"01"课程和"02"课程的情况
select a.* , b.score [课程'01'的分数],c.score [课程'02'的分数] from Student a , SC b , SC c
where a.Sno = b.Sno and a.Sno = c.Sno and b.Cno = '01' and c.Cno = '02' and b.score < c.score
--2.2、查询同时存在"01"课程和"02"课程的情况和不存在"01"课程但存在"02"课程的情况
select a.* , b.score [课程"01"的分数],c.score [课程"02"的分数] from Student a
left join SC b on a.Sno = b.Sno and b.Cno = '01'
left join SC c on a.Sno = c.Sno and c.Cno = '02'
where isnull(b.score,0) < c.score
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论