数据库 mysql例子

select sno,sname from student where sdept='MA';
create view v12 as select distinct sno from sc ;
create view v13 as select sno,grade from sc where cno = 1 order by grade desc,sno asc;

create view v14 as select sno,grade*0.8 from sc where cno=1 and grade between 80 and 90;

create view v15 as select * from student where sdept in ('ma','cs') and sname like '张%';

create view v16 as select sno,cno from sc where grade is null;

create view v17 as select student.sno,sname,ssex,sage,sdept,cname from student,course,sc where student.sno=sc.sno and https://www.360docs.net/doc/1c7133443.html,o=https://www.360docs.net/doc/1c7133443.html,o;

create view v18 as select student.sno,student.sname,https://www.360docs.net/doc/1c7133443.html,ame,sc.grade from sc,student,course where https://www.360docs.net/doc/1c7133443.html,o=https://www.360docs.net/doc/1c7133443.html,o and student.sno=sc.sno;

create view v19 as select distinct student.sno,student.sname,sc.grade from sc,student,course where https://www.360docs.net/doc/1c7133443.html,o=1 and student.sno=sc.sno and sc.grade>=90 ;

create view v110 as select https://www.360docs.net/doc/1c7133443.html,o,https://www.360docs.net/doc/1c7133443.html,ame from course a,course b,course c where a.cpno=https://www.360docs.net/doc/1c7133443.html,o and https://www.360docs.net/doc/1c7133443.html,o=b.cpno ;


（1） 查询选修了“高等数学”的学生学号和姓名；
create view v21 as select student.sno,sname from sc,student,course where student.sno=sc.sno and https://www.360docs.net/doc/1c7133443.html,o=https://www.360docs.net/doc/1c7133443.html,o and https://www.360docs.net/doc/1c7133443.html,ame='高等数学';
（2） 查询“高等数学”的成绩高于张三的学生学号和成绩；
create view v22 as select student.sno,student.sname,grade from sc,student,course where student.sno=sc.sno and https://www.360docs.net/doc/1c7133443.html,o=https://www.360docs.net/doc/1c7133443.html,o and https://www.360docs.net/doc/1c7133443.html,ame='高等数学' and sc.grade>(select grade from sc,student where sc.sno=student.sno and student.sname='张三' );
（3） 查询其他系中年龄小于计算机系年龄最大者的学生；
create view v23 as select * from student where sdept<>'cs' and sage<(select max(sage) from student where sdept='cs');
（4） 查询其他系中比计算机系学生年龄都小的学生；
create view v24 as select * from student where sdept<>'cs' and sage<(select min(sage) from student where sdept='cs');
（5） 查询选修了“信息系统”课程的学生姓名；
create view v25 as select student.sname from sc,student,course where https://www.360docs.net/doc/1c7133443.html,ame='信息系统' and https://www.360docs.net/doc/1c7133443.html,o = https://www.360docs.net/doc/1c7133443.html,o and sc.sno = student.sno;
（6） 查询没有选修“信息系统”课程的学生姓名；
create view v26 as select sname from student where sno not in (select sc.sno from sc,course where https://www.360docs.net/doc/1c7133443.html,ame='信息系统' and https://www.360docs.net/doc/1c7133443.html,o = https://www.360docs.net/doc/1c7133443.html,o ) ;
（7） 查询选修了全部课程的学生姓名；
create view v27 as select sname from student where sno in (select sno from sc group by sno having count(*)=7);
（8） 查询至少选修了学号为“95002”的学生所选修的全部课程的学生学号和姓名；
create view v28 as select distinct a.sno,sname from sc a,student where student.sno=a.sno and not exists (select * from sc b where b.sno='95002' and not exists (select * from sc c where c.sno=a.sno and c.sno=b.sno));