Database I, année 2022

Exam

You must send two files by mail to charles.paperman@univ-lille.fr with the topic [DS]_Exam_1.

Please copy paste the topic to avoid that I need any manual intervention.

You don’t need to put anything in the body of the email (I will not look at it).

  1. One file queries.sql containing the SQL queries asked in the questions.
  2. One file ans.txt containing answers to the preliminary part and the output of the queries you got (if not too long).

I will read ans.txt and execute queries.sql (and read it only if needed).

In total you have 10 queries to produce with increasing difficulties. The first one should be immediate and the last ones are expected to take several minutes to be find.

Preliminary

Download the sql file here.

You can execute this file by running

cat exam.sql | psql (...)

By replacing the (...) with your credentials to connect to the database.

Explore the database

The database contains four tables.

You can display their name with the psql commands \dt and some description with \d table_name.

Provides a short description of what is the purpose of the database.

Simple queries

  1. Give the query to display only the content of the message
  2. Give the query to display only the name of the account

Simple queries with conditions

  1. Give the query to display the most recent and the most ancient message.

You can look at ORDER BY

  1. Give the query to display the 10 most recents messages starting to the second one.

You can look at OFFSET

Simple queries with aggregation

  1. Give the query computing the account_id with the most message posted.

The answer is 224 with 15

A small join

  1. Give the query to compute all message posted by the Dawn Medina.

A timeline

Each account see only the messages posted by account its follows.

  1. Give a query that display for Dawn Medina all the message it sees order by date and at most 10.

A social graph

  1. Give a query that compute all account (by their name) that follow someone and which is followed back.

You should get 12 lines

  1. Give a query of all accounts that have send a private message to someone they are not following.

  2. Give a query of all account that are following someone without having send them a private message.

Bonus

Correction

Here
Mastodon