sql（oracle）练习题

**

练习题

**

–1.看到一张新表（或者一套新表）应该做什么
1.首先看表里的字段有什么内容，整张表是关于什么的
2.判断这张表里的首要字段是什么（后边会涉及到主键-即唯一非空的那个字段，能够与其他数据区分开的字段）
3.如果是多张表，判断表与表之间的联系（尤其是通过什么字段有联系）
4.看一下具体内容，哪些字段是主要的，哪些字段是存的具体数据，哪些字段是存的码值

select * from students;
select * from courses; 
select * from scores;
select * from teachers;

–2.如何判断何时使用表连接，何时使用子查询
1.若查询时用于展示的数据来源于不同表，必须使用表连接（员工及其部门信息，员工与上级）
2.若查询时用于判断的条件来源于不同表，可以使用子查询（某部门名称下的员工信息）

–3.子查询与表连接的思路区别
子查询（多步）：先理解题意，将题目拆解成多个步骤，前一步放在后一步的子查询中
表连接（多表）：先判断需要哪些表的数据，然后通过表之间的关联关系将其连接，最后将连接产生的结果集当做一个普通的表进行后续的查询

试题
1、 查询Student表中的所有记录的Sname、Ssex和Class列。

select sname,ssex,class from students;

2、 查询教师所有的单位即不重复的Depart列.

select distinct depart from teachers;

3、 查询Student表的所有记录。

select * from students;

4、 查询Score表中成绩在60到80之间的所有记录。

select * from scores where score between 60 and 80;

5、 查询Score表中成绩为85，86或88的记录。

select  *  from scores where score in (85,86,88)

6、 查询Student表中“95031”班或性别为“女”的同学记录。

select * from students where class=95031 or ssex ='女';

7、 以class降序查询Student表的所有记录。

select * from students order by class desc;

8、 以Cno升序、score降序查询Score表的所有记录。

select *  from scores order by cno,score desc;

9、 查询“95031”班的学生人数。

select count(sno) from students where class=95031;

10、查询Score表中的最高分的学生学号和课程号。

select sno,cno from scores where score =(select max(score) from scores );

11、查询‘3-105’号课程的平均分。

select avg(score ) from scores where cno='3-105';

12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

select cno, avg(score)
  from scores
 where cno like '3%'
 group by cno
having count(sno) >= 5;

13、查询最低分大于70，最高分小于90的Sno列。

select sno from scores group by sno having min(score)>70 and max(score)<90;

14、查询所有学生的Sname、Cno和score列。–开始表连接

select t1.sname,t2.cno ,t2.score from students t1 join scores t2 on t1.sno=t2.sno;

15、查询所有学生的Sno、Cname和score列。

select t1.sno , t2.cname , t1.score  from scores t1 join courses t2 on t1.cno=t2.cno 

16、查询每个班级的平均分。

select t1.class , avg(score) from students t1 join scores t2 on t1.sno=t2.sno group by t1.class

17、查询“95033”班所选课程的平均分。

select cno  ,avg(score) from scores t1 join students t2 on t1.sno=t2.sno where t2 .class =95033 group by  t1.cno 

19、查询选修“3-105”课程的、成绩高于“109”号同学成绩的所有同学的记录。

select *
  from scores t1
 where exists (select 1
          from scores t2
         where t1.cno = t2.cno
           and t1.score > t2.score
           and t2.sno = 109
           and t2.cno = '3-105');

20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。–多个子查询，可以使用开窗排序（用哪个？）

select *
  from socres t1
  join (select sno, max(score) max_score
          from scores
         group by sno
        having count(cno) >= 2) t2
    on t1.sno = t2.sno
 where t1.score <> t2.max_score;

21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。–与19一样

22、查询和学号为105的同学同年出生的所有学生的Sno、Sname和

Sbirthday列。--EXISTS?
select t1.sno, t1.sname, t1.Sbirthday
  from students t1
 where exists
 (select 1
          from students t2
         where substr(t1.Sbirthday, 1, 4) = substr(t2.Sbirthday, 1, 4)
           and t2.sno = 105
           and t1.sno <> 105);

23、查询“张旭“教师任课的学生成绩。–子查询？表连接？

select t1.score
  from scores t1
  join courses t2
    on t1.cno = t2.cno
  join teachers t3
    on t2.tno = t3.tno
 where t3.tname = '张旭';

24、查询选修某课程的同学人数多t于5人的教师姓名。–子查询？表连接？

1.select tname
  from teachers t1
 where exists (select 1
          from courses t2
         where exists (select 1
                  from scores t3
                 where t2.cno = t3.cno
                 group by t3.cno
                having count(sno) > 5)
           and t2.tno = t1.tno);

25、查询95033班和95031班全体学生的记录。

select * from students where class in (95033,95031);

26、查询存在有85分以上成绩的课程Cno.

select distinct cno from scores where score>85;

27、查询出“计算机系“教师所教课程的成绩表。

select * from scores t1  join courses t2  on t1.cno=t2.cno join teachers t3 on t2.tno=t3.tno
where t3.depart ='计算机系';

28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

select tname ,prof  from teachers t1 where prof in (select prof from teachers t2 group by prof having 
count(1)<2) and depart in('计算机系','电子工程系');

29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和score,并按score从高到低次序排序。

select cno , sno , score from scores t1 where exists ( select 1 from scores t2 where t2.cno='3-245'
and t1.cno='3-105' group by t2.cno having t1.score>min(t2.score )) order by t1.score desc;

30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和score.

select cno , sno , score from scores t1 where exists ( select 1 from scores t2 where t2.cno='3-245'
and t1.cno='3-105' group by t2.cno having t1.score>max(t2.score ));

31、查询所有教师和同学的name、sex和birthday.

select sname name ,ssex sex ,sbirthday birthday  from students 
union all 
select tname ,tsex ,tbirthday from teachers;

32、查询所有“女”教师和“女”同学的name、sex和birthday.

select sname name ,ssex sex ,sbirthday birthday  from students where  ssex='女'
union all 
select tname ,tsex ,tbirthday from teachers where tsex='女' ;

33、查询成绩比该课程平均成绩低的同学的成绩表。

select * from scores t1 join (select cno ,avg(score) avg_score from scores group by cno ) t2 on 
t1.cno=t2.cno where t1.score < t2.avg_score;

34、查询所有任课教师的Tname和Depart.

select tname, depart
  from teachers t1
 where  exists (select 1
          from courses t2
          join scores t3
            on t2.cno = t3.cno
         where t2.tno = t1.tno)

35 查询所有未讲课的教师的Tname和Depart. --NOT EXISTS

select tname, depart
  from teachers t1
 where not exists (select 1
          from courses t2
          join scores t3
            on t2.cno = t3.cno
         where t2.tno = t1.tno)

select *
      from teachers t1
      left join courses t2
        on t1.tno = t2.tno
      left join scores t3
        on t2.cno = t3.cno
     where t2.cname is null;

36、查询至少有2名男生的班号。

select class from students where ssex ='男' group by class having count(*)>=2;

37、查询Student表中不姓“王”的同学记录。

select * from students where sname not like '王%';

38、查询Student表中每个学生的姓名和年龄。

select sname,to_char(sysdate,'yyyy')-substr(sbirthday,1,4) age from students

39、查询Student表中最大和最小的Sbirthday日期值。

select max(sbirthday),min(sbirthday) from students

40、以班号和年龄从大到小的顺序查询Student表中的全部记录。

select * from students order by class desc ,sbirthday ;

41、查询“男”教师及其所上的课程。

select cname from teachers t1 join courses t2 on t1.tno =t2.tno where t1.tsex='男';

42、查询最高分同学的Sno、Cno和score列。

select sno ,cno ,score  from scores where score = (select  max(score ) from scores )

43、查询和“李军”同性别的所有同学的Sname.

select sname from students  where ssex in (select ssex from students where sname='李军') and sname<>'李军';

44、查询和“李军”同性别并同班的同学Sname.

select sname from students  where (class,ssex) in (select class,ssex from students where sname='李军') and sname<>'李军';

45、查询所有选修“计算机导论”课程的“男”同学的成绩表

select * from scores t1 
 left join students t2 on t1.sno=t2.sno 
 left join courses t3 on t1.cno=t3.cno
 where t3.cname='计算机导论' and t2 .ssex ='男';

建表语句

create table student(
sno varchar2(20) primary key,
sname varchar2(30),
sage number(2),
ssex varchar2(5)
);
create table teacher(
tno varchar2(10) primary key,
tname varchar2(20)
);
create table course(
cno varchar2(10),
cname varchar2(30),
tno varchar2(20),
constraint pk_course primary key (cno,tno)
);
create table sc(
sno varchar2(10),
cno varchar2(10),
score number(5,2),
constraint pk_sc primary key (sno,cno)
);
/*******初始化学生表的数据******/
insert into student values ('s001','张三',23,'男');
insert into student values ('s002','李四',23,'男');
insert into student values ('s003','吴鹏',25,'男');
insert into student values ('s004','琴沁',20,'女');
insert into student values ('s005','王丽',20,'女');
insert into student values ('s006','李波',21,'男');
insert into student values ('s007','刘玉',21,'男');
insert into student values ('s008','萧蓉',21,'女');
insert into student values ('s009','陈萧晓',23,'女');
insert into student values ('s010','陈美',22,'女');
commit;
/******************初始化教师表***********************/
insert into teacher values ('t001', '刘阳');
insert into teacher values ('t002', '谌燕');
insert into teacher values ('t003', '胡明星');
commit;
/***************初始化课程表****************************/
insert into course values ('c001','J2SE','t002');
insert into course values ('c002','Java Web','t002');
insert into course values ('c003','SSH','t001');
insert into course values ('c004','Oracle','t001');
insert into course values ('c005','SQL SERVER 2005','t003');
insert into course values ('c006','C#','t003');
insert into course values ('c007','JavaScript','t002');
insert into course values ('c008','DIV+CSS','t001');
insert into course values ('c009','PHP','t003');
insert into course values ('c010','EJB3.0','t002');
commit;
/***************初始化成绩表***********************/
insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);
insert into sc values ('s003','c001',81.9);
insert into sc values ('s004','c001',60.9);
insert into sc values ('s001','c002',82.9);
insert into sc values ('s002','c002',72.9);
insert into sc values ('s003','c002',81.9);
insert into sc values ('s001','c003','59');
commit;


CREATE TABLE students--学生表
(sno VARCHAR(10) NOT NULL,--学生编号 
sname VARCHAR(20) NOT NULL,--学生姓名
ssex VARCHAR(20) NOT NULL,--性别
sbirthday VARCHAR(20),--出生日期
class VARCHAR(5));--班级

CREATE TABLE courses--课程表                
(cno VARCHAR(5) NOT NULL,--课程编号 
cname VARCHAR(20) NOT NULL,--课程姓名 
tno VARCHAR(20) NOT NULL);--教师编号

CREATE TABLE scores--成绩表
(sno VARCHAR(5) NOT NULL,--学生编号 
cno VARCHAR(20) NOT NULL,--课程编号 
score NUMERIC(10, 1) NOT NULL);--成绩

CREATE TABLE teachers --教师表
(tno VARCHAR(5) NOT NULL, --教师编号
tname VARCHAR(20) NOT NULL, --教师姓名
tsex VARCHAR(20) NOT NULL, --教师性别
tbirthday VARCHAR(20) NOT NULL, --教师出生日期
prof VARCHAR(20), --教师职位
depart VARCHAR(20) NOT NULL);--教师工作单位



INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,'1976-01-23',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,'1976-02-20',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,'1974-06-03',95031);

INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' ,856);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,100);

INSERT INTO SCORES(SNO,CNO,score)VALUES (103,'3-245',86);
INSERT INTO SCORES(SNO,CNO,score)VALUES (105,'3-245',75);
INSERT INTO SCORES(SNO,CNO,score)VALUES (109,'3-245',68);
INSERT INTO SCORES(SNO,CNO,score)VALUES (103,'3-105',92);
INSERT INTO SCORES(SNO,CNO,score)VALUES (105,'3-105',88);
INSERT INTO SCORES(SNO,CNO,score)VALUES (109,'3-105',76);
INSERT INTO SCORES(SNO,CNO,score)VALUES (101,'3-105',64);
INSERT INTO SCORES(SNO,CNO,score)VALUES (107,'3-105',91);
INSERT INTO SCORES(SNO,CNO,score)VALUES (108,'3-105',78);
INSERT INTO SCORES(SNO,CNO,score)VALUES (101,'6-166',85);
INSERT INTO SCORES(SNO,CNO,score)VALUES (107,'6-106',79);
INSERT INTO SCORES(SNO,CNO,score)VALUES (108,'6-166',81);

INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');
commit;