Oracle Complex queries

Data grouping,

grouping function max, min, avg, SUM, Count

1. Find the name of the person with the highest pay

Select ename from emp where sal = (select Max (SAL) from EMP);

1. 10% increase in salary for all employees below average wage

Update emp Set sal = (select AVT (SAL) from EMP) *10% where sal< (the Select AVT (SAL) from EMP);

Grout by and having a clause

Groupt by is used to group statistics on query results

The HAVING clause is used to restrict the display of grouped results

1. How to show average and maximum wages for each department

Select AVG (SAL), Max (SAL), deptno from emp Group by Deptno;

1. Show average and minimum wages for each position in each department

Select AVG (sal), Min (sal), deptno,job from EMP Group by Deptno,job;

1. Show department and number of average salary below 2000 and its average salary

Select Deptno,avg (SAL) from EMP Group by Deptno have avg (SAL) >2000;

Summarize:

1. The Grouping function (Max,min,avg,count) can only appear in the select list (after Select), in the having and order BY clauses
2. If both group by,having and order by are included in the SELECT statement, their order must be group by,having and ORDER by
   (First group → Re-suppress results display → last group)
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, otherwise they will be error-- such as Deptno

Multi-Table Query

1. Show employee name, employee wage, and the name of the department where the employee is located

Select A1.ename,a1.sal,a2.dname from emp a1,dept A2 where A1.deptno=a2.deptno;

Cartesian product, principle: The condition of a multi-table query is at least not less than the number of tables-1

1. How to display the department name, employee name, and salary for department Number 10

Select B.dname, a.ename,a.sal from emp a,dept b where B.deptno=a.deptno and a.deptno=10;

--Connect 2 sheets First, then filter them out with red conditions.

1. Displays the individual employee's name, salary, and level of salary

Select A1.ename,a1.sal,a2.grade from EMP A1, Salgrade A2 where a1.sal between A2.losal and A2.hisal;

1. (extended) Displays the employee's name, employee's salary, and the name of the department and sort by department

Select A1.ename,a1.sal,a2.dname from emp a1,dept A2 where A1.deptno = A2.deptno order by A1.deptno;

--Multi-table sorting

Self-connect

Self-connection refers to the connection query in the same sheet

1. Displays the name of an employee's superior leader. such as Ford

Select A.ename from emp a WHERE a.mgr = a.empno and a.ename = ' Ford ';

Select Worker.ename, boss.ename from EMP worker, EMP boss where Worker.mgr = boss.empno and worker.ename = ' FORD ';

--Tips: Think of a table as 2 different tables, alias, and then do it.

Sub-query

A subquery is a SELECT statement that is embedded in another SQL statement. Also called nested queries.

Single-line subquery

A single-row subquery is a query statement that returns only one row of data;

Example: How to display all employees in the same department as Smith

Select A.ename from emp a WHERE A.deptno = (select Deptno from emp b where b.ename= ' SMITH ');

Multi-line Word query

Multi-row subqueries Refer to query statements that return multiple rows of data

For example: How to Query and department 10 work the same employee name, position, salary and department number

SELECT * from EMP a where a.job in (select DISTINCT job from emp where Emp.deptno = 10);

Using the all operation in multi-row subqueries

Example: How to display the employee's name, salary, and department number with a salary higher than all employees in the Department 30

Method 1:

Select Ename,sal,deptno from emp where Sal >all (select Sal from EMP where deptno=30);

Method 2:

Select Ename,sal,deptno from emp where sal > (select Max (SAL) from EMP where deptno=30);

Comparison:

Method 2 is much more efficient than Method 1, because Method 1 compares one rule to another, and Method 2 directly compares the result.

Using the any operator in multiline subqueries

Example: The name, salary, and department number of an employee who displays a salary higher than the salary of any employee in the Department 30

Method 1:

Select A.ename,a.sal,a.deptno from emp a where a.sal > No (select Sal from EMP where deptno=30);

Method 2:

Select Ename,sal,deptno from emp where sal > (select min (sal) from EMP where deptno=30);

Dolez Query

A single-row subquery is a subquery that returns single-row, single-column data;

A multi-row subquery is a subquery that returns multiple rows of data in a single row. Are for single-column purposes;

A multi-column subquery refers to a subquery that returns multiple columns in a subquery.

For example: Query all employees who are exactly the same as the Smith Department and the job.

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

Using subqueries in the FROM clause

Example: Show staff information above the average salary of their department

1) Check the average salary and department number of each department

Select Deptno, Avg (SAL) from the EMP group by DEPTNO;

2) Check your department's

Consider the above query as a sub-table, (select Deptno, avg (SAL) from the EMP Group by DEPTNO) A1

Select A2.ename,a2.sal,a2.deptno,a1.avgsal from EMP A2, (select Deptno, avg (SAL) avgsal from EMP Group by DEPTNO) A1 where A2.deptno=a1.deptno and A2.sal > a1.avgsal;

Summary: When a subquery is used in the FROM clause, the subquery is treated as a view, so it is called an inline view, and when a subquery is used in the FROM clause, the subquery must be given an alias.

--You can use as for a column alias, but aliases for tables, views, and subqueries cannot be used as

Pagination

Oracle's paging is the most complex, with 2 sub-queries, but the most efficient, because the 2-point lookup principle is used internally. MySQL paging is the simplest, the direct one limit is implemented; SQL Server is the second.

Oracle's paging is in total 3 ways

1. RowNum make a subquery first

SELECT * FROM emp

1. Show Rownum[rownum is assigned by Oracle]

Select A1.*,rownum rn from (SELECT * from EMP) A1;

Query results will be one more column, RN, indicating rownum, line number, is assigned by Oracle.

1. Display the row data you want to select

Select A1.*,rownum rn from (SELECT * from EMP) A1 where rownum<=10;

and rownum>=6; (RowNum cannot be used 2 times, otherwise the data will not be found)

Here, you can display 1-10 rows of data, and the target is to display 6-10 rows of data, which has been cut off by more than half.

1. Do another subquery

SELECT * Form (select A1.*,rownum rn from (SELECT * from EMP) A1 where rownum<=10) A2 where rn>=6;

The result is the 6-10 rows of data you want.

Note that several query changes:

1. If you want to specify a query column instead of querying all columns, just modify the innermost subquery.
2. Sorting also only needs to modify the innermost subquery.
3. Similarly, group Ah, then sort ah, all the changes in the innermost sub-query can be.

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;

After the command executes, mytable not only the table structure and EMP, even the data are all poured from the EMP table.

Merging queries

Sometimes in real-world applications, to merge the results of multiple SELECT statements, you can use the set Operation Symbol Union, union all, intersect, and minus operators

1. Union two take or action, remove duplicate rows

This operator is used to obtain a set of 2 result sets. When this 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 ';

1. Union all and do not remove duplicate rows

This operator is similar to union, but does not cancel duplicate rows and does not sort

1. Intersect merging queries

The operator takes the intersection of 2 sets

1. Minus differential set operation

This operator takes a difference set of 2 sets

Ways to create a database

1. Through the wizard tools provided by Oracle

Database Configration Assistant (DBCA) Oracle DB Configuration Assistant

1. Manual code creation directly

Java Operations oralce,jsp and paging

. NET Operations Oracle, paging

Inserting a date value using a specific format

Use the To_date () function to insert a date in the format of year-month-day

To_date (' 1989-05-11 ', ' yyyy-mm-dd ')

Date values can be formatted

Inserting data using subqueries

When you use the VALUES clause, you can insert only one row of data at a time, and an INSERT statement inserts a large amount of data when you insert data using a subquery. Subqueries can be used to insert data when processing row migrations, data migration, or loading data from external tables to the database.

Insert into myTable (ID,NAME,DEPTNO) Select (Myid,ename,eno) from EMP where eno=10;

Updating data using clauses

When updating data using the UPDATE clause, you can modify the data directly using an expression or numeric value, or you can modify the data using a subquery

For example: Want to update employee Scott's position, salary, and allowances as well as employee simth

Update emp Set (JOB,SAL,COMM) = (select Job,sal,comm from emp where Enma = ' Smith ') where Ename= ' Scott ';

Oracle Complex queries