実験四
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