SQL查询语句例题

、

 

 

 

create database test
go

use test
go

CREATE TABLE R
(
R# CHAR(4) NOT NULL PRIMARY KEY,
Rname VARCHAR(20),
STATUS VARCHAR(20),
CITY VARCHAR(20)
);


CREATE TABLE B
(
B# CHAR(4) NOT NULL PRIMARY KEY,
Bname VARCHAR(20),
Price numeric(5,2),
Pub VARCHAR(20)
);


CREATE TABLE OD
(
R# CHAR(4) NOT NULL,
B# CHAR(4) NOT NULL,
Qty SMALLINT,
PRIMARY KEY(R#,B#),
FOREIGN KEY (R#) REFERENCES R(R#),
FOREIGN KEY (B#) REFERENCES B(B#)
);

INSERT INTO R VALUES('R1','陈冰','学生','北京');
INSERT INTO R VALUES('R2','周渔采','教授','上海');
INSERT INTO R VALUES('R3','王丽萍','博士','南京');
INSERT INTO R VALUES('R4','李勇','学生','上海');
 
INSERT INTO B VALUES('B1','现代计算机',15,'教育出版社');
INSERT INTO B VALUES('B2','Pascal',17,'清华出版社');
INSERT INTO B VALUES('B3','科学发现',10,'商业出版社');
INSERT INTO B VALUES('B4','离散',9.8,'人民出版社');
INSERT INTO B VALUES('B5','数据结构',12.6,'商业出版社');
 
INSERT INTO OD VALUES('R1','B1',2);
INSERT INTO OD VALUES('R1','B2',2);
INSERT INTO OD VALUES('R2','B1',3);
INSERT INTO OD VALUES('R2','B2',3);
INSERT INTO OD VALUES('R3','B3',1);
INSERT INTO OD VALUES('R4','B2',2);
INSERT INTO OD VALUES('R4','B5',1);




1)	找出读者所在城市是上海的身份是教授的读者，或所在城市名包含“京”字的身份为学生的读者的读者号及身份，按读者号的降序排列
select R#,STATUS
from R
where CITY='上海' and STATUS='教授' OR CITY LIKE'_京'
ORDER BY R# DESC;



2)	对每一种有读者订购的图书，找出书号及有读者订购该书的所有城市
select B#,CITY
FROM OD,R
WHERE R.R#=OD.R#;



3)	找出订购了书号为B2的图书的读者姓名及所在城市(用两种方法做：连接、嵌套)
SELECT Rname,CITY
FROM  R
WHERE R# IN
	(SELECT R#
	  FROM OD
	  WHERE B#= 'B2');

-----------
	 
SELECT Rname,CITY
FROM R,OD
WHERE R.R#=OD.R# AND
	  OD.B#='B2';
	  


4)	找出有一个以上读者订购的图书书号和图书名
SELECT B#,Bname
FROM B
WHERE B# IN
			(
				SELECT B#
				FROM OD
				GROUP BY B#
				HAVING COUNT(R#)>1
			);





5)	求至少订购了一本《Pascal》的读者姓名
SELECT Rname
FROM B,R,OD
WHERE Bname='Pascal' AND B.B#=OD.B# AND OD.R#=R.R#;






6)	找出没有订购B1号图书的读者号
SELECT R#
FROM R
WHERE R# NOT IN
			(
				SELECT R#
				FROM OD
				WHERE B# = 'B1' 
			);





7)	找出价格大于等于15.00或者读者R2最近订购的图书的书号及书名
SELECT B#,Bname
FROM B
WHERE Price >= '15.00' OR B# IN 
								(
									SELECT B#
									FROM OD
									WHERE R# = 'R2'
								);





8)	求图书B2的订购数
SELECT	SUM(Qty)
FROM OD
WHERE B# = 'B2';





9)	查询订购的书的数量不确定的，读者编号和书号
SELECT R#,B#
FROM OD
WHERE Qty IS NULL;



10)	查询图书单价不是15,17,10的图书的图书号和图书名及价格
SELECT B#,Bname,Price
FROM B
WHERE Price not in (15,17,10);


11)	查询所有订购了图书的读者的读者号和姓名
SELECT Rname,R.R#
FROM R
WHERE R# IN
			(
			  SELECT R#
			  FROM OD
			);
			

12)	查询单价小于等于10的所有图书被读者订购的数量，输出书名和订购总数量
SELECT Bname,SUM(Qty)  as '订购总数量'
FROM B,OD
WHERE Price <= 10 AND B.B# = OD.B#
GROUP BY OD.B#,Bname;


13)	查询订购了至少两种书的读者姓名和书的种类数（用分组和自身连接两种方法实现）
SELECT Rname,COUNT(B#)  AS '种类数'
FROM R,OD
WHERE R.R#=OD.R#
GROUP BY R.R#,Rname
HAVING COUNT(OD.R#)>=2;


14)	查询订购了全部书籍的读者的姓名
select Rname
from R
where R# IN 
			(
				select R# 
				from OD
				group by R#
				having COUNT(B#)=5
			);
			
--------------------------------------
SELECT Rname
FROM R
WHERE NOT EXISTS
				(
					SELECT *
					FROM B
					WHERE NOT EXISTS
					(
						SELECT *
						FROM OD
						WHERE OD.R#=R.R# AND OD.B#=B.B#
					)
				);
				
				

15)	查询至少订购了r1所订购的书籍的读者的编号
SELECT DISTINCT R#
FROM OD ODX
where NOT EXISTS
	(
	  SELECT *
	  FROM OD ODY
	  WHERE ODY.R#='R1' AND not exists
	   ( 
			SELECT * 
			FROM OD ODZ
			WHERE ODZ.R# = ODX.R# and ODZ.B# = ODY.B#	     
	   )
	);
	



16)	将所有上海读者订购的图书数量改为一本
updata OD
SET Qty=1
where R# IN
			(
				select R#
				from R
				where CITY='上海'
			);



17)	删去上海的所有读者的订书单
delete
from OD
where R# IN
			(
				select R#
				from R
				where CITY='上海'
			);