1. 查询XS表中各个同学的所有信息。
Select * from xs
2. 查询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, datediff(yy,cssj,getdate()) “nl”from xs; 6. 查询XS表中专业为“计算机”的同学的情况。
Select * from xs where zym='计算机'; 7. 查询XS表中1980年出生的学生姓名和专业情况。
Select xm,zym from xs where year(cssj)=1980;;
8. 查询XS表中专业名为“计算机”或“电子”或“应用数学”的学生的情况。
Select * from xs
Where zym in ( '计算机','电子','应用数学'); 9. 查询XS表中姓“张”且单名的学生的情况。
Select * from xs Where xm like '张_';
10. 查询XS表中总学分尚未确定的学生情况。
Select * from xs Where zxf is null
11. 查询XS表中专业为“计算机”且总学分尚未确定的学生情况。
Select * from xs
Where zym='计算机' and zxf is null
12. 从XS表中查询学生的基本信息，要求按照总学分从高到低排序，学分相同时，按学号
由低到高排序。 Select * from xs Order 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 xs group by zym;
8. 统计各个专业的男女生人数。格式如下：
专业 „„
性别 „„
人数 „„
select zym,xb, count(*)人数 from xs
group by zym,xb;
9. 查找平均成绩在80分以上的学生的学号和平均成绩。
Select xh, avg(cj) 平均成绩 from xs_kc Group by xh having avg(cj)>=80;
10. 查找选修课程超过2门成绩在80分以上的学生的学号和成绩高于80分的门数。
Select xh, count(*) 门数 from xs_kc Where cj>=80 Group by xh
Having count(*)>=2;
11. 显示每个学生的学号和平均成绩，并显示总平均成绩。（使用NUION子句）格式如下：
学号 „„
平均成绩 „„ „„
总平均成绩
select xh, avg(cj) 平均成绩 from xs_kc group by xh union all
select '总平均成绩',avg(cj) from xs_kc;
12. 产生一个结果集，包括每个专业的学生人数及学生总人数。（使用NUION子句）格式
如下：
专业
人数
„„ „„
总人数 „„
select zym, count(*) 人数 from xs group by zym union all
select '总人数',count(*) from xs;
表结构操作
1. 创建表XS1（包括学号、姓名、性别和出生日期字段，各个字段属性自定），然后使用
SELECT子句向表XS1中插入多行数据（专业为“计算机”的学生数据）。 create table xs1
(xh char(8)not null primary key, xm varchar(10)not null, xb char(3)not null, cssj datetime null); go
insert into xs1 select xh,xm,xb,cssj from xs
where zym='计算机';
2. 修改XS1表的结构，增加1个“平均成绩”字段，并根据XS_KC表修改每个学生的“平
均成绩”。 Alter table xs1
Add 平均成绩 number default (0) ; Update xs1
Set 平均成绩=(select avg(cj) from xs_kc where xh=xs1.xh); 3. 将XS表中计算机专业的学生的总学分增加10分。
Update xs Set zxf=zxf+10 Where zym='计算机';
4. 将XS表中学号为“200901”的同学的总学分增加4分,备注改为“提前修完一门课程”。
Update xs
Set zxf=zxf+4, bz='提前修完一门课程' Where xh='200901';
5. 将XS_KC表中学号为“4102101”的同学的“计算机基础”课程的成绩增加10分。 方法1: 用连接
Update xs_kc Set cj=cj+10
From kc,xs_kc
Where xs_kc.kch=kc.kch and kcm='计算机基础' and xh='200901';
方法2: 用子查询
Update xs_kc Set cj=cj+10
Where kch= (select kch from kc where kcm='计算机基础') And xh='200901';
6. 修改XS1表的结构，增加1个“总学分”字段，并使其值为该学生所学各门功课的学
分之和。 Alter table xs1
Add zxf number default (0); Go
Update xs1
Set zxf=(select sum(kc.xf) from xs_kc,kc where xs_kc.kch= kc.kch and xs_kc.xh=xs1.xh);
7. 将XS1表中总学分小于40分的学生数据删除。
Delete xs1 Where zxf<40
8. 将XS_KC表中“数据库”课程的所有成绩信息删除。 方法1: 用连接
delete xs_kc From xs_kc,kc
Where xs_kc.kch=kc.kch and kcm='数据库'; 方法2: 用子查询
delete xs_kc
Where kch= (select kch from kc where kcm='数据库');
9. 将XS_KC表中其学号在XS表中不存在或其课程号在KC表中不存在的学生的成绩信息
删除。 Delete xs_kc
Where xh not in (select xh from xs) or kch not in (select kch from kc); 10. 删除XS1表中的所有数据。
创建视图：
Delete xs1;
1. 使用SQL语句创建视图V_SCORE2，显示计算机专业每个学生的学号、姓名、选修的课
程名称及其成绩。
Create or replace view v_score2 As
Select xs.xh,xm,kcm,cj from xs,xs_kc,kc
Where xs.xh=xs_kc.xh and xs_kc.kch=kc.kch And zym='计算机';
2. 使用T-SQL语句修改视图V_SCORE2，使其显示每个专业、每门课程的平均成绩，结果格式为：专业 课程名 平均成绩
„„
„„
„„
Create or replace view v_score2 As
Select zym,kcm,avg(cj) as 平均成绩 from xs,xs_kc,kc
Where xs.xh=xs_kc.xh and xs_kc.kch=kc.kch Group by kcm,zym;
3.通过V_SCORE2查看计算机专业每门课程的平均成绩。
Select * from v_score2 Where zym='计算机';
4.通过V_SCORE2查看各个专业所有课程的平均成绩，结果格式为：
专业 平均成绩 „„
„„
select zym, avg(平均成绩) 平均成绩 from v_score2 group by zym;
5.使用视图V_SCORE1向其基表XS表中输入数据（数据自定），并观察什么样的数据可以插入，什么样的数据不可以插入，并解释为什么。
insert into v_score1
values('200914','张强','计算机','男') ;
可以插入，因为插入语句正确，该学生为‘计算机’专业的学生 insert into v_score1
values('200917','王军','电子','男') ;
不可以插入，虽然插入语句正确，但该学生的专业不为‘计算机’，违反WITH CHECK OPTION选项的限定
6.使用SQL语句删除视图V_SCORE1。 Drop view v_score1.
Transact-SQL 数据库编程，创建自定义函数
1. 编一程序用于判断XS数据表中是否有年龄小于18岁的学生数据，如果有则显示学生的
人数，否则显示“没有年龄小于18岁的学生数据”。 set serveroutput on;
declare
n_count number(10); begin select
count(*)
into
n_count
from
xs
where
to_number(to_char(sysdate,'yyyy'))-to_number(to_char(cssj,'yyyy'))<18; if n_count<1 then
DBMS_OUTPUT.PUT_LINE('没有年龄小于18岁的学生数据'); else
DBMS_OUTPUT.PUT_LINE('大于18岁的学生'||to_char(n_count)||'人'); end if; end;
2. 编一程序显示每个学生的学号、姓名、平均成绩及成绩等级，成绩等级的判断条件如下：
平均成绩>=90 显示“优” 平均成绩<90 and平均成绩>=80
显示“良”
平均成绩<80 and平均成绩>=70 显示“中” 平均成绩<70 and平均成绩>=60 显示“及格” 平均成绩<60
select xs.xh,xm,avg(cj) 平均成绩,case when avg(cj)>=90 then '优' when avg(cj)>=80 then '良' when avg(cj)>=70 then '中' when avg(cj)>=60 then '及格' else '不及格' end 平均成绩 from xs,xs_kc where xs.xh=xs_kc.xh group by xs.xh,xm;
3. 创建一个用户自定义函数，以学号和课程名称为参数，返回该学生指定课程的学习成绩。
然后使用该函数查询学号“200901”同学“计算机网络”的成绩。 Create or replace function func1 (s_num in char,s_kcm in char) return number As
显示“不及格”
n_cj number; Begin
select cj into n_cj from xs_kc,kc
where xs_kc.kch=kc.kch and xh=s_num and kcm=s_kcm; RETURN(n_cj); end func1;
DECLARE
man_num NUMBER; BEGIN
man_num:=func1('200901','计算机网络'); dbms_output.put_line(to_char(man_num));
END;
4. 创建一个游标，返回计算机专业每个同学的学号、姓名、课程名称和成绩。
DECLARE v_xh char(6); v_xm char(8); v_kcm char(20); v_cj number(2); CURSOR XS_CUR3 IS
SELECT xs.xh,xm,kcm,cj FROM xs,kc,xs_kc
WHERE xs.xh=xs_kc.xh and kc.kch=xs_kc.kch and ZYM='计算机'; BEGIN
OPEN XS_CUR3;
FETCH XS_CUR3 INTO v_xh,v_xm,v_kcm,v_cj; WHILE XS_CUR3%FOUND LOOP
dbms_output.put_line(v_xh||' '||v_xm||' '||v_kcm||' '||to_char(v_cj)); FETCH XS_CUR3 INTO v_xh,v_xm,v_kcm,v_cj;
END LOOP; END;
CLOSE XS_CUR3;
5. 创建一个用户自定义函数，以学号为参数，返回指定学生的各科成绩及总成绩。然后使
用该函数查询学号为“200901”学生的各科成绩及总成绩。
Create or replace function func2 (s_num in char) return number As
n_cj number; Begin DECLARE v_kcm char(20); v_cj number(2); CURSOR XS_CUR3 IS
SELECT kcm,cj FROM xs_kc,kc
WHERE kc.kch=xs_kc.kch and xs_kc.xh=s_num; BEGIN OPEN XS_CUR3;
FETCH XS_CUR3 INTO v_kcm,v_cj; WHILE XS_CUR3%FOUND LOOP
dbms_output.put_line(v_kcm||' '||to_char(v_cj)); FETCH XS_CUR3 INTO v_kcm,v_cj; END LOOP; CLOSE XS_CUR3; END;
SELECT sum(cj) into n_cj FROM xs_kc,kc
WHERE kc.kch=xs_kc.kch and xs_kc.xh=s_num; return(n_cj); end func2;
DECLARE
man_num number; BEGIN
man_num:=func2('200901');
dbms_output.put_line('总成绩'||' '||to_char(man_num));
END;
6. 创建一用户自定义的函数，用于统计某一课程在每个分数段的人数。分数段分为：低于
60分，60～70分，70 ～80分，80～90分，90～100分。
课程名 计算机基础 „„ 低于60 0 „„ 60～70 3 „„ 70～80 7 „„ 80～90 5 „„ 90～100 4 „„ CREATE OR REPLACE FUNCTION rs
(v_kcm in char,cj1 out number,cj2 out number,cj3 out number,cj4 out number,cj5 out number) RETURN NUMBER AS
zs NUMBER; BEGIN
SELECT count(xh) INTO cj1 FROM xs_kc,kc
where xs_kc.kch=kc.kch and kcm=v_kcm and cj<60; SELECT count(xh) INTO cj2 FROM xs_kc,kc
where xs_kc.kch=kc.kch and kcm=v_kcm and cj>=60 and cj<70; SELECT count(xh) INTO cj3 FROM xs_kc,kc
where xs_kc.kch=kc.kch and kcm=v_kcm and cj>=70 and cj<80; SELECT count(xh) INTO cj4 FROM xs_kc,kc
where xs_kc.kch=kc.kch and kcm=v_kcm and cj>=80 and cj<90; SELECT count(xh) INTO cj5 FROM xs_kc,kc
where xs_kc.kch=kc.kch and kcm=v_kcm and cj>=90 ; SELECT count(xh) INTO zs FROM xs_kc,kc
where xs_kc.kch=kc.kch and kcm=v_kcm; RETURN(zs); END rs;
DECLARE
kcm char(10):='计算机网络'; zs number; cj1 number; cj2 number; cj3 number; cj4 number; cj5 number; BEGIN
zs:=rs(kcm,cj1,cj2,cj3,cj4,cj5);
dbms_output.put_line('计算机网络共有'||' '||to_char(zs)); dbms_output.put_line('不及格的有'||' '||to_char(cj1)); dbms_output.put_line('及格有'||' '||to_char(cj2)); dbms_output.put_line('中有'||' '||to_char(cj3)); dbms_output.put_line('良有'||' '||to_char(cj4)); dbms_output.put_line('优有'||' '||to_char(cj5)); END;
创建存储过程
1. 创建一存储过程xscj_show，显示每个学生的学号、姓名、课程名、成绩，然后执行该
存储过程。
set serveroutput on;
create or replace procedure xscj_show is
v_xh char(6); v_name char(8); v_kcm char(20); v_cj number; cursor bf is
Select xs.xh,xm,kcm,cj From xs,kc,xs_kc
Where xs.xh=xs_kc.xh and kc.kch=xs_kc.kch; begin open bf;
fetch bf into v_xh,v_name,v_kcm,v_cj; while bf%FOUND loop
dbms_output.put_line(v_xh||' '||v_name||' '||v_kcm||' '||to_char(v_cj)); fetch bf into v_xh,v_name,v_kcm,v_cj; end loop; end xscj_show;
exec xscj_show;
2. 创建一存储过程proc2，显示指定学生指定课程的成绩，然后执行该存储过程.
CREATE OR REPLACE PROCEDURE proc2 (v_xh IN varchar2, v_kcm in varchar2,
v_cj OUT number ) AS BEGIN
SELECT cj
INTO v_cj FROM XS,KC,XS_KC
WHERE XS.XH=XS_KC.XH and kc.kch=xs_kc.kch and xs.xh=v_xh and
kcm=v_kcm; END proc2; DECLARE v_cj number; begin
proc2('200901','计算机网络',v_cj); dbms_output.put_line(to_char(v_cj)); end;
3. 创建一存储过程proc3，显示其指定课程的成绩在指定范围内的学生学号、姓名、课程
名和成绩，并返回其人数，然后执行该存储过程。 create or replace procedure proc3 (s_kcm in varchar2, cj_start in number, cj_end in number, rs out number) is
v_xh char(6); v_name char(8); v_kcm char(20); v_cj number; cursor bf is
Select xs.xh,xm,kcm,cj From xs,kc,xs_kc
Where xs.xh=xs_kc.xh and kc.kch=xs_kc.kch and kcm=s_kcm and cj>=cj_start and cj<=cj_end; begin open bf; loop
fetch bf into v_xh,v_name,v_kcm,v_cj;
dbms_output.put_line(v_xh||' '||v_name||' '||v_kcm||' '||to_char(v_cj)); exit when bf%NOTFOUND; end loop;
Select count(*) into rs
From xs,kc,xs_kc
Where xs.xh=xs_kc.xh and kc.kch=xs_kc.kch and kcm=s_kcm and cj>=cj_start and cj<=cj_end;
dbms_output.put_line(to_char(rs)); end proc3; declare rs number; begin
proc3('计算机网络',60,90,rs); end;
4. 创建一存储过程xs_update,用于对指定学号指定课程名称的学生成绩进行修改。
CREATE OR REPLACE PROCEDURE xs_update (v_xh IN varchar2, v_kcm in varchar2, v_cj in number ) AS BEGIN
update xs_kc
set cj=v_cj where xh=v_xh
and kch in (select kch from kc where kcm=v_kcm); END xs_update;
5. 创建一存储过程，用于统计每个专业计算机基础成绩在各个分数段的学生人数。分数段
划分为：低于60分，60～70分，70 ～80分，80～90分，90～100分。
专业 计算机 „„ 低于60 0 „„ 60～70 3 „„ 70～80 7 „„ 80～90 5 „„ 90～100 4 „„ CREATE OR REPLACE PROCEDURE xs_tj (v_kcm IN varchar2 )
is
v_zym char(20); v_count_cj1 number; v_count_cj2 number; v_count_cj3 number; v_count_cj4 number; v_count_cj5 number;
cursor bf is Select distinct zym From xs; begin open bf; loop
fetch bf into v_zym;
select count(*) into v_count_cj1 from xs,xs_kc,kc
where xs.xh=xs_kc.xh and xs_kc.kch=kc.kch and kcm=v_kcm and cj<60;
select count(*) into v_count_cj2 from xs,xs_kc,kc
where xs.xh=xs_kc.xh and xs_kc.kch=kc.kch and kcm=v_kcm and cj<70 and cj>=60;
select count(*) into v_count_cj3 from xs,xs_kc,kc
where xs.xh=xs_kc.xh and xs_kc.kch=kc.kch and kcm=v_kcm and cj<80 and cj>=70;
select count(*) into v_count_cj4 from xs,xs_kc,kc
where xs.xh=xs_kc.xh and xs_kc.kch=kc.kch and kcm=v_kcm and cj<90 and cj>=80;
select count(*) into v_count_cj5 from xs,xs_kc,kc
where xs.xh=xs_kc.xh and xs_kc.kch=kc.kch and kcm=v_kcm and cj>=90;
dbms_output.put_line(v_zym||' '||to_char(v_count_cj1)||' '||to_char(v_count_cj2)||' '||to_char(v_count_cj3)||' '||to_char(v_count_cj4)||' '||to_char(v_count_cj5)); exit when bf%NOTFOUND; end loop;
END xs_tj;
exec xs_tj('计算机网络')；
因篇幅问题不能全部显示，请点此查看更多更全内容