數(shù)據(jù)庫訓(xùn)練題目課件-學(xué)習(xí)講義

1、在MSSQL2000pub數(shù)據(jù)庫下，完成下面的作業(yè)，分別寫出SQL語句。1、查找在美國的所有出版商select * from publishers where country=,USAT2、查找在法國巴黎的出版商名字select pub_name from publishers where country=1Francefand city=,Paris,3、查找所有價庶大于20美元的出版物的名字select title from titles where price154、查找在2000年出版的出版物記錄select * from titles where pubdate between 20

2、00lland 20001231 23:59:595、查找出版物名稱中含有“computer”字樣的出版物。select * from titles where title like % computer %6、查找在美國、所在州不為空的出版商記錄select * from publishers where country=US Aand state is not null7、在MSSQLServer2000的pub數(shù)據(jù)庫下，查找出版商的pubJd為1389,的出版物的名字，價格，以及 出版日期，結(jié)果按出版時間降序排列。select title,price,pubdate from title

3、s where pub_id=,1389, order by pubdate desc 8、在MSSQLServer2000的pub數(shù)據(jù)庫下，查找出版商品pubJd為3389,的出版物的數(shù)量，最大價格, 平均價格，總價格(一種刊物一份的總價)。生成的表的列名分別為數(shù)量最大價格，平均價格，總 價格。select count()數(shù)量,max(price)最大價格,avg(price)平均價格,sum(price)總價格 from titles where pub_id=n1389n9、仿照上題，查找所有出版商的出版物的數(shù)量，最大價格，平均價格，總價格，并列出各出版商的pub.ido 按照出版物的數(shù)

4、量對結(jié)果排序。select pub_id,count(*)數(shù)量,max(price)最大價格,avg(price)平均價格,sum(price)總價格 from titlesgroup by pub_idorder by 數(shù)量1()、查詢所有出版商的姓名，及其發(fā)行物的種類。(一種刊物算一個種類)select pub_name,count(title)from publishers left outer join titleson publishers.pub_id=titles.pub_idgroup by pub_name11、查詢比所看的出版物平均價格高的出版物名稱以及價格。select

5、title,price from titleswhere price(select avg(price) from titles)12、查詢美國出版商發(fā)行的刊物名稱。select title from titleswhere pub_id in(select pub_id from publishers where country=,USAt)或select title from titles,publisherswhere titles.pub_id=publishers.pub_id and country=,USA613、查詢比pubjd為389而出版物所有價格都高的出版物名稱，價格。s

6、elect title,price from titles where priceAll(select price from titles where pub_id=t1389,)春詢比pubjd為1389,的某一出版物價格高的出版物名稱，價格。select title,price from titles where priceany(select price from titles where pub_id=,1389,)14、分別查詢pub_id為P877,與“38夕的出版物，并返回一個結(jié)果集。(用union)select * from titles where pub_id=0877un

7、ionselect * from titles where pub_id=,1389,15、在出版商中插入記錄9909?夫連理工大學(xué)出版社？dalian；nun：chininsert into publishers(pub_id,pub_name,city,state,country) vahies(99O9;大連理工大 學(xué)出版社；daliannuHdiina，)或insert into publishers vahiesC9909；大連理工大學(xué)出版社？dalianniilldiiiia，)16、將pub_id為，0877,與“38夕的出版商的出版物價格均升5美元，出版日期設(shè)為2002-09-

8、09。update titlesset price=price+5,pubdate=,2002-09-09,where pubjd in(1389,0877)或update titlesset price=price+5,pubdate=,2002-09-09,where pub_id =,1389,or pub_id=,0877117、刪除pubjd為9909的出版嗇記錄delete from publishers where pub_id=,9909618、查詢沒有出版物的出版商記錄select * from publishers where pub_id not in(select di

9、stinct pub_id from titles)19、定義一個視圖，反映的是各個出版商名稱以及出版物數(shù)目。(不顯示沒有值版物的出版商)create view v_pub_counts asselect pub_name,count(*) as countfrom publishers,titleswhere publishers.pub_id=titles.pub_idgroup by pub_name2()、定義一個視圖，反映的是各個出版商名稱以及出版物數(shù)目。(顯示沒有出版物的出版商)create view pubsum asselect pub_name,count(title) as

10、 pub_countfrom publishers left outer join titleson publishers.pub_id=titles.pub_id group by pub_name21、統(tǒng)計(jì)每種遜 (type)刊物的刊物數(shù)目(每條titles記錄為1條)，并只輸出刊物數(shù)目大于2的記錄。 select type,count(title)from titlesgroup by typehaving count(title)222、查詢出版了所有類型(type)刊物的出版商姓名。方法一：select pub_namefrom publisherswhere not exists(

11、select *from (select distinct type from titles) twhere not existsselect *from titleswhere(titles.type=t.type) and(titles.pub_id=publishers.pub_id)方法二：select pub_namefrom publisherswhere not exists(select distinct type from titles twherenot existsselect *from titleswhere(titles.type=t.type) and(titles.pub_id=publishers