(附答案)数据库期中考试复习题目汇总

1、数据库概论课程期中复习题目汇总一、为某百货公司设计一个 百货管辖若干个连锁商店, 能服务于一家商店。实体类型 商店”的属性有：商店编号，店号，店址，店经理。实体类型 商品”的属性有: 商品编号，商品名，单价，产地。实体类型职工”的属性有：职工编号，职工名，性别，工资。在联系中应反映出职工参加某商店工作的开始时间，商店销售商品的有销售量。答实体三商店(店号，店名，店址亍店经理)卜商品(商品编号商品名，单价，产地亠职工(职工编号，职工名亍性别，工资卩联系P1(职工编号，店号，工作日期4P2(店号，商品编号，销售日期，销售数量)二、关系代数设有三个关系：S(S#,SNAME,AGE,SEX)SC(S

2、#,C#,GRADE)C(C#,CNAME,TEACHER)说明：S# 学号 SNAME 学生姓名 AGE 年龄 SEX 性别C# 课程号 CNAME 课程名 TEACHER 教师 GRADE 成绩 试用关系代数写出查询语句。(1) 检索 LIU 老师所授课程的课程号、课程名。(2) 检索年龄大于 23 岁的男学生的学号与姓名。(3) 检索学号为 S3 学生所学课程的课程名与任课教师名。(4) 检索至少选修 LIU 老师所授课程中一门课程的女学生的姓名。E-R 模型。每豕商店经宫右干商品， 每豕商店有右干职工， 但每个职工只集。试画出反映商店、商品、职工实体类型及联系类型的ER 图，并将其转换

3、成关系模式M /、1n /p职ZC 工作一商店商品遍号编工资销售日工作日期 Q店址销售数量姓I性别单价*Y产地*店号兴丫店名检索 WANG 同学不学的课程号。(6) 检索至少选修两门课程的学生学号。(7) 检索全部学生都选修的课程的课程号与学生学号。(8) 检索选修课程包含 LIU 老师所授课程的学生学号。(用汰代表自然连接)：(1) nc#,CNAME(OTEACHER=LIU(C)(2)ns#,SNAME(AGE23ASEX二 男(S)(3)nCNAME,TEACHER(A#=S3(SC*C)(4)nSNAME(AEX=女ATEACHER二LIU(S00SC*C)(5)nC#(C)-nc#

4、(ANAME=WANG(S00SC)(6)ns#(A=4A2r(SCXSC) (SC自乘之后，同一个学号 下两个课程号不同的元组)(7)nc#,s#(SCo(n#,C#(SC)*n#(S)(8)nS#(AEACHER=LIU(S00SCOC)三、SQL 语句(1)1、建立一个数据库和五张表的表结构。(1)/*员工人事表employee */emp_ nochar(5)Not nullprimary key员工编号emp_ namechar(10)Not null员工姓名sexchar(1)Not null性别deptchar(4)Not null所属部门titlechar(6)Not null

5、职称date hireddatetimeNot null到职日birthdaydatetimeNull生日salaryintNot null薪水addrchar(50)null住址Mod datedatetimeDefault(getdate()操作者(2)/*客户表customer */cust_idchar(5)Not nullprimary key客户号cust namechar(20)Not null,客户名称addrchar(40)Not null,客户住址tel nochar(10)Not null,客户电话zipchar(6)null邮政编码(3)/*销售主表sales */or

6、der_ nointNot nullprimary key订单编号cust_idchar(5)Not null,客户号sale_idchar(5)Not null,业务员编号tot_amtnumeric(9,2)Not null,订单金额order_datedatetimeNot null,订货日期ship_datedatetimeNot null,岀货日期inv oice_ nochar(10)Not null发票号码(4)/*销货明细表sale_item */order_ nointNot null,primary key订单编号proddchar(5)Not null,产品编号qtyin

7、tNot null销售数量uni t_pricen umeric(7,2)Not null单价order_datedatetimenull订单日期(5)/*产品名称表product */proddchar(5)Not nullprimary key产品编号prod_ namechar(20)Not null产品名称4、通过 T-SQL 语句修改表约束。1） 分别为每张表建立主键约束。2）为相关表建立外键约束。3） 在表 employee 加入 CHECK 约束：输入的员工编号必须以E开头的 5位数编号， 性别只 能为 M/F。4）为销售主表 sales 中的发票编号字段建立 UNIQUE 约束

8、。5）创建一个视图，该视图只含上海客户信息，即客户号、客户姓名、住址6） 对视图添加一条记录数据（注意：分别查看customer 表和该视图的结果）7）删除视图中所有姓 王”的客户数据8）通过视图修改表内某一客户的姓名9） 有两个基本表 employee 和 sales,创建一个视图，该视图包含相同业务员的编号、姓名、 订单号、销售总金额10） 将上述视图中订单号为10001 的记录的销售总金额改为 6000011） 给上述视图添加一条记录数据12） 删除上述视图（1）建立数据库 jianghongcreate database jia nghongon（n ame=jia nghon g,数

9、据库 jia ngho ng.mdf,size=3,maxsize=10,filegrowth= 1 ）log on（n ame=jia nghon glog,数据库 jia ngho nglog.ldf.size=1,maxsize=5, filegrowth=1) go(2)建立数据表1创建 employee 表： create table employee(emp_ no char(5) not n ull primary key,emp_ name char(10) not n ull,sex char(1) not n ull,dept char(4) not n ull, titl

10、e char(6) not null, data_hired datetime not n ull, birthdaydatetime n ull, salary int not n ull, addr char(50) null, Mod_date datetimeDefault(getdate(),)2创建 customer 表:create table customer(cust_id char(5) not n ull primary key,cust_ name char(20) not n ull, addr char(40) not null, tel_no char(10) n

11、ot null, zip charn ull,)3创建 sales 表：create table sales(order_ no int not n ull primary key, cust_id char(5) not n ull,sale_id char(5) not n ull, tot_amt nu meric(9,2) not nu II, order_date datetime not nu II,ship_date datetime not nu II, inv oice_ no char(10) not n ull,)4创建 sale_item 表：create table

12、sale_item (order_ no int not null , prod_id char(5) not n ull , qty int not null,uni t_price nu meric(7,2) not nu II, order_date datetime n ull, primary key (order_no,prodd),)5创建 product 表:create table product(prod_id char(5) not n ull primary key,prod_ name char(20) not nu II,)(3) 为相关表建立外键约束1alter

13、table sales add foreig n key (cust_id) refere nces customer(cust_id);2alter table sale_item add foreig n key (order_ no) refere nces sales(order_ no);3alter table sale_item add foreig n key (prod_id) refere nces product(prod_id);(4) 为表 employee 加入 check 约束：alter table employee add con stra int c1 ch

14、eck(emp_ no like E_and (sex=M or sex=f);(5) 为表 sales 发票号建立 unique 约束：alter table sales add uniq ue(i nv oice_ no);(6) 创建一个视图，该视图只含上海客户信息，即客户号、客户姓名、住址：create view sh_kehuasselect cust_id,cust_ name,addrfrom customerwhere addr like 上海2(7) 删除视图中所有姓“王”的客户数据：DELETEFROM sh_kehuWHERE cust_name LIKE 王 %(8)

15、通过视图修改表内某一客户的姓名update sh_kehuset cust_name= 江洪好where cust_id=4224(9) 有两个基本表 employee 和 sales，创建一个视图，该视图包含相同业务员 的编号、姓名、订单号、销售总金额：create view em_saasselect sale_id,emp_ name,order_ no,tot_amtfrom employee,saleswhere employee.emp_ no=sales.sale_id(10)将上述视图中订单号为 10001 的记录的销售总金额改为 60000：update em_saset t

16、ot_amt=60000where order_no=10001(11) 删除上述视图：drop view em_sadrop view sh_kehu5、使用 SQL 语句实现下述查询操作。1)查找表中所有姓刘的职工的工号，部门，薪水2） 查找所有定单金额高于 20000 的所有客户编号3） 查找出职称为经理和职称为职员的女员工的姓名、职称、性别。4） 选取销售数量最多的前 5 条订单订单号、数量。5） 按部门进行汇总，统计每个部门的总工资6） 由 employee 表中查找出薪水最高的员工信息。7） 由 sales 表中查找出订单金额大于“ E0013 业务员在 1996/10/15 这天

17、所接每一张订单的金 额”的所有订单。8） 检索 product 表和 sale_item 表中数量大于 2 的相同产品的产品编号、产品名称、数量、 单价。9） 查找出 employee 表中住址相同的员工的姓名、性别、职称、薪水、住址。10） 查找所有经理的姓名、职称、薪水。11） 查找出姓 王”并且姓名的最后一个字为功”的员工。12） 查找住在上海或北京的女员工，并显示其姓名、所属部门、职称、住址。13） 在表 sales 中挑出销售金额大于等于10000 元订单。14） 选取订单金额最高的前10%的订单数据。15） 查找出职称为经理或职称为职员的女员工的信息。16） 删除 sales 表中

18、作废的订单（其发票号码为I000000004 ）。17） 计算出一共销售了几种产品。18） 显示 sale_item 表中每种个别产品的订购金额总和，并且依据销售金额由大到小排列来 显示出每一种产品的排行榜。19） 计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排序。20） 由 sales 表中查找出销售金额最高的订单。21） 由 sales 表中查找出订单金额大于“ E0013 业务员在 1996/10/15 这天所接任一张订单的金 额”的所有订单，并显示承接这些订单的业务员和该条订单的金额。22） 找出公司女业务员所接的订单。23） 找出公司中姓名相同的员工，并且依据员工

19、编号排序相识这些员工信息。24） 找出目前业绩未超过200000 元的员工。25） 计算公司内各个部门的工资支出总和。26） 计算每一产品销售数量总和与平均销售单价。27） 查找出 employee 表中部门相同且住址相同的女员工的姓名、性别、职称、薪水、住址。28） 检索 product 表和 sale_item 表中相同产品的产品编号、产品名称、数量、单价。29） 检索 product 表和 sale_item 表中单价高于 2400 元的相同产品的产品编号、产品名称、 数量、单价。30） 查找员工的编号、姓名、部门和出生日期，如果出生日期为空值，-显示日期不详，并按部门排序输出，日期格式

20、为 yyyy-mm-dd。31） 查找与喻自强在同一个单位的员工姓名、性别、部门和职称32） 查找商品名称为 14 寸显示器商品的销售情况，-显示该商品的编号、销售数量、单价和金额33） 在销售明细表中按产品编号进行汇总，统计每种产品的销售数量和金额34） 按客户编号统计每个客户1996 年的订单总金额35） 查找有销售记录的客户编号、名称和订单总额36） 查找在 1997 年中有销售记录的客户编号、名称和订单总额37） 查找一次销售最大的销售记录38） 查找至少有 3 次销售的业务员名单和销售日期39） 用存在量词查找没有订货记录的客户名称40） 使用左外连接查找每个客户的客户编号、名称、订

21、货日期、订单金额-订货日期不要显示时间，日期格式为yyyy-mm-dd-按客户编号排序，同一客户再按订单降序排序输出41） 查找 16MDRAM 的销售情况，要求显示相应的销售员的姓名、-性别，销售日期、销售数量和金额，其中性别用男、女表示42） 查找每个人的销售记录，要求显示销售员的编号、姓名、性别、-产品名称、数量、单价、金额和销售日期43） 查找销售金额最大的客户名称和总货款44） 查找销售总额少于 1000 元的销售员编号、姓名和销售额45） 查找至少销售了 3 种商品的客户编号、客户名称、商品编号、商品名称、数量和金额46） 查找至少与世界技术开发公司销售相同的客户编号、名称和商品编

22、号、商品名称、数量 和金额47） 统计表中员工的薪水在40000-60000 之间的人数48） 查询表中的同一部门的职工的平均工资，但只查询住址是上海市的员工49） 将表中住址为”上海市”的员工住址改为北京市”50） 查找业务部或会计部的女员工的基本信息。51） 显示每种产品的销售金额总和，并依销售金额由大到小输出。52） 选取编号界于 C000 和 C0004 的客户编号、客户名称、客户地址。53） 将业务部员工的薪水上调3%。54） 由 employee 表中查找出薪水最低的员工信息。55） 使用 join 查询客户姓名为客户丙”所购货物的客户名称,定单金额，”定货日期，”电话 号码”56

23、） 计算P0001 产品的平均销售单价57） 找出公司女员工所接的定单58） 找出同一天进入公司服务的员工59） 找出目前业绩超过 232000 元的员工编号和姓名。60） 查询出 employee 表中所有女职工的平均工资和住址在上海市的所有女职工的平均 工资61） 在 employee 表中查询薪水超过员工平均薪水的员工信息。62） 找出目前销售业绩超过 40000 元的业务员编号及销售业绩， 并按销售业绩从大到小排序。63） 找出公司男业务员所接且订单金额超过2000 元的订单号及订单金额。64） 查询 sales 表中订单金额最高的订单号及订单金额。65） 查询在每张订单中订购金额超过

24、24000 元的客户名及其地址。66） 求出每位客户的总订购金额，显示出客户号及总订购金额，并按总订购金额降序排列。67） 求每位客户订购的每种产品的总数量及平均单价，并按客户号，产品号从小到大排列。68） 查询订购了三种以上产品的订单号。69） 查询订购的产品至少包含了订单10003 中所订购产品的订单。70） 在 sales 表中查找出订单金额大于“ E001 业务员在 1996/11/10 这天所接每一张订单的金 额”的所有订单，并显示承接这些订单的业务员和该订单的金额。71） 查询末承接业务的员工的信息。72） 查询来自上海市的客户的姓名，电话、订单号及订单金额。73） 查询每位业务员

25、各个月的业绩，并按业务员编号、月份降序排序。74） 求每种产品的总销售数量及总销售金额，要求显示出产品编号、产品名称，总数量及总 金额，并按产品号从小到大排列。75) 查询总订购金额超过C0002 客户的总订购金额的客户号，客户名及其住址。76) 查询业绩最好的的业务员号、业务员名及其总销售金额。77) 查询每位客户所订购的每种产品的详细清单，要求显示出客户号，客户名，产品号，产 品名，数量及单价。78) 求各部门的平均薪水，要求按平均薪水从小到大排序。1、查找表中所有姓刘的职工的工号，部门，薪水selectemp_no,emp_name,dept,salaryfromemployeewher

26、eemp_name like 刘2、查找所有定单金额高于 20000 的所有客户编号selectcust_idfrom saleswheretot_amt200003、查找出职称为经理和职称为职员的女员工的姓名、职称、性别。selectemp_name,title,sexfromemployeewheretitle=经理ortitle=职员and sex=F4、选取销售数量最多的前 5 条订单订单号、数量。select top5 with tiesorder_no,sup_id,qtyfromsale_itemorder byqty desc5、按部门进行汇总，统计每个部门的总工资select

27、dept,sum(salary)fromemployeegroup bydept6、由 employee 表中查找出薪水最高的员工信息。select *fromemployeewheresalary=(selectmax(salary )fromemployee )7、 由 sales 表中查找出订单金额大于 “ E0013t 务员在 1996/10/15 这天所接每- 张订单的金额”的所有订单。select *from saleswheretot_amtall(selecttot_amtfromsaleswheresale_id=E0013andorder_date=佃 96/10/15)o

28、rder bytot_amt8、 检索 product 表和 sale_item 表中数量大于 2 的相同产品的产品编号、产品 名称、数量、单价。dd,a.qty,a.unit_price,d_namefromsale_item as a innerjoin product as b 如果改成 leftjoin/rightjoin 试分析结果*/on (d_id=_id)anda.qty2order d_id9、 查找出 employee 表中住址相同的员工的姓名、性别、职称、薪水、住址。selecta.emp_name,a.s

29、ex,a.title,a.salary,a.addr,b.emp_name,b.sex,b.title,b.salary,b.addrfromemployee as a innerjoin employee as bon(a.emp_no!=b.emp_no) and(a.emp_nameb.emp_name) and (a.addr=b.addr)10、查找所有经理的姓名、职称、薪水。selectemp_name,title,salaryfromemployeewheretitle=经理11、查找出姓 王”并且姓名的最后一个字为 功”的员工。select*fromemployeewheree

30、mp_namelike 王%功12、查找住在上海或北京的女员工，并显示其姓名、所属部门、职称、住址。selectemp_name,dept title,addrfromemployeewheresex=Fand (addr like 北京 % or addr like 上海 %)13、在表 sales 中挑出销售金额大于等于 10000 元订单。select*from saleswheretot_amt=1000014、选取订单金额最高的前 10%的订单数据。selecttop10 percent *fromsalesorderbytot_amt desc15、查找出职称为经理或职称为职员的女

31、员工的信息。select*from employeewheresex=Fand ( title=经理or title=职员)16、 删除 sales 表中作废的订单(其发票号码为 1000000004)。deletefromsale_itemwhereorder_noin(select order_nofrom saleswhereinvoice_no=I000000004)17、计算出一共销售了几种产品。selectcount(distinctprod_id)fromsale_item18、显示 sale_item 表中每种产品的订购金额总和，并且依据销售金额由大到小 排列来显示出每一种产品

32、的排行榜。selectprod 口 d,sum(unit_price*qty)fromsale_itemgroupbyprod_idorderbysum(unit_price*qty) desc佃、计算每一产品和月的销售金额总和，并将结果按销售(月份，产品编号) 排序。selectprodd,month(order_date),sum(unit_price*qty) fromsale_itemgroupbyprod_id,month(order_date) orderbyprod_id,month(order_date)selectprodd,month(order_date),sum(uni

33、t_price*qty) fromsale_item groupbyprod_id,month(order_date) orderby month(order_date),prod_id20、由 sales 表中查找出销售金额最高的订单。select*fromsales wheretot_amt= (select max(tot_amt) from sales)21、由 sales 表中查找出订单金额大于 “ E001 业务员在 1996/10/15 这天所接任 张订单的金额”的所有订单，并显示承接这些订单的业务员和该条订单的金额。selectsale_id,tot_amtfromsalesw

34、heretot_amt all(select tot_amtfrom saleswhere sale_id=E0013 and order_date=佃 96-10-15)22、找出公司女业务员所接的订单。select*fromsaleswheresale_idin(select emp_nofrom employeewhere sex=f)23、找出公司中姓名相同的员工，并且依据员工编号排序相识这些员工信息。select*fromemployeeawhereexists(select emp_namefrom employee bwhere a.emp_name=b.emp_name and

35、 a.emp_no b.emp_no) orderbyemp_name24、找出目前业绩未超过 2000 元的员工。select*fromemployeewhereemp_noin (select sale_id from sales group by sale_id havingsum(tot_amt)2000)25、计算公司内各个部门的工资支出总和。selectdept,sum(salary) fromemployee groupbydept26、计算每一产品销售数量总和与平均销售单价。selectprodd,sum(unit_price*qty),avg(unit_price*qty)

36、fromsale_itemgroupbyprod_id27、查找出 employee 表中部门相同且住址相同的女员工的姓名、 性别、 职称、薪水、住址。Selecta.emp_name,a.sex,a.title,a.salary,a.addr fromemployeeas a inner joinemployee as b on(a.dept=b.dept)and(a.addr=b.addr) wherea.sex=Fandb.sex=Fand a.emp_nob.emp_no Selecta.emp_name,a.sex,a.title,a.salary,a.addrfromemploye

37、e a , employee b where(a.dept=b.dept) and (a.addr=b.addr)anda.sex=Fand b.sex=F anda.emp_nob.emp_no28、检索 product 表和 sale_item 表中相同产品的产品编号、产品名称、数量、 单价。dd,d_name,a.qty,a.unit_price fromsale_itema, product d_id=d_id/*将连接设为右连接*/ -dd,d_name,a.qty,a.unit_pric

38、e fromsale_itema, product d_id=* d_dd,d_name,a.qty,a.unit_price fromsale_itemas a right outer joinproduct as b on(d_id=d_id)29、检索 product 表和 sale_item 表中单价高于 2400 元的相同产品的产品编号、 产品名称、数量、单价。d_id,d_name,qty,unit_price fromproduct,sa

39、le_itemwhere unit_price2400 d_id=sale_d_id orderbysale_d_id30、查找员工的编号、姓名、部门和出生日期，如果出生日期为空值， -显示日期不详，并按部门排序输出，日期格式为 yyyy-mm-dd。selectemp_no ,emp_name ,dept ,isnull(convert(char(10),birthday,120 日期不详)birthday fromemployee orderby dept31、查找与喻自强在同一个单位的员工姓名、性别、部门和职称selectemp

40、_no,emp_name,dept,titlefromemployeewhereemp_name喻自强and dept in(selectdept from employeewhereemp_name=fj 自强)32、按部门进行汇总，统计每个部门的总工资selectdept,sum(salary)fromemployeegroupby dept33、查找商品名称为 14 寸显示器商品的销售情况，-显示该商品的编号、销售数量、单价和金额dd,qty,unit_price,unit_price*qty totpricefromsale_item a,product bwhe

41、d_id=d_id and prod_name=14 寸显示器34、在销售明细表中按产品编号进行汇总，统计每种产品的销售数量和金额selectprodd,sum(qty) totqty,sum(qty*unit_price) totpricefrom sale_itemgroup byprod_id35、 使用 convert 函数按客户编号统计每个客户佃 96 年的订单总金额selectcust_id,sum(tot_amt) totpricefrom saleswhereconvert(char(4),order_date,120)=1996group bycust

42、_id36、查找有销售记录的客户编号、名称和订单总额selecta.cust_id,cust_name,sum(tot_amt) totpricefrom customera,sales bwherea.cust_id=b.cust_idgroup bya.cust_id,cust_name37、查找在 1997 年中有销售记录的客户编号、名称和订单总额selecta.cust_id,cust_name,sum(tot_amt) totpricefrom customera,sales bwherea.cust_id=b.cust_id and convert(char(4),order_da

43、te,120)=1997group bya.cust_id,cust_name38、查找一次销售最大的销售记录selectorder_no,cust_id,sale_id,tot_amtfrom saleswhere tot_amt=(selectmax(tot_amt)from sales)39、查找至少有 3 次销售的业务员名单和销售日期select emp_name,order_datefrom employee a,sales bwhere emp_no=sale_id and a.emp_no in(select sale_idfrom salesgroup by sale_idha

44、ving count(*)=3)order by emp_name40、用存在量词查找没有订货记录的客户名称select cust_namefrom customer awhere not exists(select *from sales bwhere a.cust_id=b.cust_id)41、使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额-订货日期不要显示时间，日期格式为yyyy-mm-dd-按客户编号排序，同一客户再按订单降序排序输出selecta.cust_id,cust_name,convert(char(10),order_date,120),tot_amtfro

45、m customer a left outer join sales b ona.cust_id=b.cust_idorder by a.cust_id,tot_amt desc42、查找 16MDRAM 的销售情况，要求显示相应的销售员的姓名、-性别，销售日期、销售数量和金额，其中性别用男、女表示select emp_name 姓名，性另廿=case a.sex when m then 男 when f then 女else 宋end,销售日期=isnull(convert(char(10),c.order_date,120),日期不详),qty 数量,qty*unit_price as 金

46、额from employee a, sales b, sale_item c,product dwhere d_name=16M DRAM _id=dd and emp_no=b.sale_idand b.order_no=c.order_no43、查找每个人的销售记录，要求显示销售员的编号、姓名、性别、-产品名称、数量、单价、金额和销售日期select emp_no 编号,emp_name 姓名, 性另廿=case a.sex when m then 男 when fthen 女else 宋end,prod_name 产品名称，销售日期=isnull(convert(char(10),c.order_date,120)，日期 不详*),一qty 数量,qty*unit_price as 金额from employee a left outer join sales b ona.emp_no=b.sale_id, sale_item c,productdwhere _id=d_id andb.order_no=c.order_no44、查找销售金额最大的客户名称和总货款select cust_name,d.cust_sumfrom customer a,(select cust_id,cust_sumfrom (selec