Shulou(Shulou.com)06/01 Report--

Oracle Series II: Multi-table Complex Query and Transaction Processing

Multi-table queries should pay attention to removing Cartesian products. Generally, when multiple tables are used, an alias will be given to the table.

[1] It is required to query the employee's number, name, department number, department name and department location.

SQL> SELECT e.empno,e.ename,d.deptno,d.dname,d.loc FROM emp e,dept d

WHERE e.deptno = d.deptno;

[2] It is required to query the name, work and name of the employee's direct superior leader of each employee (table self-association)

SQL> SELECT e.ename,e.job,m.ename FROM emp e,emp m

WHERE e.mgr = m.empno;

[3] Expand [2] to list the department name of the employee at the same time

SQL> SELECT e.ename,e.job,m.ename,d.dname FROM emp e,emp m,dept d

WHERE e.mgr = m.empno AND e.deptno=d.deptno;

[4] Query the name, salary, department name, salary grade in the company of each employee, and the grade of the company where the name of the leader belongs.

First determine the contents of the wage scale

SQL> SELECT * FROM salgrade;

Query each employee's name, salary, department name, and salary rank in the company

SQL> SELECT e.ename,e.sal,d.dname,s.grade FROM emp e,dept d,salgrade s

WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal;

Check the name of the leader and the rank of the company where the salary is located

SQL> SELECT

e.ename,e.sal,d.dname,s.grade,m.ename,m.sal,ms.grade FROM emp e,dept d,salgrade s,emp m,salgrade ms

WHERE e.deptno = d.deptno

AND e.sal BETWEEN s.losal AND s.hisal

AND e.mgr = m.empno

AND m.sal BETWEEN ms.losal AND ms.hisal;

[5] The concept of left connection and right connection,"+" on the left side of the equal sign indicates right connection, otherwise, left connection

Inquire the employee's number and name and the number and name of his leader

SQL> SELECT e.empno,e.ename,m.empno,m.ename FROM emp e,emp m

WHERE e.mgr = m.empno(+); find that the record of KING is also connected

SQL1999 syntax has the following connections (understand)

Cross Joins produce Cartesian products

SQL> SELECT * FROM emp CROSS JOIN dept;

2. NATURAL JOIN, automatically matching associated fields

SQL> SELECT * FROM emp NATURAL JOIN dept;

3. Use USING clause to directly associate operation columns

SQL> SELECT * FROM emp JOIN dept USING(deptno) WHERE deptno=30;

4. Use the ON clause, the condition written by the user himself

SQL> SELECT * FORM emp JOIN dept ON(emp.deptno = dept.deptno) WHERE deptno=30;

5. Left connection (LEFT (OUTER) JOIN), right connection (RIGHT (OUTER) JOIN)

Group Function and Group Statistics

COUNT(): Find the total number of records

MAX(): Find the maximum value in a group

MIN(): Find the minimum value

AVG(): Find the average

5. SUM(): Sum

Find the number of employees in each department

SQL> SELECT deptno,count(empno) FROM emp

GROUP BY deptno;

[2] Group by department, and display the name of the department and the number of employees in each department

SQL> SELECT d.dname,COUNT(e.empno) FROM emp e,dept d

WHERE e.deptno=d.deptno GROUP BY d.dname;

[3] It is required to display the department number and average salary with average salary greater than 2000.

SQL> SELECT deptno,AVG(sal) FROM emp

WHERE AVG(sal)>2000 GROUP BY deptno;

A grouping function condition cannot appear in the WHERE clause. To use the HAVING clause, the above statement should be changed to the following

SQL> SELECT deptno,AVG(sal) FROM emp

GROUP BY deptno

HAVING AVG(sal)>2000

[4] Displays the job name of a non-salesperson and the sum of the monthly salaries of employees engaged in the same job.

and requires employees doing the same job to make a total monthly salary greater than $5000, the output is sorted in ascending order of total monthly salary

Find the sum of the monthly salaries of non-salespeople by work group

SQL> SELECT job,SUM(sal) FROM emp

WHERE job'SALESMAN' GROUP BY job;

restrict grouping conditions and then sort,HAVING clause cannot use aliases

SQL> SELECT job,SUM(sal) totalSal FROM emp

WHERE job'SALESMAN' GROUP BY job

HAVING SUM(sal) > 5000 ORDER BY totalSal;

[5] Grouping functions can be nested, but the column name of the grouping condition cannot appear in the SELECT column.

SQL> SELECT deptno,MAX(AVG(sal)) FROM emp

GROUP BY deptno;

Error! modified as follows

SQL> SELECT MAX(AVG(sal)) FROM emp

GROUP BY deptno;

[6] Find information about all employees whose wages are higher than 7654

The first thing to check is the salary of employee No. 7654

SQL> SELECT sal FROM emp WHERE empno=7654;

Based on the results of the above conditions, the basis for the final subsequent query

SQL> SELECT * FROM emp

WHERE sal>(SELECT sal FROM emp WHERE empno=7654);

Subqueries fall into three categories in operations:

1. Single-column subquery: The returned result is the content of a column

2. Single-row subquery: returns multiple columns, or may be a record

3. Multi-row subquery: returns multiple records

All employees whose wages are higher than 7654 and who are doing the same job as 7788 are required to inquire.

SQL> SELECT * FROM emp

WHERE sal>(SELECT sal FROM emp WHERE empno=7654)

AND job=(SELECT job FROM emp WHERE empno=7788);

[2] It is required to query the name of the department, the number of employees in the department, the average salary of the department, and the name of the lowest paid employee in the department.

Query the number of department employees and the average salary of the department

SQL> SELECT deptno,COUNT(empno),AVG(sal) FROM emp

GROUP BY deptno;

Query the name of the department, and the name of the lowest paid employee, to perform table association (subquery)

SQL> SELECT d.dname,ed.c,ed.a,e.ename FROM dept d,(

SELECT deptno,COUNT(empno) c,AVG(sal) a,MIN(sal) min FROM emp

GROUP BY deptno) ed, emp e

WHERE d.deptno=ed.deptno AND e.sal = ed.min;

If there are two minimum wages above, an error will occur. There are three query operators in the subquery.

IN: Specify a query range, e.g. minimum wage for each department (multiple return values)

SQL> SELECT * FROM emp

WHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY deptno);

ANY: =ANY(same as IN operation),>ANY(greater than minimum), SELECT * FROM emp

WHERE sal ALL(greater than max), select * from emp where job in (select job from emp where deptno=10);

Database update operations INSERT, UPDATE, Delete

[1] Copy a table, for example, copy EMP table as MYEMP

SQL> CREATE TABLE MYTEMP AS SELECT * FROM emp;

[2] Cancel the leadership of employee No. 7899

SQL> UPDATE myemp SET mgr=null WHERE empno=7899;

[3] When updating, be sure to pay attention to not batch updating (plus WHERE clause). Examples of multi-column updating are as follows

SQL> UPDATE myemp SET mgr=null,comm=null WHERE empno IN(7369,8899);

Delete all employees who receive bonuses

SQL> DELECT FROM emp WHERE comm is NOT NULL;

Transaction ACID

Atomicity: Operations in A transaction are either completed or cancelled.

C: Consistency: Operations in a transaction ensure that data in the database does not appear logically inconsistent

Isolation: The current transaction is isolated from other outstanding transactions.

D: Durability: After COMMIT, the data is permanently stored in the database, and until then, the transaction operations can be rolled back.

Verify transaction process:

Create a temporary table containing only department 10

SQL> CREATE TABLE emp10 AS SELECT * FROM emp WHERE empno=10;

Delete 7782 employees in EMP10

SQL> DELETE FROM emp10 WHERE empno=7782;

Open another window and find that the data still exists. At this time, if you can use the following two commands to perform transaction processing,

COMMIT and ROLLBACK commit transactions and rollback transactions

SQL Query Exercises

List all departments with at least one employee

SQL> SELECT d.*, ed.cou FROM dept d,(

SELECT deptno,COUNT(empno) cou FROM emp GROUP BY deptno

HAVING COUNT(empno) > 1

) ed

WHERE d.deptno=ed.deptno;

[2] List the department names and employee information of these departments, and list those departments without employees.

SQL> SELECT d.deptno,d.dname,e.empno,e.ename

FROM dept d,emp e WHERE d.deptno = e.deptno(+);

[3] List the names of all "CLERK"(clerks) and their department names, and the number of people in the department

associated dept table

SQL> SELECT e.ename,d.dname FROM emp e,dept d

WHERE e.deptno=d.deptno and e.job='CLERK';

Use GROUP BY to complete department grouping

SQL> SELECT e.ename,d.dname,ed.cou FROM emp e,dept d,

( SELECT deptno,COUNT(empno) cou FROM emp GROUP BY deptno) ed

WHERE job='CLERK' AND e.deptno=d.deptno AND ed.deptno=e.deptno;

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.