Query 1. Join example
Table 1 Table 2
EMP_NM
|
EMP_JB
|
SELECT
|
nm.id
|
ANSWER
|
||||||
,nm.name
|
================
|
|||||||||
ID NAME
|
ID JOB
|
,jb.job
|
ID NAME
|
JOB
|
||||||
FROM
|
emp_nm nm
|
|||||||||
10 Sanders
|
10 Sales
|
,emp_jb jb
|
10
|
Sanders
|
Sales
|
|||||
20 Pernal
|
20 Clerk
|
WHERE
|
nm.id = jb.id
|
20
|
Pernal
|
Clerk
|
||||
50 Hanes
|
ORDER BY 1;
|
|||||||||
Query 2. Leftouterjoin example
Tables Query Result
Table 1 Table 2
Query 3. Subquery example
Tables Query Result
Table 1 Table 2
EMP_NM
|
EMP_JB
|
SELECT
|
*
|
ANSWER
|
|||||
FROM
|
emp_nm
|
nm
|
========
|
||||||
ID NAME
|
ID JOB
|
WHERENOT EXISTS
|
ID NAME
|
||||||
(SELECT
|
== =====
|
||||||||
10 Sanders
|
10 Sales
|
FROM
|
emp_jb jb
|
50 Hanes
|
|||||
20 Pernal
|
20 Clerk
|
WHERE
|
nm.id=jb.id)
|
||||||
50 Hanes
|
ORDER BY id;
|
||||||||
Query 4. Union example
Tables Query Result
Table 1 Table 2
EMP_NM
|
EMP_JB
|
SELECT
|
*
|
ANSWER
|
|||||
FROM
|
emp_nm
|
=========
|
|||||||
ID NAME
|
ID JOB
|
WHERE
|
name < 'S'
|
ID 2
|
|||||
UNION
|
|||||||||
10 Sanders
|
10 Sales
|
SELECT
|
*
|
10
|
Sales
|
||||
20 Pernal
|
20 Clerk
|
FROM
|
emp_jb
|
20
|
Clerk
|
||||
50 Hanes
|
ORDER BY 1,2;
|
20
|
Pernal
|
||||||
50
|
Hanes
|
||||||||
Query 5. Assign rownumbersexample
Tables Query Result
Table 1
EMP_JB
|
SELECT
|
id
|
|||
,job
|
ANSWER
|
||||
ID JOB
|
,ROW_NUMBER()
OVER(ORDER BY job)ASR |
==========
|
|||
FROM
|
emp_jb
|
ID JOB
|
|||
10 Sales
|
ORDER BY job;
|
||||
20 Clerk
|
20
|
Clerk 1
|
|||
10
|
Sales 2
|
||||