数据库实验示例带答案.doc

示例1实验一实验名称：SQL语句的应用（6课时）一、实验目的通过使用SQL SERVER企业管理器建立表。模式为人事表、客户表、销售表、销售明细表、产品表。理解数据库模式的概念，理解主键约束、外键约束、UNIQUE约束和CHECK约束。通过SQL SERVER企业管理器建立表间的约束。将得到的表生成脚本，保存。二、实验环境是MS SQL SERVER 2000的中文客户端。三、实验示例create table employee1(emp_no char(5) not null,constraint emp_nochk check(emp_no like E-F0-90-90-90-9),emp_name char(10) not null,emp_sex char(1) not null,constraint emp_sexchk check(emp_sex in (m,f),emp_phone char (13) not null,constraint emp_phonechk check(emp_phone like (0-90-90-9)0-90-90-9-0-90-90-90-9),emp_add varchar(60) not null,emp_salary char(5) not null,constraint emp_salarychk check (emp_salary between 60000 and 80000)go四、实验内容与步骤1、建立五张表，每张表至少需要20条记录。（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职称date_hireddatetimeNot null到职日birthdaydatetimeNull生日salaryintNot null薪水addrchar(50)null住址（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 */order_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,出货日期invoice_nochar(10)Not null发票号码（4）/*销货明细表sale_item */order_nointNot null,primary key订单编号prod_idchar(5)Not null,产品编号qtyintNot null销售数量unit_pricenumeric(7,2)Not null单价order_datedatetimenull订单日期（5）/*产品名称表product */pro_idchar(5)Not nullprimary key产品编号prod_namechar(20)Not null产品名称2、建立表的同时创建表的约束。（1） 为每张表建立主键约束。（2） 通过拖放操作加入外键。（3） 在表employee加入CHECK约束：输入的员工编号必须以E开头的5位数编号，性别只能为M/F。（4） 为销售主表sales中的发票编号字段建立UNIQUE约束。3、通过快捷菜单得到脚本。五、实验报告实验二实验名称：使用SELECT、UPDATE/INSERT/DELETE语句。（2课时）一、实验目的理解SELECT语句的操作和基本使用方法，熟练使用UPDATE/INSERT/DELETE语句进行表操作。二、实验环境三、实验示例1、查找出职称为经理和职称为职员的女员工的姓名、职称、性别。select emp_name,title,sexfrom employeewhere title=经理or title=职员and sex=F2、选取销售数量最多的前5条订单订单号、数量。select top 5 with ties order_no,sup_id,qtyfrom sale_itemorder by qty desc3、计算出sale_item表中每一笔销售数据的销售金额，并按照销售金额的大小排序。select prod_id,qty,unit_price,(qty*unit_price)tot_amtfrom sale_itemorder by tot_amt desc四、实验内容与步骤1、 查找所有经理的姓名、职称、薪水。 select emp_name,title,salary from employee where title=经理; 2、 查找出姓“王”并且姓名的最后一个字为“功”的员工。 select * from employee where emp_name like 王_功 3、 添加一条记录至employee表（用INSERT.INTO）。 insert into employee values(E1014,a14,m,办公,经理,2012-01-20,1999-12-23,60000,北京);4、 将每个员工的薪水上调3%。 update employee set salary =salary*(1+0.03);5、 查找住在上海或北京的女员工，并显示其姓名、所属部门、职称、住址。 select emp_name,dept,title,addr from employee where sex=f and (addr=上海 or addr=北京);6、 在表sales中挑出销售金额大于等于10000元订单。 select * from sales where tot_amt=10000;7、 选取订单金额最高的前10%的订单数据。 select top 10 percent * from sales order by tot_amt desc;8、 查找出职称为经理或职称为职员的女员工的信息。 select * from employee where title=经理 or title=职员 and sex=f;9、 删除sales表中作废的订单（其发票号码为I000000004），其订货明细数据也一并删除。 delete from sales where invoice_no=I000000004; sales 表和sale_item 要建立关联，删除原则设为“层叠”10、计算出一共销售了几种产品。 select count(distinct prod_id ) from sale_item ;11、显示sale_item表中每种个别产品的订购销售金额总和，并且依据销售金额由大到小排列来显示出每一种产品的排行榜。 select prod_id,sum(qty*unit_price) as 销售金额from sale_item group by prod_id order by sum(qty*unit_price) desc ;12、计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排序。 select month(order_date) as 月份,prod_id,sum(qty*unit_price) as 销售金额from sale_item group by prod_id ,month(order_date);五、实验报告实验三实验名称：表连接JOIN（2课时）一、实验目的理解JOIN语句的操作和基本使用方法，掌握内连接、外连接、自身连接的概念和使用。二、实验环境三、实验示例1、 检索product 表和sale_item表中数量大于2的相同产品的产品编号、产品名称、数量、单价。select d_id,a.qty,a.unit_price,d_namefrom sale_item as a inner join product as b /*如果改成left join/right join 试分析结果*/on (d_id=_id) and a.qty2order by d_id2、查找出employee表中住址相同的员工的姓名、性别、职称、薪水、住址。select a.emp_name,a.sex,a.title,a.salary,a.addr,b.emp_name,b.sex,b.title,b.salary,b.addrfrom employee as a inner join employee as bon (a.emp_no!=b.emp_no) and (a.emp_nameb.emp_name) and (a.addr=b.addr)四、实验内容与步骤1、 查找出employee表中部门相同且住址相同的女员工的姓名、性别、职称、薪水、住址。 select a.emp_name,a.sex,a.title,a.dept,a.salary,a.addr,b.emp_name,b.sex,b.title,b.dept,b.salary,b.addrfrom employee as a join employee as bon (a.emp_no!=b.emp_no) and (a.emp_nameb.emp_name) and (a.dept=b.dept) and (a.addr=b.addr) and a.sex = m and b.sex = m; 2、 检索product 表和sale_item表中相同产品的产品编号、产品名称、数量、单价。 select d_id,d_name,b.qty,b.unit_price from product as a join sale_item as b on d_id=d_id order by prod_id ;3、 检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。select d_id,d_name,b.qty,b.unit_price from product as a join sale_item as b on d_id=d_id and b.unit_price2400 ;4、 分别使用左向外连接、右向外连接、完整外部连接检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。并分析比较检索的结果。 select d_id,d_name,b.qty,b.unit_price from product as a left join sale_item as b on d_id=d_id and b.unit_price2400 ; inner join left join right join full join 五、实验报告实验四实验名称：SELECT的嵌套使用，实现复杂查询（2课时）一、实验目的掌握SELECT语句的嵌套使用，实现表的复杂查询，进一步理解SELECT语句的高级使用方法。二、实验环境三、实验示例1、由employee表中查找出薪水最高的员工信息。select *from employeewhere salary= (select max(salary ) from employee )2、由sales表中查找出订单金额大于“E0107业务员在2012-1-2这天所接每一张订单的金额”的所有订单。select * from sales where tot_amt all (select tot_amt from sales where sale_id=E0107 and order_date=2012-01-02)order by tot_amt 3、利用begin.end语句实现数据查询，查询出employee表中所有女职工的平均工资和住址在上海市的所有女职工的平均工资beginselect avg(salary)from employeewhere sex like funionselect avg(salary)from employeewhere sex like f and addr like 上海end四、实验内容与步骤1、 由sales表中查找出销售金额最高的订单。select * from saleswhere tot_amt= (select max(tot_amt ) from sales )2、 由sales表中查找出订单金额大于“E0107业务员在2012-1-2这天所接任一张订单的金额”的所有订单，并显示承接这些订单的业务员和该条订单的金额。 select sale_id,tot_amt from sales where tot_amt all (select tot_amt from sales where sale_id=E0107 and order_date=2012-01-02)order by tot_amt 3、 找出公司女业务员所接的订单。 select *from saleswhere sale_id in (select emp_no from employee where sex=f)select * from sales a join employee b on a.sale_id=b.emp_no and b.sex=f order by sale_idselect a.order_no,a.cust_id,a.sale_id,a.tot_amt from sales a,employee b where a.sale_id=b.emp_no and b.sex=f4、 找出公司中姓名相同的员工，并且依据员工编号排序相识这些员工信息。select * from employee where emp_name in(select emp_name from employee group by emp_name having count(emp_name)1)order by emp_name,emp_no; select * from employee as a where exists (select * from employee b where a.emp_no!=b.emp_no and a.emp_name=b.emp_name )order by emp_name,emp_no5、 找出目前业绩未超过200000元的员工。 select * from employee where emp_no in (select sale_id from sales where tot_amt1 begin print 此删除操作可能回删除多条人事表数据! rollback transaction end*/delete from employeewhere sex=m /*结果：所影响的行数为*/3 创建AFTER-UPDATE触发器，在product表上创建触发器updproduct_trg,当对表product的记录进行修改时，触发器将给出修改时间信息。USE companyGOCREATE TRIGGER updproduct_trgON productFOR UPDATEAS /* 为触发器定义要执行的操作 */PRINT 修改的时间为:+CONVERT(char, getdate(),110) /* 显示修改的时间信息 */GOupdate product set prod_name=sql where prod_id=20（二） Instead of类型触发器1替代触发器能够使无法更新的视图支持更新功能。基于多表的视图必须使用Instead of触发器来支持基表数据的增、删、改操作。建立视图：Create View View_prod_saleitem asSELECT d_id, d_name, dbo.sale_item.order_no, dbo.sale_item.qty, dbo.sale_item.unit_price, dbo.sale_item.order_dateFROM duct INNER JOIN dbo.sale_item ON d_id = dbo.sale_d_idUSE companyGOCREATE TRIGGER InsViewprod_saleitem_TrgON View_prod_saleitemINSTEAD OF INSERTASBEGIN /* 定义触发器的行为*/DECLARE prod_id char(5),prod_name char(20) /* 定义局部变量*/DECLARE order_no int,qty int,unit_price numeric(7,2) ,order_date datetime/* 从逻辑表inserted中选择相应的数据存放到变量中*/SELECT prod_id=prod_id,prod_name=prod_name,order_no=order_no,qty=qty,unit_price=unit_price,order_date=order_dateFROM inserted/* 将相应变量值插入到两个基表中*/INSERT INTO product(prod_id,prod_name)VALUES(prod_id,prod_name)INSERT INTO sale_item(order_no,prod_id,qty,unit_price,order_date)VALUES (order_no, prod_id, qty,unit_price,order_date)END;GO*/insert into View_prod_saleitem values(26,javaee,50,100,23.5,2012-12-25)示例2数据库说明：这是一个大学入学注册管理系统，包含五张表classes开课Department开课系别Course开课课程Description课程名称max_students课程容量current_students现有学生数量Num_credits学分room_id 教室Major_stats专业统计Major专业Total_credits学分数Total_students人数Registered_students学生选修Student_id学生号Department开课系别Course开课课程Grade分数Rooms教室Room_id教室号Building大楼Room_number房间号Number_seats座位数Description说明Students学生Id 学号First_name名Last_name姓Major专业Current_credits当前已得学分students表描述学生信息id唯一标识学生，major是专业，current_credits是该学生已修的学分数classes表描述课程信息department是开课系，course是课程号，department和course唯一决定该课程max_students是该课程能容纳的学生数，current_students是当前选修的学生数num_credits是该课程的学分数，room_id是所在的教室位置（和rooms表相关联）rooms教室信息room_id唯一标识教室，building是所在的教学楼，room_number是房间号number_seats是教室的座位数registered_students 学生课程的成绩student_id是学生号（和students表关联），department和course描述具体课程grade是该门课的成绩1 查询特定学生所有课程的成绩（比如first_name是david,last_name是dinsmore）2 查询所有学生所有课程的成绩3 查询特定专业的所有学生名字（比如history专业）4 列出所有专业的学生名字5 查询cs系开的课所在教室的位置信息6 查询所有系开的课所在教室的位置信息7 统计music专业的学生总数和学分数8 统计所有专业的学生总数和学分数9 在students表中新加一个学生10.该学生选了一门新课（比如his系开的301课），请改变相应的表11.该学生修完新课，成绩为B，请改变相应表12 .一个学生毕业，请删除该学生所有相应信息13找出所有姓中以S.开头的学生。14找出每个专业的学生人数、已得最高学分、最低学分、平均学分、学分总数。15找出所有教室的座位数，最大教室的座位数、最小座位数。16找出各大楼的最大教室，最小教室，平均座位数、座位总数。17找出各课程尚可选修的人数。18找出开课最多的系。19找出开课最少的系。20找出选课最多的学生。21找出选课最少的学生。22找出不及格的学生。23找出各课程平均分以下的学生。24找出各系所占教室的座位数。25分别创建一个学生、选修课程的历史表，要求按时间存储学生、选修课程信息。将当前学生、选修信息全部存储到该历史表。当前学生毕业，将当前学生删除。26找出选修了HIS系开的所有课程的学生。参考答案：1查询特定学生所有课程的成绩（比如first_name是david,last_name是dinsmore）select first_name last_name Grade from Registered_students and Students where first_name=david and last_name=dinsmore and students.ID=Regjested_students.Student_id.2查询所有学生所有课程的成绩select ID course Grade from Registered_students and Students where students.ID=Regjested_students.Student_id.3查询特定专业的所有学生名字（比如history专业）select Firstname Lastname from students where major=”history”4列出所有专业的学生名字select major Firstname lastname from students group by major (order by major asc?)5查询cs系开的课所在教室的位置信息select course roomid building roomnumber from classes and rooms where classes.roomid =rooms.roomid and department=”cs”加引号6查询所有系开的课所在教室的位置信息select department course building roomnumber from classes and roomeswhere classes.roomid=rooms.roomid group by department 7统计music专业的学生总数和学分数select totalstudent tatalcredits from major_stats where major=”music” 8统计所有专业的学生总数和学分数select* from major_stats group by major9在students表中新加一个学生insert into students (id, firstname, lastname, major, currentcredits ) values(“4445”,”m”,”ml”,”cs”,”133”)10该学生选了一门新课（比如his系开的301课），请改变相应的表insert into registered_students(studentid, department,course) values(“4445”,”his”,”301”)update classes set currentstudents+=1 where department=”his” and course=”301”11该学生修完新课，成绩为B，请改变相应表Update registeredstudents set grade=”B” where studentid=”4445”Update students set currentcredits+=2 where studentid=”4445”update classes set current_students=current_students-1 where department=HIS and course=30112 一个学生毕业，请删除该学生所有相应信息 delete from studentswhere id=1205delete from regidtered_studentswhere student_id=1205update major_statsset total_students=total_students-1,total_credits=total_credits-12where major_stats.major=Computer Science13找出所有姓中以S.开头的学生。Select * From studentsWhere Last_name like “S%”14找出每个专业的学生人数、已得最高学分、最低学分、平均学分、学分总数。 Select major,count(distinct id),max(current_credits),min(current_credits),avg(current_credits),sum(current_credits)from studentsgroup by major15找出所有教室的座位数，最大教室的座位数、最小座位数。Select (Number_seats_Seats,Max(Number_Seats),Min(Number_Seats) From Rooms16找出各大楼的最大教室，最小教室，平均座位数、座位总数。找出各大楼的最大教室，select building,room_number as biggest_roomfrom rooms as Rwhere number_seats=all(select number_seatsfrom rooms as Twhere R.building=T.building)最小教室，select building,room_number as biggest_roomfrom rooms as Rwhere number_seats=all(select count(course)from classesgroup by department)19找出开课最少的系。select departmentfrom classesgroup by departmenthaving count(course)=all(select count(course)from registered_studentsgroup by student_id)21找出选课最少的学生。select student_id,count(course)from registered_studentsgroup by student_idhaving count(course)=all(select count(course)from registered_studentsgroup by student_id)22找出不及格的学生。select distinct id,first_name,last_namefrom registered_students,studentswhere id=stud