SQL 101 2 nd Session

1. SQL 101 2nd Session

2. Functions: - Character functions  today - Number functions - Single Row functions

3. Character Functions: LOWER UPPER INITCAP LPAD RPAD LTRIM RTRIM TRIM SUBSTR INSTR LENGTH in WHERE & GROUP BY clauses Nested functions Concatenation REPLACE TRANSLATE SOUNDEX

4. Single-Row Functions: NVL COALESCE NVL2 LNNVL NULLIF NANVL DECODE Searched CASE Simple CASE

5. Number Functions: ABS SIGN ROUND TRUNC FLOOR CEIL MOD REMAINDER Floating point numbers Arithmetic operators

6. Character functions.... UPPER(char) LOWER(char) INITCAP(char) LPAD(char1, n [, char2]) RPAD(char1, n [, char2]) SELECT state, LOWER(state), LOWER('State') FROM zipcode; SELECT UPPER(city) as "Upper Case City", state, INITCAP(state) FROM zipcode; WHERE zip = '10035'; SELECT RPAD(city, 20, '*') as "City Name", LPAD(state, 10, '-') as "State Name" FROM zipcode;

7. Character functions.... The DUAL table Character functions.... The DUAL table contains a single row and a single column called DUMMY. It’s often used with functions to select values that do not exist in tables such as today’s date. A single row is always returned in the result set. This table is owned by SYS and is available to all users. SELECT * from dual;

8. Character functions.... LTRIM(char1 [, char2]) RTRIM(char1 [, char2]) TRIM([LEADING|TRAILING|BOTH] char1 FROM char2) TRIM(char2) SUBSTR(char1, start_pos [, substr_length]) SELECT LTRIM('0001234500', '0') left, RTRIM('0001234500', '0') right, LTRIM(RTRIM('0001234500', '0'), '0') both FROM dual; SELECT TRIM(LEADING'0' FROM '0001234500') leading, TRIM(TRAILING '0' FROM '0001234500') trailing, TRIM('0' FROM '0001234500') both FROM dual; Column "1-5" format a6 Column "6 only" format a6 SELECT last_name, SUBSTR(last_name, 1, 5) as "1-5", SUBSTR(last_name, 6) as "6 only" FROM student;

9. Character functions.... INSTR(char1, char2 [,start_pos [, occurrence]]) LENGTH(char1) Column description format a30 Column "er" format 999 SELECT description, INSTR(description, 'er') "er" FROM course; SELECT LENGTH('Hello there') FROM dual; SELECT first_name, last_name FROM student WHERE SUBSTR(last_name, 1, 2) = 'Mo';

10. WHERE and GROUP BY clauses: SELECT first_name, last_name FROM student WHERE INSTR(first_name, '.') > 0 ORDER BY LENGTH(last_name); Nested Functions: Column mi format a3 Column first format a10 SELECT first_name, SUBSTR(first_name, INSTR(first_name, '.') -1) mi, SUBSTR(first_name, 1, INSTR(first_name, '.') -2) first FROM student WHERE INSTR(first_name, '.') >= 3;

11. Concatenation: SELECT CONCAT(city, state) FROM zipcode; SELECT city||state||zip FROM zipcode; SELECT city||', '||state||' '||zip FROM zipcode;

12. Character functions.... REPLACE(char, if, then) TRANSLATE(char, if, then) SELECT REPLACE('My hand is asleep', 'hand', 'foot') FROM dual; SELECT REPLACE('My hand is asleep', 'X', 'foot') FROM dual; SELECT phone FROM student WHERE TRANSLATE(phone, '0123456789','##########') <> '###-###-####'; SELECT phone FROM student WHERE TRANSLATE(phone, '012345678','##########') <> '###-###-####';

13. Soundex: SELECT student_id, last_name FROM student WHERE SOUNDEX(last_name) = SOUNDEX('Torch'); Just my 2cents.... This function is crap.... Don't ever use BUT, know it exists.