-- Anlegen einer Tabelle
CREATE TABLE Person("First Name" varchar(25), "Last Name" varchar(25), "Country" varchar(25), "Year of Birth" int);
DROP TABLE Person;

-- Implizite String-Konvertierung bei Spaltennamen
CREATE TABLE Person(first_name varchar(25), last_name varchar(25), country varchar(25), year_of_birth int);

-- Einfügen von Tupeln
INSERT INTO Person VALUES('Paul', 'Smith', 'Australia', 1986);
INSERT INTO Person VALUES('Lena', 'Jones', 'USA', 1900);
INSERT INTO Person VALUES('Hanna', 'Schulze', 'Germany', 1942);
INSERT INTO Person VALUES('Hanna', 'Schulze', 'USA', 2000);

-- Abfragen von Tupeln & Case Insensitivy bei Keywords
SELECT * FROM Person;
seLect counTry FRoM perSoN;

-- Achtung beim Löschen ohne WHERE Bedingung!
DELETE FROM Person;

SELECT first_name, last_name FROM Person;

-- erzwingen von Einzigartigkeit der Tupel in der Ergebnismenge  
SELECT DISTINCT first_name, last_name FROM Person;

-- Gruppierung 
SELECT first_name, last_name FROM Person GROUP BY first_name, last_name;

-- Attributsvergleiche
SELECT * FROM Person WHERE country = 'USA';
SELECT * FROM Person WHERE last_name LIKE 'S%';

-- Erweiterte Projektion
SELECT UPPER(SUBSTR(country, 1, 3)) FROM Person;

DROP TABLE IF EXISTS Country;
CREATE TABLE Country(country varchar(25), capital varchar(25));
INSERT INTO Country VALUES('Germany', 'Berlin');
INSERT INTO Country VALUES('USA', 'Washington');

SELECT * FROM Country;

-- kartesisches Produkt
SELECT * FROM Person, Country

-- joins
SELECT * FROM Person JOIN Country ON Person.country = Country.country
SELECT * FROM Person NATURAL JOIN Country;
SELECT * FROM Person FULL OUTER JOIN Country ON Person.country = Country.country

-- insert mit NULL
INSERT INTO Person VALUES('John', 'Doe', 'USA', NULL);

SELECT * FROM Person  WHERE year_of_birth = 1900;

-- Spaß mit NULL
SELECT * FROM Person WHERE year_of_birth = 1900 or year_of_birth != 1900;
SELECT * FROM Person WHERE year_of_birth IS NULL;

-- Aggregationsfunktionen
SELECT MAX(year_of_birth) FROM Person;
SELECT Country, MAX(year_of_birth) FROM Person GROUP BY Country;