実験四

3868 ワード

--1.            ;

select * from student

go

--2.     “CS”        ;

select * from student

where sdept='cs'

go

--3.     “CS”       19 21        、  ;

select sno,sname

from student

where sdept='cs' and sage not between 19 and 21

go

--4.         ;

select max(sage)

from student

go

--5.     “CS”        ,     、  ;

select sno,sname

from student

where sdept='cs' and sage=(select max(sage)

                           from student

                           where sdept='cs'

                          )

go

--6.              ,     、  ;

select sno,sname,sdept

from student s1

where sage=(select max(sage)

            from student

            where sdept=s1.sdept

           )

go

--7.     “CS”      ;

select count(*)

from student

where sdept='cs'

go



--8.            ,       ;

select sdept,count(sno) as sm

from student

group by sdept

order by sm

go



**********************************************

select sdept,count(*) as dd
from student
group by sdept
order by dd
go
--9. , ; select sdept,count(sage) as sm from student group by sdept order by sm desc go

select sdept,avg(sage)dd
from student
group by sdept
order by dd desc
go

--10. ; select cno,cname from course go

select cname from course
go


--11. ; select cname,ccredit from course where cpno is null go --12. ;//select * from course select sum(ccredit) from course where cpno is null go --13. 、 ; select sno,count(sc.cno),count(ccredit),avg(grade) from sc,course where sc.cno=course.cno group by sno go --14. ; select cno,count(sno),avg(grade) from sc group by cno go --15. 85 , , ; select sdept,sc.sno,avg(grade) from sc,student where sc.sno=student.sno group by sc.sno,sdept having avg(grade)>85 order by avg(grade)
go





--16. “1” “2” ; select distinct sc.sno,sname from sc,student where sc.sno=student.sno and (sc.cno='1' or sc.cno='2') go --17. “1” “2” ; select distinct sc.sno,sname from sc,student where sc.sno=student.sno and sc.cno='1' and sc.sno in(select sno from sc where cno='2') go

**********************************
7、 “CS” ;

update sc set grade=0
where sno in
( selsct sno
from student
where sdept='cs')
go


*****************************************



--18. “ ” 60 、 ; select sc.sno,sname,grade from sc,student,course where sc.sno=student.sno and sc.cno=course.cno and course.cname=' ' and grade<60 go
--19. ( : , , , , ); selsct sc.sno,sname,sc.cno,cname,grade
from student,sc,course
where student.sno=sc.sno and course.cno=sc.cno
go


--20 ; select sno,sname,sage from student where sno not in(select distinct sno from sc) go
select *
from student
where sno not in(select distinct sno from sc)
go

--21. 3 ; select sno from sc group by sno having count(cno)>=3 go --22. 80 ; select distinct sno from sc where grade>80 go --23. 80 ; select distinct sno from sc where sno not in(select distinct sno from sc where grade<80) go --24. 80 ; select sno from sc group by sno having avg(grade)>80 go