This a plain file that contains the commands that can be used to replicate the
hands-on example "PSQL - Basic SQL".

Commands are wrapped in a blocks of ``` (according to Markdown:
https://en.wikipedia.org/wiki/Markdown; please use a Markdown viewer for proper
readability) and on top the description of the code is underline with `=======`.

## (0) Preparation
================================================================================

Follow the instructions of Assignment 1 to set up the PostgreSQL database such
that the queries Q1-Q4 can be executed.

Link to the description of Assignment 1:
- https://dbresearch.uni-salzburg.at/teaching/2026ss/dim/assignment1.pdf

Then, open a psql terminal.

## (1) Show the tables
================================================================================
```
\dt
```

We may see some tables, but we should not see tables named `Department` or
`Members`. Otherwise, we can use the `DROP TABLE` command to delete a table if
it exists. However, use the following SQL statements with caution as the tables
may be lost forever.

```
DROP TABLE Members;
```

```
DROP TABLE Departments;
```

## (2) Create the tables
================================================================================

Since the tables do not exist, we create them using two `CREATE TABLE`
statements as follows.

```
CREATE TABLE Departments (
  name VARCHAR(100) PRIMARY KEY,
  location VARCHAR(75),
  year INTEGER NOT NULL CHECK(year >= 1622)
);
```

```
CREATE TABLE Members (
  name VARCHAR(50) PRIMARY KEY,
  employment VARCHAR(50),
  department VARCHAR(100) REFERENCES Departments(name)
);
```

## (3) Insert data into the tables
================================================================================

Since the tables are currently empty, we insert some data into them. We start by
inserting two members (`Daniel Kocher` and `Ana Sokolova`) into the table
`Members`. However, this insertion will fail as tuples in table `Members` *must*
reference a tuple in table `Departments`, as the column `department` is
specified accordingly.

```
INSERT INTO Members
VALUES
  ('Daniel Kocher', 'Senior Scientist', 'CS'),
  ('Ana Sokolova', 'Full Prof.', 'CS');
```

Therefore, we first populate the table `Departments`. Afterwards, we can also
insert data into the table `Members` as shown below.

```
INSERT INTO Departments
VALUES
  ('CS', 'Science City Salzburg', 2022),
  ('AIHI', 'Science City Salzburg', 2022),
  ('Z_GIS', 'Science City Salzburg', 2022),
  ('Mathematics', 'NAWI', 1622);
```

```
INSERT INTO Members
VALUES
  ('Daniel Kocher', 'Senior Scientist', 'CS'),
  ('Florian Graf', 'Postdoc. Researcher', 'AIHI'),
  ('Bianca Löhnert', 'PhD Student', 'CS'),
  ('Christoph Kirsch', 'Full Professor', 'CS'),
  ('Michael Linortner', 'PhD Student', 'AIHI'),
  ('Roland Kwitt', 'Full Professor', 'AIHI'),
  ('Dirk Tiede', 'Assoc. Professor', 'Z_GIS'),
  ('Simon Blatt', 'Assoc. Professor', 'Mathematics'),
  ('Ana Sokolova' , 'Full Professor', 'CS');
```

## (4) Show the data in our tables
================================================================================

Below, we show some example SQL statement to query the data in the tables based
on different criteria.

First, we request all columns (*) of the table `Members` and `Departments`,
respectively.

```
SELECT * FROM Members;
```

```
SELECT * FROM Departments;
```

Second, we request only the column `department` of the table `Members` and the
column `name` must match (i.e., be equal to) `'Daniel Kocher'`. That is, we ask
for all departments that employ a member named `'Daniel Kocher'`.

```
SELECT department FROM Members WHERE name = 'Daniel Kocher';
```

Third, we request the columns `name` and `department` of the table `Members` and
the column `employment` must match (i.e., be equal to) `'Full Professor'`. That
is, we ask for the names of all full professors with their affiliation.

```
SELECT name, department FROM Members WHERE employment = 'Full Professor';
```

Fourth, we request to count the number of tuples that satisfy the criterion
described in the preceding SQL statement.

```
SELECT COUNT(*) FROM Members WHERE employment = 'Full Professor';
```

## (5) Show some (PostgreSQL-specific) meta data
================================================================================

The following SQL statement retrieves various columns of `public` tables from
one of the meta data tables, in this case `information_schema.columns`.

```
SELECT  table_name,
        column_name,
        is_nullable,
        data_type,
        character_maximum_length,
        numeric_precision
FROM information_schema.columns 
WHERE table_schema = 'public';
```

## (6) Update and constraint violations
================================================================================

In this step, we observe update and constraint violations by example.

First, we try to insert a tuple into table `Departments` with `year` being
`1500`. This will fail because there is a `CHECK` constraint on the column
`year`, which only allows values greater or equal to `1622`.

```
INSERT INTO Departments VALUES ('Biologie', 'NAWI', 1500);
```

Second, we try to insert a tuple into table `Members` with `name` being
`'Daniel Kocher'`. Again, this will fail because a constraint is violated. This
time, the primary key constraint of the column `name` is violated because a
tuple with `name` equal to `'Daniel Kocher'` already exists in this table.

```
INSERT INTO Members VALUES ('Daniel Kocher', 'Full Professor', 'Z_GIS');
```

Third, we try to update the table `Departments` by replacing `'CS'` with
`'Computer Science'` for all tuples whose `name` matches `'CS'`. Once again,
this will fail due to the foreign key constraint. That is, the column
`department` of table `Members` references column `name` of table `Departments`.
Hence, we cannot simply update this specific column in table `Departments`
because it would invalidate all references (i.e., foreign keys).

```
UPDATE Departments
SET name = 'Computer Science'
WHERE name = 'CS';