oracle-数据库神器资源-CSDN文库

oracle联系

需积分: 10 21 浏览量 2018-11-28 21:00:09 上传评论收藏 70KB PDF 举报

资源推荐

资源详情

资源评论

oracle 数据库增删改查练习50例-答案

一、建表

--学生表

drop table student;

create table student (sno varchar2(10),sname varchar2(10),sage date,ssex varchar2(10));

insert into student values('01','赵雷',to_date('1990/01/01','yyyy/mm/dd'),'男');

insert into student values('02','钱电',to_date('1990/12/21','yyyy/mm/dd'),'男');

insert into student values('03','孙风',to_date('1990/05/20','yyyy/mm/dd'),'男');

insert into student values('04','李云',to_date('1990/08/06','yyyy/mm/dd'),'男');

insert into student values('05','周梅',to_date('1991/12/01','yyyy/mm/dd'),'女');

insert into student values('06','吴兰',to_date('1992/03/01','yyyy/mm/dd'),'女');

insert into student values('07','郑竹',to_date('1989/07/01','yyyy/mm/dd'),'女');

insert into student values('08','王菊',to_date('1990/01/20','yyyy/mm/dd'),'女');

--课程表

drop table course;

create table course (cno varchar2(10),cname varchar2(10),tno varchar2(10));

insert into course values ('01','语文','02');

insert into course values ('02','数学','01');

insert into course values ('03','英语','03');

--教师表

drop table teacher;

create table teacher (tno varchar2(10),tname varchar2(10));

insert into teacher values('01','张三');

insert into teacher values('02','李四');

insert into teacher values('03','王五');

--成绩表

drop table sc;

create table sc (sno varchar2(10),cno varchar2(10),score number(18,1));

insert into sc values('01','01',80.0);

insert into sc values('01','02',90.0);

insert into sc values('01','03',99.0);

insert into sc values('02','01',70.0);

insert into sc values('02','02',60.0);

insert into sc values('02','03',80.0);

insert into sc values('03','01',80.0);

insert into sc values('03','02',80.0);

insert into sc values('03','03',80.0);

insert into sc values('04','01',50.0);

insert into sc values('04','02',30.0);

insert into sc values('04','03',20.0);

insert into sc values('05','01',76.0);

insert into sc values('05','02',87.0);

insert into sc values('06','01',31.0);

insert into sc values('06','03',34.0);

insert into sc values('07','02',89.0);

insert into sc values('07','03',98.0);

commit;

二、查询

1.1、查询同时存在"01"课程和"02"课程的情况

select s.sno, s.sname, s.sage, s.ssex, sc1.score, sc2.score

from student s, sc sc1, sc sc2

where s.sno = sc1.sno

and s.sno = sc2.sno

and sc1.cno = '01'

and sc2.cno = '02';

1.2、查询必须存在"01"课程，"02"课程可以没有的情况

select t.*, s.score_01, s.score_02

from student t

inner join (select a.sno, a.score score_01, b.score score_02

from sc a

left join (select * from sc where cno = '02') b

on (a.sno = b.sno)

where a.cno = '01') s

on (t.sno = s.sno);

2.1、查询同时'01'课程比'02'课程分数低的数据

select s.sno, s.sname, s.sage, s.ssex, sc1.score, sc2.score

from student s, sc sc1, sc sc2

where s.sno = sc1.sno

and s.sno = sc2.sno

and sc1.cno = '01'

and sc2.cno = '02'

and sc1.score < sc2.score;

2.2、查询同时'01'课程比'02'课程分数低或'01'缺考的数据

select s.sno, s.sname, s.sage, s.ssex, t.score_01, t.score_02

from student s,

(select b.sno, a.score score_01, b.score score_02

from (select * from sc where cno = '01') a,

(select * from sc where cno = '02') b

where a.sno(+) = b.sno) t

where s.sno = t.sno

and (t.score_01 < t.score_02 or t.score_01 is null);

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select s.sno, s.sname, t.avg_score avg_score

from student s,

(select sno, round(avg(score), 2) avg_score

from sc

group by sno

having avg(score) >= 60

order by sno) t

where s.sno = t.sno;

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

4.1、有考试成绩，且小于60分

select s.sno, s.sname, t.avg_score avg_score

from student s,

(select sno, round(avg(score), 2) avg_score

from sc

group by sno

having avg(score) < 60

order by sno) t

where s.sno = t.sno;

4.2、包括没有考试成绩的数据

select g.*

from (select s.sno, s.sname, nvl(t.avg_score, 0) avg_score

from student s,

(select sno, round(avg(score), 2) avg_score

from sc

group by sno

order by sno) t

where s.sno = t.sno(+)) g

where g.avg_score < 60;

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

5.1、查询所有成绩的（不含缺考的）。

select s.sno,s.sname,c.count_cno,c.sum_score

from student s,

(select sno,count(cno) count_cno,sum(score) sum_score from sc group by sno order by sno) c

剩余14页未读，继续阅读

评论收藏

内容反馈

lct_81

粉丝: 0
资源: 2

oracle-数据库神器

oracle-osw神器

oracle管理神器，Toad_for_Oracle_2018_Edition_13.0.0.80_x64

Oracle数据库DBA神器

数据库两大神器【索引和锁】1

数据库连接客户端神器-DBeaver

数据库开发人员的神器

nanaSQL，数据库神器，数据库懒人专用

安装oracle11gR2数据库环境快速准备神器oracle-rdbms-server-11gR2-preinstall

oracle数据库编程ppt-期末神器

Oracle DB数据库灾难恢复神器： PRM 2.0

Oracle数据库恢复神器Dbseeker 2.1

oracle_linux_6_64（bit）上安装oracle11gR2数据库环境快速准备神器

数据库收缩神器（SQLserver）

数据库字段版对比神器

用上此神器编写数据库文档

数据库降版本，管理工具转换神器

apexsql 数据库跟踪工具（神器）

Navicat-数据库连接.rar

SSH 框架集成连接Oracle数据库的配置

Oracle数据恢复神器Dbseeker 2.5

ORACLE优化神器

Oracle数据库手册

sqlite数据库查看神器，汉化

数据库表导出WORD、Excel神器

必会神器之Redis数据库

必会神器之MySQL数据库

oracle链接工具 当年哥开发的神器了

oracle 数据库性能调优

DM DBA手记之安装篇-达梦数据库-安装手册-安装最佳实践（进阶神器）v1.3.pdf"

数据库转换神器 ESF Database Migration Toolkit - Professional

最新资源

oracle链接工具当年哥开发的神器了