Michal Rjaško

• rjasko (zavinac) dcs.fmph.uniba.sk

Databases practicum, winter 2026/2027

Homeworks

• Evaluation of lesson exercises

Lesson on 29th october cancelled

Lesson 6

Window functions

• slides
• Relevant documentation:
  • window functions in PostgreSQL
  • list of window functions
  • window functions in SQLite
  • window functions cannot be used after WHERE directly
• Percentiles
  • PERCENTILE_CONT vs. PERCENTILE_DISC
  • PERCENTILE_CONT examples
  • CUME_DIST function in PostgreSQL
  • CUME_DIST function in SQL Server
  • PERCENT_RANK in SQLite

Tasks

• Create a local database (SQLite/PostgreSQL) or connect to cvika.dcs.fmph.uniba.sk (see previous exercises for instructions).
• Download the files:
  • employee.sql --- SQL commands to create a database of employees
    • wget http://www.dcs.fmph.uniba.sk/~rjasko/db/05/employee.sql
  • queries_window.sql --- list of queries to solve
    • wget http://www.dcs.fmph.uniba.sk/~rjasko/db/05/queries_window.sql
• Solve all the excercises in the queries_window.sql

Lesson 4

Recursion in SQL

• slides
• Relevant documentation:
  • recursion in PostgreSQL
  • recursion in SQLite
  • array functions in PostgreSQL
  • examples of valid and invalid use

Tasks

• Create your local database (SQLite/PostgreSQL) or connect to cvika.dcs.fmph.uniba.sk (see previous lessons).
• Write a recursive query that calculates the value of n! (factorial) for n = 10.
• Write a recursive query that calculates the value of the 30th Fibonacci number.
• Consider the employee database (emp.sql) that we used in the previous exercises. For each manager, find a list of his subordinates and indicate for each subordinate whether he is direct or indirect.
• Create a database of routes between cities (roads.sql) and write a query:
  • wget https://dcs.fmph.uniba.sk/~rjasko/db/06/roads.sql
  1. from where we can get to Rome
  2. from where we can get to Rome after traveling at most 1000 km
  3. A truck driver travels a maximum of 720 km in one day. Create a table of pairs [city, number_of_days, number_of_places], where number_of_places determines the number of places that can be reached from the city of city in number_of_days days.

Agregation in SQL

• Create your local database (SQLite/PostgreSQL) or connect to cvika.dcs.fmph.uniba.sk (see previous lessons).
• Download the files:
  • world.sql; world_sqllite.sql--- SQL commands to create a database of world cities
    • wget https://dcs.fmph.uniba.sk/~rjasko/db/06/world.sql
  • queries_world.sql --- list of queries to solve
    • wget https://dcs.fmph.uniba.sk/~rjasko/db/06/queries_world.sql
• Import the world database:

  psql -f world.sql

• Solve all the excercises.

Lesson 3

Agregation in SQL

• slides
• Relevant documentation:
  • aggregate functions,
  • pattern matching,

Tasks

• Create your local database (SQLite/PostgreSQL) or connect to cvika.dcs.fmph.uniba.sk (see previous lesson).
• Download the file:
  • emp.sql --- SQL commands to create the EMP database
    • wget https://dcs.fmph.uniba.sk/~rjasko/db/03/emp.sql
  • queries_agg.sql --- list of queries to solve; you will edit this file
    • wget https://dcs.fmph.uniba.sk/~rjasko/db/03/queries_agg.sql
• Solve all the excercises.

Lesson 2

SQL, joins & subselects

• slides
• Relevant documentation:
  • SELECT
  • joins
  • NOT EXISTS subqueries
  • psql console

Tasks

• Try to connect to your PostgreSQL database.
• In the command line (PowerShell on Windows): ssh username@cvika.dcs.fmph.uniba.sk (your AIS username)
• Download the exercise files (you can use wget).
  • wget https://dcs.fmph.uniba.sk/~rjasko/db/02/emp.sql
  • wget https://dcs.fmph.uniba.sk/~rjasko/db/02/queries.sql
• Initialize the database: psql -f emp.sql
• Edit the queries.sql file to write your queries: vim queries.sql or nano queries.sql
• Run the queries: psql -f queries.sql
• Open the interactive psql console: psql
• \d shows a list of tables.
• \d <tablename> or \d+ <tablename> gives you details about a particular table. These only work with the interactive psql terminal.
• If you do not want to use a command line editor, try VSCode and connect it to cvicka.dcs.fmph.uniba.sk via SSH.
• Write a couple of queries using PostgreSQL, and then continue writing the queries using a database of your choice (e.g., SQLite – see the previous lecture).
• The files:
  • emp.sql — SQL commands to create the EMP database
  • queries.sql — list of queries to write, edit this file

Lesson 1

Intro, SQL

• slides
• Relevant documentation:
  • SELECT
  • comments in SQL
  • LIKE operator (documentation: W3C, SQLite, PostgreSQL, SQL Server)
  • export CSV
  • export JSON
  • SQLite CLI (command line interface)

Tasks

• First, we will work with SQLite, which database is in one file and no configuration is needed.
• Download the following files into one directory:
  • emp.sql --- SQL commands to create the EMP database
  • queries.sql --- list of queries to write, edit this file
• Create a new database using

  sqlite3 --init emp.sql emp.db

• Verify that the database is OK:

  sqlitebrowser emp.db &

  or by

  sqlite3 emp.db

  SELECT * FROM emp;

• Execute queries in the file queries.sql:

  sqlite3 emp.db < queries.sql

  (to run queries, you also use Execute SQL tab in the sqlitebrowser.)
• work on the queries in queries.sql
• Write a couple more queries on your own and check the results. You can discuss and compare them with others around you (but don't overdo it for queries for which you receive points).
  • Experiment with the LIKE operator (check the documentation links given above).
• Export some data from an SQLite database to CSV and JSON formats.

Evaluation

For each of the 2 or 3 homework assignments you can have sum of max. 90 points.

Additional points are obtained for solving exersises from lessons: 0 or 1 point for each of the 12 lessons. From the points awarded for lesson exercises, it is necessary to obtain at least 9 to successfully complete the course.

Within 6 days after each lesson, solutions must be sent by e-mail to the address rjasko (at) dcs.fmph.uniba.sk. Subsequently, the solutions will be briefly evaluated: in case of sufficiency, 1 point will be awarded, in case of insufficiency, the student will be asked to supplement the solution (if he submitted at least something meaningful), for which he has another 3 days, and then he will receive a final evaluation.

Not all exercises need to be solved; it is neccessary to have solved (at least somehow correctly) about 40% of the exercises from each topic. If the set of exercises is more extensive, they are usually at least approximately in order of difficulty; if you want to solve a minimum of exercises, choose some more demanding ones from the second half. The recommended procedure is to go in order and skip exercises where it is clear that you already know how to solve them (eg they are similar to already solved exercises and you can see what the difference is and how to deal with it).

• A --- 92 and more points


• B --- 84 - 91 points


• C --- 76 - 83 points


• D --- 68 - 75 points


• E --- 60 - 67 points


• Fx --- less than 60 points