数据库实验报告一SQL查询Word文件下载.docx
《数据库实验报告一SQL查询Word文件下载.docx》由会员分享,可在线阅读,更多相关《数据库实验报告一SQL查询Word文件下载.docx(27页珍藏版)》请在冰豆网上搜索。
F'
女'
end
whereaddresslike'
%上海%'
oraddresslike'
%南昌%'
(5)查询出职务为“职员”或职务为“科长”的女员工的信息。
whereheadshiplike'
职员'
orheadshiplike'
科长'
(6)选取编号不在C20050001~C20050004之间的客户编号、客户名称、客户地址。
selectcustomerno,customername,address
wherecustomernonotbetween'
C20050001'
and'
C20050004'
(7)在表OrderMaster中挑出销售金额大于等于5000元的订单。
先统计订单主表中的订单金额,使用命令:
updateOrderMastersetorderSum=sum2
fromOrderMastera,(selectorderNo,sum(quantity*price)sum2
fromOrderDetail
groupbyorderNo)b
wherea.orderNo=b.orderNo
selecta.orderno,b.sum2
wherea.orderNo=b.orderNoandb.sum2>
=5000
(8)选取订单金额最高的前10%的订单数据。
selecttop10percenta.orderno,b.sum2
(9)计算出一共销售了几种商品。
selectcount(*)商品总数
from(selectorderno
fromorderdetail
groupbyorderno)a
(10)计算OrderDetail表中每种商品的销售数量、平均销售单价和总销售金额,并且依据销售金额由大到小排序输出。
selectproductno,sum(quantity)销售数量,avg(price)平均价格,sum(quantity*price)销售金额
fromorderdetail
groupbyproductno
orderbysum(quantity*price)desc
(11)按客户编号统计每个客户2008年2月的订单总金额。
selectcustomerno,sum(ordersum)
fromordermaster
whereyear(orderdate)=2008andmonth(orderdate)=2
groupbycustomerno
(12)统计至少销售了10件以上的商品编号和销售数量。
selectproductno,sum(quantity)sumquantity
havingsum(quantity)>
10
(13)统计在业务科工作且在1973年或1967年出生的员工人数和平均工资。
selectyear(birthday),count(*),avg(salary)
whereyear(birthday)=1973oryear(birthday)=1967
groupbyyear(birthday)
(14)实验问题:
①给出SQL语句实现分组聚集操作的执行过程。
1、先执行form语句中的表目连接(笛卡尔积)
2、Where语句中的选择保留满足条件的分组
3、Groupby将满足条件的语句进行聚合
4、Having语句在聚合后的分组中进行条件筛选
5、Orderby对满足条件的分组进行排列
6、Select语句最后对表目中的属性选择行输出
②WHERE和HAVING子句都是用于指定查询条件的,请给出你对这两个子句的理解,用实例说明。
Where是对分组进行初步的筛选找出符合条件的分组(不能包含聚合函数)
Having对最终满足条件的分组进行选择一般会用到聚合函数(sumcountavg等)
③在分组聚集操作中,为什么在查询列中,除了集聚函数运算,其它表达式必须包含在GROUPBY子句中。
其实我也纳闷为什么有时候在groupby中只要一个属性就能唯一标识一个聚合后的分组,而在select中可能要查询多属性时groupby中要把select中出聚合函数外所有函数运算都包含进去,如果不这样就会报错这可能是硬性规定或当初设计就是这样
④分析条件BETWEEN...AND、AND、OR等关键字的使用方法。
这些语句都在where中使用
BETWEENAND是区间选择一个分组的某个属性值在这个区间就满足
AND一个分组必须同时满足这些选择条件
OR一个分组只要满足其中的一个条件就可以
⑤请总结SQL语句中的单表查询语句的使用方法。
先执行form语句中的表目连接(笛卡尔积)
Where语句中的选择保留满足条件的分组(不能包含聚合函数)
Groupby将满足条件的语句进行聚合(必须包含select语句中出聚合函数外的所有函数运算)
Having语句用聚合函数在分组中进行条件筛选(如:
sumavgcount等)
Orderby对满足条件的分组进行排列(desc由大到小)
Select语句最后对表目中的属性选择行输出
(1)找出同一天进入公司服务的员工。
selecta.employeeno,a.employeename,a.sex,a.birthday,address,a.telephone,a.hiredate,a.department,a.headship,a.salary
fromemployeea,(
selecthiredate
groupbyhiredate
havingcount(*)>
=2)b
wherea.hiredate=b.hiredate
(2)查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务。
selectb.employeename,b.sex,b.department,b.headship
fromemployeea,employeeb
wherea.employeenamelike'
陈诗杰'
anda.department=b.department
(3)在Employee表中查询薪水超过员工平均薪水的员工信息。
select*
fromemployeea,(selectavg(salary)avgsalaryfromemployee)b
wherea.salary>
b.avgsalary
(4)查找有销售记录的客户编号、名称和订单总额。
selecta.customerno,a.customername,sum(b.ordersum)
fromcustomera,ordermasterb
wherea.customerno=b.customerno
groupbya.customerno,a.customername
havingsum(b.ordersum)>
(5)查询没有订购商品的客户编号和客户名称。
selecta.customerno,a.customername
fromcustomera
wherea.customernonotin
(selectcustomernofromordermastergroupbycustomerno)
(6)使用子查询查找16MDRAM的销售情况,要求显示相应的销售员的姓名、性别,销售日期、销售数量和金额,其中性别用“男”、“女”表示。
selecta.employeename,sex=casea.sexwhen'
when'
then'
end,
b.orderdate,c.quantity,c.price*c.quantity金额
fromemployeea,ordermasterb,orderdetailc,productd
wherea.employeeno=b.salernoandb.orderno=c.ordernoandc.productno=d.productnoandd.productnamelike'
32MDRAM'
(7)查询OrderMaster表中订单金额最高的订单号及订单金额。
selecta.orderno,a.ordersum
fromordermastera,(selectmax(ordersum)maxordersumfromordermaster)b
wherea.ordersum=b.maxordersum
(8)在订单主表中查询订单金额大于“E2005002业务员在2008-1-9这天所接的任一张订单的金额”的所有订单信息。
selecta.orderno,a.customerno,a.salerno,a.orderdate,a.ordersum,a.invoiceno
fromordermastera,
(selectmin(b.ordersum)minorder
fromordermasterb
whereb.salernolike'
E2005002'
andyear(b.orderdate)=2008andmonth(b.orderdate)=1
andday(b.orderdate)=9)c
wherea.ordersum>
c.minorder
(9)查询单价高于400元的商品编号、商品名称、订货数量和订货单价。
selecta.productno,a.productname,sum(b.quantity)订单数量,b.price
fromproducta,orderdetailb
wherea.productprice>
400andb.productno=a.productno
groupbya.productno,a.productname,b.price
(10)分别使用左外连接、右外连接、完整外部连接查询单价高于400元的商品编号、商品名称、订货数量和订货单价,并分析比较检索的结果。
左外连接
fromproductaleftouterjoinorderdetailbona.productprice>
右外连接
fromproductarightouterjoinorderdetailbona.productprice>
完整外部连接
fromproductafullouterjoinorderdetailbona.productprice>
(11)使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额,其中订货日期不要显示时间,日期格式为“yyyy-mm-dd”,按客户编号排序,同一客户再按订单金额降序排序输出。
Selecta.customerno,customername,orderdate=isnull(convert(char(10),orderdate,120),'
NULL'
),ordersum
fromcustomeraleftouterjoinordermasterbona.customerno=b.customerno
orderbya.customerno,ordersumdesc
函数isnull:
第一个参数是convert(char(10),birthday,120),第二个参数是'
NULL'
作用:
若第一个参数为null,则返回结果为第二个参数,不为null则返回第一个参数.
再看函数convert,他又三个参数.作用是将日期型转换成字符串型.
第一个参数:
char(10)表示转换结果的类型及长度.
第二个参数:
birthday表示要需要被转换的日期型变量或字段
第三个参数:
120表示转换结果的表示格式.
其中第三个参数120也可以是其他数值:
style数字在转换时间时的含义如下:
------------------------------------------------------------------------------------------------------------
Style(2位表示年份)|Style(4位表示年份)|输入输出格式
0|100|monddyyyyhh:
miAM(或PM)
1|101美国|mm/dd/yy
2|102ANSI|yy-mm-dd
3|103英法|dd/mm/yy
4|104德国|dd.mm.yy
5|105意大利|dd-mm-yy
6|106|ddmonyy
7|107|mondd,yy
8|108|hh:
mm:
ss
9|109|monddyyyyhh:
mi:
ss:
mmmmAM(或PM)
10|110美国|mm-dd-yy
11|111日本|yy/mm/dd
12|112ISO|yymmdd
13|113欧洲默认值|ddmonyyyyhh:
mmm(24小时制)
14|114|hh:
20|120ODBC规范|yyyy-mm-ddhh:
ss(24小时制)
21|121|yyyy-mm-ddhh:
(12)查找每个员工的销售记录,要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期,其中性别使用“男”和“女”表示,日期使用“yyyy-mm-dd”格式显示。
selecta.employeeno,a.employeename,sex=casea.sexwhen'
end,
b.productname,d.quantity,d.price,d.quantity*d.price金额,orderdate=isnull(convert(char(10),c.orderdate,120),'
'
)
fromemployeea,productb,ordermasterc,orderdetaild
wherea.employeeno=c.salernoandb.productno=d.productnoandc.orderno=d.orderno
(13)查找16MDRAM的销售情况,要求显示相应的销售员的姓名、性别,销售日期、销售数量和金额,其中性别用“男”、“女”表示。
(题中的“16MDRAM”在Product表没有该商品只有“32MDRAM”)
c.orderdate,d.quantity,d.quantity*d.price
wherea.employeeno=c.salernoandb.productno=d.productnoandc.orderno=d.ordernoandb.productnameli