笔试必备：48道SQL练习题（Oracle为主）

练习主要涉及四张表，分别如下：

student(sid,sname,sage,ssex) 学生表 
course(cid,cname,tid) 课程表 
score(sid,cid,score) 成绩表 
teacher(tid,tname) 教师表

首先建立表结构

CREATE TABLE student 
  ( 
     sid    INT, 
     sname varchar (32), 
     sage  INT, 
     ssex  varchar (8) 
  ); 
 
CREATE TABLE course 
  ( 
     cid    INT, 
     cname varchar(32), 
     tid    INT 
  ); 
 
CREATE TABLE score 
  ( 
     sid    INT, 
     cid    INT, 
     score INT 
  ); 
 
CREATE TABLE teacher 
  ( 
     tid    INT, 
     tname varchar(16) 
  );

插入数据

--oracle
insert into student select 1,'刘一',18,'男' FROM dual union all
 select 2,'钱二',19,'女' FROM dual union all
 select 3,'张三',17,'男' FROM dual union all
 select 4,'李四',18,'女' FROM dual union all
 select 5,'王五',17,'男' FROM dual union all
 select 6,'赵六',19,'女' FROM dual; 
 
 insert into teacher select 1,'叶平' FROM dual union all
 select 2,'贺高' FROM dual union all
 select 3,'杨艳' FROM dual union all
 select 4,'周磊' FROM dual;
 
 insert into course select 1,'语文',1 FROM dual union all
 select 2,'数学',2 FROM dual union all
 select 3,'英语',3 FROM dual union all
 select 4,'物理',4 FROM dual;
 
 insert into score
 select 1,1,56 FROM dual union all 
 select 1,2,78 FROM dual union all 
 select 1,3,67 FROM dual union all 
 select 1,4,58 FROM dual union all 
 select 2,1,79 FROM dual union all 
 select 2,2,81 FROM dual union all 
 select 2,3,92 FROM dual union all 
 select 2,4,68 FROM dual union all 
 select 3,1,91 FROM dual union all 
 select 3,2,47 FROM dual union all 
 select 3,3,88 FROM dual union all 
 select 3,4,56 FROM dual union all 
 select 4,2,88 FROM dual union all 
 select 4,3,90 FROM dual union all 
 select 4,4,93 FROM dual union all 
 select 5,1,46 FROM dual union all 
 select 5,3,78 FROM dual union all 
 select 5,4,53 FROM dual union all 
 select 6,1,35 FROM dual union all 
 select 6,2,68 FROM dual union all 
 select 6,4,71 FROM dual;



--mysql
insert into student values (1,'刘一',18,'男'),
 (2,'钱二',19,'女'),
 (3,'张三',17,'男'),
 (4,'李四',18,'女'),
 (5,'王五',17,'男'),
 (6,'赵六',19,'女'); 
 
 insert into teacher values (1,'叶平'),
 (2,'贺高'),
 (3,'杨艳'),
 (4,'周磊');
 
 insert into course values (1,'语文',1),
 (2,'数学',2),
 (3,'英语',3),
 (4,'物理',4);
 
 insert into score values
 (1,1,56), 
 (1,2,78), 
 (1,3,67), 
 (1,4,58), 
 (2,1,79), 
 (2,2,81), 
 (2,3,92), 
 (2,4,68), 
 (3,1,91), 
 (3,2,47), 
 (3,3,88), 
 (3,4,56), 
 (4,2,88), 
 (4,3,90), 
 (4,4,93), 
 (5,1,46), 
 (5,3,78), 
 (5,4,53), 
 (6,1,35), 
 (6,2,68), 
 (6,4,71);

练习题Beginning！！！

1.查询“001”课程比“002”课程成绩高的所有学生的学号

select a.sid from (select sid,score from score where cid=001) a,(select sid,score from score where cid=002) b where a.sid=b.sid and a.score>b.score;

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

--oracle
select sid,avg(nvl(score,0)) from score group by sid having avg(nvl(score,0))>60;
--nvl(字段，为空返回值）
--nvl2(字段，不为空返回值，为空返回值）

--mysql
select sid,avg(ifnull(score,0)) from score group by sid having avg(ifnull(score,0))>60;

--补充：MySQL中如何查询的字段中可能存在null值，可以做一些处理
--1.isnull(exper) 判断exper是否为空，是则返回1，否则返回0
--2.ifnull(exper1,exper2)判断exper1是否为空，是则用exper2代替
--3.nullif(exper1,exper2)如果expr1= expr2 成立，那么返回值为NULL，否则返回值为expr1。

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

select t1.sid,t1.sname,count(t2.cid),sum(t2.score) from student t1 left join score t2 on t1.sid=