Review of SQL contd.

1
Review of SQL contd.

• Using Functions in SELECT Statements

SELECT empid, lastname, firstname,
DECODE(sex,M,MALE,F,FEMALE) sex FROM
emp ORDER BY empid DESC
EMPID LASTNAME FIRSTNAME SEX -----
-------- --------- ------ 60403
HARPER ROD MALE 49539 QIAN
LEE FEMALE 49392 SPANKY
STACY FEMALE 39334 SMITH GINA
FEMALE 02039 WALLA RAJENDRA MALE
SELECT empid, lastname, firstname,
RPAD(DECODE(sex,M,MALE,F,FEMALE),10,-)
sex FROM emp ORDER BY empid DESC
EMPID LASTNAME FIRSTNAME SEX ----- --------
--------- ---------- 60403 HARPER ROD
MALE------ 49539 QIAN LEE
FEMALE---- 49392 SPANKY STACY
FEMALE---- 39334 SMITH GINA
FEMALE---- 02039 WALLA RAJENDRA
MALE------
TITLE CHARS -------------------
----- HAPPY BIRTHDAY Y BIR DIAMONDS AND
RUST ONDS AMAZING GRACE ING G
SELECT title, SUBSTR(title,5,5) CHARS FROM SONGS
TITLE CHARACTERS ----------------
--- --------------- HAPPY BIRTHDAY Y
BIRTHDAY DIAMONDS AND RUST ONDS AND
RUST AMAZING GRACE ING GRACE
SELECT title, SUBSTR(title,5) CHARACTERS FROM
SONGS
2
Select Statements that Join Data from More than
One Table
SELECT a.antique_name, a.antique_cost,
a.storage_box_number, b.box_name,
b.box_location FROM antique a, storage_box
b WHERE a.antique_name in (VICTROLA,CAMERA,RA
DIO) AND a.storage_box_number
b.storage_box_number
A.ANTIQUE_N A.ANTIQ A.STOR B.BOX_NAME
B.BOX_LOCATION ----------- -------
----- ----------
-------------- VICTROLA 150.00 3
ALPHA-3 ALPHA BLDG CAMERA
75.00 4 ALPHA-4
ALPHA BLDG RADIO 200.00 4
ALPHA-4 ALPHA BLDG

• Creating Outer Joins

SELECT antique_name, antique_cost,
antique.storage_box_number, box_name,
box_location FROM antique, storage_box WHERE
box_location VICTROLA AND antique.storage_box_
number storage_box.storage_box_number
ANTIQUE_NAM ANTIQUE ANTIQU BOX_NAME
BOX_LOCATION ----------- -------
------ -------- ------------ VICTROLA
150.00 3 ALPHA-3
ALPHA BLDG
SELECT antique_name, antique_cost FROM antique
ANTIQUE_NAM ANTIQUE -----------
------- VICTROLA 150.00 VICTROLA
90.00 VICTROLA 45.00
SELECT antique_name, antique_cost,
antique.storage_box_number, box_name,
box_location FROM antique, storage_box WHERE
box_location VICTROLA AND antique.storage_box_
number storage_box.storage_box_number ()
ANTIQUE_NAM ANTIQUE ANTIQU BOX_NAME
BOX_LOCATION ----------- -------
------ --------
------------ VICTROLA 150.00 3
ALPHA-3 ALPHA BLDG VICTROLA
90.00 VICTROLA 75.00
3
Subqueries

• Joining a Table to Itself

SELECT a.empid, a.location, a.test_date,
b.location, b.test_date FROM test_results a,
test_results b WHERE a.empid b.empid AND
a.location ltgt b.location AND a.test_date gt
sysdate-365 AND b.test_date gt sysdate-365

• Nested Subqueries

SELECT candidate_id, name, employer FROM
candidate WHERE candidate_id IN (SELECT
candidate_id FROM skills WHERE skill_type
ORACLE DBA AND certified YES) AND employer
IN (SELECT employer FROM companies WHERE city
MINNEAPOLIS)
CANDIDATE_ID NAME EMPLOYER ------------
-------- -------------- 60549
DURNAM TransCom
SELECT distinct e.dept FROM emp e WHERE EXISTS
(SELECT i.empid FROM invoice i WHERE i.empid
e.empid AND i.pay_date gt SYSDATE-365)