DEV Community

Cover image for БД5 Практ (View)
Erlan Akbaraliev
Erlan Akbaraliev

Posted on

БД5 Практ (View)

#sql #database #tutorial

Что нужно сделать?

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

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

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

Скачайте этот файл чтобы создать и добавить данные в таблицы:
nepal.sql

  1. step1

  2. Отрывайте файл nepal.sql:
    step2

  3. Нажмите на кнопку "выполнить" чтобы создать все таблицы и добавить данные:
    step3

Схема таблицы 

+--------------------+-------------------+---------------------------------------------------------------------------------------------------------------+
| Столбец (Оригинал) | Столбец (Русский) | Описание                                                                                                      |
+--------------------+-------------------+---------------------------------------------------------------------------------------------------------------+
| id                 | id                | Уникальный идентификатор каждой записи переписи.                                                              |
+--------------------+-------------------+---------------------------------------------------------------------------------------------------------------+
| district           | okrug             | Название округа (района) для данной записи переписи.                                                          |
+--------------------+-------------------+---------------------------------------------------------------------------------------------------------------+
| locality           | naselennyy_punkt  | Название населенного пункта в пределах округа для данной записи.                                              |
+--------------------+-------------------+---------------------------------------------------------------------------------------------------------------+
| families           | sem'i             | Число семей, связанных с данной записью переписи.                                                             |
+--------------------+-------------------+---------------------------------------------------------------------------------------------------------------+
| households         | domokhozyaystva   | Общее число домохозяйств, связанных с данной записью (в одном домохозяйстве может проживать несколько семей). |
+--------------------+-------------------+---------------------------------------------------------------------------------------------------------------+
| population         | naselenie         | Общая численность населения, связанная с данной записью переписи.                                             |
+--------------------+-------------------+---------------------------------------------------------------------------------------------------------------+
| male               | muzhchiny         | Число людей, связанных с данной записью переписи, которые идентифицированы как мужчины.                       |
+--------------------+-------------------+---------------------------------------------------------------------------------------------------------------+
| female             | zhenshchiny       | Число людей, связанных с данной записью переписи, которые идентифицированы как женщины.                       |
+--------------------+-------------------+---------------------------------------------------------------------------------------------------------------+
Enter fullscreen mode Exit fullscreen mode

demo

SELECT * FROM nepal;

📝 Спецификация Задания

1. Rural (Сельская местность)
Напишите SQL-запрос для создания представления с именем rural. Это представление должно содержать все записи переписи, относящиеся к сельскому муниципалитету (который идентифицируется по включению слова "rural" (сельский) в его название). Убедитесь, что представление содержит все столбцы из таблицы nepal.

2. Total (Общий итог)
Напишите SQL-запрос для создания представления с именем total. Это представление должно содержать суммы по каждому числовому столбцу в таблице nepal для всех округов и населенных пунктов. Убедитесь, что представление содержит каждый из следующих столбцов:

  • families (sem'i): Сумма семей по всем населенным пунктам Непала.
  • households (domokhozyaystva): Сумма домохозяйств по всем населенным пунктам Непала.
  • population (naselenie): Сумма населения по всем населенным пунктам Непала.
  • male (muzhchiny): Сумма людей, идентифицированных как мужчины, по всем населенным пунктам Непала.
  • female (zhenshchiny): Сумма людей, идентифицированных как женщины, по всем населенным пунктам Непала.

3. By District (По округам)
Напишите SQL-запрос для создания представления с именем by_district. Это представление должно содержать суммы по каждому числовому столбцу, сгруппированные по округу (district). Убедитесь, что представление содержит каждый из следующих столбцов:

  • district (okrug): Название округа.
  • families (sem'i): Общее число семей в округе.
  • households (domokhozyaystva): Общее число домохозяйств в округе.
  • population (naselenie): Общая численность населения округа.
  • male (muzhchiny): Общее число людей, идентифицированных как мужчины, в округе.
  • female (zhenshchiny): Общее число людей, идентифицированных как женщины, в округе.

4. Most Populated (Наиболее населенные)
Напишите SQL-запрос для создания представления с именем most_populated. Это представление должно содержать наиболее населенные округа Непала, отсортированные от большего к меньшему по численности населения. Убедитесь, что представление содержит каждый из следующих столбцов:

  • district (okrug): Название округа.
  • families (sem'i): Общее число семей в округе.
  • households (domokhozyaystva): Общее число домохозяйств в округе.
  • population (naselenie): Общая численность населения округа.
  • male (muzhchiny): Общее число людей, идентифицированных как мужчины, в округе.
  • female (zhenshchiny): Общее число людей, идентифицированных как женщины, в округе.

Решения

1.

CREATE VIEW rural AS
SELECT *
FROM nepal
WHERE locality LIKE '%Rural%';
Enter fullscreen mode Exit fullscreen mode

2.

CREATE VIEW total AS
SELECT SUM(families) as "total_families", SUM(households) as "total_households", SUM(population) as "total_population", SUM(male) as "total_males", SUM(female) as "total_females"
FROM nepal;
Enter fullscreen mode Exit fullscreen mode

3.

CREATE VIEW by_district AS
SELECT district, SUM(families) as "total_families", SUM(households) as "total_households", SUM(population) as "total_population", SUM(male) as "total_males", SUM(female) as "total_females"
FROM nepal
GROUP BY district;
Enter fullscreen mode Exit fullscreen mode

4.

CREATE VIEW most_populated AS
SELECT district, SUM(families), SUM(households), SUM(population), SUM(male), SUM(female)
FROM nepal
GROUP BY district
ORDER BY SUM(population) DESC;
Enter fullscreen mode Exit fullscreen mode

Top comments (0)