众所周知，学习完sql的基本语法之后，是需要一定量的练习才能更好的掌握sql语句的书写。因此，这里整理了几十道sql语句的练习题用于巩固sql语句的书写；当然这里只考虑能得到查询结果，不考虑sql语句的性能优化之类的问题。有需要的小伙伴们可以练习一下，一定会有收获的。本文所有的题目都是整理自互联网。

1.创建表

-- 1.学生表
create table students (
    s_id        varchar(20),
    s_name      varchar(20) not null default '',
    s_birth     varchar(20) not null default '',
    s_sex       varchar(10) not null default '',
    primary key(s_id)    
);


-- 2.课程表
create table courses (
    c_id         varchar(20),
    c_name       varchar(20) not null default '',
    t_id         varchar(20) not null,
    primary key(c_id)
);


-- 教师表
create table teachers (
    t_id        varchar(20),
    t_name      varchar(20) not null default '',
    primary key(t_id)
);


-- 成绩表
create table scores (
    s_id        varchar(20),
    c_id        varchar(20),
    s_score     int(3),
    primary key(s_id, c_id)
);

2.插入初始数据

-- 插入学生表数据
insert into students values('01' , '赵雷' , '1990-01-01' , '男');
insert into students values('02' , '钱电' , '1990-12-21' , '男');
insert into students values('03' , '孙风' , '1990-05-20' , '男');
insert into students values('04' , '李云' , '1990-08-06' , '男');
insert into students values('05' , '周梅' , '1991-12-01' , '女');
insert into students values('06' , '吴兰' , '1992-03-01' , '女');
insert into students values('07' , '郑竹' , '1989-07-01' , '女');
insert into students values('08' , '王菊' , '1990-01-20' , '女');


-- 插入课程表数据
insert into courses values('01' , '语文' , '02');
insert into courses values('02' , '数学' , '01');
insert into courses values('03' , '英语' , '03');


-- 插入教师表数据
insert into teachers values('01' , '张三');
insert into teachers values('02' , '李四');
insert into teachers values('03' , '王五');

-- 插入成绩表数据
insert into scores values('01' , '01' , 80);
insert into scores values('01' , '02' , 90);
insert into scores values('01' , '03' , 99);
insert into scores values('02' , '01' , 70);
insert into scores values('02' , '02' , 60);
insert into scores values('02' , '03' , 80);
insert into scores values('03' , '01' , 80);
insert into scores values('03' , '02' , 80);
insert into scores values('03' , '03' , 80);
insert into scores values('04' , '01' , 50);
insert into scores values('04' , '02' , 30);
insert into scores values('04' , '03' , 20);
insert into scores values('05' , '01' , 76);
insert into scores values('05' , '02' , 87);
insert into scores values('06' , '01' , 31);
insert into scores values('06' , '03' , 34);
insert into scores values('07' , '02' , 89);
insert into scores values('07' , '03' , 98);

各表数据如下：

students:

 courses:

teachers: 

scores: 

3.sql语句练习

1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数

/*
使用学生表分别和两个成绩表连接，其中一个查询出01课程的成绩，另一个查询出02课程的成绩；最后使用where选出01课程成绩大于02课程成绩

*/

select st.*, sc1.s_score as '语文', sc2.s_score as '数学'
from students st 
left join scores sc1 on st.s_id = sc1.s_id and sc1.c_id = '01' 
left join scores sc2 on st.s_id = sc2.s_id and sc2.c_id = '02'
where sc1.s_score > sc2.s_score;

 2.查询"01"课程比"02"课程成绩低的学生的信息及课程分数

-- 思路与第一题类似

select st.*, sc1.s_score as '语文', sc2.s_score as '数学'
from students st 
left join scores sc1 on st.s_id = sc1.s_id and sc1.c_id = '01' 
left join scores sc2 on st.s_id = sc2.s_id and sc2.c_id = '02'
where sc1.s_score < sc2.s_score;

3. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

/*
    课程表和学生表连接，课程表中以学生id分组，然后计算成绩平均值，最后使用having过滤平均分及格的行
    avg()函数求平均值
    round()函数用于数据的四舍五入，后面的2表示保留2位小数
*/

select st.s_id, st.s_name, round(avg(sc.s_score), 2) as '平均成绩' from students st 
left join scores sc on sc.s_id = st.s_id 
group by st.s_id having avg(sc.s_score) >= 60;

4. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

/*
    思路以上一题类似，这里多了一个处理无成绩的情况；
    case 
        when   then
        else
    end 
    类似于java中的分支结构
*/

select st.s_id, st.s_name, (case when round(avg(sc.s_score), 2) is null then 0 else round(avg(sc.s_score), 2) end) as '平均成绩' from students st 
left join scores sc on sc.s_id = st.s_id 
group by st.s_id 
having avg(sc.s_score) < 60 or avg(sc.s_score) is null;

5. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

/*
    连接学生表和课程表，以学生id分组，统计每个学生的选棵数，统计每个学生的所有课程分数
*/

select st.s_id, st.s_name, count(sc.c_id) as '选课总数', sum(case when sc.s_score is null then 0 else sc.s_score end) '总成绩' from students st 
left join scores sc on sc.s_id = st.s_id 
group by st.s_id;

 6.查询"李"姓老师的数量

select count(t_id) as '人数' from teachers where t_name like '李%';

7. 查询学过"张三"老师授课的同学的信息

-- 连接4个表，筛选条件是教师姓名是张三

select st.* from students st 
left join scores sc on st.s_id = sc.s_id 
left join courses c on c.c_id = sc.c_id 
left join teachers t on t.t_id = c.t_id
where t.t_name = '张三';

8. 查询没学过"张三"老师授课的同学的信息

-- 先查询张三教的课的id
select c.c_id from courses c left join teachers t on t.t_id = c.t_id where t.t_name = '张三';

-- 查询成绩表中有张三教的课的成绩的学生id
select sc.s_id from scores sc where in(
    select c.c_id from courses c left join teachers t on t.t_id = c.t_id where t.t_name = '张三'
);

-- 在学生表中查询学生id不在上面查询的结果中的行
select st.* from students st where st.s_id not in(
    select sc.s_id from scores sc where sc.c_id in(
        select c.c_id from courses c left join teachers t on t.t_id = c.t_id where t.t_name = '张三'
    )
);

 9.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

-- 查询成绩表,获得同时学过课程01和课程02的学生id
-- 这里分两步，先找到课程01对应的学生id，然后找到课程02对应的学生id，取其公共部分
select s_id from scores where c_id = '01';

select s_id from scores where c_id = '02';

-- 取其公共部分
select temp.s_id from (select s_id from scores where c_id = '01') temp where temp.s_id in(
    select s_id from scores where c_id = '02'
);


-- 查询学生表获取学生信息，筛选条件就是上面查询出来的学生id

select st.* from students st where st.s_id in(
 select temp.s_id from (select s_id from scores where c_id = '01') temp where temp.s_id         
 in(
    select s_id from scores where c_id = '02'
  )  
);

 10.查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

-- 思路与上一题类似，不同点在于这次不是取公共部分，而是取01中有02中没有的一部分

select s_id from scores where c_id = '01';

select s_id from scores where c_id = '02';

-- 取01中有02中没有的一部分
select temp.s_id from (select s_id from scores where c_id = '01') temp where temp.s_id not in(
    select s_id from scores where c_id = '02'
);


-- 查询学生信息
select st.* from students st where st.s_id in(
    select temp.s_id from (select s_id from scores where c_id = '01') temp where temp.s_id         
    not in(
       select s_id from scores where c_id = '02'
    )
);

 11.查询没有学全所有课程的同学的信息

-- 先查询课程表，得出有多少门课
select count(c_id) from courses;

-- 再查询成绩表，按学生id分组，统计成绩是否有缺少的, 得出不缺少的学生id
-- 这种方案有一个前提，就是学生不能有两门同样的课程成绩，事实上正常情况也确实如此
select s_id from scores group by s_id having count(c_id) = (select count(c_id) from courses);


-- 查询学生信息
select st.* from students st where st.s_id not in(
    select s_id from scores group by s_id having count(c_id) = (select count(c_id) from 
    courses)
);

12. 查询至少有一门课与学号为"01"的同学所学相同的同学的信息

-- 查询学号01学生所学的课程id
select c_id from scores where s_id = '01';


-- 查询学过01学生学过的课程的学生id
select distinct sc.s_id from scores sc where sc.c_id = any(select c_id from scores where s_id = '01') and sc.s_id != '01';


-- 查询学生信息
select st.* from students st where st.s_id in(
    select distinct sc.s_id from scores sc where sc.c_id = any(select c_id from scores         
    where s_id = '01') and sc.s_id != '01'
);

 13.查询和"01"号的同学学习的课程完全相同的其他同学的信息

-- 查询学号01学生所学的课程id,然后将所有课程id连接
-- 这里使用一个函数group_concat()函数，可以将每列的值连接起来，这里连接结果为01,02,03
select group_concat(c_id) from scores where s_id = '01' order by c_id;


-- 查询学生id
select sc.s_id from scores sc group by sc.s_id having group_concat(sc.c_id) = (
    select group_concat(c_id) from scores where s_id = '01' order by c_id
) and sc.s_id != '01';

-- 查询学生信息
select st.* from students st where st.s_id in(
    select sc.s_id from scores sc group by sc.s_id having group_concat(sc.c_id) = (
        select group_concat(c_id) from scores where s_id = '01' order by c_id
    ) and sc.s_id != '01'
);

14. 查询没学过"张三"老师讲授的任一门课程的学生姓名

-- 查询张三所教课程的课程id
select c.c_id from courses c inner join teachers t on c.t_id = t.t_id and t.t_name = '张三';

-- 再连接成绩表，得出学生id
select sc.s_id from scores sc inner join courses c on sc.c_id = c.c_id 
inner join teachers t on c.t_id = t.t_id and t.t_name = '张三';


-- 查询学生信息
select st.s_name from students st where st.s_id not in(
    select sc.s_id from scores sc inner join courses c on sc.c_id = c.c_id 
    inner join teachers t on c.t_id = t.t_id and t.t_name = '张三'
);

 15.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

-- 先挑选出分数小于60或者为null的行，然后按照学生id分组，组数要大于2 
select sc.s_id from scores sc where sc.s_score < 60 or sc.score is null 
group by sc.s_id having count(1) >=2;


-- 查询学生表和成绩表，筛选条件是上面查询出来的学生id，最后按学生id分组求平均成绩
select  st.s_id, st.s_name, avg(sc.s_score) from students st 
left join scores sc on sc.s_id = st.s_id
where sc.s_id in(                                              -- 这里筛选条件是sc.s_id
    select sc.s_id from scores sc where sc.s_score < 60 or sc.s_score is null 
    group by sc.s_id having count(1) >=2
)
group by st.s_id;

16. 检索"01"课程分数小于60，按分数降序排列的学生信息

-- 连接学生表和成绩表即可

select st.*, sc.s_score from students st 
left join scores sc on st.s_id = sc.s_id 
where sc.c_id = '01' and sc.s_score < 60
order by sc.s_score desc;

 17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

-- 每门课都使用一个表与学生表连接，然后按学生id分组

select st.s_id, st.s_name, avg(sc4.s_score) '平均分', sc1.s_score '语文', sc2.s_score '数学', sc3.s_score '英语' from students st 
left join scores sc1 on sc1.s_id = st.s_id and sc1.c_id = '01'
left join scores sc2 on sc2.s_id = st.s_id and sc2.c_id = '02'
left join scores sc3 on sc3.s_id = st.s_id and sc3.c_id = '03'
left join scores sc4 on sc4.s_id = st.s_id
group by st.s_id 
order by avg(sc4.s_score) desc;

18. 查询学生的总成绩并进行排名

-- 连接学生表和成绩表，然后按学生id分组

select st.s_id, st.s_name, (case when sum(sc.s_score) is null then 0 else sum(sc.s_score) end) '总成绩' from students st 
left join scores sc on sc.s_id = st.s_id 
group by st.s_id 
order by sum(sc.s_score) desc;

19. 查询不同老师所教不同课程平均分从高到低显示

-- 查询成绩表，按课程id分组，求课程平均分
select c_id, avg(s_score) from scores group by c_id;

-- 接着连接课程表和教师表
select t.t_id, t.t_name, c.c_name, avg(sc.s_score) from scores sc
left join courses c on c.c_id = sc.c_id 
left join teachers t on t.t_id = c.t_id 
group by sc.c_id
order by avg(sc.s_score) desc;

20.统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

-- 查询课程表连接成绩表
select c.c_id, c.c_name,
((select count(1) from scores sc where sc.c_id = c.c_id and sc.s_score <= 100 and sc.s_score > 85) / (select count(1) from scores sc where sc.c_id = c.c_id)) '[100, 85]',

((select count(1) from scores sc where sc.c_id = c.c_id and sc.s_score <= 85 and sc.s_score > 70) / (select count(1) from scores sc where sc.c_id = c.c_id)) '[85, 70]',

((select count(1) from scores sc where sc.c_id = c.c_id and sc.s_score <= 70 and sc.s_score > 60) / (select count(1) from scores sc where sc.c_id = c.c_id)) '[70, 60]',

((select count(1) from scores sc where sc.c_id = c.c_id and sc.s_score <= 60 and sc.s_score > 0) / (select count(1) from scores sc where sc.c_id = c.c_id)) '[60, 0]'

from courses c order by c.c_id;

 21.查询学生平均成绩及其名次

-- 查询学生的平均成绩
select st.s_id, st.s_name, round((case when avg(sc.s_score) is null then 0 else avg(sc.s_score) end), 2) '平均分' from students st 
left join scores sc on sc.s_id = st.s_id
group by st.s_id order by sc.s_score desc;



-- 打印排名
set @i = 0;
select a.*, @i := @i+1 '排名' from(
    select st.s_id, st.s_name, round((case when avg(sc.s_score) is null then 0 else     
    avg(sc.s_score) end), 2) '平均分' from students st 
    left join scores sc on sc.s_id = st.s_id
    group by st.s_id order by sc.s_score desc
) a;

22.查询课程编号01的成绩前三名的记录

select st.s_id, st.s_name, c.c_name, sc.s_score from students st 
left join scores sc on sc.s_id = st.s_id 
inner join courses c on c.c_id = sc.c_id and c.c_id = '01'
order by sc.s_score desc limit 0, 2;

23.查询每门课程被选修的学生数

select c.c_id, c.c_name, count(sc.s_id) from courses c 
left join scores sc on sc.c_id = c.c_id
group by c.c_id;

 

24. 查询出只有两门课程的全部学生的学号和姓名

-- 查询成绩表中只有两个课程的学生id
select s_id from scores group by s_id having count(c_id) = 2;


-- 连接学生表
select st.s_id, st.s_name from students st 
left join scores sc on sc.s_id = st.s_id
group by sc.s_id having count(sc.c_id) = 2;

25. 查询男生、女生人数

select st.s_sex, count(1) from students st group by st.s_sex;

 26.查询名字中含有"风"字的学生信息

select st.* from students st where st.s_name like '%风%';

27. 查询同名同性学生名单，并统计同名人数

select st.*, count(1) from students st group by st.s_name, st.s_sex having count(1) > 1;

28. 查询1990年出生的学生名单

select st.* from students st where st.s_birth like '1990%';

29. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

select c.c_id, c.c_name, avg(sc.s_score) from courses c 
inner join scores sc on c.c_id = sc.c_id 
group by sc.c_id order by avg(sc.s_score) desc, c.c_id asc;

30. 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

select st.s_id, st.s_name, avg(sc.s_score) from students st 
left join scores sc on sc.s_id = st.s_id
group by st.s_id having avg(sc.s_score) >= 85;

 31.查询课程名称为"数学"，且分数低于60的学生姓名和分数

-- 先查询课程是数学，然后成绩低于60的学生id
select sc.s_id from scores sc 
inner join courses c on sc.c_id = c.c_id 
where c.c_name = '数学' and sc.s_score < 60;


-- 连接学生表
select st.s_id, st.s_name, sc.s_score from students st 
inner join scores sc on sc.s_id = st.s_id and sc.s_score < 60
inner join courses c on c.c_id = sc.c_id and c.c_name = '数学';

32. 查询所有学生的课程及分数情况

select st.s_id, st.s_name, c.c_name, sc.s_score from students st 
left join scores sc on sc.s_id = st.s_id 
left join courses c on c.c_id = sc.c_id
order by st.s_id, c.c_name;

33.查询本周过生日的学生

select st.* from students st
where week(now()) = week(date_format(st.s_borth, '%Y%m%d'));

-- 这种处理有点问题

34.查询本月过生日的学生

select st.* from students st
where month(now()) = month(date_format(st.s_birth, '%Y%m%d'));

35. 查询下月过生日的学生

select st.* from students st 
where month(timestampadd(month, 1, now())) = month(date_format(st.s_birth, '%Y%m%d'));

最后还想说一下，只有打好基础了才能走的更远更快， 共勉~

说明：

这个分类专栏是我学习SQL的笔记与总结，其内容包含

• Linux下ORACLE的安装
• 笔记总结（思维导图、文本）
• 练习题（思路、注意事项）

---

练习题：

1、选择部门30中的所有员工；      

2、列出所有办事员(CLERK)的姓名，编号和部门编号；

3、找出奖金高于薪金的员工；

4、找出奖金高于薪金的60%的员工；

5、找出部门10中的所有经理(MANAGER)和部门20中所有的办事员(CLERK)的详细资料；

6、找出部门10中所有的经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料；

7、找出收取奖金的员工的不同工作；

8、找出不收取奖金或者收取的奖金低于100的员工；

9、找出各月倒数第三天受雇的所有员工；

10、找出早于十三年前受雇的员工；

       如果要求年份，最准确的是使用总月数/13

11、以首字母大写的方式显示所有员工的姓名；

12、显示正好为5个字符的员工的姓名；

13、显示不带有“R”的员工姓名；     

14、显示所有员工姓名的前三个字符；

15、显示所有员工的姓名，用“a”替换所有的“A”；

17、显示员工的详细资料，按姓名排序；

18、显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面；

19、显示所有员工的姓名、工作和薪金，按工作的降序排序，若工作相同则按薪金排序；

20、显示所有员工姓名、加入公司的年份和月份，按受雇日期所在月排序，若月份相同则将最早年份的员工排在最前面；

21、显示在一个月为30天的情况所有员工的日薪金，忽略余数；

22、找出(任何年份的)2月受聘的所有员工；

23、对于每个员工，显示其加入公司的天数；

24、显示姓名字段的任何位置包含“A”的所有员工的姓名；

25、以年月日的方式显示所有员工的服务年限；

---

答案：

基本SQL语句练习

以下SQL语句 我只将关键字段大写了

1、选择部门30中的所有员工；

要查的是所有员工

范围限定的是部门30

SQL> select * from emp where deptno = 30;

      

2、列出所有办事员(CLERK)的姓名，编号和部门编号；

要查的是姓名，编号和部门编号

范围限定的是办事员

SQL> select empno,ename,deptno from emp where job='CLERK';

      

3、找出奖金高于薪金的员工；

要查的是员工

范围限定的是奖金高于薪金

SQL> select * from emp where comm>sal;

 

4、找出奖金高于薪金的60%的员工；

要查的是员工

范围限定的是奖金高于薪金60%

SQL> select * from emp where comm>(sal*0.6);

 

5、找出部门10中的所有经理(MANAGER)和部门20中所有的办事员(CLERK)的详细资料；

要查的是所有资料

范围限定的是部门10中的所有经理 部门20中所有的办事员

注意：经理与办事员字段要大写，查询的关系是or

SQL>select * from emp

where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK');

 

6、找出部门10中所有的经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料；

要查的是所有资料，范围限定的是

部门10中的所有经理

部门20中所有的办事员

既不是经理又不是办事员但其薪金大于或等于2000

select * from emp

where (deptno=10 and job='MANAGER')

or (deptno=20 and job='CLERK')

or ((Job!='MANAGER' and job!='CLERK') and sal > =2000);

第三个条件也可以用IN来进行范围限定 根据题中的要求 使用not in

(job not in ('MANAGER','CLERK') and sal > =2000)

             

7、找出收取奖金的员工的不同工作；

要查的是工作，

范围限定的是

收取奖金的员工

SQL> select job from emp where comm is not null;

SQL> select job from emp where not comm is null;

用distinct来删除重复项

SQL> select distinct job from emp where not comm is null;

 

8、找出不收取奖金或者收取的奖金低于100的员工；

要查的是员工（详细资料），

范围限定的是

不收取奖金  comm为null

收取的奖金低于100

SQL> select * from emp where comm is null or comm <100;

 

9、找出各月倒数第三天受雇的所有员工；

要查的是员工（详细资料），

范围限定的是

各月倒数第三天受雇

思路：

       先取出雇佣日期当月的最后一天 （将其天数取出来） 然后减去2 如果是受雇的天数，那么就符合条件。

步骤：

1. 先取出雇佣日期的day1
2. 取出雇佣月份的最后一天day2
3. 用大日期减小日期 day2-day1 如果等于2就输出

select last_day(hiredate) from emp;

SQL> select hiredate,to_char(hiredate,'yyyy-mm-dd'),to_char(hiredate,'  dd')day1 from emp;


SQL>select last_day(hiredate),

to_char(last_day(hiredate),'yyyy-mm-dd'),

to_char(last_day(hiredate),'  dd')day2

from emp;

 

为了计算所以只保留需要的数据字段 day1 day2

select to_char(hiredate,'  dd')day1 from emp;

select to_char(last_day(hiredate),'  dd')day2 from emp;

最终：

select * from emp where ((to_char(last_day(hiredate),'  dd') - to_char(hiredate,'  dd')) =2);

 

检查：可见第五个人确实是当月的倒数第三天 当月有30天 倒数第三天就是28天

另外一种做法是不用取出来具体的天数 直接用当月最后一天减去2 如果等于hiredate那就符合条件

SQL> SELECT * FROM EMP WHERE LAST_DAY(HIREDATE)-2=HIREDATE;

10、找出早于十三年前受雇的员工；

思路：

       首先将受雇的年份取出来，然后用现在的年份受雇的年份   >13 就打印

select to_char(hiredate,'yyyy')year1 from emp;

select to_char(sysdate,' yyyy')year2 from emp;

 

最终：

select * from emp where (to_char(sysdate,' yyyy') - to_char(hiredate,'yyyy')) > 13 ;

 

做法2：

SQL> SELECT * FROM EMP WHERE MONTHS_BETWEEN(SYSDATE,HIREDATE)/12>13;

能用函数尽量函数，因为十分便捷

 

11、以首字母大写的方式显示所有员工的姓名；

SQL> select initcap(ename) from emp;

 

12、显示正好为5个字符的员工的姓名；

SQL> select ename from emp where length(ename)=5;

13、显示不带有“R”的员工姓名；

SQL> select ename from emp where ename not like '%R%';

      

14、显示所有员工姓名的前三个字符；

按照的是正常的数数，而不是从0开始，从0开始数的是程序员

SQL> select ename,substr(ename,0,3) from emp;

SQL> select ename,substr(ename,1,3) from emp;

      

15、显示所有员工的姓名，用“a”替换所有的“A”；

SQL> select replace(ename,'A','a') from emp;

      

17、显示员工的详细资料，按姓名排序；

排序默认用的是升序

SQL> select * from emp order by ename;

SQL> select * from emp order by ename asc;

      

18、显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面；

注意：这里要按照升序排序，因为要将时间上小的排在前面（年长的）

SQL> select ename,hiredate from emp order by hiredate asc;

 

19、显示所有员工的姓名、工作和薪金，按工作的降序排序，若工作相同则按薪金排序；

SQL> select ename,job,sal from emp order by job desc,sal asc;

      

20、显示所有员工姓名、加入公司的年份和月份，按受雇日期所在月排序，若月份相同则将最早年份的员工排在最前面；

 

由于出现了年份与月份，则应该先进行拆分

月份按照默认的升序排序，最早年份的员工则按照升序

总结：

一旦出现与年份相关的，想要年长的排在前面，那么就按照升序排序

       select  ename,to_char(hiredate,'yyyy') year,to_char(hiredate,'   mm') month from emp

order by month,year asc;

             

21、显示在一个月为30天的情况所有员工的日薪金，忽略余数；

要查的是日薪金（忽略余数）利用别名（日薪金）

范围限定的是 一个月为30天

思路：

       用sal/30 并忽略余数，别名替代算出来的日薪金

select ename,trunc(sal/30)day_sal from emp;

 

22、找出(任何年份的)2月受聘的所有员工；

要查的是员工

范围限定的是2月受聘的

思路：

       利用to_char转换函数取出月份 减去02 如果等于0 就打印

 

select ename from emp where ((to_char(hiredate,' mm') -02)=00);

如果需要对所有信息都打印，那么就用*

select * from emp where ((to_char(hiredate,' mm') -02)=00);

检查：可见只有这两个人的受雇月份就是2月

 

23、对于每个员工，显示其加入公司的天数；

要查的是员工与雇佣的天数

思路：

       用当前日期减去雇佣的日期就是加入的天数

 

       select ename,hiredate,sysdate-hiredate from emp;

 

24、显示姓名字段的任何位置包含“A”的所有员工的姓名；

       思路：

       采用模糊查询

SQL> select ename from emp where ename like '%A%';

 

25、以年月日的方式显示所有员工的服务年限；

第一步：求出每个雇员的雇佣年数：被雇佣的总月数/12=年数

select  ename,hiredate,trunc(months_between(sysdate,hiredate)/12)toatl_year from emp;

 

第二步：求出月数，以上计算之中被忽略的小数点都是月份，所以直接取余即可。

select ename,hiredate,

trunc(months_between(sysdate,hiredate)/12)toatl_year,

trunc(mod(months_between(sysdate,hiredate),12))total_month

from emp;

 

第三步：求出天数，最准确的做法是在不超过30天的范围内计算；

当前时间用sysdate取出，雇佣时期用hiredate取出，可是两者中间的差距太大，所以肯定的会有误差，那么就必须想办法将hiredate日期提升到与sysdate差距在30天的范围之内，

有两个函数；

       MONTHS_BETWEEN();求出两个日期间的月数。

       ADD_MONTHS():在一个日期上加入指定的月之后的日期。

如果说hiredate+与今天相距的月数=一个新的日期，而且这个新的日期肯定和sysdate相距不超过30天。

                                  

       1987入职 +月数（month between（sys date，hiredate  ）

                                  

       SQL> select ename,hiredate,

              trunc(months_between(sysdate,hiredate)/12)year,

              trunc(mod(months_between(sysdate,hiredate),12))month,

              trunc(sysdate-add_months(hiredate,months_between(sysdate,hiredate))) day

              from emp;    

 

      

 

https://www.cnblogs.com/JunCen/p/12925266.html
数据库结构
创建四张表 分别存储 学生信息 课程信息 分数 讲师信息表 存储相应数据
学生信息表 Student
字段名    字段类型    字段约束 / 含义
Sno    Varchar(3)    Not null  / 学员编号
Sname    Varchar(4)    Not null  / 学员姓名
Ssex    Varchar(2)    Not null  / 性别
Sbirthday    Datetime    生日
Classnum    Varchar(5)    班级号

CREATE TABLE STUDENT
(
SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME,
CLASS VARCHAR(5)
)
课程信息表 course
字段名    字段类型    字段约束 / 含义
Cno    Varchar(5)    Not null / 课程编号
Cname    Varchar(10)    Not null / 课程名称
Tno    Varchar(10)    Not null / 授课老师编号


CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL,
CNAME VARCHAR(10) NOT NULL,
TNO VARCHAR(10) NOT NULL)
成绩表score
字段名    字段类型    字段约束 / 含义
Sno    Varchar(3)    Not null  / 学员编号
Cno    Varchar(5)    Not null  / 课程编号
Degree    Double(3,1)    Not null  / 分数

CREATE TABLE SCORE
(SNO VARCHAR(3) NOT NULL,
CNO VARCHAR(5) NOT NULL,
DEGREE NUMERIC(10, 1) NOT NULL)
讲师表teacher
字段名    字段类型    字段约束 / 含义
Tno    Varchar(3)    Not null  / 讲师编号
Tname    Varchar(4)    Not null  / 讲师姓名
Tsex    Varchar(2)    Not null  / 讲师性别
Tbirthday    Datetime    Not null  / 出生日期
Prof    Varchar(6)    等级
Depart    Varchar(10)    所属院系

CREATE TABLE TEACHER
(TNO VARCHAR(3) NOT NULL,
TNAME VARCHAR(4) NOT NULL, TSEX VARCHAR(2) NOT NULL,
TBIRTHDAY DATETIME NOT NULL, PROF VARCHAR(6),
DEPART VARCHAR(10) NOT NULL);

向表中存储数据
INSERT INTO student (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,"1977-09-01",95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,"1975-10-02",95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,"1976-01-23",95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,"1976-02-20",95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,"1975-02-10",95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,"1974-06-03",95031);
GO
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' ,856);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,100);
GO
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);
GO
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');


1、 查询Student表中的所有记录的Sname、Ssex和Class列。
        
2、 查询教师所有的单位即不重复的Depart列。
    
3、 查询Student表的所有记录。

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

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

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

7、 以Class降序查询Student表的所有记录。
    
8、 以Cno升序、Degree降序查询Score表的所有记录。

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

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

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

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

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

14、查询所有学生的Sname、Cno和Degree列。

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

16、查询所有学生的Sname、Cname和Degree列。

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

18、假设使用如下命令建立了一个grade表：

create table grade(low number(3,0),upp number(3),rank char(1));
insert into grade values(90,100,’A’);
insert into grade values(80,89,’B’);
insert into grade values(70,79,’C’);
insert into grade values(60,69,’D’);
insert into grade values(0,59,’E’);
commit;
现查询所有同学的Sno、Cno和rank列。
    
19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
    

20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。


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

22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

23、查询“张旭“教师任课的学生成绩。

24、查询选修某课程的同学人数多于5人的教师姓名。

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

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

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

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

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

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

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

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

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

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

35 查询所有未讲课的教师的Tname和Depart.
    
36、查询至少有2名男生的班号。
    
37、查询Student表中不姓“王”的同学记录。

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

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

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

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

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

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

44、查询和“李军”同性别并同班的同学Sname.
    
45、查询所有选修“计算机导论”课程的“男”同学的成绩表
 

https://blog.csdn.net/qq_43154385/article/details/87967878
–1.学生表
Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
–2.课程表
Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
–3.教师表
Teacher(t_id,t_name) –教师编号,教师姓名
–4.成绩表
Score(s_id,c_id,s_score) –学生编号,课程编号,分数

--建表
--学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
--课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
--教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
--成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
--插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
--课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

--教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数


-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数


-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩


-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
        -- (包括有成绩的和无成绩的)


-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩


-- 6、查询"李"姓老师的数量


-- 7、查询学过"张三"老师授课的同学的信息


-- 8、查询没学过"张三"老师授课的同学的信息


-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息


-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息


-- 11、查询没有学全所有课程的同学的信息


-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息


-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息


-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名


-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩


-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息


-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩


-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90


-- 19、按各科成绩进行排序，并显示排名


-- 20、查询学生的总成绩并进行排名


-- 21、查询不同老师所教不同课程平均分从高到低显示


-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩


-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比


-- 24、查询学生平均成绩及其名次


-- 25、查询各科成绩前三名的记录


-- 26、查询每门课程被选修的学生数


-- 27、查询出只有两门课程的全部学生的学号和姓名


-- 28、查询男生、女生人数


-- 29、查询名字中含有"风"字的学生信息


-- 30、查询同名同性学生名单，并统计同名人数


-- 31、查询1990年出生的学生名单


-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列


-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩


-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数


-- 35、查询所有学生的课程及分数情况；


-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数


-- 37、查询不及格的课程


-- 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名


-- 39、求每门课程的学生人数


-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩


-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩


-- 42、查询每门功成绩最好的前两名


-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列  


-- 44、检索至少选修两门课程的学生学号


-- 45、查询选修了全部课程的学生信息


-- 46、查询各学生的年龄


-- 47、查询本周过生日的学生
  -- 此处可能有问题,week函数取的为当前年的第几周,2017-12-12是第50周而2018-12-12是第49周,可以取月份,day,星期几(%w),
  -- 再判断本周是否会持续到下一个月进行判断,太麻烦,不会写


-- 48、查询下周过生日的学生


-- 49、查询本月过生日的学生

-- 50、查询下月过生日的学生
 -- 注意:当 当前月为12时,用month(now())+1为13而不是1,可用timestampadd()函数或mod取模