Oracle Study Notes 2

1. Multi-Table query:

Select * from Scott. Dept;
Select * from Scott. salgrade;

Select * from Scott. EMP, Scott. Dept;
Each record in the dept table is matched with each record in the EMP table;

Cartesian Product: If a multi-Table query does not contain any conditions, a Cartesian product is generated. How can we avoid this problem:
The multi-Table query condition must be at least one less than the number of tables;

Example:
1. display the name, salary, and salary level of each employee;
2. display the employee name, salary, and department name, and sort by department.

3. display the name of each employee and the name of his/her supervisor (self-join: regard the EMP table as two tables, one is the employee table worker and the other is the boss table)

Self-connection:
Select worker. ename, boss. ename from Scott. EMP worker, Scott. EMP boss where worker. Mgr = boss. empno

Left Outer Join
Select worker. ename, boss. ename from Scott. EMP worker, Scott. EMP boss where worker. Mgr = boss. empno (+ );
Outer right connection
Select worker. ename, boss. ename from Scott. EMP worker, Scott. EMP boss where worker. Mgr (+) = boss. empno
2

When to add a table name to a multi-Table query: if the two tables have the same column name, you need to add a table name to distinguish between them,
Otherwise, the table name is recommended.
Note: We recommend that you use aliases when performing multi-table queries.

3
Select distinct job from EMP where deptno = 10;
Use of the All OPERATOR:
Select * from EMP where SAL> All (select Sal from EMP where deptno = 30 );
Any operator:
Select * from EMP where SAL> Any (select Sal from EMP where deptno = 30 );

4. Multi-column subquery:
Query all employees who have the same department and position as Smith:

Select * from EMP where (deptno, job) = (select deptno, job from
EMP where ename = 'Smith ');

5. Use subqueries in the from clause (use results as temporary tables) (important knowledge points)

How do I display employee information higher than the average salary of my department?
Ideas:
1. query the average salary of each department
Select AVG (SAL), deptno from Scott. EMP group by deptno;
2. Treat the query result as a temporary table.
Select t1.deptno, t1.ename, t1.sal, t2.myavg from Scott. EMP T1, (select AVG (SAL) myavg, deptno from Scott. EMP group by deptno) T2 where t1.deptno = t2.deptno and t1.sal> t2.myavg;

What are the details of the persons with the highest salaries in each department?
Ideas:
1. Select max (SAL), deptno from Scott. EMP group by deptno;
2. select t1.ename, t1.sal, t1.deptno, t2.maxsal from Scott. EMP T1, (select max (SAL) maxsal, deptno from Scott. EMP group by deptno) T2 where t1.deptno = t2.deptno and t1.sal = t2.maxsal;

What is the information (number, name) and number of employees of each department displayed?
Select count (*) pnum, deptno from Scott. EMP group by deptno;

Select D. deptno, D. dname, P. pnum from Scott. dept D, (select count (*) pnum, deptno from Scott. EMP group by deptno) P where D. deptno = P. deptno;

6. Paging query:
MySQL:
Select * from table name where condition limit takes the number of entries and takes the number of entries;
SQL Server:
Select top 10 * from table name where id not in (select top 10 ID from table name where condition)
Exclude the first 10, and then fetch 10 more. Actually, extract 11-20;
Oralce:
Select T2. * from (select T1. *, rownum rn from (select * from Scott. EMP) T1 where rownum <= 6) T2 where rn> = 4;

Test efficiency:
Simulate a table with 40 million data records
Create Table mytest as select empno, ename, Sal, comm, deptno from Scott. EMP;
Self-replication
Insert into mytest (empno, ename, Sal, comm, deptno) Select empno, ename, Sal, comm, deptno from mytest;
Paging test:
Select T2. * from (select T1. *, rownum rn from (select * From mytest) T1 where rownum <= 6) T2 where rn> = 4;
View tables owned by Scott
Select table_name from dba_tables where owner = 'Scott ';

7. Merge Query
1. Union gets the union of two result sets. When this operator is used, duplicate rows in the result set are automatically removed.
2. Union all is similar to union, but duplicate rows are not canceled and not sorted.
3. Intersect obtains the intersection
4. Minus obtains the difference set and displays the data in the first set. The data in the second set is not displayed.

Cube function:
Select AVG (SAL), deptno, job from Scott. EMP group by cube (deptno, job );

Internal Connection and external connection
1. Inner join Select column name from table 1 inner join table 2 on condition
Feature: only two tables match at the same time can be selected and displayed.
2. There are three types of external connections: left outer connection, right outer connection, and full outer connection.
Create Table Stu (ID number, name varchar2 (33 );
Insert into Stu values (1, 'jack ');
Insert into Stu values (2, 'Tom ');
Insert into Stu values (3, 'k ');
Insert into Stu values (4, 'nono ');

Create Table exam (ID number, grade number );
Insert into exam values (1, 58 );
Insert into exam values (2,78 );
Insert into exam values (11,87 );

1 -- left Outer Join: The left table is completely displayed, and the right side is matched by conditions.
Select Stu. Name, Stu. ID, exam. grade from Stu left join exam on Stu. ID = exam. ID;
Or:
Select Stu. Name, Stu. ID, exam. grade from Stu, exam where Stu. ID = exam. ID (+ );
2 -- right outer join: The right table is completely displayed, and the left table is matched by conditions.
Select Stu. Name, Stu. ID, exam. grade from Stu right join exam on Stu. ID = exam. ID;
Or:
Select Stu. Name, Stu. ID, exam. grade from Stu, exam where Stu. ID (+) = exam. ID;
3 -- full connection: the left and right sides are completely displayed.

 

 

9 series:
Sequence creation:
Create sequence myseq -- myseq indicates the sequence name
Start with 1
Increment by 1
Minvalue 1
Max value 30000
Cycle -- if you do not want to use nocycle
Nocache -- Cache 10: indicates that you use the cache to generate a total of 10 numbers at a time. Using the cache to generate a number increases efficiency and may generate a skip sign.

Sequence usage:
Create Table test1 (ID numbe primary key, name varchar2 (33 ));
Insert into test1 values (myseq. nextval, 'abc'); -- nextval is the keyword

System users can use the sequence of Scott users. It continues to grow from where it was last used

View the current serial number:
To create a sequence, use
Select sequence name. nextval from dual; -- used again. after each use, the sequence value increases with the corresponding step size.
Select sequence name. currval from dual;

Delete sequence: drop sequence myseq;