Образовательный проект «SnakeProject» Михаила Козлова

⇒ ORACLE SQL \ JAVA ⇐

CISCO

Voice(Asterisk\Cisco)

Microsoft

Powershell

Python

SQL\T-SQL

FreeBSD and Nix

1С

Общая

WEB Разработка

Мото

Стрельба, пневматика, оружие

Саморазвитие и психология

2урок по oracle sql, группировки, JOIN и подзапросы

Продолжаем знакомство с возможностями запросов в ORACLE, это второй урок.

Еще немного о работе с датами:
--Выбрать месяц из даты
SELECT EXTRACT(MONTH FROM HIRE_DATE) FROM HR.EMPLOYEES; 
 
--Выбрать день из даты
SELECT EXTRACT(day FROM HIRE_DATE) FROM HR.EMPLOYEES;
 
--Выбрать год из даты
SELECT EXTRACT(YEAR FROM HIRE_DATE) FROM HR.EMPLOYEES;

--Преобразование к строке, тут 9 - любая цифра, в таком формате мы выводим строку из числа
SELECT TO_CHAR(54245.4567, '99,999.9999') FROM DUAL;
--Пример на римский лад
SELECT TO_CHAR(54, 'RM') FROM DUAL;
--Пример со знаком доллара
SELECT TO_CHAR(54, '$99') FROM DUAL;

--Работа с Null
--Функция NVL (expr1, expr2) возвращает значение expr1, в случае если expr1 is null, то функция NVL возвращает значение expr2.
SELECT first_name, COMMISSION_PCT, NVL(COMMISSION_PCT, 0) FROM hr.employees;

--Функция NVL2 (expr1, expr2, expr3)
--expr1 – выражение
--expr2 – если expr1 “is not null” будет возвращено expr2
--expr3 – если expr1 “is null” будет возвращено expr3
SELECT first_name, COMMISSION_PCT, NVL2(COMMISSION_PCT, 0, 13) FROM hr.employees;

--Если передать в COALESCE три аргумента, то будет возвращен первый, если он не NULL, иначе второй, если он не NULL, иначе третий
SELECT first_name, COALESCE(COMMISSION_PCT, MANAGER_ID, 777) FROM hr.employees;

--Конструкция CASE проверяет выражение
SELECT 
  CASE NVL(COMMISSION_PCT, 0) 
  WHEN 0 THEN 13 
  ELSE COMMISSION_PCT
  END
FROM hr.employees;

--Конструкция DECODE проверяет выражение
SELECT 
  DECODE (NVL(COMMISSION_PCT, 0),
    0, 'No money',
    0.1, 'Low money',
  COMMISSION_PCT)
FROM hr.employees;

--Работа с группировками
--Функции работы со значениями
--MAX() - максимальное значение
--MIN() - минимальное значение
--AVG() - среднее значение
--SUM() - сумма значений
--COUNT() - посчитать кол-во записей
--GROUP BY указывает по какому столбцу проводим группировку значений
--HAVING - (необязательно)условие для сгруппированных данных (аналог where)
SELECT DEPARTMENT_ID, 
  MAX(SALARY) AS mx, 
  MIN(SALARY) AS mn, 
  ROUND(AVG(SALARY), 2) AS ag, 
  SUM(SALARY) AS sm,
  COUNT(SALARY) AS ct
FROM hr.employees
GROUP BY DEPARTMENT_ID
HAVING COUNT(SALARY) >= 3;

--Подзапросы
SELECT first_name FROM hr.employees 
WHERE EMPLOYEE_ID IN (SELECT MANAGER_ID FROM HR.DEPARTMENTS);

--ANY - равно любому в списке
SELECT first_name FROM hr.employees 
WHERE first_name = ANY 
  (SELECT first_name FROM hr.employees WHERE FIRST_NAME LIKE 'M%');

--ALL - равно всем в списке
SELECT first_name FROM hr.employees 
WHERE first_name = ALL 
  (SELECT first_name FROM hr.employees WHERE FIRST_NAME = 'Michael');

--EXISTS\NOT EXISTS - если хотите проверить, возвращает ли подзапрос записи
--Т.е. запрос выполнится, если EXISTS вернет True и наоборот с NOT EXISTS.
SELECT 'Michael in employees' FROM hr.employees 
WHERE EXISTS
  (SELECT first_name FROM hr.employees WHERE FIRST_NAME = 'Michael');

SELECT 'Michael in employees' FROM hr.employees 
WHERE NOT EXISTS
  (SELECT first_name FROM hr.employees WHERE FIRST_NAME = 'Michael');

--Работа с JOIN
--традиционный синтаксис Oracle
SELECT e.FIRST_NAME, d.DEPARTMENT_NAME 
FROM HR.EMPLOYEES e, HR.DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;

--Синтаксис ANSI/ISO
SELECT e.FIRST_NAME, d.DEPARTMENT_NAME 
FROM HR.EMPLOYEES e 
JOIN HR.DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;

--NATURAL JOIN(довольно сомнительна) не требует указания столбца для сопоставления
SELECT e.FIRST_NAME, d.DEPARTMENT_NAME 
FROM HR.EMPLOYEES e 
NATURAL JOIN HR.DEPARTMENTS d;

--LEFT OUTER левое внешнее(все значения из левой)
SELECT e.FIRST_NAME, d.DEPARTMENT_NAME 
FROM HR.EMPLOYEES e 
LEFT OUTER JOIN HR.DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;

--RIGHT OUTER правое внешнее(все значения из правой)
SELECT e.FIRST_NAME, d.DEPARTMENT_NAME 
FROM HR.EMPLOYEES e 
RIGHT OUTER JOIN HR.DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;

--FULL полное(все значения из обоих таблиц в т.ч. не имеющие связь)
SELECT e.FIRST_NAME, d.DEPARTMENT_NAME 
FROM HR.EMPLOYEES e 
FULL OUTER JOIN HR.DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;

--INNER внутреннее(все значения из обоих таблиц имеющие связь)
SELECT e.FIRST_NAME, d.DEPARTMENT_NAME 
FROM HR.EMPLOYEES e 
INNER JOIN HR.DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;

--CROSS перекрестное соединение(все со всеми)
SELECT e.FIRST_NAME, d.DEPARTMENT_NAME 
FROM HR.EMPLOYEES e 
CROSS JOIN HR.DEPARTMENTS d;

--Соединение таблицы с собой
SELECT e1.FIRST_NAME||' работает для '||e2.FIRST_NAME 
FROM HR.EMPLOYEES e1, HR.EMPLOYEES e2
WHERE e1.MANAGER_ID = e2.EMPLOYEE_ID;

На этом - все, видео можно посмотреть на моем канале в YouTube

Комментарии пользователей