Lösungen

CRUD

CRUD-1

SELECT age, city FROM users;

CRUD-2

INSERT INTO users
  (name, age, city)
VALUES
  ('Elena', 22, 'Bern');

CRUD-3

UPDATE users
SET age = 18
WHERE id = 584;

CRUD-4

DELETE FROM users WHERE id = 84;

CRUD-5

Sara

SFW

SFW-1

SELECT age, city FROM users;

SFW-2

DELETE FROM users
WHERE age >= 20 AND age <= 25;

SFW-3

SELECT age FROM users
WHERE country != 'CH';

SFW-4

UPDATE users
SET city = 'Biel/Bienne'
WHERE city = 'Biel';

SFW-5

Melanie, Oceane, Manuela, Mauro

SFW-6

DELETE FROM
Agate, Sara, Viktor

SFW-7

Gibt alle Users die in Deutschland wohnen oder in der Stadt Pescara zurück.

Aggregate

Aggregate-1

SELECT MIN(age) FROM users;

Aggregate-2

49

Aggregate-3

SELECT AVG(age) FROM users
WHERE country = 'IT';

Aggregate-4

5

Aggregate-5

Thomas

GroupBy

GroupBy-1

SELECT MIN(age), country
FROM users
GROUP BY country;

GroupBy-2

10 FR | 2 IT | 14 NL | 8 UK | 5 CH | 20 DE

GroupBy-3

SELECT COUNT(id), city
FROM users
WHERE country = 'CH'
GROUP BY city;

OrderBy

OrderBy-1

SELECT * FROM users
ORDER BY age;

OrderBy-2

Thomas

OrderBy-3

SELECT AVG(age), country
FROM users
WHERE age >= 18
GROUP BY country
ORDER BY country;

Pagination

Pagination-1

10, 11

Pagination-2

SELECT * FROM users
ORDER BY age DESC
LIMIT 5;

Pagination-3

4, 8

Like

Like-1

Bern, Pescara, Zuzwil

Like-2

65

Like-3

SELECT id FROM users
WHERE name LIKE '%ne';