谁能介绍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)