sql语句面试练习

---------------------------------------------
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 as
select s.sId,s.sName,s.sage,s.sSex,sc.cId,c.cName,t.tId,t.tName,sc.score
from Student s,sc,Course c,Teacher t
where 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) b
where a.score>b.score and a.sId=b.sId;


2、查询平均成绩大于60分的同学的学号和平均成绩；
select sId,avg(score)
from sc
group by sId having avg(score)>60;

3、查询所有同学的学号、姓名、选课数、总成绩；
select s.sId,s.sName,count(sc.cId),sum(score)
from Student s,sc
where 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.sId
group by s.sId,s.sName;

4、查询姓“聪”的老师的个数；
select count(distinct(tName))
from Teacher
where tName like "聪%";

5、查询没学过“谢芳”老师课的同学的学号、姓名；
select Student.sId,Student.sName
from Student
where sId not in (select sc.sId from sc,Course,Teacher where sc.cId=Course.cId
and 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.sId
in (select sc.sId from sc,Student where cId=02 and sc.sId=Student.sId ));

select Student.sId,Student.sName
from Student,sc 
where Student.sId=sc.sId and sc.cId=01 and
exists(select * from sc as sc_2 where sc_2.sId=sc.sId and sc_2.cId=02);


7、查询学过“聪姐”老师所教的同学的学号、姓名；

select distinct s.sId,s.sName
from Student s,sc,Course c,Teacher t
where 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.sName
from (select s.sId,s.sName,sc.score from Student s,sc
where s.sId=sc.sId and sc.score>60) result;

10、查询没有学全所有课的同学的学号、姓名；

select s.sId,s.sName
from Student s,sc
where s.sId=sc.sId 
group by s.sId,s.sName 
having count(sc.cId)<(select count(cId) from Course);