Oracle PL/SQL

1. Oracle PL/SQL SQL

2. Select SELECT column1, column2,...columnN FROM table_name WHERE condition;

3. WHERE Clause SELECT phone FROM customer WHERE customer_id = 300; = Is equal to <>, != Is not equal to < Less than > Greater than <= Is less than or equal to >= Is greater than or equal to

4. Logical Operators • AND • OR • NOT • SELECT first_name, last_name, salary • FROM sales_person • WHERE salary >= 30000 • AND salary <= 40000;

5. Nesting Logical Operators 1. NOT 2. AND 3. OR SELECT first_name, last_name, state, salary FROM sales_person WHERE salary >= 30000 AND salary <= 40000 OR NOT (state = 'VA‘)

6. Other Comparison Keywords • LIKE • IN • BETWEEN…AND • IS NULL

7. LIKE SELECT product_name FROM product WHERE product_name LIKE 'C%'; SELECT product_name FROM product WHERE product_name LIKE '_e%';

8. BETWEEN...AND SELECT first_name, last_name, salary FROM sales_person WHERE salary BETWEEN 30000 AND 40000;

9. IN SELECT first_name, last_name FROM sales_person WHERE state IN ('VA', 'MD');

10. IS NULL SELECT first_name, last_name FROM sales_person WHERE commission IS NULL;

11. Order by SELECT salary, first_name FROM sales_person ORDER BY salary, first_name; SELECT salary, first_name FROM sales_person ORDER BY 1, 2 DESC;

12. Expressions in a SELECT Statement || Concatenate or append - Subtract + Add / Divide * Multiply ( ) Parentheses are used to group expressions.

13. || SELECT first_name || ' ' || last_name FROM sales_person; SELECT first_name || ' ' || last_name fulln FROM sales_person;

14. Expressions in the WHERE Clause SELECT product_name, unit_price, unit_price*1.1 FROM product WHERE unit_price *1.1>45; SELECT product_name, unit_price, unit_price*1.1 AS new_salary FROM product WHERE new_salary > 45;

15. Expressions in the ORDER BY Clause SELECT product_name, unit_price, unit_price*1.1 FROM product WHERE unit_price*1.1 > 45 ORDER BY unit_price * 1.1; SELECT product_name, unit_price, unit_price*1.1 new_price FROM product WHERE unit_price*1.1 > 45 ORDER BY new_price DESC;

16. Single Row Functions • Dual is a dummy table Oracle provides to help us perform ad-hoc queries or mathematical calculations. • Select 224*12 from dual

17. Single Row Functions • There are four types of single row functions. • Numeric Functions • Character Functions • Date Functions • Conversion Functions: • NVL, TO_CHAR, TO_NUMBER, TO_DATE.

18. Numeric Functions • ABS(n) - Absolute value of n • CEIL(n) - Integer value that is Greater than or Equal to n • FLOOR(n) - Integer value that is Less than or Equal to n • ROUND(n, m) - Rounded off value of nup to mdecimal places • TRUNC(n, m) - Truncated value of nup to mdecimal places

19. Character Functions • LOWER (text) Returns text with all letters in lowercase • UPPER (text) Returns text with all letters in uppercase • INITCAP (text) Returns text with all letters in mixed case • LTRIM (text,trim_text) Returns text after removing all occurrences of trim_text from the left of text • RTRIM (text,trim_text)Returns text after removing all occurrences of trim_text from the right of text • SUBSTR (text, m, n) Returns n characters from text starting from the mth position • LENGTH (text) Returns the number ofcharacters in text • LPAD (text, n,pad_text) Returns text left-padded with pad_text for a length of n characters • RPAD (text, n,pad_text) Returns text right-padded with pad_text for a length of n characters

20. Date Functions • ADD_MONTHS (d, n) - Returns the date after adding the number, n to the date d • MONTHS_BETWEEN(d1,d2) - Returns the number after adding the dates, d1 and d2 • ROUND(d, format) - Returns the date rounded off to the nearest century, year,month, date, hour, minute, or second as specifed by theformat. • TRUNC(d, format) - Returns the date lesser than or equal to the nearest century,year, month, date, hour, minute, or second as speci.ed by the format. • NEXT_DAY(d,week_day) - Returns the nearest date on or after d, that is the same as the week_day specifed. week_day must be a valid format of day of the week. • LAST_DAY(d) - Returns the date of the last day in the month with respect to d. Used to determine the number of days remaining in a month. • SYSDATE - Returns the current date and time.

21. Examples • ADD_MONTHS('12-JUN-99', 2) 12-AUG-99 • MONTHS_BETWEEN('12-AUG-99','12-JUN-99') 2 • NEXT_DAY('01-JAN-00','MONDAY') 03-JAN-00 • LAST_DAY('01-JAN-00') 31-JAN-00. • SELECT SYSDATE FROM dual • SELECT ROUND(SYSDATE, 'YEAR') FROM dual;

22. Conversion Functions • SELECT TO_CHAR(22) FROM DUAL; • SELECT TO_DATE('11-JAN-02') FROM DUAL; • NVL(val1,val2) – if val1=null replace it with val2 • DECODE(val1, val2, val3, val4, val5, val6, default val) • SELECT grade, DECODE(grade, 1, 'A', 2, 'B', 3, 'C', 4, 'D', 'E')FROM salary_grade;

23. SELECT TO_CHAR(SYSDATE,'Month dd, yyyy')FROM dual; • SELECT TO_DATE('01/01/2000', 'mm/dd/yyyy')FROM dual; • SELECT salary, commission, salary+NVL(commission, 0) incomeFROM sales_person;

24. Group Functions • AVG • COUNT • MAX • MIN • SUM

25. DISTINCT AND ALL • SELECT COUNT (DISTINCT product_id)FROM order_items; • SELECT SUM(DISTINCT MANAGER_ID) FROM SALES_PERSON • SELECT SUM(ALL MANAGER_ID) FROM SALES_PERSON

26. Using GROUP BY and HAVING SELECT state, MAX(salary) FROM sales_person GROUP BY state; SELECT state, gender, MAX (salary) FROM sales_person GROUP BY gender, state;

27. SELECT state, gender, MAX (salary) FROM sales_person WHERE MAX (salary) > 40000 GROUP BY gender, state; SELECT state, gender, MAX (salary) FROM sales_person GROUP BY gender, state HAVING MAX (salary) > 40000;