Oracle查询语句面试题
01． 查询员工表所有数据，并说明使用*的缺点 select * from emp; 01．查询职（job）为
查询员工表所有数据，并说明使用*的缺点
select * from emp;
查询职（job）为'PRESIDENT'的员工的工资
select sal from emp where job='PRESIDENT';
【专题推荐】：2020年oracle面试题汇总（最新）
02．查询佣金为0或为null的员工的信息
select * from emp where comm is null or comm=1;
03．查询入职日期在1981-5-1到1981-12-31之间的所有员工的信息
select * from emp where hiredate between to_date(‘1-5月-81’) and to_date(‘31-12月-81’)
04．.查询所有名字长度为4的员工的员工的编号，姓名
select empno,ename from emp where length(ename)=4
05．显示10号部门饿所有经理和20号部门的所有员工
select * from emp where job='MANAGER' and deptno=10 or job='CLERK' and deptno=20;
06．显示姓名没有'L'字的员工的详细信息或含有'SM'字的员工信息
select * from emp where ename not like '%L%' or ename like '%SM%';
07．显示各个部门经理的工资
select deptno,sal from emp where job='MANAGER';
08．显示佣金收入比工资高的员工的详细信息
select * from emp where comm>sal;
10．把hiredate列看做是员工的生日，求本月过生日的员工：
SQL> select * from emp where to_char(hiredate,'mm')=to_char(sysdate,'mm');
11．把hiredate列看做是员工的生日，求下月过生日的员工
SQL> select * from emp where to_char(hiredate,'mm')=to_char(add_months(sysdate,1),'mm');
12.求1982年入职的员工
SQL> select * from emp where to_char(hiredate,'yyyy')='1982';
hiredate是date类型的，1982是字符串类型的，类型匹配才可以
13．求1981年下半年入职的员工
select * from emp where hiredate between to_date('1981-7-1','yyyy-mm-dd') and to_date('1982-1-1','yyyy-mm-dd')-1;
注：to_char()函数和to_date()函数
to_char()函数是把日期的类型转换为指定的格式
to_date()是把字符串转换为日期类型
-1的原因是更精确
14．求1981年各个月入职的员工个数
SQL> select to_char(hiredate,'mm'),count(*) from emp where to_char(hiredate,'yyyy')='1981' group by to_char(hiredate,'mm') order by to_char(hiredate,'mm');
或
select to_char(hiredate,'mm'),count(*) from emp where to_char(hiredate,'yyyy')='1981' group by to_char(hiredate,'mm') order by to_char(hiredate,'mm');
PartII
01 .查询各个部门的平均工资
SQL> select deptno,avg(nvl(sal,0)) from emp group by deptno;
02.显示各种职位的最低工资
SQL> select job,min(sal) from emp group by job;
03．按照入职日期由新到旧排列员工信息
SQL> select hiredate from emp order by hiredate desc;
04．查询员工的基本信息，附加其上级的姓名（自关联）
SQL> select e.*,e1.ename from emp e,emp e1 where e.mgr=e1.empno;
05．显示工资比’ALLEN’高的所有员工的姓名和工作
SQL> select ename,sal from emp where sal>(select sal from emp where ename='ALLEN');
06．显示与scott从事相同工作的员工的信息（子查询）
SQL> select * from emp where job=(select job from emp where ename='SCOTT');
07．显示销售部(‘SALES’)员工的姓名
SQL> select e.ename from emp e inner join dept d on e.deptno=d.deptno where d.dname='SALES';
08．显示与30号门’MARTIN’员工工资相同的员工的姓名和工资
SQL> select ename,sal from emp where sal=(select sal from emp where deptno=30 and ename='MARTIN');
09．查询所有工资高于平均工资（包括所有员工）的销售人员
SQL> select * from emp where sal>( select avg(sal) from emp) and job='SALESMAN';
或
SQL> select * from emp where job='SALESMAN' and sal>(select avg(sal) from emp);
10．显示所有职员的姓名及其所在部门的名称和工资(表连接)
SQL> select e.*,e.sal,d.dname from emp e inner join dept d on e.deptno=d.deptno;
11．查询在研发部(RESEARCH)工作人员的编号，姓名，，工作部门，工作所在地
SQL> select e.empno,e.ename,d.dname,d.loc from emp e inner join dept d on e.deptno=d.deptno where dname='RESEARCH';
12．查询各个部门的名称和员工人数
select e.deptno,d.dname,count(*) from emp e inner join dept d on e.deptno=d.deptno group by e.deptno,d.dname
分析：
SQL> select d.dname from emp e inner join dept d on e.deptno=d.deptno group by e.deptno,d.dname; DNAME -------------- ACCOUNTING RESEARCH SALES SQL> select e.deptno,d.dname from emp e inner join dept d on e.deptno=d.deptno group by e.deptno,d.dname; DEPTNO DNAME ------ -------------- 10 ACCOUNTING 20 RESEARCH 30 SALES SQL> select e.deptno,d.dname,count(*) from emp e inner join dept d on e.deptno=d.deptno group by e.deptno,d.dname; DEPTNO DNAME COUNT(*) ------ -------------- ---------- 10 ACCOUNTING 3
13．查询各个部门员工工资大于平均工资（平均工资包括所有员工）的人数和员工职位(子查询)
SQL> select count(*),job from emp where sal>(select avg(sal) from emp) group by job;
14．查询工资相同的员工的工资和姓名（子查询）
SQL> select sal,ename from emp e where(select count(*) from emp where sal=e.sal group by sal)>1;
或
SQL> select e.sal,e.ename from emp e,emp e1 where e.sal=e1.sal and e.ename<>e1.ename;
相关学习推荐：oracle数据库学习教程
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1664
14
1422
52
1316
25
1266
29
1239
24
When might a full table scan be faster than using an index in MySQL?
Apr 09, 2025 am 12:05 AM
Full table scanning may be faster in MySQL than using indexes. Specific cases include: 1) the data volume is small; 2) when the query returns a large amount of data; 3) when the index column is not highly selective; 4) when the complex query. By analyzing query plans, optimizing indexes, avoiding over-index and regularly maintaining tables, you can make the best choices in practical applications.
MySQL: Simple Concepts for Easy Learning
Apr 10, 2025 am 09:29 AM
MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.
MySQL: The Ease of Data Management for Beginners
Apr 09, 2025 am 12:07 AM
MySQL is suitable for beginners because it is simple to install, powerful and easy to manage data. 1. Simple installation and configuration, suitable for a variety of operating systems. 2. Support basic operations such as creating databases and tables, inserting, querying, updating and deleting data. 3. Provide advanced functions such as JOIN operations and subqueries. 4. Performance can be improved through indexing, query optimization and table partitioning. 5. Support backup, recovery and security measures to ensure data security and consistency.
MySQL's Role: Databases in Web Applications
Apr 17, 2025 am 12:23 AM
The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.
MySQL: An Introduction to the World's Most Popular Database
Apr 12, 2025 am 12:18 AM
MySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables.
Explain the role of InnoDB redo logs and undo logs.
Apr 15, 2025 am 12:16 AM
InnoDB uses redologs and undologs to ensure data consistency and reliability. 1.redologs record data page modification to ensure crash recovery and transaction persistence. 2.undologs records the original data value and supports transaction rollback and MVCC.
MySQL's Place: Databases and Programming
Apr 13, 2025 am 12:18 AM
MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen
Why Use MySQL? Benefits and Advantages
Apr 12, 2025 am 12:17 AM
MySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions.
