How to use Oracle SQL SELECT statements

Select Format:
SELECT [All | DISTINCT] < field expression 1[,< field expression 2[,...]
From < table name 1>,< table name 2>[, ...]
[WHERE < screen selection condition expression]
[GROUP BY < group expression > [having< Group conditional expression]]
[Order by < Field >[ASC | DESC]]

Statement Description:
[] square brackets are optional
[GROUP BY < group expression > [having< Group conditional expression]]
The result is grouped by the value of the < group expression >, which is equal to a set of records, with the "having"
A phrase is output only if the group satisfies the specified criteria.
[Order by < Field >[ASC | DESC]]
Display results to sort by < field > value in ascending or descending order

Practice:
1: Table Hkb_test_sore take out the records of the top 5 of the score sore,
2: Take the 5th place record

1, answer select a.sore_id, A.sore
From (SELECT * to Hkb_test_sore ORDER BY sore Desc) a
where RowNum <=5

2, answer select a.sore_id, A.sore
  from (SELECT * to Hkb_test_sore ORDER BY sore desc) a
 where rownum <=5
 minus
Select a.sore_id, A.sore
  from (SELECT * to Hkb_test_sore ORDER BY sore desc) a
 where rownum <=4;
3: Query two fractions of records
SELECT *
  from Hkb_test_sore a
 where a.sore = (select sore
  &nbs p;                from Hkb_test_sore A
                  GROUP BY A.sore
                  Having count (a.sore) = 2);

The difference between union,union All,intersect,minus:
Sql> select * from Hkb_test2;
X Y
---- -----
A 1
B 2
C 3
G 4

Sql&gt; select * from Hkb_test3;


X Y


---- -----


A 1


B 2


E 3


F 4





Sql&gt; select * from Hkb_test2;


X Y


---- -----


A 1


B 2


C 3


G 4





Sql&gt; select * from Hkb_test3;


X Y


---- -----


A 1


B 2


E 3


F 4





Sql&gt; SELECT * from Hkb_test2


2 Union


3 SELECT * from Hkb_test3;


X Y


---- -----


A 1


B 2


C 3


E 3


F 4


G 4





6 Rows selected





Sql&gt; SELECT * from Hkb_test2


2 UNION ALL


3 SELECT * from Hkb_test3;


X Y


---- -----


A 1


B 2


C 3


G 4


A 1


B 2


E 3


F 4





8 Rows selected

Sql> SELECT * from Hkb_test2
2 intersect
3 SELECT * from Hkb_test3;
X Y
---- -----
A 1
B 2

Sql> SELECT * from Hkb_test2
2 minus
3 SELECT * from Hkb_test3;
X Y
---- -----
C 3
G 4

Look at a complete example from the above example

Sql&gt;


Sql&gt;


Sql&gt;--Create demo table


Sql&gt; CREATE TABLE Employee (


2 ID VARCHAR2 (4 BYTE) not NULL primary key,


3 first_name VARCHAR2 (BYTE),


4 last_name VARCHAR2 (BYTE),


5 start_date Date,


6 end_date Date,


7 Salary Number (8,2),


8 City VARCHAR2 (BYTE),


9 Description VARCHAR2 (MB)


10)


11/

Table created.

Sql>
Sql>--Prepare data
sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (' ', ' Jason ', ' Martin ', to_date (' 19960725 ', ' YYYYMMDD '), to_date (' 20060725 ', ' YYYYMMDD '), 1234.5 6, ' Toronto ', ' Programmer ')
3/

1 row created.

sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (' ', ' Alison ', ' Mathews ', To_date (' 19760321 ', ' YYYYMMDD '), to_date (' 19860221 ', ' YYYYMMDD '), 6661.7 8, ' Vancouver ', ' Tester ')
3/

1 row created.

sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (', ' James ', ' Smith ', to_date (' 19781212 ', ' YYYYMMDD '), to_date (' 19900315 ', ' YYYYMMDD '), 6544.7 8, ' Vancouver ', ' Tester ')
3/

1 row created.

sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (' ', ' Celia ', ' Rice ', to_date (' 19821024 ', ' YYYYMMDD '), to_date (' 19990421 ', ' YYYYMMDD '), 2344.7 8, ' Vancouver ', ' Manager '
3/

1 row created.

sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (' ', ' Robert ', ' Black ', to_date (' 19840115 ', ' YYYYMMDD '), to_date (' 19980808 ', ' YYYYMMDD '), 2334.7 8, ' Vancouver ', ' Tester ')
3/

1 row created.

sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (' ', ' Linda ', ' Green ', to_date (' 19870730 ', ' YYYYMMDD '), to_date (' 19960104 ', ' YYYYMMDD '), 4322.7 8, ' New York ', ' Tester '
3/

1 row created.

sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (' Modified ', ' David ', ' Larry ', to_date (' 19901231 ', ' YYYYMMDD '), to_date (' 19980212 ', ' YYYYMMDD '), 7897.7 8, ' New York ', ' Manager '
3/

1 row created.

sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (', ' James ', ' Cat ', to_date (' 19960917 ', ' YYYYMMDD '), to_date (' 20020415 ', ' YYYYMMDD '), 1232.7 8, ' Vancouver ', ' Tester ')
3/

1 row created.

Sql>
Sql>
Sql>
Sql>--Display data in the table
Sql> SELECT * from Employee
2/

ID first_name last_name start_dat end_date SALARY City DESCRIPTION


---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------


Jason Martin 25-jul-96 25-jul-06 1234.56 Toronto Programmer


Alison Mathews 21-mar-76 21-feb-86 6661.78 Vancouver Tester


James Smith 12-dec-78 15-mar-90 6544.78 Vancouver Tester


Celia Rice 24-oct-82 21-apr-99 2344.78 Vancouver Manager


Robert Black 15-jan-84 08-aug-98 2334.78 Vancouver Tester


Linda Green 30-jul-87 04-jan-96 4322.78 New York Tester


David Larry 31-dec-90 12-feb-98 7897.78 New York Manager


James Cat 17-sep-96 15-apr-02 1232.78 Vancouver Tester

8 rows selected.

Sql>
Sql>
sql> SELECT ID, first_name, last_name from employee
2/

ID first_name last_name


---- -------------------- --------------------


Jason Martin


Alison Mathews


James Smith


Celia Rice


Robert Black


Linda Green


Modified David Larry


James Cat

8 rows selected.

How to implement select Top N in Oracle
1. Implement select Top N in Oracle

Because Oracle does not support select top statements, it is often used in Oracle to implement a SELECT top N query with a combination of order by and RowNum.

Simply put, the implementation method looks like this:

SELECT Column Name 1 ... Column name n from

(SELECT column Name 1 ... Column name n from table name order BY column name 1 ... Column name N)

WHERE rownum <= N (number of extracted records)

ORDER BY RowNum ASC

Here's a quick example to illustrate.

Customer table Customer (Id,name) has the following data:

ID NAME

-A

Second

Third

Forth

Fifth

Modified Sixth

Modified Seventh

Eighth

Ninth

Ten Tenth

One last

The SQL statements for the first three customers by name letter are as follows:

SELECT * FROM

(SELECT * from CUSTOMER ORDER by NAME)

WHERE rownum <= 3

ORDER BY RowNum ASC

The output results are:

ID NAME

Eighth

Fifth

-A

2. Extract m (m <= N) records in top N records

After we get the top n data, we can consider starting from rownum in order to extract the record of section m in this N record. As we know, RowNum is a hidden fragment of the data number in the record table, so you can extract the record's rownum at the same time as the top n record, and then extract the record number m from the N records, even if we want to get the result.

From the above analysis you can easily get the following SQL statement.

SELECT Column Name 1 ... Column name n from

(

SELECT rownum recno, column name 1 ... Column name Nfrom

(SELECT column Name 1 ... Column name n from table name order BY column name 1 ... Column name N)

WHERE rownum <= N (number of extracted records)

ORDER BY RowNum ASC

)

WHERE recno = m (M <= N)

Based on the data of the same table above, the SQL statement that gets the information of the second customer sorted by the letter of name should read as follows:

SELECT ID, NAME from

(

SELECT rownum recno, ID, NAME from

(SELECT * from CUSTOMER ORDER by NAME)

WHERE rownum <= 3

ORDER by RowNum ASC)

WHERE RECNO = 2

The result is:

ID NAME

Fifth

3. Extract the nth record in a recordset sorted in some way

In the 2 note, when m = N, that is the result of our title. In fact, 2 of the n>m part of the data is basically not used, we are simply to illustrate the convenience of adoption.

As described above, the SQL statement should be:

SELECT Column Name 1 ... Column name n from

(

SELECT rownum recno, column name 1 ... Column name Nfrom

(SELECT column Name 1 ... Column name n from table name order BY column name 1 ... Column name N)

WHERE rownum <= N (number of extracted records)

ORDER BY RowNum ASC

)

WHERE recno = N

Then, the SQL statement for the example in 2 is:

SELECT ID, NAME from

(

SELECT rownum recno, ID, NAME from

(SELECT * from CUSTOMER ORDER by NAME)

WHERE rownum <= 2

ORDER BY RowNum ASC

)

WHERE RECNO = 2

The results are:

ID NAME

Fifth

4. Extract the X record from the beginning of section m of the recordset, sorted in some way

3 is only a case of taking a record, when we need to extract more than one record, at this time the value of N in 2 should be in the range of n >= (M + X-1), when the most economical value is to wait for the good time. Of course the final extraction condition is not RECNO = n, it should be recno BETWEEN m and (M + X-1), so the following SQL statement is:

SELECT Column Name 1 ... Column name n from

(

SELECT rownum recno, column name 1 ... Column name Nfrom

(

SELECT Column Name 1 ... Column name n from table name order BY column name 1 ... Column name N)

WHERE rownum <= N (n >= (M + X-1))

ORDER BY RowNum ASC

)

WHERE recno BETWEEN m and (M + X-1)

Also, take the above data as an example, the SQL statement that extracts the 3 records at the beginning of the alphabetical 2nd record of name is:

SELECT ID, NAME from

(

SELECT rownum recno, ID, NAME from

(SELECT * from CUSTOMER ORDER by NAME)

WHERE rownum <= (2 + 3-1)

ORDER BY RowNum ASC

)

WHERE recno BETWEEN 2 and (2 + 3-1)

Attention:

The order in which the SQL clauses are executed:

1. From
2. WHERE
3. GROUP by
4. Having
5. SELECT
6. ORDER BY