实验3 交互式SQL语言1

数据库实验 实验3

因为老师给的文档全是英文，看不懂，于是截了几张图翻译图
这次实验没有用外键
使用的软件是Microsoft SQL Server Management Studio 18

附上翻译图
authors:

publishers:

sales:

titles:

stores:

4、使用SQL查询分析器，对Pubs数据库完成如下查询操作：
1）简单查询
（1）查询所有作者的作者号、姓名信息；（authors表）
（2）查询所有作者的姓名和作者号信息，并在每个作者的作者号前面显示字符串“身份证号：”表明显示信息是身份证信息；（authors表）
（3）改变显示列名。显示所有作者的姓名信息和作者号信息，要求用“名”和“姓”来区别fname和lname，“作者编号”来区分作者号；（authors表）
（4）查询所有书在价格提高10%后的价格和书名信息；（titles表）
（5）查询所有书的书号和税后价格。（titles表，royalty列表示税率）；
（6）查询所有作者的姓和“名的第一个字符”以及作者号；（authors表，SUBSTRING函数）
（7）查询邮政编码大于9000的作者姓名和电话信息；（authors表）
（8）查询在CA州的作者姓名和城市；（authors表）
（9）查询出版日期在1/1/1991到12/31/1991之间的书名（书名限制为38个字符）和出版日期；（titles表，SUBSTRING函数）
（10）查询书的类型是mod_cook或trad_cook的书名和它的类型；（titles表）
（11）查询店名中包含Book的店的信息；(stores表)；
（12）查询所有无价格的图书；（titles表）
（13）查询书名以T开头或者出版号为0877，且价格大于16美元的书的信息；（titles表）
（14）查询所有作者的所在城市和州名，要求没有重复信息；（authors表）
（15）按照类型的升序和价格的降序显示书的信息；（titles表）
2）生成汇总数据
（1）计算多少种书已被定价；（titles表）
（2）计算每本书的书号及它的售书总量；（sales表）
（3）求销售量大于30的书号及销售数量；（sales表）
（4）显示在1994年1月1日到1994年10月31日间，每本书的销售总额；（sales表）
3）连接查询
（1）求每本杂志上刊登的文章；（titles, publishers表）
（2）求某书店销售某书的数量；（titles, stores, sales表）
（3）显示所有已销售的书名。
4）子查询
（1）查询有销售记录的所有书信息，包括书的编号、书名、类型和价格；
（2）求已销售的书的信息；

全部代码 最后一个问题没写

CREATE TABLE authors
(
au_id VARCHAR(11) PRIMARY KEY,
au_lname VARCHAR(40) NOT NULL,
au_fname VARCHAR(20)NOT NULL,
phone CHAR(12)NOT NULL,
address VARCHAR(40),
city VARCHAR(20),
state CHAR(2),
zip CHAR(5),
contract BIT NOT NULL
);

CREATE TABLE publishers
(
pub_id CHAR(10)PRIMARY KEY,
pub_name VARCHAR(40)NOT NULL,
city VARCHAR(20)NOT NULL,
state CHAR(4),
country VARCHAR(20)NOT NULL
);

CREATE TABLE titles
(
title_id VARCHAR(6)PRIMARY KEY,
title VARCHAR(80) NOT NULL,
type CHAR(12) NOT NULL,
pub_id CHAR(4),
price MONEY,
advance MONEY,
royalty INT,
ytd_sales INT,
notes VARCHAR(200),
pubdate DATETIME NOT NULL
);

create table stores(
stor_id CHAR(4) primary key,
stor_name VARCHAR(40),
stor_adderss varchar(40),
city varchar(20),
state char(2),
zip char(5)
);

create table sales
(
stor_id CHAR(4) not null,
ord_num VARCHAR(20)NOT NULL,
ord_date DATETIME NOT NULL,
qty SMALLINT NOT NULL,
payterms VARCHAR(12) NOT NULL,
title_id VARCHAR(6) NOT NULL
);

insert into authors values
('172-32-1176','White','Johnson','408 496-7223','10932 Bigge Rd', 'Menlo Park','CA','94025','TRUE'),
('213-46-8915','Green','Marjorie','415 986-7020','309 63rd St. #411', 'Oakland','CA','94618','TRUE'),
('238-95-7766','Carson','Cheryl','415 548-7723','589 Darwin Ln.','Berkeley','CA','94705','TRUE'),
('267-41-2394','O''Leary','Michael','408 286-2428','22 Cleveland Av. #14','San Jose','CA','95128','TRUE'),
('274-80-9391','Straight','Dean','415 834-2919','5420 College Av.', 'Oakland','CA','94609','TRUE'),
('341-22-1782','Smith','Meander','913 843-0462','10 Mississippi Dr.','Lawrence','KS','66044','FALSE'),
('409-56-7008','Bennet','Abraham','415 658-9932','6223 Bateman St.','Berkeley','CA','94705','TRUE'),
('427-17-2319','Dull','Ann','415 836-7128','3410 Blonde St.','Palo Alto','CA','94301','TRUE'),
('472-27-2349','Gringlesby','Burt','707 938-6445','PO Box 792','Covelo','CA','95428','TRUE'),
('486-29-1786','Locksley','Charlene','415 585-4620','18 Broadway Av.','San Francisco','CA','94130','TRUE'),
('527-72-3246','Greene','Morningstar','615 297-2723','22 Graybar House Rd.','Nashville','TN','37215','FALSE'),
('648-92-1872','Blotchet-Halls','Reginald','503 745-6402','55 Hillsdale Bl.','Corvallis','OR','97330','TRUE'),
('672-71-3249','Yokomoto','Akiko','415 935-4228','3 Silver Ct.','Walnut Creek','CA','94595','TRUE'),
('712-45-1867','del Castillo','Innes','615 996-8275','2286 Cram Pl. #86','Ann Arbor','MI','48105','TRUE'),
('722-51-5454','DeFrance','Michel','219 547-9982','3 Balding Pl.','Gary','IN','46403','TRUE'),
('724-08-9931','Stringer','Dirk','415 843-2991','5420 Telegraph Av.','Oakland','CA','94609','FALSE'),
('724-80-9391','MacFeather','Stearns','415 354-7128','44 Upland Hts.','Oakland','CA','94612','TRUE'),
('756-30-7391','Karsen','Livia	','415 534-9219','5720 McAuley St.','Oakland','CA','94609','TRUE'),
('807-91-6654','Panteley','Sylvia','301 946-8853','1956 Arlington Pl.','Rockville','MD','20853','TRUE'),
('846-92-7186','Hunter','Sheryl	','415 836-7128','3410 Blonde St.','Palo Alto','CA','94301','TRUE'),
('893-72-1158','McBadden','Heather','707 448-4982','301 Putnam','Vacaville','CA','95688','FALSE'),
('899-46-2035','Ringer','Anne','801 826-0752','67 Seventh Av.','Salt Lake City','UT','84152','TRUE'),
('998-72-3567','Ringer','Albert','801 826-0752','67 Seventh Av.','Salt Lake City','UT','84152','TRUE')

insert into publishers values
('0736','New Moon Books','Boston','MA','USA'),
('0877','Binnet & Hardley','Washington','DC','USA'),
('1389','Algodata Infosystems','Berkeley','CA','USA'),
('1622','Five Lakes Publishing','Chicago','IL','USA'),
('1756','Ramona Publishers','Dallas','TX','USA'),
('9901','GGG&G','New York',' ','Germany'),
('9952','Scootney Books','New York','NY','USA'),
('9999','Lucerne Publishing','Paris',' ','France')

insert into sales values
('6380','6871','1994/9/14',5,'Net 60'	,'BU1032'),
('6380','722a','1994/9/13',3,'Net 60'	,'PS2091'),
('7066','A2976','1993/5/24',50,'Net 30','PC8888'),
('7066','QA7442.3','1994/9/13',75,'ON invoice','PS2091'),
('7067','D4482','1994/9/14',10,'Net 60','PS2091'),
('7067','P2121','1992/6/15',40,'Net 30','TC3218'),
('7067','P2121','1992/6/15',20,'Net 30','TC4203'),
('7067','P2121','1992/6/15',20,'Net 30','TC7777'),
('7131','N914008','1994/9/14',20,'Net 30','PS2091'),
('7131','N914014','1994/9/14',25,'Net 30','MC3021'),
('7131','P3087a	','1993/5/29',20,'Net 60','PS1372'),
('7131','P3087a	','1993/5/29',25,'Net 60','PS2106'),
('7131','P3087a	','1993/5/29',15,'Net 60','PS3333'),
('7131','P3087a	','1993/5/29',25,'Net 60','PS7777'),
('7896','QQ2299	','1993/10/2',15,'Net 60','BU7832'),
('7896','TQ456	','1993/12/1',10,'Net 60','MC2222'),
('7896','X999','1993/2/21',35,'ON invoice','BU2075'),
('8042','423LL922',	'1994/9/14',15,'ON invoice','MC3021'),
('8042','423LL930',	'1994/9/14',10,'ON invoice','BU1032'),
('8042','P723',	'1993/3/11',25,'Net 30','BU1111'),
('8042','QA879.1','1993/5/22',30,'Net 30','PC1035')

insert into stores values
('6380','Eric the Read Books','788 Catamaugus Ave.','Seattle','WA','98056'),
('7066','Barnum''s','567 Pasadena Ave.','Tustin','CA','92789'),
('7067','News & Brews','577 First St.','Los Gatos','CA','96745'),
('7131','Doc-U-Mat: Quality Laundry and Books','24-A Avogadro Way','Remulade','WA','98014'),
('7896','Fricative Bookshop','89 Madison St.','Fremont','CA','90019'),
('8042','Bookbeat','679 Carson St.','Portland','OR','89076')

insert into titles values
('BU1032','The Busy Executive''s Database Guide','business','1389','¥19.99','¥5,000.00'	,10,4095,'An overview of available database systems with emphasis on common business applications. Illustrated.','1991/6/12'),
('BU1111','Cooking with Computers: Surreptitious Balance Sheets','business','1389','¥11.95','¥5,000.00',10,3876,
'Helpful hints on how to use your electronic resources to the best advantage.','1991/6/9'),
('BU2075','You Can Combat Computer Stress!','business','0736','¥2.99','¥10,125.00',24,18722,'The latest medical and psychological techniques for living with the electronic office. Easy-to-understand explanations.','1991/6/30'),
('BU7832','Straight Talk About Computers','business','1389','¥19.99','¥5,000.00',10,4095,	'Annotated analysis of what computers can do for you: a no-hype guide for the critical user.','1991/6/22'),
('MC2222','Silicon Valley Gastronomic Treats','mod_cook','0877','¥19.99','¥0.00',12,2032,'Favorite recipes for quick, easy, and elegant meals.','1991/6/9'),
('MC3021','The Gourmet Microwave','mod_cook','0877','¥2.99','¥15,000.00',24,22246,'Traditional French gourmet recipes adapted for modern microwave cooking.','1991/6/18'),
('MC3026','The Psychology of Computer Cooking','UNDECIDED','0877',' ',' ',' ',' ',' ','2000/8/6'),						
('PC1035','But Is It User Friendly?','popular_comp','1389','¥22.95','¥7,000.00',16,8780,'A survey of software for the naive user, focusing on the ''friendliness'' of each.','1991/6/30'),
('PC8888','Secrets of Silicon Valley','popular_comp','1389','¥20.00','¥8,000.00',10,4095,'Muckraking reporting on the world''s largest computer hardware and software manufacturers.','1994/6/12'),
('PC9999','Net Etiquette','popular_comp','1389',' ',' ',' ',' ','A must-read for computer conferencing.','2000/8/6'),
('PS1372','Computer Phobic AND Non-Phobic Individuals: Behavior Variations','psychology','0877','¥21.59','¥7,000.00','10',375,'A must for the specialist, this book examines the difference between those who hate and fear computers and those who don''t.','1991/10/21'),
('PS2091','Is Anger the Enemy?','psychology','0736','¥10.95','¥2,275.00',12,2045,'Carefully researched study of the effects of strong emotions on the body. Metabolic charts included.','1991/6/15'),
('PS2106','Life Without Fear','psychology','0736','¥7.00','¥6,000.00',10,111,'New exercise, meditation, and nutritional techniques that can reduce the shock of daily interactions. Popular audience. Sample menus included, exercise video available separately.','1991/10/5'),
('PS3333','Prolonged Data Deprivation: Four Case Studies','psychology','0736','¥19.99','¥2,000.00',10,4072,'What happens when the data runs dry?  Searching evaluations of information-shortage effects.','1991/6/12'),
('PS7777','Emotional Security: A New Algorithm','psychology','0736','¥7.99','¥4,000.00',10,3336,'Protecting yourself and your loved ones from undue emotional stress in the modern world. Use of computer and nutritional aids emphasized.','1991/6/12'),
('TC3218','Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean','trad_cook','0877','¥20.95','¥7,000.00',10,375,'Profusely illustrated in color, this makes a wonderful gift book for a cuisine-oriented friend.','1991/10/21'),
('TC4203','Fifty Years in Buckingham Palace Kitchens','trad_cook','0877','¥11.95','¥4,000.00',14,15096,'More anecdotes from the Queen''s favorite cook describing life among English royalty. Recipes, techniques, tender vignettes.','1991/6/12'),
('TC7777','Sushi, Anyone?','trad_cook','0877','¥14.99','¥8,000.00',10,4095,'Detailed instructions on how to make authentic Japanese sushi in your spare time.','1991/6/12')

select au_id,au_lname,au_fname from authors;
select '身份证号'+au_id,au_lname,au_fname from authors;
select au_id 身份编号,au_lname 姓,au_fname 名 from authors;
select titles.price*1.1 from titles;
select title_id,price*(1+royalty) price from titles;
select au_id,au_lname,SUBSTRING(au_fname,1,1) from authors;
select au_lname,au_fname from authors where zip>9000;
select SUBSTRING(title,1,38),pubdate from titles where pubdate>'1/1/1991' and pubdate <'12/31/1991';
select title,titles.type from titles where type='mod_cook' or type='trad_cook';
select *from stores where stor_name like '%Book%';
select *from titles where price=' ';
select *from titles where SUBSTRING(title,1,1)='T' or pub_id=0877 and price>16;
select DISTINCT city,state from authors;
select *from titles order by type asc,price desc;

select count(title_id) from titles where price is not null;
select title_id,sum(qty) from sales group by title_id;
select stor_id,sum(qty) qty from sales group by stor_id having sum(qty)>30;
select titles.title_id,sum(sales.qty)*titles.price 销售总额 from sales,titles where 
titles.pubdate between '1/1/1994'and '10/31/1994' group by sales.title_id,titles.price,titles.title_id;


select titles.title,publishers.pub_name from titles,publishers where titles.pub_id=publishers.pub_id;
select a.title,b.stor_name,c.qty from titles a,stores b,sales c where a.title_id=c.title_id 
and b.stor_id=c.stor_id;
select  distinct titles.title from titles,stores,sales where stores.stor_id=sales.stor_id;

--子查询--
select *from titles where titles.title_id in(select title_id from sales );