常用SQL語句有哪些

這篇文章將為大家詳細講解有關常用SQL語句有哪些，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

說明：以下五十個語句都按照測試數據進行過測試，最好每次只單獨運行一個語句。

問題及描述：

--1.學生表

Student(Sid,Sname,Sage,Ssex)--Sid學生編號,Sname學生姓名,Sage出生年月,Ssex
學生性別

--2.課程表

Course(Cid,Cname,Tid)--Cid --課程編號,Cname課程名稱,Tid教師編號

--3.教師表

Teacher(Tid,Tname) --Tid
教師編號,Tname 教師姓名

--4.成績表

SC(Sid,Cid,score) --Sid
學生編號,Cid 課程編號,score分數

*/

--創建測試數據

createtable Student(Sidvarchar(10),Snamenvarchar(10),Sagedatetime,Ssex
nvarchar(10))

insertinto Studentvalues('01'
, N'趙雷' ,
'1990-01-01' , N'男')

insertinto Studentvalues('02'
, N'錢電' ,
'1990-12-21' , N'男')

insertinto Studentvalues('03'
, N'孫風' ,
'1990-05-20' , N'男')

insertinto Studentvalues('04'
, N'李云' ,
'1990-08-06' , N'男')

insertinto Studentvalues('05'
, N'周梅' ,
'1991-12-01' , N'女')

insertinto Studentvalues('06'
, N'吳蘭' ,
'1992-03-01' , N'女')

insertinto Studentvalues('07'
, N'鄭竹' ,
'1989-07-01' , N'女')

insertinto Studentvalues('08'
, N'王菊' ,
'1990-01-20' , N'女')

createtable Course(Cidvarchar(10),Cnamenvarchar(10),Tidvarchar(10))

insertinto Coursevalues('01'
, N'語文' ,
'02')

insertinto Coursevalues('02'
, N'數學' ,
'01')

insertinto Coursevalues('03'
, N'英語' ,
'03')

createtable Teacher(Tidvarchar(10),Tnamenvarchar(10))

insertinto Teachervalues('01'
, N'張三')

insertinto Teachervalues('02'
, N'李四')

insertinto Teachervalues('03'
, N'王五')

createtable SC(Sidvarchar(10),Cidvarchar(10),scoredecimal(18,1))

insertinto SCvalues('01'
,'01' , 80)

insertinto SCvalues('01'
,'02' , 90)

insertinto SCvalues('01'
,'03' , 99)

insertinto SCvalues('02'
,'01' , 70)

insertinto SCvalues('02'
,'02' , 60)

insertinto SCvalues('02'
,'03' , 80)

insertinto SCvalues('03'
,'01' , 80)

insertinto SCvalues('03'
,'02' , 80)

insertinto SCvalues('03'
,'03' , 80)

insertinto SCvalues('04'
,'01' , 50)

insertinto SCvalues('04'
,'02' , 30)

insertinto SCvalues('04'
,'03' , 20)

insertinto SCvalues('05'
,'01' , 76)

insertinto SCvalues('05'
,'02' , 87)

insertinto SCvalues('06'
,'01' , 31)

insertinto SCvalues('06'
,'03' , 34)

insertinto SCvalues('07'
,'02' , 89)

insertinto SCvalues('07'
,'03' , 98)

go

--1、查詢"01"課程比"02"課程成績高的學生的信息及課程分數

思路：課程01（一個記錄集合），課程02（一個記錄集合），STUDENT表（一個記錄集合），包含在這三個記錄集合里，并且01分數>02分數的記錄。

select*
fromstudent s inner
join(select*
from sc where cid='01') a

on s.sid=a.sidinnerjoin
(select*
from sc where cid='02') b

on s.sid=b.sidwherea.score>b.score

select a.*,b.*,c.*fromstudent a
innerjoinsc b

on a.sid=b.sidandb.cid='01'inner
join sc c

on a.sid=c.sidandc.cid='02'where
b.score>c.score

--1.1、查詢同時存在"01"課程和"02"課程的情況

思路：課程01（一個記錄集合），課程02（一個記錄集合），STUDENT表（一個記錄集合），包含在這三個記錄集合里的記錄。

select*
fromstudent s inner
join(select*
from sc where cid='01') a

on s.sid=a.sidinnerjoin
(select
* from sc where cid='02') b

on s.sid=b.sidwherea.sid=b.sid

select s.*,a.*,b.*fromstudent s
innerjoinsc a

on s.sid=a.sidanda.cid='01'inner
joinsc b

on s.sid=b.sidandb.cid='02'

select a.* , b.score[課程'01'的分數],c.score[課程'02'的分數]from
Student a , SC b , SC c

where a.Sid= b.Sid
and a.Sid= c.Sid
and b.Cid='01'and c.Cid='02'and
b.score> c.score

--1.2、查詢同時存在"01"課程和"02"課程的情況和存在"01"課程但可能不存在"02"課程的情況(不存在時顯示為null)(以下存在相同內容時不再解釋)

思路：課程01（一個記錄集合），課程02可能有，可能不存在（cid=’02’ or cid is null）,STUDENT表（一個記錄集合）

select*
fromstudent s inner
joinsc a

on s.sid=a.sidanda.cid='01'left
join sc b

on s.sid=b.sidand(b.cid='02'or
b.cid is
null) where a.score>isnull(b.score,0)

select a.* , b.score[課程"01"的分數],c.score[課程"02"的分數]from
Student a leftjoin SC b

on a.Sid= b.Sid
and b.Cid='01'leftjoin SC c

on a.Sid= c.Sid
and c.Cid='02'

where b.score>isnull(c.score,0)

--2、查詢"01"課程比"02"課程成績低的學生的信息及課程分數

select*
fromstudent s inner
joinsc a

on s.sid=a.sidanda.cid='01'inner
join sc b

on s.sid=b.sidandb.cid='02'where
a.score<b.score

--2.1、查詢同時存在"01"課程和"02"課程的情況

select a.* , b.score[課程'01'的分數],c.score[課程'02'的分數]from
Student a , SC b , SC c

where a.Sid= b.Sid
and a.Sid= c.Sid
and b.Cid='01'and c.Cid='02'and
b.score< c.score

--2.2、查詢同時存在"01"課程和"02"課程的情況和不存在"01"課程但存在"02"課程的情況

select*
fromstudent s left
joinsc a

on s.sid=a.sidand(a.cid='01'or
a.cid is
null) innerjoin sc b

on s.sid=b.sidandb.cid='02'

select*
fromstudent s inner
join

(select*
from sc where cid='02') aon s.sid=a.sidleft
join

(select*
from sc where
(cid='01'or cid
is null)) b
on s.sid=b.sid

select a.* , b.score[課程"01"的分數],c.score[課程"02"的分數]from
Student a

leftjoin SC bon a.Sid
= b.Sid and b.Cid='01'

leftjoin SC con a.Sid
= c.Sid and c.Cid='02'

whereisnull(b.score,0)<
c.score

--3、查詢平均成績大于等于60分的同學的學生編號和學生姓名和平均成績

思路：平均成績大于等于60分（一個記錄集合），STUDENT表（一個記錄集合）

select s.sid,s.sname,b.[平均成績]fromstudent
s innerjoin

(select sid,convert(decimal(18,2),avg(score))as
'平均成績'from sc
group by sid
having avg(score)>=60) b

on s.sid=b.sid

select*
fromstudent s inner
join

(select sid,avg(score)as
avgscore from scgroup
by sid having
avg(score)>=60) a

on s.sid=a.sid

select a.Sid , a.Sname ,cast(avg(b.score)asdecimal(18,2))
avg_score

from Student a , sc b

where a.Sid= b.Sid

groupby a.Sid , a.Sname

havingcast(avg(b.score)asdecimal(18,2))>=60

orderby a.Sid

--4、查詢平均成績小于60分的同學的學生編號和學生姓名和平均成績

思路：平均成績小于60分（一個記錄集合），STUDENT（一個記錄集合）

select s.sid,s.sname,b.[平均成績]fromstudent
s innerjoin

(select sid,convert(decimal(18,2),avg(score))as
'平均成績'from sc
group by sid
having avg(score)>60) b

on s.sid=b.sid

--4.1、查詢在sc表存在成績的學生信息的SQL語句。

思路：STUDENT表（一個記錄集合）是否有記錄包含在SC表（一個記錄集合）

select*
fromstudent where sid
in(select sidfrom sc)

select*
fromstudent s where
exists(select 1from sc a
where s.sid=a.sid)

select a.Sid , a.Sname ,cast(avg(b.score)asdecimal(18,2))
avg_score

from Student a , sc b

where a.Sid= b.Sid

groupby a.Sid , a.Sname

havingcast(avg(b.score)asdecimal(18,2))<60

orderby a.Sid

--4.2、查詢在sc表中不存在成績的學生信息的SQL語句。

select
* from student where sid not
in (select distinct sid from sc)

select*
fromstudent s where
notexists(select 1
from sc a where s.sid=a.sid)

select a.Sid , a.Sname ,isnull(cast(avg(b.score)asdecimal(18,2)),0)
avg_score

from Student aleftjoin sc b

on a.Sid= b.Sid

groupby a.Sid , a.Sname

havingisnull(cast(avg(b.score)asdecimal(18,2)),0)<60

orderby a.Sid

--5、查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績

思路：SC表的選課總數、總成績（一個記錄集合），STUDENT表（一個記錄集合）

select s.sid,s.sname,a.[選課總數],a.[總成績]fromstudent
s innerjoin

(select sid,count(*)as
'選課總數',sum(score)as
'總成績'from sc
group by sid) a

on s.sid=a.sid

select*
fromstudent s inner
join

(select sid,count(cid)as
'課程總數',sum(score)as
'課程總成績'from sc
group by sid) a

on s.sid=a.sid

select s.sid,s.sname,count(a.cid)as
'課程總數',sum(a.score)as
'課程總成績'from student s
innerjoin sc a

on s.sid=a.sidgroupby
s.sid,s.sname

--5.1、查詢所有有成績的SQL。

select s.sid,s.sname,a.[選課總數],a.[總成績]fromstudent
s innerjoin

(select sid,count(*)as
'選課總數',sum(score)as
'總成績'from sc
group by sid) a

on s.sid=a.sid

select a.Sid[學生編號],
a.Sname[學生姓名],count(b.Cid)
選課總數,sum(score)
[所有課程的總成績]

from Student a , SC b

where a.Sid= b.Sid

groupby a.Sid,a.Sname

orderby a.Sid

--5.2、查詢所有(包括有成績和無成績)的SQL。

select s.sid,s.sname,a.[選課總數],a.[總成績]fromstudent
s leftjoin

(select sid,count(*)as
'選課總數',sum(score)as
'總成績'from sc
group by sid) a

on s.sid=a.sid

select*
fromstudent s left
join

(select sid,count(cid)as
'課程總數',sum(score)as
'課程總成績'from sc
group by sid) a

on s.sid=a.sidorderby
s.sid

select s.sid,s.sname,count(a.cid)as
'課程總數',sum(a.score)as
'課程總成績'from student s
leftjoin sc a

on s.sid=a.sidgroupby
s.sid,s.snameorder
by s.sid

select a.Sid[學生編號],
a.Sname[學生姓名],count(b.Cid)
選課總數,sum(score)
[所有課程的總成績]

from Student aleftjoin SC b

on a.Sid= b.Sid

groupby a.Sid,a.Sname

orderby a.Sid

--6、查詢"李"姓老師的數量

select count(*) as '數量' fromteacher where left(tname,1)='李'

--方法1

selectcount(Tname)["李"姓老師的數量]from
Teacher where Tnamelike N'李%'

--方法2

selectcount(Tname)["李"姓老師的數量]from
Teacher whereleft(Tname,1)=
N'李'

--7、查詢學過"張三"老師授課的同學的信息

思路： STUDENT（一個記錄集合），張三老師（一個記錄集合），張三老師上的課（一個記錄集合），張三老師上的課的成績（一個記錄集合）

select*
fromstudent s inner
joinsc a

on s.sid=a.sidinnerjoin
course c

on a.cid=c.cidinnerjoin
teacher t

on c.tid=t.tidwheret.tname='張三'

思路：從全部學生中（一個記錄集合）提取上過張三老師課的學生（一個記錄集合）

select*
fromstudent where sid
in(

select sidfrom sc a
inner join course b

on a.cid=b.cidinnerjoin
teacher c

on b.tid=c.tidandc.tname='張三')

selectdistinct Student.*from
Student , SC , Course , Teacher

where Student.Sid= SC.Sid
and SC.Cid
= Course.Cidand Course.Tid
= Teacher.Tid
and Teacher.Tname= N'張三'

orderby Student.Sid

--8☆、查詢沒學過"張三"老師授課的同學的信息

思路：從全部學生中（一個記錄集合）刪除上過張三老師課的學生（一個記錄集合）。

select*
fromstudent where sid
notin(
select distinct sid
from sc a inner
join course c

on a.cid=c.cidinnerjoin
teacher t

on c.tid=t.tidwheret.tname='張三')

select m.*from Student mwhere
Sid notin (selectdistinct SC.Sidfrom
SC , Course , Teacherwhere SC.Cid
= Course.Cid
and Course.Tid= Teacher.Tid
and Teacher.Tname
= N'張三')orderby
m.Sid

--9、查詢學過編號為"01"并且也學過編號為"02"的課程的同學的信息

思路：上過課程01（一個記錄集合），上過課程02（一個記錄集合），STUDENT表（一個記錄集合）

select*
fromstudent s inner
joinsc a

on s.sid=a.sidanda.cid='01'inner
join sc b

on s.sid=b.sidandb.cid='02'

思路：上過課程01的學生（一個記錄集合）并且存在上過課程02的學生（一個記錄集合）

select*
fromstudent s inner
joinsc a

on s.sid=a.sidanda.cid='01'and
exists (select 1
from sc bwhere s.sid=b.sidand b.cid='02')

--方法1

select Student.*from Student , SCwhere
Student.Sid= SC.Sid
and SC.Cid
='01'andexists (Select1from
SC SC_2 where SC_2.Sid= SC.Sid
and SC_2.Cid
='02')orderby Student.Sid

--方法2

select Student.*from Student , SCwhere
Student.Sid= SC.Sid
and SC.Cid
='02'andexists (Select1from
SC SC_2 where SC_2.Sid= SC.Sid
and SC_2.Cid
='01')orderby Student.Sid

--方法3

select m.*from Student mwhere
Sid in

(

 
select Sid from

 
(

   selectdistinctSidfrom
SC where Cid='01'

   unionall

   selectdistinctSidfrom
SC where Cid='02'

 
) t groupby Sidhavingcount(1)=2

)

orderby m.Sid

--10☆、查詢學過編號為"01"但是沒有學過編號為"02"的課程的同學的信息

思路：上過課程01的學生（一個記錄集合）并且不存在上過課程02的學生（一個記錄集合）

select*
fromstudent s inner
joinsc a

on s.sid=a.sidanda.cid='01'and
not exists
(select 1from sc b
where s.sid=b.sidand b.cid='02')

思路：從全部學生中（一個記錄集合）先提取上過課程01的學生記錄（一個記錄集合）再排除沒上過課程02的學生記錄（一個記錄集合）

select*
fromstudent where sid
in

(select sidfrom sc
where cid='01')and sid
not in
(

select sidfrom sc
where cid='02')

select*
fromstudent s inner
joinsc a

on s.sid=a.sidanda.cid='01'where
s.sid not
in (select sidfrom sc
where cid='02')

--方法1

select Student.*from Student , SCwhere
Student.Sid= SC.Sid
and SC.Cid
='01'andnotexists (Select1from
SC SC_2where SC_2.Sid
= SC.Sid
and SC_2.Cid='02')orderby
Student.Sid

--方法2

select Student.*from Student , SCwhere
Student.Sid= SC.Sid
and SC.Cid
='01'and Student.Sidnotin (Select
SC_2.Sidfrom SC SC_2
where SC_2.Sid
= SC.Sidand SC_2.Cid
='02')orderby Student.Sid

--11、查詢沒有學全所有課程的同學的信息

思路：從全部學生中（一個記錄集合）提取在SC表中課程總數不是全部的學生（一個記錄集合）

select*
fromstudent where sid
in

(select sidfrom

(select sid,count(*)as
abc from sc group
by sid havingcount(*)<(selectcount(*)
from course)) t)

該方法只列出有課程分數的學生，一個課程分數也沒有的學生不存在第二個記錄集合中。

思路：從全部學生中（一個記錄集合）排除在SC表中有全部課程分數的學生（一個記錄集合）

select*
fromstudent where sid
notin

(select sidfrom

(select sid,count(*)as
abc from sc group
by sid havingcount(*)=(selectcount(*)
from course)) t)

該方法還會列出一個課程分數都沒有的學生。

--11.1、

select Student.*

from Student , SC

where Student.Sid= SC.Sid

groupby Student.Sid , Student.Sname ,Student.Sage , Student.Ssexhavingcount(Cid)<
(selectcount(Cid)from Course)

--11.2

select Student.*

from Studentleftjoin SC

on Student.Sid= SC.Sid

groupby Student.Sid , Student.Sname ,Student.Sage , Student.Ssexhavingcount(Cid)<
(selectcount(Cid)from Course)

--12、查詢至少有一門課與學號為"01"的同學所學相同的同學的信息

思路：從全部學生中（一個記錄集合）提取所學課程中至少有一門和學生01所學課程相同（一個記錄集合）（也就是課程ID至少有一個存在于學生01的課程ID中）并排除學生01

select*
fromstudent where sid
in

(selectdistinct sid
from scwhere cid
in

(select cidfrom sc
where sid='01')and sid<>'01')

selectdistinct Student.*from
Student , SC where Student.Sid= SC.Sid
and SC.Cid
in (select Cidfrom SC
where Sid='01')and Student.Sid
<>'01'

--13☆、查詢和"01"號的同學學習的課程完全相同的其他同學的信息

思路：從全部學生中（一個記錄集合）提取所學全部課程ID存在于學生01的課程ID中并且課程總數等于學生01的課程總數（一個記錄集合）

select*
fromstudent where sid
in

(selectdistinct sid
from scwhere cid
in

(select cidfrom sc
where sid='01')and sid<>'01'group
by sid

havingcount(*)=(selectcount(*)
from sc where sid='01'))

select Student.*from Studentwhere
Sid in

(selectdistinct SC.Sidfrom
SC where Sid<>'01'and SC.Cidin
(selectdistinct Cidfrom SC
where Sid='01')

groupby SC.Sidhavingcount(1)=
(selectcount(1)from
SC where Sid='01'))

--14、查詢沒學過"張三"老師講授的任一門課程的學生姓名

思路：從全部學生中（一個記錄集合）排除學過老師張三上過的課的學生（一個記錄集合）（就是在SC表中有張三老師上過的課的分數）

select*
fromstudent where sid
notin

(selectdistinct a.sid
from sc a inner
join course b

on a.cid=b.cidinnerjoin
teacher c

on b.tid=c.tidwherec.tname='張三')

select student.*from studentwhere
student.Sidnotin

(selectdistinct sc.Sidfrom
sc , course , teacherwhere sc.Cid
= course.Cid
and course.Tid= teacher.Tid
and teacher.tname
= N'張三')

orderby student.Sid

--15☆、查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績

思路：全部學生（一個記錄集合），兩門及以上不及格課程（一個記錄集合）

select*
fromstudent s inner
join

(select sid,count(*)as
'不及格課程總數',convert(decimal(18,2),avg(score))as
'平均分數'from sc
where score<60group
by sid having
count(*)>=2) b

on s.sid=b.sid

select s.sid,s.sname,convert(decimal(5,2),avg(a.score))as
average fromstudent sinner
joinsc a

on s.sid=a.sidgroupby
s.sid,s.snamehaving s.sid
in

(select sidfrom

(select sid,count(*)as
times from sc where score<60
groupby sid
having count(*)>=2) t)

select student.Sid ,student.sname ,cast(avg(score)asdecimal(18,2))
avg_score from student , sc

where student.Sid= SC.Sid
and student.Sid
in (select Sidfrom SC
where score<60groupby
Sidhavingcount(1)>=2)

groupby student.Sid , student.sname

--16、檢索"01"課程分數小于60，按分數降序排列的學生信息

思路：全部學生（一個記錄集合），課程01分數小于60（一個記錄集合）

select*
fromstudent s inner
joinsc a

on s.sid=a.sidwherecid='01'and
score<60 order
by score desc

select*
fromstudent s inner
join(select*
from sc where cid='01'and score<60)
a

on s.sid=a.sidorderby
a.score

select student.* , sc.Cid , sc.scorefrom
student , sc

where student.Sid= SC.Sid
and sc.score
<60and sc.Cid='01'

orderby sc.scoredesc 

--17☆☆☆、按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績

思路：全部學生（一個記錄集合），全部課程分數和平均分（一個記錄集合），兩個記錄集合進行合并行轉列（新的一個記錄集合）

select s.sid,s.sname,max(case
b.cname when N'語文'then a.score
else null
end)as
'語文',

                                              max(case b.cnamewhen
N'數學'then a.score
else null
end)as
'數學',

                                              max(case b.cnamewhen
N'英語'then a.score
else null
end)as
'英語',

                                              convert(decimal(18,2),avg(a.score))as
'平均成績'

from student sleft
join sc a

on s.sid=a.sidleftjoin
course b

on a.cid=b.cidgroupby
s.sid,s.sname

orderby [平均成績]desc

--17.1 SQL 2000靜態

select a.Sid學生編號 , a.Sname學生姓名
,

      max(case c.Cnamewhen
N'語文'then b.score
elsenullend)[語文],

      max(case c.Cnamewhen
N'數學'then b.score
elsenullend)[數學],

      max(case c.Cnamewhen
N'英語'then b.score
elsenullend)[英語],

      cast(avg(b.score)asdecimal(18,2))平均分

from Student a

leftjoin SC bon a.Sid
= b.Sid

leftjoin Course con b.Cid
= c.Cid

groupby a.Sid , a.Sname

orderby平均分desc

--17.2 SQL 2000動態

declare@sqlnvarchar(4000)

set@sql='select a.Sid '+
N'學生編號'+' , a.Sname '+
N'學生姓名'

select@sql=@sql+',max(case
c.Cname when N'''+Cname+''' then b.score else null end) ['+Cname+']'

from (selectdistinct Cnamefrom
Course) as t

set@sql=@sql+' , cast(avg(b.score)
as decimal(18,2))'+ N'平均分'+'
from Student a left join SC b on a.Sid= b.Sid left join Course c on b.Cid = c.Cid

groupby a.Sid , a.Sname order by '+ N'平均分'+'
desc'

exec(@sql)

--17.3有關sql2005的動靜態寫法參見我的文章《普通行列轉換(version 2.0)》或《普通行列轉換(version
3.0)》。

--18☆☆☆☆☆、查詢各科成績最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優良率，優秀率

--及格為>=60，中等為：70-80，優良為：80-90，優秀為：>=90

思路：SC表和COURSE表聯合查詢，每一個字段要求都可以看作是一個子查詢，一個一個子查詢單獨做出來后，再拼接在一起。

select b.cid,b.cname,max(score)as
'最高分',min(score)as
'最低分',convert(decimal(5,2),avg(score))as
'平均分',

convert(varchar,convert(decimal(5,2),convert(decimal(5,2),count(casewhen
a.score>=60then 1
else null
end))/count(1)*100))+'%'as
'及格率',

convert(varchar,convert(decimal(5,2),convert(decimal(5,2),count(casewhen
a.score>=70and a.score<80then 1
else null
end))/count(1)*100))+'%'as
'中等率',

convert(varchar,convert(decimal(5,2),convert(decimal(5,2),count(casewhen
a.score>=80and a.score<90then 1
else null
end))/count(1)*100))+'%'as
'優良率',

convert(varchar,convert(decimal(5,2),convert(decimal(5,2),count(casewhen
a.score>=90then 1
else null
end))/count(1)*100))+'%'as
'優秀率'

from sc ainner
join course bon a.cid=b.cidgroup
by b.cid,b.cname

--方法1

select m.Cid[課程編號],
m.Cname[課程名稱],

 
max(n.score)
[最高分],

 
min(n.score)
[最低分],

 
cast(avg(n.score)asdecimal(18,2))[平均分],

 
cast((selectcount(1)from
SC where Cid= m.Cid
and score>=60)*100.0/
(selectcount(1)from
SC where Cid= m.Cid)
asdecimal(18,2))[及格率(%)],

 
cast((selectcount(1)from
SC where Cid= m.Cid
and score>=70and score<80
)*100.0/ (selectcount(1)from
SC where Cid= m.Cid)
asdecimal(18,2))[中等率(%)],

 
cast((selectcount(1)from
SC where Cid= m.Cid
and score>=80and score<90
)*100.0/ (selectcount(1)from
SC where Cid= m.Cid)
asdecimal(18,2))[優良率(%)],

 
cast((selectcount(1)from
SC where Cid= m.Cid
and score>=90)*100.0/
(selectcount(1)from
SC where Cid= m.Cid)
asdecimal(18,2))[優秀率(%)]

from Course m , SC n

where m.Cid= n.Cid

groupby m.Cid , m.Cname

orderby m.Cid

--方法2

select m.Cid[課程編號],
m.Cname[課程名稱],

 (selectmax(score)from
SC where Cid= m.Cid)
[最高分],

 
(selectmin(score)from SCwhere Cid
= m.Cid)
[最低分],

 
(selectcast(avg(score)asdecimal(18,2))from
SC where Cid= m.Cid)
[平均分],

 
cast((selectcount(1)from
SC where Cid= m.Cid
and score>=60)*100.0/
(selectcount(1)from
SC where Cid= m.Cid)
asdecimal(18,2))[及格率(%)],

 
cast((selectcount(1)from
SC where Cid= m.Cid
and score>=70and score<80
)*100.0/ (selectcount(1)from
SC where Cid= m.Cid)
asdecimal(18,2))[中等率(%)],

 
cast((selectcount(1)from
SC where Cid= m.Cid
and score>=80and score<90
)*100.0/ (selectcount(1)from
SC where Cid= m.Cid)
asdecimal(18,2))[優良率(%)],

 
cast((selectcount(1)from
SC where Cid= m.Cid
and score>=90)*100.0/
(selectcount(1)from
SC where Cid= m.Cid)
asdecimal(18,2))[優秀率(%)]

from Course m

orderby m.Cid

--19、按各科成績進行排序，并顯示排名

思路：利用over(partition by字段名order by
字段名)函數。

正常排序：1，2，3

select row_number()over(partitionby
cid order by cid,score
desc)as sort,*
from sc

合并重復不保留空缺：1，1，2，3

select dense_rank()over(partitionby
cid order by cid,score
desc)as sort,*
from sc

合并重復保留空缺：1，1，3

select rank() over(partitionby cid order by cid,score desc) as sort,*
from sc

--19.1 sql 2000用子查詢完成

--Score重復時保留名次空缺

select t.* , px=
(selectcount(1)from
SC where Cid= t.Cid
and score> t.score)
+1from sc torderby
t.cid , px

--Score重復時合并名次

select t.* , px=
(selectcount(distinct score)from
SC where Cid= t.Cid
and score>= t.score)
from sc t
orderby t.cid , px

--19.2sql 2005用rank,DENSE_RANK完成

--Score重復時保留名次空缺(rank完成)

select t.* , px=
rank() over(partition
by cidorderby scoredesc)
from sc torderby t.Cid , px

--Score重復時合并名次(DENSE_RANK完成)

select t.* , px=
DENSE_RANK() over(partition
by cidorderby scoredesc)
from sc torderby t.Cid , px

--20、查詢學生的總成績并進行排名

思路：所有學生的總成績（一個記錄集合），再使用函數進行排序。

select rank()over(orderby
sum(a.score)desc)
as ranking,s.sid,s.sname,sum(a.score)as
'總成績'from student s
innerjoin sc a

on s.sid=a.sidgroupby
s.sid,s.sname

這個查詢只能查詢到有成績的7名學生。

select dense_rank()over(orderby
isnull(sum(a.score),0)desc)
as ranking,s.sid,s.sname,

isnull(sum(a.score),0)as
'總成績'

from student sleft
join sc a on s.sid=a.sidgroup
by s.sid,s.sname

用了leftjoin就可以查詢到所有的8名學生了，包括沒有成績的1名學生。

--20.1查詢學生的總成績

select m.Sid[學生編號]
,

      m.Sname
[學生姓名] ,

      isnull(sum(score),0)[總成績]

from Student mleftjoin SC non
m.Sid = n.Sid

groupby m.Sid , m.Sname

orderby[總成績]desc

--20.2查詢學生的總成績并進行排名，sql 2000用子查詢完成，分總分重復時保留名次空缺和不保留名次空缺兩種。

select t1.* , px=
(selectcount(1)from

(

 
select m.Sid [學生編號] ,

        m.Sname
[學生姓名] ,

        isnull(sum(score),0)[總成績]

 
from Student m leftjoin SC non m.Sid=
n.Sid

 
groupby m.Sid, m.Sname

)t2where總成績>
t1.總成績)+1from

(

 
select m.Sid [學生編號] ,

        m.Sname
[學生姓名] ,

        isnull(sum(score),0)[總成績]

 
from Student m leftjoin SC non m.Sid=
n.Sid

 
groupby m.Sid, m.Sname

)t1

orderby px

select t1.* , px=
(selectcount(distinct總成績)from

(

 
select m.Sid [學生編號] ,

        m.Sname
[學生姓名] ,

        isnull(sum(score),0)[總成績]

 
from Student m leftjoin SC non m.Sid=
n.Sid

 
groupby m.Sid, m.Sname

)t2where總成績>=
t1.總成績)from

(

 
select m.Sid [學生編號] ,

        m.Sname
[學生姓名] ,

        isnull(sum(score),0)[總成績]

 
from Student m leftjoin SC non m.Sid=
n.Sid

 
groupby m.Sid, m.Sname

)t1

orderby px

--20.3查詢學生的總成績并進行排名，sql 2005用rank,DENSE_RANK完成，分總分重復時保留名次空缺和不保留名次空缺兩種。

select t.* , px=
rank() over(orderby[總成績]desc)from

(

 
select m.Sid [學生編號] ,

        m.Sname
[學生姓名] ,

        isnull(sum(score),0)[總成績]

 
from Student m leftjoin SC non m.Sid=
n.Sid

 
groupby m.Sid, m.Sname

)t

orderby px

select t.* , px=
DENSE_RANK() over(orderby[總成績]desc)from

(

 
select m.Sid [學生編號] ,

        m.Sname
[學生姓名] ,

        isnull(sum(score),0)[總成績]

 
from Student m leftjoin SC non m.Sid=
n.Sid

 
groupby m.Sid, m.Sname

)t

orderby px

--21、查詢不同老師所教不同課程平均分從高到低顯示

思路：不同老師所教不同課程的平均分（一個記錄集合），再使用函數over(order by字段名)

select rank()over(orderby
convert(decimal(5,2),avg(score))desc)
as ranking,c.tid,c.tname,b.cid,b.cname,

convert(decimal(5,2),avg(score))as
'平均分'from sc a

innerjoin course b
on a.cid=b.cidinner
join teacher con b.tid=c.tidgroup
by c.tid,c.tname,b.cid,b.cname

select m.Tid , m.Tname ,cast(avg(o.score)asdecimal(18,2))
avg_score

from Teacher m , Course n , SCo

where m.Tid= n.Tid
and n.Cid= o.Cid

groupby m.Tid , m.Tname

orderby avg_scoredesc

--22☆、查詢所有課程的成績第2名到第3名的學生信息及該課程成績

思路：所有課程成績的學生及課程信息（一個記錄集合），再利用函數排序（一個記錄集合），選擇第2名和第3名的記錄。

;with abc as

(select row_number() over(partition by a.cidorder by a.score
desc)as ranking,s.sid,s.sname,a.cid,b.cname,

a.score from student sinner
join sc a on s.sid=a.sidinner
join course b on a.cid=b.cid)

select
* from abc where ranking in
(2,3)

select
* from

(select row_number() over(partition by a.cidorder by a.score
desc)as ranking,s.sid,s.sname,a.cid,b.cname,

a.score from student sinner
join sc a on s.sid=a.sidinner
join course b on a.cid=b.cid) t

where t.rankingin(2,3)

--22.1 sql 2000用子查詢完成

--Score重復時保留名次空缺

select*from (select
t.* , px
= (selectcount(1)from
SC where Cid= t.Cid
and score> t.score)
+1from sc t) mwhere px
between2and3orderby
m.cid , m.px

--Score重復時合并名次

select*from (select
t.* , px
= (selectcount(distinct score)from
SC where Cid= t.Cid
and score>= t.score)
from sc t) m
where pxbetween2and3orderby
m.cid , m.px

--22.2 sql 2005用rank,DENSE_RANK完成

--Score重復時保留名次空缺(rank完成)

select*from (select
t.* , px
= rank() over(partitionby cid
orderby scoredesc)
from sc t) mwhere px
between2and3orderby
m.Cid , m.px

--Score重復時合并名次(DENSE_RANK完成)

select*from (select
t.* , px
= DENSE_RANK() over(partitionby cid
orderby scoredesc)
from sc t) mwhere px
between2and3orderby
m.Cid , m.px

--23☆☆☆、統計各科成績各分數段人數：課程編號,課程名稱,[100-85],[85-70],[70-60],[0-60]及所占百分比

思路：SC表和COURSE表聯合查詢（一個記錄集合），然后每個字段都看做是一個子查詢，最后將這些子查詢拼接起來。

select b.cidas
'課程編號',b.cnameas
'課程名稱',

count(1)as'總人數',

count(casewhen a.score<60then
1 else null
end) as
'不及格人數',

convert(decimal(5,2),count(casewhen
a.score>=0and a.score<60then 1
else null
end)*100/count(1))as
'不及格率%',

count(casewhen a.score>=60and
a.score<70then 1
else null
end) as
'及格人數',

convert(decimal(5,2),count(casewhen
a.score>=60and a.score<70then 1
else null
end)*100/count(1))as
'及格率%',

count(casewhen a.score>=70and
a.score<85then 1
else null
end) as
'優良人數',

convert(decimal(5,2),count(casewhen
a.score>=70and a.score<85then 1
else null
end)*100/count(1))as
'優良率%',

count(casewhen a.score>=85then
1 else null
end) as
'優秀人數',

convert(decimal(5,2),count(casewhen
a.score>=85then 1
else null
end)*100/count(1))as
'優秀率%'

from sc ainner
join course bon a.cid=b.cidgroup
by b.cid,b.cname

以上方法為橫向顯示。

select b.cidas
'課程編號',b.cnameas
'課程名稱',(casewhen score<60
then '0-59'

                                                                                                                                  when score>=60
and score<70
then'60-69'

                                                                                                                                  when score>=70
and score<85
then'70-85'

                                                                                                                                  else
'85-100' end)
as '分數段',

count(1)as'人數',

convert(decimal(18,2),count(1)*100/(selectcount(1)from
sc where cid=b.cid))as
'百分比'

from sc ainner
join course bon a.cid=b.cidgroup
by all b.cid,b.cname,(casewhen
score<60 then
'0-59'

                                                                                                                                  when score>=60
and score<70
then'60-69'

                                                                                                                                  when score>=70
and score<85
then'70-85'

                                                                                                                                  else
'85-100' end)

orderby b.cid,b.cname,'分數段'

以上方法為縱向顯示，但為0的就不顯示了。

--23.1統計各科成績各分數段人數：課程編號,課程名稱,[100-85],[85-70],[70-60],[0-60]

--橫向顯示

select Course.Cid[課程編號]
, Cnameas[課程名稱] ,

 
sum(casewhen score>=85then1else0end)[85-100],

 
sum(casewhen score>=70and
score<85then1else0end)[70-85],

 
sum(casewhen score>=60and
score<70then1else0end)[60-70],

 
sum(casewhen score<60then1else0end)[0-60]

from sc , Course

where SC.Cid= Course.Cid

groupby Course.Cid , Course.Cname

orderby Course.Cid

--縱向顯示1(顯示存在的分數段)

select m.Cid[課程編號]
, m.Cname[課程名稱] ,分數段=
(

 
casewhenn.score>=85then'85-100'

      when n.score
>=70and n.score<85then'70-85'

      when n.score
>=60and n.score<70then'60-70'

      else'0-60'

 
end) ,

 
count(1)數量

from Course m , sc n

where m.Cid= n.Cid

groupby m.Cid , m.Cname , (

 
casewhenn.score>=85then'85-100'

      when n.score
>=70and n.score<85then'70-85'

      when n.score
>=60and n.score<70then'60-70'

      else'0-60'

 
end)

orderby m.Cid , m.Cname ,分數段

--縱向顯示2(顯示存在的分數段，不存在的分數段用0顯示)

select m.Cid[課程編號]
, m.Cname[課程名稱] ,分數段=
(

 
casewhenn.score>=85then'85-100'

      when n.score
>=70and n.score<85then'70-85'

      when n.score
>=60and n.score<70then'60-70'

      else'0-60'

 
end) ,

 
count(1)數量

from Course m , sc n

where m.Cid= n.Cid

groupbyall m.Cid , m.Cname , (

 
casewhenn.score>=85then'85-100'

      when n.score
>=70and n.score<85then'70-85'

      when n.score
>=60and n.score<70then'60-70'

      else'0-60'

 
end)

orderby m.Cid , m.Cname ,分數段

--23.2統計各科成績各分數段人數：課程編號,課程名稱,[100-85],[85-70],[70-60],[<60]及所占百分比

--橫向顯示

select m.Cid課程編號, m.Cname課程名稱,

 (selectcount(1)from
SC where Cid= m.Cid
and score<60)[0-60],

 
cast((selectcount(1)from
SC where Cid= m.Cid
and score<60)*100.0/
(selectcount(1)from
SC where Cid= m.Cid)
asdecimal(18,2))[百分比(%)],

 
(selectcount(1)from
SC where Cid= m.Cid
and score>=60and score<70)[60-70],

 
cast((selectcount(1)from
SC where Cid= m.Cid
and score>=60and score<70)*100.0/
(selectcount(1)from
SC where Cid= m.Cid)
asdecimal(18,2))[百分比(%)],

 
(selectcount(1)from
SC where Cid= m.Cid
and score>=70and score<85)[70-85],

 
cast((selectcount(1)from
SC where Cid= m.Cid
and score>=70and score<85)*100.0/
(selectcount(1)from
SC where Cid= m.Cid)
asdecimal(18,2))[百分比(%)],

 (selectcount(1)from
SC where Cid= m.Cid
and score>=85)[85-100],

 
cast((selectcount(1)from
SC where Cid= m.Cid
and score>=85)*100.0/
(selectcount(1)from
SC where Cid= m.Cid)
asdecimal(18,2))[百分比(%)]

from Course m

orderby m.Cid

--縱向顯示1(顯示存在的分數段)

select m.Cid[課程編號]
, m.Cname[課程名稱] ,分數段=
(

 
casewhenn.score>=85then'85-100'

      when n.score
>=70and n.score<85then'70-85'

      when n.score
>=60and n.score<70then'60-70'

      else'0-60'

 
end) ,

 
count(1)數量
, 

 
cast(count(1)*100.0/
(selectcount(1)from
sc where Cid= m.Cid)
asdecimal(18,2))[百分比(%)]

from Course m , sc n

where m.Cid= n.Cid

groupby m.Cid , m.Cname , (

 
casewhenn.score>=85then'85-100'

      when n.score
>=70and n.score<85then'70-85'

      when n.score
>=60and n.score<70then'60-70'

      else'0-60'

 
end)

orderby m.Cid , m.Cname ,分數段

--縱向顯示2(顯示存在的分數段，不存在的分數段用0顯示)

select m.Cid[課程編號]
, m.Cname[課程名稱] ,分數段=
(

 
casewhenn.score>=85then'85-100'

      when n.score
>=70and n.score<85then'70-85'

      when n.score
>=60and n.score<70then'60-70'

      else'0-60'

 
end) ,

 
count(1)數量
, 

 
cast(count(1)*100.0/
(selectcount(1)from
sc where Cid= m.Cid)
asdecimal(18,2))[百分比(%)]

from Course m , sc n

where m.Cid= n.Cid

groupbyall m.Cid , m.Cname , (

 
casewhenn.score>=85then'85-100'

      when n.score
>=70and n.score<85then'70-85'

      when n.score
>=60and n.score<70then'60-70'

      else'0-60'

 
end)

orderby m.Cid , m.Cname ,分數段

--24、查詢學生平均成績及其名次

思路：所有學生的平均成績（一個記錄集合），再使用函數進行排序。

select s.sid,s.sname,row_number()over(orderby
avg(score)desc)
as ranking,convert(decimal(18,2),

avg(score))as
'平均成績'from student s
innerjoin sc a
on s.sid=a.sidgroup
by s.sid,s.sname

只顯示有成績的學生。

select s.sid,s.sname,row_number()over(orderby
avg(score)desc)
as ranking,convert(decimal(18,2),

avg(score))as
'平均成績'from student s
leftjoin sc a
on s.sid=a.sidgroup
by s.sid,s.sname

顯示所有學生。

--24.1查詢學生的平均成績并進行排名，sql 2000用子查詢完成，分平均成績重復時保留名次空缺和不保留名次空缺兩種。

select t1.* , px=
(selectcount(1)from

(

 
select m.Sid [學生編號] ,

        m.Sname
[學生姓名] ,

        isnull(cast(avg(score)asdecimal(18,2)),0)[平均成績]

 
from Student m leftjoin SC non m.Sid=
n.Sid

 
groupby m.Sid, m.Sname

)t2where平均成績>
t1.平均成績)+1from

(

 
select m.Sid [學生編號] ,

        m.Sname
[學生姓名] ,

        isnull(cast(avg(score)asdecimal(18,2)),0)[平均成績]

 
from Student m leftjoin SC non m.Sid=
n.Sid

 
groupby m.Sid, m.Sname

)t1

orderby px

select t1.* , px=
(selectcount(distinct平均成績)from

(

 
select m.Sid [學生編號] ,

        m.Sname
[學生姓名] ,

        isnull(cast(avg(score)asdecimal(18,2)),0)[平均成績]

 
from Student m leftjoin SC non m.Sid=
n.Sid

 
groupby m.Sid, m.Sname

)t2where平均成績>=
t1.平均成績)from

(

 
select m.Sid [學生編號] ,

        m.Sname
[學生姓名] ,

        isnull(cast(avg(score)asdecimal(18,2)),0)[平均成績]

 
from Student m leftjoin SC non m.Sid=
n.Sid

 
groupby m.Sid, m.Sname

)t1

orderby px

--24.2查詢學生的平均成績并進行排名，sql 2005用rank,DENSE_RANK完成，分平均成績重復時保留名次空缺和不保留名次空缺兩種。

select t.* , px=
rank() over(orderby[平均成績]desc)from

(

 
select m.Sid [學生編號] ,

        m.Sname
[學生姓名] ,

        isnull(cast(avg(score)asdecimal(18,2)),0)[平均成績]

 
from Student m leftjoin SC non m.Sid=
n.Sid

 
groupby m.Sid, m.Sname

)t

orderby px

select t.* , px=
DENSE_RANK() over(orderby[平均成績]desc)from

(

 
select m.Sid [學生編號] ,

        m.Sname
[學生姓名] ,

        isnull(cast(avg(score)asdecimal(18,2)),0)[平均成績]

 
from Student m leftjoin SC non m.Sid=
n.Sid

 
groupby m.Sid, m.Sname

)t

orderby px

--25、查詢各科成績前三名的記錄

思路：各學科成績排序（一個記錄集合），再取前3。

select
* from

(select row_number() over(partition by a.cidorder by a.score
desc)as ranking,

s.sid,s.sname,a.score from student sinnerjoin
sc a on s.sid=a.sid) t where rankingin
(1,2,3)

--25.1分數重復時保留名次空缺

select m.* , n.Cid , n.scorefrom
Student m, SC nwhere m.Sid
= n.Sid and n.scorein

(selecttop3 scorefrom
sc where Cid= n.Cid
orderby scoredesc)
orderby n.Cid , n.scoredesc

--25.2分數重復時不保留名次空缺，合并名次

--sql 2000用子查詢實現

select*from (select
t.* , px
= (selectcount(distinct score)from
SC where Cid= t.Cid
and score>= t.score)
from sc t) m
where pxbetween1and3orderby
m.cid , m.px

--sql 2005用DENSE_RANK實現

select*from (select
t.* , px
= DENSE_RANK() over(partitionby cid
orderby scoredesc)
from sc t) mwhere px
between1and3orderby
m.Cid , m.px

--26、查詢每門課程被選修的學生數

思路：每門課被選修的學生數（一個記錄集合）。

select*
fromcourse a inner
join

(select cid,count(*)as
'人數'from sc
group by cid) b

on a.cid=b.cid

select a.cid,a.cname,count(1)as
'人數'from course a
innerjoin sc b

on a.cid=b.cidgroupby
a.cid,a.cname

select cid ,count(Sid)[學生數]from
sc groupby Cid

--27、查詢出只有兩門課程的全部學生的學號和姓名

select Student.Sid ,Student.Sname

from Student , SC

where Student.Sid= SC.Sid

groupby Student.Sid , Student.Sname

havingcount(SC.Cid)=2

orderby Student.Sid

--28、查詢男生、女生人數

思路：

select ssex,count(1)as'人數'from
student groupby ssex

selectcount(Ssex)as男生人數from
Studentwhere Ssex
= N'男'

selectcount(Ssex)as女生人數from
Studentwhere Ssex
= N'女'

selectsum(casewhen
Ssex = N'男'then1else0end)[男生人數],sum(casewhen
Ssex = N'女'then1else0end)[女生人數]from
student

selectcasewhen Ssex=
N'男'then N'男生人數'else
N'女生人數'end[男女情況]
, count(1)[人數]from
studentgroupbycasewhen Ssex=
N'男'then N'男生人數'else
N'女生人數'end

--29、查詢名字中含有"風"字的學生信息

select*
fromstudent where sname
like'%風%'

select*from studentwhere
sname like N'%風%'

select*from studentwherecharindex(N'風'
, sname) >0

--30、查詢同名同性學生名單，并統計同名人數

思路：按照姓名字段進行GROUP BY，同時計算人數，只要大于1，就是同姓同名。

select sname,count(1)as
'人數'from student
groupby sname
having count(1)>1

select Sname[學生姓名],count(*)[人數]from
Studentgroupby Snamehavingcount(*)>1

--31、查詢1990年出生的學生名單(注：Student表中Sage列的類型是datetime)

select*
fromstudent where
datepart(year,sage)='1990'

select*from Studentwhereyear(sage)=1990

select*from Studentwheredatediff(yy,sage,'1990-01-01')=0

select*from Studentwheredatepart(yy,sage)=1990

select*from Studentwhereconvert(varchar(4),sage,120)='1990'

--32、查詢每門課程的平均成績，結果按平均成績降序排列，平均成績相同時，按課程編號升序排列

思路：每門課程的平均成績（一個記錄集合），再使用函數排序，排序時根據平均成績、課程編號。

select row_number()over(orderby
convert(decimal(18,2),avg(a.score))desc,b.cid)as
'排名',b.cid,b.cname,convert(decimal(18,2),avg(a.score))as
'平均成績'from sc a
inner join course b

on a.cid=b.cidgroupby
b.cid,b.cname

select m.Cid , m.Cname ,cast(avg(n.score)asdecimal(18,2))
avg_score

from Course m, SC n

where m.Cid= n.Cid   

groupby m.Cid , m.Cname

orderby avg_scoredesc, m.Cid
asc

--33、查詢平均成績大于等于85的所有學生的學號、姓名和平均成績

select s.sid,s.sname,convert(decimal(18,2),avg(a.score))as
'平均成績'from student s
innerjoin sc a

on s.sid=a.sidgroupby
s.sid,s.snamehaving
avg(a.score)>=85

select a.Sid , a.Sname ,cast(avg(b.score)asdecimal(18,2))
avg_score

from Student a , sc b

where a.Sid= b.Sid

groupby a.Sid , a.Sname

havingcast(avg(b.score)asdecimal(18,2))>=85

orderby a.Sid

--34、查詢課程名稱為"數學"，且分數低于60的學生姓名和分數

select s.sid,s.sname,b.cname,a.scorefrom
student sinnerjoin sc a

on s.sid=a.sidinnerjoin
course b

on a.cid=b.cid

where b.cname='數學'and
a.score<60

select sname , score

from Student , SC , Course

where SC.Sid= Student.Sid
and SC.Cid
= Course.Cidand Course.Cname= N'數學'and
score <60

--35、查詢所有學生的課程及分數情況；

select s.sid,s.sname,b.cid,b.cname,a.score

from student sinner
join sc a on s.sid=a.sidinner
join course bon a.cid=b.cid

select Student.* , Course.Cname , SC.Cid ,SC.score 

from Student, SC , Course

where Student.Sid= SC.Sid
and SC.Cid
= Course.Cid

orderby Student.Sid , SC.Cid

--36、查詢任何一門課程成績在70分以上的姓名、課程名稱和分數；

select s.sid,s.sname,b.cid,b.cname,a.scorefrom
student sinnerjoin sc a

on s.sid=a.sidinnerjoin
course b

on a.cid=b.cid

where a.score>70

select Student.* , Course.Cname , SC.Cid ,SC.score 

from Student, SC , Course

where Student.Sid= SC.Sid
and SC.Cid
= Course.Cidand SC.score
>=70

orderby Student.Sid , SC.Cid

--37、查詢不及格的課程

select s.sid,s.sname,b.cid,b.cname,a.scorefrom
student sinnerjoin sc a

on s.sid=a.sidinnerjoin
course b

on a.cid=b.cid

where a.score<60

select Student.* , Course.Cname , SC.Cid ,SC.score 

from Student, SC , Course

where Student.Sid= SC.Sid
and SC.Cid
= Course.Cidand SC.score
<60

orderby Student.Sid , SC.Cid

--38、查詢課程編號為01且課程成績在80分以上的學生的學號和姓名；

select s.sid,s.sname,b.cid,b.cname,a.scorefrom
student sinnerjoin sc a

on s.sid=a.sidinnerjoin
course b

on a.cid=b.cid

where a.score>=80and b.cid='01'

select Student.* , Course.Cname , SC.Cid ,SC.score 

from Student, SC , Course

where Student.Sid= SC.Sid
and SC.Cid
= Course.Cidand SC.Cid
='01'and SC.score>=80

orderby Student.Sid , SC.Cid

--39、求每門課程的學生人數

select b.cid,b.cname,count(1)as
'人數'from sc a
inner join course b

on a.cid=b.cidgroupby
b.cid,b.cname

select Course.Cid , Course.Cname,count(*)[學生人數]

from Course , SC

where Course.Cid= SC.Cid

groupby Course.Cid , Course.Cname

orderby Course.Cid , Course.Cname

--40、查詢選修"張三"老師所授課程的學生中，成績最高的學生信息及其成績

思路：上張三老師課的學生（一個記錄集合）

selecttop 1
* from student s
inner join sc a

on s.sid=a.sidinnerjoin
course b

on a.cid=b.cidinnerjoin
teacher c

on b.tid=c.tidwherec.tname='張三'order
by a.scoredesc

--40.1當最高分只有一個時

selecttop1 Student.*
, Course.Cname , SC.Cid ,SC.score 

from Student, SC , Course ,Teacher

where Student.Sid= SC.Sid
and SC.Cid
= Course.Cidand Course.Tid
= Teacher.Tid
and Teacher.Tname= N'張三'

orderby SC.scoredesc

--40.2當最高分出現多個時

select Student.* , Course.Cname , SC.Cid ,SC.score 

from Student, SC , Course ,Teacher

where Student.Sid= SC.Sid
and SC.Cid
= Course.Cidand Course.Tid
= Teacher.Tid
and Teacher.Tname= N'張三'and

SC.score= (selectmax(SC.score)from
SC , Course , Teacherwhere SC.Cid
= Course.Cid
and Course.Tid= Teacher.Tid
and Teacher.Tname
= N'張三')

--41☆☆☆☆☆、查詢不同課程成績相同的學生的學生編號、課程編號、學生成績

思路：

--方法1

select m.*from SC m ,(select
Cid , score from SCgroupby Cid , scorehavingcount(1)>1)
n

where m.Cid= n.Cidand
m.score = n.score
orderby m.Cid , m.score , m.Sid

--方法2

select m.*from SC mwhereexists
(select1from (select Cid , scorefrom
SC groupby Cid , scorehavingcount(1)>1)
n

where m.Cid= n.Cidand
m.score = n.score)
orderby m.Cid , m.score , m.Sid

--42、查詢每門課程成績最好的前兩名

思路：每門課程全部成績（一個記錄集合）。

select
* from (selectrow_number() over(partitionby cid order by score desc) as ranking,* from sc)
a whereranking in (1,2)

select t.*from sc twhere
score in (selecttop2 scorefrom
sc where Cid= T.Cid
orderby scoredesc)
orderby t.Cid , t.scoredesc

--43、統計每門課程的學生選修人數（超過5人的課程才統計）。要求輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列 

select b.cid,b.cname,count(1)as
'人數'from sc a
inner join course b

on a.cid=b.cidgroupby
b.cid,b.cnamehaving
count(1)>5order
by count(1)
desc,b.cid

select Course.Cid , Course.Cname,count(*)[學生人數]

from Course , SC

where Course.Cid= SC.Cid

groupby Course.Cid , Course.Cname

havingcount(*)>=5

orderby[學生人數]desc
, Course.Cid

--44、檢索至少選修兩門課程的學生學號

select s.sid,s.sname,count(1)as
'課程數'from student s
innerjoin sc a

on s.sid=a.sidgroupby
s.sid,s.snamehaving
count(1)>=2

select student.Sid ,student.Sname

from student , SC

where student.Sid= SC.Sid

groupby student.Sid , student.Sname

havingcount(1)>=2

orderby student.Sid

--45、查詢選修了全部課程的學生信息

select s.sid,s.sname,count(1)as
'課程數'from student s
innerjoin sc a

on s.sid=a.sidgroupby
s.sid,s.snamehaving
count(1)>=(selectcount(1)from
course)

--方法1根據數量來完成

select student.*from studentwhere
Sid in

(select Sidfrom sc
groupby Sidhavingcount(1)=
(selectcount(1)from
course))

--方法2使用雙重否定來完成

select t.*from student twhere
t.Sid notin

(

 
selectdistinctm.Sidfrom

 
(

   select Sid , Cidfrom student , course

 
) m wherenotexists (select1from
sc n where n.Sid= m.Sid
and n.Cid= m.Cid)

)

--方法3使用雙重否定來完成

select t.*from student twherenotexists(select1from

(

 
selectdistinctm.Sidfrom

 
(

   select Sid , Cidfrom student , course

 
) m wherenotexists (select1from
sc n where n.Sid= m.Sid
and n.Cid= m.Cid)

) kwhere k.Sid
= t.Sid

)

--46、查詢各學生的年齡

select*,datediff(year,sage,getdate())as
'年齡'from student

粗略算法

select*,datediff(day,sage,getdate())/365as
'年齡'from student

具體算法

--46.1只按照年份來算

select* ,datediff(yy , sage ,getdate())
[年齡]from student

--46.2按照出生日期來算，當前月日 <
出生年月的月日則，年齡減一

select* ,casewhenright(convert(varchar(10),getdate(),120),5)<right(convert(varchar(10),sage,120),5)thendatediff(yy
, sage ,getdate())
-1elsedatediff(yy , sage ,getdate())
end[年齡]from student

--47、查詢本周過生日的學生

思路：將學生出生日期的年換成今年，然后加上具體日期，再和今天比較，如果為0，就是本周，如果為-1，就是下周，如果為1，就是上周。

select*
fromstudent

wheredatediff(week,convert(varchar,datepart(yy,getdate()))+right(convert(varchar(10),sage,120),6),getdate())=0

select*from studentwheredatediff(week,datename(yy,getdate())+right(convert(varchar(10),sage,120),6),getdate())=0

--48、查詢下周過生日的學生

select*
fromstudent

wheredatediff(week,convert(varchar,datepart(yy,getdate()))+right(convert(varchar(10),sage,120),6),getdate())=-1

select*from studentwheredatediff(week,datename(yy,getdate())+right(convert(varchar(10),sage,120),6),getdate())=-1

--49、查詢本月過生日的學生

思路：把學生的出生日期的年換成今年，然后判斷月是否在當前月。為0就是本月，為1就是上月，為-1就是下月。

select*
fromstudent

wheredatediff(mm,convert(varchar,datepart(yy,getdate()))+right(convert(varchar(10),sage,120),6),getdate())=0

select*from studentwheredatediff(mm,datename(yy,getdate())+right(convert(varchar(10),sage,120),6),getdate())=0

--50、查詢下月過生日的學生

select*
fromstudent

wheredatediff(mm,convert(varchar,datepart(yy,getdate()))+right(convert(varchar(10),sage,120),6),getdate())=-1

select*from studentwheredatediff(mm,datename(yy,getdate())+right(convert(varchar(10),sage,120),6),getdate())=-1

總結：

1．一種是先組合成一個總的記錄集合，然后再進行GROUP BY或者ORDER
BY等其他操作；另一種是分別先對小的記錄集合進行其他操作，然后再組合到一起成為最終的一個記錄集合。

2．針對排序，有三種情況：

RANK()OVER()：排名1，1，3——保留

DENSE_RANK()OVER：排名1，1，2——不保留

ROW_NUMBEROVER()：排名1，2，3——沒有同排名的

3．有關日期的計算，一是要注意東西方對星期開始的差異，最好是使用SET DATEFIRST 1來人為的設定每周開始為星期一。二是要注意年、月、日三個元素的分別調整。三是要注意在調整過程中數據類型的變換。

關于“常用SQL語句有哪些”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。