--函數及多表查詢
--=====================================================================================================
--在Pubs數據庫中，完成以下查詢
--1、使用內聯接查詢出authors和publishers表中位於同壹個城市的作者和出版社信息
use pubs
go
select au_id,au_lname,au_fname,phone as au_phone,address as au_address,
authors.city,authors.state,authors.zip as au_zip,pub_name,country
from authors
inner join publishers
on authors.city=publishers.city
--2、查詢出作者號以1~5開頭的所有作者，並使用右外聯接在查詢的結果集中
--列出和作者在同壹個城市的出版社名
select au_lname,au_fname,b.pub_name from authors a
right outer join
publishers b
on a.city=b.city
where au_id like '[1-5]%'
--3、使用自聯接查找居住在 Oakland 相同郵碼區域中的作者。
select distinct a.au_lname,a.au_fname
from authors a
inner join
authors b
on a.zip=b.zip
and a.au_id<>b.au_id
where a.city='Oakland'
--學習手冊P26
--1.略
--2、（1）需要得到年齡在35到40歲之間的外部候選人的信息
use Recruitment
go
select * from 外部侯選人
where datediff(yy,出生日期,getdate()) between 35 and 40
--(2)需要在當前日期之後的10天在報紙上登載壹則廣告，
--系統需要計算出日期，按以下格式顯示
-- | Today | 10 Days From Today |
-- |----------|--------------------|
-- | | |
-- |----------|--------------------|
select getdate() as Today,dateadd(dd,10,getdate()) as [10 Days From Today]
--(3)統計外部候選人接受測試和面試日期的間隔的時間平均值
select avg(datediff(dd,測試日期,面試日期)) as 時間間隔平均時間
from 外部侯選人
--(4)需要獲取外部候選人的姓名和他們申請的職位名
select a.侯選人名字 as 候選人姓名,b.職位描述 as 職位名
from 外部侯選人 a
inner join 職位 b
on a.職位號=b.職位號
--(5)需要獲得在2001年應聘的外部候選人的名字，及推薦他們的招聘機構名
select a.侯選人名字 as 外部候選人名字,b.名字 as 推薦招聘機構名
from 外部侯選人 a
inner join 招聘公司 b
on a.招聘公司號=b.招聘公司代號
where datepart(yyyy,應聘時間)=2001
--(6)需要獲取外部候選人的姓名、及他們的參照的照片的廣告所屬的報紙名
select a.侯選人名字,c.報紙名
from 外部侯選人 a
inner join 廣告 b
on a.廣告號=b.廣告號
inner join 報紙 c
on b.報紙號=c.報紙代碼
--(7)需要獲取大學名、報紙名稱以及他們地址的列表
select 大學名字 as 名字,大學地址 as 地址 from 大學
union
select 報紙名,地址 from 報紙
--P27上機作業
--(1)按以下格式顯示所有運貨的報表（運送天數=實際到達日期-運貨日期）
-- | 定單號 | 運貨日期 | 實際到達日期 | 運送天數 |
-- |----------|---------|---------－---|---------|
-- | | | | |
-- |----------|---------|--------------|---------|
use GlobalToyz
go
select 定單號,運送日期 as 運貨日期,
實際到達日期,datediff(dd,運送日期,實際到達日期) as 運送天數
from 運輸情況
--(2)按以下格式顯示所有的訂單
-- | 定單號 | 購物者號 | 訂單日期（號）| 星期幾 |
-- |----------|---------|---------－---|---------|
-- | | | | |
-- |----------|---------|--------------|---------|
select 定單號,購物者號,定單日期 as [日期（號）],
datepart(dw,定單日期) as 星期幾
from 定單
--(3)顯示所有玩具名和所屬的種類名
select 玩具名,類別名
from 玩具 a
inner join 類別 b
on a.類別號=b.類別號
select 玩具名,類別名 from 玩具 a,類別 b where a.類別號=b.類別號
--(4)按以下格式顯示所有玩具的名稱、商標和種類
-- | 玩具名 | 商標名 | 類別名 |
-- |----------|---------|--------|
-- | | | |
-- |----------|---------|--------|
select 玩具名,商標名,類別名
from 玩具 a
inner join 類別 b
on a.類別號=b.類別號
inner join 商標 c
on a.商標=c.商標號
select 玩具名,商標名,類別名 from 玩具 a,類別 b,商標 c
where a.類別號=b.類別號 and a.商標=c.商標號
--(5)格式顯示玩具的定貨號、玩具ID和玩具使用的禮品包裝說明
-- | 定單號 | 玩具號 | 包裝信息 |
-- |----------|---------|---------|
-- | | | |
-- |----------|---------|---------|
select 定單號,玩具號,信息 as 包裝信息
from 定單詳情
--(6)顯示所有購物者名，及他們所購買的訂單信息（無論購物者是否有訂單）
-- | 購物者名 | 定單號 | 定單時間 | 定單金額|
-- |----------|---------|---------|---------|
-- | | | | |
-- |----------|---------|---------|---------|
select 名 as 購物者名,定單號,定單日期 as 定單時間,總價格 as 定單金額
from 購物者 a
left outer join 定單 b
on a.購物者號=b.購物者號
--(7)以下面的格式顯示定單號碼、定單日期和每個定單所在的季節
-- | 定單號 | 定單日期 | 季節 |
-- |----------|---------|---------|
-- | | | |
-- |----------|---------|---------|
select 定單號,定單日期,datepart(qq,定單日期) as 季節
from 定單
--(8)顯示所有購物者ID、名字、電話和相應定單的接受者
-- | 購物者號 | 名字 | 電話 | 接受者名 | 電話 |
-- |----------|---------|---------|---------|------|
-- | | | | | |
-- |----------|---------|---------|---------|------|
select a.購物者號,a.名 as 名字,a.電話,c.名 as 接受者名,c.電話
from 購物者 a
inner join 定單 b
on a.購物者號=b.購物者號
inner join 接受者 c
on b.定單號=c.定單號
--(9)顯示所有購物者和接受者的名字、地址
-- | 名字 | 地址 |
-- |----------|---------|
-- | | |
-- |----------|---------|
select 名 as 名字,地址 from 接受者
union
select 名,地址 from 購物者
--(10)顯示所有玩具名及該玩具的銷售數量
select 玩具名,sum(銷售數量) as 總銷售數量
from 玩具 a
left outer join 月銷售情況 b
on a.玩具號=b.玩具號
group by 玩具名
--(11)顯示在2001年5月消費金額最高的前3名購物者名，及消費金額
select top 3 名 as 購物者姓名,sum(總價格) as 消費金額
from 購物者 a
inner join 定單 b
on a.購物者號=b.購物者號
where 定單日期 between '2001-05-01' and '2001-05-31 23:59:59'
group by 名
order by sum(總價格) desc
--=======================================================================
--子查詢
--=======================================================================
--P31學習手冊上機試驗
--(1)列出外部候選人“陳曉曉”所在城市的招聘公司
use Recruitment
go
select * from 招聘公司
where 城市 in
(select 城市 from 外部候選人
where 候選人名字='陳曉曉')
--(2)列出擁有“網絡能力”的候選人名字
select 候選人名字 from 外部候選人
where 候選人代號 in
(
select 候選人代號 from 候選人技能
where 技能號 in
(
select 技能號 from 技能
where 技能描述='網絡能力'
)
)
--(3)列出沒有推薦過候選人的招聘公司
select * from 招聘公司
where 招聘公司代號
not in
(select 招聘公司號 from 外部候選人
where 招聘公司號 is not null)
--(4)列出測試成績在所有外部候選人平均分以上的外部候選人信息
Select * from 外部候選人
Where 測試成績>
(Select avg(測試成績) from 外部候選人)
--(5)列出測試成績在各“職位”申請人的平均分以上的外部候選人信息
select * from 外部候選人 as a,
(select 職位號,avg(測試成績) as 平均成績
from 外部候選人
group by 職位號) as b
where a.職位號=b.職位號 and 測試成績>平均成績
--(6)列出各員工的名字及擁有的技能數
select 員工姓名,擁有的技能數
from 員工 a,(select 員工號,count(技能號) as 擁有的技能數 from 員工技能 group by 員工號) b
where a.員工號=b.員工號
--(7)求得銷售部的所有員工2001年的工資總額
select sum(月工資) as 工資總額 from 工資
where datepart(yy,支付日期)=2001 and 員工號 in
(select 員工號 from 員工
where 部門號 =
(select 部門號 from 部門
where 部門名='銷售部')
)
--上機作業
--(1)查詢購買了“捕鯨”玩具的訂單
use GlobalToyz
go
select * from 定單詳情 where 玩具號 in
(select 玩具號 from 玩具 where 玩具名='捕鯨')
--(2)查詢價格低於所有玩具平均價格的玩具
select * from 玩具
where 價格<
(select avg(價格) from 玩具)
--(3)查詢價格高於同類玩具平均價格的玩具
select * from 玩具 a
where 價格>
(select avg(價格) from 玩具 b
where a.類別號=b.類別號 group by 類別號)
--(4)查詢沒有被售出過的玩具信息（用兩種方法實現）
select * from 玩具
where 玩具號 not in
(select 玩具號 from 月銷售情況)
select * from 玩具 a
where not exists
(
select * from 月銷售情況 b
where a.玩具號=b.玩具號
)
--(5)查詢售價最高和最低的玩具名
-- | 價格最高 | 價格最低 |
-- |-----------|---------|
-- | | |
-- |-----------|---------|
select (select 玩具名 from 玩具 where 價格=(select max(價格) from 玩具)) as 價格最高,
(select 玩具名 from 玩具 where 價格=(select min(價格) from 玩具)) as 價格最低
--(6)查詢“拉爾森”這個顧客所購買的各訂單的接受者分別是誰
select * from 接受者 where 定單號 in
(select 定單號 from 定單 where 購物者號=
(select 購物者號 from 購物者 where 名='拉爾森'))
--(7)查詢各玩具的類別中，玩具種類在3以上的玩具類別信息
select * from 類別 where 類別號 in
(select 類別號 from 玩具 group by 類別號 having count(玩具號)>3)