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) å¸æè¿äºå¯¹ä½ æ帮å©ï¼è¿æ¯å¤è¡¨æ¥è¯¢ç»å¸¸ç¨ç示ä¾ï¼å¦ææä»ä¹ä¸æï¼å¯ä»¥è®¨è®ºï¼æè
ä½ ååå
·ä½æ³è§£å³ä»ä¹æ ·çé®é¢ï¼
温馨提示:答案为网友推荐,仅供参考