Как изучить SQL за ночь или шпаргалка для системного аналитика

2022-05-06 в 21:06, admin, рубрики: sql, Анализ и проектирование систем, базы данных, обучение

Помните, как вы были студентами, и готовились к экзаменам по ночам?

Предлагаю вашему вниманию простую шпаргалку по SQL с теорией и практикой, которой вы сможете воспользоваться в любое время.

Как изучить SQL за ночь или шпаргалка для системного аналитика - 1

Статья пригодится:

ИТ специалисту, которому необходимо быстро освоить минимальный уровень SQL для выполнения рабочих задач,
Системному аналитику, которому требуется освежить знания перед собеседованием или научиться, если раньше не было задач с SQL.

В статье есть:

Минимум теории для задач на работе или собеседовании (прим. операторов в SQL больше, но в своей работе использовала чаще всего эти);
Практические задания, которые можно выполнить у себя на ПК бесплатно, предварительно установив сервер баз данных.

SQL теория

SQL (structured query language) - язык структурированных запросов, который позволяет работать с данными (найти, изменить, удалить или создать) в реляционной базе данных (БД).

Реляционные БД - это базы, где связанная информация, представленная в виде двумерных таблиц (например, Postgres, Mysql, Oracle и др.).

СУБД - система управления БД, программа с помощью которой можно создавать, наполнять и просматривать БД .

ER диаграммы (Entity-Relationship model) - показывает структуру и связи таблиц в БД. Помогает в написании SQL запросов.

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

Наша БД состоит из таблиц:

(прим. показана часть БД с необходимыми таблицами для выполнения практических заданий)

sweets_types - виды сладостей

Столбец

Тип данных

Обязательность

Описание

integer

not null (должно быть значение)

Идентификатор вида сладости.

name

character varying

not null

Вид сладости

manufacturers - производители

Столбец	Тип данных	Обязательность	Описание
id	integer	not null	Идентификатор производителя. PK
name	character varying	not null	Производитель
phone	character varying		Телефон
adress	character varying		Адрес
city	character varying	not null	Населенный пункт
country	character varying	not null	Страна

storehouses - склады

Столбец	Тип данных	Обязательность	Описание
id	integer	not null	Идентификатор склада. PK
name	character varying	not null	Название склада
adress	character varying		Адрес
city	character varying	not null	Населенный пункт
country	character varying	not null	Страна

manufacturers_storehouses - связь производителя со складом

Столбец	Тип данных	Обязательность	Описание
id	integer	not null	Идентификатор связи. PK
storehouses_id	character varying	not null	Идентификатор склада. FK
manufacturers_id	character varying		Идентификатор производителя. FK

sweets - сладости

Столбец	Тип данных	Обязательность	Описание
id	integer	not null	Идентификатор сладости. PK
sweets_types_id	integer		Идентификатор вида. FK
name	character varying	not null	Название сладости
cost	character varying	not null	Стоимость
weight	character varying	not null	Вес
manufacturer_id	integer	not null	Идентификатор производителя. FK
with_sugar	boolean		С сахаром? true - да, false - нет
requires_freezing	boolean		Требует заморозки? true - да, false - нет
production_date	date	not null	Дата изготовления
expiration_date	date	not null	Срок годности

В таблице есть:

Столбцы,
Строки,
Ячейки,
Ограничения (constraint): PK - первичный ключ, FK - вторичный ключ,
Тип данных.

В SQL выделяют основные 4 группы операторов:

DDL (Data Definition Language) – работа со структурой БД,
DML (Data Manipulation Language) – работы с данными таблиц,
DCL (Data Control Language) – работа с правами,
TCL (Transaction Control Language) – работа с транзакциями.

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

А теперь шпаргалки SQL операторов

Работа со структурой БД (DDL)

CREATE. Создание таблицы.

CREATE TABLE public.sweets_types
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 ),
    name character varying NOT NULL,
    PRIMARY KEY (id)

Имя создаваемой таблицы указано в формате:
Имя схемы.Имя таблицы.
Например, схема = public, имя таблицы = sweets_types, тогда имя создаваемой таблицы = public.sweets_types

Конструкция GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 ) позволяет задать генерацию значения поля id от 1 с увеличением +1.

ALTER. Добавление, изменение или удаление столбцов в таблице.

Пример SQL запроса, который добавит столбец name_english с типом данных character varying в таблицу sweets_types

ALTER TABLE IF EXISTS public.sweets_types
    ADD COLUMN name_english character varying;

DROP. Удаление целиком таблицы из БД.

DROP TABLE public.sweets_types;

TRUNCATE. Удаление всех записей из таблицы.

TRUNCATE TABLE public.sweets_type;

Работа с данными таблиц (DML)

INSERT. Добавление строки в таблицу.

INSERT INTO public.sweets_types(name)
VALUES
	('вафли'),
	('конфеты');

Столбец id в таблице sweets_types является PK и сгенерится автоматом при добавлении, поэтому в INSERT добавляем значение столбца name

UPDATE. Обновление данных строки в таблице.

UPDATE public.sweets_types SET name = 'вафли новые'
WHERE id = 1;

DELETE. Удаление строки из таблицы.

DELETE FROM public.sweets_types WHERE name = 'вафли';

SELECT. Просмотр данных из таблицы.

Все виды сладостей (идентификатор и имя)

SELECT * FROM public.sweets_types;

Только имена видов сладостей

SELECT name FROM public.sweets_types;

Дальше приведены операторы условия, агрегатные функции, группировки, сортировки, объединения.

DISTINCT. Возвращает уникальные значения, без повторений.

SELECT DISTINCT name FROM public.sweets

WHERE. Условие фильтрации записей при выборе данных.

Список сладостей, у которых стоимость равна 100

SELECT name FROM public.sweets
WHERE cost = '100';

AND, OR, BETWEEN. Оператор «И», «Или», «Между».

Список сладостей, у которых стоимость равна 100 и вес равен 100

SELECT name FROM public.sweets
WHERE cost = '100' AND weight = '100';

Список сладостей, у которых стоимость от 100 до 150

SELECT name FROM public.sweets
WHERE cost  BETWEEN '100' AND '150';

Примечание: в выборку попадут сладости, у которых стоимость равно 100 и равна 150.

GROUP BY. Группировка столбцов.

SELECT sweets_types_id FROM public.sweets 
GROUP BY sweets_types_id;

HAVING. Используется для фильтрации по условию, когда есть группировка.

Найти вид сладости, у которого есть изделия с весом 300

SELECT sweets_types_id FROM public.sweets 
GROUP BY sweets_types_id, weight
HAVING weight = '300';

ORDER BY ASC, DESC. Сортировка в порядке возрастания (asc) или убывания (desc).

ASC можно не указывать.

SELECT * FROM public.sweets
ORDER BY  name DESC;

COUNT. Количество строк.

Посчитать количество сладостей, у которых вес равен 300

SELECT COUNT(id) FROM public.sweets 
WHERE weight = '300';

SUM, MAX, MIN, AVG. Сумма значений, максимальное, минимальное, среднее значение.

SELECT SUM(id) FROM public.sweets;
SELECT MAX(id) FROM public.sweets;
SELECT MIN(id) FROM public.sweets;
SELECT AVG(id) FROM public.sweets;

LIKE. Поиск заданного значения в столбце по совпадению.

С оператором LIKE используются два подстановочных знака:

% - любое количество символов;
_ - один символ.

Найти список сладостей, которые начинаются на М

SELECT * FROM public.sweets
WHERE name LIKE 'М%';

JOIN или INNER JOIN, LEFT JOIN, RIGHT JOIN. Объединение двух таблиц.

JOIN или INNER JOIN - возвращает записи, имеющие в обеих таблицах
LEFT JOIN - возвращает все записи из левой таблицы и те, которые есть в левой и правой таблице
RIGHT JOIN - возвращает все записи из правой таблицы и те, которые есть в правой таблице

Подробная работа с JOIN описана в статье.

SELECT * FROM public.sweets s
JOIN public.sweets_types st ON s.sweets_types_id = st.id
WHERE st.name = 'шоколад';

SQL практика

Шаг 1. Установить инструменты для работы

Для выполнения практических заданий берем базу данных - Postgres и СУБД - pgAdmin

Скачать Postgres и pgAdmin можно по ссылке. Как поставить показано в видео по ссылке.

После установки у вас создастся локальный сервер на вашем ПК, в котором вы создадите базу данных.

Как настроить подключение к серверу через pgAdmin описано в статье по ссылке, раздел 2. Запуск.

ШАГ 1. Создаем таблицы в БД

В pgAdmin есть форма создания таблиц: Схемы -> public -> Таблицы -> Создать.

Заполните вкладки General, Столбцы.

Посмотреть картинки как это сделать в pgAdmin

Как изучить SQL за ночь или шпаргалка для системного аналитика - 4

Как изучить SQL за ночь или шпаргалка для системного аналитика - 5

Для столбцов id в таблицах устанавливаем ограничения

Как изучить SQL за ночь или шпаргалка для системного аналитика - 6

Скрипт SQL на создание таблиц в БД

CREATE TABLE public.sweets_types
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 ),
    name character varying NOT NULL,
    PRIMARY KEY (id)
);

ALTER TABLE IF EXISTS public.sweets_types
    OWNER to postgres;
	
COMMENT ON TABLE public.sweets_types
    IS 'Виды сладостей';
	
CREATE TABLE public.sweets
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 ),
    sweets_types_id integer,
    name character varying NOT NULL,
    cost character varying NOT NULL,
    weight character varying NOT NULL,
    manufacturer_id integer NOT NULL,
    with_sugar boolean,
    requires_freezing boolean,
    production_date date NOT NULL,
    expiration_date date NOT NULL,
    PRIMARY KEY (id)
);

ALTER TABLE IF EXISTS public.sweets
    OWNER to postgres;

COMMENT ON TABLE public.sweets
    IS 'Записи о сладостях';
	
CREATE TABLE public.manufacturers_storehouses
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 ),
    storehouses_id integer NOT NULL,
    manufacturers_id integer NOT NULL,
    PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.manufacturers_storehouses
    OWNER to postgres;

COMMENT ON TABLE public.manufacturers_storehouses
    IS 'Связь компании производителя и склада';
	
CREATE TABLE public.manufacturers
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 ),
    name character varying NOT NULL,
	phone character varying,
	adress character varying,
	city character varying NOT NULL,
	country character varying NOT NULL,
    PRIMARY KEY (id)
);

ALTER TABLE IF EXISTS public.manufacturers
    OWNER to postgres;	
	
COMMENT ON TABLE public.manufacturers
    IS 'Компании производители';
	
CREATE TABLE public.storehouses
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 ),
    name character varying NOT NULL,
    adress character varying,
    city character varying NOT NULL,
    country character varying NOT NULL,
    PRIMARY KEY (id)
);

ALTER TABLE IF EXISTS public.storehouses
    OWNER to postgres;

COMMENT ON TABLE public.storehouses
    IS 'Склады';

✅ Таблицы созданы!

ШАГ 2. Добавляем записи в БД

Наполняем таблицы данными с помощью SQL скрипта. Используем оператор INSERT INTO ... VALUES

В pgAdmin заходим в таблицу sweets_types и создаем скрипт Insert: Схемы -> public -> Таблицы -> sweets_types (правая кнопка мыши) -> Scripts -> Insert.

Аналогично наполняем данными другие таблицами.

Посмотреть картинки как это сделать в pgAdmin

Как изучить SQL за ночь или шпаргалка для системного аналитика - 7

Скрипт SQL на наполнение данными таблиц

INSERT INTO public.sweets_types(
	name)
	VALUES
	('вафли'),
	('конфеты'),
	('мармелад'),
	('печенье'),
	('шоколад');

INSERT INTO public.storehouses(
	name, adress, city, country)
	VALUES 
	('MSK-1', '109235, г. Москва, Проектируемый проезд 4386, д.8', 'Moscow', 'Russia'),
	('SPB-1', '197375, г. Санкт-Петербург, Суздальское шоссе, д. 26', 'Saint-petersburg', 'Russia'),
	('EKB-1', '620137, г. Екатеринбург, Шефская улица, д. 1А', 'Ekaterinburg', 'Russia'),
	('EKB-2', '620137, г. Екатеринбург, Шефская улица, д. 2А', 'Ekaterinburg', 'Russia');
	
INSERT INTO public.manufacturers(
	name, phone, adress, city, country)
	VALUES 
	('Мишаня', '', '109235, г. Москва, Проектируемый проезд, д.15', 'Moscow', 'Russia'),
	('Собакен', '78125748899', '197375, г. Санкт-Петербург, Суздальское шоссе, д. 75', 'Saint-petersburg', 'Russia'),
	('Мартыха', '74657896525', '620137, г. Екатеринбург, Шефская улица, д. 5А', 'Ekaterinburg', 'Russia');
	
INSERT INTO public.manufacturers_storehouses(
	storehouses_id, manufacturers_id)
	VALUES 
	(1, 1),
	(2, 2),
	(3, 3),
	(1, 2),
	(2, 1);
	
INSERT INTO public.sweets(
	sweets_types_id, name, cost, weight, manufacturer_id, with_sugar, requires_freezing, production_date, expiration_date)
	VALUES 
	(1, 'Мильтик', '100', '200',1, false, false, '2022-05-03', '2022-05-15'),
	(2, 'Микус', '150', '300', 1 , true, true, '2022-04-03', '2022-05-03'),
	(3, 'Миви', '110', '100', 1 , true, false, '2022-03-03', '2022-04-14'),
	(4, 'Ми', '120', '200', 1, false, true, '2022-03-04', '2022-04-04'),
	(5, 'Миса', '145', '570', 1, true, false, '2021-03-03', '2021-12-03'),
	(1, 'Сольтик', '115', '200', 2 , false, false, '2022-05-03', '2022-05-15'),
	(2, 'Сокус', '155', '300', 2 , true, true, '2022-03-03', '2022-05-03'),
	(3, 'Сови', '117', '500', 2 , true, false, '2022-03-03', '2022-04-14'),
	(4, 'Со', '129', '250', 2, false, true, '2022-03-04', '2022-04-04'),
	(5, 'Сор', '148', '500', 2, true, false, '2021-02-03', '2021-12-03'),
	(1, 'Мальтик', '210', '200', 3 , false, false, '2022-05-03', '2022-05-15'),
	(2, 'Макус', '350', '300', 3 , true, true, '2022-01-03', '2022-05-03');

✅ Таблицы наполнены!

ШАГ 3. Отрабатываем поиск данных

Предлагаю вам сначала самим написать SQL запросы, а потом смотреть решение. Так вы научитесь искать данные на практических задачах и закрепите теоретические знания

Как изучить SQL за ночь или шпаргалка для системного аналитика - 8

№1. Какие компании производители есть в базе?

Решение №1

SELECT * FROM manufacturers;

Выгрузите все столбцы из таблицы manufacturers

№2. Найдите все виды сладостей.
Примечание: виды сладостей в таблице не повторяются

Решение №2

SELECT name FROM public.sweets_types;

№3. В каких городах есть склады?

Решение №3

SELECT DISTINCT city FROM storehouses;

№4. Найти сладости с истекшим срок годности.
Подсказка: используйте для условия переменную current_date.

Решение №4

SELECT name FROM public.sweets WHERE expiration_date<current_date;

№5. Найти сладости, у которых стоимость от 200 до 300

Решение №5.

 SELECT * FROM public.sweets WHERE cost>='200' AND cost<'300'; 
 --или 
 SELECT * FROM public.sweets WHERE cost BETWEEN '200' AND '300';

Так как в условии задачи не сказано нужно ли включать в выборку стоимость равную 200 и равную 300, то запрос SELECT * FROM public.sweets WHERE cost BETWEEN '200' AND '300'; даст включая стоимость 200 и 300, а запрос SELECT * FROM public.sweets WHERE cost>='200' AND cost<'300'; не включит стоимость 300.

№6. Найти сладости, у которых название начинается на букву М

Решение №6

SELECT * FROM public.sweets WHERE name LIKE 'М%';

№7. Составить список сладостей, отсортированных от А до Я

Решение №7

SELECT * FROM public.sweets ORDER BY name

№8. Найти количество сладостей по каждому виду. В ответе вывести имя вида и количество

Решение №8

SELECT COUNT (s.id), st.name FROM public.sweets s 
JOIN public.sweets_types st ON s.sweets_types_id = st.id 
GROUP BY st.name;

№9. Найти количество сладостей по каждому виду, у которых истек срок годности.

Решение №9

 SELECT COUNT (s.id), st.name FROM public.sweets s 
 JOIN public.sweets_types st ON s.sweets_types_id = st.id 
 GROUP BY st.name, s.expiration_date 
 HAVING s.expiration_date<current_date;

№10. Найти количество сладостей по каждому виду, у которых название вида начинается на букву п.

Решение №10

 SELECT COUNT (s.id), st.name FROM public.sweets s 
 JOIN public.sweets_types st ON s.sweets_types_id = st.id 
 GROUP BY st.name
 HAVING st.name LIKE 'п%';

№11. В каких городах есть склады со сладостями Мильтик?

Решение №11

SELECT DISTINCT s.city FROM public.storehouses s 
JOIN public.manufacturers_storehouses ms ON s.id = ms.storehouses_id 
JOIN public.sweets sw ON sw.manufacturer_id = ms.manufacturers_id 
WHERE sw.name = 'Мильтик';

№12. Какое максимальное значение идентификатора у сладости?

Решение №12

SELECT MAX(id) FROM public.sweets;

№13. Какое количество сладостей на каждом складе?

Решение №13

 SELECT s.name, COUNT (sw.id) FROM public.storehouses s 
 LEFT JOIN public.manufacturers_storehouses ms ON s.id = ms.storehouses_id 
 LEFT JOIN public.sweets sw ON sw.manufacturer_id = ms.manufacturers_id 
 GROUP BY s.name;

Используем LEFT JOIN, чтобы склады с нулевым количеством сладостей попали в выборку

✅ Обучился несложным запросам SQL!

Конечно за ночь весь SQL не изучить, но разобраться с необходимым минимум для несложных задач или собеседования вполне реально. Главное желание учиться!

А какие каверзные задачки по SQL задавали вам на собеседовании или встречались в вашей работе?

Как изучить SQL за ночь или шпаргалка для системного аналитика - 9

Автор: Анна Овзяк

Источник

Информация

Обсуждаемое

Рекомендуем

Как изучить SQL за ночь или шпаргалка для системного аналитика

SQL теория

SQL практика

Шаг 1. Установить инструменты для работы

ШАГ 1. Создаем таблицы в БД

ШАГ 2. Добавляем записи в БД

ШАГ 3. Отрабатываем поиск данных

Архив

Информация

Обсуждаемое

Рекомендуем

Как изучить SQL за ночь или шпаргалка для системного аналитика

SQL теория

SQL практика

Шаг 1. Установить инструменты для работы

ШАГ 1. Создаем таблицы в БД

ШАГ 2. Добавляем записи в БД

ШАГ 3. Отрабатываем поиск данных

Рекомендованный контент

Новости

Актуальные темы

Архив