数据库系统原理与设计实验教程实验1Word格式.docx
《数据库系统原理与设计实验教程实验1Word格式.docx》由会员分享,可在线阅读,更多相关《数据库系统原理与设计实验教程实验1Word格式.docx(18页珍藏版)》请在冰豆网上搜索。
fromemployee
whereaddresslike'
%上海%'
oraddresslike'
%南昌%'
andsex='
(5)在订单明细表OrderDetail中挑出销售金额大于等于10000元的订单。
selectorderNo
fromOrderDetail
groupbyorderNo
havingsum(quantity*price)>
=10000
(6)选取订单金额最高的前10%的订单数据。
SELECTTOP10PERCENTorderNo
FROMOrderdetail
GROUPBYorderNo
ORDERBYsum(quantity*price)DESC
(7)查询出职务为“职员”或职务为“科长”的女员工的信息。
where(headship='
职员'
orheadship='
科长'
)andsex='
(8)查找定单金额高于8000的所有客户编号。
1)查询总金额高于8000元的客户编号
selectCustomerNo
fromOrderMastera,Orderdetailb
wherea.orderNo=b.orderNo
groupbyCustomerNo
8000
2)查找定单金额高于8000的所有客户编号
selectCustomerNo
fromOrderMaster
whereorderNoin(selectorderNo
fromOrderDetail
groupbyorderNo
havingsum(quantity*price)>
8000)
(9)选取编号界于“C”和“C”的客户编号、客户名称、客户地址。
selectCustomerNo,CustomerName,address
whereCustomerNobetween'
C'
and'
(11)找出同一天进入公司服务的员工。
Selecta.employeeNo,a.employeeName,b.employeeNo,b.employeeName
fromEmployeea,Employeeasb
wherea.employeeNo!
=b.employeeNoanda.employeeName>
b.employeeName
and(a.hireDate=b.hireDate)
(12)在订单主表中查询订单金额大于“E业务员在2008-1-9这天所接的任一张订单的金额”的所有订单信息。
1)首先计算订单主表的订单金额
updateOrderMastersetorderSum=totalSum
fromOrderMastera,(selectorderNo,sum(quantity*price)totalSum
groupbyorderNo)b
wherea.orderNO=b.orderNo
2)
SELECT*
FROMOrderMaster
WHEREorderSum>
any
(SELECTorderSum
FROMOrderMaster
WHEREsalerNo='
E'
ANDorderDate='
'
)
(13)查询既订购了“52倍速光驱”商品,又订购了“17寸显示器”商品的客户编号、订单编号和订单金额。
SelectcustomerNo,orderNo,orderSum
wherecustomerNoin
(selectcustomerNo
fromOrderMastera,OrderDetailb,Productc
wherea.orderNo=b.orderNoandb.productNo=c.productNoand
productName='
52倍速光驱'
)
andcustomerNoin(selectcustomerNo
17寸显示器'
(14)查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务。
Selectb.employeeName,b.sex,b.department,b.headShip
fromEmployeea,Employeeb
wherea.department=b.departmentanda.employeeName='
陈诗杰'
(15)查询每种商品的商品编号、商品名称、订货数量和订货单价。
SELECTa.productNo,productName,quantity,price
FROMProducta,OrderDetailb
WHEREa.productNo=b.productNo
ORDERBYproductName,price
(16)查询单价高于400元的商品编号、商品名称、订货数量和订货单价。
WHEREa.productNo=b.productNoANDprice>
400
ORDERBYproductName
(17)分别使用左外连接、右外连接、完整外部连接查询单价高于400元的商品编号、商品名称、订货数量和订货单价,并分析比较检索的结果。
左外连接命令:
FROMProductaLEFTOUTERJOINOrderDetailb
ONa.productNo=b.productNo
WHEREprice>
Selecta.productNo,a.productName,b.quantity,b.price
FromOrderDetailAsbleftJOINProductAsa
ON(a.productNo=b.productNo)
andprice>
Orderbya.productNo
右外连接命令:
FROMProductaRIGHTOUTERJOINOrderDetailb
FromOrderDetailAsbRIGHTJOINProductAsa
全连接命令:
FROMProductaFULLOUTERJOINOrderDetailb
FromOrderDetailAsbfullJOINProductAsa
(18)分别使用左外连接、右外连接、完整外部连接查询客户编号、客户名称、订货金额和订单编号,并分析比较检索的结果。
SELECTa.customerNo,customerName,orderSum,orderNo
FROMCustomeraleftOUTERJOINOrderMasterb
ONa.customerNo=b.customerNo
FROMCustomerarightOUTERJOINOrderMasterb
FROMCustomerafullOUTERJOINOrderMasterb
从上述结果可知:
若表a和表b做外连接,且b表是外码表,则a和b表左外连接可能会出现空值,但是右连接一定不会出现空值,全外连接与左外连接一样的结果。
(18)查找每个员工的销售记录,要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期,其中性别使用“男”和“女”表示,日期使用“yyyy-mm-dd”格式显示。
SELECTsalerNo,employeeName,
CASEsexWHEN'
THEN'
WHEN'
END性别,
productName,quantity,price,orderSum,
convert(char(10),orderDate,120)orderDate
FROMEmployeea,OrderMasterb,OrderDetailc,Productd
WHEREa.employeeNo=b.salerNoANDb.orderNo=c.orderNo
ANDc.productNo=d.productNo
ORDERBYsalerNo
(19)查找在2008年3月中有销售记录的客户编号、名称和订单总额。
SELECTa.customerNo,customerName,orderSum
FROMOrderMastera,Customerb
WHEREyear(orderDate)=2008ANDmonth(orderDate)=3
ANDa.customerNo=b.customerNo
(20)使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额,其中订货日期不要显示时间,日期格式为“yyyy-mm-dd”,按客户编号排序,同一客户再按订单金额降序排序输出。
SELECTa.customerNo,customerName,convert(char(10),orderDate,120)orderDate,orderSum
FROMCustomeraLEFTOUTERJOINOrderMasterbONa.customerNo=b.customerNo
ORDERBYa.customerNo,orderSumdesc
(21)查找16MDRAM的销售情况,要求显示相应的销售员的姓名、性别,销售日期、销售数量和金额,其中性别用“男”、“女”表示。
SELECTemployeeName,CASEsex
THEN'
WHEN'
ENDsex,
orderDate,quantity,quantity*pricetotal
WHEREa.employeeNo=b.salerNoANDb.orderNo=c.orderNoAND
c.productNo=d.productNoANDproductName='
16MDRAM'
(22)查找每个人的销售记录,要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期。
selecta.employeeNo,a.employeeName,a.sex,d.ProductName,
quantity,price,price*quantitytotal,orderDate
fromEmployeea,OrderMasterb,OrderDetailc,Productd
wherea.employeeNo=b.salerNoANDb.orderNo=c.orderNoANDc.ProductNo=d.ProductNo
(23)查询客户姓名为“客户丙”所购货物的“客户名称”、“定单金额”、“定货日期”和“电话号码”。
SELECTCustomerName,orderSum,orderDate,telephone
FROMCustomera,OrderMasterb
WHEREa.CustomerNo=b.CustomerNoANDa.CustomerName='
客户丙'
(24)找出公司男业务员所接且订单金额超过2000元的订单号及订单金额。
SELECTorderNo,orderSum
FROM(SELECTorderNo,orderSum
FROMOrderMastera,Employeeb
WHEREa.salerNo=b.employeeNoANDb.sex='
)c
WHEREordersum>
2000
或:
FROMOrderMastera,Employeeb
WHEREa.salerNo=b.employeeNoANDb.sex='
andordersum>
(25)查询来自上海市的客户的姓名、电话、订单号及订单金额。
SELECTCustomerName,telephone,orderNo,orderSum
WHEREa.CustomerNo=b.CustomerNoANDa.addressLIKE'
(26)实验问题
①连接操作类型有哪些?
分析外连接在现实应用中的意义。
答:
连接运算包括内连接(等值连接,非等值连接,自然连接)外连接(左外连接,右外连接,全外连接),自表连接。
外连接的意义:
对于存在多对多的关系的两个关系,将关联不上的部分信息也能显示出来。
在现实中对于数据库的数据初始化很有用。
②查询表可以用在什么地方?
使用查询表要注意哪些地方?
查询表用在from子句之后,必须为查询表设置一个元组变量名,用该名表示查询表。
查询表是执行过程中产生的表,执行完后其表自动丢失。
子查询也可以认为是一个查询表,但是不需要为他指定元组变量名。
③分析条件BETWEEN...AND、AND、OR等关键字的使用方法。
BETWEEN...AND是范围查询,可用于查询属性值在某一个范围内的元组,NOTBETWEEN….AND可用于查询属性值不在某个范围内的元组,BETWEEN后是属性的下限值,AND后是属性的上限值。
AND、OR是逻辑查询,AND实现逻辑与运算,要注意的是在逻辑运算中,不可以对同一个属性进行逻辑“与”的等值运算OR实现逻辑或运算。
用BETWEEN…..AND能实现的查询,也可以用AND来达到。
同样,用NOTBETWEEN…AND能实现的查询,也可以用OR来实现。
④请总结SQL语句中的单表查询语句的使用方法。
⒈查询所有列
SELECT*FROM[表名]
2.查询指定列
SELECT[字段列表]FROM[表名]
3.消除重复元组:
SELECTDistinct[字段列表]FROM[表名]
4.查询经过计算的列
SELECT【字段及字段表达式】FROM【表名】
5.指定查询结果的查询
SELECTTOPn[PERCENT]【字段列表】FROM【表名】
6.给属性取别名
①:
SELECT【字段名】1as'
A'
FROM【表名】
②:
SELECT"
A"
=字段名1FROM【表名】
③:
SELECT字段名1"
7.选择查询
SELECT【字段名列表】FROM【表名】WHERE【条件表达式】
条件表达式运算符:
比较运算:
=,<
<
=,>
>
=,!
<
!
>
逻辑查询:
or,AND,not
范围查询:
between…AND,notbetween…AND
集合查询:
IN,notIN
字符匹配查询:
ike,notLIKE
空值查询:
isnull,isnotnull
8.排序查询
SELECT【字段名1】,【字段名2】FROM【表名】
WHERE【条件表达式】
ORDERBY【字段表达式1】,【字段表达式2】[ASC][DESC]
9.分组查询
①SELECT字段名列表FROM表名[WHERE条件表达式]
GROUPBY字段名
②SELECT字段名列表FROM表名[WHERE条件表达式]
GROUPBY字段名HAVING筛选表达式
10.聚合查询
SELECTcount([ALL|DISTINCT]{*|字段名列表})FROM表名
[WHERE条件表达式]
SELECTsum|avg|max|min([ALL|DISTINCT]<
字段名列表>
)
FROM表名
⑤分析哪几种情况需要使用自表连接。
当同表中的某个元组变量与表中的另外的元组变量相关时,可以使用自表连接,从而得到一些特殊的数据或者是说带特定条件的数据。
自表连接一般是用来判断并筛选单一表中的一些数据。