system commands:
SHOW ALL;

select

SELECT salutation, first_name, last_name FROM student WHERE LAST_NAME = 'Grant' ORDER BY salutation DESC, first_name ASC;

SELECT DISTINCT zip FROM instructor
output: all those zips which are unique/prints out only once

.....
WHERE last_name LIKE 'S%';

SELECT description, cost FROM course
WHERE cost BETWEEN 1000 AND 1100;

....
WHERE prerequisite IS NULL;

....
WHERE zip IN ('10048', '11102', '11209');

....
WHERE zip = '10048' OR zip = '11102' OR zip = '11209';

....
WHERE (last_name LIKE '%i%' OR last_name LIKE '%I%')
AND zip = '10025';

formatting

SELECT RPAD(city, 20, '*') "City Name",

LPAD(state, 10, '-') "State Name" FROM zipcode;

format for LPAD:
LPAD(char1, n [, char2])
RPAD(char1, n [, char2])

SELECT LPAD(city, 20) as "City Name", state FROM zipcode;

output: city Name         ST
        ----------------- --
                Santurce  NY    

SELECT last_name, SUBSTR(last_name, 1, 5),

SUBSTR(last_name, 6) FROM student;

2nd param: start position
3rd param: optional/length of subset

---

SELECT description, INSTR(description, 'er') FROM course;

---

SELECT CONCAT(city, state) FROM zipcode CONCAT(CITY, STATE)

output

SanturcePR NorthAdamsMA .....

---

SELECT city || state || zip FROM zipcode;

output: puts them all together with no whitespace between them ,same as above

CITY||','||STATE||''||ZIP

---

SELECT description, INSTR(description, 'er') FROM course;
output: 2 columns the latter one titled INSTR(description, 'er') and containing a number

---

TRANSLATE provides one-for-one character substitution using if-then-else logic

SELECT phone FROM student WHERE
TRANSLATE( phone, '01234567890', '##########-') <> '##-###-####';

---

SELECT state, LOWER(state), LOWER('State') FROM zipcode;
INITCAP
output: capilatizes just the first letter of the argument

number functions:
SELECT 'the absolute of -29 is '|| ABS(-29) FROM dual;

---

Leave a Reply Your Name:     anonymous Your Email: Website:

Comments: The author will be notified of your reply.

return to top