面试中的SQL分析二

最新推荐文章于 2019-11-05 14:56:06 发布

小飞鹤

最新推荐文章于 2019-11-05 14:56:06 发布

阅读量2.4k

点赞数

分类专栏：数据库文章标签： SQL面试题

本文链接：https://blog.csdn.net/he90227/article/details/38385011

版权

本文列举了四个SQL面试中常见的问题，涵盖子查询的使用，包括嵌套和相关子查询，以及多表连接操作，特别是第三方关联表的应用。通过这些实例，帮助求职者准备SQL面试。

摘要由CSDN通过智能技术生成

1，面试题范例一

1.用一条SQL语句 查询出每门课都大于80分的学生姓名 

name   kecheng   fenshu 
张三     语文       81
张三     数学       75
李四     语文       76
李四     数学       90
王五     语文       81
王五     数学       100
王五     英语       90

A: select distinct name from table  where  name not in (select distinct name from table where fenshu<=80)

2.学生表 如下:
自动编号   学号   姓名 课程编号 课程名称 分数
1        2005001  张三  0001      数学    69
2        2005002  李四  0001      数学    89
3        2005001  张三  0001      数学    69
删除除了自动编号不同,其他都相同的学生冗余信息

A: delete tablename where 自动编号 not in(select min(自动编号) from tablename group by 学号,姓名,课程编号,课程名称,分数)

一个叫department的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合.
你先按你自己的想法做一下，看结果有我的这个简单吗？
答：select a.name, b.name 
from team a, team b 
where a.name < b.name
 
请用SQL语句实现：从TestDB数据表中查询出所有月份的发生额都比101科目相应月份的发生额高的科目。请注意：TestDB中有很多科目，都有1－12月份的发生额。
AccID：科目代码，Occmonth：发生额月份，DebitOccur：发生额。
数据库名：JcyAudit，数据集：Select * from TestDB
答：select a.*
from TestDB a 
,(select Occmonth,max(DebitOccur) Debit101ccur from TestDB where AccID='101' group by Occmonth) b
where a.Occmonth=b.Occmonth and a.DebitOccur>b.Debit101ccur
************************************************************************************
面试题：怎么把这样一个表儿
year  month amount
1991   1     1.1
1991   2     1.2
1991   3     1.3
1991   4     1.4
1992   1     2.1
1992   2     2.2
1992   3     2.3
1992   4     2.4
查成这样一个结果
year m1  m2  m3  m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4 

答案一、
select year, 
(select amount from  aaa m where month=1  and m.year=aaa.year) as m1,
(select amount from  aaa m where month=2  and m.year=aaa.year) as m2,
(select amount from  aaa m where month=3  and m.year=aaa.year) as m3,
(select amount from  aaa m where month=4  and m.year=aaa.year) as m4
from aaa  group by year
 
这个是ORACLE  中做的：
select * from (select name, year b1, lead(year) over
(partition by name order by year) b2, lead(m,2) over(partition by name order by year) b3,rank()over(
partition by name order by year) rk from t) where rk=1;
************************************************************************************
精妙的SQL语句！
精妙SQL语句  
作者：不详 发文时间：2003.05.29 10:55:05 

说明：复制表(只复制结构,源表名：a 新表名：b) 

SQL: select * into b from a where 1<>1 

说明：拷贝表(拷贝数据,源表名：a 目标表名：b) 

SQL: insert into b(a, b, c) select d,e,f from b; 

说明：显示文章、提交人和最后回复时间 

SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 

说明：外连接查询(表名1：a 表名2：b) 

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 

说明：日程安排提前五分钟提醒 

SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 

说明：两张关联表，删除主表中已经在副表中没有的信息 

SQL: 

delete from info where not exists ( select * from infobz where info.infid=infobz.infid ) 

说明：-- 

SQL: 

SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE 

FROM TABLE1, 

(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE 

FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND 

FROM TABLE2 

WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X, 

(SELECT NUM, UPD_DATE, STOCK_ONHAND 

FROM TABLE2 

WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = 

TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') ¦¦ '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y, 

WHERE X.NUM = Y.NUM （+） 

AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B 

WHERE A.NUM = B.NUM 

说明：-- 

SQL: 

select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='"&strdepartmentname&"' and 专业名称='"&strprofessionname&"' order by 性别,生源地,高考总成绩 

说明： 

从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源） 

SQL: 

SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear, 

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN, 

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI, 

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR, 

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR, 

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY, 

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE, 

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL, 

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU, 

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP, 

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT, 

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV, 

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC 

FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration 

FROM TELFEESTAND a, TELFEE b 

WHERE a.tel = b.telfax) a 

GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') 

说明：四表联查问题： 

SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ..... 

说明：得到表中最小的未使用的ID号 

SQL: 

SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID 

FROM Handle 

WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)
 
*******************************************************************************
有两个表A和B，均有key和value两个字段，如果B的key在A中也有，就把B的value换为A中对应的value
这道题的SQL语句怎么写？
update   b   set   b.value=(select   a.value   from   a   where   a.key=b.key)   where   b.id   in(select   b.id   from   b,a   where   b.key=a.key);
***************************************************************************
高级sql面试题
原表: 
courseid coursename score 
------------------------------------- 
1 java 70 
2 oracle 90 
3 xml 40 
4 jsp 30 
5 servlet 80 
------------------------------------- 
为了便于阅读,查询此表后的结果显式如下(及格分数为60): 
courseid coursename score mark 
--------------------------------------------------- 
1 java 70 pass 
2 oracle 90 pass 
3 xml 40 fail 
4 jsp 30 fail 
5 servlet 80 pass 
--------------------------------------------------- 
写出此查询语句
没有装ＯＲＡＣＬＥ，没试过 
select courseid, coursename ,score ,decode（sign(score-60),-1,'fail','pass') as mark from course
完全正确 

SQL> desc course_v 
Name Null? Type 
----------------------------------------- -------- ---------------------------- 
COURSEID NUMBER 
COURSENAME VARCHAR2(10) 
SCORE NUMBER 

SQL> select * from course_v; 

COURSEID COURSENAME SCORE 
---------- ---------- ---------- 
1 java 70 
2 oracle 90 
3 xml 40 
4 jsp 30 
5 servlet 80 

SQL> select courseid, coursename ,score ,decode(sign(score-60),-1,'fail','pass') as mark from course_v; 

COURSEID COURSENAME SCORE MARK 
---------- ---------- ---------- ---- 
1 java 70 pass 
2 oracle 90 pass 
3 xml 40 fail 
4 jsp 30 fail 
5 servlet 80 pass
*******************************************************************************
原表: 

id proid proname 
1 1 M 
1 2 F 
2 1 N 
2 2 G 
3 1 B 
3 2 A 
查询后的表: 

id pro1 pro2 
1 M F 
2 N G 
3 B A 
写出查询语句
解决方案 

sql求解 
表a 
列 a1 a2 
记录 1 a 
1 b 
2 x 
2 y 
2 z 
用select能选成以下结果吗？ 
1 ab 
2 xyz 
使用pl/sql代码实现，但要求你组合后的长度不能超出oracle varchar2长度的限制。 
下面是一个例子 
create or replace type strings_table is table of varchar2(20); 
/ 
create or replace function merge (pv in strings_table) return varchar2 
is 
ls varchar2(4000); 
begin 
for i in 1..pv.count loop 
ls := ls || pv(i); 
end loop; 
return ls; 
end; 
/ 
create table t (id number,name varchar2(10)); 
insert into t values(1,'Joan'); 
insert into t values(1,'Jack'); 
insert into t values(1,'Tom'); 
insert into t values(2,'Rose'); 
insert into t values(2,'Jenny'); 

column names format a80; 
select t0.id,merge(cast(multiset(select name from t where t.id = t0.id) as strings_table)) names 
from (select distinct id from t) t0; 

drop type strings_table; 
drop function merge; 
drop table t; 




用sql： 

Well if you have a thoretical maximum, which I would assume you would given the legibility of listing hundreds of employees in the way you describe then yes. But the SQL needs to use the LAG function for each employee, hence a hundred emps a hundred LAGs, so kind of bulky. 

This example uses a max of 6, and would need more cut n pasting to do more than that. 

SQL> select deptno, dname, emps 
2 from ( 
3 select d.deptno, d.dname, rtrim(e.ename ||', '|| 
4 lead(e.ename,1) over (partition by d.deptno 
5 order by e.ename) ||', '|| 
6 lead(e.ename,2) over (partition by d.deptno 
7 order by e.ename) ||', '|| 
8 lead(e.ename,3) over (partition by d.deptno 
9 order by e.ename) ||', '|| 
10 lead(e.ename,4) over (partition by d.deptno 
11 order by e.ename) ||', '|| 
12 lead(e.ename,5) over (partition by d.deptno 
13 order by e.ename),', ') emps, 
14 row_number () over (partition by d.deptno 
15 order by e.ename) x 
16 from emp e, dept d 
17 where d.deptno = e.deptno 
18 ) 
19 where x = 1 
20 / 

DEPTNO DNAME EMPS 
------- ----------- ------------------------------------------ 
10 ACCOUNTING CLARK, KING, MILLER 
20 RESEARCH ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH 
30 SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD 

also 
先create function get_a2; 
create or replace function get_a2( tmp_a1 number) 
return varchar2 
is 
Col_a2 varchar2(4000); 
begin 
Col_a2:=''; 
for cur in (select a2 from unite_a where a1=tmp_a1) 
loop 
Col_a2=Col_a2||cur.a2; 
end loop; 
return Col_a2; 
end get_a2; 

select distinct a1 ,get_a2(a1) from unite_a 
1 ABC 
2 EFG 
3 KMN
*******************************************************************************
一个SQL 面试题
去年应聘一个职位未果,其间被考了一个看似简单的题,但我没有找到好的大案.
不知各位大虾有无好的解法?


题为:
有两个表, t1, t2,
Table t1:

SELLER | NON_SELLER
----- -----

A B
A C
A D
B A
B C
B D
C A
C B
C D
D A
D B
D C


Table t2:

SELLER | COUPON | BAL
----- --------- ---------
A 9 100
B 9 200
C 9 300
D 9 400
A 9.5 100
B 9.5 20
A 10 80



要求用SELECT 语句列出如下结果:------如A的SUM(BAL)为B,C,D的和,B的SUM(BAL)为A,C,D的和.......
且用的方法不要增加数据库负担,如用临时表等.

NON-SELLER| COUPON | SUM(BAL) ------- --------
A 9 900
B 9 800
C 9 700
D 9 600 
A 9.5 20
B 9.5 100
C 9.5 120
D 9.5 120
A 10 0
B 10 80
C 10 80
D 10 80
关于论坛上那个SQL微软面试题
问题：

一百个账户各有100$，某个账户某天如有支出则添加一条新记录，记录其余额。一百天后，请输出每天所有账户的余额信息
 

这个问题的难点在于每个用户在某天可能有多条纪录，也可能一条纪录也没有（不包括第一天）

返回的记录集是一个100天*100个用户的纪录集

下面是我的思路：

1.创建表并插入测试数据：我们要求username从1-100
CREATE TABLE [dbo].[TABLE2] (
[username] [varchar] (50) NOT NULL , --用户名
[outdate] [datetime] NOT NULL , --日期
[cash] [float] NOT NULL --余额
) ON [PRIMARY

declare @i int
set @i=1
while @i<=100
  begin
    insert table2 values(convert(varchar(50),@i),'2001-10-1',100)
    insert table2 values(convert(varchar(50),@i),'2001-11-1',50)
    set @i=@i+1
  end
insert table2 values(convert(varchar(50),@i),'2001-10-1',90)

select * from table2 order by outdate,convert(int,username)

2.组合查询语句：
a.我们必须返回一个从第一天开始到100天的纪录集：
如：2001-10-1（这个日期是任意的）到 2002-1-8
由于第一天是任意一天，所以我们需要下面的SQL语句：
select top 100 dateadd(d,convert(int,username)-1,min(outdate)) as outdate
from table2
group by username
order by convert(int,username)
这里的奥妙在于：
convert(int,username)-1（记得我们指定用户名从1-100 :-))
group by username,min(outdate):第一天就可能每个用户有多个纪录。
返回的结果：
outdate                                                
------------------------------------------------------ 
2001-10-01 00:00:00.000
.........
2002-01-08 00:00:00.000

b.返回一个所有用户名的纪录集：
select distinct username from table2 
返回结果：
username                                          
-------------------------------------------------- 
1
10
100
......
99

c.返回一个100天记录集和100个用户记录集的笛卡尔集合：
select * from
(
select top 100 dateadd(d,convert(int,username)-1,min(outdate)) as outdate
from table2
group by username
order by convert(int,username)
) as A
CROSS join 
(
select distinct username from table2 
) as B
order by outdate,convert(int,username)
返回结果100*100条纪录：
outdate                            username
2001-10-01 00:00:00.000            1
......
2002-01-08 00:00:00.000            100

d.返回当前所有用户在数据库的有的纪录：
select outdate,username,min(cash) as cash from table2
group by outdate,username

order by outdate,convert(int,username)
返回纪录：
outdate                            username    cash
2001-10-01 00:00:00.000            1          90
......
2002-01-08 00:00:00.000            100        50

e.将c中返回的笛卡尔集和d中返回的纪录做left join:
select C.outdate,C.username,
D.cash
from
(
select * from
(
select top 100 dateadd(d,convert(int,username)-1,min(outdate)) as outdate
from table2
group by username
order by convert(int,username)
) as A
CROSS join 
(
select distinct username from table2 
) as B
) as C
left join
(
select outdate,username,min(cash) as cash from table2
group by outdate,username
) as D
on(C.username=D.username and datediff(d,C.outdate,D.outdate)=0)

order by C.outdate,convert(int,C.username)
注意：用户在当天如果没有纪录，cash字段返回NULL，否则cash返回每个用户当天的余额
outdate                            username    cash
2001-10-01 00:00:00.000            1          90
2001-10-01 00:00:00.000            2          100
......
2001-10-02 00:00:00.000            1          90
2001-10-02 00:00:00.000            2          NULL  <--注意这里
......

2002-01-08 00:00:00.000            100        50

f.好了，现在我们最后要做的就是，如果cash为NULL，我们要返回小于当前纪录日期的第一个用户余额(由于我们使用order by cash,所以返回top 1纪录即可，使用min应该也可以)，这个余额即为当前的余额：
case isnull(D.cash,0)
when 0 then 
(
select top 1 cash from table2 where table2.username=C.username
and datediff(d,C.outdate,table2.outdate)<0 
order by table2.cash
)
else D.cash
end as cash

g.最后组合的完整语句就是
select C.outdate,C.username,
case isnull(D.cash,0)
when 0 then 
(
select top 1 cash from table2 where table2.username=C.username
and datediff(d,C.outdate,table2.outdate)<0 
order by table2.cash
)
else D.cash
end as cash
from
(
select * from
(
select top 100 dateadd(d,convert(int,username)-1,min(outdate)) as outdate
from table2
group by username
order by convert(int,username)
) as A
CROSS join 
(
select distinct username from table2 
) as B
) as C
left join
(
select outdate,username,min(cash) as cash from table2
group by outdate,username
) as D
on(C.username=D.username and datediff(d,C.outdate,D.outdate)=0)

order by C.outdate,convert(int,C.username)

返回结果：
outdate                                 username        cash
2001-10-01 00:00:00.000    1                    90
2001-10-01 00:00:00.000    2                   100
......
2002-01-08 00:00:00.000    100                50
***********************************************************************************
取出sql表中第31到40的记录（以自动增长ID为主键）
*从数据表中取出第n条到第m条的记录*/ 

declare @m int 
declare @n int 
declare @sql varchar(800) 
set @m=40 
set @n=31 
set @sql='select top '+str(@m-@n+1) + '* from idetail where autoid not in( 
select top '+ str(@n-1) + 'autoid from idetail)' 
exec(@sql)
 
 
select top 10 * from t where id not in (select top 30 id from t order by id ) orde by id
--------------------------------------------------------------------------------
select top 10 * from t where id in (select top 40 id from t order by id) order by id desc
 
*******************************************************************************
一道面试题,写sql语句

有表a存储二叉树的节点,要用一条sql语句查出所有节点及节点所在的层.
表a 
c1 c2 A ----------1 
---- ---- / /
A B B C --------2
A C / / /
B D D N E ------3
C E / / /
D F F K I ---4
E I
D K
C N


所要得到的结果如下

jd cs
----- ----
A 1
B 2
C 2
D 3
N 3
E 3
F 4
K 4
I 4
有高手指导一下,我只能用pl/sql写出来,请教用一条sql语句的写法
SQL> select c2, level + 1 lv
2 from test start
3 with c1 = 'A'
4 connect by c1 = prior c2
5 union
6 select 'A', 1 from dual
7 order by lv;

C2 LV
-- ----------
A 1
B 2
C 2
D 3
E 3
N 3
F 4
I 4
K 4

已选择9行。

2，面试题范例二

多表连接和子查询

--================================
-- ylb:表的高级查询-  多表连接和子查询
--    pubs库的练习
-- 17:18 2011/12/13
--================================
use pubs
go
select * from authors
select * from titles
--select * from titleauthor
select * from publishers
--1,查看出版社名称，书名称
go
--2,查看出版社名称,出版社出书预付款总额
select pub_name,SUM(advance) '预付款总额' from publishers p
inner join titles t on p.pub_id=t.pub_id
group by pub_name
go
--3,查看出版社名称，出版社出书预付款最大值，单价最小值
select pub_name,max(advance) '付款最大值',MIN(price) '单价最小值' from publishers p
inner join titles t on p.pub_id=t.pub_id
group by pub_name
go
--4,查看出版社编号，出版社名称，书名称，书单价，作者编号
select * from publishers p
inner join titles t on p.pub_id=t.pub_id
inner join titleauthor ta on t.title_id=ta.title_id
go
--5,查看出版社编号，书总价，书最高价格，作者总数
---5分析
--这本书(TC7777)有两个作者（472-27-2349,672-71-3249）
--这个作者（486-29-1786） 出了两本书（PC9999,PS7777）

--5_1,错的
select p.pub_id,SUM(price),MAX(price),count(title_id) from publishers p
inner join titles t on p.pub_id=t.pub_id
group by p.pub_id
go
--5_2,正确的
select p.pub_id,SUM(price),MAX(price),count(distinct au_id) from publishers p
inner join titles t on p.pub_id=t.pub_id
inner join titleauthor ta on t.title_id=ta.title_id
group by p.pub_id
go

--6,查看出版社编号，书总价，书最高价格，要求作者总数大于9的信息
select pub_id,SUM(price),MAX(price) from titles t 
inner join titleauthor ta on t.title_id=ta.title_id
group by pub_id
having count(distinct au_id)>9
go

--7,查找书名称，书编号，作者编号
 
 go
--8,查找书名称，作者数量
select title,COUNT(au_id) '作者数量' from titles t
inner join titleauthor ta on t.title_id=ta.title_id
group by title
go
--9,查找商业书的所有书名称，作者数量, 
select title,COUNT(au_id) '作者数量' from titles t
inner join titleauthor ta on t.title_id=ta.title_id
where type='business'
group by title
go
--10,查找商业书的所有作者姓名，作者编号，作者城市
select * from titles t
inner join titleauthor ta on t.title_id=ta.title_id
inner join authors a on ta.au_id=a.au_id
where type='business'
go
--11,在CA州的作者出的书，书名，书单价总和，
--书最高预付款，书的最低价格。
select title,SUM(price),MAX(advance),MIN(price) from titles t
inner join titleauthor ta on t.title_id=ta.title_id
inner join authors a on ta.au_id=a.au_id
where state='CA'
group by title
go
--12,和商店的同一州作者出的书，书名，书单价总和，
--书最高预付款，书的最低价格。
--12_1,
select au_id from authors a
where state in(select state from stores where state=a.state)
go
--12_2,
select distinct title_id from titleauthor ta
where au_id in(select au_id from authors a
where state in(select state from stores where state=a.state))
go
--12_3,
select * from titles t
where title_id in(select distinct title_id from titleauthor ta
where au_id in(select au_id from authors a
where state in(select state from stores where state=a.state)))
go
--12_4,结论
select title,SUM(price),MAX(advance),MIN(price) from titles t
where title_id in(select distinct title_id from titleauthor ta
where au_id in(select au_id from authors a
where state in(select state from stores where state=a.state)))
group by title
go
--13,和商店的同一州作者出的书，书名，书单价总和，
--书最高预付款，书的最低价格,要
--求书必须大于所有商业书的价格。
--13_1,
select * from titles 
where price>(select max(price) from titles where type='business')
go
--13_2,总结
select title,SUM(price),MAX(advance),MIN(price) from titles t
where title_id in(select distinct title_id from titleauthor ta
where au_id in(select au_id from authors a
where state in(select state from stores where state=a.state)))
and price>(select max(price) from titles where type='business')
group by title
go
--SQL脚本总结
--1，如果有条件“先做条件”
--2，多列先链表，再做条件
--3, 先做条件后排序
--3，分组常和聚合函数在一起。
--4，
--5，子查询，如果有已知条件用嵌套子查询，否则就是相关子查询。

--前（where 列条件）group by 后（having 组条件）

select * from titles
where type='business'
order by title_id desc

子查询

--================================
-- ylb:表的高级查询-子查询
--    pubs库的练习
-- 12/12/2011
--================================
use pubs
go
select * from authors
select * from titleauthor
select * from titles
select * from publishers
select * from stores
go
--1. 查找和出版商同一州的作者姓名。
select * from authors a
where state in(select state from publishers where state=a.state)
go
select * from authors a
where exists(select * from publishers where state=a.state)
go
--2. 查找和商店同一州的作者姓名
select * from authors a
where state in(select state from stores where state=a.state)
go
--3. 查找和商店同一城市的出版社名称
select * from publishers p
where city in (select city from stores where city=p.city)
go
--4. 查找写商业书的作者名
select * from authors
select * from titleauthor
select * from titles
go
--4_1,
select title_id from titles
where type='business'
go
--4_2,
select au_id from titleauthor
where title_id in('BU1032','BU1111','BU2075','BU7832')
go
--4_3,
select * from authors
where au_id in('213-46-8915','267-41-2394')
go
--4,结论
select * from authors
where au_id in(select au_id from titleauthor
where title_id in(select title_id from titles
where type='business'))
go
--5. 查找美国出版社出版的所有书
select * from publishers
select * from titles
go
--5_1,
select pub_id from publishers
where country='USA'
go
--5_2,
select * from titles
where pub_id in('0877','0736')
go
--5结论
select * from titles
where pub_id in(select pub_id from publishers
where country='USA')
go
--6. 查找美国出版社出版书的作者姓名
--6_1,
select pub_id from publishers
where country='USA'
go
--6_2,
select title_id from titles
where pub_id in('0877','0736')
go
--6_3,
select au_id from titleauthor
where title_id in('BU2075','MC2222')
go
--6_4,
select * from authors
where au_id in('213-46-8915','712-45-1867')
go
--6总结
select * from authors
where au_id in(select au_id from titleauthor
where title_id in(select title_id from titles
where pub_id in(select pub_id from publishers
where country='USA')))
go
--7. 查找在CA州出版社所出版的商业书作者姓名
--7-1,
select pub_id from publishers
where state='CA'
go
--7-2,
select title_id from  titles
where pub_id in('1389')
and [type]='business'
go
--7-3,
select au_id from titleauthor
where title_id in('BU1032','BU1111')
go
--7-4,
select * from authors
where au_id in('213-46-8915','409-56-7008')
go
--7总结
select * from authors
where au_id in(select au_id from titleauthor
where title_id in(select title_id from  titles
where pub_id in(select pub_id from publishers
where state='CA')
and [type]='business'))
go
--P:8. 查找和出版社在同一州的作者所写的书名
--8_1,
select au_id from authors a
where state in(select state from publishers where state=a.state)
go
--8-2,
select title_id from titleauthor
where au_id in(select au_id from authors a
where state in(select state from publishers where state=a.state))
go
--8-3,
select * from titles
where title_id in(select title_id from titleauthor)
go

--8 结论
select * from titles
where title_id in(select title_id from titleauthor
where au_id in(select au_id from authors a
where state in(select state from publishers where state=a.state)))
go
--9. 查找和作者在同一城市的出版社名称
select * from publishers p
where city in(select city from authors where city=p.city)
go
--10. 查找单价大于所有商业书的书，它的作者姓名

--方法一、
--10-1,
select MAX(price) from titles where type='business'
go
--10-2a,
select title_id from titles
where price >(select MAX(price) from titles where type='business')
go
--10-2b,
select title_id from titles
where price > all(select price from titles where type='business')
go
--10-3,
select au_id from titleauthor
where title_id in(select title_id from titles
where price >(select MAX(price) from titles where type='business'))
go
--10总结
select * from authors
where au_id in(select au_id from titleauthor
where title_id in(select title_id from titles
where price >(select MAX(price) from titles where type='business')))
go

--11.   查找（Algodata Infosystems）出版社所在州，出过商业书的作者姓名
--11_1,
select pub_id from publishers
where pub_name='Algodata Infosystems'
go
--11-2,
select title_id from titles
where type='business' and pub_id =(select pub_id from publishers
where pub_name='Algodata Infosystems')
go
--11-3,
select au_id from titleauthor
where title_id in(select title_id from titles
where pub_id =(select pub_id from publishers
where pub_name='Algodata Infosystems'))
go
--11-4,
select * from authors
where au_id in(select au_id from titleauthor
where title_id in(select title_id from titles
where type='business' and pub_id =(select pub_id from publishers
where pub_name='Algodata Infosystems')))

SQL Server 子查询（嵌套子查询）和子查询（相关子查询）。

1，ylb：1，子查询（嵌套子查询）返回顶部

 
     
   
-- =============================================
-- ylb：本案例的目的是：“嵌套子查询”
-- 11:25 2011/12/9
-- =============================================
use pubs
go
--一、子查询
--1，嵌套子查询
---特点：in里面的查询语句可以独立运行。
--P1:查询图书名是“Net Etiquette”的作者是谁？
--分析
select title_id from titles where title='Net Etiquette'
--title_id='PC9999'
go
select au_id from titleauthor
where title_id='PC9999'
--au_id='486-29-1786'
go
--小结
select au_id from titleauthor
where title_id in(select title_id from titles where title='Net Etiquette')
go
select * from authors 
where 