一張表user存儲如下數(shù)據(jù)
實現(xiàn)：
sql：
select
nameas姓名,
sum(casedegreewhen'A'then1else0end)asA,
sum(casedegreewhen'B'then1else0end)asB,
sum(casedegreewhen'C'then1else0end)asC
from
user
groupby
name
問題及描述：
--1.學(xué)生表
Student(SID,Sname,Sage,Ssex) --SID?學(xué)生編號,Sname?學(xué)生姓名,Sage?出生年月,Ssex?學(xué)生性別
--2.課程表
Course(CID,Cname,TID) --CID --課程編號,Cname?課程名稱,TID?教師編號
--3.教師表
Teacher(TID,Tname) --TID?教師編號,Tname?教師姓名
--4.成績表
SC(SID,CID,score) --SID?學(xué)生編號,CID?課程編號,score?分?jǐn)?shù)
*/
--創(chuàng)建測試數(shù)據(jù)
create table Student(SID varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10));
insert into Student values('01' , '趙雷' , '1990-01-01' , '男');
insert into Student values('02' , '錢電' , '1990-12-21' , '男');
insert into Student values('03' , '孫風(fēng)' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吳蘭' , '1992-03-01' , '女');
insert into Student values('07' , '鄭竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
create table Course(CID varchar(10),Cname nvarchar(10),TID varchar(10));
insert into Course values('01' , '語文' , '02');
insert into Course values('02' , '數(shù)學(xué)' , '01');
insert into Course values('03' , '英語' , '03');
create table Teacher(TID varchar(10),Tname nvarchar(10));
insert into Teacher values('01' , '張三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(SID varchar(10),CID varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
--1、查詢"01"課程比"02"課程成績高的學(xué)生的信息及課程分?jǐn)?shù)
--1.1清笨、查詢同時存在"01"課程和"02"課程的情況
select a.* , b.score?課程01的分?jǐn)?shù),c.score?課程02的分?jǐn)?shù)?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)(以下存在相同內(nèi)容時不再解釋)
select a.* , b.score?課程01的分?jǐn)?shù),c.score?課程02的分?jǐn)?shù)?from Student a
left join SC b on a.SID = b.SID and b.CID = '01'
left join SC c on a.SID = c.SID and c.CID = '02'
where b.score > isnull(c.score,0)
方法1.1 sql 解析（基于 mysql 數(shù)據(jù)庫，方法 1.2 不是mysql 數(shù)據(jù)庫齐苛，mysql 數(shù)據(jù)庫里面沒有 ?isnull 函數(shù)）查詢流程如下：
--2脸狸、查詢"01"課程比"02"課程成績低的學(xué)生的信息及課程分?jǐn)?shù)
--2.1炊甲、查詢同時存在"01"課程和"02"課程的情況
select a.* , b.score ?課程01的分?jǐn)?shù)?,c.score ?課程02的分?jǐn)?shù)??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 a.* , b.score ?課程01的分?jǐn)?shù)?,c.score ?課程02的分?jǐn)?shù)??from Student a
left join SC b on a.SID = b.SID and b.CID = '01'
left join SC c on a.SID = c.SID and c.CID = '02'
where isnull(b.score,0) < c.score
--3剑逃、查詢平均成績大于等于60分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績
select a.SID , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_score
from Student a , sc b
where a.SID = b.SID
group by a.SID , a.Sname
having cast(avg(b.score) as decimal(18,2)) >= 60
order by a.SID
--4蛹磺、查詢平均成績小于60分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績
--4.1萤捆、查詢在sc表存在成績的學(xué)生信息的SQL語句俗或。
select a.SID , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_score
from Student a , sc b
where a.SID = b.SID
group by a.SID , a.Sname
having cast(avg(b.score) as decimal(18,2)) < 60
order by a.SID
--4.2辛慰、查詢在sc表中不存在成績的學(xué)生信息的SQL語句帅腌。
select a.SID , a.Sname , isnull(cast(avg(b.score) as decimal(18,2)),0) avg_score
from Student a left join sc b
on a.SID = b.SID
group by a.SID , a.Sname
having isnull(cast(avg(b.score) as decimal(18,2)),0) < 60
order by a.SID
--5、查詢所有同學(xué)的學(xué)生編號挽封、學(xué)生姓名辅愿、選課總數(shù)点待、所有課程的總成績
--5.1癞埠、查詢所有有成績的SQL苗踪。
select a.SID ?學(xué)生編號?, a.Sname ?學(xué)生姓名?, count(b.CID)?選課總數(shù), sum(score) ?所有課程的總成績
from Student a , SC b
where a.SID = b.SID
group by a.SID,a.Sname
order by a.SID
--5.2通铲、查詢所有(包括有成績和無成績)的SQL颅夺。
select a.SID ?學(xué)生編號?, a.Sname ?學(xué)生姓名?, count(b.CID)?選課總數(shù), sum(score) ?所有課程的總成績
from Student a left join SC b
on a.SID = b.SID
group by a.SID,a.Sname
order by a.SID
--6吧黄、查詢"李"姓老師的數(shù)量
--方法1
select count(Tname) ?李姓老師的數(shù)量??from Teacher where Tname like '李%'
--方法2
select count(Tname) ?李姓老師的數(shù)量??from Teacher where left(Tname,1) = '李'
--7廓八、查詢學(xué)過"張三"老師授課的同學(xué)的信息
select distinct Student.* from Student , SC , Course , Teacher
where Student.SID = SC.SID and SC.CID = Course.CID and Course.TID = Teacher.TID and Teacher.Tname = '張三'
order by Student.SID
--8、查詢沒學(xué)過"張三"老師授課的同學(xué)的信息
select m.* from Student m where SID not in (select distinct SC.SID from SC , Course , Teacher where SC.CID = Course.CID and Course.TID = Teacher.TID and Teacher.Tname = '張三') order by m.SID
--9昌讲、查詢學(xué)過編號為"01"并且也學(xué)過編號為"02"的課程的同學(xué)的信息
--方法1
select Student.* from Student , SC where Student.SID = SC.SID and SC.CID = '01' and exists (Select 1 from SC SC_2 where SC_2.SID = SC.SID and SC_2.CID = '02') order by Student.SID
--方法2
select Student.* from Student , SC where Student.SID = SC.SID and SC.CID = '02' and exists (Select 1 from SC SC_2 where SC_2.SID = SC.SID and SC_2.CID = '01') order by Student.SID
方法1的講解：這里的 select 1 只是查看這個子查詢有沒有查詢記錄短绸，而并不關(guān)心查詢到什么，這個 1 可以換成 * 或者某個字段证逻，因為這個 select 子查詢是在 exists 條件下的囚企，所以和 exists 結(jié)合后龙宏，只會返回 true 或者 false 银酗，如果返回true 黍特，則外查詢的記錄被錄入到結(jié)果集中衅澈，如果 exists 的結(jié)果返回 false 经备，則外查詢的結(jié)果不被放入結(jié)果集中
這里的子查詢中侵蒙，SC_2 與外查詢的 SC 進行關(guān)聯(lián)查詢纷闺，由于在外查詢中犁功，篩選過一次：SC.CID='01' 選出了有選 '01' 課程的同學(xué)浸卦，但是這些記錄里面既有包含同時選'02' 課程的同學(xué)，也包含選其他課程的同學(xué)怒医，所以用 exists+子查詢的方式判斷外查詢里面的記錄是否滿足子查詢的條件（也就是這些外查詢記錄必須是包含選過課程'02'的同學(xué)）稚叹，如果滿足入录，外查詢的這條記錄放入結(jié)果集僚稿，否則舍棄蚀同，這里外查詢中是 student 和 sc 其實已經(jīng)是一個新表了
exists 是把外查詢的每一條記錄都與子查詢的結(jié)果集進行比對衰猛，若滿足子查詢的條件啡省，exists 返回true卦睹，外查詢的該條記錄放入結(jié)果集结序，否則舍棄該條記錄
--方法3
select m.* from Student m where SID in
(
select SID from
(
select distinct SID from SC where CID = '01'
union all
select distinct SID from SC where CID = '02'
) t group by SID having count(1) = 2
)
order by m.SID
--10、查詢學(xué)過編號為"01"但是沒有學(xué)過編號為"02"的課程的同學(xué)的信息
--方法1
select Student.* from Student , SC where Student.SID = SC.SID and SC.CID = '01' and not exists (Select 1 from SC SC_2 where SC_2.SID = SC.SID and SC_2.CID = '02') order by Student.SID
--方法2
select Student.* from Student , SC where Student.SID = SC.SID and SC.CID = '01' and Student.SID not in (Select SC_2.SID from SC SC_2 where SC_2.SID = SC.SID and SC_2.CID = '02') order by Student.SID
--11返敬、查詢沒有學(xué)全所有課程的同學(xué)的信息
--11.1、
select Student.*
from Student , SC
where Student.SID = SC.SID
group by Student.SID , Student.Sname , Student.Sage , Student.Ssex having count(CID) < (select count(CID) from Course)
--11.2
select Student.*
from Student left join SC
on Student.SID = SC.SID
group by Student.SID , Student.Sname , Student.Sage , Student.Ssex having count(CID) < (select count(CID) from Course)
--12经磅、查詢至少有一門課與學(xué)號為"01"的同學(xué)所學(xué)相同的同學(xué)的信息
select distinct Student.* from Student , SC where Student.SID = SC.SID and SC.CID in (select CID from SC where SID = '01') and Student.SID <> '01'
--13、查詢和"01"號的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息
select Student.* from Student where SID in
(select distinct SC.SID from SC where SID <> '01' and SC.CID in (select distinct CID from SC where SID = '01')
group by SC.SID having count(1) = (select count(1) from SC where SID='01'))
--14、查詢沒學(xué)過"張三"老師講授的任一門課程的學(xué)生姓名
select student.* from student where student.SID not in
(select distinct sc.SID from sc , course , teacher where sc.CID = course.CID and course.TID = teacher.TID and teacher.tname = '張三')
order by student.SID
--15刊棕、查詢兩門及其以上不及格課程的同學(xué)的學(xué)號甥角，姓名及其平均成績
select student.SID , student.sname , cast(avg(score) as decimal(18,2)) avg_score from student , sc
where student.SID = SC.SID and student.SID in (select SID from SC where score < 60 group by SID having count(1) >= 2)
group by student.SID , student.sname
--16震束、檢索"01"課程分?jǐn)?shù)小于60垢村，按分?jǐn)?shù)降序排列的學(xué)生信息
select student.* , sc.CID , sc.score from student , sc
where student.SID = SC.SID and sc.score < 60 and sc.CID = '01'
order by sc.score desc
--17、按平均成績從高到低顯示所有學(xué)生的所有課程的成績以及平均成績
--17.1 SQL 2000?靜態(tài)
select a.SID?學(xué)生編號?, a.Sname?學(xué)生姓名?,
max(case c.Cname when '語文' then b.score else null end) ?語文?,
max(case c.Cname when '數(shù)學(xué)' then b.score else null end) ?數(shù)學(xué)?,
max(case c.Cname when '英語' then b.score else null end) ?英語?,
cast(avg(b.score) as decimal(18,2))?平均分
from Student a
left join SC b on a.SID = b.SID
left join Course c on b.CID = c.CID
group by a.SID , a.Sname
order by?平均分?desc
max 等這些聚合函數(shù)是在 group by 之后才進行計算的
--17.2 SQL 2000?動態(tài)
declare @sql nvarchar(4000)
set @sql = 'select a.SID ' + '學(xué)生編號' + ' , a.Sname ' + '學(xué)生姓名'
select @sql = @sql + ',max(case c.Cname when '''+Cname+''' then b.score else null end) ?'+Cname+' '
from (select distinct Cname from Course) as t
set @sql = @sql + ' , cast(avg(b.score) as decimal(18,2)) ' + '平均分' + ' from Student a left join SC b on a.SID = b.SID left join Course c on b.CID = c.CID
group by a.SID , a.Sname order by ' + '平均分' + ' desc'
exec(@sql)
--18侵佃、查詢各科成績最高分、最低分和平均分：以如下形式顯示：課程ID首有，課程name井联，最高分烙常，最低分，平均分驼鞭，及格率挣棕，中等率洛心，優(yōu)良率，優(yōu)秀率
--及格為>=60法严，中等為：70-80渐夸，優(yōu)良為：80-90瘟忱，優(yōu)秀為：>=90
--方法1
select?m.CID??課程編號?, m.Cname??課程名稱?,
max(n.score)??最高分?,
min(n.score)??最低分?,
cast(avg(n.score)?as?decimal(18,2))??平均分?,
cast((select?count(1)?from?SC?where?CID?=?m.CID?and?score?>=60)*100.0/?(select?count(1)?from?SC?where?CID?=?m.CID)?as?decimal(18,2))??及格率?,
cast((select?count(1)?from?SC?where?CID?=?m.CID?and?score?>=70and?score?<80)*100.0/?(select?count(1)?from?SC?where?CID?=?m.CID)?as?decimal(18,2))??中等率?,
cast((select?count(1)?from?SC?where?CID?=?m.CID?and?score?>=80and?score?<90)*100.0/?(select?count(1)?from?SC?where?CID?=?m.CID)?as?decimal(18,2))??優(yōu)良率?,
cast((select?count(1)?from?SC?where?CID?=?m.CID?and?score?>=90)*100.0/?(select?count(1)?from?SC?where?CID?=?m.CID)?as?decimal(18,2))??優(yōu)秀率
from?Course m , SC n
where?m.CID?=?n.CID
group?by?m.CID , m.Cname
order?by?m.CID
--方法2
select?m.CID??課程編號?, m.Cname??課程名稱?,
(select?max(score)?from?SC?where?CID?=?m.CID)??最高分?,
(select?min(score)?from?SC?where?CID?=?m.CID)??最低分?,
(select?cast(avg(score)?as?decimal(18,2))?from?SC?where?CID?=?m.CID)??平均分?,
cast((select?count(1)?from?SC?where?CID?=?m.CID?and?score?>=60)*100.0/?(select?count(1)?from?SC?where?CID?=?m.CID)?as?decimal(18,2))??及格率,
cast((select?count(1)?from?SC?where?CID?=?m.CID?and?score?>=70and?score?<80)*100.0/?(select?count(1)?from?SC?where?CID?=?m.CID)?as?decimal(18,2))??中等率?,
cast((select?count(1)?from?SC?where?CID?=?m.CID?and?score?>=80and?score?<90)*100.0/?(select?count(1)?from?SC?where?CID?=?m.CID)?as?decimal(18,2))??優(yōu)良率?,
cast((select?count(1)?from?SC?where?CID?=?m.CID?and?score?>=90)*100.0/?(select?count(1)?from?SC?where?CID?=?m.CID)?as?decimal(18,2))??優(yōu)秀率
from?Course m
order?by?m.CID
--19韩肝、按各科成績進行排序哀峻，并顯示排名
--19.1 sql 2000用子查詢完成
--Score重復(fù)時保留名次空缺
select?t.*?, px?=?(select?count(1)?from?SC?where?CID?=?t.CID?and?score?>?t.score)?+1from?sc t?order?by?t.cid , px
--Score重復(fù)時合并名次
select?t.*?, px?=?(select?count(distinct?score)?from?SC?where?CID?=?t.CID?and?score?>=?t.score)?from?sc t?order?by?t.cid , px
--19.2 sql 2005用rank,DENSE_RANK完成
--Score重復(fù)時保留名次空缺(rank完成)
select?t.*?, px?=?rank()?over(partition?by?cid?order?by?score?desc)?from?sc t?order?by?t.CID , px
--Score重復(fù)時合并名次(DENSE_RANK完成)
select?t.*?, px?=?DENSE_RANK()?over(partition?by?cid?order?by?score?desc)?from?sc t?order?by?t.CID , px
--20催蝗、查詢學(xué)生的總成績并進行排名
--20.1 查詢學(xué)生的總成績
select?m.SID??學(xué)生編號??,
m.Sname??學(xué)生姓名??,
isnull(sum(score),0)??總成績
from?Student m?left?join?SC n?on?m.SID?=?n.SID
group?by?m.SID , m.Sname
order?by??總成績??desc
--20.2 查詢學(xué)生的總成績并進行排名丙号，sql 2000用子查詢完成，分總分重復(fù)時保留名次空缺和不保留名次空缺兩種怀薛。
select?t1.*?, px?=?(select?count(1)?from
(
select?m.SID??學(xué)生編號??,
m.Sname??學(xué)生姓名??,
isnull(sum(score),0)??總成績
from?Student m?left?join?SC n?on?m.SID?=?n.SID
group?by?m.SID , m.Sname
) t2?where?總成績?>?t1.總成績)?+1from
(
select?m.SID??學(xué)生編號??,
m.Sname??學(xué)生姓名??,
isnull(sum(score),0)??總成績
from?Student m?left?join?SC n?on?m.SID?=?n.SID
group?by?m.SID , m.Sname
) t1
order?by?px
select?t1.*?, px?=?(select?count(distinct?總成績)?from
(
select?m.SID??學(xué)生編號??,
m.Sname??學(xué)生姓名??,
isnull(sum(score),0)??總成績
from?Student m?left?join?SC n?on?m.SID?=?n.SID
group?by?m.SID , m.Sname
) t2?where?總成績?>=?t1.總成績)?from
(
select?m.SID??學(xué)生編號??,
m.Sname??學(xué)生姓名??,
isnull(sum(score),0)??總成績
from?Student m?left?join?SC n?on?m.SID?=?n.SID
group?by?m.SID , m.Sname
) t1
order?by?px
--20.3 查詢學(xué)生的總成績并進行排名迂苛，sql 2005用rank,DENSE_RANK完成就漾，分總分重復(fù)時保留名次空缺和不保留名次空缺兩種抑堡。
select?t.*?, px?=?rank()?over(order?by??總成績??desc)?from
(
select?m.SID??學(xué)生編號??,
m.Sname??學(xué)生姓名??,
isnull(sum(score),0)??總成績
from?Student m?left?join?SC n?on?m.SID?=?n.SID
group?by?m.SID , m.Sname
) t
order?by?px
select?t.*?, px?=?DENSE_RANK()?over(order?by??總成績??desc)?from
(
select?m.SID??學(xué)生編號??,
m.Sname??學(xué)生姓名??,
isnull(sum(score),0)??總成績
from?Student m?left?join?SC n?on?m.SID?=?n.SID
group?by?m.SID , m.Sname
) t
order?by?px
--21偎漫、查詢不同老師所教不同課程平均分從高到低顯示
select?m.TID , m.Tname ,?cast(avg(o.score)?as?decimal(18,2)) avg_score
from?Teacher m , Course n , SC o
where?m.TID?=?n.TID?and?n.CID?=?o.CID
group?by?m.TID , m.Tname
order?by?avg_score?desc
--22象踊、查詢所有課程的成績第2名到第3名的學(xué)生信息及該課程成績
--22.1 sql 2000用子查詢完成
--Score重復(fù)時保留名次空缺
select?*?from?(select?t.*?, px?=?(select?count(1)?from?SC?where?CID?=?t.CID?and?score?>?t.score)?+1from?sc t) m?where?px?between2and3order?by?m.cid , m.px
--Score重復(fù)時合并名次
select?*?from?(select?t.*?, px?=?(select?count(distinct?score)?from?SC?where?CID?=?t.CID?and?score?>=?t.score)?from?sc t) m?where?px?between2and3order?by?m.cid , m.px
--22.2 sql 2005用rank,DENSE_RANK完成
--Score重復(fù)時保留名次空缺(rank完成)
select?*?from?(select?t.*?, px?=?rank()?over(partition?by?cid?order?by?score?desc)?from?sc t) m?where?px?between2and3order?by?m.CID , m.px
--Score重復(fù)時合并名次(DENSE_RANK完成)
select?*?from?(select?t.*?, px?=?DENSE_RANK()?over(partition?by?cid?order?by?score?desc)?from?sc t) m?where?px?between2and3order?by?m.CID , m.px
--23、統(tǒng)計各科成績各分?jǐn)?shù)段人數(shù)：課程編號,課程名稱, 100-85 , 85-70 , 70-60 , 0-60 及所占百分比
--23.1 統(tǒng)計各科成績各分?jǐn)?shù)段人數(shù)：課程編號,課程名稱, 100-85 , 85-70 , 70-60 , 0-60
--橫向顯示
select?Course.CID??課程編號??, Cname?as??課程名稱??,
sum(case?when?score?>=85then1else0end)??85-100?,
sum(case?when?score?>=70and?score?<85then1else0end)??70-85?,
sum(case?when?score?>=60and?score?<70then1else0end)??60-70?,
sum(case?when?score?<60then1else0end)??0-60
from?sc , Course
where?SC.CID?=?Course.CID
group?by?Course.CID , Course.Cname
order?by?Course.CID
--縱向顯示1(顯示存在的分?jǐn)?shù)段)
select?m.CID??課程編號??, m.Cname??課程名稱??, 分?jǐn)?shù)段?=?(
case?when?n.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) 數(shù)量
from?Course m , sc n
where?m.CID?=?n.CID
group?by?m.CID , m.Cname , (
case?when?n.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)
order?by?m.CID , m.Cname , 分?jǐn)?shù)段
--縱向顯示2(顯示存在的分?jǐn)?shù)段曼验，不存在的分?jǐn)?shù)段用0顯示)
select?m.CID??課程編號??, m.Cname??課程名稱??, 分?jǐn)?shù)段?=?(
case?when?n.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) 數(shù)量
from?Course m , sc n
where?m.CID?=?n.CID
group?by?all?m.CID , m.Cname , (
case?when?n.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)
order?by?m.CID , m.Cname , 分?jǐn)?shù)段
--23.2 統(tǒng)計各科成績各分?jǐn)?shù)段人數(shù)：課程編號,課程名稱, 100-85 , 85-70 , 70-60 , <60 及所占百分比
--橫向顯示
select?m.CID 課程編號, m.Cname 課程名稱,
(select?count(1)?from?SC?where?CID?=?m.CID?and?score?<60)??0-60?,
cast((select?count(1)?from?SC?where?CID?=?m.CID?and?score?<60)*100.0/?(select?count(1)?from?SC?where?CID?=?m.CID)?as?decimal(18,2))??百分比?,
(select?count(1)?from?SC?where?CID?=?m.CID?and?score?>=60and?score?<70)??60-70?,
cast((select?count(1)?from?SC?where?CID?=?m.CID?and?score?>=60and?score?<70)*100.0/?(select?count(1)?from?SC?where?CID?=?m.CID)?as?decimal(18,2))??百分比?,
(select?count(1)?from?SC?where?CID?=?m.CID?and?score?>=70and?score?<85)??70-85?,
cast((select?count(1)?from?SC?where?CID?=?m.CID?and?score?>=70and?score?<85)*100.0/?(select?count(1)?from?SC?where?CID?=?m.CID)?as?decimal(18,2))??百分比?,
(select?count(1)?from?SC?where?CID?=?m.CID?and?score?>=85)??85-100?,
cast((select?count(1)?from?SC?where?CID?=?m.CID?and?score?>=85)*100.0/?(select?count(1)?from?SC?where?CID?=?m.CID)?as?decimal(18,2))??百分比
from?Course m
order?by?m.CID
--縱向顯示1(顯示存在的分?jǐn)?shù)段)
select?m.CID??課程編號??, m.Cname??課程名稱??, 分?jǐn)?shù)段?=?(
case?when?n.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) 數(shù)量 ,
cast(count(1)?*100.0/?(select?count(1)?from?sc?where?CID?=?m.CID)?as?decimal(18,2))??百分比
from?Course m , sc n
where?m.CID?=?n.CID
group?by?m.CID , m.Cname , (
case?when?n.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)
order?by?m.CID , m.Cname , 分?jǐn)?shù)段
--縱向顯示2(顯示存在的分?jǐn)?shù)段，不存在的分?jǐn)?shù)段用0顯示)
select?m.CID??課程編號??, m.Cname??課程名稱??, 分?jǐn)?shù)段?=?(
case?when?n.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) 數(shù)量 ,
cast(count(1)?*100.0/?(select?count(1)?from?sc?where?CID?=?m.CID)?as?decimal(18,2))??百分比
from?Course m , sc n
where?m.CID?=?n.CID
group?by?all?m.CID , m.Cname , (
case?when?n.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)
order?by?m.CID , m.Cname , 分?jǐn)?shù)段
--24、查詢學(xué)生平均成績及其名次
--24.1?查詢學(xué)生的平均成績并進行排名翼抠，sql 2000用子查詢完成丐膝，分平均成績重復(fù)時保留名次空缺和不保留名次空缺兩種偎肃。
select t1.* , px = (select count(1) from
(
select m.SID ?學(xué)生編號??,
m.Sname ?學(xué)生姓名??,
isnull(cast(avg(score) as decimal(18,2)),0) ?平均成績
from Student m left join SC n on m.SID = n.SID
group by m.SID , m.Sname
) t2 where?平均成績?> t1.平均成績) + 1 from
(
select m.SID ?學(xué)生編號??,
m.Sname ?學(xué)生姓名??,
isnull(cast(avg(score) as decimal(18,2)),0) ?平均成績
from Student m left join SC n on m.SID = n.SID
group by m.SID , m.Sname
) t1
order by px
select t1.* , px = (select count(distinct?平均成績) from
(
select m.SID ?學(xué)生編號??,
m.Sname ?學(xué)生姓名??,
isnull(cast(avg(score) as decimal(18,2)),0) ?平均成績
from Student m left join SC n on m.SID = n.SID
group by m.SID , m.Sname
) t2 where?平均成績?>= t1.平均成績) from
(
select m.SID ?學(xué)生編號??,
m.Sname ?學(xué)生姓名??,
isnull(cast(avg(score) as decimal(18,2)),0) ?平均成績
from Student m left join SC n on m.SID = n.SID
group by m.SID , m.Sname
) t1
order by px
--24.2?查詢學(xué)生的平均成績并進行排名，sql 2005用rank,DENSE_RANK完成，分平均成績重復(fù)時保留名次空缺和不保留名次空缺兩種料饥。
select t.* , px = rank() over(order by ?平均成績??desc) from
(
select m.SID ?學(xué)生編號??,
m.Sname ?學(xué)生姓名??,
isnull(cast(avg(score) as decimal(18,2)),0) ?平均成績
from Student m left join SC n on m.SID = n.SID
group by m.SID , m.Sname
) t
order by px
select t.* , px = DENSE_RANK() over(order by ?平均成績??desc) from
(
select m.SID ?學(xué)生編號??,
m.Sname ?學(xué)生姓名??,
isnull(cast(avg(score) as decimal(18,2)),0) ?平均成績
from Student m left join SC n on m.SID = n.SID
group by m.SID , m.Sname
) t
order by px
--25、查詢各科成績前三名的記錄
--25.1?分?jǐn)?shù)重復(fù)時保留名次空缺
select m.* , n.CID , n.score from Student m, SC n where m.SID = n.SID and n.score in
(select top 3 score from sc where CID = n.CID order by score desc) order by n.CID , n.score desc
--25.2?分?jǐn)?shù)重復(fù)時不保留名次空缺朱监，合并名次
--sql 2000用子查詢實現(xiàn)
select * from (select t.* , px = (select count(distinct score) from SC where CID = t.CID and score >= t.score) from sc t) m where px between 1 and 3 order by m.Cid , m.px
--sql 2005用DENSE_RANK實現(xiàn)
select * from (select t.* , px = DENSE_RANK() over(partition by Cid order by score desc) from sc t) m where px between 1 and 3 order by m.CID , m.px
--26岸啡、查詢每門課程被選修的學(xué)生數(shù)
select Cid , count(SID)?學(xué)生數(shù)??from sc group by CID
--27、查詢出只有兩門課程的全部學(xué)生的學(xué)號和姓名
select Student.SID , Student.Sname
from Student , SC
where Student.SID = SC.SID
group by Student.SID , Student.Sname
having count(SC.CID) = 2
order by Student.SID
--28赫编、查詢男生、女生人數(shù)
select count(Ssex) as?男生人數(shù)?from Student where Ssex = N'男'
select count(Ssex) as?女生人數(shù)?from Student where Ssex = N'女'
select sum(case when Ssex = N'男' then 1 else 0 end) ?男生人數(shù)?,sum(case when Ssex = N'女' then 1 else 0 end) ?女生人數(shù)??from student
select case when Ssex = N'男' then N'男生人數(shù)' else N'女生人數(shù)' end ?男女情況??, count(1) ?人數(shù)??from student group by case when Ssex = N'男' then N'男生人數(shù)' else N'女生人數(shù)' end
--29沛慢、查詢名字中含有"風(fēng)"字的學(xué)生信息
select * from student where sname like N'%風(fēng)%'
select * from student where charindex(N'風(fēng)' , sname) > 0
--30赡若、查詢同名同性學(xué)生名單，并統(tǒng)計同名人數(shù)
select Sname ?學(xué)生姓名?, count(*) ?人數(shù)??from Student group by Sname having count(*) > 1
--31团甲、查詢1990年出生的學(xué)生名單(注：Student表中Sage列的類型是datetime)
select * from Student where year(sage) = 1990
select * from Student where datediff(yy,sage,'1990-01-01') = 0
select * from Student where datepart(yy,sage) = 1990
select * from Student where convert(varchar(4),sage,120) = '1990'
--32逾冬、查詢每門課程的平均成績，結(jié)果按平均成績降序排列躺苦，平均成績相同時身腻，按課程編號升序排列
select m.CID , m.Cname , cast(avg(n.score) as decimal(18,2)) avg_score
from Course m, SC n
where m.CID = n.CID
group by m.CID , m.Cname
order by avg_score desc, m.CID asc
--33、查詢平均成績大于等于85的所有學(xué)生的學(xué)號匹厘、姓名和平均成績
select a.SID , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_score
from Student a , sc b
where a.SID = b.SID
group by a.SID , a.Sname
having cast(avg(b.score) as decimal(18,2)) >= 85
order by a.SID
--34嘀趟、查詢課程名稱為"數(shù)學(xué)"，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)
select sname , score
from Student , SC , Course
where SC.SID = Student.SID and SC.CID = Course.CID and Course.Cname = N'數(shù)學(xué)' and score < 60
--35愈诚、查詢所有學(xué)生的課程及分?jǐn)?shù)情況她按；
select Student.* , Course.Cname , SC.CID , SC.score
from Student, SC , Course
where Student.SID = SC.SID and SC.CID = Course.CID
order by Student.SID , SC.CID
--36、查詢?nèi)魏我婚T課程成績在70分以上的姓名炕柔、課程名稱和分?jǐn)?shù)酌泰；
select Student.* , Course.Cname , SC.CID , SC.score
from Student, SC , Course
where Student.SID = SC.SID and SC.CID = Course.CID and SC.score >= 70
order by Student.SID , SC.CID
--37、查詢不及格的課程
select Student.* , Course.Cname , SC.CID , SC.score
from Student, SC , Course
where Student.SID = SC.SID and SC.CID = Course.CID and SC.score < 60
order by Student.SID , SC.CID
--38匕累、查詢課程編號為01且課程成績在80分以上的學(xué)生的學(xué)號和姓名陵刹；
select Student.* , Course.Cname , SC.CID , SC.score
from Student, SC , Course
where Student.SID = SC.SID and SC.CID = Course.CID and SC.CID = '01' and SC.score >= 80
order by Student.SID , SC.CID
--39、求每門課程的學(xué)生人數(shù)
select Course.CID , Course.Cname , count(*) ?學(xué)生人數(shù)
from Course , SC
where Course.CID = SC.CID
group by? Course.CID , Course.Cname
order by Course.CID , Course.Cname
--40欢嘿、查詢選修"張三"老師所授課程的學(xué)生中，成績最高的學(xué)生信息及其成績
--40.1?當(dāng)最高分只有一個時
select top 1 Student.* , Course.Cname , SC.CID , SC.score
from Student, SC , Course , Teacher
where Student.SID = SC.SID and SC.CID = Course.CID and Course.TID = Teacher.TID and Teacher.Tname = N'張三'
order by SC.score desc
--40.2?當(dāng)最高分出現(xiàn)多個時
select Student.* , Course.Cname , SC.CID , SC.score
from Student, SC , Course , Teacher
where Student.SID = SC.SID and SC.CID = Course.CID and Course.TID = Teacher.TID and Teacher.Tname = N'張三' and
SC.score = (select max(SC.score) from SC , Course , Teacher where SC.CID = Course.CID and Course.TID = Teacher.TID and Teacher.Tname = N'張三')
--41羡宙、查詢不同課程成績相同的學(xué)生的學(xué)生編號辛辨、課程編號斗搞、學(xué)生成績
--方法1
select m.* from SC m ,(select CID , score from SC group by CID , score having count(1) > 1) n
where m.CID= n.CID and m.score = n.score order by m.CID , m.score , m.SID
--方法2
select m.* from SC m where exists (select 1 from (select CID , score from SC group by CID , score having count(1) > 1) n
where m.CID= n.CID and m.score = n.score) order by m.CID , m.score , m.SID
--42僻焚、查詢每門功成績最好的前兩名
select t.* from sc t where score in (select top 2 score from sc where CID = T.CID order by score desc) order by t.CID , t.score desc
--43、統(tǒng)計每門課程的學(xué)生選修人數(shù)（超過5人的課程才統(tǒng)計）架馋。要求輸出課程號和選修人數(shù)叉寂，查詢結(jié)果按人數(shù)降序排列勘纯，若人數(shù)相同钓瞭，按課程號升序排列
select Course.CID , Course.Cname , count(*) ?學(xué)生人數(shù)
from Course , SC
where Course.CID = SC.CID
group by? Course.CID , Course.Cname
having count(*) >= 5
order by ?學(xué)生人數(shù)??desc , Course.CID
--44堤结、檢索至少選修兩門課程的學(xué)生學(xué)號
select student.SID , student.Sname
from student , SC
where student.SID = SC.SID
group by student.SID , student.Sname
having count(1) >= 2
order by student.SID
--45霍殴、查詢選修了全部課程的學(xué)生信息
--方法1?根據(jù)數(shù)量來完成
select student.* from student where SID in
(select SID from sc group by SID having count(1) = (select count(1) from course))
--方法2?使用雙重否定來完成
select t.* from student t where t.SID not in
(
select distinct m.SID from
(
select SID , CID from student , course
) m where not exists (select 1 from sc n where n.SID = m.SID and n.CID = m.CID)
)
--方法3?使用雙重否定來完成
select t.* from student t where not exists(select 1 from
(
select distinct m.SID from
(
select SID , CID from student , course
) m where not exists (select 1 from sc n where n.SID = m.SID and n.CID = m.CID)
) k where k.SID = t.SID
)
--46来庭、查詢各學(xué)生的年齡
--46.1?只按照年份來算
select * , datediff(yy , sage , getdate()) ?年齡??from student
--46.2?按照出生日期來算月弛，當(dāng)前月日?<?出生年月的月日則，年齡減一
select * , case when right(convert(varchar(10),getdate(),120),5) < right(convert(varchar(10),sage,120),5) then datediff(yy , sage , getdate()) - 1 else datediff(yy , sage , getdate()) end ?年齡??from student
--47厉萝、查詢本周過生日的學(xué)生
select * from student where datediff(week,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = 0
--48谴垫、查詢下周過生日的學(xué)生
select * from student where datediff(week,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = -1
--49乳怎、查詢本月過生日的學(xué)生
select * from student where datediff(mm,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = 0
--50蚪缀、查詢下月過生日的學(xué)生
select * from student where datediff(mm,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = -1
drop table? Student,Course,Teacher,SC
