---------------------------------------------create table Student(    sId int not null primary key,    sName varchar(20) not null,    sage int,    sSex varchar(20));insert into Student values (01,"congcong",22,"男"),(02,"bingbing",23,"女"),(03,"fangfang",24,"女");---------------------------------------create table Course(    cId int not null primary key,    cName varchar(20) not null,    tId int not null);insert into Course values (01,"数据结构",01),(02,"java基础",02),(03,"c++基础",02),(04,"操作系统",03);--------------------------------------------create table SC(    sId int not null ,    cId int not null ,    score int,    foreign key (cId) references Course(cId),  foreign key (sId) references Student(sId))insert into SC values(01,01,98);insert into SC values(01,02,91);insert into SC values(01,03,30);insert into SC values(02,03,80);insert into SC values(02,01,99);insert into SC values(02,02,70);insert into SC values(03,01,66);create table Teacher(    tId int not null primary key,    tName int not null)alter table course add foreign key (tId) references Teacher(tId);alter table Teacher change tName tName varchar(20);insert into Teacher values(01,"张燕");insert into Teacher values(02,"聪姐");insert into Teacher values(03,"谢芳");

create view view_test asselect s.sId,s.sName,s.sage,s.sSex,sc.cId,c.cName,t.tId,t.tName,sc.scorefrom Student s,sc,Course c,Teacher twhere s.sId=sc.sId and sc.cId=c.cId and c.tId=t.tId;select * from view_test;

1、查询“01”课程比“02”课程成绩高的所有学生的学号；select a.sId from (select sId,score from SC where cId=01) a,(select sId,score from SC where cId=02) bwhere a.score>b.score and a.sId=b.sId;2、查询平均成绩大于60分的同学的学号和平均成绩；select sId,avg(score)from scgroup by sId having avg(score)>60;3、查询所有同学的学号、姓名、选课数、总成绩；select s.sId,s.sName,count(sc.cId),sum(score)from Student s,scwhere s.sId=sc.sId group by s.sId,s.sName;select s.sId,s.sName,count(sc.cId),sum(score)from Student s left outer join sc on s.sId=sc.sIdgroup by s.sId,s.sName;4、查询姓“聪”的老师的个数；select count(distinct(tName))from Teacherwhere tName like "聪%";5、查询没学过“谢芳”老师课的同学的学号、姓名；select Student.sId,Student.sNamefrom Studentwhere sId not in (select sc.sId from sc,Course,Teacher where sc.cId=Course.cIdand Course.tId=Teacher.tId and Teacher.tName="谢芳" );6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； select sId,sName from Student where sId in(select sc.sId from sc,Student where cId=01 and sc.sId=Student.sId and  sc.sIdin (select sc.sId from sc,Student where cId=02 and sc.sId=Student.sId ));select Student.sId,Student.sNamefrom Student,sc where Student.sId=sc.sId and sc.cId=01 andexists(select * from sc as sc_2 where sc_2.sId=sc.sId and sc_2.cId=02);7、查询学过“聪姐”老师所教的同学的学号、姓名；select distinct s.sId,s.sNamefrom Student s,sc,Course c,Teacher twhere t.tName="聪姐" and c.tId=t.tId and c.cId=sc.cId and sc.sId=s.sId;8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；select result.sId,result.sName from (select s.sId,s.sName,sc.score,(select score from sc sc_2 where sc_2.sId=s.sId and sc_2.cId=02) score2 from Student s,sc where s.sId=sc.sId and sc.cId=01)result where score2<score;9、查询所有课程成绩小于60分的同学的学号、姓名select result.sId,result.sNamefrom (select s.sId,s.sName,sc.score from Student s,scwhere s.sId=sc.sId and sc.score>60) result;10、查询没有学全所有课的同学的学号、姓名；select s.sId,s.sNamefrom Student s,scwhere s.sId=sc.sId group by s.sId,s.sName having count(sc.cId)<(select count(cId) from Course);