Oracle Database 12c: SQL Workshop I: 02 Retrieving Data Using the SQL SELECT Statement

Retrieving Data Using the SQL SELECT Statement

Basic SELECT Statement

SELECT {*|[DISTINCT] column|expression [alias],...}
FROM table;

NULL values in the SELECT statement

Null is a value that is unavailable, unassigned, unknown, or inapplicable. Null is not the same as zero or a blank space. Zero is a number and blank space is a character.
Columns of any data type can contain nulls. However, some constraints (NOT NULL and PRIMARY KEY) prevent nulls from being used in the column.
If any column value in an arithmetic expression is null, the result is null.

Column aliases

SQL> select riqi from t1;

RIQI
-------------------
2018-08-07 22:21:54
2001-01-01 00:00:00

SQL> select riqi hiredate from t1;

HIREDATE
-------------------
2018-08-07 22:21:54
2001-01-01 00:00:00

SQL> select riqi as hiredate from t1;

HIREDATE
-------------------
2018-08-07 22:21:54
2001-01-01 00:00:00

SQL> select riqi "hiredate" from t1;

hiredate
-------------------
2018-08-07 22:21:54
2001-01-01 00:00:00

SQL> select riqi as "Hire Date" from t1;

Hire Date
-------------------
2018-08-07 22:21:54
2001-01-01 00:00:00

SQL> select riqi "Hire Date" from t1;

Hire Date
-------------------
2018-08-07 22:21:54
2001-01-01 00:00:00

Use of concatenation operator, alternative quote operator

SQL> select t2name||id quantity from t2;

QUANTITY
--------------------------------------------------------------------------------
coat1

SQL> select t2name||' amt is '||id from t2;

T2NAME||'AMTIS'||ID
--------------------------------------------------------------------------------
coat amt is 1

SQL> select t2name||'s amount is||id from t2;
ERROR:
ORA-01756: quoted string not properly terminated

SQL> select t2name||q'< 's amt is >'||id from t2;

T2NAME||Q'<'SAMTIS>'||ID
--------------------------------------------------------------------------------
coat 's amt is 1

SQL> select t2name||q'[ 's amt is ]'||id from t2;

T2NAME||Q'['SAMTIS]'||ID
--------------------------------------------------------------------------------
coat 's amt is 1

SQL> select t2name||q'{ 's amt is }'||id from t2;

T2NAME||Q'{'SAMTIS}'||ID
--------------------------------------------------------------------------------
coat 's amt is 1

SQL> select t2name||q'( 's amt is )'||id from t2;

T2NAME||Q'('SAMTIS)'||ID
--------------------------------------------------------------------------------
coat 's amt is 1

SQL> select t2name||q'< 's >'||amt is ||id from t2;
select t2name||q'< 's >'||amt is ||id from t2
                              *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> select t2name||q'< 's >'||'amt is '||id from t2;

T2NAME||Q'<'S>'||'AMTIS'||ID
--------------------------------------------------------------------------------
coat 's amt is 1