Oracle SQL highlights

 

1. A common query statement, for example:
SELECT last_name, department_id FROM employees;
2. query view
SELECT employee_id, last_name, job_title, department_name, country_name,
Region_name FROM emp_details_view;
3. Change column name query
SELECT Employee ID "Employee ID number", last_name "Employee last name ",
First_name "Employee first name" FROM employees;
4. join query
Natural join: The public columns in the two tables must have the same name and structure type. Otherwise, an error is returned.
SELECT employee_id, last_name, first_name, department_id,
Department_name, manager_id FROM employees natural join orders;
USING is used to query three or more tables, provided that the columns used to connect the two tables must have the same name.
SELECT e. employee_id, e. last_name, e. first_name, e. manager_id, department_id,
D. department_name, d. manager_id FROM employees e
JOIN orders ments d USING (department_id );
Add the WHERE clause to join.
SELECT e. employee_id, e. last_name, e. first_name, e. department_id,
D. department_name, d. manager_id, d. location_id, l. country_id FROM employees e
JOIN orders ments d ON e. department_id = d. department_id
JOIN locations l ON d. location_id = l. location_id
WHERE l. location_id = 1700;
Self-connection:
SELECT e. employee_id emp_id, e. last_name emp_lastname, m. employee_id mgr_id,
M. last_name mgr_lastname
FROM employees e
JOIN employees m ON e. manager_id = m. employee_id;
Left outer join: all records in the LEFT table will be queried even if there is no JOIN in the right table.
SELECT e. employee_id, e. last_name, e. department_id, d. department_name
FROM employees e left outer join orders ments d
ON (e. department_id = d. department_id );
Right outer join: all records in the RIGHT table will be queried even if there is no JOIN in the left table.
SELECT e. employee_id, e. last_name, e. department_id, d. department_name
FROM employees e right outer join orders ments d
ON (e. department_id = d. department_id );
Full outer join: all records in the left and right tables are queried.
SELECT e. employee_id, e. last_name, e. department_id, d. department_name
FROM employees e full outer join orders ments d
ON (e. department_id = d. department_id );

5. Bind Variable query: Oracle stores parsed and compiled SQL statements and other content in the shared pool, this is a very important Storage Structure in SGA (the memory structure is mainly divided into SGA and PGA ). The bound variable query is compiled only once during execution in Oracle, and then the query plan is stored in a shared pool for reuse. This is called soft parsing. The usage is as follows:
SELECT * FROM employees WHERE employee_id =: employee_id
6. query virtual columns: a virtual column is a table-like column in Oracle, but is not stored in a table. When querying a virtual column, a value is returned, so it is similar to a function. The virtual columns in Oracle include ROWNUM, SYSDATE, and USER. Eg:
Select sysdate "NOW" from dual;
Select user from dual;
SELECT employee_id, hire_date, sysdate from employees where rownum <10;
7. Function query:
Using Numeric Functions:
SELECT employee_id, ROUND (salary/30, 2) "Salary per day" FROM employees;
SELECT employee_id, TRUNC (salary/30, 0) "Salary per day" FROM employees;
SELECT employee_id, MOD (employee_id, 2) FROM employees;
Using Character Functions:
SELECT employee_id, UPPER (last_name), LOWER (first_name) FROM employees;
SELECT employee_id, INITCAP (first_name), INITCAP (last_name) FROM employees;
SELECT employee_id, RTRIM (first_name) | ''| LTRIM (last_name) FROM employees;
SELECT employee_id, TRIM (last_name) | ',' | TRIM (first_name) FROM employees;
SELECT employee_id, RPAD (last_name, 30, ''), first_name FROM employees;
SELECT employee_id, SUBSTR (last_name, 1, 10) FROM employees;
Select length (last_name) FROM employees;
SELECT employee_id, REPLACE (job_id, 'sh', 'shipping') FROM employees
Where substr (job_id, 1, 2) = 'sh ';
Using Date Functions:
SELECT employee_id, TRUNC (MONTHS_BETWEEN (SYSDATE, HIRE_DATE) "Months Employed"
FROM employees;
SELECT employee_id, EXTRACT (year from hire_date) "Year Hired" FROM employees;
Select extract (year from sysdate) | EXTRACT (month from sysdate) |
EXTRACT (day from sysdate) "Current Date" from dual;
SELECT employee_id, hire_date, ADD_MONTHS (hire_date, 3) FROM employees;
SELECT employee_id, hire_date, LAST_DAY (hire_date) "Last day of month"
FROM employees;
SELECT distinct imestamp from dual;
Using Conversion Functions:
SELECT TO_CHAR (SYSDATE, 'dd-MON-YYYY ad') "Today" from dual;
SELECT TO_CHAR (SYSDATE, 'fmmonth DD yyyy') "Today" from dual;
SELECT TO_CHAR (SYSDATE, 'Mm-DD-YYYY HH24: MI: ss') "Now" from dual;
SELECT hire_date, TO_CHAR (hire_date, 'ds') "Short Date" FROM employees;
SELECT hire_date, TO_CHAR (hire_date, 'dl ') "Long Date" FROM employees;
SELECT TO_CHAR (EXTRACT (year from sysdate) |
TO_CHAR (EXTRACT (month from sysdate), 'fm09') |
TO_CHAR (EXTRACT (day from sysdate), 'fm09') "Current Date" from dual;
SELECT TO_CHAR (CURRENT_DATE, 'dd-MON-YYYY HH24: MI: ss') "Current Date" from dual;
SELECT TO_CHAR (salary, '$99,999.99') salary FROM employees;
SELECT TO_NUMBER ('192. 99') + 1234 from dual;
SELECT TO_NUMBER ('123', '99g999d99') + 11,200.34 from dual;
SELECT TO_DATE ('27-OCT-98 ', 'dd-MON-RR') from dual;
SELECT TO_DATE ('28-Nov-05 14:10:10 ', 'dd-Mon-YY HH24: MI: ss') from dual;
SELECT TO_DATE ('january 15,200 6, A. M. ', 'month dd, YYYY, HH: mi a. M .')
From dual;
SELECT TO_TIMESTAMP ('10-Sep-05 14:10:10. 123', 'dd-Mon-RR HH24: MI: SS. ff ')
From dual;
Using Aggregate Functions:
Select count (*) "Employee Count" FROM employees WHERE manager_id = 122;
Select count (*) "Employee Count", manager_id FROM employees
Group by manager_id order by manager_id
Select count (commission_pct) FROM employees;
Select count (DISTINCT department_id) FROM employees;
Select min (salary), MAX (salary), AVG (salary), job_id FROM employees
Group by job_id order by job_id;
Select rank (2600) WITHIN GROUP
(Order by salary DESC) "Rank of $2,600 among clerks"
FROM employees WHERE job_id LIKE '% cler ';
SELECT job_id, employee_id, last_name, salary, DENSE_RANK () OVER
(Partition by job_id order by salary DESC) "Salary Rank (Dense )"
FROM employees WHERE job_id = 'sh _ cler ';
SELECT employee_id, salary, hire_date, STDDEV (salary)
OVER (order by hire_date) "Std Deviation of Salary"
FROM employees WHERE job_id = 'st _ cler ';
Using NULL Value Functions:
SELECT commission_pct, NVL (commission_pct, 0) FROM employees;
SELECT phone_number, NVL (phone_number, 'missing') FROM employees;
SELECT employee_id, last_name, commission_pct, salary,
NVL2 (commission_pct, salary + (salary * commission_pct), salary) income
FROM employees;
Using Conditional Functions:
SELECT employee_id, hire_date, salary,
Case when hire_date <TO_DATE ('01-JAN-90') THEN salary * 1.20
WHEN hire_date <TO_DATE ('01-JAN-92 ') THEN salary * 1.15
WHEN hire_date <TO_DATE ('01-JAN-94 ') THEN salary * 1.10
ELSE salary * 1.05 END "Revised Salary"
FROM employees;
SELECT employee_id, job_id, salary,
DECODE (job_id, 'pu _ cler', salary * 1.05,
'Sh _ cler', salary * 1.10,
'St _ CLERK ', salary * 1.15,
Salary) "Revised Salary"
FROM employees;