Select语句查询练习pubs.docx

1、Select语句查询练习pubs该实验以SQL Server 2000系统自带的 pubs 数据库为例，以一个图书出版公司为模型。 SQL SERVER200实体关系图描述 E-R图描述（1）该系统中数据库基本表如下：Authors：属性名数据类型含义说明可为空检查键/索引au_idId作者编号否是1主键au_lnamevarchar(40)作者姓否au_fnamevarchar(20)作者名否phonechar(12)电话否addressvarchar(40)地址是cityvarchar(20)所在城市是statechar(2)所在州是zipchar(5)邮编是是2contractBit是否

2、签约否1 au_id CHECK 约束定义为 (au_id LIKE 0-90-90-9-0-90-9-0-90-90-90-9)。2 zip CHECK 约束定义为 (zip LIKE 0-90-90-90-90-9)。=discounts属性名数据类型含义说明可为空检查键/索引discounttypevarchar(40)折扣类型否stor_idchar(4)商店编号是外键 stores(stor_id)lowqtySmallint数量下限是highqtySmallint数量上限是discountFloat折扣否=Employee属性名数据类型含义说明可为空默认值检查键/索引emp_idE

3、mpid职工编号否是1主键fnamevarchar(20)职工名否minitchar(1)是lnamevarchar(30)职工姓否job_idSmallint工作编号否1外键 jobs(job_id)job_lvlTinyint否10pub_idchar(4)出版社编号否9952外键publishers(pub_id)Hire_dateDatetime工作日期否GETDATE( )CHECK 约束定义为：(emp_id LIKE A-ZA-ZA-Z1-90-90-90-90-9FM) OR (emp_id LIKE A-Z-A-Z1-90-90-90-90-9FM)。=Jobs属性名数据类型

4、含义说明可为空检查键/索引job_idSmallint工作编号否主键job_descvarchar (50)工作描述否min_lvlTinyint否是1max_lvlTinyint否是2(1) min_lvl CHECK 约束定义为 (min_lvl = 10)。(2) max_lvl CHECK 约束定义为 (max_lvl 80 ORDER BY 成绩 DESC5 方法一：SELECT 姓名 FROM 学生 WHERE 专业信息系方法二：SELECT 姓名 FROM 学生 WHERE NOT 专业=信息系方法三：SELECT 姓名 FROM 学生 WHERE 专业!=信息系6 方法一：SE

5、LECT * FROM 选课 WHERE 成绩=70 AND 成绩all (select price from titles where type=business)在pubs数据库的sales表中查找所有销售量大于所有图书平均销售量avg（qty）的书的代号(title_id)及销售量（qty）。 select title_id ,qtyfrom saleswhere qtyall(select avg(qty)from sales)用带有IN的嵌套查询，查询来自城市（city）为“London”的客户所订的订单信息（customers和orders表）。select *from order

6、swhere customerID in (select customerIDfrom customerswhere city=london)用带有IN的嵌套查询，查询Northwind数据库中的产品表（Products）中来自国家为“Germany” （在供应商表（Suppliers）表中）的供货商供应的产品信息（包括Productid，Productname， categoryid, unitprice）。SELECT Productid,Productname, categoryid,unitpricefrom Productswhere Productid in(select supp

7、lierIDfrom Supplierswhere country=Germany)2、提高操作实验练习使用EXISTS操作符进行嵌套查询操作。请完成以下习题：使用EXISTS子查询在Pubs数据库titles 表及publishers表中查询New Moon Books出版社所出版的图书名称（title)select title from titles where exists (select * from publishers where pub_name=New Moon Books) T-SQL高级查询课堂练习及答案-练习1-找出pubs数据库titles表中计算机类图书中价格最高的图

8、书的价格。USE pubsGOSELECT max(price) FROM titleswhere type=popular_compGO-练习2-查询titles表中有几类图书。USE pubsGOSELECT count(distinct type) FROM titlesGO-练习3-按照州进行分类，查找每个州有几名作者。USE pubsGOSELECT state, count(*) FROM authorsgroup by stateorder by 1GO-练习4-要求按照出版商id进行分类，查找每个出版商的书到目前为止的销售额总和（ytd_sales）。USE pubsGOSEL

9、ECT pub_id, sum(ytd_sales) FROM titlesgroup by pub_idorder by 1GO-练习5-在pubs数据库的titles表中，找出平均价格大于18美元的书的种类。USE pubsGOSELECT pub_id,avg(price) 平均价格 FROM titlesGROUP BY pub_idHAVING avg(price) 18GO-练习6-在pubs数据库的titles表中，找出最高价大于20美元的书的种类。USE pubsGOSELECT type,max(price) 平均价格 FROM titlesGROUP BY typeHAVI

10、NG max(price) 20GO-练习7-找出title_id和pub_name的对应关系。Use pubsgoSelect titles.title_id, publishers.pub_nameFrom titles JOIN publishersON titles.pub_id=publishers.pub_idGo-练习8-找出title_id, title和pub_name的对应关系。Use pubsgoSelect titles.title_id, titles.title, publishers.pub_nameFrom titles JOIN publishersON titles.pub_id=publishers.pu