SQL server 实验二 数据查询

/* 1 */
select *
from Student;

/* 2 */
select *
from Student
where Sdept='CS';

/* 3 */
select *
from Student
where Sage not between 19 and 21;

/* 4 */
select MAX(Sage)
from Student;

/* 5 */
select Sno,Sname
from Student
where Sage=(select MAX(Sage)
			from Student
			where Sdept='IS');

/* 6 */
select Sno,Sname,Sdept,Sage
from Student x
where x.Sage=(select MAX(Sage)
			from Student y
			where x.Sdept=y.Sdept);

/* 7 */
select Sdept,COUNT(*) People
from Student
where Sdept='MA'
group by Sdept;

/* 8 */
select Sdept,COUNT(*) People
from Student
group by Sdept
order by People asc;

/* 9 */
select Sdept,AVG(Sage) avg_sage
from Student
group by Sdept
order by avg_sage desc;

/* 10 */
select Cname
from Course;

/* 11 */
select Cname,Ccredit,Cterm
from Course
where Cterm='1';

/* 12 */
select Cterm,SUM(Ccredit)
from Course
where Cterm='2'
group by Cterm;

/* 13 */
select Student.Sno,Sname,COUNT(SC.Cno) as class,SUM(Course.Ccredit) as sum_credit,AVG(Grade) avg_grade
from Student,Course,SC
where Student.Sno=SC.Sno and Course.Cno=SC.Cno
group by Student.Sno,Sname;

/* 14 */
select Sno,Cno
from SC
where Cno='1'
union
select Sno,Cno
from SC
where Cno='2';

/* 15 */
select Student.Sno,Sname,Grade
from Student,SC,Course
where Student.Sno=SC.Sno and Course.Cno=SC.Cno and Cname='数据库' and Grade<60;

/* 16 */
select Student.Sno,Sname,SC.Cno,Course.Cname,Grade
from Student,Course,SC
where Student.Sno=SC.Sno and Course.Cno=SC.Cno;

/* 17 */
select *
from Student
where  Sno not in (	select Sno
					from SC);

/* 18 */
select Sno
from SC
group by Sno
having COUNT(*/*Sno 也可以*/)>=3;

/* 19 */
select distinct Sno
from SC
where Grade>80;