学生表s 学号sno 姓名sname 系名depart 姓名sex 出生日期ddate
课程表c 课程编号cno 课程名称cname
成绩表sc 学号sno 课程编号cno 成绩grade问1 根据s,c和sc 表。查询 计算机系 所有学生的成绩 要求输出 sno sname cname grade,并按成绩降序排列
问2 s表。查询各系学生数,要求输出depart,人数
课程表c 课程编号cno 课程名称cname
成绩表sc 学号sno 课程编号cno 成绩grade问1 根据s,c和sc 表。查询 计算机系 所有学生的成绩 要求输出 sno sname cname grade,并按成绩降序排列
问2 s表。查询各系学生数,要求输出depart,人数
select s.sno,s.sname,c.cname,sc.grade
from s,c,sc
where s.sno=sc.sno and c.cno=sc.cno
order by sc.grade desc
---------------------------------------
select depart,count(sno)
from s
group by depart
select sno,sname,cname,grade from s as a
left join (select sno from sc as ab left join c as b on ab.cno = b.cno ) as cc on cc.sno = a.sno
where b.cname = '计算机系'
left join (select sno from sc as ab left join c as b on ab.cno = b.cno ) as cc on cc.sno = a.sno
where b.cname = '计算机系'
desc ab.grade
第一问
1select s.sno , s.sname , c.cname , sc.grade from s , c, sc where s.sno = sc.sno and sc.cno = c.cno and s.depart = '计算机系' order by sc.grade desc2.
select depart , count(1) from s group by depart
select
depart as '院系',
count(sno) as '人数'
from s
group by depart
select distinct t1.sno,t1.sname,t2.cname,t2.grade from
(select s.sno,s.sname from s,sc where s.sno=sc.sno and s.depart='计算机') t1
left join (select c.cname,sc.sno,sc.grade from c,sc where c.cno=sc.cno) t2
on t1.sno=t2.sno order by t2.grade desc
---第二个问题
select depart,count(1) as c1 from s group by depart
from s,c,sc
where s.sno=sc.sno and c.cno=sc.cno
order by sc.grade desc
select depart,count(sno)as '人数'
from s
group by depart
select
s.sno,
s.sname,
c.cname,
sc.grade
from
s,c,sc
where
s.sno=sc.sno
and c.cno=sc.cno
order
by sc.grade desc
---------------------------------------
select
depart,
count(sno)
from
s
group by
depart