Select语句查询理解练习pubs.docx

Select语句查询理解练习pubs.docx

《Select语句查询理解练习pubs.docx》由会员分享，可在线阅读，更多相关《Select语句查询理解练习pubs.docx（22页珍藏版）》请在冰豆网上搜索。

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方法一：

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_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