Resume: Today you will see how relational model works and how to get needed data based on basic constructions of SQL
- Chapter I
1.1. Preamble - Chapter II
2.1. General Rules - Chapter III
3.1. Rules of the day - Chapter IV
4.1. Exercise 00 - First steps into SQL world - Chapter V
5.1. Exercise 01 - First steps into SQL world - Chapter VI
6.1. Exercise 02 - First steps into SQL world - Chapter VII
7.1. Exercise 03 - First steps into SQL world - Chapter VIII
8.1. Exercise 04 - First steps into SQL world - Chapter IX
9.1. Exercise 05 - First steps into SQL world - Chapter X
10.1. Exercise 06 - First steps into SQL world - Chapter XI
11.1. Exercise 07 - First steps into SQL world - Chapter XII
12.1. Exercise 08 - First steps into SQL world - Chapter XIII
13.1. Exercise 09 - First steps into SQL world
Standards are everywhere, and Relational Databases are also under control as well :-). To be honest between us, more restricted SQL standards were at the beginning of 2000 years. Actually when the “Big Data” pattern was born, Relational Databases had their own way to realize this pattern and therefore standards are more... lightweight right now.
Please take a look at some SQL standards below and try to think about the future of Relational Databases.
 |
 |
 |
 |
 |
 |
-
- Use this page as your only reference. Do not listen to rumors and speculations about how to prepare your solution.
- Make sure you are using the latest version of PostgreSQL.
- It is perfectly fine if you use the IDE to write source code (aka SQL script).
- To be evaluated, your solution must be in your GIT repository.
- Your solutions will be evaluated by your peers.
- You should not leave any files in your directory other than those explicitly specified by the exercise instructions. It is recommended that you modify your `.gitignore' to avoid accidents.
- Got a question? Ask your neighbor to the right. Otherwise, try your neighbor on the left.
- Your reference manual: mates / Internet / Google.
- Read the examples carefully. You may need things not specified in the topic.
- And may the SQL-Force be with you!
- Absolutely anything can be represented in SQL! Let's get started and have fun!
- 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.
- 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)
- 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
- 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
- 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
- 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.
| Exercise 00: First steps into SQL world | |
|---|---|
| Turn-in directory | ex00 |
| Files to turn-in | day00_ex00.sql |
| Allowed | |
| Language | ANSI SQL |
Let’s make our first task. Please make a select statement which returns all person's names and person's ages from the city ‘Kazan’.
| Exercise 01: First steps into SQL world | |
|---|---|
| Turn-in directory | ex01 |
| Files to turn-in | day00_ex01.sql |
| Allowed | |
| Language | ANSI SQL |
Please make a select statement which returns names , ages for all women from the city ‘Kazan’. Yep, and please sort result by name.
| Exercise 02: First steps into SQL world | |
|---|---|
| Turn-in directory | ex02 |
| Files to turn-in | day00_ex02.sql |
| Allowed | |
| Language | ANSI SQL |
Please make 2 syntax different select statements which return a list of pizzerias (pizzeria name and rating) with rating between 3.5 and 5 points (including limit points) and ordered by pizzeria rating.
- the 1st select statement must contain comparison signs (<=, >=);
- the 2nd select statement must contain
BETWEENkeyword.
| Exercise 03: First steps into SQL world | |
|---|---|
| Turn-in directory | ex03 |
| Files to turn-in | day00_ex03.sql |
| Allowed | |
| Language | ANSI SQL |
Please make a select statement that returns the person identifiers (without duplicates) who visited pizzerias in a period from January 6, 2022 to January 9, 2022 (including all days) or visited pizzerias with identifier 2. Also include ordering clause by person identifier in descending mode.
| Exercise 04: First steps into SQL world | |
|---|---|
| Turn-in directory | ex04 |
| Files to turn-in | day00_ex04.sql |
| Allowed | |
| Language | ANSI SQL |
Please make a select statement which returns one calculated field with name ‘person_information’ in one string like described in the next sample:
Anna (age:16,gender:'female',address:'Moscow')
Finally, please add the ordering clause by calculated column in ascending mode. Please pay attention to the quotation marks in your formula!
| Exercise 05: First steps into SQL world | |
|---|---|
| Turn-in directory | ex05 |
| Files to turn-in | day00_ex05.sql |
| Allowed | |
| Language | ANSI SQL |
| Denied | |
| SQL Syntax Construction | IN, any types of JOINs |
Write a select statement that returns the names of people (based on an internal query in the SELECT clause) who placed orders for the menu with identifiers 13, 14, and 18, and the date of the orders should be January 7, 2022. Be careful with "Denied Section" before your work.
Please take a look at the pattern of internal query.
SELECT
(SELECT ... ) AS NAME -- this is an internal query in a main SELECT clause
FROM ...
WHERE ...
| Exercise 06: First steps into SQL world | |
|---|---|
| Turn-in directory | ex06 |
| Files to turn-in | day00_ex06.sql |
| Allowed | |
| Language | ANSI SQL |
| Denied | |
| SQL Syntax Construction | IN, any types of JOINs |
Use the SQL construction from Exercise 05 and add a new calculated column (use column name ‘check_name’) with a check statement a pseudocode for this check is given below) in the SELECT clause.
if (person_name == 'Denis') then return true
else return false
| Exercise 07: First steps into SQL world | |
|---|---|
| Turn-in directory | ex07 |
| Files to turn-in | day00_ex07.sql |
| Allowed | |
| Language | ANSI SQL |
Let's apply data intervals to the person table.
Please make an SQL statement that returns the identifiers of a person, the person's names, and the interval of the person's ages (set a name of a new calculated column as 'interval_info') based on the pseudo code below.
if (age >= 10 and age <= 20) then return 'interval #1'
else if (age > 20 and age < 24) then return 'interval #2'
else return 'interval #3'
And yes... please sort a result by ‘interval_info’ column in ascending mode.
| Exercise 08: First steps into SQL world | |
|---|---|
| Turn-in directory | ex08 |
| Files to turn-in | day00_ex08.sql |
| Allowed | |
| Language | ANSI SQL |
Create an SQL statement that returns all columns from the person_order table with rows whose identifier is an even number. The result must be ordered by the returned identifier.
| Exercise 09: First steps into SQL world | |
|---|---|
| Turn-in directory | ex09 |
| Files to turn-in | day00_ex09.sql |
| Allowed | |
| Language | ANSI SQL |
| Denied | |
| SQL Syntax Construction | any types of JOINs |
Please make a select statement that returns person names and pizzeria names based on the person_visits table with a visit date in a period from January 07 to January 09, 2022 (including all days) (based on an internal query in the `FROM' clause).
Please take a look at the pattern of the final query.
SELECT (...) AS person_name , -- this is an internal query in a main SELECT clause
(...) AS pizzeria_name -- this is an internal query in a main SELECT clause
FROM (SELECT … FROM person_visits WHERE …) AS pv -- this is an internal query in a main FROM clause
ORDER BY ...
Please add a ordering clause by person name in ascending mode and by pizzeria name in descending mode.