SQL多表查询及子查询代码示例

SQL多表查询及子查询代码示例
--函数及多表查询
--=====================================================================================================
--在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)