Oracle 11g Study Notes 10_26

1. Basic Oracle Query

This is the most important operation on a table.
* View the table structure

desc dept;

. Query all columns

select * from dept;

. Query specified Columns

select empno, ename, job, deptno from emp where ename = 'SMITH';

In Oracle, the content enclosed in single quotes is case sensitive.
. Cancel duplicate rows

select distinct deptno from emp;

. Use arithmetic expression
. Use alias

Select ename 'name', Sal * 2 as "annual income" from EMP;

* How to Handle null values
Use the nvl function for processing

Select Sal * 13 + nvl (Comm, 0) * 13 "annual salary", ename, comm from EMP;

Nvl (Comm, 0) indicates that, if comm is empty, comm is expressed as 0. If comm is not empty, no change is made.
. How to connect strings

select ename || 'is a ' || job from emp;

. Use where clause?
Displays employees whose salaries are higher than 3000;

SELECT * FROM emp WHERE sal > 3000;  

Find the employees who have joined the company after 1982.1.1;

Select ename, hiredate from EMP where hiredate> '1-January 1, January-1982 ';

Pay attention to the date format.
Show employees with salaries ranging from 2000 to 3000

SELECT ename,sal FROM emp WHERE sal >=2000 AND sal <= 3000;  

* How to Use the like Operator
%: 0 to multiple characters _: represents any single character
Display the name and salary of the employee whose first character is S

SELECT ename,sal FROM emp WHERE ename like 'S%';  

Display the names and salaries of all employees whose third character is uppercase o

SELECT ename,sal FROM emp WHERE ename like '__O%'; 

. Use in the where Condition
Displays the number of employees whose empno is 7844,783

SELECT * FROM emp WHERE empno in (7844, 7839,123,456);

Here, in (7844,783 123,456,); indicates that empno contains;
. Use the is null operator 
Displays employees without superiors
Incorrect syntax: Select * from EMP where Mgr = '';
Statement: Select * from EMP where MgrIs null;
* Use of order
Display the EMP table salary sorted from high to low

select * from emp order by sal desc;

Here, order by Sal indicates sorting by SAL, DESC indicates descending order, and ASC indicates Ascending Order (default ).
Displays the descending order of employees' salaries by Department number.

select * from emp order by deptno, empno desc;

You can use set timing on to query the operation time;

When you want to query data with null values in a field, you can write

select * from student where birthdya is null;

Note: Double quotation marks must be added to the Chinese content.
2. Complex Query

. Data Group
-Max, Min, AVG, sum, count
. Display the highest salary and minimum wage among all employees

select max(sal),min(sal) from emp;

Displays the name and salary of the employee with the highest salary;
At first, I thought this question was very simple, but I tried it as below and the result was incorrect:
(Error) SQL> select ename, Sal from EMP
2 Where sal = max (SAL)
3 group by ename, Sal;
ORA-00934: grouping functions are not allowed here
(Incorrect) SQL> select ename, max (SAL) as "highest salary" from EMP;
ORA-00937: not a single component group Function
If one column is a grouping function, all other columns must be a grouping function.
Correct:

select ename, sal from emp where sal =(select max(sal) from emp);

Displays information about employees whose salaries are higher than average.
* Group by and having clauses
Group by is used to group statistics on query results.
The having clause is used to limit the display results of a group.
. Display the average and maximum salaries of each department

select deptno,avg(sal),max(sal) from emp group by deptno;

. Display the average salary and minimum wage for each position in each department

select avg(sal),max(sal),deptno,job from emp group by deptno,job;

Shows the average salary of a department number less than 2000 and its average salary.

 select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2500;

* Summary of data groups
The I grouping function can only appear in the selection list, having, and order by clauses. It cannot be used as a condition, for example, a condition in select or where.
Ii If select statements contain group by, having, and order by statements, their order is group by, having, and order.
Iii. If columns, expressions, and grouping functions exist in the selection column, one of these columns and expressions must appear in the group by clause; otherwise, an error occurs.
Iii. Multi-Table query

Multi-Table query refers to queries based on two or more tables or views. In practice, querying a single table cannot meet the requirements.
Display the employee name, employee salary, and department name, and sort by department.

select ename, sal, dname from emp, dept where emp.deptno = dept.deptno order by dname;

4. subquery

A subquery is a SELECT statement embedded in other SQL statements. Also called nested Query
* Single row subquery
A single row subquery refers to a query in which a subquery statement returns only one row of data;
. Displays all employees in the same department as Smith.

select * from emp   where deptno = (select deptno from emp where ename = 'SMITH');

* Multi-row subquery
Multi-row subquery refers to a subquery that returns multi-row data.
Query the names, positions, salaries, and department numbers of employees with the same work as department 10.

select * from emp where job in(select distinct job from emp where deptno = 10);

* Multi-column subquery
Multi-column subquery is a subquery statement that queries and returns data of multiple columns;
. Query all employees who have the same department and position as Smith.

select ename from emp where deptno = (select deptno from emp where ename = 'SMITH') and job = (select job from emp where ename = 'SMITH');

Or

select ename from emp where (deptno, job) = (select deptno, job from emp where ename = 'SMITH');