SQLpubs例子学习.docx
- 文档编号:5356713
- 上传时间:2022-12-15
- 格式:DOCX
- 页数:15
- 大小:171.61KB
SQLpubs例子学习.docx
《SQLpubs例子学习.docx》由会员分享,可在线阅读,更多相关《SQLpubs例子学习.docx(15页珍藏版)》请在冰豆网上搜索。
SQLpubs例子学习
实验SQLServer2000表的查询
SQLServer2000系统自带的Pubs示例数据库为例,以一个虚拟图书出版公司为模型。
以下是它的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
版权百分比
是
实验
一、目的要求:
掌握表的查询,学会使用查询语句。
二、内容步骤:
(上机练习题)
打开
每题输入完都先后执行
、
。
1:
SELECTALL*FROMauthors
2:
SELECTtitle,price,pubdateFROMtitles
3:
SELECTtitle_id,title,price*0.8as‘NewPrice’FROMtitles
4:
USEpubs
SELECTDISTINCTtypeFROMtitles
5:
USEpubs
SELECTau_lname,au_fname,phoneFROMauthorswherestate='CA'
6:
USEpubs
SELECTtitleFROMtitleswhereprice<10ANDtype='business'
7USEpubs
SELECTALL*FROMtitlesWHEREpriceBETWEEN10AND20;
8:
USEpubs
SELECTALL*FROMtitlesWHEREprice<10orprice>20
9:
USEpubs
SELECTALL*FROMauthorsWHEREcity='Oakland'ORcity='Covelo'ORcity='PaloAlto'
10:
USEpubs
SELECTALL*FROMauthorsWHEREau_fnameLIKE('____')ORDERBYau_lname
11:
USEpubs
SELECTALL*FROMauthorsWHEREau_lnameLIKE('S%')ANDau_fnameLIKE('%e%')
12USEpubs
SELECTALL*FROMauthorsWHEREau_lnameLIKE'[A~K]%'
13:
USEpubs
SELECTMAX(price)as最高价格,MIN(price)最低价格,SUM(price)as总价格,AVG(price)as平均价格FROMtitles
14:
USEpubs
SELECTCOUNT(DISTINCT(type))as图书种类FROMtitles
15:
USEpubs
SELECTCOUNT(*)AS加利福亚洲作者人数FROMauthorswherestate='CA'
16:
USEpubs
SELECTstateAS州名,COUNT(au_id)AS该州作者人数FROMauthorsGROUPBYstate
17:
USEpubs
SELECTtypeAS图书分类,AVG(price)AS平均价格FROMtitlesGROUPBYtype
18:
USEpubs
SELECTtitleAS书名,typeAS类型,priceAS单价FROMtitlesORDERBYtypeCOMPUTEAVG(price)BYtype
三、练习内容
目的1:
1.加深对表间关系的理解。
2.理解数据库中数据的查询方法和应用。
3.学会各种查询的异同及相互之间的转换方法。
内容1:
1.查询所有作者的作者号、姓名信息
SELECTau_id,au_lname,au_fname
FROMauthors;
2.查询所有作者的姓名、作者号信息,并在每个作者的作者号前面显示字符串“身份证号:
”,表明显示的信息是身份证信息
SELECT'身份证号:
'as身份证号,au_id,au_lname,au_fname
FROMauthors;
3.查询在CA州的作者姓名和城市
SELECTau_lname,au_fname,city
FROMauthors
WHEREstate='CA';
4.查询出版日期在1992.1.1-2000.12.31之间的书名和出版日期(查询1991年出版的书)
SELECTtitle,pubdate
FROMtitles
WHEREpubdatebetween'1/1/1992'and'12/31/2000';
5.查询每个出版社出版的书
SELECTtitle,pub_name
FROMtitles,Publishers
WHEREtitles.pub_id=Publishers.pub_idorderbypub_name;
6.查询某店销售某书的数量
SELECTstor_name,sum(qty)
FROMsales,stores
WHEREsales.stor_id=stores.stor_idgroupbystor_name;
7.查询有销售记录的所有书信息,包括书的编号、书名、类型和价格
SELECTdistinctsales.title_id,title,type,price
FROMsales,titles
WHEREsales.title_id=titles.title_id;
8.显示所有的书名(无销售记录的书也包括在内)
SELECTtitle
FROMtitles;
9.查询已销售书的信息(书号、书名、作者等)
SELECTDISTINCTsales.title_id,titles.title,authors.au_lname
FROMsales,titles,titleauthor,authors
WHEREsales.title_id=titles.title_idandsales.title_id=titleauthor.title_idandtitleauthor.ord=1andtitleauthor.au_id=authors.au_id;
10.查询所有出版商业(business)书籍的出版社的名称
SELECTDISTINCTpub_name
FROMtitles,publishers
WHEREtitles.pub_id=publishers.pub_idandtype='business';
目的2:
1.理解数据库中数据的其他查询方法和应用;
2.学会各种查询要求的实现。
内容2:
在实验1的基础上,练习查询语句的使用,包括计算列、求和、最大、最小值、各类选择条件、字符匹配、分组和排序,体会各种查询的执行过程,为简单综合应用打下良好的基础。
1.查询书名以T开头或者出版社号为0877,而且价格大于16的书的信息。
SELECT*
FROMtitles
WHERE(titleLIKE'T%'ORpub_id='0087')ANDPRICE>16;
2.按照类型的升序和价格的降序(在类型相同时)显示书的信息(书名、作者、出版社、类型、价格)
SELECTtitles.title,authors.au_lname,publishers.pub_name,titles.type,titles.priceFROMtitles,titleauthor,authors,publishers
WHEREtitles.title_id=titleauthor.title_idandtitleauthor.au_id=authors.au_idandtitles.pub_id=publishers.pub_id
ORDERBYtitles.type,titles.priceDESC
3.查询销售量大于30的书名及销售数量
SELECTDISTINCTtitles.title,sales.qty
FROMtitles,titleauthor,authors,sales
WHEREtitles.title_id=titleauthor.title_idandtitleauthor.au_id=authors.au_idandtitles.title_id=sales.title_idandsales.qty>30
4.查询在2002.1.1到2002.10.31间,每本书的销售总额
SELECTtitles.title,SUM(sales.qty*titles.price)AS销售总额
FROMtitles,titleauthor,authors,sales
WHEREtitles.title_id=titleauthor.title_idandtitleauthor.au_id=authors.au_idandtitles.title_id=sales.title_idandsales.ord_dateBETWEEN'1/1/1993'AND'10/31/1993'
GROUPBYtitles.title
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州的所有商店的视图
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQLpubs 例子 学习