- NVL stands FOR NULL VALUE
- NVL(COLUMN,VALUE)
- Must be always same TO data TYPE
- Used FOR NULL VALUES
- TO change NULL VALUE
- SELECT employee_id,first_name,NVL(commission_pct,0) AS commission FROM employees WHERE First_Name LIKE 'A%';
- TO_CHAR IS used TO CONVERT data TYPE
- SELECT employee_id,first_name,NVL(TO_CHAR(commission_pct),'N/A') AS commission FROM employees WHERE First_Name LIKE 'A%';
- SELECT department_id,department_name,NVL(TO_CHAR(manager_id),'N/A') AS ManagerID FROM departments;
- SELECT table_name FROM user_tables;
- TO show ALL tables OF the CURRENT logged IN username
- SELECT 2+2 AS SUM FROM dual;
- SELECT Employee_ID, Salary*1.1 AS Salary, Hire_Date-4 AS HIRE_DATE, Department_ID-1 AS Department_ID FROM employees;
- SELECT UPPER(first_name) FROM hr.employees;
- - TO CONVERT TO UPPER CASE
- SELECT LOWER(first_name) AS First_name FROM hr.employees;
- - TO CONVERT TO the LOWER CASE
- SELECT employee_id FROM employees WHERE employee_id IN (100,105,102);
- - TO include the only VALUES IN "IN".
- SELECT region_id FROM hr.regions WHERE region_id IN (1,2,5);
- SELECT * FROM departments WHERE manager_id IS NULL;
- WHEN manager_id IS NULL
- SELECT employee_id,first_name,salary FROM employees ORDER BY salary;
- SELECT employee_id,first_name,salary FROM employees ORDER BY salary DESC;
- SELECT employee_id, first_name, salary, (10*(salary/5)+3000)-1000 "New Salary" FROM employees ORDER BY "New Salary";
- SELECT employee_id, first_name, last_name, department_id FROM employees WHERE department_id = &department_no;
- TO take input, department_no IS a variable
- SELECT * FROM cat; - FOR catalogue
- TABLE, VIEW, SEQUENCE - three types
- currently doing TABLE
- SELECT ABS(-15) "Absolute" FROM dual; - CONVERT TO +
- SELECT POWER(3,2) "Raised" FROM dual; - POWER
- SELECT ROUND(15.91,1) FROM dual; - TO ROUND off TO 1 DECIMAL
- SELECT SQRT(25) "SQUARE ROOT" FROM dual;
- SELECT COUNT(*) FROM departments; - total NUMBER OF ROWS IN department
- SELECT COUNT(department_name) FROM departments WHERE department_name = 'IT';
- SELECT COUNT(department_name) AS Departmemt_Name FROM departments WHERE department_name LIKE 'IT%';
- SELECT first_name,commission_pct FROM employees ORDER BY commission_pct NULLS FIRST; - TO show NULL FIRST
- SELECT first_name,commission_pct FROM employees ORDER BY commission_pct NULLS LAST; - TO show ALL NULL IN LAST
Recent Pastes