【 二 】表连接
1、显示每个学生所属的班级名称及姓名
--select classname,stuname from class inner join student on class.classno=student.classno
2、显示所有与你同姓的学生所属的班级名称及姓名
--select classname,stuname from class inner join student on class.classno=student.classno where stuname like '钟%'
3、显示网络班所有与你同姓的学生所属的班级名称及姓名
--select classname,stuname from class inner join student on class.classno=student.classno where stuname like '钟%' and classname like '%网络%'
4、显示每个班级所属的院系编号、院糸名称及班级名称
--select department.departno,departname,classname from department inner join class on class.departno=department.departno
5、显示每个学生所属的院系名称、班级名称、学号及姓名
--select departname,classname,stuno,stuname from class inner join department on department.departno=class.departno inner join student on class.classno=student.classno
6、显示信息科技系每个学生所属的院系名称、班级名称、学号及姓名
--select departname,classname,stuno,stuname from class inner join department on department.departno=class.departno inner join student on class.classno=student.classno where departname like '信息科技系'
7、显示信息科技系每个与你同姓学生所属的院系名称、班级名称、学号及姓名
--select departname,classname,stuno,stuname from class inner join department on department.departno=class.departno inner join student on class.classno=student.classno where stuname like '钟%'
8、显示每个学生所属的院系编号、院系名称、班级编号、班级名称、学号及姓名六个字段
--select department.departno,departname,class.classno,classname,stuno,stuname from class inner join department on department.departno=class.departno inner join student on class.classno=student.classno
9、显示院系编号为的院系的学生的院系名称、班级编号、班级名称、学号及姓名五个字段
--select departname,class.classno,classname,stuno,stuname from class inner join department on department.departno=class.departno inner join student on class.classno=student.classno where department.departno = '01'
10、显示每个学生的班级名称、学号、姓名、选修的课程号
--select classname,student.stuno,stuname,couno from student inner join stucou on student.stuno=stucou.stuno inner join class on class.classno=student.classno
11、显示所有选了课程的学生的班级名称、学号、姓名、选修的课程号
--select classname,student.stuno,stuname,couno from student inner join stucou on student.stuno=stucou.stuno inner join class on class.classno=student.classno where couno like '002'
12、显示所有选了002、005、008三门课程的学生的班级名称、学号、姓名、选修的课程号
--select classname,student.stuno,stuname,couno from student inner join stucou on student.stuno=stucou.stuno inner join class on class.classno=student.classno where couno in ('002','005','008')
13、显示所有选了、、三门课程的学生的班级名称、学号、姓名、选修的课程号，
------查询结果先按课程号排序，课程号相同的再按学生姓名排序
--select classname,stucou.stuno,stuname,couno from student inner join stucou on student.stuno=stucou.stuno inner join class on class.classno=student.classno where couno in ('002','005','008') order by couno,stuname
14、显示“00电子商务”班选修了课程002的学生的班级名称、学号、姓名、选修的课程编号
--select classname,student.stuno,stuname,couno from student inner join stucou on student.stuno=stucou.stuno inner join class on class.classno=student.classno where couno like '002' and classname like '00电子商务'
【 五 】
1、显示每个学生所属的院系名称、班级编号、班级名称、学号、姓名、所选课程编号，按课程号排序
--select departname,class.classno,classname,student.stuno,stuname,couno from department inner join class on department.departno=class.departno inner join student on class.classno=student.classno inner join stucou on student.stuno=stucou.stuno order by couno
2、显示每个学生所属的院系名称、班级编号、班级名称、学号、姓名、所选课程名称，按课程名称排序
--select departname,class.classno,classname,student.stuno,stuname,couname from department inner join class on department.departno=class.departno inner join student on class.classno=student.classno inner join stucou on student.stuno=stucou.stuno inner join course on stucou.couno=course.couno order by couname
3、显示每个学生所属的院系名称、班级编号、班级名称、学号、姓名、所选课程名称、任课老师，按课程名称排序
--select departname,class.classno,classname,student.stuno,stuname,couname,teacher from department inner join class on department.departno=class.departno inner join student on class.classno=student.classno inner join stucou on student.stuno=stucou.stuno inner join course on stucou.couno=course.couno order by couname
4、显示院系编号为的每个学生所属的院系名称、班级编号、班级名称、学号、姓名、所选课程名称、任课老师，按课程名称排序
--select departname,class.classno,classname,student.stuno,stuname,couname,teacher fromdepartment inner join class on department.departno=class.departno inner join student on class.classno=student.classno inner join stucou on student.stuno=stucou.stuno inner join course on stucou.couno=course.couno where department.departno='01' order by couname
5、显示选修了课程的学生所属的院系名称、班级编号、班级名称、学号、姓名、所选课程编号，按志愿号排序
--select departname,class.classno,classname,student.stuno,stuname,couno from department inner join class on department.departno=class.departno inner join student on class.classno=student.classno inner join stucou on student.stuno=stucou.stuno where couno='008' order by willorder
6、显示选修了课程的学生所属的院系名称、班级编号、班级名称、学号、姓名、所选课程名称，按志愿号排序
--select departname,class.classno,classname,student.stuno,stuname,couname from department inner join class on department.departno=class.departno inner join student on class.classno=student.classno inner join stucou on student.stuno=stucou.stuno inner join course on stucou.couno=course.couno where stucou.couno='008' order by willorder
7、显示每个院系的编号、名称及对应的班级名称（分别使用左外连接、右外连接完成）
--select department.departno,departname,classname from department left join class on department.departno=class.departno
--select department.departno,departname,classname from department right join classon department.departno=class.departno
8、使用全外连接显示每个院系的编号、名称及对应的班级名称
--select department.departno,departname,classname from department full join classon department.departno=class.departno
9、将class表及student表进行交叉连接，写出命令并执行（看有没有实际的使用意义）
--select class.*,student.* from class cross join student
【 六 】
1、显示每个学生所属的班级名称及姓名
--select classname,stuname from class inner join student on class.classno=student.classno
2、显示所有与你同姓的学生所属的班级名称及姓名
--select classname,stuname from class inner join student on class.classno=student.classno where stuname like '钟%'
3、显示网络班所有与你同姓的学生所属的班级名称及姓名
--select classname,stuname from class inner join student on class.classno=student.classno where stuname like '钟%' and classname like '%网络%'
4、显示每个班级所属的院系编号、院糸名称及班级名称
--select department.departno,departname,classname from department inner join class on class.departno=department.departno
5、显示每个学生所属的院系名称、班级名称、学号及姓名
--select departname,classname,stuno,stuname from class inner join department on department.departno=class.departno inner join student on class.classno=student.classno
6、显示信息科技系每个学生所属的院系名称、班级名称、学号及姓名
--select departname,classname,stuno,stuname from class inner join department on department.departno=class.departno inner join student on class.classno=student.classno where departname like '信息科技系'
7、显示信息科技系每个与你同姓学生所属的院系名称、班级名称、学号及姓名
--select departname,classname,stuno,stuname from class inner join department on department.departno=class.departno inner join student on class.classno=student.classno where stuname like '钟%'
8、显示每个学生所属的院系编号、院系名称、班级编号、班级名称、学号及姓名六个字段
--select department.departno,departname,class.classno,classname,stuno,stuname from class inner join department on department.departno=class.departno inner join student on class.classno=student.classno
9、显示院系编号为的院系的学生的院系名称、班级编号、班级名称、学号及姓名五个字段
--select departname,class.classno,classname,stuno,stuname from class inner join department on department.departno=class.departno inner join student on class.classno=student.classno where department.departno = '01'
10、显示每个学生的班级名称、学号、姓名、选修的课程号
--select classname,student.stuno,stuname,couno from student inner join stucou on student.stuno=stucou.stuno inner join class on class.classno=student.classno
11、显示所有选了课程的学生的班级名称、学号、姓名、选修的课程号
--select classname,student.stuno,stuname,couno from student inner join stucou on student.stuno=stucou.stuno inner join class on class.classno=student.classno where couno like '002'
12、显示所有选了、、三门课程的学生的班级名称、学号、姓名、选修的课程号
--select classname,student.stuno,stuname,couno from student inner join stucou on student.stuno=stucou.stuno inner join class on class.classno=student.classno where couno in ('002','005','008')
13、显示所有选了、、三门课程的学生的班级名称、学号、姓名、选修的课程号，
------查询结果先按课程号排序，课程号相同的再按学生姓名排序
--select classname,stucou.stuno,stuname,couno from student inner join stucou on student.stuno=stucou.stuno inner join class on class.classno=student.classno where couno in ('002','005','008') order by couno,stuname
14、显示“电子商务”班选修了课程的学生的班级名称、学号、姓名、选修的课程编号
--select classname,student.stuno,stuname,couno from student inner join stucou on student.stuno=stucou.stuno inner join class on class.classno=student.classno where couno like '002' and classname like '00电子商务‘
