-- was gibt es für Datenbanken?
SHOW DATABASES;
-- benutze diese Datenbank
USE bar;
-- was gibt es für Tabellen
SHOW TABLES;
-- Wie sieht die Tabelle aus
DESCRIBE bars;
--- Zeige alle Werte einer Tabelle
SELECT * FROM bars;
-- Zeige nur bestimmte Attribute einer Tabelle
SELECT city, phone FROM bars;
-- Zeige bestimmte Attribute mit einem bestimmten Namen einer Tabelle
SELECT name as BarName, city, phone FROM bars;
-- Verändern von Werten in einem Attribut + neuer Attributname 
SELECT beer, price, price + 1.5 AS NeuerPreis FROM sells;
-- where Bedingung 
SELECT * FROM sells WHERE price < 4;
-- where Bedingung mit Umbennung des Attributswertes
SELECT beer AS KostenGut FROM sells WHERE price < 4;
-- Ausgabe nur einfach
SELECT distinct(beer) AS KostenGut FROM sells WHERE price < 4;
-- Sortierung aufsteigend
SELECT distinct(beer) AS KostenGut FROM sells WHERE price < 4
ORDER BY KostenGut;

-- Sortierung absteigend
SELECT beer AS KostenGut, price FROM sells WHERE price < 4
ORDER BY KostenGut DESC;

-- Sortierung aufsteigend
SELECT beer AS KostenGut, price, bar FROM sells WHERE price < 4
ORDER BY price ASC, bar DESC;

-- Nummerische where Bedingung

SELECT * FROM sells where price = 5;
SELECT * FROM sells where price > 5;
SELECT * FROM sells where price < 5;
SELECT * FROM sells where price <= 5;
SELECT * FROM sells where price >= 5;
SELECT * FROM sells where price <> 5;

-- logische Operatoren

SELECT * FROM sells where price < 5 AND
beer='Budweiser';

SELECT * FROM sells where price < 5 OR
beer='Budweiser';

SELECT * FROM sells where price < 5 AND
NOT (Beer='Budweiser');

-- vollst. Strings
SELECT * FROM sells WHERE bar LIKE 'Caravan';

-- String mit einem Wert
SELECT * FROM sells WHERE bar LIKE '%C%';

SELECT * FROM sells WHERE bar LIKE '_h%';

SELECT * FROM sells WHERE bar LIKE '_% _% _%';

SELECT * FROM sells WHERE bar NOT LIKE '%e%';

-- Aggregationen
SELECT * FROM sells;
SELECT MIN(price) FROM sells;
SELECT MAX(price) FROM sells;
SELECT AVG(price) FROM sells;
SELECT SUM(price) FROM sells;
SELECT COUNT(*) FROM sells;

-- erst SELECT, dann Projektion
SELECT MAX(price) FROM sells WHERE beer='Budweiser';


-- NULL-Vales
SELECT * FROM drinkers;

SELECT * FROM drinkers WHERE addr LIKE '1%';
SELECT * FROM drinkers WHERE addr LIKE '1%' OR addr NOT LIKE '1%';

SELECT COUNT(*), COUNT(addr) FROM drinkers;

SELECT * FROM drinkers WHERE addr IS NULL;


-- Gruppierung
SELECT beer, MIN(price), MAX(price), COUNT(*), AVG(price) FROM sells GROUP BY beer;

-- Having
SELECT beer, MIN(price), MAX(price), COUNT(*) AS Anzahl, AVG(price) 
FROM sells 
GROUP BY beer HAVING Anzahl > 1;

-- JOINS
SELECT * FROM sells, bars;
SELECT * FROM likes NATURAL JOIN 
(SELECT NAME AS drinker, city FROM drinkers)t1;

-- Mengenoperatoren


SELECT city FROM bars
UNION
SELECT city FROM drinkers;

SELECT city FROM bars
UNION ALL
SELECT city FROM drinkers;

SELECT city FROM drinkers
INTERSECT 
SELECT city FROM bars;

SELECT city FROM drinkers
INTERSECT ALL
SELECT city FROM bars;

SELECT city FROM bars
EXCEPT 
SELECT city FROM drinkers;

SELECT city FROM bars
EXCEPT ALL
SELECT city FROM drinkers;

