2022年2022年结构化查询语言答案 .pdf

试验二结构化查询语句一、简单查询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,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 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 null12.从 XS表中查询学生的基本信息，要求按照总学分从高到低排序，学分相同时，按学号由低到高排序。Select*from xs Order by zxf desc,xh名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 7 页 -二、数据汇总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 名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 7 页 -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.查询每个学生的基本情况及选修的课程情况，格式如下：学号姓名专业课程号成绩,方法 1：select xs.xh,xm,zym,kch,cj from xs,xs_kc where xs.xh=xs_kc.xh;方法 2：select xs.xh,xm,zym,kch,cj from xs join xs_kc on xs.xh=xs_kc.xh;2.查询选修了课程号为001 的每个学生的姓名及成绩。Select xm,cj From xs,xs_kc Where xs.xh=xs_kc.xh and kch=001;3.查询“计算机”专业且选修了“计算机基础”课程的学生的学号、姓名及成绩。方法 1：Select xs.xh,xm,cj From xs,xs_kc,kc Where xs.xh=xs_kc.xh and xs_kc.kch=kc.kch And zym=计算机 and kcm=计算机基础;方法 2：Select xs.xh,xm,cj From xs join xs_kc on xs.xh=xs_kc.xh 名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 7 页 -join kc on xs_kc.kch=kc.kch where zym=计算机 and kcm=计算机基础;4.查询选修了课程号为001 的每个学生的基本情况及成绩，若学生未选修101 号课程，也包括其情况。Select xs.*,cj From xs left join xs_kc On xs.xh=xs_kc.xh and kch=001;5.显示每门课程的平均成绩并显示总平均成绩。格式如下：课程名平均成绩,总平均,select kcm,avg(cj)平均成绩from xs_kc,kc where xs_kc.kch=kc.kch group by kcm union all select 总平均,avg(cj)from xs_kc;6.查询各个专业各门课程的平均成绩。格式如下专业课程名平均成绩,select zym,kcm,avg(cj)平均成绩 from xs,xs_kc,kc where xs.xh=xs_kc.xh and xs_kc.kch=kc.kch group by zym,kcm;7.查询“计算机”专业平均成绩在85 分以上的学生的学号、姓名和平均成绩。Select xs.xh,xm,avg(cj)平均成绩From xs,xs_kc Where xs.xh=xs_kc.xh and zym=计算机 Group by xs.xh,xm Having avg(cj)=85;四、子查询的使用1.查询平均成绩高于101 号课程平均分的课程号及平均成绩。Select kch,avg(cj)平均成绩From xs_kc Group by kch 名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 7 页 -Having avg(cj)=(select avg(cj)from xs_kc where kch=001);2.查询 001 号课程及格的学生的基本情况。方法 1:用连接查询select xs.*from xs,xs_kc where xs.xh=xs_kc.xh and kch=001 and cj=60;方法 2:用子查询select*from xs where xh in(select xh from xs_kc where kch=001 and cj=60);方法 3:用 EXISTS子查询select*from xs where exists(select*from xs_kc where xh=xs.xh and kch=001 and cj=60);3.查找比所有计算机系的学生的年龄都大的学生的情况。Select*from xs Where cssj=(select avg(cj)from xs_kc,kc where xs_kc.kch=kc.kch and kcm=计算机基础)order by cj desc;8.查询计算机基础成绩及格的学生的学号、姓名、性别和专业情况。Select xh,xm,xb,zym from xs 名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 7 页 -Where xh in(select xh from xs_kc,kc where xs_kc.kch=kc.kch and kcm=计算机基础 and cj=60);9.在 XS_KC 表中查询其学号在XS 表中不存在或其课程号在KC 表中不存在的学生的成绩信息。Select*from xs_kc Where xh not in(select xh from xs)Or kch not in(select kch from kc);五、表结构操作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);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 名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页，共 7 页 -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 zxf408.将 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 名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 7 页 -