Select语句查询理解练习.docx
《Select语句查询理解练习.docx》由会员分享，可在线阅读，更多相关《Select语句查询理解练习.docx（25页珍藏版）》请在冰豆网上搜索。
Select语句查询理解练习
该实验以SQLServer2000系统自带的pubs数据库为例，以一个图书出版公司为模型。
SQLSERVER200实体关系图描述
roysched
kirupr^TaLty
hir3in|4
r^-paLty
publizh^Tz
jrab_idFl-b^sajrn
e
state
coTiatrF
h簾i
Iemployee
9
卷mP—idi
fiarc
minit
laam
klrc.date
oa_
jobs
Idiscc-urds
discvultrs世
ni*r—id.
kwqtr
hifhQiy
diecouii
litlauthor
an^iititle.ilai^j&rdLroyaltyper
job.idjob_deE匚miiLlifl
E-R图描述
Sales（销售）
N
销售
roys
（版税）
ched
1
pub_
info
（发行信息）
N
对应
有
N
X1
1
z1
1
stores（
商店）
\
titles（
书）
publi
（发行商
shers
）
N
1
实施折扣
雇佣
discounts（折扣）
参与编写
N
empl
oyee
（雇员
N
authors
（作者）
担任
jobs（职位）
（1）该系统中数据库基本表如下:
Authors
属性名
数据类型
含义说明
可为空
检查
键/索引
au」d
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」vl
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」d
id
作者编号
否
组合主键，聚集索引，外键
authors（au」d）
title_id
tid
书编号
否
组合主键，聚集索引，外键
titles（title_id）
au_ord
tinyint
是
royaltype
r
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数据库中，用比较运算符引出的子查询找出在名称为"Algodata
Infosystems”的出版社所在城市中的作者的姓名（au」name,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表中查询NewMoon
Books出版社所出版的图书名称（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方法一：
SELECT姓名FROM学生WHERE专业<>'信息系’
方法二：
SELECT姓名FROM学生WHERENOT专业='信息系’
方法三：
SELECT姓名FROM学生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」din（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
wherecustomerlDin（selectcustomerlD
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表中查询NewMoon
Books出版社所出版的图书名称（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（distinettype）FROMtitles
GO
--练习3
--按照州进行分类，查找每个州有几名作者。
USEpubs
GO
SELECTstate,count（*）FROMauthorsgroupbystate
orderby1
GO
--练习4
（ytd_sales）。
--要求按照出版商id进行分类，查找每个出版商的书到目前为止的销售额总和
USEpubs
GO
SELECTpub_id,sum（ytd_sales）FROMtitles
groupbypub_id
orderby1
GO
--练习5
--在pubs数据库的titles表中，找出平均价格大于18美元的书的种类。
USEpubs
GO
SELECTpub_id,avg（price）'平均价格'FROMt
升级会员 