SQLpubs例子学习文档格式.docx
《SQLpubs例子学习文档格式.docx》由会员分享,可在线阅读,更多相关《SQLpubs例子学习文档格式.docx(15页珍藏版)》请在冰点文库上搜索。
所在州
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
折扣类型
stor_id
char(4)
商店编号
外键stores(stor_id)
lowqty
Smallint
数量下限
highqty
数量上限
discount
Float
折扣
Employee
默认值
emp_id
Empid
职工编号
fname
职工名
minit
char
(1)
lname
varchar(30)
职工姓
job_id
工作编号
1
外键jobs(job_id)
job_lvl
Tinyint
10
pub_id
出版社编号
'
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
[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'
Jobs
job_desc
varchar(50)
工作描述
min_lvl
max_lvl
(1)min_lvlCHECK约束定义为(min_lvl>
=10)。
(2)max_lvlCHECK约束定义为(max_lvl<
=250)。
pub_info
主键,外键publishers(pub_id)
logo
Image
标志图
pr_info
Text
出版信息
Publishers
pub_name
出版社名称
country
所在国家
1pub_idCHECK约束定义为
(pub_id='
1756'
OR(pub_id='
1622'
0877'
0736'
1389'
))))OR(pub_idLIKE'
99[0-9][0-0]'
roysched
title_id
Tid
书编号
外键titles(title_id)
lorange
Int
低
hirange
高
royalty
版权
Sales
组合主键,聚集索引,外键stores(stor_id)
ord_num
订单编码
组合主键,聚集索引
ord_date
订购日期
qty
数量
payterms
varchar(12)
付款方式
组合主键,聚集索引,外键titles(title_id)
titles
title
varchar(80)
书名
type
类型
外键publishers(pub_id)
price
Money
价格
advance
预付款
版税
Ytd_sales
年销售量
notes
varchar(200)
简介
pubdate
出版日期
Stores
stor_name
商店名称
stor_address
商店地址
titleauthor
id
组合主键,聚集索引,外键authors(au_id)
tid
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:
SELECTtitleFROMtitleswhereprice<
10ANDtype='
business'
7USEpubs
SELECTALL*FROMtitlesWHEREpriceBETWEEN10AND20;
8:
SELECTALL*FROMtitlesWHEREprice<
10orprice>
20
9:
SELECTALL*FROMauthorsWHEREcity='
Oakland'
ORcity='
Covelo'
PaloAlto'
10:
SELECTALL*FROMauthorsWHEREau_fnameLIKE('
____'
)ORDERBYau_lname
11:
SELECTALL*FROMauthorsWHEREau_lnameLIKE('
S%'
)ANDau_fnameLIKE('
%e%'
)
12USEpubs
SELECTALL*FROMauthorsWHEREau_lnameLIKE'
[A~K]%'
13:
SELECTMAX(price)as最高价格,MIN(price)最低价格,SUM(price)as总价格,AVG(price)as平均价格FROMtitles
14:
SELECTCOUNT(DISTINCT(type))as图书种类FROMtitles
15:
SELECTCOUNT(*)AS加利福亚洲作者人数FROMauthorswherestate='
16:
SELECTstateAS州名,COUNT(au_id)AS该州作者人数FROMauthorsGROUPBYstate
17:
SELECTtypeAS图书分类,AVG(price)AS平均价格FROMtitlesGROUPBYtype
18:
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
3.查询在CA州的作者姓名和城市
SELECTau_lname,au_fname,city
FROMauthors
WHEREstate='
;
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='
目的2:
1.理解数据库中数据的其他查询方法和应用;
2.学会各种查询要求的实现。
内容2:
在实验1的基础上,练习查询语句的使用,包括计算列、求和、最大、最小值、各类选择条件、字符匹配、分组和排序,体会各种查询的执行过程,为简单综合应用打下良好的基础。
1.查询书名以T开头或者出版社号为0877,而且价格大于16的书的信息。
SELECT*
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销售总额
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州的所有商店的视图