数据库第二次实验要求
来源:互联网 发布:海福盛粥能吃吗 知乎 编辑:程序博客网 时间:2024/06/10 07:55
第二次实验内容
实验名称:数据库的查询和更新操作
实验要求:要求学生掌握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)上交此次实验结果要求参考实验一
- 数据库第二次实验要求
- Java第二次实验要求
- 第二次实验
- 第二次实验
- 第二次实验
- 第二次实验
- 第二次实验
- 第二次实验
- 第二次实验
- 第二次实验
- 第二次实验 实验报告
- C++第二次实验-实验报告
- 汇编第二次实验
- 第二次C++实验
- 第二次上机实验
- 第二次实验任务
- 第二次上机实验
- 第二次实验报告
- 简单工厂模式
- 数据结构(严蔚敏)之三——顺序栈之c语言实现
- hdu 1072 Nightmare
- 使用HTML5 跨域共享特性解决AJAX跨域数据同步问题
- 正文
- 数据库第二次实验要求
- xcodebuild和xcrun自动化编译ipa包
- UML类图中箭头和线条的含义和用法
- android droidparts框架使用注意 db篇
- 面试经验分享之阿里客户端研发实习生笔试
- LeetCode 162 Find Peak Element
- 蓝桥杯-带分数
- css框架960grid
- .net产生非对称密钥对的方法RAS公钥及私钥产生