-------------------------------------------------------------------------
--嵌套子查询
-------------------------------------------------------------------------
--查询出版了popular_comp(流行计算机)的书的作者编号和姓名
--嵌套子查询
--联接查询
-------------------------------------------------------------------------
--相关子查询
-------------------------------------------------------------------------
--查找获得书的 100 % 共享版税 (royaltyper) 的所有作者名。
-------------------------------------------------------------------------
--自联接的另一种写法
-------------------------------------------------------------------------
--查找所有雇佣日期相同的雇员
--自联接
--子查询
-------------------------------------------------------------------------
--在子查询中使用UPDATE、DELETE 和 INSERT 语句
-------------------------------------------------------------------------
--把所有New Moon Books出版社出版的书的价格改为原价的2倍
-------------------------------------------------------------------------
--在子查询中使用 ANY 或 ALL
-------------------------------------------------------------------------
--查找价格大于所有商业类图书价格的图书,列出其名称和价格
--查询其他类型的书中比商业类的某一本图书价格还低的书名和价格
-------------------------------------------------------------------------
--使用 EXISTS 和 NOT EXISTS
-------------------------------------------------------------------------
--查询没有著书的作者编号和姓名。
*********************************
答案:
-------------------------------------------------------------------------
--嵌套子查询
-------------------------------------------------------------------------
--查询出版了popular_comp(流行计算机)的书的作者编号和姓名
--嵌套子查询
use pubs
go
select au_id,au_lname, au_fname
from authors
where au_id in
(select au_id
from titleauthor
where title_id in
(select title_id
from titles
where type = 'popular_comp'))
--联接查询
select a.au_id,au_lname,au_fname
from authors a,titleauthor b,titles c
where a.au_id = b.au_id
and b.title_id = c.title_id
and type = 'popular_comp'
-------------------------------------------------------------------------
--相关子查询
-------------------------------------------------------------------------
--查找获得书的 100 % 共享版税 (royaltyper) 的所有作者名。
select au_fname,au_lname
from authors
where 100 in
(select royaltyper from titleauthor
where titleauthor.au_id = authors.au_id)
select distinct au_fname,au_lname
from titleauthor inner join authors
on titleauthor.au_id = authors.au_id
where royaltyper = 100
-------------------------------------------------------------------------
--自联接的另一种写法
-------------------------------------------------------------------------
--查找所有雇佣日期相同的雇员
--自联接
use northwind
go
select * from employees a inner join employees b
on a.hiredate = b.hiredate
and a.employeeid <> b.employeeid
--子查询
select * from employees a
where hiredate in
(select hiredate from employees b
where a.employeeid <> b.employeeid
)
-------------------------------------------------------------------------
--在子查询中使用update、delete 和 insert 语句
-------------------------------------------------------------------------
--把所有new moon books出版社出版的书的价格改为原价的2倍
use pubs
go
select price from titles
where pub_id in
(select pub_id
from publishers
where pub_name = 'new moon books')
update titles
set price = price * 2
where pub_id in
(select pub_id
from publishers
where pub_name = 'new moon books')
-------------------------------------------------------------------------
--在子查询中使用 any 或 all
-------------------------------------------------------------------------
--查找价格大于所有商业类图书价格的图书,列出其名称和价格
select title,price from titles
where price > all
(select price from titles
where type = 'business')
select title,price from titles
where price >
(select max(price) from titles
where type = 'business')
-------------------------------------------------------------------------
--使用 exists 和 not exists
-------------------------------------------------------------------------
--查询没有著书的作者编号和姓名。
select au_id,au_fname,au_lname
from authors
where not exists
(select au_id from titleauthor
where titleauthor.au_id = authors.au_id)
select au_id,au_fname,au_lname
from authors
where au_id not in
(select au_id from titleauthor)