数据库基础与应用复习一.doc

数据库基础与应用复习（一）必须要熟悉书上的两个数据库实例1. 教学库：包括学生、课程和选课三个基本表它们的定义分别为：学生(学生号 char(7),姓名 char(6),性别 char(2), 专业 char(10)课程(课程号 char(4),课程名 char(10),课程学分 int)选课(学生号 char(7),课程号 char(4),成绩 int)学生表：学生号姓名性别专业0101001王明男计算机0102005刘芹女电子0202003张鲁男电子0303001赵红女电气0304006刘川男通信课程表：课程号课程名课程学分C001C+语言4C004操作系统3E002电子技术5X003信号原理4选课表：学生号课程号成绩0101001C001780101001C004620102005E002730202003C001940202003C004650202003X003800303001C001760304006E002722. 商品库：包括商品表1和商品表2两个基本表它们的定义分别为：商品表1(商品代号 char(8),分类名 char(8),单价 float,数量 int) 商品表2(商品代号 char(8),产地 char(6),品牌 char(6)商品表1：商品代号分类名单价数量DBX-134电冰箱14568DSJ-120电视机186515DSJ-180电视机207310DSJ-340电视机37265KTQ-12空调器280012WBL-6微波炉64010XYJ-13洗衣机46820XYJ-20洗衣机87312商品表2：商品代号产地品牌DBX-134北京雪花DSJ-120南京熊猫DSJ-180南京熊猫DSJ-340北京牡丹KTQ-12无锡春兰WBL-6青岛海信XYJ-13无锡小天鹅XYJ-20山西海棠根据主教材第四章所给的商品库和教学库，按照下列所给的每条SQL查询语句写出相应的功能1select x.商品代号,分类名,数量,品牌from 商品表1 x,商品表2 ywhere x.商品代号=y.商品代号从商品库中查询出每一种商品的商品代号、分类名、数量和品牌等信息2select distinct 产地from 商品表2从商品库中查询出所有商品的不同产地3select distinct 品牌from 商品表2从商品库中查询出所有商品的不同品牌4select count(distinct 产地) as 产地数from 商品表2从商品库中查询出所有商品的不同产地的总数5select x.商品代号,分类名,产地,品牌from 商品表1 x,商品表2 ywhere x.商品代号=y.商品代号 and (品牌=熊猫 or 品牌=春兰)从商品库中查询出品牌为熊猫或春兰的所有商品的商品代号、分类名、产地和品牌等信息6select 学生.学生号,count(*) as 选课门数from 学生,选课where 学生.学生号=选课.学生号group by 学生.学生号从教学库中查询出每个学生的选课门数7select 课程.课程号,课程.课程名,count(*) as 选课人数from 课程,选课where 课程.课程号=选课.课程号group by 课程.课程号,课程.课程名从教学库中查询出每门课程的选课学生数8select distinct x.* from 学生 x,选课 y,选课 z where y.学生号=z.学生号 and y.课程号z.课程号 and x.学生号=y.学生号从教学库中查询出至少选修了两门课程的全部学生9select *from 学生where 学生号 in (select 学生号 from 选课 group by 学生号 having count(*)=1)从教学库中查询出只选修了一门课程的全部学生10select x.学生号,y.学生号,y.课程号from 选课 x,选课 ywhere x.学生号=s1 and y.学生号=s2 and x.课程号=y.课程号注：一个由字母开头的标识符再加上字符前缀，则表示为一个变量，假定已保存着一个相应值。从教学库中查询出学生号为s1的学生和学生号为s2的学生所选修的共同课程的课程号11select x.*from 课程 x,选课 ywhere x.课程号=y.课程号 and y.学生号=s1and y.课程号 not in (select 课程号 from 选课 where 选课.学生号=s2)从教学库中查询出学生号为s1的学生所选修、而学生号为s2的学生没有选修的全部课程12select *from 课程where not exists (select * from 选课 where 课程.课程号=选课.课程号)从教学库中查询出所有未被学生选修的课程13select *from 课程where exists (select * from 选课 where 课程.课程号=选课.课程号)从教学库中查询出所有已被学生选修的课程14select *from 课程where exists (select 课程号 from 选课 where 课程.课程号=选课.课程号 group by 课程号 having count(*) between 2 and 4)从教学库中查询出被2至4名学生选修的所有课程15select *from 学生where exists (select * from 选课 where 学生.学生号=选课.学生号 group by 选课.学生号 having count(*)=3)从教学库中查询出同时选修了3门课程的全部学生16select *from 学生where 学生号 in (select 学生号 from 选课 group by 学生号 having count(*)103从商品库中查询出数量在10和20之间的所有商品。select * from 商品表1where 数量 between 10 and 20 （或where 数量=10 and 数量=2000 or 数量=158从商品库中查询出比所有电视机的单价都高的每种商品。select *from 商品表1where 单价all(select 单价 from 商品表1 where 分类名=电视机)或select * from 商品表1where 单价all(select max(单价) from 商品表1 where 分类名=电视机)9从商品库中查询出比所有商品单价的平均值要高的全部商品。select * from 商品表1where 单价all(select avg(单价) from 商品表1)10从商品库中查询出数量最多的一种商品。select * from 商品表1where 数量=some(select max(数量) from 商品表1)11从商品库中查询出同一类商品多于一种的所有分类名。select distinct 分类名from 商品表1group by 分类名 having count(*)112从商品库中查询出同一产地的商品只有一种的所有商品。select 商品表1.*,产地from 商品表1,商品表2where 商品表1.商品代号=商品表2.商品代号 and 产地 in (select 产地 from 商品表1 x,商品表2 y where x.商品代号=y.商品代号 group by 产地 having count(*)=1)13从商品库中查询出每种商品的总价值，并按降序排列出来。select *,单价*数量 as 总价值from 商品表1order by 总价值 desc 14从教学库中查询出至少有两名学生所选修的全部课程。select distinct x.* from 课程 x,选课 y,选课 zwhere y.课程号=z.课程号 and y.学生号z.学生号 and x.课程号=y.课程号或：select * from 课程where 课程号 in (select 课程号 from 选课 group by 课程号 having count(*)=2)15从教学库中查询出至少选修了姓名为m1学生所选课程中一门课的全部学生。select distinct 学生.* from 学生,选课where 学生.学生号=选课.学生号 and 课程号=any(select 课程号 from 学生,选课 where 学生.学生号=选课.学生号 and 姓名=m1 )16从教学库中查询出每门课程被选修的学生人数，并按所选人数的升序排列出课程号、课程名和选课人数。select 课程.课程号,课程名,count(课程.课程号) as 人数from 课程,选课where 课程.课程号=选课.课程号group by 课程.课程号,课程名order by 人数写出下列每条语句或程序段的功能假设存在名为AAA的数据库，包括Students（学号 char(8)，姓名 varchar(8)，年龄 int，专业 varchar(20)，入学日期 DateTime）和Score（学号 char(8)，课程名 varchar(10),成绩 numeric(5,2)）两张表。1SELECT * FROM StudentsWHERE DATEPART(year,入学日期) =DATEPART(year,GETDATE()从Students表中查询出所有当年（系统时间）入学的学生记录。2DECLARE MyNO CHAR(8)SET MyNO=20030001IF (SELECT 专业 FROM Students WHERE 学号=MyNO)=计算机软件 BEGIN SELECT AVG(成绩) AS 平均成绩 FROM Score WHERE 学号=MyNO ENDELSE PRINT 学号为 +MyNO+的学生不存在或不属于软件专业GO首先定义一个名为MyNo的局部变量，并给它赋初值，如果MyNo属于计算机软件专业，则显示出平均成绩，否则显示“学号为MyNo的学生不存在或不属于软件专业”。3declare a numeric(5,2)set a=(select avg(成绩) from score)select *from scorewhere 成绩=a从score表中查询出成绩大于等于平均成绩的所有记录。4declare a numeric(5,2),b numeric(5,2)set a=(select max(成绩) from score)set b=(select min(成绩) from score)print a-b求出score表中最高成绩与最低成绩的分数之差。5declare a char(6)set a=刘亮if(exists(select * from students where 姓名=a) print 姓名为+a+的同学存在！else print 姓名为+a+的同学不存在！从students表中查询姓名为a的值（即“刘亮”）的同学是否存在，根据不同情况显示出相应信息。6declare a char(8)set a=计算机select 计算机专业人数=count(*)from studentswhere left(专业,3)=a从students表中统计出专业名开头为a的值（即“计算机”）的所有学生人数。7select year(入学日期) as 入学年份,count(*) as 人数from studentsgroup by year(入学日期)从students表中分组统计出每个年份入学的学生人数。8select month(入学日期) as 入学月份,count(*) as 人数from studentsgroup by month(入学日期)从students表中分组统计出每个月份入学的学生人数。9select day(入学日期) as 入学日号,count(*) as 人数from studentsgroup by day(入学日期)从students表中分组统计出每个日子入学的学生人数。10create procedure xxk1asbegin select * from students x,score y where x.学号=y.学号end显示出AAA库中所有学生的记录信息及选课成绩11.create procedure xxk2asbegin select x.学号,x.姓名,x.专业,count(*) as 门数 from students x,score y where x.学号=y.学号 group by x.学号,x.姓名,x.专业end显示出AAA库中每个学生的学号、姓名、专业等信息及选课门数12.create procedure xxk3asbegin select 学号,avg(成绩) as 平均成绩 from score group by 学号end显示出AAA库中每个学生的平均成绩13.create procedure xxk4( a char(8),b varchar(10),c numeric(5,2)asbegin update score set 成绩=c where 学号=a and 课程名=bend修改score表中学号为a的值、课程名为b的值的学生的成绩为c的值。14.create procedure xxk5(a char(8),b varchar(10),c numeric(5,2)asbegin insert into score values(a,b,c)end向score表中插入学号为a的值、课程名为b的值、成绩为c的值的学生成绩记录。15.create procedure xxk6(a char(8),b varchar(10)asbegin delete from score where 学号=a and 课程名=bend从score表中删除学号为a的值、课程名为b的值的学生成绩记录。16.declare a char(8),b varchar(10),c numeric(5,2)declare d intset d=0declare xxx cursorfor select 学号,课程名,成绩 from scoreopen xxxfetch xxx into a,b,cwhile fetch_status=0begin set d=d+1 fetch from xxx into a,b,cendclose xxxdeallocate xxxprint d从score表中统计并显示出记录总数17.declare a char(8),b varchar(10),c numeric(5,2)declare xxx cursorfor select 学号,课程名,成绩 from scoreopen xxxfetch xxx into a,b,cwhile fetch_status=0begin print a+replicate( ,3)+b+str(c)+replicate( ,3) +(case when c=90 then 优秀 when c=70 then 良好 when c=60 then 及格 else 不及格 end) fetch from xxx into a,b,cendclose xxxdeallocate xxx显示出score表中每个成绩记录，并根据具体成绩在每条记录最后给出优秀、良好、及格、不及格等相应等级。18.declare c numeric(5,2)declare c1 int, c2 int, c3 int, c4 intset c1=0; set c2=0; set c3=0; set c4=0declare xxx cursorfor select 成绩 from scoreopen xxxfetch xxx into cwhile fetch_status=0begin if(c=90) set c1=c1+1; else if(c=70) set c2=c2+1; else if(c=60) set c3=c3+1; else set c4=c4+1 fetch from xxx into cendclose xxxdeallocate xxxprint 优秀生人数:+str(c1,5);print 良好生人数:+str(c2,5);print 及格生人数:+str(c3,5);print 及格生人数:+str(c4,5)从score表中按成绩统计并显示出优秀、良好、及格、不及格各多少人。19.declare a char(8),b varchar(10)declare c numeric(5,2)declare d intset d=80declare xxx cursorfor select 学号,课程名,成绩 from scoreopen xxxfetch xxx into a,b,cwhile fetch_status=0begin if(c=d) print a+replicate( ,3)+b+str(c,5) fetch from xxx into a,b,cendclose xxxdeallocate xxx显示出score表中成绩大于等于d值的所有记录。20.declare a char(8),b varchar(10),c numeric(5,2)declare s char(8),r varchar(10)set s=20030001set r=数学set c=84declare xxx cursorfor select 学号,课程名 from scoreopen xxxfetch xxx into a,bwhile fetch_status=0begin if(a=s and b=r) update score set 成绩=c where current of xxx fetch from xxx into a,bendclose xxxdeallocate xxx修改score表中学号为a的值、课程名为b的值的学生的成绩为c的值。21.declare a char(8),b varchar(10)declare s char(8),r varchar(10)set s=20030001set r=数学declare xxx cursorfor select 学号,课程名 from scoreopen xxxfetch xxx into a,bwhile fetch_status=0begin if(a=s and b=r) begin delete from score where current of xxx break end fetch from xxx into a,bendclose xxxdeallocate xxx从score表中删除学号为a的值、课程名为b的值的学生记录。写出下列每条语句或程序段的功能假设存在名为AAA的数据库，包括Students（学号 char(8)，姓名 varchar(8)，年龄 int，专业 varchar(20)，入学日期 DateTime）和Score（学号 char(8)，课程名 varchar(10),成绩 numeric(5,2)）两张表。1SELECT CEILING(888.33)，CEILING(-888.33) 889 -8882SELECT FLOOR(888.33)，FLOOR(-888.33) 888 -8893SELECT ROUND(888.33,0)，ROUND(888.55,0) 888 8894. SELECT LEN(I am a student)145. SELECT SUBSTRING(I am a student,8,7)student6. select 学生号,课程号,isnull(null,0) 学生号 课程号 07select 学生号,课程号,isnull(cast(null as char(6),无成绩) 学生号 课程号 无成绩8. select 学生号,课程号,isnull(cast(0 as char(6),无成绩)学生号 课程号 0根据下面所给的AAA数据库，按照下列每种功能写出相应的Transact-SQL语句或程序段假设使用名称为AAA的数据库，它包括Students（学号 char(8)，姓名 varchar(8)，年龄 int，专业 varchar(20)，入学日期 DateTime）和Score（学号 char(8)，课程名 varchar(10),成绩 numeric(5,2)）两张表。1. 以“xxxx年xx月xx日”的格式显示某日期时间型数据，假设字段名为Mydate，存于Mytable表中。SELECT DATEPART(year,Mydate) + 年 /year(Mydate) + DATEPART(month,Mydate) + 月 + DATEPART(day,MyDate) + 日 FROM Mytable2. 将字符串I am a student以大写字母显示。SELECT UPPER(I am a student)3. 从名字为“My Tab