# PostgreSQL database hands-on session 1

## Initialize dvdrental demo database (you can also use your own method)

1. Install PostgreSQL from [www.postgresql.org](https://www.postgresql.org/).
2. You might need to add `C:\Program Files\PostgreSQL\13\bin` to the path.
3. Start the server on the command line with `psql -U postgres`. `psql` is located in `C:\Program Files\PostgreSQL\13\bin`
4. Create a new database `CREATE DATABASE dvdrental;` and then exit `exit;`.
5. Download the [demo data set](https://www.postgresqltutorial.com/load-postgresql-sample-database/). Unzip the file so you get access to the .tar file.
6. Execute `pg_restore -U postgres -d dvdrental "C:\path_to_file\dvdrental.tar"` with the correct path. Again, `pg_restore` is located in `C:\Program Files\PostgreSQL\13\bin`.
7. Run `psql -U postgres` again and connect to the dvdrental database with `\connect dvdrental`.

## Exercises

### Remarks before doing the exercises

* SQL is not case sensitive i.e. SELECT = select = sElEcT.
* Instead of using the command line, you can connect to Postgres using pgAdmin. pgAdmin is installed along with Postgres by default. It is good when you are writing longer queries and studying the query processing details.
* Depending on if you use command line or pgAdmin, you need to remember to use semicolons ; in the end of queries.
* After you have executed a query which produces more rows than the database is able to show you, you can exit from this view by simply pushing `q` (also `ctrl+c` seems to be working).
* While doing the exercises it is necessary to have the [dvdrental database schema](https://www.postgresqltutorial.com/postgresql-sample-database/) open.
* You can see [more examples and filtering operators](https://www.postgresqltutorial.com/postgresql-where/) for WHERE clause.
* This might be useful when running the database first time on Linux: https://gist.github.com/mokxter/da25272b5276f7e5cc9130fbb9cb04e5

**Remember to write down your answers for the first exercise set.**

1. **Querying Data**
1. Query all the actors so that the result includes the first name and last name. Use suitable column aliases for the first name column and the last name column.
2. Query films with title, release year as year, and length. Order the result by the length of the films. Limit the result to 10 first results.
2. **Filtering Data**
1. Find the customer with id 400 and return the person’s `customer_id` with alias ”id”, `first_name` with alias ”first name”, `last_name` with alias ”surname” and email.
2. Query those films with a description including the word ”*Database*”. Return title, rating, and description. (The demo in the beginning presentation did not include any examples of this kind of query so you might need to study the examples behind the proposed link.)
3. **Joining Multiple Tables**
1. Query first name, last name, and address for each customer. Try the same query with different joins and think about the reason behind the results. Pay attention to the size of the result. Does this happen with messy real-world data sets?
2. Query films so that you get the title for each film and all the actors in the film. The actor information can contain the first and last name of the actors. Order the result by film title so that you can easily read actors for each film.
4. **Understanding PostgreSQL Constraints**
1. Next, we make the dvdrental database a little bit more modern.

Create a new `streaming_service` table with attributes `service_id` and `company_name`. Create the first table so that `service_id` is the primary key of the table.
2. Create table `film_service` that has attributes `film_id` and `service_id`. Ensure that `film_id` cannot be `null`. Create this table so that `film_id` is the foreign key pointing to `film_id` which is the primary key of `film` table and `service_id` is the foreign key pointing to the primary key of `streaming_service` table.

You can insert some demo data into these tables with the following code:
`INSERT INTO streaming_service(service_id, company_name) VALUES(1,'Company 1'), (2,'Company 2');`

`INSERT INTO film_service(film_id, service_id) VALUES(76, 1), (77, 1), (83, 2), (84, 2);`

The idea is that streaming service company is streaming some films that are in the dvdrental database.

## Link to the slides [[Slides link]](https://drive.google.com/file/d/1OJe8FWpN2aAoixaee0Ni7ZQVG0idCw73/view?usp=drive_link)

You can see the presentation and the demonstrative examples in the slides.