数据库基础与应用复习一(共17页).doc

《数据库基础与应用复习一(共17页).doc》由会员分享，可在线阅读，更多相关《数据库基础与应用复习一(共17页).doc（17页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、精选优质文档-倾情为你奉上数据库基础与应用复习（一）必须要熟悉书上的两个数据库实例1. 教学库：包括学生、课程和选课三个基本表它们的定义分别为：学生(学生号 char(7),姓名 char(6),性别 char(2), 专业 char(10)课程(课程号 char(4),课程名 char(10),课程学分 int)选课(学生号 char(7),课程号 char(4),成绩 int)学生表：学生号姓名性别专业王明男计算机刘芹女电子张鲁男电子赵红女电气刘川男通信课程表：课程号课程名课程学分C001C+语言4C004操作系统3E002电子技术5X003信号原理4选课表：学生号课程号成绩C00178C

2、00462E00273C00194C00465X00380C00176E002722. 商品库：包括商品表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：商品

3、代号产地品牌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从商品

4、库中查询出所有商品的不同品牌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 课程.

5、课程号,课程.课程名,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)从教学库中查询出只选修了一

6、门课程的全部学生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的学生所选

7、修、而学生号为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(*) be

8、tween 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

9、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从商品库中查询出数量最多

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 产地

11、 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从教学库中查询出

12、至少选修了姓名为m1学生所选课程中一门课的全部学生。select distinct 学生.* from 学生,选课where 学生.学生号=选课.学生号 and 课程号=any(select 课程号 from 学生,选课 where 学生.学生号=选课.学生号 and 姓名=m1 )16从教学库中查询出每门课程被选修的学生人数，并按所选人数的升序排列出课程号、课程名和选课人数。select 课程.课程号,课程名,count(课程.课程号) as 人数from 课程,选课where 课程.课程号=选课.课程号group by 课程.课程号,课程名order by 人数写出下列每条语句或程序段的功

13、能假设存在名为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=IF (SELECT 专业 FROM Stude

14、nts 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表中查询出成绩大于等于平均

15、成绩的所有记录。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的值（即“刘亮”）的同学是否存在，根据不同情况显示

16、出相应信息。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(入学日

17、期)从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 门

18、数 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 学号=

19、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表中删除学

20、号为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),

21、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

22、 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; els

23、e 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 xx

24、x 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=set

25、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

26、(8),r varchar(10)set s=set 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的值的学生记录。写出下列每条语句或程序段的功能假设存在名为

27、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 stud

28、ent)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)，姓名 varc

29、har(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以大写字母显示。SEL

30、ECT UPPER(I am a student)3. 从名字为“My Table”的表中查询出所有记录。SELECT * FROM My Table4. 将字符串SOFTWARE中的SOFT提取出来，并以小写形式显示出来。SELECT LOWER(LEFT(SOFTWARE,4)5. 显示出该Score表中的全部数据，并要求当成绩为空时显示数值-1。select 学号,课程名,isnull(成绩,-1)from Score6. 显示出当前日期中的年份和月份数据。select year(getdate(),month(getdate()7. 显示出Students表中所有学生记录的学号列的前四

31、个字符。select left(学号,4) from Students8. 显示出一个整数25和当前日期getdate()的值分别占用的字节数。select datalength(25),datalength(getdate()按照下列每种功能写出相应的Transact-SQL语句或程序段。假设使用名称为AAA的数据库，它包括Students（学号 char(8)，姓名 varchar(8)，年龄 int，专业 varchar(20)，入学日期 DateTime）和Score（学号 char(8)，课程名 varchar(10),成绩 numeric(5,2)）两张表。1. 创建AAA数据库，

32、所有参数取默认值。create database AAA2. 创建Students表。create table students ( 学号 char(8) primay key, 姓名 varchar(8), 年龄 int, 专业 varchar(20), 入学日期 datetime)3. 将AAA数据库的初始大小更改为5MB，最大空间限定为10MB。ALTER DATABASE AAAMODIFY FILE ( NAME = AAA_data, SIZE = 5, MAXSIZE=10)4. 假设Students表中已存在大量学生记录，求所有“计算机软件”专业学生的平均年龄，如果平均年龄大于19则显示“平均年龄超过19”，否则显示“平均年龄没有超过19”。IF (Select Avg(年龄) From Students Where 专业=计算机软件)19 SELECT 平均年龄超过19ELSE SELECT 平均年龄没有超过19专心-专注-专业