SQL补充作业完整版.docx

SQL补充作业完整版.docx

《SQL补充作业完整版.docx》由会员分享，可在线阅读，更多相关《SQL补充作业完整版.docx（15页珍藏版）》请在冰豆网上搜索。

SQL补充作业完整版

SQLServer补充作业

1．项目表（items_ordered）

customerid

order_date

Item

quantity

price

10330

30-Jun-1999

Pogostick

1

28.00

10101

30-Jun-1999

Raft

1

58.00

10298

01-Jul-1999

Skateboard

1

33.00

10101

01-Jul-1999

LifeVest

4

125.00

10299

06-Jul-1999

Parachute

1

1250.00

10339

27-Jul-1999

Umbrella

1

4.50

10449

13-Aug-1999

Unicycle

1

180.79

10439

14-Aug-1999

SkiPoles

2

25.50

10101

18-Aug-1999

RainCoat

1

18.30

10449

01-Sep-1999

SnowShoes

1

45.00

10439

18-Sep-1999

Tent

1

88.00

10298

19-Sep-1999

Lantern

2

29.00

10410

28-Oct-1999

SleepingBag

1

89.22

10438

01-Nov-1999

Umbrella

1

6.75

10438

02-Nov-1999

Pillow

1

8.50

10298

01-Dec-1999

Helmet

1

22.00

10449

15-Dec-1999

Bicycle

1

380.50

10449

22-Dec-1999

Canoe

1

280.00

10101

30-Dec-1999

HoolaHoop

3

14.75

10330

01-Jan-2000

Flashlight

4

28.00

10101

02-Jan-2000

Lantern

1

16.00

10299

18-Jan-2000

InflatableMattress

1

38.00

10438

18-Jan-2000

Tent

1

79.99

10413

19-Jan-2000

Lawnchair

4

32.00

10410

30-Jan-2000

Unicycle

1

192.50

10315

2-Feb-2000

Compass

1

8.00

10449

29-Feb-2000

Flashlight

1

4.50

10101

08-Mar-2000

SleepingBag

2

88.70

10298

18-Mar-2000

PocketKnife

1

22.38

10449

19-Mar-2000

Canoepaddle

2

40.00

10298

01-Apr-2000

EarMuffs

1

12.50

10330

19-Apr-2000

Shovel

1

16.75

Createtableitems_ordered

（

customeridvarchar（20）notnull,

order_datedatenotnull,

Itemvarchar（20）notnull,

quantityintnotnull,

pricemoneynotnull

）插入excel数据到数据库的语句：

--1:

打开外围应用配置

EXECsp_configure'showadvancedoptions',1

GO--sp_configure是T-SQL中的一个系统存储过程的名称，用于显示或更改当前服务器的全局配置设置

/*showadvancedoptions选项用来显示sp_configure系统存储过程高级选项。

当showadvancedoptions设置为1时，可以使用sp_configure列出高级选项。

默认值为0。

该设置将立即生效，无需重新启动服务器。

*/

RECONFIGURE--重新配置[ˌri:

kənˈfiɡə]

GO

EXECsp_configure'AdHocDistributedQueries',1

GO

RECONFIGURE

GO

--2：

导入表格excel

insertintoitems_orderedselect*from

OPENROWSET（'MICROSOFT.JET.OLEDB.4.0',

'Excel5.0;HDR=NO;DATABASE=d:

\items_ordered.xls',Sheet1$）

2.顾客表（customer）

customerid

firstname

lastname

city

state

10101

John

Gray

Lynden

Washington

10298

Leroy

Brown

Pinetop

Arizona

10299

Elroy

Keller

Snoqualmie

Washington

10315

Lisa

Jones

Oshkosh

Wisconsin

10325

Ginger

Schultz

Pocatello

Idaho

10329

Kelly

Mendoza

Kailua

Hawaii

10330

Shawn

Dalton

CannonBeach

Oregon

10338

Michael

Howell

Tillamook

Oregon

10339

Anthony

Sanchez

Winslow

Arizona

10408

Elroy

Cleaver

Globe

Arizona

10410

MaryAnn

Howell

Charleston

SouthCarolina

10413

Donald

Davids

GilaBend

Arizona

10419

Linda

Sakahara

Nogales

Arizona

10429

Sarah

Graham

Greensboro

NorthCarolina

10438

Kevin

Smith

Durango

Colorado

10439

Conrad

Giles

Telluride

Colorado

10449

Isabela

Moore

Yuma

Arizona

Createtablecustomer

（

customeridvarchar（50notnull）,

firstnamevarchar（50）notnull,

lastnamevarchar（50）notnull,

cityvarchar（50）notnull,

statevarchar（50）notnull

）

3.作者表（authors）

author_id

lastname

firstname

company

1

Hunter

Janson

O’Reilly

2

Kanetkar

Yashwant

BPBPublications

3

Pai

Kiran

PaiLtd.

Createtableauthors

（

author_idintnotnull,

firstnamevarchar（50）notnull,

lastnamevarchar（50）notnull,

companyvarchar（50）notnull

）4.书表（books）

book_id

Title

author_id

pages

1

LetusC

2

100

2

LetusC++

2

200

3

JavaServletProgramming

1

300

4

CProjects

2

400

Createtablebooks

（

author_idintnotnull,

book_idintnotnull,

Titlevarchar（50）notnull,

pagesvarchar（50）notnull

）--报错：

消息8152，级别16，状态14，第1行将截断字符串或二进制数据

--原因：

消息8152，级别16，状态14，第1行将截断字符串或二进制数据

如果确认你插入的数据没有超过限制，有可能是触发器的问题。

其原因就是在你改写数据时，所添加的数据超出了字段的限制。

5.习题

1）从itemsordered表中查找客户号为10449的客户的购买项目列表。

Select*fromitems_orderedwherecustomerid=10449

2）在itemsordered表中找出曾买过帐篷的所有记录列表。

Select*fromitems_orderedwhereitem='tent'

3）在itemsordered表中检索任何时间且开始字母为‘S’的项目的所有客户号，订购日期，项目价格的列表.

uselonewolf

selectcustomerid,order_date,price

fromitems_orderedwhereItemlike'S%'

/*谓词like可以用来进行字符串的匹配。

其一般语法格式为：

[not]like‘匹配串’[escape'<换码字符>']

其含义为查找指定的属性列值与<匹配串>相匹配的元组。

<匹配串>可以是一个完整的字符串，也可以含有通配符%和_其中：

%百分号代表任意长度（长度可以为）的字符串，如a%b表示以a开头，以b结尾的任意长度的字符串

_下横线代表任意单个字符如：

a_b表示以a开头，以b结尾的长度为的任意字符串

*/

4）显示itemsordered表的全部客户号的列表。

--显示itemsordered表的全部客户号的列表

selectcustomeridfromitems_ordered

5）在itemsordered表中检索最高价格。

selectMAX（price）fromitems_ordered

6）检索十二月份销售的平均价格。

仅仅查询某月的平均销售价。

/*SELECTAVG（price）

FROMitems_ordered

WHEREorder_date>=某月第一天ANDorder_date<=某月最后一天

当然月份必须要用单引号*/即：

selectAVG（price）

fromitems_ordered

whereorder_date>='1999-12-01'ANDorder_date<='1999-12-31'

或者利用datepart（）函数：

selectAVG（price）from

items_ordered

whereDATEPART（MM,order_date）=12

7）itemsordered表的总记录数是多少？

usesag

selectCOUNT（*）

fromitems_ordered

8）在itemsordered表中所有被订购的帐篷中，帐篷的最低价格是多少？

selectMIN（price）

fromitems_orderedwhereitem='tent'

9）检索customers表中居住在每一个州的总人数。

selectstate,COUNT（customerid）

fromcustomer

groupbystate

GROUPBY子句将查询结果按某一列或多列的值分组，值相等的为一组。

上句语句的解析如下：

对查询结果按state的值分组，所有具有相同state值的元组为一组，然后对每一组作用聚集函数count计算以求的该组的每州人数

10）在itemsordered表中，检索每一种item的item名，最高价和最低价。

selectitem,minimum=MIN（price）,maximum=MAX（price）

fromitems_ordered

groupbyitem

--显示三列item，minimum和maximum

11）每个顾客订货多少次？

在itemsordered表中检索顾客号，订购次数，订购总额。

selectcustomerid,COUNT（item）,sum（price）

fromitems_ordered

groupbycustomerid

12）在customers表中，找出每个州居住的总人数和州的名字。

selectstate,renshu=COUNT（customerid）

fromcustomer

groupbystate

13）在itemsordered表中检索最高价格大于190.00的项目名、项目的最高价和最低价。

--在itemsordered表中检索最高价格大于.00的项目名、项目的最高价和最低价。

selectitem,maxnimum=MAX（price）,minimum=MIN（price）

fromitems_ordered

whereprice>190.00groupbyitem

--groupby放置在where后面

14）显示定购次数大于1次的客户名，订购次数和订购总额。

selectfirstname+''+lastnameas'客户名',COUNT（item）as'订购次数',SUM（price）as'订购总额'

fromcustomer,items_ordered

wherecustomer.customerid=items_ordered.customerid/*where这句是必须要加的，否则会得出错误的结果*/

groupbyitems_ordered.customerid,firstname,lastname

havingCOUNT（item）>1

15）按lastname升序显示customers表中所有客户的lastname,firstname和city。

selectlastname+''+firstnameasname,city

fromcustomerorderbylastnameASC

16）根据customers表和itemordered表，找出定购次数最多的用户名字。

--从表customer表中找出客户号相符的客户名

selectlastname+''+firstname'订购次数最多的用户'

from

（

--求出等于最大订货次数的客户号

selectcustomerid

fromitems_ordered

groupbycustomerid

havingcount（item）=

（

--从cishu表中求出次数的最大值

selectmax（time）

from

（

--先找出全部的订货次数

selectcount（item）time,customeridid

fromitems_ordered

groupbycustomerid

）cishu

）

）a,customer

wherea.customerid=customer.customerid

17）在itemsordered表中检索价格大于10.00的所有项目的价格列表。

并根据价格升序排列

selectitem,price

fromitems_ordered

whereprice>10.00

orderbypriceASC

18）在itemsordered表中检索定购商品名称中不包含‘ snowshoes’和‘earmuffs’的客户ID，订货日期和项目名

selectcustomeridasID,order_dateasdate,item

fromitems_ordered

whereitemnotin（'snowshoes','earmuffs'）

19）检索开始字母是，‘S’，‘P’或‘F’的所有项目的项目名称和价格。

selectitem,price

fromitems_ordered

whereitemlike'[SPF]%'

20）在itemsordered表中检索所有价格值在10.00到80.00间的所有项目的订购日期，项目名和价格列表

selectorder_date,item,price

fromitems_ordered

wherepricebetween10.00and80.00

21）在customers表中检索所有州是Arixona或Washington或Oklahoma或Colorado或Hawaii的姓，城市名，州名的记录列表。

selectfirstname,city,state

fromcustomer

wherestatein（'Arixona','Washington','Oklahoma','Colorado','Hawaii'）

22）在itemsordered表中检索项目名和每项目的单位价格。

selectshouru/time'单位价格',a.Item'项目'

from（

--购买次数

Selectcount（customerid）astime,item

Fromitems_ordered

Groupbyitem

）a,

（

--求各项目的总收入

selectSUM（price）shouru,item

fromitems_ordered

groupbyitem

）b

wherea.Item=b.Item

orderbyb.Item

23）根据authors和book表，显示出所有作者的信息，如该作者已有

书出版，同时显示出该作者所有出版书籍的名字。

selectauthors.*,books.*

fromauthors,books

whereauthors.author_id=books.author_id

或者：

selectbooks.author_id,lastname+firstnameasname,book_id,company,title,pages

fromauthors,books

whereauthors.author_id=books.author_id

24）找到出版书最多的作者名。

--有问题

selectfirstname+''+lastnameas'出版数最多的作者'

fromauthors

whereauthor_id=

（--对两个新表操作，选出作者出版数量与最大数量相等的作者号

selectauthor_idfrom

（--对表books操作,根据作者号分组,选出作者号及相应出版书的数量

selectauthor_id,COUNT（author_id）book_coun

frombooks

groupbyauthor_id

）t1

wherebook_coun=

（--从新的表中选出出版数量最多的量

selectMAX（book_coun）max_pubfrom

（--将表books按照author_id分组,并计算出各作者出版数量,列出数量和作者号,组成新的表

selectauthor_id,COUNT（author_id）book_coun

frombooksgroupbyauthor_id

）t2

）

）

或者：

selectfirstname+''+lastname'作者名'

fromauthors

whereauthor_id=

（

--对表books操作,选出书籍数量与最大数量相匹配的作者号

selectauthor_id

frombooks

groupbyauthor_id

havingCOUNT（title）=

（

--在对a表进行操作,选出times最大的数量

selectmax（a.times）b

from

（

--对books表操作,先根据作者号排列表,并计算出各作者的书籍本数

selectCOUNT（title）times,author_id

frombooks

groupbyauthor_id

）a

）

）

25）如每一页的报酬是500元，显示出有书出版的作者的全部稿酬。

Selectfirstname+''+lastname'作者',sum（convert（int,pages））*500'酬劳'

frombooks,authors

whereauthors.author_id=books.author_id

Groupbyauthors.author_id,firstname,lastname

26）找出Janson写的所有书

selecttitleas'janson写的所有书'

frombooks

whereauthor_id=

（

selectauthor_id

fromauthors

wherelastname='janson'）