Select语句查询练习pubs.docx
《Select语句查询练习pubs.docx》由会员分享，可在线阅读，更多相关《Select语句查询练习pubs.docx（21页珍藏版）》请在冰点文库上搜索。
Select语句查询练习pubs
该实验以SQLServer2000系统自带的pubs数据库为例，以一个图书出版公司为模型。
●SQLSERVER200实体关系图描述
●E-R图描述
（1）该系统中数据库基本表如下：
Authors：
属性名
数据类型
含义说明
可为空
检查
键/索引
au_id
Id
作者编号
否
是1
主键
au_lname
varchar（40）
作者姓
否
au_fname
varchar（20）
作者名
否
phone
char（12）
否
address
varchar（40）
地址
是
city
varchar（20）
所在城市
是
state
char
（2）
所在州
是
zip
char（5）
邮编
是
是2
contract
Bit
是否签约
否
1au_idCHECK约束定义为（au_idLIKE'[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'）。
2zipCHECK约束定义为（zipLIKE'[0-9][0-9][0-9][0-9][0-9]'）。
============================
discounts
属性名
数据类型
含义说明
可为空
检查
键/索引
discounttype
varchar（40）
折扣类型
否
stor_id
char（4）
商店编号
是
外键stores（stor_id）
lowqty
Smallint
数量下限
是
highqty
Smallint
数量上限
是
discount
Float
折扣
否
============================
Employee
属性名
数据类型
含义说明
可为空
默认值
检查
键/索引
emp_id
Empid
职工编号
否
是1
主键
fname
varchar（20）
职工名
否
minit
char
（1）
是
lname
varchar（30）
职工姓
否
job_id
Smallint
工作编号
否
1
外键jobs（job_id）
job_lvl
Tinyint
否
10
pub_id
char（4）
编号
否
'9952'
外键publishers（pub_id）
Hire_date
Datetime
工作日期
否
GETDATE（）
CHECK约束定义为：
（emp_idLIKE'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]'）OR
（emp_idLIKE'[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'）。
============================
Jobs
属性名
数据类型
含义说明
可为空
检查
键/索引
job_id
Smallint
工作编号
否
主键
job_desc
varchar（50）
工作描述
否
min_lvl
Tinyint
否
是1
max_lvl
Tinyint
否
是2
（1）min_lvlCHECK约束定义为（min_lvl>=10）。
（2）max_lvlCHECK约束定义为（max_lvl<=250）。
============================
pub_info
属性名
数据类型
含义说明
可为空
检查
键/索引
pub_id
char（4）
编号
否
主键，外键publishers（pub_id）
logo
Image
标志图
是
pr_info
Text
出版信息
是
============================
Publishers
属性名
数据类型
含义说明
可为空
检查
键/索引
pub_id
char（4）
编号
否
是1
主键
pub_name
varchar（40）
名称
是
city
varchar（20）
所在城市
是
state
char
（2）
所在州
是
country
varchar（30）
所在国家
是
1pub_idCHECK约束定义为
（pub_id='1756'OR（pub_id='1622'OR（pub_id='0877'OR（pub_id='0736'OR（pub_id='1389'））））OR（pub_idLIKE'99[0-9][0-0]'）。
============================
roysched
属性名
数据类型
含义说明
可为空
检查
键/索引
title_id
Tid
书编号
否
外键titles（title_id）
lorange
Int
低
是
hirange
Int
高
是
royalty
Int
是
============================
Sales
属性名
数据类型
含义说明
可为空
键/索引
stor_id
char（4）
商店编号
否
组合主键，聚集索引,外键stores（stor_id）
ord_num
varchar（20）
订单编码
否
组合主键，聚集索引
ord_date
Datetime
订购日期
否
qty
Smallint
数量
否
payterms
varchar（12）
付款方式
否
title_id
Tid
书编号
否
组合主键，聚集索引,外键titles（title_id）
============================
titles
属性名
数据类型
含义说明
可为空
检查
键/索引
title_id
Tid
书编号
否
主键
title
varchar（80）
书名
否
type
char（12）
类型
否
pub_id
char（4）
编号
是
外键publishers（pub_id）
price
Money
价格
是
advance
Money
预付款
是
royalty
Int
版税
是
Ytd_sales
Int
年销售量
是
notes
varchar（200）
简介
是
pubdate
Datetime
出版日期
是
============================
Stores
属性名
数据类型
含义说明
可为空
检查
键/索引
stor_id
char（4）
商店编号
否
主键
stor_name
varchar（40）
商店名称
是
stor_address
varchar（40）
商店地址
是
city
varchar（20）
所在城市
是
state
char
（2）
所在州
是
zip
char（5）
邮编
是
============================
titleauthor
属性名
数据类型
含义说明
可为空
检查
键/索引
au_id
id
作者编号
否
组合主键，聚集索引,外键authors（au_id）
title_id
tid
书编号
否
组合主键，聚集索引,外键titles（title_id）
au_ord
tinyint
是
royaltyper
int
百分比
是
（2）练习容
目的1：
1.加深对表间关系的理解。
2.理解数据库中数据的查询方法和应用。
3.学会各种查询的异同及相互之间的转换方法。
容1：
1.查询所有作者的作者号、信息
2.查询所有作者的、作者号信息，并在每个作者的作者号前面显示字符串“号：
”，表明显示的信息是信息
3.查询在CA州的作者和城市
4.查询出版日期在1992.1.1-2000.12.31之间的书名和出版日期（查询1991年出版的书）
5.查询每个出版的书
6.查询某店销售某书的数量
7.查询有销售记录的所有书信息，包括书的编号、书名、类型和价格
8.查询已销售书的信息
9.显示所有的书名（无销售记录的书也包括在）
10.查询已销售书的信息（书号、书名、作者等）
11.查询所有出版商业（business）书籍的的名称
目的2：
1.理解数据库中数据的其他查询方法和应用；
2.学会各种查询要求的实现。
容2：
在实验1的基础上，练习查询语句的使用，包括计算列、求和、最大、最小值、各类选择条件、字符匹配、分组和排序，体会各种查询的执行过程，为简单综合应用打下良好的基础。
1.查询书名以T开头或者号为0877，而且价格大于16的书的信息。
2.按照类型的升序和价格的降序（在类型相同时）显示书的信息（书名、作者、、类型、价格）
3.查询销售量大于30的书名及销售数量
4.查询在2002.1.1到2002.10.31间，每本书的销售总额
5.查询所有作者的所在城市和州名，要求没有重复信息
6.计算多少种书已被订价
7.查询每本书的书名、作者及它的售书总量
8.计算所有书的平均价格
9.查询价格最高的书的书名、作者及价格
目的3：
1.加深对数据库相关性质的理解；
2.各种约束性理解；
3.学会数据库中数据的更新的方法。
容3：
1.参照以上各表给出的主键、外键设置的设置要求，在自己创建的表中进行相应的设置。
2.向authors表中插入一行作者信息（具体值自定）
3.数量超过100的商店增加10%的折扣
4.删除2001.10.3的订单
5.删除1中所建立的索引
6.建立CA州作者所著书的视图（包括作者号、、所在州、书名、价格、出版日期）
7.建立付款方式是现金（cash）的订单视图
8.建立CA州的所有商店的视图
目的4：
1.在查询分析器中，练习使用IN、比较符、ANY或ALL等操作符进行查询。
2.练习使用EXISTS操作符进行嵌套查询操作
容4：
1.在pubs数据库的titleauthor和中，用IN谓词查询来自‘CA’州（在authors表中）的作家的全部作品（title_id）和作家的代号（au_id）。
2.在pubs数据库中，用比较运算符引出的子查询找出在名称为“AlgodataInfosystems”的所在城市中的作者的（au_lname,au_fname）
3.在pubs数据库中的titles表中，查询价格大于所有类型（TYPE）为“business”的图书价格的书名（title）和价格（price）
4.在pubs数据库的sales表中查找所有销售量大于所有图书平均销售量avg（qty））的书的代号（title_id）及销售量（qty）。
5.用带有IN的嵌套查询，查询来自城市（city）为“London”的客户所订的订单信息（customers和orders表）。
6.用带有IN的嵌套查询，查询Northwind数据库中的产品表（Products）中来自国家为“Germany”（在供应商表（Suppliers）表中）的供货商供应的产品信息（包括Productid，Productname，categoryid,unitprice）。
7.使用EXISTS子查询在Pubs数据库titles表及publishers表中查询NewMoonBooks所出版的图书名称（title）
目的5：
1.分类汇总。
容5：
1.找出pubs数据库titles表中计算机类图书中价格最高的图书的价格。
2.查询titles表中有几类图书。
3.按照州进行分类，查找每个州有几名作者。
4.要求按照出版商id进行分类，查找每个出版商的书到目前为止的销售额总和（ytd_sales）。
5.在pubs数据库的titles表中，找出平均价格大于18美元的书的种类。
6.在pubs数据库的titles表中，找出最高价大于20美元的书的种类。
7.找出title_id和pub_name的对应关系。
8.找出title_id,title和pub_name的对应关系。
9.查询每个作者的编号，，所出的书的编号，并对结果排序。
10.从authors表中选择state,city列，从publisher表中选择state,city列，并把两个查询的结果合并为一个结果集，并对结果集按city列、state列进行排序。
11.对上面的查询语句作修改，保留所有重复的记录。
12.显示所有来自CA州的作家的全部作品和作家代号。
（使用IN，和连接两种方法）
13.查找由位于以字母B开头的城市中的任一出版商出版的书名：
（使用exists和in两种方法）
一、简单查询学生选课数据
1、列出全部学生的信息。
2、列出信息系全部学生的学号及。
3、列出所有已被选修的选修课的课号。
4、求c01号课成绩大于80分的学生的学号及成绩，并按成绩由高到低列出。
5、列出非信息系学生的。
6、查询成绩在70~80分之间的学生选课得分情况
7、列出选修c01号课或c03号课的全体学生的学号和成绩。
8、列出所有95级学生的学生成绩情况。
9、列出成绩为空值（或不为空值）的学生的学号和课号。
10、求出所有学生的总成绩。
11、列出每个学生的平均成绩。
12、列出各科的平均成绩、最高成绩、最低成绩和选课人数。
1SELECT*FROM学生
2SELECT学号,FROM学生WHERE专业=’信息系’
3SELECTDISTINCT课号FROM选修课
4SELECT学号,成绩FROM选课WHERE课号=’01’AND成绩>80ORDERBY成绩DESC
5方法一：
SELECTFROM学生WHERE专业<>’信息系’
方法二：
SELECTFROM学生WHERENOT专业=’信息系’
方法三：
SELECTFROM学生WHERE专业!
=’信息系’
6方法一：
SELECT*FROM选课WHERE成绩>=70AND成绩<=80
方法二：
SELECT*FROM选课WHERE成绩BETWEEN70AND80
不在此围的查询：
（注意写出和以下语句等价的语句）
SELECT*FROM选课WHERE成绩NOTBETWEEN70AND80
7方法一：
SELECT学号,成绩FROM选课WHERE课号=’c01’OR课号=’c03’
方法二：
SELECT学号,成绩FROM选课WHERE课号IN（‘c01’,’c03’）
相反条件查询：
SELECT学号,成绩FROM选课WHERE课号NOTIN（‘c01’,’c03’）
8SELECT*FROM选课WHERE学号LIKE‘95%’
SELECT*FROM选课WHERE学号LIKE‘95____’
相反条件查询：
SELECT*FROM选课WHERE学号NOTLIKE‘98%’
9答案一：
SELECT学号,课号FROM选课WHERE成绩ISNULL
答案二：
SELECT学号,课号FROM选课WHERE成绩ISNOTNULL
10SELECTSUM（成绩）AS总成绩FROM选课
11SELECT学号,AVG（成绩）AS平均成绩FROM选课GROUPBY学号
12SELECT课号,AVG（成绩）AS平均成绩,MAX（成绩）AS最高分,
MIN（成绩）AS最低分,COUNT（学号）AS选课人数FROM选课GROUPBY课号
目的4：
2.在查询分析器中，练习使用IN、比较符、ANY或ALL等操作符进行查询。
3.练习使用EXISTS操作符进行嵌套查询操作
请完成以下习题：
14.在pubs数据库的titleauthor和中，用IN谓词查询来自‘CA’州（在authors表中）的作家的全部作品（title_id）和作家的代号（au_id）。
selecttitle_id,au_id
fromtitleauthor
whereau_idin（selectau_id
fromauthors
wherestate='CA'）
在pubs数据库中，用比较运算符引出的子查询找出在名称为“AlgodataInfosystems”的所在城市中的作者的（au_lname,au_fname）
selectau_lname,au_fname
fromauthors
wherecity=（selectcity
frompublishers
wherepub_name='AlgodataInfosystems'）
在pubs数据库中的titles表中，查询价格大于所有类型（TYPE）为“business”的图书价格的书名（title）和价格（price）
selecttitle,price
fromtitles
whereprice>all（selectprice
fromtitles
wheretype='business'）
在pubs数据库的sales表中查找所有销售量大于所有图书平均销售量avg（qty））的书的代号（title_id）及销售量（qty）。
selecttitle_id,qty
fromsales
whereqty>all（selectavg（qty）
fromsales
）
用带有IN的嵌套查询，查询来自城市（city）为“London”的客户所订的订单信息（customers和orders表）。
select*
fromorders
wherecustomerIDin（selectcustomerID
fromcustomers
wherecity='london'）
用带有IN的嵌套查询，查询Northwind数据库中的产品表（Products）中来自国家为“Germany”（在供应商表（Suppliers）表中）的供货商供应的产品信息（包括Productid，Productname，categoryid,unitprice）。
SELECTProductid,Productname,categoryid,unitprice
fromProducts
whereProductidin（selectsupplierID
fromSuppliers
wherecountry='Germany'）
2、提高操作实验
练习使用EXISTS操作符进行嵌套查询操作。
请完成以下习题：
使用EXISTS子查询在Pubs数据库titles表及publishers表中查询NewMoonBooks所出版的图书名称（title）
selecttitle
fromtitles
whereexists（select*
frompublishers
wherepub_name='NewMoonBooks'）
T-SQL高级查询课堂练习及答案
--练习1
--找出pubs数据库titles表中计算机类图书中价格最高的图书的价格。
USEpubs
GO
SELECTmax（price）FROMtitles
wheretype='popular_comp'
GO
--练习2
--查询titles表中有几类图书。
USEpubs
GO
SELECTcount（distincttype）FROMtitles
GO
--练习3
--按照州进行分类，查找每个州有几名作者。
USEpubs
GO
SELECTstate,count（*）FROMauthors
groupbystate
orderby1
GO
--练习4
--要求按照出版商id进行分类，查找每个出版商的书到目前为止的销售额总和（ytd_sales）。
USEpubs
GO
SELECTpub_id,sum（ytd_sales）FROMtitles
groupbypub_id
orderby1
GO
--练习5
--在pubs数据库的titles表中，找出平均价格大于18美元的书的种类。
USEpubs
GO
SELECTpub_id,avg（price）'平均价格'FROMtitles
GROUPBYpub_id
HAVINGavg（price）>18
GO
--练习6
--在pubs数据库的titles表中，找出最高价大于20美元的书的种类。
USEpubs
GO
SELECTtype,max（price）'平均价格'FROMtitles
GROUPBYtype
HAVINGmax（price）>20
GO
--练习7
--
找出title_id和pub_name的对应关系。
Usepubs
go
Selecttitles.title_id,publishers.pub_name
FromtitlesJOINpublishers
ONtitles.pub_id=publishers.pub_id
Go
--练习8
--找出title_id,title和pub_name的对应关系。
Usepubs
go
Selecttitles.title_id,titles.title,publishers.pub_name
FromtitlesJOINpublishers
ONtitles.pub_id=publishers.pub_id
Go
--练习9
--查询每个作者的编号，，所出的书的编号，并对结果排序。
Usepubs
go
Selectauthors.au_id,
auth
升级会员 