1, Multi table query

multi-table query
  -- merge union
  -- intersection intersect
  -- subtraction minus

The INTERSECT operator returns only the common rows of the two queries
For example:

SELECT orderno FROM order_master
INTERSECT
SELECT orderno FROM order_detail;

The MINUS operator returns rows that appear in the second query excluded from the first query result
For example:

SELECT orderno FROM order_master
MINUS
SELECT orderno FROM order_detail;

2, Association query

Association query
  -- internal and external connections
  -- inner join
  -- right / left / full outer join

Inner connection

select x.emp, x.ena, y.dep, y.dna
from em x
join de y
on x.dep = y.dep;

from is directly followed by two table names. The system will perform Cartesian product operation on the two tables and generate a Cartesian product intermediate table (this table is equivalent to the multiplication operation of two sets, referring to the multiplication of two sets). If both tables are large, the intermediate table will be large and occupy a lot of memory space

The inner link can only query qualified results. The left outer link can fully display the data of one or two tables

External connection
left join and right join can also be replaced by (+)
(+) should be placed on the side where the corresponding information is missing

The main table connected on the left is join, and the table on the left is the main table, and all contents will be displayed

select x.emp, x.ena, y.dep, y.dna from em x left join de y on x.dep = y.dep; 

Plus sign of left outer link

select x.emp, x.ena, y.dep, y.dna from em x, de y where x.dep = y.dep(+);

The main table connected on the right is the join. The table on the right is the main table, and all contents will be displayed

select x.emp, x.ena, y.dep, y.dna from em x right join de y on x.dep = y.dep ;

Plus sign for right outer link

select x.emp, x.ena, y.dep, y.dna from em x, de y where x.dep(+) = y.dep

3, Case statement

CASE usage 1

CASE expression/field/variable...
WHEN Value 1 THEN Result 1
WHEN Value 2 THEN Result 2
...
WHEN value N THEN result N
ELSE Default result
END

CASE usage 2

CASE
WHEN Conditional expression 1 THEN Result 1
WHEN Conditional expression 2 THEN Result 2
...
WHEN Conditional expression N THEN result N
ELSE Default result
END

4, Alias

Use column aliases
  -- provide different names for column expressions
  -- the alias specifies the column header

For example:

SELECT c1,c2,c3*2 AS x FROM t;

SELECT c1,c2,c3*2 "x" FROM t;

5, Uniqueness

Unique data distinct / unique

Pseudo column rowid

For example:
Select a row that has no duplicates and use the DISTINCT keyword

SELECT DISTINCT c1 FROM t;

Add a unique identifier to each row: rowid

SELECT rowid,c1 FROM t;

6, Line number

oracle does not have the limit function in MySQL and the top keyword in SQL server, so it can only meet the paging function through pseudo columns

Rownum pseudo column is a pseudo column added after Oracle first queries and obtains the result set. This pseudo column adds a serial number starting from 1 to the qualified results. Rownum is dynamic, that is, there must be a queried result set first, and then add a column to the result set. For example, the value of rownum of the first data in the result set is 1

select rownum as "Line number",tname from tab;

7, Ranking

Continuous ranking number is given after sorting by field
row_number() over(order by field desc/asc)

For example:

select ename,job,deptno,sal,
row_number( ) over(order by sal desc) as "ranking"
from scott.emp;

Sort by field, and give parallel ranking numbers for duplicate data. The ranking is discontinuous with skip numbers
rank() over(order by field desc/asc)
For example:

select ename,deptno,sal,
rank( ) over(order by sal desc) as "ranking"
from scott.emp;

Sort by field, and give parallel ranking numbers for duplicate data, ranking continuously
dense_rank() over(order by field desc/asc)
For example:

select ename,deptno,sal,
dense_rank( ) over(order by sal desc) as "ranking"
from scott.emp;

8, Sequence

Sequence is a database object provided by oracle to generate a series of unique numbers. Since there is no method of auto incrementing columns in oracle, we mainly use sequences to realize the function of auto incrementing primary keys in oracle database

Create sequence:

CREATE SEQUENCE sequence //Create sequence name
[INCREMENT BY n] //The incremental sequence value is n. if n is a positive number, it will increase. If n is a negative number, it will decrease. The default value is 1
[START WITH n] //The starting value is increased by minvalue and decreased by maxvalue by default
[{MAXVALUE n | NOMAXVALUE}] //Maximum  
[{MINVALUE n | NOMINVALUE}] //minimum value
[{CYCLE | NOCYCLE}] //Cycle / no cycle
[{CACHE n | NOCACHE}];//Allocate and store in memory

Use sequence:

//The sequence call generates a new sequence
select seq_test.nextval from dual
//View the value of the current sequence
select seq_test.currval from dual

Thank everyone who reads my article carefully!!! If the following materials are available, they can be taken away directly:

1. Complete project source code and environment necessary for self-study development or testing

2. All templates in test work (test plan, test case, test report, etc.)

3. Classic interview questions for software testing

4. Python/Java automated test practice pdf

5. Jmeter/postman interface test full set of video acquisition

I have personally sorted out some technical materials of my software testing career in recent years, including e-books, resume modules, various work templates, interview scriptures, self-study projects, etc. If you encounter problems in your study or work, you can directly Click this link Enter the group to ask, and there will be great gods in the group to help answer, or you can manually add the group number 743262921 Remarks 222