数据库第二次实验要求

第二次实验内容

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

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

实验内容：

1.针对实验一的五张数据表，插入如下数据：

--以下语句可以直接拷贝到新建查询中执行

（1）employee表数据

insert intoemployee(emp_no,emp_name,sex,dept,title,date_hired,birthday,salary,addr) values('E0001','王胜波','m','后台部','技术经理','1998-10-10','1983-10-10',3000,'山东省潍坊市');

insert intoemployee(emp_no,emp_name,sex,dept,title,date_hired,birthday,salary,addr) values('E0002','张二','f','销售部','营销员','1996-10-10','1982-10-10',1200,'北京市澄海区');
insert intoemployee(emp_no,emp_name,sex,dept,title,date_hired,birthday,salary,addr)values('E0003','李三','f','后台部','文员','1987-10-10','1976-10-10',800,'山东省潍坊市');
insert into employee(emp_no,emp_name,sex,dept,title,date_hired,birthday,salary,addr)values('E0004','王四','m','后台部','职员','1991-10-10','1968-10-10',1000,'山东省潍坊市');
insert intoemployee(emp_no,emp_name,sex,dept,title,date_hired,birthday,salary,addr)values('E0005','老五','f','销售部','业务经理','1992-10-10','1973-10-10',1800,'上海市华景区');
insert intoemployee(emp_no,emp_name,sex,dept,title,date_hired,birthday,salary,addr)values('E0006','王老六功','m','业务部','业务员','1993-10-10','1972-10-10',1200,'山东省潍坊市');
insert into employee(emp_no,emp_name,sex,dept,title,date_hired,birthday,salary,addr)values('E0007','老七','f','后台部','销售员','1997-10-10','1975-10-10',1200,'山东省潍坊市');
insert intoemployee(emp_no,emp_name,sex,dept,title,date_hired,birthday,salary,addr)values('E0008','老八','f','后台部','职员','1989-10-10','1979-10-10',1000,'山东省潍坊市');
insert intoemployee(emp_no,emp_name,sex,dept,title,date_hired,birthday,salary,addr)values('E0009','老九','f','前台部','会计','1999-10-10','1982-10-10',1300,'北京市海淀区');
insert into employee(emp_no,emp_name,sex,dept,title,date_hired,birthday,salary,addr)values('E0010','王老十功','m','后台部','职员','1990-10-10','1975-10-10',1000,'山东省潍坊市');
insert intoemployee(emp_no,emp_name,sex,dept,title,date_hired,birthday,salary,addr)values('E0011','王老十一','m','前台部','职员','1995-10-10','1981-10-10',1000,'山东省潍坊市');
insert intoemployee(emp_no,emp_name,sex,dept,title,date_hired,birthday,salary,addr)values('E0012','王老十二','f','业务部','业务员','1998-10-10','1978-10-10',1000,'山东省潍坊市');
insert into employee(emp_no,emp_name,sex,dept,title,date_hired,birthday,salary,addr)values('E0013','王老十三','m','前台部','职员','1987-10-10','1976-10-10',1000,'山东省潍坊市');
insert intoemployee(emp_no,emp_name,sex,dept,title,date_hired,birthday,salary,addr)values('E0014','王老十四','f','销售部','职员','1996-10-10','1980-10-10',1000,'北京市澄海区');
insert intoemployee(emp_no,emp_name,sex,dept,title,date_hired,birthday,salary,addr)values('E0015','王老十五','m','业务部','业务员','1998-10-10','1978-10-10',1000,'上海市华景区');

insertinto employee(emp_no,emp_name,sex,dept,title,date_hired,birthday,salary,addr)values('E0016','王老十五','m','销售部','服务员','1989-10-10','1968-10-10',700,'山东省潍坊市');

（2）customer表数据

insertinto customer(cust_id,cust_name,addr,tel_no,zip) values('C001','白展堂','山东省潍坊市','13672245110','330010');
insert into customer(cust_id,cust_name,addr,tel_no,zip) values('C002','佟湘玉','上海市华景区','13672245111','330011');
insert into customer(cust_id,cust_name,addr,tel_no,zip) values('C003','吕秀才','江西省南昌市','13672245112','330012');
insert into customer(cust_id,cust_name,addr,tel_no,zip) values('C004','郭大侠','上海市华景区','13672245113','330013');
insert into customer(cust_id,cust_name,addr,tel_no,zip) values('C005','李大嘴','江西省南昌市','13672245114','330014');
insert into customer(cust_id,cust_name,addr,tel_no,zip) values('C006','张三','上海市华景区','13672245115','330015');
insert into customer(cust_id,cust_name,addr,tel_no,zip) values('C007','李四','江西省南昌市','13672245116','330016');
insert into customer(cust_id,cust_name,addr,tel_no,zip) values('C008','王五','上海市天河区','13672245117','330017');
insert into customer(cust_id,cust_name,addr,tel_no,zip) values('C009','老刘','江西省南昌市','13672245118','330018');
insert into customer(cust_id,cust_name,addr,tel_no,zip) values('C0010','小十','江西省南昌市','13672245119','330019');

（3）product表数据

insertinto product(prod_id,prod_name)values('p101','好迪洗发水');
insert into  product(prod_id,prod_name)values('p102','128M的Mp3');
insert into product(prod_id,prod_name)values('p103','安利礼品包');
insert into product(prod_id,prod_name)values('p104','海飞丝洗发水');
insert into product(prod_id,prod_name)values('p105','新盖中盖高钙片');
insert into  product(prod_id,prod_name)values('p106','黄金搭档');
insert into product(prod_id,prod_name)values('p107','脑残金');
insert into product(prod_id,prod_name)values('p108','成龙专用霸王洗发水');
insert into product(prod_id,prod_name)values('p109','盗版MP3');
insert into  product(prod_id,prod_name)values('p1010','盗版MP4');
insert into product(prod_id,prod_name)values('p1011','盗版MP5');
insert into product(prod_id,prod_name)values('p1012','正版MP3');
insert into product(prod_id,prod_name)values('p1013','正版MP4');
insert into  product(prod_id,prod_name)values('p1014','正版MP5');
insert into product(prod_id,prod_name)values('p1015','潘婷洗发水');
insert into product(prod_id,prod_name)values('p1016','小饰品');
insert into product(prod_id,prod_name)values('p1017','随身听');
insert into  product(prod_id,prod_name)values('p1018','隐形耳机');
insert into product(prod_id,prod_name)values('p1019','双星皮包');
insert into product(prod_id,prod_name)values('p1020','阿迪袜子');
insert into product(prod_id,prod_name)values('p1021','耐克袜子');
insert into  product(prod_id,prod_name)values('p1022','广州衬衣');
insert into product(prod_id,prod_name)values('p1023','假老鼠药');
insert into product(prod_id,prod_name)values('p1024','爱立信啫喱水');
insert into product(prod_id,prod_name)values('p1025','盗版手机');
insert into  product(prod_id,prod_name)values('p1026','摩托手机');
insert into product(prod_id,prod_name)values('p1027','盗版组装机');
insert into product(prod_id,prod_name)values('p1028','方正组装机');
insert into product(prod_id,prod_name)values('p1029','盗版笔记本');
insert into  product(prod_id,prod_name)values('p1030','正版笔记本');

（4）sales表数据

insertinto sales(order_no,cust_id,sale_id,tot_amt,order_date,ship_date,invoice_no)values(1101,'C001','E0001','1205','2008-9-10','2008-10-10','f101');
insert into sales(order_no,cust_id,sale_id,tot_amt,order_date,ship_date,invoice_no)values(1102,'C002','E0002','1426','2005-9-10','2005-10-10','f102');
insert into sales(order_no,cust_id,sale_id,tot_amt,order_date,ship_date,invoice_no)values(1103,'C003','E0003','1635','2002-9-10','2002-10-10','f103');
insert into  sales(order_no,cust_id,sale_id,tot_amt,order_date,ship_date,invoice_no)values(1104,'C004','E0004','1875','1987-9-10','1987-10-17','f104');
insert into sales(order_no,cust_id,sale_id,tot_amt,order_date,ship_date,invoice_no)values(1105,'C005','E0005','2345','2009-9-10','2009-10-10','f105');
insert into sales(order_no,cust_id,sale_id,tot_amt,order_date,ship_date,invoice_no)values(1106,'C005','E0006','3245','1992-9-10','1992-10-10','f106');
insert into sales(order_no,cust_id,sale_id,tot_amt,order_date,ship_date,invoice_no)values(1107,'C005','E0007','6572','1990-9-10','1990-10-10','f107');
insert into sales(order_no,cust_id,sale_id,tot_amt,order_date,ship_date,invoice_no)values(1108,'C005','E0008','12547','1989-9-10','1989-10-10','f108');
insert into  sales(order_no,cust_id,sale_id,tot_amt,order_date,ship_date,invoice_no)values(1109,'C005','E0009','19824','1987-9-10','1987-10-10','f109');
insert into sales(order_no,cust_id,sale_id,tot_amt,order_date,ship_date,invoice_no)values(11010,'C006','E0010','200311','2009-9-10','2009-10-10','f1010');
insert into sales(order_no,cust_id,sale_id,tot_amt,order_date,ship_date,invoice_no)values(11011,'C006','E0013','250311','1996-10-15','1996-10-20','f1011');
insert into sales(order_no,cust_id,sale_id,tot_amt,order_date,ship_date,invoice_no)values(11012,'C007','E0013','290311','1996-10-15','1996-10-20','f1012');
insert into sales(order_no,cust_id,sale_id,tot_amt,order_date,ship_date,invoice_no)values(11013,'C008','E0013','300311','1996-10-15','1996-10-20','f1013');
insert into  sales(order_no,cust_id,sale_id,tot_amt,order_date,ship_date,invoice_no)values(11014,'C002','E0015','350311','1986-10-15','1996-10-20','f1014');
insert into sales(order_no,cust_id,sale_id,tot_amt,order_date,ship_date,invoice_no)values(11015,'C003','E0016','280311','1987-10-15','1989-10-20','f1015');
insert into sales(order_no,cust_id,sale_id,tot_amt,order_date,ship_date,invoice_no)values(10001,'C003','E0016','480311','1987-10-15','1989-10-20','f1016');

（5）sales_item表数据

insertinto  sale_item(order_no,prod_id,qty,unit_price,order_date)values(1101,'p101',1420,18,'2009-6-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1102,'p102',1230,37,'2008-6-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1103,'p103',567,55,'2007-3-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1104,'p104',670,12,'2006-4-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1105,'p105',560,128,'2005-5-10');
insert into  sale_item(order_no,prod_id,qty,unit_price,order_date)values(1106,'p106',390,200,'2004-6-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1107,'p107',890,260,'2003-7-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1108,'p108',780,275,'2002-8-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1109,'p109',330,320,'2001-9-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(11010,'p1010',320,340,'1999-10-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(11011,'p1020',650,340,'1998-10-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(11012,'p1025',230,340,'1987-10-17');
insert into  sale_item(order_no,prod_id,qty,unit_price,order_date)values(11013,'p1029',234,340,'1983-10-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1101,'p1021',230,400,'1982-3-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1101,'p1022',1300,450,'1981-9-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1103,'p1013',970,500,'1980-6-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1104,'p1014',780,550,'1979-7-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1105,'p1015',450,600,'7978-3-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1106,'p101',980,650,'1977-4-10');
insert into  sale_item(order_no,prod_id,qty,unit_price,order_date)values(1107,'p102',390,700,'1976-5-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1108,'p104',340,750,'1975-7-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1109,'p101',980,800,'1974-8-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1102,'p1022',450,850,'1973-10-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1101,'p106',300,900,'1972-4-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1102,'p1012',800,950,'1989-7-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1103,'p105',780,1000,'1977-4-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1104,'p1024',1200,1200,'2001-6-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1105,'p107',460,1250,'2005-3-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1106,'p1016',560,1300,'2006-2-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1107,'p101',356,1400,'2004-7-10');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1108,'p1018',323,1500,'1996-10-15');
insert into  sale_item(order_no,prod_id,qty,unit_price,order_date)values(1109,'p103',412,2000,'1996-10-15');
insert into sale_item(order_no,prod_id,qty,unit_price,order_date)values(1103,'p1023',330,2600,'1996-10-15');

2.使用SQL语句完成如下操作

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

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

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

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

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

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

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

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

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

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

（11）计算每一产品每月的销售金额总和，并将结果按销售（月份，产品编号）排序。

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

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

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

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

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

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

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

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

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

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

附：

（1）大家可以在此基础上将前面相关作业题目练习一下，并仔细分析所使用SQL语句的执行过程以及效率

（2）上交此次实验结果要求参考实验一