Day 01 — SQL Bootcamp

Данный проект является продолжением вводного курса по SQL и посвящён работе с множествами и соединениями таблиц. В нём представлены практические упражнения и их решение, направленные на освоение операций объединения, пересечения и разности множеств (UNION, INTERSECT, EXCEPT), а также различных видов соединений (JOIN, NATURAL JOIN).

---

Стек технологий:

• PostgreSQL

---

Описание базы данных:

• Please make sure you have your own database and access to it on your PostgreSQL cluster.
• Please download a script with Database Model here and apply the script to your database (you can use command line with psql or just run it through any IDE, for example DataGrip from JetBrains or pgAdmin from PostgreSQL community).
• All tasks contain a list of Allowed and Denied sections with listed database options, database types, SQL constructions etc. Please have a look at the section before you start.
• Please have a look at the Logical View of our Database Model.

1. pizzeria table (Dictionary Table with available pizzerias)

• field id — primary key
• field name — name of pizzeria
• field rating — average rating of pizzeria (from 0 to 5 points)

2. person table (Dictionary Table with persons who loves pizza)

• field id — primary key
• field name — name of person
• field age — age of person
• field gender — gender of person
• field address — address of person

3. menu table (Dictionary Table with available menu and price for concrete pizza)

• field id — primary key
• field pizzeria_id — foreign key to pizzeria
• field pizza_name — name of pizza in pizzeria
• field price — price of concrete pizza

4. person_visits table (Operational Table with information about visits of pizzeria)

• field id — primary key
• field person_id — foreign key to person
• field pizzeria_id — foreign key to pizzeria
• field visit_date — date (for example 2022-01-01) of person visit

5. person_order table (Operational Table with information about persons orders)

• field id — primary key
• field person_id — foreign key to person
• field menu_id — foreign key to menu
• field order_date — date (for example 2022-01-01) of person order

People's visit and people's order are different entities and don't contain any correlation between data. For example, a customer can be in a restaurant (just looking at the menu) and at the same time place an order in another restaurant by phone or mobile application. Or another case, just be at home and again make a call with order without any visits.

Задания

Задание 00
Turn-in directory	ex00
Files to turn-in	day01_ex00.sql
Allowed
Language	ANSI SQL

Please write a SQL statement that returns the menu identifier and pizza names from the menu table and the person identifier and person name from the person table in one global list (with column names as shown in the example below) ordered by object_id and then by object_name columns.

object_id	object_name
1	Anna
1	cheese pizza
...	...

---

Задание 01
Turn-in directory	ex01
Files to turn-in	day01_ex01.sql
Allowed
Language	ANSI SQL

Please modify an SQL statement from "Exercise 00" by removing the object_id column. Then change the order by object_name for part of the data from the person table and then from the menu table (as shown in an example below). Please save duplicates!

object_name
Andrey
Anna
...
cheese pizza
cheese pizza
...

---

Задание 02
Turn-in directory	ex02
Files to turn-in	day01_ex02.sql
Allowed
Language	ANSI SQL
Denied
SQL Syntax Construction	DISTINCT, GROUP BY, HAVING, any type of JOINs

Write an SQL statement that returns unique pizza names from the menu table and sorts them by the pizza_name column in descending order. Please note the Denied section.

---

Задание 03
Turn-in directory	ex03
Files to turn-in	day01_ex03.sql
Allowed
Language	ANSI SQL
Denied
SQL Syntax Construction	any type of JOINs

Write an SQL statement that returns common rows for attributes order_date, person_id from the person_order table on one side and visit_date, person_id from the person_visits table on the other side (see an example below). In other words, let's find the identifiers of persons who visited and ordered a pizza on the same day. Actually, please add the order by action_date in ascending mode and then by person_id in descending mode.

action_date	person_id
2022-01-01	6
2022-01-01	2
2022-01-01	1
2022-01-03	7
2022-01-04	3
...	...

---

Задание 04
Turn-in directory	ex04
Files to turn-in	day01_ex04.sql
Allowed
Language	ANSI SQL
Denied
SQL Syntax Construction	any type of JOINs

Please write a SQL statement that returns a difference (minus) of person_id column values while saving duplicates between person_order table and person_visits table for order_date and visit_date are for January 7, 2022.

---

Задание 05
Turn-in directory	ex05
Files to turn-in	day01_ex05.sql
Allowed
Language	ANSI SQL

Please write a SQL statement that returns all possible combinations between person and pizzeria tables, and please set the order of the person identifier columns and then the pizzeria identifier columns. Please take a look at the sample result below. Please note that the column names may be different for you.

person.id	person.name	age	gender	address	pizzeria.id	pizzeria.name	rating
1	Anna	16	female	Moscow	1	Pizza Hut	4.6
1	Anna	16	female	Moscow	2	Dominos	4.3
...	...	...	...	...	...	...	...

---

Задание 06
Turn-in directory	ex06
Files to turn-in	day01_ex06.sql
Allowed
Language	ANSI SQL

Let's go back to Exercise #03 and modify our SQL statement to return person names instead of person identifiers and change the order by action_date in ascending mode and then by person_name in descending mode. Take a look at the sample data below.

action_date	person_name
2022-01-01	Irina
2022-01-01	Anna
2022-01-01	Andrey
...	...

---

Задание 07
Turn-in directory	ex07
Files to turn-in	day01_ex07.sql
Allowed
Language	ANSI SQL

Write an SQL statement that returns the order date from the person_order table and the corresponding person name (name and age are formatted as in the data sample below) who made an order from the person table. Add a sort by both columns in ascending order.

order_date	person_information
2022-01-01	Andrey (age:21)
2022-01-01	Andrey (age:21)
2022-01-01	Anna (age:16)
...	...

---

Задание 08
Turn-in directory	ex08
Files to turn-in	day01_ex08.sql
Allowed
Language	ANSI SQL
SQL Syntax Construction	NATURAL JOIN
Denied
SQL Syntax Construction	other type of JOINs

Please rewrite a SQL statement from Exercise #07 by using NATURAL JOIN construction. The result must be the same like for Exercise #07.

---

Задание 09
Turn-in directory	ex09
Files to turn-in	day01_ex09.sql
Allowed
Language	ANSI SQL

Write 2 SQL statements that return a list of pizzerias that have not been visited by people using IN for the first and EXISTS for the second.

---

Задание 10
Turn-in directory	ex10
Files to turn-in	day01_ex10.sql
Allowed
Language	ANSI SQL

Please write an SQL statement that returns a list of the names of the people who ordered pizza from the corresponding pizzeria. The sample result (with named columns) is provided below and yes ... please make the ordering by 3 columns (person_name, pizza_name, pizzeria_name) in ascending mode.

person_name	pizza_name	pizzeria_name
Andrey	cheese pizza	Dominos
Andrey	mushroom pizza	Dominos
Anna	cheese pizza	Pizza Hut
...	...	...

---

Структура проекта

• src/ — директория с решением заданий
• model.sql — модель базы данных

---

Установка и запуск

Клонирование репозитория

git clone https://github.com/School21-projects/SQL_beginner.Day01.git
cd SQL_beginner.Day01

Установка PostgreSQL (Linux)

sudo apt update
sudo apt install postgresql postgresql-contrib

Создание базы данных

# Войти в PostgreSQL под своим пользователем
psql -U postgres
# Создать базу
CREATE DATABASE bootcamp;
\q

Применение модели базы данных

psql -U postgres -d bootcamp -f model.sql

Запуск решений

cd src
# Выполнить запрос (например из упражнения 00)
psql -U postgres -d bootcamp -f ex00/day01_ex00.sql