亚洲免费不卡_在线视频精品_国产尤物精品_久久久久网址_久久精品91_欧美va天堂在线_狠狠入ady亚洲精品_亚洲午夜精品福利_国产精品草草_午夜精品久久99蜜桃的功能介绍

select a.s# from (select s#,score from sc where c#='001') a,(select s#,score
from sc where c#='002') b
where a.score>b.score and a.s#=b.s#;

select s#,avg(score)
from sc
group by s# having avg(score) >60;

select student.s#,student.sname,count(sc.c#),sum(score)
from student left outer join sc on student.s#=sc.s#
group by student.s#,sname

select count(distinct(tname))
from teacher
where tname like '李%';

select student.s#,student.sname
from student
where s# not in (select distinct( sc.s#) from sc,course,teacher where sc.c#=course.c# and teacher.t#=course.t# and teacher.tname='葉平');

select student.s#,student.sname from student,sc where student.s#=sc.s# and sc.c#='001'and exists( select * from sc as sc_2 where sc_2.s#=sc.s# and sc_2.c#='002');

select s#,sname
from student
where s# in (select s# from sc ,course ,teacher where sc.c#=course.c# and teacher.t#=course.t# and teacher.tname='葉平' group by s# having count(sc.c#)=(select count(c#) from course,teacher where teacher.t#=course.t# and tname='葉平'));

select s#,sname from (select student.s#,student.sname,score ,(select score from sc sc_2 where sc_2.s#=student.s# and sc_2.c#='002') score2
from student,sc where student.s#=sc.s# and c#='001') s_2 where score2 <score;

select s#,sname
from student
where s# not in (select student.s# from student,sc where s.s#=sc.s# and score>60);

select student.s#,student.sname
from student,sc
where student.s#=sc.s# group by student.s#,student.sname having count(c#) <(select count(c#) from course);

select s#,sname from student,sc where student.s#=sc.s# and c# in select c# from sc where s#='1001';

select distinct sc.s#,sname
from student,sc
where student.s#=sc.s# and c# in (select c# from sc where s#='001');

update sc set score=(select avg(sc_2.score)
from sc sc_2
where sc_2.c#=sc.c# ) from course,teacher where course.c#=sc.c# and course.t#=teacher.t# and teacher.tname='葉平');

select s# from sc where c# in (select c# from sc where s#='1002')
group by s# having count(*)=(select count(*) from sc where s#='1002');

delect sc
from course ,teacher
where course.c#=sc.c# and course.t#= teacher.t# and tname='葉平';

insert sc select s#,'002',(select avg(score)
from sc where c#='002') from student where s# not in (select s# from sc where c#='002');

select s# as 學生id
,(select score from sc where sc.s#=t.s# and c#='004') as 數據庫
,(select score from sc where sc.s#=t.s# and c#='001') as 企業管理
,(select score from sc where sc.s#=t.s# and c#='006') as 英語
,count(*) as 有效課程數, avg(t.score) as 平均成績
from sc as t
group by s#
order by avg(t.score)

select l.c# as 課程id,l.score as 最高分,r.score as 最低分
from sc l ,sc as r
where l.c# = r.c# and
l.score = (select max(il.score)
from sc as il,student as im
where l.c# = il.c# and im.s#=il.s#
group by il.c#)
and
r.score = (select min(ir.score)
from sc as ir
where r.c# = ir.c#
group by ir.c#
);

select t.c# as 課程號,max(course.cname)as 課程名,isnull(avg(score),0) as 平均成績
,100 * sum(case when isnull(score,0)>=60 then 1 else 0 end)/count(*) as 及格百分數
from sc t,course
where t.c#=course.c#
group by t.c#
order by 100 * sum(case when isnull(score,0)>=60 then 1 else 0 end)/count(*) desc

select sum(case when c# ='001' then score else 0 end)/sum(case c# when '001' then 1 else 0 end) as 企業管理平均分
,100 * sum(case when c# = '001' and score >= 60 then 1 else 0 end)/sum(case when c# = '001' then 1 else 0 end) as 企業管理及格百分數
,sum(case when c# = '002' then score else 0 end)/sum(case c# when '002' then 1 else 0 end) as 馬克思平均分
,100 * sum(case when c# = '002' and score >= 60 then 1 else 0 end)/sum(case when c# = '002' then 1 else 0 end) as 馬克思及格百分數
,sum(case when c# = '003' then score else 0 end)/sum(case c# when '003' then 1 else 0 end) as uml平均分
,100 * sum(case when c# = '003' and score >= 60 then 1 else 0 end)/sum(case when c# = '003' then 1 else 0 end) as uml及格百分數
,sum(case when c# = '004' then score else 0 end)/sum(case c# when '004' then 1 else 0 end) as 數據庫平均分
,100 * sum(case when c# = '004' and score >= 60 then 1 else 0 end)/sum(case when c# = '004' then 1 else 0 end) as 數據庫及格百分數
from sc

select max(z.t#) as 教師id,max(z.tname) as 教師姓名,c.c# as 課程ｉｄ,max(c.cname) as 課程名稱,avg(score) as 平均成績
from sc as t,course as c ,teacher as z
where t.c#=c.c# and c.t#=z.t#
group by c.c#
order by avg(score) desc

select distinct top 3
sc.s# as 學生學號,
student.sname as 學生姓名 ,
t1.score as 企業管理,
t2.score as 馬克思,
t3.score as uml,
t4.score as 數據庫,
isnull(t1.score,0) + isnull(t2.score,0) + isnull(t3.score,0) + isnull(t4.score,0) as 總分
from student,sc left join sc as t1
on sc.s# = t1.s# and t1.c# = '001'
left join sc as t2
on sc.s# = t2.s# and t2.c# = '002'
left join sc as t3
on sc.s# = t3.s# and t3.c# = '003'
left join sc as t4
on sc.s# = t4.s# and t4.c# = '004'
where student.s#=sc.s# and
isnull(t1.score,0) + isnull(t2.score,0) + isnull(t3.score,0) + isnull(t4.score,0)
not in
(select
distinct
top 15 with ties
isnull(t1.score,0) + isnull(t2.score,0) + isnull(t3.score,0) + isnull(t4.score,0)
from sc
left join sc as t1
on sc.s# = t1.s# and t1.c# = 'k1'
left join sc as t2
on sc.s# = t2.s# and t2.c# = 'k2'
left join sc as t3
on sc.s# = t3.s# and t3.c# = 'k3'
left join sc as t4
on sc.s# = t4.s# and t4.c# = 'k4'
order by isnull(t1.score,0) + isnull(t2.score,0) + isnull(t3.score,0) + isnull(t4.score,0) desc);

select sc.c# as 課程id, cname as 課程名稱
,sum(case when score between 85 and 100 then 1 else 0 end) as [100 - 85]
,sum(case when score between 70 and 85 then 1 else 0 end) as [85 - 70]
,sum(case when score between 60 and 70 then 1 else 0 end) as [70 - 60]
,sum(case when score < 60 then 1 else 0 end) as [60 -]
from sc,course
where sc.c#=course.c#
group by sc.c#,cname;

select 1+(select count( distinct 平均成績)
from (select s#,avg(score) as 平均成績
from sc
group by s#
) as t1
where 平均成績 > t2.平均成績) as 名次,
s# as 學生學號,平均成績
from (select s#,avg(score) 平均成績
from sc
group by s#
) as t2
order by 平均成績 desc;

select t1.s# as 學生id,t1.c# as 課程id,score as 分數
from sc t1
where score in (select top 3 score
from sc
where t1.c#= c#
order by score desc
)
order by t1.c#;

select c#,count(s#) from sc group by c#;

select sc.s#,student.sname,count(c#) as 選課數
from sc ,student
where sc.s#=student.s# group by sc.s# ,student.sname having count(c#)=1;

select count(ssex) as 男生人數 from student group by ssex having ssex='男';
select count(ssex) as 女生人數 from student group by ssex having ssex='女'；

select sname from student where sname like '張%';

select sname,count(*) from student group by sname having count(*)>1;

 

 

select sname, convert(char (11),datepart(year,sage)) as age
from student
where convert(char(11),datepart(year,sage))='1981';

select c#,avg(score) from sc group by c# order by avg(score),c# desc ;

select sname,sc.s# ,avg(score)
from student,sc
where student.s#=sc.s# group by sc.s#,sname having avg(score)>85;

select sname,isnull(score,0)
from student,sc,course
where sc.s#=student.s# and sc.c#=course.c# and course.cname='數據庫'and score <60;

select sc.s#,sc.c#,sname,cname
from sc,student,course
where sc.s#=student.s# and sc.c#=course.c# ;

select distinct student.s#,student.sname,sc.c#,sc.score
from student,sc
where sc.score>=70 and sc.s#=student.s#;

select c# from sc where scor e <60 order by c# ;

select sc.s#,student.sname from sc,student where sc.s#=student.s# and score>80 and c#='003';

select count(*) from sc;

select student.sname,score
from student,sc,course c,teacher
where student.s#=sc.s# and sc.c#=c.c# and c.t#=teacher.t# and teacher.tname='葉平' and sc.score=(select max(score)from sc where c#=c.c# );

select count(*) from sc group by c#;

select distinct a.s#,b.score from sc a ,sc b where a.score=b.score and a.c# <>b.c# ;

select t1.s# as 學生id,t1.c# as 課程id,score as 分數
from sc t1
where score in (select top 2 score
from sc
where t1.c#= c#
order by score desc
)
order by t1.c#;

select c# as 課程號,count(*) as 人數
from sc
group by c#
order by count(*) desc,c#

select s#
from sc
group by s#
having count(*) > = 2

select c#,cname
from course
where c# in (select c# from sc group by c#)

select sname from student where s# not in (select s# from course,teacher,sc where course.t#=teacher.t# and sc.c#=course.c# and tname='葉平');

select s#,avg(isnull(score,0)) from sc where s# in (select s# from sc where score <60 group by s# having count(*)>2)group by s#;

select s# from sc where c#='004'and score <60 order by score desc;

delete from sc where s#='001'and c#='001';