Курс Анализ данных на языке SQL: урок 2

Ознакомимся со структурой и содержанием базы данных Mediastar, на которой мы будем выполнять все задания. Схема базы данных - на рисунке ниже.

Рассмотрим каждую таблицу базы данных и установим, какие данные содержатся в каждом столбце каждой таблицы.

Таблица Reader (Читатель). Содержит данные о читателях медиа (сайтов), которые дали своё согласие на отслеживание их активности в сети. Данные анонимны. В нашей базе представлены 100 читателей, что представляет некоторую уменьшенную модель данных, используемых в реальных исследованиях. Данные были уменьшены во избежание громоздкости выводимых результатов. В таблице содержатся столбцы:

Reader_ID - идентификационный номер читателя, первичный ключ таблицы;
Age - возраст в годах;
Sex - пол (M - мужской, F - женский);
City_ID - идентификационный номер города проживания, внешний ключ, связывающий эту таблицу с таблицей City.

Таблица City (Город) В таблице содержатся столбцы:.

City_ID - идентификационный номер города, первичный ключ таблицы;
Name - название;
District - область;
Populsize - численность населения в тысячах человек.

Таблица Visit (Визит) Содержит данные о визитах в интернет в течение 4 недель в пределах одного месяца. В таблице содержатся столбцы:.

Visit_ID - идентификационный номер визита, первичный ключ таблицы;
Datetimestart - дата и время начала визита в формате 'ДД-ММ-ГГГГ чч:мм:сс';
Duration - продолжительность в секундах;
Reader_ID - идентификационный номер читателя, внешний ключ, связывающий эту таблицу с таблицей Reader;
Media_ID - идентификационный номер медиа (сайта), внешний ключ, связывающий эту таблицу с таблицей Media;
Adv_ID - идентификационный номер единицы рекламы, внешний ключ, связывающий эту таблицу с таблицей Advertisment.

Таблица Media (Медиа) Содержит данные о сайтах, на которые заходили читатели в течение 4 недель в пределах одного месяца. В таблице содержатся столбцы:.

Media_ID - идентификационный номер медиа, первичный ключ таблицы;
Name - название, например, "Всегда на колёсах";
Mlevel - уровень, допустимые значения: "National" (национальный) "Regional" (региональный);
Mcont_ID - идентификационный типа содержания (контента) медиа, внешний ключ, связывающий эту таблицу с таблицей Mconttype;
Mtheme_ID - идентификационный темы медиа (сайта), внешний ключ, связывающий эту таблицу с таблицей Mediatheme.

Таблица Mconttype (Тип контента медиа) Содержит данные о типах контента сайтов. В таблице содержатся столбцы:.

Mcont_ID - идентификационный номер типа контента медиа, первичный ключ таблицы;
Ctype - тип контента медиа, состоит из латинских букв T (текст), A (аудио), V (видео), буквы располагаются в обозначении типа в порядке убывания удельного веса того или иного подтипа содержания.

Таблица Mediatheme (Тема медиа) Содержит данные о темах сайтов. В таблице содержатся столбцы:.

Mtheme_ID - идентификационный номер темы медиа, первичный ключ таблицы;
Name - название темы, например, "Авто", "Строительство", "Новости".

Таблица Advertisment (Реклама) Содержит данные о единицах рекламы, которая показывалась читателям во время визита. В таблице содержатся столбцы:.

Adv_ID - идентификационный номер единицы рекламы, первичный ключ таблицы;
Acont_ID - идентификационный типа содержания (контента) рекламы, внешний ключ, связывающий эту таблицу с таблицей Advconttype;
Advtheme_ID - идентификационный темы рекламы, внешний ключ, связывающий эту таблицу с таблицей Advtheme.

Таблица Advconttype (Тип контента рекламы) Содержит данные о типах контента сайтов. В таблице содержатся столбцы:.

Acont_ID - идентификационный номер типа контента рекламы, первичный ключ таблицы;
Advtype - тип контента рекламы, состоит из латинских букв T (текст), A (аудио), V (видео), буквы располагаются в обозначении типа в порядке убывания удельного веса того или иного подтипа содержания.

Таблица Advtheme (Тема рекламы) Содержит данные о темах рекламы. В таблице содержатся столбцы:.

Advtheme_ID - идентификационный номер темы рекламы, первичный ключ таблицы;
Name - название темы, например, "Авто", "Строительство", "Новости".

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

Задание 1. Вывести идентификаторы и названия 15 сайтов (медиа), следующих после 10-го. Остортировать по идентификатору.

Запрос для выполнения этого задания должен быть таким:

USE mediastar; SELECT Media_ID, Name FROM Media ORDER BY Media_ID OFFSET 10 ROWS FETCH NEXT 15 ROWS ONLY

При написании подобных запросов с применением конструкций OFFSET и FETCH следует учитывать, что они работают только с набором строк, упорядоченных при помощи оператора ORDER BY.

В результате выполнения этого запроса будет выведена следующая таблица:

Пришло время запросов, выводящих строки, соответствующие некоторым условиям. Эти условия указываются в секции WHERE.

Задание 2. Вывести идентификаторы и названия сайтов (медиа) только регионального уровня ("Regional").

Запрос для выполнения этого задания должен быть таким:

USE mediastar; SELECT Media_ID, Name FROM Media WHERE Mlevel='Regional'

В результате выполнения этого запроса будет выведена следующая таблица:

Задание 3. Вывести идентификаторы и названия сайтов (медиа), в названии которых содержится подстрока "авто".

Запрос для выполнения этого задания должен быть таким:

USE mediastar; SELECT Media_ID, Name FROM Media WHERE Name LIKE '%авто%'

Обратим внимание на то, что условие, указываемое после предиката LIKE, является регистронезависимым: условию будут соответствовать и строки с подстрокой "Авто", и строки с подстрокой "авто".

В результате выполнения этого запроса будет выведена следующая таблица:

Задания для самостоятельного решения

Ответы после этого урока

Задание 4. Вывести идентификаторы читателей из городов Ленинградской области ("Ленинградская").

Подсказка. Задействовано две таблицы: User и City. Они связаны ключом City_ID (идентификатор города), который для таблицы City является первичным, а для таблицы User - внешним. Использовать подзапрос (вложенный запрос), возвращающий из другой таблицы идентификационный номер города, соответствующий условию. Поскольку вывести требуется не одно, а множество значений, использовать предикат IN.

Задание 5. Вывести названия медиа (сайтов), тема которых - медицина.

Подсказка. Задействовано две таблицы: Media и Mediatheme. Они связаны ключом Mtheme_ID (идентификатор темы), который для таблицы Mediatheme является первичным, а для таблицы Media - внешним. Использовать подзапрос (вложенный запрос), возвращающий из другой таблицы идентификационный номер темы, соответствующей условию. Поскольку требуется вывести сайты, соответствующие одной и только одной теме, не нужно использовать предикат IN: основной запрос и подзапрос нужно соединить знаком равенства.

Задание 6. Вывести идентификаторы читателей, читавших медиа (сайты), в названии которых есть подстрока "жен".

Подсказка. Задействовано три таблицы: Reader, Visit и Media. Использовать два подзапроса (второй вложен в первый). Первый подзапрос возвращает идентификаторы читателей из таблицы Visit. Второй подзапрос возвращает идентификаторы медиа, соответствующие условию.

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

Задание 4. Вывести идентификаторы читателей из городов Ленинградской области ("Ленинградская").

Запрос для выполнения этого задания должен быть таким:

USE mediastar; SELECT Reader_ID FROM Reader WHERE City_ID IN (SELECT City_ID FROM City WHERE District='Ленинградская')

В результате выполнения этого запроса будет выведена следующая таблица:

Задание 5. Вывести названия медиа (сайтов), тема которых - медицина.

Запрос для выполнения этого задания должен быть таким:

USE mediastar; SELECT Name FROM Media WHERE Mtheme_ID = (SELECT Mtheme_ID FROM Mediatheme WHERE Name='Медицина')

В результате выполнения этого запроса будет выведена следующая таблица:

Запрос для выполнения этого задания должен быть таким:

USE mediastar; SELECT Reader_ID FROM Reader WHERE Reader_ID IN (SELECT Reader_ID FROM Visit WHERE Media_ID IN (SELECT Media_ID FROM Media WHERE Name LIKE '%жен%'))

В результате выполнения этого запроса будет выведена следующая таблица:

Ссылка на скачивание курса в виде книги PDF

Скачать курс Анализ данных на языке SQL

Стоимость 60 руб. По ссылке выше Вы перейдёте на страницу оплаты книги доступным для Вас способом. После оплаты книгу можно скачать на той же странице.

Уроки курса содержат ссылки на размещённые на сайте материалы, из которых можно получить элементарные сведения об операторах, предикатах и функциях языка SQL и ознакомиться с более простыми примерами их использования в написании запросов.