数据库查询小案例

建表语句

student表：

create table student
(sno char(9) primary key,
sname char(20) unique,
ssex char(2),
sage smallint,
sdept char(20)
);

course表：

create table course
(cno char(4) primary key,
cname char(9) not null,
cpno char(4),
ccredit smallint,
foreign key(cpno) references course(cno)
);

sc表：

create table sc
(sno char(9),
cno char(4),
grade smallint,
primary key(sno,cno),
foreign key(sno) references student(sno),
foreign key(cno) references course(cno)
)

1.查询选修了“数据库”课程的学生学号，姓名，选课数量.

select student.sno as 学号,student.sname as 姓名,COUNT(sc.cno) as 选课数量 from
(student join sc on student.sno=sc.sno)
where student.sno in
    (
        select sno from sc where cno in         
        (        
            select cno from course where cname='数据库'        
        )    
    )group by student.sno,student.sname;

2.查询没有选“数据库”这门课、选了“VB”课程的同学的学号，姓名.

select sno,sname from student
where sno in (select sno from SC where cno=(select cno from course where cname='VB')
and cno!=(select cno from course where cname='数据库')
 )

3.查询选修了“VB”课程、选课总学分超过30的同学的人数.

select COUNT(distinct sno) from sc
where sno in (select sno from sc where cno in (select cno from course where cname='数学'))
and sno in
(select student.sno from student join sc on student.sno=sc.sno join course on sc.cno=course.cno
group by student.sno having SUM(ccredit)>7
)

4.查询考试不及格3科以上（包含3科），选课总学分超过20的学生的学号、姓名.

select sno,sname from student where sno in(
select student.sno from (student join sc on student.sno=sc.sno join course on 
sc.cno=course.cno)
 group by student.sno having SUM(ccredit)>20) 
 and  sno in
 (select sno from sc  where grade >= 60 group by sno having COUNT(cno)>3)

5.查询只选修了“VB”、“数据库”这两门课的同学的数量.

select count(distinct sno) from sc where cno in (select cno from course where cname='数据库' or cname='VB')

6.查询选修3门课、平均成绩在85分以上有那些同学.

select sno,sname from student where sno in (select sno from sc group by sno having count(cno)=3 and avg(grade)>85)