数据库实验二(数据库的查询和更新操作)

南昌航空大学实验报告

2012年月日

课程名称：数据库概论实验名称：数据库的查询和更新操作

班级：姓名：同组人：

指导教师评定：签名：

一、实验综述

1、实验目的及要求

掌握SQL Server查询分析器的使用方法，加深对SQL和Transact-SQL语言的查询语句的理解，熟练掌握简单表的数据查询、更新、数据排序和数据连接查询的操作方法。

2、实验仪器、设备或软件

计算机,sql sever 2005

二、实验过程（实验步骤、记录、数据、分析）

（一）实验步骤

（1）查找所有经理的姓名、职称、薪水。

select emp_name,title,salary

from employee

where title like '%经理'

（2）查找出姓“王”并且姓名的最后一个字为“功”的员工。

select *

from employee

where emp_name like '王%功'

（3）将每个员工的薪水上调3%。

update employee

set salary=1.3*salary

（4）查找住在上海或北京的女员工，并显示其姓名、所属部门、职称、住址

select emp_name,dept,title,addr

from employee

where addr like '北京%' or addr like '上海%' and sex like 'f'

（5）在表sales中挑出销售金额大于等于10000元订单

select *

from sales

where tot_amt>=10000

（6）选取订单金额最高的前10%的订单数据。

use jian

go

SELECT TOP(10) PERCENT WITH TIES

*

from sales

order by tot_amt desc

（7）查找出职称为经理或职称为职员的女员工的信息

select *

from employee

where (title like '%经理' or title like '职员') and sex='f'

（8）删除sales表中作废的订单（其发票号码为I000000004）。

delete

from sales

where invoice_no='I000000004'

（9）计算出一共销售了几种产品。

select count(distinct prod_id) as sale_product_number

from sale_item

（10）显示sale_item表中每种个别产品的订购金额总和，并且依据销售金额由大到小排列来显示出每一种产品的排行榜。

select prod_id,sum(qty*unit_price) as tot_price

from sale_item

group by prod_id

order by tot_price desc

（11）计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排序。select prod_id,SUM(qty*unit_price) as tot_price,month(order_date) as M

from sale_item

group by MONTH(order_date),prod_id

order by M desc,prod_id desc

（12）由sales表中查找出销售金额最高的订单。

select TOP(1) with ties *

from sales

order by tot_amt desc

（13）由sales表中查找出订单金额大于“E0013业务员在1996/10/15这天所接任一张订单的金额”的所有订单，并显示承接这些订单的业务员和该条订单的金额。

select *

from sales

where tot_amt>all(select tot_amt from sales where sale_id like 'E0013' and order_date='1996/10/15')

（14）找出公司女业务员所接的订单。（尝试用两种方法）

方法一：

select emp_name,sex,sale_id,order_no,tot_amt

from employee,sales

where emp_no=sale_id and sale_id in (select emp_no from employee where sex like 'f')

方法二：

select emp_name,sex,sale_id,order_no,tot_amt

from employee,sales

where emp_no=sale_id and sex like 'f'

（15）找出公司中姓名相同的员工，并且依据员工编号排序显示这些员工信息。（尝试用两种方法）

方法一：

select emp_no,emp_name

from employee

where emp_name in

(select emp_name

from employee

group by emp_name

having count(*)>1)

order by emp_no

方法二：

select a.emp_no,a.emp_name

from employee a,employee b

where a.emp_no<>b.emp_no and a.emp_name=b.emp_name

order by a.emp_no

（16）找出目前业绩未超过200000元的员工。（尝试用三种以上方法）

方法一：

select emp_no,emp_name,SUM(tot_amt) as 销售总额

from employee,sales

where sale_id=emp_no

group by sale_id,emp_no,emp_name

having SUM(tot_amt)<=200000

方法二：

select emp_no,emp_name

from employee

where emp_no in

(select sale_id

from sales

group by sale_id

having sum(tot_amt)<200000)

方法三：

select sale_id,SUM(tot_amt) as 销售总金额

from sales

group by sale_id

having SUM(tot_amt)<=200000

（17）计算公司内各个部门的工资支出总和。（尝试用两种方法）

select dept,sum(salary) as 支出总和

from employee

group by dept

（18）计算每一产品销售数量总和与平均销售单价（尝试用两种方法）

select prod_id,sum(qty),avg(unit_price)

from sale_item

group by prod_id

（19）查找出employee表中部门相同且住址相同的女员工的姓名、性别、职称、薪水、住址。

select distinct a.emp_name,a.sex,a.title,a.salary,a.addr

from employee a , employee b

where a.emp_no!=b.emp_no and a.addr=b.addr and a.dept=b.dept and a.sex='F'

（20）检索product 表和sale_item表中相同产品的产品编号、产品名称、数量、单价。select distinct a.prod_id,prod_name,qty,unit_price

from product a,sale_item b

where a.prod_id=b.prod_id

order by a.prod_id

（21）检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。

select a.prod_id,b.prod_name,a.qty,a.unit_price

from sale_item a, product b

where a.prod_id=b.prod_id and a.unit_price>2400

（三）分析

在每次使用聚合函数时要对每一元组进行group by;

连接要去掉重复的（distinct）。

三、结论

1、实验结果

(1)

(2)

(3)

(4)

(5)

(6)

(7)

(8)

(9)（10）

(11)

（12）

（13）

（14）

（15）

（16）

（17）

（18）

（19）

（20）

（21）

2、分析讨论

进一步掌握SQL Server查询分析器的使用方法，加深了SQL和Transact-SQL语言的查询语句的理解，更熟练加深了对简单表的数据查询、更新、数据排序和数据连接查询的操作方法。

四、指导教师评语及成绩：

成绩：指导教师签名：

批阅日期：