# USU Assessment Test - Thelen, Christian Rene
> 17. November 2021, Version 3.0

## Logic

1. Ina
2. Max
3. Nadine
4. Lars
5. Jonas

## Relational Data Modeling

1. Which plane models have arrived at the airport until now? (Output column: model)

SELECT model
FROM plane
JOIN airport ON plane.serial_number = airport.plane_serial_number
WHERE airport.time < CURRENT_TIMESTAMP
  AND airport.type = ’arrival’

2. How many planes of each type have started on a given date? (Output columns: type | number)

SELECT model
FROM plane
JOIN airport ON plane.serial_number = airport.plane_serial_number
WHERE airport.time > TIME_A 
  AND airport.time < TIME_B
  AND airport.type = departure

3. How many persons have been transported during the last week? (Output column: number)

SELECT COUNT(plane.capacity)
FROM plane
JOIN airport ON plane.serial_number = airport.plane_serial_number
WHERE airport.time > TIME_A 
  AND airport.time < TIME_B
  AND plane.type = passenger

## Database Queries with SQL

### a)
Description: Total sum of earnings.
Result:

| total |
| -------- |
| 8.500 |

### b)

Description: List of managers with their first and last name.
Result:

| first_name | last_name |
| ---------- | --------- |
| Horst | Müller |
| Frank | Meier |

### c)
Description: Lowest earning of an employee, that isn't a manager.
Result:

| MIN(earnings) |
| -------- |
| 1.900 |

### d)

Description: List of managers (first and last name) with the number their assigned team members.
Result:

| first_name | last_name | COUNT |
| -------- | -------- | -------- |
| Horst | Müller | 2 |
| Frank | Meier | 1 |