SET SCHEMA students;

DROP TABLE Person;
CREATE TABLE Person("First Name" varchar(25), "Last Name" varchar(25), "Country" varchar(25), "Year of Birth" int);
DELETE FROM Person;

CREATE TABLE Person(first_name varchar(25), last_name varchar(25), country varchar(25), year_of_birth int);

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);

SELECT * FROM Person;
seLect counTry FRoM perSoN;

SELECT first_name, last_name FROM Person;
SELECT DISTINCT first_name, last_name FROM Person;
SELECT first_name, last_name FROM Person GROUP BY first_name, last_name;

SELECT * FROM Person WHERE country = 'USA';
SELECT * FROM Person WHERE last_name LIKE 'S%';
SELECT UPPER(SUBSTR(country, 1, 3)) FROM Person;

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

SELECT * FROM Person, Country
SELECT * FROM Person JOIN Country ON Person.country = Country.country
SELECT * FROM Person FULL OUTER JOIN Country ON Person.country = Country.country

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

SELECT * FROM Person  WHERE year_of_birth = 1900;
SELECT * FROM Person WHERE year_of_birth = 1900 or year_of_birth != 1900;

SELECT * FROM Person WHERE year_of_birth IS NULL;

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