oracle复习资料

一、简单查询1.查询XS表中各个同学的所有信息。

Select * from xs2.查询XS表中各个同学的姓名、专业名和总学分。

select xm,zym,zxf from xs;3.查询XS表中所有同学的学号、姓名和总学分，结果中各列的标题分别指定为“学号”、“姓名”和“总学分”。

Select xh as 学号, xm as 姓名,zxf as 总学分 from xs;4.查询XS表中的学生数据来自哪些专业（使用DISTINCT子句消除结果集中的重复行）。

Select distinct zym from xs;5.查询XS表中每个学生的学号、姓名和年龄信息。

Select xh,xm,to_number(to_char(sysdate,'yyyy'))-to_number(to_char(cssj,'yyyy')) as nl from xs;6.查询XS表中专业为“计算机”的同学的情况。

Select * from xs where zym='计算机';7.查询XS表中1980年出生的学生姓名和专业情况。

Select xm,zym from xs where to_number(to_char(cssj,'yyyy'))=1980;8.查询XS表中专业名为“计算机”或“电子”或“应用数学”的学生的情况。

Select * from xsWhere zym in ( '计算机','电子','应用数学');9.查询XS表中姓“张”且单名的学生的情况。

Select * from xsWhere xm like '张_';10.查询XS表中总学分尚未确定的学生情况。

Select * from xsWhere zxf is null11.查询XS表中专业为“计算机”且总学分尚未确定的学生情况。

Select * from xsWhere zym='计算机' and zxf is null12.从XS表中查询学生的基本信息，要求按照总学分从高到低排序，学分相同时，按学号由低到高排序。

Select * from xsOrder by zxf desc, xh二、数据汇总1.求选修了“001”课程的学生的平均成绩。

Select avg(cj) as 平均成绩 from xs_kc where kch='001';2.求选修了“002”课程的学生的最高分和最低分。

Select max(cj) as 最高分, min(cj) as 最低分from xs_kc where kch='002';3.求学号为“4102101”学生的总成绩。

Select sum(cj) as 总成绩 from xs_kc where xh='200901';4.求专业为“计算机”的学生的总人数。

Select count(*) as 总人数 from xs where zym='计算机';5.求选修了“001”课程的学生的人数。

Select count(*) 人数 from xs_kc where kch='001';6.求选修了课程的学生的人数。

Select count(distinct xh) 人数 from xs_kc;7.统计各个专业的学生数。

（按专业分组）select zym, count(*) as 人数 from xsgroup by zym;8.统计各个专业的男女生人数。

格式如下：专业性别人数………………select zym,xb, count(*)人数from xsgroup by zym,xb;9.查找平均成绩在80分以上的学生的学号和平均成绩。

Select xh, avg(cj) 平均成绩 from xs_kcGroup by xh having avg(cj)>=80;10.查找选修课程超过2门成绩在80分以上的学生的学号和成绩高于80分的门数。

Select xh, count(*) 门数 from xs_kcWhere cj>=80Group by xhHaving count(*)>=2;11.显示每个学生的学号和平均成绩，并显示总平均成绩。

（使用NUION子句）格式如下：学号平均成绩…………总平均成绩……select xh, avg(cj) 平均成绩 from xs_kcgroup by xhunion allselect '总平均成绩',avg(cj) from xs_kc;12.产生一个结果集，包括每个专业的学生人数及学生总人数。

（使用NUION子句）格式如下：专业人数…………总人数……select zym, count(*) 人数 from xsgroup by zymunion allselect '总人数',count(*) from xs;表结构操作1.创建表XS1（包括学号、姓名、性别和出生日期字段，各个字段属性自定），然后使用SELECT子句向表XS1中插入多行数据（专业为“计算机”的学生数据）。

Create table xs1( xh char(6) not null primary key,xm char(8) not null,xb char(2) default(1),cssj date null);goinsert into xs1select xh,xm,xb,cssjfrom xs where zym='计算机';2.修改XS1表的结构，增加1个“平均成绩”字段，并根据XS_KC表修改每个学生的“平均成绩”。

Alter table xs1Add 平均成绩number default (0) ;Update xs1Set 平均成绩=(select avg(cj) from xs_kc where xh=xs1.xh);3.将XS表中计算机专业的学生的总学分增加10分。

Update xsSet zxf=zxf+10Where zym='计算机';4.将XS表中学号为“200901”的同学的总学分增加4分,备注改为“提前修完一门课程”。

Update xsSet zxf=zxf+4, bz='提前修完一门课程'Where xh='200901';5.将XS_KC表中学号为“4102101”的同学的“计算机基础”课程的成绩增加10分。

方法1: 用连接Update xs_kcSet cj=cj+10From kc,xs_kcWhere xs_kc.kch=kc.kch and kcm='计算机基础'and xh='200901';方法2: 用子查询Update xs_kcSet cj=cj+10Where kch= (select kch from kc where kcm='计算机基础')And xh='200901';6.修改XS1表的结构，增加1个“总学分”字段，并使其值为该学生所学各门功课的学分之和。

Alter table xs1Add zxf number default (0);GoUpdate xs1Set zxf=(select sum(kc.xf) from xs_kc,kcwhere xs_kc.kch= kc.kch and xs_kc.xh=xs1.xh);7.将XS1表中总学分小于40分的学生数据删除。

Delete xs1Where zxf<408.将XS_KC表中“数据库”课程的所有成绩信息删除。

方法1: 用连接delete xs_kcFrom xs_kc,kcWhere xs_kc.kch=kc.kch and kcm='数据库';方法2: 用子查询delete xs_kcWhere kch= (select kch from kc where kcm='数据库');9.将XS_KC表中其学号在XS表中不存在或其课程号在KC表中不存在的学生的成绩信息删除。

Delete xs_kcWhere xh not in (select xh from xs) orkch not in (select kch from kc);10.删除XS1表中的所有数据。

Delete xs1;创建视图：1.使用SQL语句创建视图V_SCORE2，显示计算机专业每个学生的学号、姓名、选修的课程名称及其成绩。

Create or replace view v_score2AsSelect xs.xh,xm,kcm,cjfrom xs,xs_kc,kcWhere xs.xh=xs_kc.xh and xs_kc.kch=kc.kchAnd zym='计算机';2. 使用T-SQL语句修改视图V_SCORE2，使其显示每个专业、每门课程的平均成绩，结果格式为：专业课程名平均成绩………………Create or replace view v_score2AsSelect zym,kcm,avg(cj) as 平均成绩from xs,xs_kc,kcWhere xs.xh=xs_kc.xh and xs_kc.kch=kc.kchGroup by kcm,zym;3.通过V_SCORE2查看计算机专业每门课程的平均成绩。

Select * from v_score2 Where zym='计算机';4.通过V_SCORE2查看各个专业所有课程的平均成绩，结果格式为：专业平均成绩…………select zym, avg(平均成绩) 平均成绩from v_score2group by zym;5.使用视图V_SCORE1向其基表XS表中输入数据（数据自定），并观察什么样的数据可以插入，什么样的数据不可以插入，并解释为什么。

insert into v_score1values('200914','张强','计算机','男') ;可以插入，因为插入语句正确，该学生为‘计算机’专业的学生insert into v_score1values('200917','王军','电子','男') ;不可以插入，虽然插入语句正确，但该学生的专业不为‘计算机’，违反WITH CHECK OPTION选项的限定6.使用SQL语句删除视图V_SCORE1。