- 检索dept中所有的记录,并按部门名称排序。
select *
from univ.dept
order by dname;
- 列出工资(SAL)在15000到25000之间的所有员工的ENAME,SAL,并按ENAME排序。
select ename, sal
from univ.emp
where sal between 15000 and 25000
order by ename;
- 显示DEPT表中的部门编号和部门名称,并按部门名称排序。
select deptno, dname
from univ.dept
order by dname;
- 显示所有不同的工作类型,并降序排序。
select distinct job
from univ.emp
order by job desc;
- 列出没有提成(comm)的所有职工的姓名与工资,并按姓名排序。
select ename, sal
from univ.emp
where comm is null
order by ename;
- 列出部门编号是20,工作是“clerk”(办事员)的职工信息,并按姓名排序。
select *
from univ.emp
where job = 'clerk' and deptno = 20
order by ename;
- 显示姓名中包含AR或IN的职工姓名,并按姓名排序。
select ename
from univ.emp
where ename like '%AR%' or ename like '%IN%'
order by ename;
- 显示姓名中包含AR和IN的职工姓名,并按姓名排序。
select ename
from univ.emp
where ename like '%AR%' and ename like '%IN%'
order by ename;
- 显示所有职工的姓名和各项收入总和(sal+comm),并按姓名排序。
select ename, sal+nvl(comm, 0) as total
from univ.emp
order by ename;
- 查询每个部门的平均工资(用round函数取整),并按部门编号排序。
select deptno, round(avg(sal)) as "平均工资"
from univ.emp
group by deptno
order by deptno;
- 查询出每个部门中工资最高的职工,并按部门编号排序。若同一部门有多个最高工资者,再按职工号排序。
select *
from univ.emp
where sal in
(
select max(sal)
from univ.emp
group by deptno
)
order by deptno, empno;
- 查询出每个部门比本部门平均工资高的职工人数,并按部门编号排序。
select deptno, count(empno) as "人数"
from univ.emp natural join (select deptno, avg(sal) as avgsal from univ.emp group by deptno)
where sal > avgsal
group by deptno
order by deptno;
- 列出至少有一个职工的所有部门,并按部门编号排序。
select *
from univ.dept
where deptno in
(
select deptno
from univ.emp
group by deptno
having count(deptno) > 0
)
order by deptno;
- 列出工资比“JONES”多的所有职工,并按职工编号排序。
select *
from univ.emp
where sal >
(
select sal
from univ.emp
where ename = 'JONES'
)
order by empno;
- 列出所有职工的姓名及其直接上级的姓名,并按职工姓名排序。
select a.ename, b.ename
from univ.emp a, univ.emp b
where a.mgr = b.empno
order by a.ename;
- 列出受雇日期早于其直接上级的所有职工,并按姓名排序。
select a.*
from univ.emp a, univ.emp b
where a.mgr = b.empno and a.hiredate < b.hiredate
order by a.ename;
- 列出部门名称和这些部门职工的编号、姓名,要求同时列出那些没有职工的部门,并按部门名称和职工编号排序。
select dname, empno, ename
from univ.dept natural left outer join univ.emp
order by dname, empno;
- 列出所有办事员的姓名及其部门名称,并按姓名排序。
select ename, dname
from univ.emp natural full outer join univ.dept
where job = 'clerk'
order by ename;
- 列出最低工资大于35000的各种工作类型,并降序排序。
select distinct job
from univ.emp
where sal > 35000
order by job desc;
- 列出在销售部(“sales”)工作的职工的姓名,并按姓名排序。
select emp.ename
from univ.emp natural join univ.dept
where dept.dname = 'SALES'
order by emp.ename;
- 列出工资高于公司平均工资的所有职工,并按姓名排序。
select *
from univ.emp
where sal >
(
select avg(sal)
from univ.emp
)
order by ename;
- 列出与“SCOTT”从事相同工作的所有职工,并按姓名排序。
select *
from univ.emp
where job =
(
select job
from univ.emp
where ename = 'SCOTT'
)
order by ename;
- 列出工资高于“30号部门的最高工资”的职工姓名和工资,并按姓名排序。***
select ename, sal
from univ.emp
where sal >
(
select max(sal)
from univ.emp
where emp.deptno = 30
)
order by ename;
- 列出工资高于在部门30工作的所有职工的工资的职工姓名和工资,并按姓名排序。***
select ename, sal
from univ.emp
where sal >
(
select max(sal)
from univ.emp
where emp.deptno = 30
)
order by ename;
- 列出在每个部门工作的职工数量、平均工资(用round函数取整),并按部门编号排序。
select deptno, count(empno) as "职工数量", round(avg(sal)) as "平均工资"
from univ.emp
group by deptno
order by deptno;
- 列出所有职工的姓名、部门名称和工资,并按姓名排序。
select ename, dname, sal
from univ.emp natural join univ.dept
order by ename;
- 列出所有部门的详细信息和部门人数,并按部门编号排序。
select deptno, dname, cid, loc, emps, income, nvl(count(ename), 0) as "部门人数"
from univ.dept natural left outer join univ.emp
group by deptno, dname, cid, loc, emps, income
order by deptno;
- 列出职工中各种工作类型的最低工资,并按工作类型排序。
select job, min(sal) as "最低工资"
from univ.emp
group by job
order by job;
- 列出各个部门的经理(“manager”)的最低工资。
select min(sal)
from univ.emp natural join univ.dept
where job = 'manager';
- 列出平均工资最高的部门的名称。***
select dname
from
(
select dname, avg(sal) as avgsal
from univ.emp natural join univ.dept
group by dname
)
where avgsal =
(
select max(avgsal)
from
(
select dname, avg(sal) as avgsal
from univ.emp natural join univ.dept
group by dname
)
);
-- having永远滴神
select dname
from univ.dept natural join univ.emp
group by dname
having avg(sal) >= all(select avg(sal) from univ.emp group by deptno);
- 列出不在北美工作职工的姓名,并按姓名排序。
select ename
from univ.emp natural join univ.dept natural join univ.countries
where region <> 'North America'
order by ename;
- 列出在亚洲工作且工资最高的职工的姓名,并按姓名排序。***
select ename, sal
from univ.emp
where sal in
(
select max(sal) as maxsal
from univ.emp natural join univ.dept natural join univ.countries
where region like '%Asia'
)
order by ename;
- 列出总裁(president)工作部门所在的国家。
select cid, cname, region
from univ.emp natural join univ.dept natural join univ.countries
where emp.job = 'president';
- 列出平均工资最高的国家。
select cname
from
(
select cname, dname
from univ.countries natural join univ.dept
)
where dname =
(
select dname from univ.dept natural join univ.emp
group by dname
having avg(sal) >= all(select avg(sal) from univ.emp group by deptno)
);
select cname
from univ.emp natural join univ.dept natural join univ.countries
group by cname
having avg(sal) >= all(select avg(sal) from univ.emp natural join univ.dept natural join univ.countries group by cname);
- 列出各部门名称及其人数,含没有职工的部门,并按部门名称排序。
select dname, nvl(count(empno), 0) as "人数"
from univ.dept natural left outer join univ.emp
group by dname
order by dname;
- 查询2016年考试成绩不到20分的学生的学号,按学号排列。
select id
from univ.student natural join univ.takes
where grade<20 and year=2016
order by id;
- 查询到2018年1月该毕业却没有毕业的学生的学号,按学号排列。***
select id
from univ.student
where status = '0' and status_date<'2014'
order by id;
- 列出英语系与数学系的教师的姓名与 工资,并按姓名排序。
select name, salary
from univ.instructor
where dept_name = '数学' or dept_name = '英语'
order by name;
- 列出英语系与数学系外其他系的姓“李”的教师姓名与工资,并按姓名排序。
(
select name, salary
from univ.instructor
where name like '李%'
)
minus
(
select name, salary
from univ.instructor
where dept_name = '数学' or dept_name = '英语'
)
order by name;
select name, salary
from univ.instructor
where dept_name <> '数学' and dept_name <> '英语' and name like '李%'
order by name;
- 列出名称中含有"计算机"的课程的名称与学分,并按课程名排序
select title, credits
from univ.course
where title like '%计算机%'
order by title;
- 列出所有姓名以"武"开头且只有3个字的学生的学号、姓名与所在班级,并按姓名排序
select id, name, class_name
from univ.student
where name like '武__'
order by name;
- 列出所有姓名以"李"开头、以"军"结束且只有3个字的学生的学号、姓名,并按姓名排序
select id, name
from univ.student
where name like '李_军'
order by name;
- 查询所有姓名中第2个字为"日"的学生的姓名与所在班级,并按姓名排序。
select name, class_name
from univ.student
where name like '_日%'
order by name;
- 列出2017年下学期选修了CS013号课程却没有及格的学生的学号及其成绩,并按学号排序。
select id, grade
from univ.takes
where year = 2017 and course_id = 'CS013' and grade < 60
order by id;
- 给出有5个学分的课程的名称,并按名称排序。
select title
from univ.course
where credits = 5
order by title;
- 查询专业表中有哪些系,给出系名,按升序排列
select distinct dept_name
from univ.major
order by dept_name;
- 查询软件工程专业2016年毕业的学生的学号和姓名,并按学号排序。
select id, name
from univ.student natural join univ.class
where major_name = '软件工程' and status = '1' and status_date = '2016'
order by id;
- 查询2017级软件工程1班是哪个系的,给出系名。
select dept_name
from univ.major
where major_name =
(
select major_name
from univ.class
where class_name = '2017级软件工程1班'
);
- 查询2015年选修了CS013号课程且姓“李”的学生的学号与姓名,并按学号排序。
select id, name
from univ.student natural join univ.takes
where year = 2015 and course_id = 'CS013' and name like '李%'
order by id;
- 查询2017年选修了CS001号课程且学号最后两位为“27”的学生的学号、姓名,以及课程名称及成绩,按学号排序。
select id, name, title, grade
from univ.student natural join univ.takes natural join univ.course
where year = 2017 and course_id = 'CS001' and id like '%27'
order by id;
- 查询2016年选修课程名为"C Programming"且学号最后一位为“9”的学生的学号、成绩以及该门课获得的学分,按学号排序。
select id, grade, credits
from univ.takes natural join univ.course
where year = 2016 and title = 'C Programming' and id like '%9'
order by id;
- 查询软件工程专业2017级还没有获得SE002号课程学分的学生的学号与姓名,按学号排序。
select id, name
from univ.student natural join univ.class natural join univ.takes
where class_name like '2017级软件工程%班' and course_id = 'SE002' and (grade < 60 or grade is null)
order by id;
- 查询2018年上学期PH001号课程考试成绩不及格的学生的学号与姓名,按学号排序。
select id, name
from univ.student natural join univ.takes
where year = 2018 and semester = '上' and course_id = 'PH001' and grade < 60
order by id;
- 查询2018年上学期“数据结构”课程的任课教师名单,按姓名排序。
select name
from univ.teaches natural join univ.instructor natural join univ.course
where year = 2018 and semester = '上' and title = '数据结构'
order by name;
- 查询2018年上学期叶映岚老师所授的课程,按课程名排序。
select course_id, title, major_name, course_type, term, credits
from univ.course natural join univ.teaches natural join univ.instructor
where year = 2018 and semester = '上' and name = '叶映岚'
order by title;
- 查询2018年上学期计算机系没有上课的教师名单,按姓名排序。
select name
from univ.instructor
where name not in
(
select name
from univ.instructor natural left outer join univ.teaches
where year = 2018 and semester = '上' and dept_name = '计算机'
)
and dept_name = '计算机'
order by name;
- 查询选修了CS013号课程但没有选修CS021号两门课程的学生的姓名,按姓名排序。
select name
from univ.student
where id in
(
(
select id
from univ.student natural join univ.takes
where course_id = 'CS013'
)
minus
(
select id
from univ.student natural join univ.takes
where course_id = 'CS021'
)
)
order by name;
- 查询2018年上学期选修了G0001号课程的学生的学号和班级,按学号排序。
select distinct id, class_name
from univ.takes natural join univ.student
where course_id = 'G0001' and year = 2018 and semester = '上'
order by id;
- 查询2018年上学期同时选修了G0001号和PH001课程的学生的学号和班级,按学号排序。
select distinct id, class_name
from univ.takes natural join univ.student
where year = 2018 and semester = '上' and course_id = 'G0001'
intersect
select distinct id, class_name
from univ.takes natural join univ.student
where year = 2018 and semester = '上' and course_id = 'PH001'
order by id;
- 查询选修了化学专业所开设的全部课程、2017年已经毕业的姓陈的学生的学号、姓名与班级,按学号排序。
select id, name, class_name
from univ.student natural join univ.class
where not exists
(
select *
from univ.course
where major_name='化学' and not exists
(
select *
from univ.takes
where course_id=univ.course.course_id and id=univ.student.id
)
) and status = 1 and status_date <= 2017 and name like '陈%'
order by id;
- 查询学号最后两位为09且至少选修了2015020101(学号)选修的全部课程的学生的学号与姓名,按学号排序。
select id, name
from univ.student
where not exists
(
(select course_id
from univ.takes
where id = '2015020101')
minus
(select course_id
from univ.takes
where student.id = takes.id and id like '%09')
)
order by id;
-- student.id = takes.id 作用是确保查询同一学生
- 查询学生总人数。
select count(id) as "总人数"
from univ.student;
- 查询选修了G0001号课程的学生人数。
select count(id) as "人数"
from univ.takes
where course_id = 'G0001';
- 统计选课人数最多的课程有多少人
select max(sumid) as "人数"
from
(
select count(id) as sumid
from univ.takes
group by course_id
);
- 查询平均成绩大于84的学生学号与姓名,按学号排序。
select id, name
from(
select id, name, avg(grade) as avg_grade
from univ.student natural join univ.takes
group by id, name
)
where avg_grade > 84
order by id;
- 查询到目前为止学号为2017010101的学生已经获得的学分数。
select sum(credits) as tot_cred
from univ.takes natural join univ.course
where id = '2017010101' and grade > 60;
- 查询2017年CS001号课程成绩超过该课程平均成绩20分的学生的学号与姓名,按学号排序。
select id, name
from univ.student natural join univ.takes
where year = 2017 and course_id = 'CS001' and grade >
(
select avg(grade)+20 as avg_grade
from univ.takes
where year = 2017 and course_id = 'CS001'
)
order by id;
- 查询每年都修了课程的学生人数,列出年份与人数,按年份升序排列。
select year as "年份", count(distinct id) as "人数"
from univ.takes
group by year
order by year;
- 查询每年都修了CS013号课程的学生人数,列出年份与人数,按年份升序排列。
select year as "年份", count(distinct id) as "人数"
from univ.takes
where course_id = 'CS013'
group by year
order by year;
sqlplus A201905556837/250836@172.22.112.234/orcl
70) 查询2010年5月以来的招生情况,列出年份与人数,按年份升序排列。学号的前4位为入学年份(使用substr函数去子串)。
select substr(id, 0, 4) as "年份", count(id) as "人数"
from univ.student
where substr(id, 0, 4) > 2009
group by substr(id, 0, 4)
order by substr(id, 0, 4);
- 查询从2016年开始,各个学期选修了课程的学生人数,列出年份、学期与人数,按年份与学期排序。
select year, semester, count(distinct id) as "人数"
from univ.takes natural join univ.course
where year > 2015
group by year, semester
order by year, semester;
- 查询2008年计算机系各位教师的姓名(含没有授课任务的教师)及其授课门数,按姓名排序。***
select name, count(distinct nvl(course_id, 0)) as "授课门数"
from univ.instructor natural left outer join univ.teaches
where (year = 2008 or year is null) and dept_name = '计算机'
group by name
order by name;
- 查询2015年-2018年选修“人工智能”课程的学生人数,列出年份与人数,按年份排序。
select year, count(id) as "人数"
from univ.takes natural join univ.course
where title = '人工智能' and year between 2015 and 2018
group by year
order by year;
- 查询2018年"大学物理"课程每个班及其平均成绩(用round函数取整),按班名排序。
select class_name, round(avg(grade))
from univ.takes natural join univ.course natural join univ.student
where title like '%大学物理%' and year = 2018
group by class_name
order by class_name;
- 查询2017年全校性通识课程G0001各专业的选课情况,列出专业名与选修学生人数,按专业名称排序。
select major_name, count(id)
from univ.takes natural join univ.student natural join univ.class
where course_id = 'G0001' and year = 2017
group by major_name
order by major_name;
检验视图创建是否正确代码如下:
set serveroutput on;
execute IsMyViewOK('V01');
set serveroutput on;
execute IsMyViewOK('V02');
set serveroutput on;
execute IsMyViewOK('V03');
set serveroutput on;
execute IsMyViewOK('V04');
set serveroutput on;
execute IsMyViewOK('V05');
set serveroutput on;
execute IsMyViewOK('V06');
set serveroutput on;
execute IsMyViewOK('V07');
set serveroutput on;
execute IsMyViewOK('V08');
set serveroutput on;
execute IsMyViewOK('V09');
set serveroutput on;
execute IsMyViewOK('V10');
set serveroutput on;
execute IsMyViewOK('V11');
set serveroutput on;
execute IsMyViewOK('V12');
set serveroutput on;
execute IsMyViewOK('V13');
set serveroutput on;
execute IsMyViewOK('V14');
set serveroutput on;
execute IsMyViewOK('V15');
set serveroutput on;
execute IsMyViewOK('V16');
set serveroutput on;
execute IsMyViewOK('V17');
set serveroutput on;
execute IsMyViewOK('V18');
set serveroutput on;
execute IsMyViewOK('V19');
set serveroutput on;
execute IsMyViewOK('V20');
set serveroutput on;
execute IsMyViewOK('V21');
set serveroutput on;
execute IsMyViewOK('V22');
set serveroutput on;
execute IsMyViewOK('V23');
set serveroutput on;
execute IsMyViewOK('V24');
set serveroutput on;
execute IsMyViewOK('V25');
set serveroutput on;
execute IsMyViewOK('V26');
set serveroutput on;
execute IsMyViewOK('V27');
set serveroutput on;
execute IsMyViewOK('V28');
set serveroutput on;
execute IsMyViewOK('V29');
set serveroutput on;
execute IsMyViewOK('V30');
set serveroutput on;
execute IsMyViewOK('V31');
set serveroutput on;
execute IsMyViewOK('V32');
set serveroutput on;
execute IsMyViewOK('V33');
set serveroutput on;
execute IsMyViewOK('V34');
set serveroutput on;
execute IsMyViewOK('V35');
set serveroutput on;
execute IsMyViewOK('V36');
set serveroutput on;
execute IsMyViewOK('V37');
set serveroutput on;
execute IsMyViewOK('V38');
set serveroutput on;
execute IsMyViewOK('V39');
set serveroutput on;
execute IsMyViewOK('V40');
set serveroutput on;
execute IsMyViewOK('V41');
set serveroutput on;
execute IsMyViewOK('V42');
set serveroutput on;
execute IsMyViewOK('V43');
set serveroutput on;
execute IsMyViewOK('V44');
set serveroutput on;
execute IsMyViewOK('V45');
set serveroutput on;
execute IsMyViewOK('V46');
set serveroutput on;
execute IsMyViewOK('V47');
set serveroutput on;
execute IsMyViewOK('V48');
set serveroutput on;
execute IsMyViewOK('V49');
set serveroutput on;
execute IsMyViewOK('V50');
set serveroutput on;
execute IsMyViewOK('V51');
set serveroutput on;
execute IsMyViewOK('V52');
set serveroutput on;
execute IsMyViewOK('V53');
set serveroutput on;
execute IsMyViewOK('V54');
set serveroutput on;
execute IsMyViewOK('V55');
set serveroutput on;
execute IsMyViewOK('V56');
set serveroutput on;
execute IsMyViewOK('V57');
set serveroutput on;
execute IsMyViewOK('V58');
set serveroutput on;
execute IsMyViewOK('V59');
set serveroutput on;
execute IsMyViewOK('V60');
set serveroutput on;
execute IsMyViewOK('V61');
set serveroutput on;
execute IsMyViewOK('V62');
set serveroutput on;
execute IsMyViewOK('V63');
set serveroutput on;
execute IsMyViewOK('V64');
set serveroutput on;
execute IsMyViewOK('V65');
set serveroutput on;
execute IsMyViewOK('V66');
set serveroutput on;
execute IsMyViewOK('V67');
set serveroutput on;
execute IsMyViewOK('V68');
set serveroutput on;
execute IsMyViewOK('V69');
set serveroutput on;
execute IsMyViewOK('V70');
set serveroutput on;
execute IsMyViewOK('V71')
set serveroutput on;
execute IsMyViewOK('V72');
set serveroutput on;
execute IsMyViewOK('V73');
set serveroutput on;
execute IsMyViewOK('V74');
set serveroutput on;
execute IsMyViewOK('V75');
有误请指正!代码供学习交流,切勿直接CV!!!敲码还需自身硬!
祝学弟学妹们考个好成绩,俺考了85嘿嘿嘿