以lifetragedy大神的文章为参考整理的笔记（数据库）

sql经常不写总是容易忘，记下来方便以后回忆！

1 查询“1”课程比“2”课程成绩高的所有学生的学号；

子查询运用不熟

select a.s#,a.score
from
(select s#,score from sc where c#= '1') a,
(select s#,score from sc where c#= '2') b
where a.score > b.score and a.s#=b.s#

2 查询平均成绩大于60分的同学的学号和平均成绩；

avg（）只能跟在haiving后  不能跟在where 后

select s#,avg(score) 
from sc 
having avg(score)>60 group by s#

3 查询所有同学的学号、姓名、选课数、总成绩；

内链接、外联结知识点模糊

select s.s#,count(sc.c#),sum(score)
from student s
left outer join sc on s.s# = sc.s#
group by s.s#

4 查询姓“郭”的老师的个数；

select count(distinct(tname))
from teacher
where tname like '郭%'

5 查询没学过“赵嘉玉”老师课的同学的学号、姓名；

大神sql：

select Student.S#,Student.Sname
    from Student 
    where S# not in (select distinct( SC.S#) fromSC,Course,Teacher where  SC.C#=Course.C#and Teacher.T#=Course.T# andTeacher.Tname='赵嘉玉');

本人写的sql(差距啊)：

select distinct(s.s#) 
from student s left join sc on s.s# = sc.s# 
where s.s# not in 
(select s# from sc where c# = 
 (select t# from teacher  where tname = '赵嘉玉')
)

6 查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；

select a.s#
from (select s# from sc where c#='1') a join (select s# from sc where c#='2') b 
on a.s#=b.s#

7 查询学过“邢玉燕”老师所教的所有课的同学的学号、姓名；

select s.s# from student s join sc on s.s# = sc.s# where sc.c# in 
(select c# from course where t# in
(select t# from teacher where tname='邢玉燕'))

8 查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；

select a.s#
from 
(select s#,score from sc where sc.c# = '1') a,
(select s#,score from sc where sc.c# = '2') b
where a.score > b.score and a.s# = b.s#

9 查询所有课程成绩小于60分的同学的学号；

select s#,avg(score) from sc having avg(score)<60 group by s#

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

select s.s# from student s left join sc on s.s# = sc.s#
having count(sc.s#) < 
(select count(c#) from course) 
group by s.s#

准备数据

prompt PL/SQL Developer import file
prompt Created on 2013年11月5日 by 雷
set feedback off
set define off
prompt Creating COURSE...
create table COURSE
(
  C#    VARCHAR2(100) not null,
  CNAME VARCHAR2(100),
  T#    VARCHAR2(100)
)
;
comment on column COURSE.C#
  is '课程号';
comment on column COURSE.CNAME
  is '课程名';
comment on column COURSE.T#
  is '教工号';
alter table COURSE
  add constraint C_PK primary key (C#);

prompt Creating SC...
create table SC
(
  S#    VARCHAR2(100),
  C#    VARCHAR2(100),
  SCORE NUMBER
)
;
comment on column SC.S#
  is '学号';
comment on column SC.C#
  is '课程号';
comment on column SC.SCORE
  is '分数';

prompt Creating STUDENT...
create table STUDENT
(
  S#   VARCHAR2(100) not null,
  SAGE VARCHAR2(100),
  SSEX VARCHAR2(100)
)
;
comment on column STUDENT.S#
  is '学号';
comment on column STUDENT.SAGE
  is '学生年龄';
comment on column STUDENT.SSEX
  is '学生性别';
alter table STUDENT
  add constraint S_PK primary key (S#);

prompt Creating TEACHER...
create table TEACHER
(
  T#    VARCHAR2(100) not null,
  TNAME VARCHAR2(100)
)
;
comment on column TEACHER.T#
  is '教工号';
comment on column TEACHER.TNAME
  is '教师姓名';
alter table TEACHER
  add constraint T_PK primary key (T#);

prompt Loading COURSE...
insert into COURSE (C#, CNAME, T#)
values ('1', '英语', '1');
insert into COURSE (C#, CNAME, T#)
values ('2', '数学', '2');
insert into COURSE (C#, CNAME, T#)
values ('3', '语文', '3');
commit;
prompt 3 records loaded
prompt Loading SC...
insert into SC (S#, C#, SCORE)
values ('1', '1', '100');
insert into SC (S#, C#, SCORE)
values ('1', '2', '90');
insert into SC (S#, C#, SCORE)
values ('1', '3', '80');
insert into SC (S#, C#, SCORE)
values ('2', '1', '70');
insert into SC (S#, C#, SCORE)
values ('2', '2', '80');
insert into SC (S#, C#, SCORE)
values ('2', '3', '60');
insert into SC (S#, C#, SCORE)
values ('3', '1', '100');
insert into SC (S#, C#, SCORE)
values ('3', '2', '100');
insert into SC (S#, C#, SCORE)
values ('3', '3', '100');
insert into SC (S#, C#, SCORE)
values ('4', '1', '15');
insert into SC (S#, C#, SCORE)
values ('4', '2', '15');
insert into SC (S#, C#, SCORE)
values ('4', '3', '15');
insert into SC (S#, C#, SCORE)
values ('5', '1', '78');
insert into SC (S#, C#, SCORE)
values ('5', '2', '75');
insert into SC (S#, C#, SCORE)
values ('6', '1', '34');
insert into SC (S#, C#, SCORE)
values ('8', '1', '45');
insert into SC (S#, C#, SCORE)
values ('8', '2', '89');
insert into SC (S#, C#, SCORE)
values ('8', '3', '43');
insert into SC (S#, C#, SCORE)
values ('9', '1', '15');
insert into SC (S#, C#, SCORE)
values ('9', '2', '79');
insert into SC (S#, C#, SCORE)
values ('9', '3', '98');
commit;
prompt 21 records loaded
prompt Loading STUDENT...
insert into STUDENT (S#, SAGE, SSEX)
values ('1', 'wang', '男');
insert into STUDENT (S#, SAGE, SSEX)
values ('2', 'sun', '男');
insert into STUDENT (S#, SAGE, SSEX)
values ('3', 'li', '男');
insert into STUDENT (S#, SAGE, SSEX)
values ('4', 'zhao', '男');
insert into STUDENT (S#, SAGE, SSEX)
values ('5', 'zhang', '女');
insert into STUDENT (S#, SAGE, SSEX)
values ('6', 'qian', '女');
insert into STUDENT (S#, SAGE, SSEX)
values ('7', 'zhou', '女');
insert into STUDENT (S#, SAGE, SSEX)
values ('8', 'yang', '女');
insert into STUDENT (S#, SAGE, SSEX)
values ('9', 'lei', '女');
insert into STUDENT (S#, SAGE, SSEX)
values ('10', 'xi', '女');
commit;
prompt 10 records loaded
prompt Loading TEACHER...
insert into TEACHER (T#, TNAME)
values ('1', '郭研');
insert into TEACHER (T#, TNAME)
values ('2', '赵嘉玉');
insert into TEACHER (T#, TNAME)
values ('3', '邢玉燕');
commit;
prompt 3 records loaded
set feedback on
set define on
prompt Done.