Академия: Конспект первой недели курса INTRO TO SQL: QUERYING AND MANAGING DATA

В первой части академии я был автором, а сейчас попробую себя в роли куратора. Забегая вперёд скажу - мне понравилось и я обязательно продолжу эту тему :)
Так как я это всё уже давно знаю и умею - то моё мнение об этом курсе будет не с позиции новичка, а с позиции специалиста, который с темой знаком непонаслышке. Разобрав каждую часть урока я дам краткую выдержку по предоставленному материалу и своё мнение о нём.

Имея многолетний опыт работы с SQL - я всё же решил посмотреть курс INTRO TO SQL: QUERYING AND MANAGING DATA и узнать - как же сейчас будущие программисты осваивают это поприще.

Вступление

В первом ролике достаточно лаконично, но ёмко объясняется:

• что такое база данных
• для чего она нужна
• что такое SQL
• каким образом в БД структурируются данные

Чего, на мой взгляд, нехватает - не объяснено что такое нереляционные базы данных.

Форма подачи информации удобна. Если чего-то не понимаете разговорный английский - благодаря иллюстрациям всё легко усваивается, и да - субтитры присутствуют.

Ещё хочу отметить, что в первом же ролике говорится о том, что не стоит всё помещать в одну таблицу, а лучше будет разносить данные по разным таблицам(на то нам и нужна реляционная база данных).

Создание таблиц и добавление в них данных

В данном разделе на примере разбираются 3 типа запросов - create table, insert и select.
Создать таблицу достаточно просто:
CREATE TABLE groceries (id INTEGER PRIMARY KEY, name TEXT, quantity INTEGER );
В данном запросе groceries - это имя таблицы. id, name и quantity - это имена колонок.
Ключевые слова TEXT и INTEGER - это типы данных, которые можно хранить в этой ячейке. То есть в name можно записать строку, а в id и quantity - натуральное число.
PRIMARY KEY - это указание того, что ячейка id является ключевой для данной таблицы. Её значение не может повторяться(является уникальным) в рамках данной таблицы.

В ячейку name можно записать и цифры, но храниться они будут как строковые значения, что играет важную роль при сортировке по данной колонке.

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

Хотел бы отметить, что помимо объяснения синтаксиса преподаются и правила хорошего тона при написании кода(да, sql запросы - это тоже код), что тоже немалый плюс.

Запросы на вставку и выборку тоже достаточно простые, новичок с лёгкостью сможет их повторить.
INSERT INTO groceries VALUES (1, "Bananas", 4);
Таким запросом можно вставить строку в таблицу groceries. Значения в скобках должны идти в той-же последовательности, что и имена столбцов в таблице.
SELECT * FROM groceries;
Это самый простой запрос для осуществления выборки данных из таблицы.
Знак * означает, что нужно извлечь значения всех столбцов. Ключевое слово после FROM - имя таблицы. Лимиты в запросе не указаны, поэтому будут извлечены все строки.

Наверное стоило бы показать, как добавить несколько строк один запросом. Чего я ждал, но не увидел в этой части, так это использования auto increment.

Практика

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

http://i.imgur.com/ezuTTPy.png

Вот это реально круто. Почему в моём детстве такого не было?:)

Данное задание легко можно выполнить просмотрев предыдущий ролик. Так что усвоить просмотренную ранее теорию не составит труда.
Если вы заметили - запросы на вставку я написал не как в предыдущем уроке, я так-же указывал имена столбцов. Валидатор корректно распарсил мой запрос и поставил мне зачёт.

Запросы к таблицам

Для данной темы используется несколько другая таблица. В ней на одну колонку больше.

http://i.imgur.com/GiKLzML.png
В уроке объясняются самые элементарные операторы, которые присутствуют в 99% запросов, а именно условие и сортировка.
Для запроса с условием используется оператор WHERE, после которого описываются сами условия выборки.
Например, чтобы выбрать все строки, в которых значение колонки aisle больше 5 следует написать:
SELECT * FROM groceries WHERE aisle > 5;
Синтаксис после оператора WHERE интуитивно понятен.

Знак больше можно заменить на меньше(<) или, например, на меньше или равно(<=).

Для сортировки используется оператор ORDER BY
SELECT * FROM groceries ORDER BY aisle;
Данные при таком запросе будут отсортированы по колонке aisle от меньшего к большему.
Можно совместить в запросе оба оператора:
SELECT * FROM groceries WHERE aisle > 5 ORDER BY aisle;

От себя хочу добавить, что важно помнить правильную последовательность операторов, иначе запрос не будет работать. Ещё в данном уроке не сказано как сортировать данные от большего к меньшему. Реализуется это ключевым словом DESC, которое ставят после имени столбца указанного в сортировке.
Например:
SELECT * FROM groceries WHERE aisle > 5 ORDER BY aisle ASC; - от меньшего к большему
SELECT * FROM groceries WHERE aisle > 5 ORDER BY aisle DESC; - от большего к меньшему

Практика по выборке данных

В этом уроке нужно извлечь все данные из таблицы movies. Это достаточно просто.
Напишем два запроса - выборка всего и выборка с условием и сортировкой:
SELECT * FROM `movies`;
SELECT * FROM `movies` WHERE release_year >= 2000 ORDER BY release_year;

http://i.imgur.com/cti2Mix.png

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

Чего в этом уроке точно нехватает - так это использование оператора LIMIT. Ведь выборка может быть и очень большой, что может существенно повлиять на время выполнения запроса и дальнейшую обработку полученных данных. Данный оператор ограничивает количество выбранных строк.
Например:
SELECT * FROM `movies` LIMIT 15; - будет выбрано только 15 строк

Агрегация данных

Реляционные базы данных умеют не только делать выборки строк, но и выполнять несложные математические операции прямо на этапе выборки данных. Речь идёт о нахождении минимума, максимума, суммы, среднего значения или количества строк.

• COUNT - вернёт количество строк сформированных SELECT запросом;
• SUM - вернёт сумму всех значений указанного поля;
• AVG - вернёт среднее значение для всех значений указанного поля;
• МАХ - вернёт максимальное выбранное значение указанного поля;
• MIN - вернёт минимальное выбранное значение указанного поля;
  Как это работает:
  Вернёт сумму всех значений колонки quantity:
  SELECT SUM(quantity) FROM groceries;
  Если в таблице есть ещё одна колонка, для каждого значения которой нужно посчитать сумму другой колонки - то нужно сгруппировать данные по этой колонке:
  SELECT SUM(quantity) FROM groceries GROUP BY aisle;
  Такой запрос выдаст сумму всех строк quantity для каждого значения aisle.
  Словами такое не очень понятно, поэтому я поясню на примере такой схемы:
  
  http://i.imgur.com/XcZHWOK.png
  Запрос SELECT aisle,SUM(quantity) FROM groceries GROUP BY aisle
  сгруппирует выборку по колонке aisle и посчитает сумму в колонке quantity.
  Результат будет таким:
  
  http://i.imgur.com/uHy8tvA.png

Что хочу сказать. Новичек будет крайне удивлён, когда попытается исключить из запроса строки, в которых сагрегированное значение(count или avg) больше или меньше определённого значения. Хотя на первый взгляд это элементарно. В данном разделе не только не объяснено как решить данную задачу, но и не сказано что оператор where тут бесполезен. Думаю этот момент будет разобран в одном из следующих уроков.

Практика по агрегации данных

В данной части было задание ещё из самого начала курса, по добавлению данных в таблицу и собственно по агрегации. Посчитать сумму по колонке можно одним простым запросом SELECT SUM(minutes) FROM todo_list;

http://i.imgur.com/snes1TV.png
Задание выполнено, 2100 поинтов получено.

Тут можно потренироваться находить среднее значение, минимальное и т.д.

SQL или SEQUEL

SQL - это Structured Query Language
SEQUEL - Structured English QUEry Language
Изначально данный язык назывался SEQUEL, но в дальнейшем аббревиатура была сокращена до SQL. Так-же в этом подразделе обговаривается произношение данного термина. По-русски это может звучать как:

• эскуэль
• эскуэл
• сиквэль
  Так как запросы пишутся только на английском языке - то слово English было исключено из сокращения. Так-же были опущены U и E в query.

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

Контрольное задание

Данная работа подразумевает, что слушатель уже должен понимать как создать таблицу, поместить в неё данные и потом извлечь их.
Необходимо спроектировать таблицы для интернет-магазина, наполнить их и извлечь данные.
Я выбрал магазин одежды. В моём магазине будет две таблицы: товары и разделы.
Я создал их так:
CREATE TABLE `products` (id INTEGER primary key, name TEXT, price INTEGER, section INTEGER, color TEXT, country TEXT);
CREATE TABLE `sections` (id INTEGER primary key, section_id INTEGER, color_name TEXT);

И наполнил их следующими данными:
INSERT INTO products VALUES (1, "Jacket", 111, 10, "Brown", "Denmark");
INSERT INTO products VALUES (2, "Jacket", 120, 10, "White", "Denmark");
INSERT INTO products VALUES (3, "Jacket", 120, 10, "Black", "China");
INSERT INTO products VALUES (4, "T-shirt", 20, 20, "Colorful", "Russia");
INSERT INTO products VALUES (5, "T-shirt", 20, 20, "Yellow", "Russia");
INSERT INTO products VALUES (6, "T-shirt", 20, 20, "Blue", "Russia");
INSERT INTO products VALUES (7, "T-shirt", 40, 20, "Brown", "France");
INSERT INTO products VALUES (8, "Shirt", 15, 20, "Blue", "France");
INSERT INTO products VALUES (9, "Shirt", 27, 20, "Purple", "France");
INSERT INTO products VALUES (10, "Cap", 10, 30, "Black", "China");
INSERT INTO products VALUES (11, "Cap", 12, 30, "Gray", "China");
INSERT INTO products VALUES (12, "Baseball cap", 83, 30, "White", "Denmark");
INSERT INTO products VALUES (13, "Baseball cap", 50, 30, "White", "China");
INSERT INTO products VALUES (14, "Baseball cap", 45, 30, "Brown", "Russia");
INSERT INTO products VALUES (15, "Baseball cap", 50, 30, "Brown", "Italy");
INSERT INTO products VALUES (16, "Pants", 95, 40, "Beige", "Russia");
INSERT INTO products VALUES (17, "Pants", 120, 40, "Gray", "Italy");
INSERT INTO products VALUES (18, "Pants", 120, 40, "Brown", "Italy");
INSERT INTO products VALUES (19, "Pants", 125, 40, "Blue", "Italy");
INSERT INTO products VALUES (20, "Coat", 100, 10, "Gray", "China");

INSERT INTO sections VALUES (1, 10, "Outerwear");
INSERT INTO sections VALUES (2, 20, "Shirts and T-shirts");
INSERT INTO sections VALUES (3, 30, "Hats");
INSERT INTO sections VALUES (4, 40, "Pants and jeans");

Запросы я сделал такие:

Выборка имени товара и его средней цены, отсортированного по количеству от меньшего к большему.
SELECT name, AVG(price) as avg_price FROM `products` GROUP BY name ORDER BY avg_price;

Выборка всех товаров, не дешевле 100.
SELECT * FROM `products` WHERE price >= 100;

Подсчёт количества каждого вида товара, отсортированного по количеству от большего к меньшему.
SELECT name, COUNT(*) as product_count FROM `products` GROUP BY name ORDER BY product_count DESC;

http://i.imgur.com/ERD9yxR.png

Такой подход в постановке задачи я считаю абсолютно правильным. Поясню. Когда перед вами встанет задача сделать интернет-магазин - вам точно так-же придётся проектировать структуру базы данных, ячейки таблиц и индексы, исходя из вашего задания.

Заключение

Данный курс даёт хорошее понимание того, как начать работать с базой данных, как хранить в ней произвольный массив данных и затем удобно его считывать. Ещё момент - парсер “спотыкается” на кириллице. Поэтому лучше используйте английский алфавит, тогда не будет ложных сообщений об ошибках синтаксиса.

Совет ещё не прошедшим данный курс.

Если вам интересна эта тема - смотрите обязательно. Прослушав данный курс хотел бы отметить чёткую структурированность курса, форму подачи материала и грамотное чередование теории с практикой. Сложность материала - необходимый минимум. Вы точно не сумеете запутаться.

PS

@studentka, мне понравился ваш разбор данного курса. Единственная неточность - “books” - это таблица, а не база данных, в остальном всё отлично:)

Заглавное фото с сайта datareview.info