How to use Oracle SQL SELECT statements

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.