数据库练习题

最新推荐文章于 2021-04-04 05:06:46 发布

一尘

最新推荐文章于 2021-04-04 05:06:46 发布

阅读量484

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/weixin_43976363/article/details/94390801

版权

where 子句中的单行子查询
在 where 子句中，可以使用单行比较运算符来比较某个表达式与子查询的结果；
举例:
查询跟 SMITH 的职位相同所有人的员工号、姓名、薪水、职位

 SQL> select empno , ename , sal , job from emp
2 where job = (
3 select job from emp where ename='SMITH'
4 );
EMPNO ENAME SAL JOB
---------- ---------- ---------- ---------
7369 SMITH 800 CLERK
7876 ADAMS 1100 CLERK
7900 JAMES 950 CLERK
7934 MILLER 1300 CLERK
9257 HUA'AN 1300 CLERK

having 子句中的单行子查询

哪些职位的平均工资比整个公司的平均工资高? (emp 表)
SQL> select job , avg(sal) from emp
2 group by job
3 having avg(sal) > ( select avg(sal) from emp ) ;
JOB AVG(SAL)
----------- ----------
PRESIDENT 5000
MANAGER 2758.33333

ANALYST 2466.66667

from 字句中的单行子查询

哪些员工的工资比整个公司的平均工资高
SQL> select e.empno , e.ename , e.sal , e.job , a.avgsal
2 from emp e , ( select avg(sal) avgsal from emp ) a
3 where e.sal > a.avgsal ;
EMPNO ENAME SAL JOB AVGSAL
---------- ---------- ---------- --------- ----------
7566 JONES 2975 MANAGER 1982.8125
7698 BLAKE 2850 MANAGER 1982.8125
7782 CLARK 2450 MANAGER 1982.8125
7788 SCOTT 3000 ANALYST 1982.8125
7839 KING 5000 PRESIDENT 1982.8125
7902 FORD 3000 ANALYST 1982.8125

使用单行子查询的常见错误
单行子查询中的常见错误包括返回多行数据和子查询包含了 order by 子句；
下例中是返回了多行数据的错误：

select first_name , dept_id from s_emp
where dept_id = ( select id from s_dept );
下例中是包含了 order by 时的错误：
select first_name , dept_id from s_emp
where dept_id in ( select id from s_dept order by id ) ;

如果希望对数据排序，那么只能在外查询语句中使用 order by 子句。
6.2.2 多行子查询
多行子查询可以返回单例多行数据。执行返回单例多行数据的子查询被称为多
行子查询。
多行子查询必须使用多行运算符来判断操作。
多行运算符包括：in 、not in、any、all、exists、not exists 。
举例:

显示与部门号为 20 的员工的岗位相同的雇员的信息：
select ename , deptno , job , sal from emp
where job in ( select distinct job from emp where deptno=20 ) ;
显示高于部门号为 20 的任意雇员工资的雇员信息：
elect ename , deptno , sal , job from emp
where sal > any ( select sal from emp where deptno=20 ) ;

6.2.3 多列子查询
子查询返回多个列的数据，那么称该子查询为多列子查询。
一般的查询中，如果需要比较两个或两个以上列的数据，那么必须在 where 子
句中使用逻辑运算符组合一个符合条件。如果通过使用多列子查询技术，就可以把一
个复合 where 子句写成一个单个 where 子句。
成对比较的多列子查询，表示多个列同时相等，才可以称为满足匹配的条件；
举例:

显示与 SMITH 在同一个部门、并且岗位相同的所有雇员的信息：
select ename , deptno , sal , job from emp
where (deptno , job) = (
select deptno , job from emp where ename='SMITH'
) ;

6.2.4 关联子查询
关联子查询，即相关子查询，指引用了外部 SQL 中某些表或某些列的子查询。

但是，外部 SQL 中不能引用子查询中的表或列！
这里的外部 SQL 可以是一条 select、update、delete 语句！
举例:

显示每个部门的最高工资的雇员信息(查询 s_emp 表)：
select id , first_name , salary , dept_id
from s_emp b
where salary = ( select max(salary) from s_emp a where a.dept_id = b.dept_id ) ;
显示每个部门的最高工资：
select deptno ,
( select max(sal) from emp b where b.deptno=a.deptno) maxsal
from emp a
order by deptno;

以下结合关联子查询，解释说明 exists 和 not exists 的用法：
exists 运算符会根据具体情况，产生相关行是否存在的逻辑值，即 true 或 false，
从而使得子查询没有实际意义而忽略，因此，子查询中可以使用一个虚拟的列名(如 ’
x ’ 或 * )。
举例:

显示工作在 NEW YORK 的雇员信息
select ename , deptno , sal , job
from emp
where exists (
select * from dept
where dept.deptno=emp.deptno and dept.loc='NEW YORK'
) ;
如果父查询中使用了子查询中的表或列，那么将会导致错误：
select ename , deptno , sal , job , loc
from emp
where exists (
select * from dept
where dept.deptno=emp.deptno and dept.loc='NEW YORK'
) ;
Oracle 提示: 第 1 行出现错误: ORA-00904: "LOC": 标识符无效

各个部门平均、最大、最小工资、人数，按照部门号升序排列
select avg(sal),max(sal),min(sal),count(*)
from emp
group by deptno
order by deptno
各个部门中工资大于 1500 的员工人数
select deptno,count(*)
from emp
where sal>1500
group by deptno
各个部门平均工资和人数，按照部门名字升序排列
select e.deptno,d.dname,avg(e.sal),count(*)
from emp e,dept d
where e.deptno=d.deptno
group by e.deptno,d.dname
order by e.deptno
列出每个部门中有同样工资的员工的统计信息，列出他们的部门号，工资，人数
该部门中工资高于 1000 的员工数量超过 2 人，列出符合条件的部门：显示部门名字、地区名称
select d.deptno,d.dname,d.loc,count()
from dept d,emp e
where d.deptno=e.deptno
and e.sal>1000
group by d.deptno,d.dname,d.loc
having count()>2
哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序）
select ename,sal
from emp
where sal>(select count(sal)
from emp b
)
order by sal desc
哪些员工的工资，介于 32 和 33 部门（33 高些）平均工资之间
select *
from s_emp e
where e.salary between (select avg(salary)
from s_emp
where dept_id=32)
and (select avg(salary)
from s_emp
where dept_id=33)
所在部门平均工资高于 1500 的员工名字
select deptno,ename
from emp
group by deptno,ename
having avg(sal)>1500
列出各个部门中工资最高的员工的信息：名字、部门号、工资
select id , first_name , salary , dept_id
from s_emp b
where salary = ( select max(salary) from s_emp a where a.dept_id = b.dept_id order by dept_id)
最高的部门平均工资值的是多少
哪些部门的人数比 32 号部门的人数多
select deptno
from dept
group by deptno
having count(deptno)>(select count(deptno)
from dept
where deptno=32)
Ben 的领导是谁（非关联子查询）
select first_name
from s_emp
where id=(select manager_id
from s_emp
where first_name=‘Ben’)
Ben 领导谁（非关联子查询）
Ben 的领导是谁（关联子查询）
Ben 领导谁（关联子查询）
select id,first_name
from s_emp
where manager_id=(select id
from s_emp
where first_name=‘Ben’)
列出在同一部门共事，入职日期晚但工资高于其他同事的员工：名字、工资、入职日期
（关联子查询）
哪些员工跟 Biri(last_name)不在同一个部门（非关联子查询）
select dept_id,first_name
from s_emp
where dept_id not in(select dept_id
from s_emp
where last_name=‘Biri’)
哪些员工跟 Biri(last_name)不在同一个部门（关联子查询）
Operations 部门有哪些职位（非关联子查询）
Operations 部门有哪些职位（关联子查询）
找出员工名字中含有 a 和 e 的
1 SELECT empno,ename
2 FROM emp
3* WHERE lower(ename) LIKE ‘%a%e%’ OR lower(ename) LIKE ‘%e%a%’
SQL> /
42 部门有哪些职位

1 select distinct dept_id,title
2 FROM s_emp
3* WHERE dept_id=42
SQL> /
DEPT_ID TITLE

    42 Warehouse Manager         
    42 Stock Clerk

SQL>
6. 除了 Sales 部门，还有哪些部门，列出部门号、部门名称。
SQL> ed
已写入 file afiedt.buf

1 SELECT deptno,dname
2 FROM dept
3* WHERE lower(dname) NOT IN ‘sales’
SQL> /

DEPTNO DNAME

    10 ACCOUNTING                   
    20 RESEARCH                     
    40 OPERATIONS

SQL>
8. 显示职位为 Stock Clerk 和 Sales Representative，年薪在 14400 和 17400 之间的
员工的信息：名字、职位、年薪。
SQL> select last_name,title,salary12
2 from s_emp
3 where title in (‘Stock Clerk’,‘Sales representative’) and salary12 between 14400 and 17400 ;

LAST_NAME TITLE SALARY*12

Maduro Stock Clerk 16800
Nozaki Stock Clerk 14400
9. 解释 select id ，commission_pct from s_emp where commission_pct is null
和 select id ， commission_pct from s_emp where commission_pct = null
的输出结果。
SQL> select id,commission_pct from s_emp where commission_pct is null ;

    ID COMMISSION_PCT

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    16

    ID COMMISSION_PCT

已选择20行。
SQL> select id,commission_pct from s_emp where commission_pct = null ;

未选定行
is null 判断是否为空，=null判断某个值是否等于‘null’，null=null和null<>null都为null ；
10. select 语句的输出结果为
select * from s_dept;
select * from s_emp;
select * from s_region;
select * from s_customer;
……
当前用户有多少张表，结果集有多少条记录。

                                                                                         
'SELECT*FROM'||TABLE_NAME||';'                                                           
---------------------------------------------                                            
SELECT * FROM EMP;                                                                       
SELECT * FROM DEPT;                                                                      
SELECT * FROM BONUS;                                                                     
SELECT * FROM SALGRADE;                                                                  
SELECT * FROM DUMMY;                                                                     
SELECT * FROM S_CUSTOMER;                                                                
SELECT * FROM S_DEPT;                                                                    
SELECT * FROM S_EMP;                                                                     
SELECT * FROM S_IMAGE;                                                                   
SELECT * FROM S_INVENTORY;                                                               
SELECT * FROM S_ITEM;                                                                    
                                                                                         
'SELECT*FROM'||TABLE_NAME||';'                                                           
---------------------------------------------                                            
SELECT * FROM S_LONGTEXT;                                                                
SELECT * FROM S_ORD;                                                                     
SELECT * FROM S_PRODUCT;                                                                 
SELECT * FROM S_REGION;                                                                  
SELECT * FROM S_TITLE;                                                                   
SELECT * FROM S_WAREHOUSE;                                                               
                                                                                         
已选择17行。                                                                                  
                                                                                         
SQL>

改变 NLS_LANG 的值，让 select to_char(salary*12,’L99,999.99’) from s_emp
输出结果的货币单位是￥和$
列出每个员工的名字，工资、涨薪后工资（涨幅为 8%），元为单位进行四舍五入
SQL> select last_name,salary,round(salary*1.08)
2 from s_emp ;
找出谁是最高领导，将名字按大写形式显示
SQL> select mgr,upper(ename)
2 from emp
3 where mgr is null ;
Ben 的领导是谁（Ben 向谁报告）。

1 select userid,upper(first_name)
2 from s_emp
3 where id=(select manager_id
4 from s_emp
5* where first_name=‘Ben’)

哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资
select e.first_name,e.salary,m.first_name,m.salary
from s_emp e,s_emp m
where e.manager_id=m.id
and e.salary>m.salary ;
哪些员工和 Biri(last_name)同部门
select first_name
from s_emp
where dept_id=(select dept_id from s_emp where last_name=‘Biri’);
哪些员工跟 Smith(last_name)做一样职位
select first_name
from s_emp
where title=(select title from s_emp where last_name=‘Smith’)
哪些员工跟 Biri(last_name)不在同一个部门
select first_name
from s_emp
where dept_id!=(select dept_id from s_emp where last_name=‘Biri’)
哪些员工跟 Smith(last_name)做不一样的职位
select first_name
from s_emp
where title!=(select title from s_emp where last_name=‘Smith’);
显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称
select e.ename,e.comm,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno and e.comm is not null
显示 Operations 部门有哪些职位
select title
from s_emp
where last_name=‘Operations’;
整个公司中，最高工资和最低工资相差多少
select max(sal)-min(sal)
from emp ;
提成大于 0 的人数
select count(*)
from emp
where comm>0
显示整个公司的最高工资、最低工资、工资总和、平均工资，保留到整数位。
select max(sal),min(sal),sum(sal),avg(sal)
from emp ;
整个公司有多少个领导
select count(mgr)
from emp ;
列出在同一部门入职日期晚但工资高于其他同事的员工：名字、工资、入职日期
select distinct a.ename,a.sal,a.hiredate
from emp a,emp b
where a.hiredate>b.hiredate and a.sal>b.sal