SELECT

<select-stmt> ::= <query-expression> [<order-by-clause>]

 

<query-expression> ::= <query-specification>

| <query-expression> UNION [ALL] <query-expression>

 

<query-specification> ::= SELECT [ALL|DISTINCT] [TOP N] <select-list> FROM

<table-ref-list> [<where-clause>]

[<group-by-clause>] [having-clause]

| SELECT [ALL|DISTINCT]<select-list> FROM

<table-ref-list> [<where-clause>] FOR UPDATE [NOWAIT]

 

<select-list> ::= <select-item> [,<select-item>]…

 

<select-item> ::= <derived-column> [<as-clause>]

| <column-name> = <derived-column>

| *

 

<derived-column> ::= <value-expression>

 

<as-clause> ::= [AS] <column-name>

| [AS] <character-literal>

 

<table-ref-list> ::= <table-ref> [, <table-ref>]…

 

<table-ref> ::= <simple-table-ref>

| <derived-table-ref>

| <joined-table>

| <odbc-joined-table>

 

<simple-table-ref> ::= <table_name> [[AS] identifier]

| <table_name> [[AS] <character-literal>

 

<derived_table_ref> ::= <derived_table> [[AS] identifier]

| <derived_table> [[AS] <character-literal>

 

<derived_table> ::= <table_subquery>

<table_subquery> ::= <subquery>

 

<subquery> ::= LP <query_expression> RP

 

<joined-table> ::= <qualified-join>

| ( <joined-table> )

 

<qualified-join> ::= <table-ref> <outer_join_type> <table-ref>

ON <search-condition>

 

<outer-join-type> ::= [LEFT | RIGHT | FULL] [OUTER] JOIN| INNER JOIN

 

<odbc-joined-table> ::= <ODBC_std_esc_initiator> oj <joined-table>

<ODBC_std_esc_terminator>

| <ODBC_ext_esc_initiator> oj < joined-table>

<ODBC_ext_esc_terminator> ON search-condition

 

<where-clause> ::= WHERE <search-condition>

 

<group-by-clause> ::= GROUP BY <value-expression> [,<value-expression>]…

 

<having-clause> ::= HAVING <search-condition>

 

<order-by-clause> ::= ORDER BY <sort-spec> [, < sort-spec>]…

 

<sort-spec> ::= <sort-key> [ASC | DESC]

 

<sort-key> ::= <value-expression>

 

<search-condition> ::= <boolean-term>

| <search-condition> OR <boolean-term>

 

<boolean-term> ::= <boolean-factor>

| <boolean-term> AND <boolean-factor>

 

<boolean-factor> ::= [NOT] <boolean-primary>

 

<boolean-primary> ::= <predicate>

| (<search-condition>)

 

<predicate> ::= <comparison-predicate>

| <between-predicate>

| <in-predicate>

| <like-predicate>

| <null-predicate>

| <quantified-comparison-predicate>

| <exists-predicate>

| <unique-predicate>

 

<comparison-predicate> ::= <row-value-constructor> <compare-operator>

<row-value-constructor>

 

<compare-operator> ::= > | >= | < | <= | = | != | <>

 

<between-predicate> ::= <row-value-constructor> [NOT] BETWEEN

<row-value-constructor>

 

<in-predicate> ::= <row-value-constructor> [NOT] IN

<in-value-list>

| <row-value-constructor> [NOT] IN

<subquery>

 

<in-value-list> ::= <value-expression> [,<value-expression>]…

 

<like-predicate> ::= <row-value-constructor> [NOT] LIKE

<pattern-value> <escape-clause>

 

<null-predicate> ::= <row-value-constructor> [NOT] NULL

 

<pattern-value> ::= <value-expression>

| ?

<escape-clause> ::= ESCAPE <value-expression>

 

<quantified-comparison-predicate> ::= <row-value-constructor>

<compare-operator> <comparison-quantifier> <table-subquery>

 

<comparison-quantifier> ::= ALL | SOME | ANY

 

<exists_predicate> ::= EXISTS <table_subquery>

 

<unique_predicate> ::= UNIQUE <table_subquery>

 

<row-value-constructor> ::= <value-expression> | NULL

 

<value-expression> ::= <numeric-value-expression>

 

<numeric-value-expression> ::= <term>

| <numeric-value-expression> + <term>

| <numeric-value-expression> - <term>

 

<term> ::= <factor>

| <term> * <factor>

| <term> / <factor>

 

<factor> ::= <numeric-primary>

 

<numeric-primary> ::= <numeric-literal>

| <character-literal>

| <datetime-literal>

| < interval-literal>

| <column-name>

| <set-function-spec>

| ?

| <subquery>

| NULL

| ( < value-expression> )

| <case-expression>

 

<case-expression> :: =

CASE <value-expression>

WHEN <value-expression> THEN <value-expression>

[, WHEN <value-expression> THEN <value-expression> ] …

[

ELSE <value-expression>

]

END

| CASE

WHEN <value-expression> THEN <value-expression>

[,WHEN <value-expression> THEN <value-expression> ] …

[

ELSE <value-expression>

]

END

<datetime-literal> ::= <ODBC-date-time-escape>

 

<scalar-function> ::= <ODBC_std_esc_initiator>

fn <scalar-function-call>

<ODBC_std_esc_terminator>

| <ODBC_ext_esc_initiator>

fn <scalar-function-call>

<ODBC_ext_esc_terminator>

| <scalar-function-call>

 

<scalar-function-call> ::= <scalar-function-name> ( )

| <scalar-function-name> ( <value-expression-list> )

 

< value-expression-list > ::= <value-expression > [,<value-expression >]…

 

<set-function-spec> ::= COUNT ( * )

| AVG ( [ALL | DISTINCT] < value-expression > )

| MIN ( [ALL | DISTINCT] < value-expression > )

| MAX ( [ALL | DISTINCT] < value-expression > )

| SUM ( [ALL | DISTINCT] < value-expression > )

| COUNT ( [ALL | DISTINCT] < value-expression > )

| VAR ( [ALL | DISTINCT] < value-expression > )

| VAR_SAMP ( [ALL | DISTINCT] < value-expression > )

| STDDEV ( [ALL | DISTINCT] < value-expression > )

| STDDEV_SAMP ( [ALL | DISTINCT] < value-expression > )

| STDEV ( [ALL | DISTINCT] < value-expression > )

| VARP ( [ALL | DISTINCT] < value-expression > )

| VAR_POP ( [ALL | DISTINCT] < value-expression > )

| STDDEVP ( [ALL | DISTINCT] < value-expression > )

| STDDEV_POP ( [ALL | DISTINCT] < value-expression > )

| STDEVP ( [ALL | DISTINCT] < value-expression > )

SELECT * FROM emp WHERE sal >1000 ORDER BY sal

SELECT deptno, count(*) FROM emp GROUP BY deptno

SELECT emp.empno, emp.ename, dept.deptno, dept.divno FROM emp

INNER JOIN dept ON emp.deptno=dept.deptno

SELECT * FROM emp WHERE deptno NOT IN (SELECT deptno FROM dept)