SubSelect Statement

SELECT a.IDTEMPAH, a.MATRIK, a.WARGA_TYPE,
decode(a.WARGA_TYPE, 'S', (select b.nama from asas b where b.nostaf = a.matrik), 'P', (select c.nama from biodata c where c.matrik = a.matrik)) as nama,

decode(a.WARGA_TYPE, 'S', (select '('||e.KDFKLT||')'||' '||e.SINGKATN||' - '||e.NAMA from khidmat d, kdfaklt e where d.FAKULTI = e.KDFKLT and d.nostaf = a.matrik),'P',(select '('||f.kodfak||')'||' '||g.singfak||' - '||g.nfakulti from biodata f, fakulti g where f.kodfak = g.kfakulti AND f.matrik = a.matrik )) as ptj,

a.TKH_PERGI, a.TKH_PULANG, a.STATUS, a.LOC_CODE, a.LOC_DESC

FROM TEMPAHAN a
WHERE a.IDTEMPAH = :as_id;

/* one of modified select statement used in one of my project. The first decode used to define WARGA_TYPE either equal to 'S' or 'P'. If 'S' then select nama from asas table else select nama from biodata table
*/

My Superimpossed Image

I have modified this image sometime ago, only the eyes part is mine. Others from street fighters' character named Kyo

Using ROWNUM

//getting first row of result

SELECT a.nostaf, a.nama , b.department
FROM asas, khidmat b
WHERE a.nostaf = b.nostaf
AND ROWNUM = 1;

JOIN SQL

//INNER JOIN
SELECT a.nostaf, a.nama , b.department
FROM asas a INNER JOIN khidmat b
ON a.nostaf = b.nostaf
AND a.nostaf = :stafno;

//OR
SELECT a.nostaf, a.nama , b.department
FROM asas, khidmat b
WHERE a.nostaf = b.nostaf
AND a.nostaf = :stafno;


//NATURAL JOIN
SELECT a.nostaf, a.nama , b.department
FROM asas a NATURAL JOIN khidmat b
WHERE a.nostaf = b.nostaf

CASE SQL

SELECT a.KOD_SUBJECT,
    a.SESI,
    a.SEMESTER,
    a.SKOR,
    c.NAME,
    b.DESCRIPTION,
    c.STAFNO,
    (CASE
     WHEN a.SKOR > 4.49 THEN 'Cemerlang'
     WHEN a.SKOR > 3.99 THEN 'Baik'
     WHEN a.SKOR > 2.99 THEN 'Sederhana'
     ELSE 'Lemah'
    END) as STATUS
FROM SCORES a, JABATAN b, ASAS c
WHERE ( a.KODFAK = b.KODFAK ) AND
    ( a.KODJAB = b.KODJAB ) AND
    ( a.STAFNO = c.STAFNO ) AND
    ( a.STAFNO = :no_staff )