编辑代码

CREATE DATABASE test;
use test;
CREATE TABLE student (
	id int,
	name varchar(255),
	address varchar(255),
	city varchar(255)
);

INSERT INTO student VALUES (1, '刘一', '郑十家隔壁', '河南');
INSERT INTO student VALUES (2, '陈二',  '李四家隔壁', '安徽');
INSERT INTO student VALUES (3, '张三',  '白娘子家隔壁', '杭州');
INSERT INTO student VALUES (4, '李四',  '许仙家隔壁', '杭州');
INSERT INTO student VALUES (5, '王五',  '李四家隔壁', '杭州');
INSERT INTO student VALUES (6, '赵六',  '赵六家隔壁', '杭州');
INSERT INTO student VALUES (7, '孙七',  '张三家隔壁', '杭州');
INSERT INTO student VALUES (8, '周八',  '雷峰塔附近', '杭州');
INSERT INTO student VALUES (9, '吴九',  '孙七家隔壁', '杭州');
INSERT INTO student VALUES (10, '郑十',  '周八家隔壁', '杭州');
 
 -- 86 
select class.monitor,student.sname,class.cno,class.speciality from class,student where class.monitor=student.sno;
-- group by having
-- 查询2018年讲授过两门或两门以上的课程教师编号和课程号
select TID,LNO from election where syear= 2018  group by TID having count(LNO)>2;
select TID,LNO from election where syear= 2018 and TID in (select TID from election where syear= 2018  group by TID having count(*)>=2);
-- 统计计算机系所有教师的教师编号 姓名，2018 年所有的总课程数 总学分数 并按学分低到高排序
select TID,TNAME，
from  TEACHAR,election，lesson
where TEACHAR,TID=election.TID  and election.LNO=lesson.LNO and syear=2018
and department='计算机' group by teacher.TID,Tname order by 4;

select class.speciality,count(student.sno) from class,student where class.cno=student.cno and class cno in
(select election.cno from election ,lesson where LANAME='数据库' and election .lno=lesson .lno )

-- 创建视图 所有计算机系的学号姓名性别，所在班级号，和数据库课程的分数   三个表做笛卡尔积
create view V1(sno,sname,gender,cno,grade.score) as select sno,sname,gender,cno,grade.score from student,grade,lesson
where lesson.LANAME='数据库' and student.sno=grade.sno and grade.lno=lesson.lno and department='计算机系;