1.一個(gè)表中的Id有多個(gè)記錄，把所有這個(gè)id的記錄查出來(lái)，并顯示共有多少條記錄數(shù)。
select ID,count(*) from Student
group by ID having count(*)>1
select * from(select ID,count(ID) as count
from Student group by ID)T
2.有一張表，里面有3個(gè)字段：語(yǔ)文，數(shù)學(xué)，英語(yǔ)。其中有3條記錄分別表示語(yǔ)文70分，數(shù)學(xué)80分，英語(yǔ)58分，請(qǐng)用一條sql語(yǔ)句查詢出這三條記錄并按以下條件顯示出來(lái)（并寫出您的思路）：?
?? 大于或等于80表示優(yōu)秀，大于或等于60表示及格，小于60分表示不及格。?
if object_id('tb') is not null
drop table tb
go
create table tb(yw int,sx int,yy int)
insert into
tb
select 70,80,58
select
(case when yw <60 then '不及格'
???????? when yw >= 80 then
'優(yōu)秀'
???????? else'及格' end) as '語(yǔ)文',
(case when sx <60 then
'不及格'
???????? when sx >= 80 then '優(yōu)秀'
???????? else'及格' end) as
'數(shù)學(xué)',
(case when yy <60 then '不及格'
???????? when yy >= 80 then
'優(yōu)秀'
???????? else'及格' end) as '英語(yǔ)'
from tb
drop table tb
3.請(qǐng)教一個(gè)面試中遇到的SQL語(yǔ)句的查詢問(wèn)題
表中有A B
C三列,用SQL語(yǔ)句實(shí)現(xiàn)：當(dāng)A列大于B列時(shí)選擇A列否則選擇B列，當(dāng)B列大于C列時(shí)選擇B列否則選擇C列。
select (case when a>b
then a else b end),
(case when b>c then b else c end)
from tb
4.為管理崗位業(yè)務(wù)培訓(xùn)信息，建立3個(gè)表:
S (S#,SN,SD,SA) S#,SN,SD,SA
分別代表學(xué)號(hào)、學(xué)員姓名、所屬單位、學(xué)員年齡
C (C#,CN ) C#,CN 分別代表課程編號(hào)、課程名稱
SC ( S#,C#,G ) S#,C#,G 分別代表學(xué)號(hào)、所選修的課程編號(hào)、學(xué)習(xí)成績(jī)
A. 使用標(biāo)準(zhǔn)SQL嵌套語(yǔ)句查詢選修課程名稱為’稅收基礎(chǔ)’的學(xué)員學(xué)號(hào)和姓名
B.
使用標(biāo)準(zhǔn)SQL嵌套語(yǔ)句查詢選修課程編號(hào)為’C2’的學(xué)員姓名和所屬單位
C.
使用標(biāo)準(zhǔn)SQL嵌套語(yǔ)句查詢不選修課程編號(hào)為’C5’的學(xué)員姓名和所屬單位
D. 使用標(biāo)準(zhǔn)SQL嵌套語(yǔ)句查詢選修全部課程的學(xué)員姓名和所屬單位
E.
查詢選修了課程的學(xué)員人數(shù)
F. 查詢選修課程超過(guò)5門的學(xué)員學(xué)號(hào)和所屬單位
A. select S#,SN from S where S# in(select S# from SC,C where SC.C#=C.C# and
CN='稅收基礎(chǔ)')
B. select S.SN,S.SD from S,SC where S.S#=SC.S# and C#=C2
C. SELECT SN,SD FROM S where [S#] NOT IN(SELECT [S#] FROM SC WHERE
[C#]='C5')
? 或者select S.SN,S.SD from S,SC where S.S#=SC.S# and C#!=C5
D. select SN,SD, from S where (select count(S#) from SC group by S#)=(select
count(*) from C group by C#)
E. select count(distinct S#) from SC
F. select S#,SD from S where S# in (SELECT [S#] FROM SC GROUP BY [S#] HAVING
COUNT(DISTINCT [C#])>5)
5.問(wèn)題描述:
S (SNO,SNAME) 學(xué)生關(guān)系。SNO 為學(xué)號(hào)，SNAME 為姓名
C (CNO,CNAME,CTEACHER) 課程關(guān)系。CNO 為課程號(hào)，CNAME 為課程名，CTEACHER 為任課教師
SC(SNO,CNO,SCGRADE) 選課關(guān)系。SCGRADE 為成績(jī)
A. 找出沒(méi)有選修過(guò)“李明”老師講授課程的所有學(xué)生姓名
Select SNAME FROM S Where NOT EXISTS (
Select * FROM SC,C Where SC.CNO=C.CNO AND CNAME='李明' AND SC.SNO=S.SNO)
B. 列出有二門以上(含兩門)不及格課程的學(xué)生姓名及其平均成績(jī)
Select
S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE) FROM? S ,? SC , (Select SNO FROM SC
Where SCGRADE<60 GROUP BY SNO HAVING COUNT(DISTINCT CNO)>=2) A Where
S.SNO=A.SNO AND SC.SNO=A.SNO GROUP BY S.SNO,S.SNAME
C. 列出既學(xué)過(guò)“1”號(hào)課程，又學(xué)過(guò)“2”號(hào)課程的所有學(xué)生姓名
Select S.SNO,S.SNAME FROM S,(Select
SC.SNO? FROM SC,C Where SC.CNO=C.CNO AND C.CNAME IN('1','2') GROUP BY SNO HAVING
COUNT(DISTINCT CNO)=2)SC Where S.SNO=SC.SNO
D.
列出“1”號(hào)課成績(jī)比“2”號(hào)同學(xué)該門課成績(jī)高的所有學(xué)生的學(xué)號(hào)
Select S.SNO,S.SNAME FROM S,
(Select
SC1.SNOFROM SC SC1,C C1,SC SC2,C C2
Where SC1.CNO=C1.CNO AND
C1.NAME='1'
AND SC2.CNO=C2.CNO AND C2.NAME='2'
AND
SC1.SCGRADE>SC2.SCGRADE? )? SC
Where S.SNO=SC.SNO
E. 列出“1”號(hào)課成績(jī)比“2”號(hào)課成績(jī)高的所有學(xué)生的學(xué)號(hào)及其“1”號(hào)課和“2”號(hào)課的成績(jī)
Select
S.SNO,S.SNAME,SC.[1號(hào)課成績(jī)],SC.[2號(hào)課成績(jī)] FROM S,
(Select
SC1.SNO,[1號(hào)課成績(jī)]=SC1.SCGRADE,[2號(hào)課成績(jī)]=SC2.SCGRADE
FROM SC SC1,C C1,SC SC2,C
C2
Where SC1.CNO=C1.CNO AND C1.NAME='1'
AND SC2.CNO=C2.CNO AND
C2.NAME='2'
AND SC1.SCGRADE>SC2.SCGRADE)?? SC
Where S.SNO=SC.SNO
6.
======
有如下表記錄:
ID????????? Name???????? EmailAddress??????????
LastLogon
100???????? test4?????? test4@yahoo.cn?????? 2007-11-25
16:31:26
13????????? test1?????? test1@yahoo.cn?????? 2007-3-22
16:27:07
19????????? test1?????? test1@yahoo.cn?????? 2007-10-25
14:13:46
42????????? test1?????? test1@yahoo.cn?????? 2007-11-20
14:20:10
45????????? test2?????? test2@yahoo.cn?????? 2007-4-25
14:17:39
49????????? test2?????? test2@yahoo.cn?????? 2007-5-25 14:22:36
用一句sql查詢出每個(gè)用戶最近一次登錄的記錄(每個(gè)用戶只顯示一條最近登錄的記錄)
方法一:
SELECT a.* from
users? a? inner join
????????????????????????????? (SELECT [Name],
LastLogon=MAX(LastLogon)??? FROM? users??? GROUP BY [Name])?
b
?????????????????????????????? on a.[Name]=b.[Name] and
a.[LastLogon]=b.[LastLogon]
方法二:
SELECT a.* from users? a?? inner
join
?????????????????????????????? (SELECT? Name,MAX(LogonID) LogonID????
FROM? users??? GROUP BY [Name])? b
???????????????????????????? on
a.LogonID=b.LogonID
???????????????????????????? --where a.LogonId=b.LogonId
7.現(xiàn)在我們假設(shè)只有一個(gè)table，名為pages，有四個(gè)字段，id,
url,title,body。里面儲(chǔ)存了很多網(wǎng)頁(yè)，網(wǎng)頁(yè)的url地址，title和網(wǎng)頁(yè)的內(nèi)容，然后你用一個(gè)sql查詢將url匹配的排在最前，
title匹配的其次，body匹配最后，沒(méi)有任何字段匹配的，不返回。
select a.[id],a.mark from
(
select
[page].[id],100 as mark from [page] where [page].[url] like '%baidu%'
union
select [page].[id],50 as mark from [page] where [page].[title] like
'%baidu%'
union
select [page].[id],10 as mark from [page] where
[page].[body] like '%baidu%'
) as a? order by mark desc?
?
8.查詢某表中的第30到40行的數(shù)據(jù)
select top * 10 from (select top * 40 from table order
by id asc) table_a order by id desc
9.對(duì)于教學(xué)數(shù)據(jù)庫(kù)的三個(gè)基本表
學(xué)生 S(S#,SNAME,AGE,SEX)
學(xué)習(xí) SC(S#,C#,GRADE)
課程 C(C#,CNAME,TEACHER)
試用SQL的查詢語(yǔ)句表達(dá)下列查詢：
A.檢索LIU老師所授課程的課程號(hào)和課程名。
? select C#,CNAME from C where TEACHER='LIU'
B.檢索年齡大于23歲的男學(xué)生的學(xué)號(hào)和姓名。
? select S#,SNAME from S where AGE>23 and
SEX='男'
C.檢索至少選修LIU老師所授課程中一門課程的女學(xué)生姓名。
? select SNAME from S left join SC on
S.S#=SC.S# left join C on SC.C#=C.C# where S.SEX='女' and C.TEACHER='LIU'
D.檢索WANG同學(xué)不學(xué)的課程的課程號(hào)。
? SELECT C# FROM C WHERE C# NOT IN (SELECT C# FROM SC
WHERE S# IN (SELECT S# FROM S WHERE SNAME='WANG'))
?
E.檢索至少選修兩門課程的學(xué)生學(xué)號(hào)。
? select distinct SC1.S# from SC as SC1 where
(select count(*) from SC as SC2 where SC1.S#=SC2.S#)>=2
F.檢索全部學(xué)生都選修的課程的課程號(hào)與課程名。
? SELECT C.C#,C.CNAME FROM C WHERE NOT EXISTS
(SELECT S.S# FROM S WHERE S.S# NOT IN(SELECT SC.S# FROM SC WHERE SC.C# =
C.C#))
G.檢索選修課程包含LIU老師所授課的學(xué)生學(xué)號(hào)?！?br />? select SC.S# from SC,C where SC.C#=C.C# and
C.TEACHER='LIU'