Oracle 3-Multi-Table query

1. Cartesian sets and cross sets are generated under the following conditions: omitted join conditions, invalid join conditions, and all rows in all tables are connected to each other. To avoid Cartesian sets, a valid join condition can be added to the WHERE clause. In the actual operating environment, full Cartesian sets should be avoided. Use the CROSSJOIN clause to generate a cross set for the joined table. Cross set

1. Cartesian sets and cross sets are generated under the following conditions: omitted join conditions, invalid join conditions, and all rows in all tables are connected to each other. To avoid Cartesian sets, a valid join condition can be added to the WHERE clause. In the actual operating environment, full Cartesian sets should be avoided. Use the cross join clause to generate a CROSS set for the joined table. Cross set

1. Cartesian set and cross set

Descartes are generated under the following conditions:Ignore the connection condition, the connection condition is invalid, and all rows in all tables are connected to each other.

To avoid Cartesian sets, a valid join condition can be added to the WHERE clause. In the actual operating environment, full Cartesian sets should be avoided.

Use the cross join clause to generate a CROSS set for the joined table. The cross set and Cartesian set are the same.

2. Oracle connection type:

Equijoin: equivalent join

Non-equijoin: Non-equivalent join

Outer join: Outer join

Self join: Self join

Use the table name prefix to differentiate identical columns in multiple tables. Columns with the same column names in different tables can be distinguished by table aliases.

Alias can be used to simplify query. You can use the table name prefix to improve execution efficiency. If the table alias is used, the real name of the table cannot be used.

To connect n tables, at least n-1 join conditions are required. For example, connecting three tables requires at least two join conditions.

3. Internal Connection and external connection

Internal Connection:Merge rows of more than two tables with the same column. The result set does not contain rows that do not match the other table.

External Connection:In addition to returning rows that meet the connection conditions, the two tables also return rows that do not meet the conditions in the left (or right) table.Left (or right) Outer Join. If no matching row exists, the corresponding column in The result table is NULL ). the WHERE clause condition for outer join is similar to the internal link, but the column of the table that does not match the row in the join condition must be followed by the outer join operator, that is, the plus sign (+) enclosed in parentheses ).

In SQL: only data that meets the connection conditions is returned for inner join. In addition to the rows that meet the connection conditions, the two tables return the left (or right) rows that do not meet the conditions in the table. This join is called a left (right) Outer Join.

In addition to returning rows that meet the connection conditions, the two tables return rows that do not meet the conditions during the connection process. This connection is called a full outer join.

Natural connection:The natural join clause creates an equijoin based on the conditions of the columns with the same name in the two tables.

Query the data that meets the equivalence conditions in the table.

If the column names are the same and the data types are different, an error is returned.

Use the USING clause to create a connection

When the natural join clause creates an equijoin, you can use the USING clause to specify the columns to be used in the equijoin. You can use USING to select when multiple columns meet the conditions. Do not add the prefix or alias of the table name to the selected column. Natural join and USING clauses are often used at the same time.

Use the ON clause to create a connection

In a natural connection, a join condition is a column with the same name. You can use the ON clause to specify additional join conditions. This connection condition is separate from other conditions. The ON clause makes the statement more readable.

4. Grouping Functions

The grouping function is: Acts on a group of data, and returns a value for a group of data.

Group function type (the group function ignores NULL values, and the NVL function prevents the group function from ignoring null values .)

AVG, COUNT, MAX, MIN, STDDEV (standard variance), SUM, COUNT (expr) return the total number of records with expr not null, COUNT (DISTINCT expr) returns the total number of records that are not empty and repeat.

You can use the group by clause to divide the data in the table into several groups. All columns not included in the GROUP function in the SELECT list should be included in the group by clause.

Columns included in the group by clause do not need to be included in the SELECT list

Invalid Group Function:

Columns used in the select list and not included in GROUP functions must be included in the group by clause. You cannot use group functions in the WHERE clause (note ).

You can use group functions in the HAVING clause.

Filter Group: HAVING clause; use HAVING to filter groups:

1). the row has been grouped.

2) group functions are used.

3). Groups that meet the conditions in the HAVING clause will be displayed.

Example:

SQL> select count(deptno)from emp; COUNT(DEPTNO)                                                                   -------------                                                                             14                                                                   SQL> select count(distinct deptno)from emp; COUNT(DISTINCTDEPTNO)                                                          ---------------------                                                                              3                                                            SQL> select avg(nvl(comm,0)) from emp; AVG(NVL(COMM,0))                                                               ----------------                                                                     157.142857                                                                 SQL> select avg(comm) from emp;  AVG(COMM)                                                                     ----------                                                                            550             SQL> select deptno,job,sum(sal) from emp group by deptno,job order by deptno asc,sum(sal) asc;     DEPTNO JOB         SUM(SAL)                                                ---------- --------- ----------                                                        10 CLERK           1300                                                        10 MANAGER         2450                                                        10 PRESIDENT       5000                                                        20 CLERK           1900                                                        20 MANAGER         2975                                                        20 ANALYST         6000                                                        30 CLERK            950                                                        30 MANAGER         2850                                                        30 SALESMAN        5600                                                 

5. subquery

Subquery (internal query) is executed once before the primary query. The subquery results are used by the primary query (external query ). The subquery must be included in brackets. Place the subquery on the right of the comparison condition.

Single-row operators correspond to single-row subqueries, and multi-row operators correspond to multiple-row subqueries.

First, execute the subquery. Returns the result to the HAVING clause in the primary query.

Invalid subquery:Multi-row subqueries use single-row comparison characters. null values in subqueries: No rows are returned in subqueries.

6. Case Analysis (the user operates under hr/hr)

Query the names and dates of employment of employees in the same department as Zlotkey.

Select initcap (concat (last_name, first_name) as "name", hire_date from employees where department_id = (Select department_id from employees where last_name = 'zlotkey ');

Query the employee numbers, names, and salaries of employees with higher salaries than the company average.

Select employee_id, initcap (concat (last_name, first_name) as "name", salary from employees where salary> (select avg (salary) from employees );

Query the employee numbers, names, and salaries of employees with higher salaries than the average salaries of each department.

Select employee_id, initcap (concat (last_name, first_name) as "name", salary from (select department_id, avg (salary) avgsalary from employees group by department_id) s join employees e on e. department_id = s. department_id and e. salary> s. avgsalary;

Query the employee ID and name of an employee whose name contains the letter u in the same department

Select e. employee_id, initcap (concat (last_name, first_name) as "name" from (select employee_id, initcap (concat (last_name, first_name )) as "name" from employees where initcap (concat (last_name, first_name) like '% u %') s join employees e on e. employee_id = s. employee_id;

Query the employee ID of a department whose location_id is 1700

Statement 1:

Select employee_id from employees e, departments d, locations l where e. department_id = d. department_id and d. location_id = l. location_id and l. location_id = 1700;

Statement 2:

Select employee_id from employees e join orders ments d on e. department_id = d. department_id

Join locations l on d. location_id = l. location_id and l. location_id = 1700;

Query the name and salary of an employee whose manager is King

Select initcap (concat (e. last_name, e. first_name) as "name", e. salary, e. manager_id from employees e, employees m where e. manager_id = m. manager_id and m. last_name = 'King ';

7. Switch to the scott table for exercises:

Find the top three employees with the highest salary in the employee table in the following format:

Select rownum, d. * from (select empno, ename, sal from emp order by sal desc) d where rownum <= 3;

Count the number of employees each year. The effect is as follows:

Method 1:

Select

(Select count (*) from emp) total,

(Select count (*) from emp where to_char (hiredate, 'yyyy') = '000000') "1980 ",

(Select count (*) from emp where to_char (hiredate, 'yyyy') = '000000') "1981 ",

(Select count (*) from emp where to_char (hiredate, 'yyyy') = '000000') "1982 ",

(Select count (*) from emp where to_char (hiredate, 'yyyy') = '000000') "1987 ",

(Select count (*) from emp where to_char (hiredate, 'yyyy') = '000000') "1990"

From emp where rownum = 1;

Method 2:

Select count (*) total,

Sum (Decode (to_char (hiredate, 'yyyy'), '000000', 1, 0) "1980 ",

Sum (Decode (to_char (hiredate, 'yyyy'), '000000', 1, 0) "1981 ",

Sum (Decode (to_char (hiredate, 'yyyy'), '000000', 1, 0) "1982 ",

Sum (Decode (to_char (hiredate, 'yyyy'), '000000', 1, 0) "1987 ",

Sum (Decode (to_char (hiredate, 'yyyy'), '000000', 1, 0) "1990"

From emp;