Oracle Table Query Two

1. Use logical operation symbols
Question: Query for wages higher than 500 or job manager employees, but also to meet their initials in uppercase J?
SELECT * from emp where (Sal > $ or job = ' MANAGER ') and ename like ' j% ';

2. Use the ORDER BY clause default ASC
Question: How do I display employee information in order of low to high wages?
SELECT * from emp order by Sal;
Problem: The employee's wages are sorted in descending order by department number ascending
SELECT * from emp order by deptno, Sal Desc;

3. Sorting using the alias of the column
Question: Sort by yearly salary
Select Ename, (SAL+NVL (comm,0)) *12 "annual salary" from the EMP Order by "annual salary" ASC;
Note: Aliases need to use "" in the ring, English does not need "" number

4. Aggregate function Usage: max,min,avg,sum,count
Question: How do i show the highest wage and minimum wage for all employees?
Select Max (sal), Min (sal) from EMP e;
Who is the man with the highest salary?
Error notation: Select ename, sal from EMP where Sal=max (SAL);
Correct wording: Select Ename, sal from EMP where sal= (select Max (SAL) from EMP);
Note: select Ename, Max (SAL) from EMP; This statement executes with an error, saying ora-00937: Not a single group of grouping functions. Because Max is a grouping function, and ename is not a grouping function ....
But select min (sal), Max (SAL) from EMP; This sentence can be executed. Because Min and Max are grouped functions, that is to say: if there is a grouping function in the column, the other must be a grouping function, otherwise it will be an error. It's a grammar rule.

5. Question: How do i show the total wage and salary of all employees?
Select SUM (e.sal), AVG (e.sal) from EMP e;
Search for the highest wage employee's name, job position
Select ename, Job, Sal from emp e where sal = (select Max (SAL) from EMP);
Show employee information for wages above average
SELECT * from emp e where sal > (select AVG (SAL) from EMP);

6. GROUP BY and HAVING clause
Group BY is used to group statistics on the results of a query,
The HAVING clause is used to restrict the display of grouping results.
Question: How do i show the average salary and maximum wage for each department?
Select AVG (SAL), Max (SAL), Deptno from EMP Group by DEPTNO;
(Note: There is a hidden point, if you want to group query, the Group field deptno must appear in the query list, otherwise it will error.) Because the grouped fields do not appear, there is no way to group them.
Question: Show the average wage and minimum wage for each position in each department?
Select min (sal), avg (SAL), Deptno, job from EMP Group by DEPTNO, job;
Question: Show the department number and its average salary below 2000 of the average salary?
Select AVG (SAL), Max (SAL), Deptno from EMP Group BY DEPTNO have avg (SAL) < 2000;

7, the summary of data grouping
1 The grouping function can only appear in the select list, having, ORDER BY clause (cannot appear in the where)
2 If the SELECT statement contains group BY, have, order by then their order is group by, have, order by
3 in the Select column, if there are columns, expressions, and grouping functions, then the columns and expressions must have one in the GROUP BY clause, or an error will occur.
such as Select Deptno, avg (SAL), Max (SAL) from the EMP GROUP by DEPTNO have avg (SAL) < 2000; Here Deptno must appear in GROUP by

8. Multi-Table Query
A multi-table query is a query based on two and more than two tables or views. In a real-world application, querying a single table might not meet your needs, such as displaying the sales department location and the names of its employees, in which case the Dept table and the EMP table would be used.
1), question: Show the employee's name, employee's salary and the department's name "Descartes set"?
SELECT E.ename, E.sal, d.dname from EMP E, dept d WHERE E.deptno = D.deptno;
Rule: Multi-table query condition is at least not less than the number of tables N-1 to exclude the Cartesian set (if there is n-table union query, you must have N-1 conditions to avoid the Cartesian collection)

2), question: Show department name, employee name and salary of department Number 10?
SELECT D.dname, E.ename, e.sal from EMP E, dept d WHERE E.deptno = D.deptno and E.deptno = 10;

3), question: Show the individual employee's name, salary and salary level?
SELECT E.ename, E.sal, s.grade from EMP E, Salgrade s WHERE e.sal between S.losal and S.hisal;

4), question: Show employee's name, employee's salary and name of department, and sort by department?
SELECT E.ename, E.sal, d.dname from EMP E, dept d WHERE E.deptno = D.deptno ORDER by E.deptno;
Note: If you use GROUP BY, be sure to put E.deptno in the query column

5), self-connected
A self-join refers to a connection query in the same table
Question: Display the name of the superior leader of an employee?
For example, show employees ' FORD ' superiors
SELECT worker.ename, boss.ename from EMP worker, EMP boss WHERE worker.mgr = boss.empno and worker.ename = ' FORD ';

6), sub-query
What is a subquery?
A subquery is a SELECT statement embedded in another SQL statement, also called a nested query.

Single-line subquery?
A single-line subquery is a subquery statement that returns only one row of data
Consider: Show all employees in the same department as Smith?
Ideas:
1)), query the department number of Smith (return single-row result set)
Select Deptno from emp WHERE ename = ' SMITH ';
2)), display
SELECT * from emp where deptno = (select Deptno from emp where ename = ' SMITH ');
The database is scanned from left to right in the execution of the SQL, and if there are parentheses, the parentheses are prioritized first.

Multi-row subqueries
Multiline subquery refers to subqueries that return multiple rows of data
Consider: How to query and department 10 work the same employee's name, position, salary, department number
1)), query out all work of department 10 (return multi-row result set)
SELECT DISTINCT job from emp WHERE deptno = 10;
2)), display
SELECT * from emp where job in (SELECT DISTINCT job from emp where deptno = 10);
Note: You cannot use job=., because the equals sign = is

Using the all operator in multiline subqueries
Question: How do i show the name, salary, and department number of the employee with a higher salary than all the employees in the Department 30?
--Method One
Select Ename, Sal, deptno from emp where sal > All (select Sal from emp where deptno = 30);
--Method two (highest execution efficiency, using aggregate functions)
Select Ename, Sal, deptno from emp where Sal > (SELECT max (SAL) from emp where deptno = 30);

Using the any operator in multiline subqueries
Question: How do i show the employee's name, salary, and department number with a salary higher than any employee in the Department 30?
--Method One
Select Ename, Sal, deptno from emp where Sal > No (SELECT sal from emp where deptno = 30);
--Method two (highest execution efficiency, using aggregate functions)
Select Ename, Sal, deptno from emp where sal > (SELECT min (sal) from emp where deptno = 30);

Dolez Query
A single-line subquery refers to a subquery that returns only single-column, single-row data, and a multiline subquery is a single row of multiple rows of data, all for a single column, whereas a multicolumn subquery is a subquery that returns multiple column data.

Consider how to query all employees who are exactly the same as Smith's department and position.
SELECT deptno, job from emp WHERE ename = ' SMITH ';
SELECT * from EMP where (deptno, job) = (select Deptno, Job from emp where ename = ' SMITH ');

Using subqueries in the FROM clause
Consider: How to display information about employees who are above their average salary
Ideas:
1. Find out the average wage and department number for each sector
SELECT Deptno, AVG (SAL) mysal from emp GROUP by Deptno;
2. Consider the results of the above query as a sub-table
SELECT E.ename, E.deptno, E.sal, ds.mysal
From EMP E, (SELECT deptno, AVG (SAL) mysal from emp GROUP by Deptno) DS
WHERE E.deptno = Ds.deptno and e.sal > ds.mysal;

Small summary:
Here's what you need to illustrate when using a subquery in the FROM clause, the subquery is treated as a view, so called inline view, and when a subquery is used in the FROM clause, the subquery must be given an alias.
Note: Aliases cannot be used as, for example: Select E.ename, E.deptno, E.sal, ds.mysal from emp E, (select Deptno, AVG (SAL) mysal from emp GROUP by Deptno As ds WHERE E.deptno = Ds.deptno and e.sal > ds.mysal;
Cannot add as before DS, otherwise it will error (when you alias the table, you cannot add as, but you can add an alias to the list)

How to measure a programmer's level?
Network processing power, database, program code optimization program efficiency is very high

7), create a new table with query results, this command is a quick way to build a table
CREATE TABLE mytable (ID, name, sal, Job, Deptno) as SELECT empno, ename, Sal, Job, deptno from EMP;

8), combined query
Sometimes in real life, in order to merge the results of multiple SELECT statements, you can use the set operation symbol Union,union All,intersect,minus.
It is used for the data warehouse with large data volume, which runs fast.
1). Union
This operator is used to obtain a set of two result sets. When the operator is used, duplicate rows in the result set are automatically removed.
SELECT ename, Sal, job from emp WHERE Sal >2500
UNION
SELECT ename, Sal, job from emp WHERE job = ' MANAGER ';

2). UNION ALL
This operator is similar to union, but it does not cancel duplicate rows and is not sorted.
SELECT ename, Sal, job from emp WHERE Sal >2500
UNION All
SELECT ename, Sal, job from emp WHERE job = ' MANAGER ';
This operator is used to obtain a set of two result sets. When this operator is used, duplicate rows in the result set are not automatically removed.

3). Intersect
Use this operator to get the intersection of two result sets.
SELECT ename, Sal, job from emp WHERE Sal >2500
INTERSECT
SELECT ename, Sal, job from emp WHERE job = ' MANAGER ';

4). Minus
Using this operator to get the difference set of two result sets, he only shows the existence of the first collection, not the data in the second collection.
SELECT ename, Sal, job from emp WHERE Sal >2500
Minus
SELECT ename, Sal, job from emp WHERE job = ' MANAGER ';
(minus is the meaning of subtraction)

Oracle Table Query Two