examples.sql

/*************************************************************************************************************************************************************************************/

  /*
  * Служебные операторы
  *
  * CREATE TABLE [table] (column type [mod] , [column type [mod]])      - Создание таблицы
  * DESCRIBE (DESC) [table]                                             - Описание таблицы
  * CONSTRAINT                                                          - Ограничение
  * REFERENCES table (column)                                           - Ссылается
  */

  /*
  * Модификаторы столбцов
  *
  * NOT NULL          - не может быть без значения
  * UNSIGNED          - беззнаковое число ( Только положительные числа )
  * AUTO_INCREMENT    - Инкремент ( используется в идентификаторах для обеспечения уникальности )
  */

  /*
  *	Типы данных
  *
  *	CHAR(length)		                    -	Строки константной длинны	( макс 255 символов )
  *	VARCHAR(length)	                    -	Строки переменной длинны	( макс 255 символов )
  *	TINYTEXT			                      -	Маленький текст				    ( макс 255 символов )
  *	TEXT				                        -	Текст						          ( макс 65 535 символов )
  *	MEDIUMTEXT			                    -	Средний текст				      ( макс 16 777 215 символов )
  *	LONGTEXT			                      -	Длинный текст				      ( макс 4 294 967 295 символов )
  * ENUM(A, B)                          - Перечисление и проверочное ограничение в описание типа
  *
  *	TINYINT                             - Мелкое число              ( от -128 до 127 )
  *	SMALLINT                            - Маленькое число           ( от -32 768 до 32 768 )
  *	MEDIUMINT                           - Среднее число             ( от -8 388 608 до 8 388 608 )
  *	INT                                 - Число                     ( от -2 147 483 648 до 2 147 483 648 )
  *	BIGINT                              - Большое число             ( от -9 223 372 036 854 775 808 до 9 223 372 036 854 775 808 )
  *
  * FLOAT(точность масштаб)             - Число с плавающей точкой  ( от -3,402823466E+38 до -1,175494351E-38 и от 1,175494351E-38 до 3,402823466E+38 )
  * DOUBLE(точность масштаб)            - Дробное (более точное)    ( от 1,7976931348623157E+308 до -2,2250738585072014E-308 и от 2,2250738585072014E-308 до 1,7976931348623157E+308 )
  *
  * DATE                                - Дата                      ( Date YYYY-MM-DD )
  * DATETIME                            - Дата и время              ( от 1000-01-01 до 9999-12-31 )
  * TIMESTAMP                           - Временной отпечаток       ( Datetime YYYY-MM-DD HH:MI:SS от 1000-01-01 00:00:00 до 9999-12-31 23:59:59 )
  * YEAR                                - Год                       ( Timestamp YYYY-MM-DD HH:MI:SS от 1970-01-01 00:00:00 до 2037-12-31 23:59:59 )
  * TIME                                - Время                     ( Year YYYY от 1901 до 2155 )
  *
  */

  /*
  * Ограничения
  *
  * CONSTRAINT pk_person PRIMARY KEY (person_id)                                    - Ограничение первичного ключа ( primary key constraint )
  * CONSTRAINT fk_person_id FOREIGN KEY (person_id) REFERENCES person (person_id)   - Ограничения внешнего ключа ( foreign key constraint )
  */

/*************************************************************************************************************************************************************************************/


/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************                                                     *******************/
/*********************                                                     *******************/
/*********************        2. СОЗДАНИЕ И ЗАПОЛНЕНИЕ БАЗЫ ДАННЫХ         *******************/
/*********************                                                     *******************/
/*********************                                                     *******************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/



/* Создание нового пользователя */

  grant all privileges on *.* to 'username'@'localhost' identified by 'password';


/* Выбро текущего времени */

  SELECT now();



/* Создание таблицы person (человек) */

  CREATE TABLE person
    (
      person_id    SMALLINT UNSIGNED,
      fname        VARCHAR(20),
      lname        VARCHAR(20),
      gender       ENUM('M', 'F'),
      birdh_date   DATE,
      address      VARCHAR(30),
      city         VARCHAR(20),
      state        VARCHAR(20),
      country      VARCHAR(20),
      postal_code  VARCHAR(20),
      CONSTRAINT pk_person PRIMARY KEY (person_id)
    );



/* Создание таблицы favorite_food (любимое блюдо) */

  CREATE TABLE favorite_food
    (
      person_id SMALLINT UNSIGNED,
      food VARCHAR(20),
      CONSTRAINT pk_favorite_food PRIMARY KEY (person_id, food),
      CONSTRAINT fk_person_id FOREIGN KEY (person_id) REFERENCES person (person_id)
    );



/* Добовление автоинкремента полю person_id */

  ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;



/* Для изменения поля, которое является внешним ключём для другой таблицы нужно выключить проврку внешних ключей */

  SET FOREIGN_KEY_CHECKS = 0;
  /* DO WHAT YOU NEED HERE */
  SET FOREIGN_KEY_CHECKS = 1;



/* Добавление строки для Вильяма Тёрнера */

  INSERT INTO person (person_id, fname, lname, gender, birdh_date)
  VALUES (null, 'Whilliam', 'Turner', 'M', '1972-05-27');



/* Выбор значений из таблицы person где person_id = 1*/

  SELECT person_id, fname, lname, birdh_date
  FROM person
  WHERE person_id = 1;



/* Добавление любимых блюд Вильяма Тёрнера */

  INSERT INTO favorite_food (person_id, food)
  VALUES (1, 'pizza');

  INSERT INTO favorite_food (person_id, food)
  VALUES (1, 'cookies');

  INSERT INTO favorite_food (person_id, food)
  VALUES (1, 'nachos');



/* Выбор любимых блюд Вильяма Тёрнера */

  SELECT food
  FROM favorite_food
  WHERE person_id = 1
  ORDER BY food;




/*  Добавление Сьюзен Смит */

  INSERT INTO person (person_id, fname, lname, gender, birdh_date, address, city, state, country, postal_code)
  VALUES (null, 'Susan', 'Smith', 'F', '1975-11-02', '23 Maple St.', 'Arlington', 'VA', 'USA', '20220');




/* Обновление данных для Вильяма Тёрнера */

  UPDATE person
  SET
    address = '1225 Tremont St.',
    city = 'Boston',
    state = 'MA',
    country = 'USA',
    postal_code = '02138'
    WHERE person_id = 1;




/* Удаление Сьюзен */

  DELETE FROM person
  WHERE person_id = 2;



/* Удаление тестовых таблиц person и favorite_food */

  DROP TABLE favorite_food;
  DROP TABLE person;



/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************                                                     *******************/
/*********************                                                     *******************/
/*********************                3. АЗБУКА ЗАПРОСОВ                   *******************/
/*********************                                                     *******************/
/*********************                                                     *******************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/


/*
  Банковская система, которая приводится в примерах книги
|-------------------------------------------------------------------------------------------------
| Таблица           | Описание
|-------------------------------------------------------------------------------------------------
| Account           | Конкретный счет, открытый для конкретного клиента
| Business          | Клиент - юридическое лицо (подтип таблицы Customer)
| Customer          | Физическое или юридическое лицо, известные банку
| Department        | Группа сотрудников банка, реализующая определенную банковскую функцию
| Employee          | Человек, работающий в банке
| Individual        | Клиент - физическое лицо (подтип таблицы Customer)
| Officer           | Человек, которому разрешено вести дела от лица клиента юридического лица
| Product           | Услуга банка, предлагаемая клиентам
| Product_type      | Группа функционально схожих услуг
| Transaction       | Изменение баланса счета
|-------------------------------------------------------------------------------------------------
*/

/*
* Блоки запроса
*
* SELECT      - Определяет столбцы, которые должны быть включены в результирующий набор запроса
* FROM        - Указывает таблицы, из которых должны быть извлечены данные, и то, как эти таблицы должны быть соединены
* WHERE       - Ограничивает число строк в окончательном результирующем наборе
* GROUP BY    - Используется для группировки строк по одинаковым значениям столбцов
* HAVING      - Ограничивает число строк в окончательном результирующем наборе с помощью группировки данных
* ORDER BY    - Сортирует строки окончательного результирующего набора по одному или более столбцам
*/



-- БЛОК SELECT



/* Выражение выборки с демонстрацией использования литерала, выражения и вызова встроенной функции */

  SELECT emp_id, 'ACTIVE', emp_id * 3.14159, UPPER(lname);
  FROM employee;



/* Если требуется только выполнить встроенную функцию или вычислить простое выражение, можно вообще обойтись без блока from */

  SELECT VERSION(), USER(), DATABASE();



/* Псевдонимы столбцов ( указываются через пробел после имён столбцов ) */

  SELECT emp_id,
  'ACTIVE'          status,
  emp_id * 3.14159  empid_x_pi,
  UPPER(lname)      last_name_upper
  FROM employee;



/* Выборка данных без дубликатов */

  SELECT DISTINCT cust_id
  FROM account;



-- БЛОК FROM



/* Выборка из таблицы формируемых подзапросом */

  SELECT e.emp_id, e.fname, e.lname
  FROM (SELECT emp_id, fname, lname, start_date, title
        FROM employee
        ) e;



/* Выборка из представления (view) */

  CREATE VIEW employee_vw AS
  SELECT emp_id, fname, lname,
  YEAR(start_date) start_year
  FROM employee;

  SELECT emp_id, start_year
  FROM employee_vw;



/* Выборка из несколькиз таблиц */

  SELECT employee.emp_id, employee.fname, employee.lname, department.name dept_name
  FROM employee INNER JOIN department ON employee.dept_id = department.dept_id;



/* Определение псевдонимов таблиц */

  SELECT e.emp_id, e.fname, e.lname, d.name dept_name
  FROM employee e INNER JOIN department d
  ON e.dept_id = d.dept_id



-- БЛОК WHERE



/* Извлечение только старших операционистов */

  SELECT emp_id, fname, lname, start_date, title
  FROM employee
  WHERE title = 'Head Teller';



/* Извлечение старших операционистов принятых на работу до 2002 года */

  SELECT emp_id, fname, lname, start_date, title
  FROM employee
  WHERE title = 'Head Teller'
  AND  start_date > '2002-01-01';



/* Извлечение старших операционистов и других сотрудников принятых на работу до 2002 года */

  SELECT emp_id, fname, lname, start_date, title
  FROM employee
  WHERE title = 'Head Teller'
  OR start_date > '2002-01-01';



/* Извлечение старших операционистов принятых позже 2002 года или операционистов принятых позже 2003 года */

  SELECT emp_id, fname, lname, start_date, title
  FROM employee
  WHERE (title = 'Head Teller' AND start_date > '2002-01-01')
  OR (title = 'Teller' AND start_date > '2003-01-01');



-- БЛОК ORDER BY



/* Сортировка по идентификатору сотрудника */

  SELECT open_emp_id, product_cd
  FROM account
  ORDER BY open_emp_id;



/* Сортировка по идентификатору сотрудника и типу счёта */

  SELECT open_emp_id, product_cd
  FROM account
  ORDER BY open_emp_id, product_cd;



/* Извлечение списка всех счетов, отсортированного по доступному остатку начиная с самого большого */

  SELECT account_id, product_cd, open_date, avail_balance
  FROM account
  ORDER BY avail_balance DESC;



/* Сортировка с выражением. Сотировка данных клиентов по последним трём разрядам федерального ID */

  SELECT cust_id, cust_type_cd, city, state, fed_id
  FROM customer
  ORDER BY RIGHT(fed_id, 3);



/* Сортировка по номеру столбца в результатирующем наборе */

  SELECT emp_id, title, start_date, fname, lname
  FROM employee
  ORDER BY 2, 5;



-- Упражнения



/* Извлечь id, имя и фамилию всех банковских сотрудников, сортировка по фамилии а затем по имени */

  SELECT emp_id, fname, lname
  FROM employee
  ORDER BY lname, fname;



/* Извлеките ID счета, ID клиента и доступный остаток всех счетов, имеющих статус 'ACTIVE' (активный) и доступный остаток более 2500 долларов. */

  SELECT account_id, cust_id, avail_balance
  FROM account
  WHERE status = 'ACTIVE'
  AND avail_balance > 2500;



/* Извлечь ID сотрудников, отрывших счета (используйте столбец account.open_emp_id). Результирующий набор должен включать по одной строке для каждого сотрудника. */

  SELECT DISTINCT open_emp_id
  FROM account;



/* заполнить дырки */

  SELECT p.product_cd, a.cust_id, a.avail_balance
  FROM product p INNER JOIN account a
  ON p.product_cd = a.product_cd
  WHERE p.product_type_cd = 'ACCOUNT';



/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************                                                     *******************/
/*********************                                                     *******************/
/*********************                  4. ФИЛЬТРАЦИЯ                      *******************/
/*********************                                                     *******************/
/*********************                                                     *******************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/

/*
* Условие фильтрации это одно, или более выражений соединённых операторами
*
* ВЫРАЖЕНИЯ
*
* - Число
* - Столбец таблицы или представления
* - Строковый литерал
* - Встроенная функция, например CONCAT('Learning', ' ', 'sql');
* - Подзапрос
* - Список выражений, например ('Teller', 'Head Teller', 'OperationsManager')
*
* ОПЕРАТОРЫ
*
* - Операторы сравнения, такие как =, !=, <, >, <>, LIKE, IN и BETWEEN
* - Арифметические операторы, такие как +, 
, * и /
*/

/*
* ВЫРАЖЕНИЯ ПОИСКА
*
* При поиске частичных соответствий строк интерес могут представлять
*
* Строки, начинающиеся/заканчивающиеся определенным символом
* Строки, начинающиеся/заканчивающиеся подстрокой
* Строки, содержащие определенный символ в любом месте строки
* Строки, содержащие подстроку в любом месте строки
* Строки определенного формата, независимо от входящих в них от дельных символов
*
* Символы маски
*
*   _     - Точно один символ
*   %     - Любое число символов (в том числе ни одного)
*
* Примеры выражений поиска
*
*   F%                   - Строки, начинающиеся с «F»
*   %t                   - Строки, заканчивающиеся на «t»
*   %bas%                - Строки, содержащие подстроку «bas»
*   _ _t_                - Строки, состоящие из четырех символов с «t» в третьей позиции
*   _ _ _-_ _-_ _ _ _    - Строки из 11 символов, где четвертый и седьмой символы – дефисы
*/

/*
* NULL - это опеределённое значение, которое применяется в следующих случаях:
*
* Значение неприменимо      - Например, столбец с ID сотрудника для транзакции, которая выполняется с банкоматом.
* Значение еще не известно  - Например, если в момент создания строки клиента федеральный ID неизвестен.
* Значение не определено    - Например, если создается счет для продукта, который еще не был добавлен в БД.
*
*
*
* При работе с null необходимо помнить:
*
* Выражение может быть нулевым (null), но оно никогда не может быть равным нулю.
* Два null никогда не равны друг другу.
* Проверить выражение на значение null можно с помощью оператора is null
*/



/* Скобки и логика. Выбрать операционистов или сотрудников принятых до 2003 года, работающих до сих пор */

  SELECT * FROM employee
  WHERE end_date IS NULL
  AND (title = 'Teller' OR start_date < '2003-01-01');



/* Скобки и логика. Выбрать НЕ операционистов или сотрудников принятых НЕ до 2003 года, работающих до сих пор */

  SELECT * FROM employee
  WHERE end_date IS NULL
  AND NOT (title = 'Teller' OR start_date < '2003-01-01');



/* Условия равенства */

  SELECT pt.name product_type, p.name product
  FROM product p INNER JOIN product_type pt
  ON p.product_type_cd = pt.product_type_cd
  WHERE pt.name = 'Customer Accounts';



/* Удалить счета закрытые в 1999 году */

  DELETE FROM account
  WHERE status = 'CLOSED' AND YEAR(close_date) = 1999;



/* Условия вхождения в диапазон */

  SELECT emp_id, fname, lname start_date
  FROM employee
  WHERE start_date < '2003-01-01'



/* Условия вхождения в диапазон с нижней границей */

  SELECT emp_id, fname, lname, start_date
  FROM employee
  WHERE start_date < '2003-01-01'
  AND start_date >= '2001-01-01';



/* Условия вхождения в диапазон оператор BETWEEN (между) */

  SELECT emp_id, fname, lname, start_date
  FROM employee
  WHERE start_date BETWEEN '2001-01-01' AND '2003-01-01';



/* Условия вхождения в числовой диапазон */

  SELECT account_id, product_cd, cust_id, avail_balance
  FROM account
  WHERE avail_balance BETWEEN 3000 AND 5000;



/* Условия вхождения в строковый диапазон (необходимо знать порядок символов в наборе символов (collation)) */

  SELECT cust_id, fed_id
  FROM customer
  WHERE cust_type_cd = 'I'
  AND fed_id BETWEEN '500-00-0000' AND '999-99-9999';



/* Условия челенства */

  SELECT account_id, product_cd, cust_id, avail_balance
  FROM account
  WHERE product_cd IN ('CHK','SAV','CD','MM');



/* Условия челенства с использованием подзапроса */

  SELECT account_id, product_cd, cust_id, avail_balance
  FROM account
  WHERE product_cd IN (SELECT product_cd FROM product
    WHERE product_type_cd = 'ACCOUNT');



/* Условие отсутствия */

  SELECT account_id, product_cd, cust_id, avail_balance
  FROM account
  WHERE product_cd NOT IN ('CHK','SAV','CD','MM');



/* Условие соответствия ( первая буква фамилии достаётся путём вызова встроенной функции ) */

  SELECT emp_id, fname, lname
  FROM employee
  WHERE LEFT(lname, 1) = 'T';



/* Символы маски */

  SELECT lname
  FROM employee
  WHERE lname LIKE '_a%e%';



/* Выражение поиска по строке маске формата социальной страховки */

  SELECT cust_id, fed_id
  FROM customer
  WHERE fed_id LIKE '___-__-____';



/* Несколько выражений поиска */

  SELECT emp_id, fname, lname
  FROM employee
  WHERE lname LIKE 'G%' OR lname LIKE 'F%';



/* Выражения поиска с использованием регулярных выражений */

  SELECT emp_id, fname, lname
  FROM employee
  WHERE lname REGEXP '^[FG]';



-- Значение NULL



/* Проверка значения на NULL */

  SELECT emp_id, fname, lname, superior_emp_id
  FROM employee
  WHERE superior_emp_id IS NULL;



/* Проверка присутствия значения */

  SELECT emp_id, fname, lname, superior_emp_id
  FROM employee
  WHERE superior_emp_id IS NOT NULL;



/* При работе нужно всегда учитывать что значение может не быть */
/* Выбрать всех сотрудников не подчиняющихся хелен флеминг (С ошибкой) */

  SELECT emp_id, fname, lname, superior_emp_id
  FROM employee
  WHERE superior_emp_id != 6;

/* В этот набор не войдут люди которые никому не подчиняются */
/* Выбрать всех сотрудников не подчиняющихся хелен флеминг (Верный вариант) */

  SELECT emp_id, fname, lname, superior_emp_id
  FROM employee
  WHERE superior_emp_id != 6 OR superior_emp_id IS NULL;



-- Упражнения



/* Создайте запрос, выбирающий все счета, открытые в 2002 году. */

  SELECT *
  FROM account
  WHERE open_date BETWEEN '2002-01-01' AND '2002-12-31';



/*Создайте запрос, выбирающий всех клиентов физических лиц, второй буквой фамилии которых является буква 'a' и есть 'e' в любой позиции после 'a'.*/

  SELECT cust_id, lname, fname
  FROM individual
  WHERE lname LIKE '_a%e%';




/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************                                                     *******************/
/*********************                                                     *******************/
/*********************          5. ЗАПРОСЫ К НЕСКОЛЬКИМ ТАБЛИЦАМ           *******************/
/*********************                                                     *******************/
/*********************                                                     *******************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/



/* Декартово (прямое) произведение */

  SELECT e.fname, e.lname, d.name
  FROM employee e JOIN department d;



/* Внутренние соединения ( Тип не указан но если он не указан субд использует внутреннее соединение, хотя лучше всегда указывать ) */

  SELECT e.fname, e.lname, d.name
  FROM employee e JOIN department d
  ON e.dept_id = d.dept_id;



/* Если имена ключей совпадают можно использовать подблок using */

   SELECT e.fname, e.lname, d.name
   FROM employee e INNER JOIN department d
   USING(dept_id);


/* Еще один пример запроса с соединением двух таблиц */

  SELECT a.account_id, c.fed_id
  FROM account a INNER JOIN customer c
  ON a.cust_id = c.cust_id
  WHERE c.cust_type_cd = 'B';



/* Добавление имени операциониста открывшего счёт ( Соединение трёх таблиц ) */

  SELECT a.account_id, c.fed_id, e.fname, e.lname
  FROM account a

  INNER JOIN customer c
  ON a.cust_id = c.cust_id

  INNER JOIN employee e
  ON a.open_emp_id = e.emp_id

  WHERE c.cust_type_cd = 'B';



/* Если изменить порядок присоединяемых таблиц результат не изменится */

  SELECT a.account_id, c.fed_id, e.fname, e.lname
  FROM account a

  INNER JOIN employee e
  ON a.open_emp_id = e.emp_id

  INNER JOIN customer c
  ON a.cust_id = c.cust_id

  WHERE c.cust_type_cd = 'B';



/* Выбор всех счетов, открытых опытными операционистами, в настоящее время работающими в отделении Woburn */
/* Вся фильтрация происходит засчёт внутреннего объединения таблиц, формируемых подзапросами*/

  SELECT *
  FROM account a

  INNER JOIN (
    SELECT emp_id, assigned_branch_id
    FROM employee
    WHERE start_date <= '2003-01-01'
    AND(title = 'Teller' OR title = 'Head Teller')
  ) e
  ON a.open_emp_id = e.emp_id

  INNER JOIN (
    SELECT branch_id
    FROM branch
    WHERE name = 'Woburn Branch'
  ) b
  ON e.assigned_branch_id = b.branch_id;



/* Повторное использование таблицы. */
/* Извлечь сотрудника открвшего каждый текущий счет, в каком отделении это произошло и к какому отделению приписан в настоящее время сотрудник */

  SELECT a.account_id, e.emp_id, b_a.name open_branch, b_e.name emp_branch
  FROM account a

  INNER JOIN branch b_a
  ON a.open_branch_id = b_a.branch_id

  INNER JOIN employee e
  ON a.open_emp_id = e.emp_id

  INNER JOIN branch b_e
  ON e.assigned_branch_id = b_e.branch_id

  WHERE a.product_cd = 'CHK';



/* Рекурсивное соединение таблицы с самой собой по ( self referencing foreign key ) [ Псевдонимы таблиц обязательны ] */
/* Вывести список сотрудников с указанием их начальников */

  SELECT e.fname, e.lname, e_mgr.fname mgr_fname, e_mgr.lname mgr_lname
  FROM employee e INNER JOIN employee e_mgr
  ON e.superior_emp_id = e_mgr.emp_id;



/* Рекурсивное неэквисоединение, то есть соединение таблицы с сомой собой не по условию равенства  */
/* Составить поры всех сотрудников для шахматного турнира */

  SELECT e1.fname, e1.fname, 'VS' vs, e2.fname, e2.lname
  FROM employee e1 INNER JOIN employee e2
  ON e1.emp_id != e2.emp_id
  WHERE e1.title = 'Teller' AND e2.title = 'Teller';



/* хитрость для избежания обратных пар сотрудников с результатирующем наборе */

  SELECT e1.fname, e1.fname, 'VS' vs, e2.fname, e2.lname
  FROM employee e1 INNER JOIN employee e2
  ON e1.emp_id > e2.emp_id
  WHERE e1.title = 'Teller' AND e2.title = 'Teller';



-- Упражнения по внутренним соединениям



/*  */

  SELECT e.emp_id, e.fname, e.lname, b.name
  FROM employee e INNER JOIN branch b
  ON e.assigned_branch_id = b.branch_id;



/* Напишите запрос, по которому для каждого клиента физического
лица (customer.cust_type_cd = 'I') возвращаются ID счета, федеральный ID
(customer.fed_id) и тип созданного счета (product.name). */

  SELECT a.account_id, c.fed_id, p.name
  FROM account a

  INNER JOIN customer c
  ON a.cust_id = c.cust_id

  INNER JOIN product p
  ON a.product_cd = p.product_cd;



/* Создайте запрос для выбора всех сотрудников, начальник которых приписан к другому отделу. Извлеките ID, имя и фамилию сотрудника. */

  SELECT e1.emp_id, e1.fname, e1.lname
  FROM employee e1 INNER JOIN employee e2
  ON e1.superior_emp_id = e2.emp_id
  WHERE e1.dept_id != e2.dept_id;



/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************                                                     *******************/
/*********************                                                     *******************/
/*********************               6. РАБОТА С МНОЖЕСТВАМИ               *******************/
/*********************                                                     *******************/
/*********************                                                     *******************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/

/*
* Три выида операций по множествами
*
* 1) Объединение  ( UNION )         [ Все данные из двух множеств ]                                                       - ПОДДЕРЖИВАЕТСЯ mysql
* 2) Пересечение  ( INTERSECT )     [ Все пересекающиеся данные из двух множеств  ]                                       - НЕ ПОДДЕРЖИВАЕТСЯ mysql
* 3) Разность     ( EXCEPT )        [ Все данные из первого множества, кроме данных пересекающихся со вторым множеством ] - НЕ ПОДДЕРЖИВАЕТСЯ mysql
*/


/* Операции с множествами осуществляются путем помещения оператора
   работы с множествами (set operator) между двух выражений select,
   как показано ниже: */

  SELECT 1 num, 'abc' str
  UNION
  SELECT 9 num, 'xyz' str;



/* Формирование полного множества данных клиентов из двух таблиц */

  SELECT cust_id, lname name
  FROM individual
  UNION ALL
  SELECT cust_id, name
  FROM business;



/* При использовании оператора полного множества повторные строки не удаляются */

  SELECT cust_id, lname name
  FROM individual
  UNION ALL
  SELECT cust_id, name
  FROM business
  UNION ALL
  SELECT cust_id, name
  FROM business;



/* Ещё один пример дублирования строк при извлечении полного множества */

  SELECT emp_id
  FROM employee
  WHERE assigned_branch_id = 2
    AND (title = 'Teller' OR title = 'Head Teller')
  UNION ALL
  SELECT DISTINCT open_emp_id
  FROM account
  WHERE open_branch_id = 2;



/* Если необходимо исключить дублирующие значения необходимо использовать оператор union */

  SELECT emp_id
  FROM employee
  WHERE assigned_branch_id = 2
    AND (title = 'Teller' OR title = 'Head Teller')
  UNION
  SELECT DISTINCT open_emp_id
  FROM account
  WHERE open_branch_id = 2;



/* Как бы выглядел оператор пересечения, если бы он был в mysql ( В данном примере множества не пересекаются так что empty set ) */

  SELECT emp_id, fname, lname
  FROM employee
  INTERSECT
  SELECT cust_id, fname, lname
  FROM individual;



/* Как бы выглядел оператор пересечения в mysql с пересечением в множествах ( emp_id:10 )*/

  SELECT emp_id
  FROM employee
  WHERE assigned_branch_id = 2
  AND (title = 'Teller' OR title = 'Head Teller')
  INTERSECT
  SELECT DISTINCT open_emp_id
  FROM account
  WHERE open_branch_id = 2;



/* Как бы выглядел оператор разности, если бы он был в mysql */

  SELECT emp_id
  FROM employee
  WHERE assigned_branch_id = 2
  AND (title = 'Teller' OR title = 'Head Teller')
  EXCEPT
  SELECT DISTINCT open_emp_id
  FROM account
  WHERE open_branch_id = 2;



/* Если требуется отсортровать результаты состовного запроса после последнего входящего в него запроса можно добавить блок order by
   В блоке order by указываются имена столбцов из первого запроса составного запроса */

  SELECT emp_id, assigned_branch_id
  FROM employee
  WHERE title = 'Teller'
  UNION
  SELECT open_emp_id, open_branch_id
  FROM account
  WHERE product_cd = 'SAV'
  ORDER BY emp_id;



/* Приоритет операций. Пример состовного запроса из трёх запросов */

  SELECT cust_id
  FROM account
  WHERE product_cd IN ('SAV', 'MM')
  UNION ALL
  SELECT a.cust_id
  FROM account a INNER JOIN branch b
  ON a.open_branch_id = b.branch_id
  WHERE b.name = 'Woburn Branch'
  UNION
  SELECT cust_id
  FROM account
  WHERE avail_balance BETWEEN 500 AND 2500;



-- Упражнения с множествами



/* Имеются множество A = {L M N O P} и множество B = {P Q R S T}. Какие множества будут получены в результате следующих операций: */

A union B - { L M N O P Q R S T }
A union all B - { L M N O P P Q R S T }
A intersect B - { P }
A except B - { L M N O }



/* Напишите составной запрос для выбора имен и фамилий всех клиентов физических лиц, а также имен и фамилий всех сотрудников. */

  SELECT fname, lname
  FROM individual
  UNION
  SELECT fname, lname
  FROM employee



/* Отсортируйте результаты упражнения 6.2 по столбцу lname. */

  SELECT fname, lname
  FROM individual
  UNION
  SELECT fname, lname
  FROM employee
  ORDER BY lname;



/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************                                                     *******************/
/*********************                                                     *******************/
/*********************   7. СОЗДАНИЕ, ПРЕОБРАЗОВАНИЕ И РАБОТА С ДАННЫМИ    *******************/
/*********************                                                     *******************/
/*********************                                                     *******************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/



-- Строковые данные



/* Талица для иллюстрирования работы со строковыми данными */

  CREATE TABLE string_tbl(
    char_fld    CHAR(30),
    vchar_fld   VARCHAR(30),
    text_fld    TEXT
  );



/* Самый простой способ заполнить символьный столбец – заключить строку в кавычки */

  INSERT INTO string_tbl (char_fld, vchar_fld, text_fld)
  VALUES (
    'This is char data',
    'This is varchar data',
    'This is text data'
  );



/* следующее выражение update пытается обновить строкой из 46 символов столбец vchar_fld, для которого задан максимальный размер в 30 символов */
/* В книге написано что строка обрежется и будет варнинг но по делу была ошибка и строка не вставилась ( Новый mysql новые правила )  */

  UPDATE string_tbl
  SET vchar_fld = 'This is a piece of exttemely long varchr data';



/* Экранирование апострофов ещё одним апострофом */

  UPDATE string_tbl
  SET text_fld = 'this does''t work last time, but work now ';



/* При извлечении строки для дальнейшего использования в других программах можно пользоваться встроенной функцией QUOTE,
 Которая возвращает строку с заэкранированными символами */

  SELECT QUOTE(text_fld)
  FROM string_tbl;



/* Использование функций конкатенации и вывода символа по номеру в кодировке */

  SELECT CONCAT('danke sch', CHAR(148), 'N');



/* Возвращение и искодному ( Почемуто в книге это исходное ) состоянию таблицы string_tbl */

  DELETE FROM string_tbl;
  INSERT INTO string_tbl ( char_fld, vchar_fld, text_fld )
  VALUES(
    'This string is 28 characters',
    'This string is 28 characters',
    'This string is 28 characters'
  );



-- Строковые функции, возвращающие числа



/* Функция, возвращающая длинну строки LENGTH() */

  SELECT
    LENGTH(char_fld) char_length,
    LENGTH(vchar_fld) char_length,
    LENGTH(text_fld) char_length
  FROM string_tbl;



/* Функция, возвращающая позицию вхождения подстроки POSITION() [ Нумерация позиции с еденицы (1) ] */

  SELECT POSITION('characters' IN vchar_fld)
  FROM string_tbl;



/* Функция, возвращающая позицию вхождения подстроки начиная поиск с определённой позиции LOCATE() */

  SELECT LOCATE('is', vchar_fld, 5)
  FROM string_tbl;



-- Строковые функции, возвращающие строки



/* Функция конкотенации строк CONCAT() */

  UPDATE string_tbl
  SET text_fld = CONCAT( text_fld, 'this string was added by CONCAT function' );



/* Построение строки из отдельных частей данных с помощью CONCAT() */

  SELECT CONCAT(fname, ' ', lname, ' has been a ', title, ' since ', start_date) emp_narrative
  FROM employee
  WHERE title = 'Teller' OR title = 'Head Teller';



/* Функция добавляющая строку в определённую позицию другой строки INSERT( STR, STRPOS, CUTSTR, INSERTSTR ) */

  SELECT INSERT('goodbye world', 9, 0, 'cruel ') string;



/* Если третий аргумент больше нуля, то замещающая строка замещает указанное количество символов, например: */

  SELECT INSERT('goodbye world', 1, 7, 'hello') string;



/* Функция извлекающая указанное число символов, начиная с заданной позиции. SUBSTING
   В следующем примере из строки извлекается пять символов, начиная с девятой позиции */

  SELECT SUBSTRING('goodbye cruel world', 9, 5);



-- Числовые данные ( Арифметика, точность дробных чисел и обработка чисел со знаком [ чушь ] )



-- Временные данные



/* Создание временных данных */

-- Копирование даты из имеющегося столбца типа date, datetime или time
-- Выполнение встроенной функции, возвращающей значение типа date, datetime или time
-- Создание строкового представления временных данных, которое потом преобразовывается сервером



/* Строковое представление данных */

-- YYYY    Год, включая столетие       от 1000 до 9999
-- MM      Месяц                       от 01 (январь) до 12 (декабрь)
-- DD      День                        от 01 до 31
-- HH      Час                         от 00 до 23
-- HHH     Часы (прошедшие)            от –838 до 838
-- MI      Минута                      от 00 до 59
-- SS      Секунда                     от 00 до 59



/* Чтобы создать строку, которая может быть интерпретирована сервером как тип date, datetime или time, необходимо свести различные компоненты вместе в порядке */

-- Date          YYYY-MM-DD
-- Datetime      YYYY-MM-DD HH:MI:SS
-- Timestamp     YYYY-MM-DD HH:MI:SS
-- Time          HHH:MI:SS



/* Функция принудительно приводящая значение строки к временному значению CAST */

  SELECT CAST('2005-03-27 15:30:00' AS DATETIME);



/* Формирование значений datetime и time */

  SELECT CAST('2005-03-27' AS DATE) date_field,
    CAST('108:17:57' AS TIME) time_field;



/* Все нижеприведённые строки приведутся к дате в mysql */

-- '2005-03-27 15:30:00'
-- '2005/03/27 15:30:00'
-- '2005,03,27,15,30,00'
-- '20050327153000'



/* Функция str_to_date() Приводит строку к временому значению по переданному вторым параметром формату строки */

  UPDATE individual
  SET birth_date = STR_TO_DATE('Match 27, 2005', '%M %d, &Y')
  WHERE cust_id = 9999;



/* Компоненты форматирвоания для функции str_to_date */

-- %M     Название месяца (от January до December)
-- %m     Номер месяца (от 01 до 12)
-- %d     Число (от 01 до 31)
-- %j     День года (от 001 до 366)
-- %W     Дни недели (от Sunday до Saturday)
-- %Y     Год, четырехзначное число
-- %y     Год, двузначное число
-- %H     Час (от 00 до 23)
-- %h     Час (от 01 до 12)
-- %i     Минуты (от 00 до 59)
-- %s     Секунды (от 00 до 59)
-- %f     Микросекунды (от 000000 до 999999)
-- %p     A.M. или P.M.



/* date_add() позволяет добавить любой интервал (т. е. дни, месяцы, года) к заданной дате, чтобы получить другую дату */

  SELECT DATE_ADD(CURRENT_DATE( ), INTERVAL 5 DAYS);



/* Типы интервалов для функции date_add */

-- Second            Количество секунд
-- Minute            Количество минут
-- Hour              Количество часов
-- Day               Количество дней
-- Month             Количество месяцев
-- Year              Количество лет
-- Minute_second     Количества минут и секунд, разделенные двоеточием
-- Hour_second       Количества часов, минут и секунд, разделенные двоеточием
-- Year_month        Количества лет и месяцев, разделенные дефмсом



/* Добавить к значению в таблице время ( сделка была на 3 часа 27 минут и 11 секунд позже ) */

  UPDATE transaction
  SET txn_date = DATE_ADD( txn_date, INTERVAL '3:27:11' HOUR_SECOND)
  WHERE txn_id = 9999;



/* Сотрудник моложе заявленного возраста на 9 лет и 11 месяцев */

  UPDATE employee
  SET birth_date = DATE_ADD(birth_date, INTERVAL '9-11' YEAR_MONTH)
  WHERE emp_id = 4789



/* Функция, возвращающая последний день месяца last_date() */

  SELECT LAST_DAY('2005-03-25');



/* функция, возвращающая дату, преобразует значение типа datetime из одного временного пояса в другой */

  SELECT CURRENT_TIMESTAMP() current_est,
    CONVERT_TZ(CURRENT_TIMESTAMP(), 'US/Eastern', 'UTS') current_uts;



/* Функция, возвращающая название дня */

  SELECT DAYNAME('2005-03-22');



/* extract() использует те же типы интервалов, что и функция date_add() */

  SELECT EXTRACT(YEAR FROM '2005-03-22 22:19:05');



/* datediff возвращает колличество полных дней между двумя датами */

  SELECT DATEDIFF('2005-09-05', '2005-06-22');



/* Если переставить аргументы, поместив первой более раннюю дату, datediff() вернет отрицательное число */

  SELECT DATEDIFF('2005-06-22', '2005-09-05');



/* Пример приобразования строки в число */

  SELECT CAST('1456328' AS SIGNED INTEGER)



/* Преабразует до первого вхождения чимвола, который не приводится к числу */

  SELECT CAST('999ABC111' AS UNSIGNED INTEGER);



-- Упаржнения



/* Напишите запрос, возвращающий с 17-го по 25-й символы строки 'Please find the substring in this string' */

  SELECT SUBSTRING( 'Please find the substring in this string', 17, 9);



/* Напишите запрос, возвращающий только значение месяца текущей даты. */

  SELECT EXTRACT(MONTH FROM CURRENT_TIMESTAMP());



/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************                                                     *******************/
/*********************                                                     *******************/
/*********************             8. ГРУППИРОВКА И АГРЕГАТЫ               *******************/
/*********************                                                     *******************/
/*********************                                                     *******************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/



/* Чтобы узнать сколько счетов открыл каждый операционист можно было бы выбрать все данные и посчитать в ручную */

  SELECT open_emp_id
  FROM account;



/* Но я забыл куда я положил свои счёты и на дворе 2018 год так что лучше сгруппировать данные по id сотрудника */

  SELECT open_emp_id
  FROM account
  GROUP BY open_emp_id;



/* Таким образом мы узнали какие сотрудники открывали счёт
   но чтобы узнать сколько счётов они открыли нужно воспользоваться агрегатной функцией */

  SELECT open_emp_id, COUNT(*) how_many
  FROM account
  GROUP BY open_emp_id;



/* Блок group by выполняется послевычисления блока where, поэтому условия фильтрации нельзя добавлять в блок where.
   Вот, например, попытка отфильтровать всех сотрудников, открывших меньше пяти счетов [ОШИБКА] */

  SELECT open_emp_id, COUNT(*) how_many
  FROM account
  WHERE COUNT(*) > 4
  GROUP BY open_emp_id, product_cd;



/* Вот пример того же запроса с блоком having: [РАБОТАЕТ] */

  SELECT open_emp_id, COUNT(*) how_many
  FROM account
  GROUP BY open_emp_id
  HAVING COUNT(*) > 4;



/* Агрегатные функции осуществляют определенную операцию над всеми строками группы. Хотя у всех серверов БД есть собственные наборы
специализированных агрегатных функций, большинством из них реализованы следующие общие агрегатные функции: */

-- Max()       - Возвращает максимальное значение из набора.
-- Min()       - Возвращает минимальное значение из набора.
-- Avg()       - Возвращает среднее значение набора.
-- Sum()       - Возвращает сумму значений из набора.
-- Count()     - Возвращает количество значений в наборе.



/* Вот запрос, использующий все обычные агрегатные функции для анализа доступных остатков (available balance) всех текущих счетов: */

  SELECT  MAX(avail_balance) max_balance,
          MIN(avail_balance) min_balance,
          AVG(avail_balance) avg_balance,
          SUM(avail_balance) tot_balance,
          COUNT(*) num_accounts
  FROM account
  WHERE product_cd = 'CHK';



/* Смешивание сгруппированных данны с обычными приводит к ошибке */
-- он хочет применить агрегатные функции к множеству счетов каждого типа, выявленного в таблице
-- account, этот запрос дает сбой, потому что не был явно задан способ группировки данных

  SELECT  product_cd,
          MAX(avail_balance) max_balance,
          MIN(avail_balance) min_balance,
          AVG(avail_balance) avg_balance,
          SUM(avail_balance) tot_balance,
          COUNT(*) num_accounts
  FROM account;



/* Для корректной работы нужно указывать группу данных для агрегации */
-- Если есть блок group by, сервер знает, что сначала надо сгруппировать строки с одинаковым
-- значением в столбце product_cd, а затем применить пять агрегатных функций к каждой из шести групп.

  SELECT  product_cd,
          MAX(avail_balance) max_balance,
          MIN(avail_balance) min_balance,
          AVG(avail_balance) avg_balance,
          SUM(avail_balance) tot_balance,
          COUNT(*) num_accounts
  FROM account
  GROUP BY product_cd;



/* Функцию подсчёта можно использовать в месте с ключевым словом DISTINCT [ Уникальный ] */

  SELECT COUNT(DISTINCT open_emp_id)
  FROM account;



/* Использование выражений в качестве аргументов агрегатной функции */
/* значение отложенных вкладов по всем счетам, которое вычисляется путем вычитания доступного остатка из отложенного остатка */

  SELECT MAX(pending_balance - avail_balance) max_uncleared
  FROM account;



-- Проверка на null как стиль жизни, как отче наш



/* Таблица для иллюстрирования падения великих империй из за отсутствия проверки на null */

  CREATE TABLE number_tbl ( val SMALLINT );
  INSERT INTO number_tbl VALUES (1);
  INSERT INTO number_tbl VALUES (3);
  INSERT INTO number_tbl VALUES (5);



/* Применим 5 агрегатных функций для этой талицы */

  SELECT  COUNT(*) num_rows,
          COUNT(val) num_vals,
          SUM(val) total,
          MAX(val) max_val,
          AVG(val) avg_val
  FROM number_tbl;



/* Теперь на сцену выходит null При выполнении прошлого запроса всё нормально только записей больше на одну */

  INSERT INTO number_tbl VALUES (NULL);



/* Группировка по одному столбцу ( найти общие остатки (total balance) для всех типов счетов нужно провести группировку по одному столбцу account.product_cd ) */

  SELECT product_cd, SUM(avail_balance) prod_balance
  FROM account
  GROUP BY product_cd;



/* Группировка по нескольким столбцам ( каков общий остаток для всех текущих счетов, открытых в отделении Woburn? ) */

  SELECT product_cd, open_branch_id, SUM(avail_balance) tot_balance
  FROM account
  GROUP BY product_cd, open_branch_id;



/* Группировка по выражению ( запрос, который группирует сотрудников по году начала их работы в банке ) */

  SELECT EXTRACT(YEAR FROM start_date) year,
  COUNT(*) how_many
  FROM employee
  GROUP BY EXTRACT(YEAR FROM start_date);



/* Формирование обобщений */
/* допустим, что кроме общих остатков для каждого сочетания тип счетов/отделение требуется получить и общие остатки по каждому отдельному типу счетов */

  SELECT product_cd, open_branch_id,
  SUM(avail_balance) tot_balance
  FROM account
  GROUP BY product_cd, open_branch_id WITH ROLLUP;



/* Условия групповой фильтрации ( Блок having ) */

  SELECT product_cd, SUM(avail_balance) prod_balance
  FROM account
  WHERE status = 'ACTIVE'
  GROUP BY product_cd
  HAVING SUM(avail_balance) >= 10000;


/* В блоке having можно использовать агрегатные функции В отличии от блока where */

  SELECT product_cd, SUM(avail_balance) prod_balance
  FROM account
  WHERE status = 'ACTIVE'
  GROUP BY product_cd
  HAVING MIN(avail_balance) >= 1000
  AND MAX(avail_balance) <= 10000;



-- Упражнения



/* Создайте запрос для подсчета числа строк в таблице account. */

  SELECT COUNT(*) FROM account;



/* Измените свой запрос из упражнения 8.1 для подсчета числа счетов,
   имеющихся у каждого клиента. Для каждого клиента выведите ID клиента и количество счетов.*/

  SELECT cust_id, COUNT(*) FROM account
  GROUP BY cust_id;



/* Измените запрос из упражнения 8.2 так, чтобы в результирующий набор были включены только клиенты, имеющие не менее двух счетов. */

  SELECT cust_id, COUNT(*) FROM account
  GROUP BY cust_id
  HAVING COUNT(*) > 2;



/* Найдите общий доступный остаток по типу счетов и отделению, где
   на каждый тип и отделение приходится более одного счета.
   Результаты должны быть упорядочены по общему остатку (от наибольшего к наименьшему) */

  SELECT  SUM(avail_balance), product_cd, open_branch_id
  FROM account
  GROUP BY product_cd, open_branch_id
  HAVING COUNT(product_cd) > 1 AND COUNT(open_branch_id) > 1
  ORDER BY SUM(avail_balance);



/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************                                                     *******************/
/*********************                                                     *******************/
/*********************                   9. ПОДЗАПРОСЫ                     *******************/
/*********************                                                     *******************/
/*********************                                                     *******************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/



/* Простой пример выражения с подзапросом */
/* В случае если подзапрос возвращает одно значение его можно использовать в условии равенства */

  SELECT account_id, product_cd, cust_id, avail_balance
  FROM account
  WHERE account_id = (SELECT MAX(account_id) FROM account);



/* Применение скалярного подзапроса в условии неравенства */

  SELECT account_id, product_cd, cust_id, avail_balance
  FROM account
  WHERE open_emp_id <> (
    SELECT e.emp_id
    FROM employee e INNER JOIN branch b
    ON e.assigned_branch_id = b.branch_id
    WHERE e.title = 'Head Teller' AND b.city = 'Woburn'
  );



/* Если подзапрос, используемый в условии равенства будет возвращать более одной строки, генерируется ошибка */

  SELECT account_id, product_cd, cust_id, avail_balance
  FROM account
  WHERE open_emp_id <> (
    SELECT e.emp_id
    FROM employee e INNER JOIN branch b
    ON e.assigned_branch_id = b.branch_id
    WHERE e.title = 'Teller' AND b.city = 'Woburn'
  );



/* В случае если необходимо стравнить с набором значений можно использовать оператор IN (NOT IN) */

  SELECT account_id, product_cd, cust_id, avail_balance
  FROM account
  WHERE open_emp_id NOT IN (
    SELECT e.emp_id
    FROM employee e INNER JOIN branch b
    ON e.assigned_branch_id = b.branch_id
    WHERE e.title = 'Teller' AND b.city = 'Woburn'
  );



/* Следующий запрос использует оператор in и подзапрос в правой части условия фильтрации для того, чтобы выявить руководящий состав банка */

  SELECT emp_id, fname, lname, title
  FROM employee
  WHERE emp_id IN (
    SELECT superior_emp_id
    FROM employee
  );


/* Пример использования оператора NOT IN */

  SELECT emp_id, fname, lname, title
  FROM employee
  WHERE emp_id NOT IN (
    SELECT superior_emp_id
    FROM employee
    WHERE superior_emp_id IS NOT NULL
  );



/* Оператор ALL используется вместе с одним из операторов сравнения следующий запрос находит всех сотрудников,
   ID которых не равен ни одному из ID руководителей */

  SELECT emp_id, fname, lname, title
  FROM employee
  WHERE emp_id <> ALL (
    SELECT superior_emp_id
    FROM employee
    WHERE superior_emp_id != NULL
  );



/* Бывают случаи, когда оператор all чуть более естественен. Следующий пример использует all для поиска счетов,
   доступный остаток которых меньше, чем на любом из счетов Фрэнка Такера (Frank Tucker): */

  SELECT account_id, cust_id, product_cd, avail_balance
  FROM account
  WHERE avail_balance < ALL (
    SELECT a.avail_balance
    FROM account a INNER JOIN individual i
    ON a.cust_id = i.cust_id
    WHERE i.fname = 'Frank' AND i.lname = 'Tucker'
  );



/* Tребуется найти все счета, доступный остаток которых больше, чем на любом из счетов Фрэнка Такера ( Оператор ANY, любой ) */

  SELECT account_id, cust_id, product_cd, avail_balance
  FROM account
  WHERE avail_balance > ANY (
    SELECT a.avail_balance
    FROM account a INNER JOIN individual i
    ON a.cust_id = i.cust_id
    WHERE i.fname = 'Frank' AND i.lname = 'Tucker'
  );



/* пример использования нескольких подзапросов, возвращающих один столбец */

  SELECT account_id, product_cd, cust_id
  FROM account
  WHERE open_branch_id = (
    SELECT branch_id
    FROM branch
    WHERE name = 'Woburn Branch'
  )
  AND open_emp_id IN (
    SELECT emp_id
    FROM employee
    WHERE title = 'Teller' OR title = 'Head Teller'
  );



/* В таблице employee есть информация об отделении, в котором числится каждый сотрудник, поэтому те же результаты можно получить путем сравнения
   столбцов account.open_branch_id и account.open_emp_id с единственным подзапросом к таблицам employee и branch. Для этого в условии фильтрации
   следует указать в круглых скобках имена обоих столбцов таблицы account в том же порядке, в каком они возвращаются подзапросом  */

  SELECT account_id, product_cd, cust_id
  FROM account
  WHERE (open_branch_id, open_emp_id) IN (
    SELECT b.branch_id, e.emp_id
    FROM branch b INNER JOIN employee e
    ON b.branch_id = e.assigned_branch_id
    WHERE b.name = 'Woburn Branch'
    AND (e.title = 'Teller' OR e.title = 'Head Teller')
  );



/* следующий запрос использует связанный подзапрос для подсчета количества счетов у каждого клиента. Затем основной запрос
   выбирает тех клиентов, у которых ровно по два счета */

  SELECT c.cust_id, c.cust_type_cd, c.city
  FROM customer c
  WHERE 2 = (
    SELECT COUNT(*)
    FROM account a
    WHERE a.cust_id = c.cust_id
  );



/* Связанный запрос используемый в условии вхождения в диапазон */

  SELECT c.cust_id, c.cust_type_cd, c.city
  FROM customer c
  WHERE (
    SELECT SUM(a.avail_balance)
    FROM account a
    WHERE a.cust_id = c.cust_id
  ) BETWEEN 5000 AND 10000;



/* Оператор exists применяется, если требуется показать, что связь есть, а количество связей при этом не имеет значения.
   Например, следующий запрос находит все счета, для которых транзакция была выполнена в определенный день, без учета количества транзакций */

  SELECT a.account_id, a.product_cd, a.cust_id, a.avail_balance
  FROM account a
  WHERE EXISTS (
    SELECT 1
    FROM transaction t
    WHERE t.account_id = a.account_id
    AND t.txn_date = '2005-01-22'
  );



/* Для поиска подзапросов, не возвращающих строки, можно использовать и оператор not exists */
/* Этот запрос выявляет всех клиентов, ID которых нет в таблице business, – окольный путь для поиска всех клиентов физических лиц. */

  SELECT a.account_id, a.product_cd, a.cust_id
  FROM account a
  WHERE NOT EXISTS(
    SELECT 1
    FROM business b
    WHERE b.cust_id = a.cust_id
  );



/* Изменение и другие манипуляции с данными с помошью связанных запросов */
/* Вот пример связанного подзапроса, с помощью которого изменяется столбец last_activity_date таблицы account: */

  UPDATE account a
  SET a.last_activity_date = (
    SELECT MAX(t.txn_date)
    FROM transaction t
    WHERE t.account_id = a.account_id
  );



/* В предидущем примере обновляются все строки, но в некоторых счётах нет не одной транзакции, это приводит к записи значения NULL в поля таких счётов */
/* Вариант запроса с проверкой  */

  UPDATE account a
  SET a.last_activity_date = (
    SELECT MAX(t.txn_date)
    FROM transaction t
    WHERE t.account_id = a.account_id
  )
  WHERE EXISTS(
    SELECT 1
    FROM transaction t
    WHERE t.account_id = a.account_id
  )



/* Использование связанных запросов в выражениях удаления записей */
/* Выражение, которое удаляет из таблицы department данные, не имеющие дочерних строк в таблице employee */

  DELETE FROM department
  WHERE NOT EXISTS (
    SELECT *
    FROM employee
    WHERE employee.dept_id = department.dept_id
  );



-- В выражениях DELETE нельзя использовать псевдонимы таблиц !!!



/* использование подзапросов в качестве таблиц – один из самых мощных инструментов, доступных при написании запросов. Вот простой пример */

  SELECT d.dept_id, d.name, e_cnt.how_many num_employees
  FROM department d INNER JOIN (
    SELECT dept_id, COUNT(*) how_many
    FROM employee
    GROUP BY dept_id
  ) e_cnt
  ON d.dept_id = e_cnt.dept_id;


/* С помощью подзапросов можно как резюмировать имеющиеся данные, так и формировать данные, которых в БД нет ни в какой форме */
/* На пример нужно разделить всех клиентов на мелкую рыбу средняк и большую рыбу */

  SELECT 'Small fry' name, 0 low_limit, 4999.99 high_limit
  UNION ALL
  SELECT 'Average Joes' name, 5000 low_limit, 9999.99 high_limit
  UNION ALL
  SELECT 'Heavy Hitters' name, 10000 low_limit, 9999999.99 high_limit;



/* Теперь можно использовать этот результатирующий набор в другом запросе  ( В БУДУЮЩЕМ НУЖНО ИЗУЧИТЬ ПОДРОБНЕЕ ) */

  SELECT groups.name, COUNT(*) num_customers
  FROM (
    SELECT SUM(a.avail_balance) cust_balance
    FROM account a INNER JOIN product p
    ON a.product_cd = p.product_cd
    WHERE p.product_type_cd = 'ACCOUNT'
    GROUP BY a.cust_id
    ) cust_rollup

    INNER JOIN (
      SELECT 'Small Fry' name, 0 low_limit, 4999.99 high_limit
      UNION ALL
      SELECT 'Average Joes' name, 5000 low_limit, 9999.99 high_limit
      UNION ALL
      SELECT 'Heavy Hitters' name, 10000 low_limit, 9999999.99 high_limit
    ) groups

    ON cust_rollup.cust_balance
    BETWEEN groups.low_limit AND groups.high_limit
  GROUP BY groups.name



/* Подзапросы орентированные на задачи */

  SELECT p.name product, b.name branch, CONCAT(e.fname, ' ', e.lname) name, SUM(a.avail_balance) tot_depostis
  FROM account a

  INNER JOIN employee e
  ON a.open_emp_id = e.emp_id

  INNER JOIN branch b
  ON a.open_branch_id = b.branch_id

  INNER JOIN product p
  ON a.product_cd = p.product_cd

  WHERE p.product_type_cd = 'ACCOUNT'
  GROUP BY p.name, b.name, e.fname, e.lname;



/* задачу по формированию групп можно было бы выделить в подзапрос, а затем для получения нужного
   результата соединить остальные три таблицы с таблицей, сгенерированной подзапросом.
   ( в запросе формируются группы с айдишниками вместо имён отдлов, сотрудников ) */

  SELECT product_cd, open_branch_id branch_id, open_emp_id emp_id,
    SUM(avail_balance) tot_deposits
  FROM account
  GROUP BY product_cd, open_branch_id, open_emp_id;



/* Финальный запрос с группировкой данных в подзапросе */

  SELECT p.name product, b.name branch, CONCAT(e.fname, ' ', e.lname) name, account_groups.tot_deposits
  FROM (
    SELECT product_cd, open_branch_id branch_id, open_emp_id emp_id, SUM(avail_balance) tot_deposits
    FROM account
    GROUP BY product_cd, open_branch_id, open_emp_id
  ) account_groups

  INNER JOIN employee e ON e.emp_id = account_groups.emp_id
  INNER JOIN branch b ON b.branch_id = account_groups.branch_id
  INNER JOIN product p ON p.product_cd = account_groups.product_cd

  WHERE p.product_type_cd = 'ACCOUNT';



/* Подзапросы в условиях фильтрации
   следующий запрос использует блок having для поиска сотрудника, открывшего наибольшее количество счетов: */

  SELECT open_emp_id, COUNT(*) how_many
  FROM account
  GROUP BY open_emp_id
  HAVING COUNT(*) = (
    SELECT MAX(emp_cnt.how_many)
    FROM (
      SELECT COUNT(*) how_many
      FROM account
      GROUP BY open_emp_id
    ) emp_cnt
  );



/* Подзапросы как генераторы прошлый пример, только набор собирается по столбцу  */

  SELECT (
    SELECT p.name FROM product p
    WHERE p.product_cd = a.product_cd
    AND p.product_type_cd = 'ACCOUNT'
  ) product, (
    SELECT b.name FROM branch b
    WHERE b.branch_id = a.open_branch_id
  ) branch, (
    SELECT CONCAT(e.fname, ' ', e.lname)
    FROM employee e
    WHERE e.emp_id = a.open_emp_id
  ) name,
  SUM(a.avail_balance) tot_deposits
  FROM account a
  GROUP BY a.product_cd, a.open_branch_id, a.open_emp_id;



/* Тут прошлый запрос используется в блоке FROM чтобы можно было отсеять резальтатирующий набор на предмет NULL в поле продукт */

  SELECT all_prods.product, all_prods.branch, all_prods.name, all_prods.tot_deposits
  FROM (
      SELECT (
        SELECT p.name FROM product p
        WHERE p.product_cd = a.product_cd
        AND p.product_type_cd = 'ACCOUNT'
      ) product, (
        SELECT b.name FROM branch b
        WHERE b.branch_id = a.open_branch_id
      ) branch, (
        SELECT CONCAT(e.fname, ' ', e.lname)
        FROM employee e
        WHERE e.emp_id = a.open_emp_id
      ) name,
      SUM(a.avail_balance) tot_deposits
      FROM account a
      GROUP BY a.product_cd, a.open_branch_id, a.open_emp_id
  ) all_prods
  WHERE all_prods.product IS NOT NULL;



/* Скалярные запросы в выражении группировки */

  SELECT emp.emp_id, CONCAT(emp.fname, ' ', emp.lname) emp_name,(
      SELECT CONCAT(boss.fname, ' ', boss.lname)
      FROM employee boss
      WHERE boss.emp_id = emp.superior_emp_id
    ) boss_name
    FROM employee emp
    WHERE emp.superior_emp_id IS NOT NULL
    ORDER BY (
      SELECT boss.lname
      FROM employee boss
      WHERE boss.emp_id = emp.superior_emp_id
    ), emp.lname;



/* Скалярные несвязанные запросы в выражениях INSERT
   (в частности ля того чтобы запролнить все поля с внешними ключами к предоставленным данным) */

  INSERT INTO account (
    account_id, product_cd, cust_id, open_date, last_activity_date,
    status, open_branch_id, open_emp_id, avail_balance, pending_balance
  )
  VALUES (
    NULL,
    ( SELECT product_cd FROM product WHERE name = 'savings account' ),
    ( SELECT cust_id FROM customer WHERE fed_id = '555-55-5555' ),
    '2005-01-25', '2005-01-25', 'ACTIVE',
    ( SELECT branch_id FROM branch WHERE name = 'Quincy Branch' ),
    ( SELECT emp_id FROM employee WHERE lname = 'Portman' AND fname = 'Frank' ),
    0,0
  );



-- Упражнения



/* Создайте запрос к таблице account, использующий условие фильтрации с несвязанным подзапросом к
   таблице product для поиска всех кредитных счетов (product.product_type_cd = 'LOAN'). Должны быть
   выбраны ID счета, код счета, ID клиента и доступный остаток. */

  SELECT account_id, product_cd, cust_id, avail_balance
  FROM account
  WHERE product_cd IN (
    SELECT product_cd
    FROM product
    WHERE product_type_cd = 'LOAN'
  );



/* Переработайте запрос из упражнения 9.1, используя связанный подзапрос
   к таблице product для получения того же результата. */

  SELECT account_id, product_cd, cust_id, avail_balance
  FROM account a
  WHERE EXISTS(
    SELECT 1
    FROM product p
    WHERE p.product_type_cd = 'LOAN' AND p.product_cd = a.product_cd
  );



/* Соедините следующий запрос с таблицей employee, чтобы показать уровень квалификации каждого сотрудника:
   Дайте подзапросу псевдоним levels (уровни) и включите ID сотрудника, имя, фамилию и квалификацию (levels.name).
   (Совет: в условии соединения определяйте диапазон, в который попадает столбец employee.start_date, с помощью условия неравенства.) */

  SELECT e.emp_id, e.fname, e.lname, levels.name
  FROM (
    SELECT 'trainee' name, '2004-01-01' start_dt, '2005-12-31' end_dt
    UNION ALL
    SELECT 'worker' name, '2002-01-01' start_dt, '2003-12-31' end_dt
    UNION ALL
    SELECT 'mentor' name, '2000-01-01' start_dt, '2001-12-31' end_dt
  ) levels
  INNER JOIN employee e
  ON e.start_date BETWEEN levels.start_dt AND levels.end_dt;



/* Создайте запрос к таблице employee для получения ID, имени и фамилии сотрудника
   вместе с названиями отдела и отделения, к которым он приписан. Не используйте соединение таблиц. */

  SELECT e.emp_id, e.fname, e.lname, (
    SELECT d.name
    FROM department d
    WHERE d.dept_id = e.dept_id
  ) dep_name, (
    SELECT b.name
    FROM branch b
    WHERE b.branch_id = e.assigned_branch_id
  ) branch_name
  FROM employee e;



/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************                                                     *******************/
/*********************                                                     *******************/
/*********************             10. СОЕДЕНЕНИЯ ПОДРОБНЕЕ                *******************/
/*********************                                                     *******************/
/*********************                                                     *******************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/



/* Если соединить account с business не все аккаунты войдут в результатирующий набор, по тому что не каждый клиент бизнесмен */

  SELECT a.account_id, b.cust_id, b.name
  FROM account a INNER JOIN business b
  ON a.cust_id = b.cust_id;



/* Если необходимо выбрать все аккаунты и если есть название предприятия необходимо использовать внешнее соединение */

  SELECT a.account_id, b.cust_id, b.name
  FROM account a LEFT OUTER JOIN business b
  ON a.cust_id = b.cust_id;



/* Аналогичный запрос с внешним соединением, но с таблицей individual вместо business */

  SELECT a.account_id, a.cust_id, i.fname, i.lname
  FROM account a LEFT OUTER JOIN individual i
  ON a.cust_id = i.cust_id;



/* Левостороннее соединение включает в результатирующий набор все значения из левой таблицы в блоке FROM
   и если ему соответсвуют значения из правой добавляет их. Правосторонее соединение работает обратным образом */

  SELECT c.cust_id, b.name
  FROM customer c RIGHT OUTER JOIN business b
  ON c.cust_id = b.cust_id;



/* ( Соединение с тремя таблицами ) список всех счетов с указанием или имени и фамилии физического лица, или названия фирмы для юридического лица: */

  SELECT a.account_id, a.product_cd, CONCAT(i.fname, ' ', i.lname) person_name, b.name business_name
  FROM account a LEFT OUTER JOIN individual i
  ON a.cust_id = i.cust_id
  LEFT OUTER JOIN business b
  ON a.cust_id = b.cust_id;



/* Чтобы не городить много соединений можно воспользоваться подзапросами */

  SELECT account_ind.account_id, account_ind.product_cd, account_ind.person_name, b.name business_name
  FROM (
    SELECT a.account_id, a.product_cd, a.cust_id, CONCAT(i.fname, ' ', i.lname) person_name
    FROM account a LEFT OUTER JOIN individual i
    ON a.cust_id = i.cust_id
  ) account_ind LEFT OUTER JOIN business b
  ON account_ind.cust_id = b.cust_id;



/* ( Внешнее соединение таблицы с самой собой ) Выбрать всех сотрудников и их начальников если они есть */

  SELECT e.fname, e.lname, e_mgr.fname mgr_fname, e_mgr.lname mgr_lname
  FROM employee e LEFT OUTER JOIN employee e_mgr
  ON e.superior_emp_id = e_mgr.emp_id;



/* Натуральные соединения по идентиным именам столбцов */
/* Поскольку задано естественное соединение, сервер проверил описания таблиц и добавил для этих двух таблиц условие соединения a.cust_id = c.cust_id */

  SELECT a.account_id, a.cust_id, c.cust_type_cd, c.fed_id
  FROM account a NATURAL JOIN customer c;



-- Упражнения



/* Напишите запрос, возвращающий все типы счетов и открытые счета этих типов
   (для соединения с таблицей product используйте столбец product_cd таблицы account).
   Должны быть включены все типы счетов, даже если не был открыт ни один счет определенного типа. */

  SELECT p.product_cd, a.cust_id, a.account_id, a.avail_balance
  FROM product p LEFT OUTER JOIN account a
  ON p.product_cd = a.product_cd;



/* Переформулируйте запрос из упражнения 10.1 и примените другой
   тип внешнего соединения (т. е. если в упражнении 10.1 использовалось левостороннее внешнее соединение,
   используйте правостороннее), так чтобы результаты были, как в упражнении 10.1. */

  SELECT p.product_cd, a.cust_id, a.account_id, a.avail_balance
  FROM account a RIGHT OUTER JOIN product p
  ON p.product_cd = a.product_cd;



/* Проведите внешнее соединение таблицы account с таблицами individual и business (посредством столбца account.cust_id)
   таким образом, чтобы результирующий набор содержал по одной строке для каждого счета. Должны быть включены столбцы
   count.account_id, account.product_cd, individual.fname, individual.lname и business.name. */

  SELECT a.account_id, a.product_cd, i.fname, i.lname, b.name
  FROM account a

  LEFT OUTER JOIN individual i
  ON a.cust_id = i.cust_id

  LEFT OUTER JOIN business b
  ON a.cust_id = b.cust_id;



/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************                                                     *******************/
/*********************                                                     *******************/
/*********************                11. УСЛОВНАЯ ЛОГИКА                  *******************/
/*********************                                                     *******************/
/*********************                                                     *******************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/
/*********************************************************************************************/



/* Выбрать или имя клиента физ лица или имя клиента компании через условную логику */

  SELECT c.cust_id, c.fed_id,
    CASE
      WHEN c.cust_type_cd = 'I'
        THEN CONCAT(i.fname, ' ', i.lname)
      WHEN c.cust_type_cd = 'B'
        THEN b.name
      ELSE 'Unknown'
    END name
  FROM customer c LEFT OUTER JOIN individual i
  ON c.cust_id = i.cust_id
  LEFT OUTER JOIN business b
  ON c.cust_id = b.cust_id;



/* Выражение case с перебором вариантов ( search case expression ) [ синтаксис ] */

--   CASE
--     WHEN C1 THEN E1
--     WHEN C2 THEN E2
--     ...
--     WHEN CN THEN EN
--     ELSE DEFAULT
--   END



/* Выражения case могу возвращать значения любого типа, в том числе подзапросы */

  SELECT c.cust_id, c.fed_id,
    CASE
      WHEN c.cust_type_cd = 'I' THEN
        (SELECT CONCAT(i.fname, ' ', i.lname)
         FROM individual i
         WHERE i.cust_id = c.cust_id)
      WHEN  c.cust_type_cd = 'B' THEN
        (SELECT b.name
         FROM business b
         WHERE b.cust_id = c.cust_id)
      ELSE 'Unknown'
    END name
  FROM customer c;



/* Простые выражения case где сравнение идёт с указанным в ночале значением [ синтаксис ] */

--   CASE V0
--     WHEN V1 THEN E1
--     WHEN V2 THEN E2
--     ...
--     WHEN VN THEN EN
--     [ELSE ED]
--   END



/* Вот пример простого выражения case: */

  CASE customer.cust_type_cd
    WHEN 'I' THEN
      (SELECT CONCAT(i.fname, ' ', i.lname)
      FROM individual I
      WHERE i.cust_id = customer.cust_id)
    WHEN 'B' THEN
      (SELECT b.name
      FROM business b
      WHERE b.cust_id = customer.cust_id)
    ELSE 'Unknown Customer Type'
  END



-- Транзакции и индексы не нуждаются в выполнении примеров





/*********************************************************************************/
/***************************** Книга прочитанна **********************************/
/*********************************************************************************/