Select语句查询理解练习.docx
《Select语句查询理解练习.docx》由会员分享,可在线阅读,更多相关《Select语句查询理解练习.docx(25页珍藏版)》请在冰豆网上搜索。
![Select语句查询理解练习.docx](https://file1.bdocx.com/fileroot1/2023-8/13/26c5b6d7-8447-42a8-bdb7-b00743967ac8/26c5b6d7-8447-42a8-bdb7-b00743967ac81.gif)
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