数据库实验-数据查询语言

 select * from S;

select * from S where Sdept =’CS’;

select Sno, Sname from S 

where Sdept = ‘CS’ and Sage not

Between 19 and 21;

select max(Sage) from S;

select * from S where

Sage = (select max(Sage) from S where Sdept = 'CS');   

  select Sno, Sname from S 
  where Sage in
 (select max(Sage) from S group by Sdept);

 select count(*) from S;

select Sdept, count(*) from S group by Sdept

Order by count(*) asc;

select Sdept, avg(Sage) as AVG from 
group by Sdept 
order by AVG desc;

select Cname from C;

select Cname, Ccredit from C where Cpno is null;

select sum(Ccredit) from C 
where Cpno is null;

select Sno ,count(SC.Cno), avg(Grade),Sum(C.Credit)
from SC,C 
where SC.Cno = C.Cno 
group by Sno;

select Cno, count(Sno) as num, avg(Grade) as Avg 
from SC
group by Cno;

select S.Sdept ,avg(Grade) as Avg , S.Sname as 'name'  from S
left join  SC on S.Sno = SC.Sno 
group by Sdept,S.Sname 
having avg(Grade) > 85 order by avg(Grade);

select S.Sno, S.Sname from S, SC
where (SC.Cno = 1 or SC.Cno = 2)
and S,Sno = SC.Sno group by S.Sno, S.Sname
order by S.Sno, S.Sname;

select S.Sno , S.Sname ,SC.Grade from S ,SC, C 
where C.Cname = '数据库' 
and Grade < 60 
and SC.Cno = C.Cno 
and SC.Sno = S.Sno;

select * from S,C,SC 
where S.Sno = SC.Sno 
and C.Cno = SC.Cno;

select * from S 
where Sno not in (select Sno from SC);

select Sno 
from SC 
group by Sno 
having count(Cno)>=3;

select S.Sno 
from S left join SC on SC.Sno = S.Sno 
where Grade > 80 and Grade is not null 
group by S.Sno 
order by S.Sno

select Sno, Sname from S 
where Sno in
(select Sno from SC group by Sno having avg(Grade) >=80);

select Sno, Sname from S 
where Sno in
(select Sno from SC group by Sno having avg(Grade) >=80);