表连接练习

create table student(

sno char(5),

sname varchar(20),

sage int,

ssex char(2),

sdept char(2)

);

create table course(

cno char(2),

cname varchar(20),

cpno char(2),

ccredit int

);

create table sc(

sno char(5),

cno char(2),

grade int

);

insert into student values('98001','钱横',18,'男','cs');

insert into student values('98002','王林',19,'女','cs');

insert into student values('98003','李明',20,'男','is');

insert into student values('98004','赵三',16,'女','ma');

insert into course values('1','数据库系统','5',4);

insert into course values('2','数学分析',null,2);

insert into course values('3','信息系统导论','1',3);

insert into course values('4','操作系统原理','6',3);

insert into course values('5','数据结构','7',4);

insert into course values('6','数据处理基础', null,4);

insert into course values('7','C语言','6',3);

insert into sc values('98001','1',87);

insert into sc values('98001','2',67);

insert into sc values('98001','3',90);

insert into sc values('98002','2',95);

insert into sc values('98002','3',85);

表连接（两表连接）

1.查询显示学生的姓名、学号及其课程号、成绩

select sname,student.sno,cno,grade from student,sc

where student.sno=sc.sno

2.查询性别为男、课程成绩及格的学生姓名、学号及课程号、成绩

select sname,student.sno,cno,grade from student,sc

where student.sno=sc.sno

and student.ssex='男'

and sc.grade>60

3.查询学生的学号、姓名及平均成绩

select student.sno,sname,AVG(grade)from student,sc

where student.sno=sc.sno group by student.sno,sname

4.查询各科的平均成绩大于80分的学生的学号、姓名、平均成绩

select student.sno,sname,AVG(grade)from student,sc

where student.sno=sc.sno

group by student.sno,sname

having AVG(grade)>80

5.查询计算机系（cs）选修了不少于两门课程的学生的学号

select sname,student.sno from student,sc where student.sno=sc.sno and student.sdept='cs'

group by student.sno,sname

having COUNT(cno)>=2

表连接（三表连接）

1.查询各门课程的平均成绩，显示课程名、课程编号、平均成绩

select o,cname,AVG(grade)from sc,course

where o=o

group by o,cname

2.查询选修了课程名为“数据库系统”的学生学号、姓名和所在系

select student.sno,sname,sdept from student,course,sc where student.sno=sc.sno

and o=o

and ame='数据库系统'

3.查询学生姓名、学号及其课程名称、成绩等情况

select student.sno,sname,cname,grade from student,course,sc where student.sno=sc.sno

and o=o

4.查询课程中的先修课的名称

select o,course.cpno from sc,course

where course.cpno=o

子查询练习

练习.根据emp表和dept表写查询代码

1、列出至少有一个雇员的所有部门

select DNAME from DEPT