1.一個表中的Id有多個記錄，把所有這個id的記錄查出來，并顯示共有多少條記錄數(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個字段：語文，數(shù)學，英語。其中有3條記錄分別表示語文70分，數(shù)學80分，英語58分，請用一條sql語句查詢出這三條記錄并按以下條件顯示出來（并寫出您的思路）：?
?? 大于或等于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 '語文',
(case when sx <60 then
'不及格'
???????? when sx >= 80 then '優(yōu)秀'
???????? else'及格' end) as
'數(shù)學',
(case when yy <60 then '不及格'
???????? when yy >= 80 then
'優(yōu)秀'
???????? else'及格' end) as '英語'
from tb
drop table tb
3.請教一個面試中遇到的SQL語句的查詢問題
表中有A B
C三列,用SQL語句實現(xiàn)：當A列大于B列時選擇A列否則選擇B列，當B列大于C列時選擇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è)務培訓信息，建立3個表:
S (S#,SN,SD,SA) S#,SN,SD,SA
分別代表學號、學員姓名、所屬單位、學員年齡
C (C#,CN ) C#,CN 分別代表課程編號、課程名稱
SC ( S#,C#,G ) S#,C#,G 分別代表學號、所選修的課程編號、學習成績
A. 使用標準SQL嵌套語句查詢選修課程名稱為’稅收基礎’的學員學號和姓名
B.
使用標準SQL嵌套語句查詢選修課程編號為’C2’的學員姓名和所屬單位
C.
使用標準SQL嵌套語句查詢不選修課程編號為’C5’的學員姓名和所屬單位
D. 使用標準SQL嵌套語句查詢選修全部課程的學員姓名和所屬單位
E.
查詢選修了課程的學員人數(shù)
F. 查詢選修課程超過5門的學員學號和所屬單位
A. select S#,SN from S where S# in(select S# from SC,C where SC.C#=C.C# and
CN='稅收基礎')
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.問題描述:
S (SNO,SNAME) 學生關系。SNO 為學號，SNAME 為姓名
C (CNO,CNAME,CTEACHER) 課程關系。CNO 為課程號，CNAME 為課程名，CTEACHER 為任課教師
SC(SNO,CNO,SCGRADE) 選課關系。SCGRADE 為成績
A. 找出沒有選修過“李明”老師講授課程的所有學生姓名
Select SNAME FROM S Where NOT EXISTS (
Select * FROM SC,C Where SC.CNO=C.CNO AND CNAME='李明' AND SC.SNO=S.SNO)
B. 列出有二門以上(含兩門)不及格課程的學生姓名及其平均成績
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. 列出既學過“1”號課程，又學過“2”號課程的所有學生姓名
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”號課成績比“2”號同學該門課成績高的所有學生的學號
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”號課成績比“2”號課成績高的所有學生的學號及其“1”號課和“2”號課的成績
Select
S.SNO,S.SNAME,SC.[1號課成績],SC.[2號課成績] FROM S,
(Select
SC1.SNO,[1號課成績]=SC1.SCGRADE,[2號課成績]=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查詢出每個用戶最近一次登錄的記錄(每個用戶只顯示一條最近登錄的記錄)
方法一:
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)在我們假設只有一個table，名為pages，有四個字段，id,
url,title,body。里面儲存了很多網(wǎng)頁，網(wǎng)頁的url地址，title和網(wǎng)頁的內(nèi)容，然后你用一個sql查詢將url匹配的排在最前，
title匹配的其次，body匹配最后，沒有任何字段匹配的，不返回。
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.對于教學數(shù)據(jù)庫的三個基本表
學生 S(S#,SNAME,AGE,SEX)
學習 SC(S#,C#,GRADE)
課程 C(C#,CNAME,TEACHER)
試用SQL的查詢語句表達下列查詢：
A.檢索LIU老師所授課程的課程號和課程名。
? select C#,CNAME from C where TEACHER='LIU'
B.檢索年齡大于23歲的男學生的學號和姓名。
? select S#,SNAME from S where AGE>23 and
SEX='男'
C.檢索至少選修LIU老師所授課程中一門課程的女學生姓名。
? 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同學不學的課程的課程號。
? SELECT C# FROM C WHERE C# NOT IN (SELECT C# FROM SC
WHERE S# IN (SELECT S# FROM S WHERE SNAME='WANG'))
?
E.檢索至少選修兩門課程的學生學號。
? select distinct SC1.S# from SC as SC1 where
(select count(*) from SC as SC2 where SC1.S#=SC2.S#)>=2
F.檢索全部學生都選修的課程的課程號與課程名。
? 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老師所授課的學生學號。
? select SC.S# from SC,C where SC.C#=C.C# and
C.TEACHER='LIU'