sql_buch.sql

/* Benutzen Sie die Suchfunktion über [Strg] + [F] und geben Sie die ersten Wörter des gesuchten Programms ein, 
   um zum gewünschten Programm zu gelangen */

CREATE DATABASE vereinDB;
USE vereinDB;
 CREATE TABLE mitglieder (
	id INT,
    vorname VARCHAR(20),
    name VARCHAR(20),
    telefon INT,
    alter_jahre INT
);

DROP TABLE mitglieder;
CREATE TABLE IF NOT EXISTS mitglieder(
	mitglieder_id INT PRIMARY KEY AUTO_INCREMENT,
	mitglieder_vorname VARCHAR(20) NOT NULL,
	mitglieder_name VARCHAR(20) NOT NULL,
    mitglieder_telefon INT,
    mitglieder_alter INT
);
insert into mitglieder (mitglieder_id) VALUES (1);
delete from mitglieder where mitglieder_id = 1;
select * from mitglieder where mitglieder_id = 1;

CREATE TABLE IF NOT EXISTS mitglieder2(
	mitglieder2_id INT PRIMARY KEY AUTO_INCREMENT,
	mitglieder2_vorname VARCHAR(20) NOT NULL,
	mitglieder2_name VARCHAR(20) NOT NULL,
    mitglieder2_telefon INT,
    mitglieder2_alter TINYINT UNSIGNED,
    mitglieder_nutzername VARCHAR(20) UNIQUE
);

ALTER TABLE mitglieder ADD mitglieder_nutzername VARCHAR(20) UNIQUE;

ALTER TABLE mitglieder ADD PRIMARY KEY (mitglieder_id);

ALTER TABLE mitglieder MODIFY COLUMN mitglieder_alter TINYINT UNSIGNED;

ALTER TABLE mitglieder MODIFY COLUMN mitglieder_vorname VARCHAR(20) NOT NULL;
ALTER TABLE mitglieder MODIFY COLUMN mitglieder_name VARCHAR(20) NOT NULL;

ALTER TABLE mitglieder MODIFY mitglieder_id INT AUTO_INCREMENT;
ALTER TABLE mitglieder DROP mitglieder_nutzername;
DROP TABLE mitglieder2;
DROP DATABASE testdb1;
DROP DATABASE testdb2;
CREATE DATABASE onlineshopXY;
USE onlineshopXY;
CREATE TABLE sortiment (
	sortiment_id INT PRIMARY KEY AUTO_INCREMENT,
    sortiment_produkttyp VARCHAR(30) NOT NULL,
    sortiment_preis DECIMAL(5,2) NOT NULL,
    sortiment_anzahl INT
);
ALTER TABLE sortiment ADD sortiment_versandkosten DECIMAL(5,2);
CREATE USER 'frank_maurer'@'localhost' IDENTIFIED BY 'abc+1';
GRANT ALL ON vereinDB.* TO 'frank_maurer'@'localhost';
REVOKE DROP ON vereinDB.* FROM 'frank_maurer'@'localhost';
DROP USER 'frank_maurer'@'localhost';

CREATE USER 'michaela_bauer'@'localhost' IDENTIFIED BY 'Ft4G6*s';
GRANT ALTER ON vereinDB.mitglieder TO 'michaela_bauer'@'localhost';
REVOKE ALL, GRANT OPTION FROM 'michaela_bauer'@'localhost';
DROP USER 'michaela_bauer'@'localhost';
INSERT INTO mitglieder (mitglieder_vorname, mitglieder_name, mitglieder_telefon, mitglieder_alter)
	VALUES ("Franziska", "Heinze", 12345, 32);
SELECT * FROM mitglieder;
INSERT INTO mitglieder (mitglieder_vorname, mitglieder_name)
	VALUES ("Patrick", "Schober");
USE vereinDB;


SELECT * FROM mitglieder;


SELECT mitglieder_name FROM mitglieder;
SELECT mitglieder_name, mitglieder_telefon FROM mitglieder;
SELECT mitglieder_name FROM mitglieder WHERE mitglieder_id = 2;
SELECT * FROM mitglieder WHERE mitglieder_id = 1;
INSERT INTO mitglieder (mitglieder_id, mitglieder_vorname, mitglieder_name, mitglieder_telefon, mitglieder_alter)
	VALUES (5, "Franziska", "Gruber", 76352, 26);
INSERT INTO mitglieder VALUES (3,"Sebastian", "Becker", 2575577, 24);
DELETE FROM mitglieder WHERE mitglieder_id > 2;
SELECT mitglieder_telefon FROM mitglieder WHERE mitglieder_vorname = "Sebastian";
SELECT mitglieder_telefon FROM mitglieder WHERE mitglieder_vorname = "Franziska";
SELECT mitglieder_telefon, mitglieder_vorname FROM mitglieder WHERE mitglieder_alter <= 27;
SELECT mitglieder_telefon, mitglieder_vorname FROM mitglieder WHERE mitglieder_vorname <> "Patrick";
UPDATE mitglieder SET mitglieder_telefon = 8273646 WHERE mitglieder_id = 2;
UPDATE mitglieder SET mitglieder_telefon = 8273646, mitglieder_alter = 42 WHERE mitglieder_id = 2;
UPDATE mitglieder SET mitglieder_alter = 25 WHERE mitglieder_id = 3;
UPDATE mitglieder SET mitglieder_alter = 33 WHERE mitglieder_vorname = "Franziska";
DELETE FROM mitglieder WHERE mitglieder_id = 2;
UPDATE mitglieder set mitglieder_alter = 26 WHERE mitglieder_id = 5;
USE onlineshopXY;
INSERT INTO sortiment VALUES (1, "Lautsprecher", 18.99, 53);
INSERT INTO sortiment (sortiment_produkttyp, sortiment_preis) VALUES ("USB-Kabel", 2.49);
UPDATE sortiment SET sortiment_anzahl = 153 WHERE sortiment_id = 2;
SELECT * FROM sortiment;
SELECT sortiment_preis FROM sortiment WHERE sortiment_id = 1;
SELECT sortiment_id FROM sortiment WHERE sortiment_preis <= 8.50;
DELETE FROM sortiment WHERE sortiment_id = 1;
INSERT INTO mitglieder VALUES (6,"Hermann", "Wolf", 2645256, 53);
INSERT INTO mitglieder VALUES (7,"Gerda", "Gruber", 43725013, 47);
INSERT INTO mitglieder VALUES (8,"Sebastian", "Becker", 75647712, 36);
SELECT * from mitglieder WHERE mitglieder_vorname = "Sebastian" AND mitglieder_name = "Becker" AND mitglieder_alter = 25;
SELECT * from mitglieder WHERE (mitglieder_name = "Gruber" OR mitglieder_name = "Wolf") AND mitglieder_alter > 40;
SELECT * FROM mitglieder WHERE NOT mitglieder_vorname = "Hermann";
SELECT * FROM mitglieder WHERE mitglieder_alter <= 30;
SELECT * FROM mitglieder WHERE NOT mitglieder_alter > 30;
SELECT * FROM mitglieder WHERE NOT (mitglieder_vorname = "Franziska" AND mitglieder_name = "Gruber");
SELECT * FROM mitglieder where mitglieder_vorname = "Gerda" OR mitglieder_vorname = "Hermann" OR mitglieder_vorname = "Sebastian";
SELECT * FROM mitglieder where mitglieder_vorname IN ("Gerda",  "Hermann", "Sebastian");
SELECT * FROM mitglieder WHERE mitglieder_alter >= 30 AND mitglieder_alter <= 40;
SELECT * FROM mitglieder WHERE mitglieder_alter BETWEEN 30 AND 40;
CREATE TABLE IF NOT EXISTS sportangebote(
	sportangebote_id INT PRIMARY KEY AUTO_INCREMENT,
	sportangebote_sportart VARCHAR(20) NOT NULL,
	sportangebote_teilnehmer INT NOT NULL
);
INSERT INTO sportangebote (sportangebote_sportart, sportangebote_teilnehmer) VALUES
	("Volleyball", 1),
    ("Volleyball", 3),
    ("Volleyball", 6),
    ("Volleyball", 7),
    ("Fußball", 3),
    ("Fußball", 5),
    ("Fußball", 8),
    ("Handball", 8);
SELECT * FROM sportangebote;
SELECT sportangebote_teilnehmer FROM sportangebote WHERE sportangebote_sportart = "Volleyball";
SELECT mitglieder_telefon FROM mitglieder WHERE mitglieder_id IN (SELECT sportangebote_teilnehmer FROM sportangebote WHERE sportangebote_sportart = "Volleyball");
INSERT INTO sortiment (sortiment_produkttyp, sortiment_preis, sortiment_anzahl) VALUES
	("MP3-Player", 14.49, 14),
    ("LED-Taschenlampe", 3.99, 27),
    ("Tablet", 143.00, 17),
    ("Kopfhörer", 28.99, 57),
    ("MP3-Player", 18.99, 97);
SELECT * FROM sortiment;

SELECT * FROM sortiment WHERE sortiment_anzahl < 30 AND sortiment_produkttyp != "Tablet";

SELECT sortiment_preis FROM sortiment WHERE sortiment_produkttyp IN ("USB-Kabel", "LED-Taschenlampe", "Tablet");
SELECT * FROM sortiment WHERE sortiment_preis BETWEEN 10 AND 100;

CREATE TABLE IF NOT EXISTS lieferanten(
	lieferanten_id INT PRIMARY KEY AUTO_INCREMENT,
	lieferanten_produktID INT NOT NULL,
	lieferanten_telefon INT NOT NULL
);


INSERT INTO lieferanten (lieferanten_produktID, lieferanten_telefon) VALUES
	(2, 15579975),
    (4, 3324413),
    (5, 6723442),
    (6, 7163435),
    (7, 39963556),
    (8, 6333892);
    
    select * from lieferanten;
    
SELECT lieferanten_telefon FROM lieferanten WHERE lieferanten_produktID IN (SELECT sortiment_ID FROM sortiment WHERE sortiment_anzahl < 30);
drop table sportangebote2;
CREATE TABLE IF NOT EXISTS sportangebote2(
	sportangebote_id INT PRIMARY KEY AUTO_INCREMENT,
	sportangebote_sportart VARCHAR(20) NOT NULL,
	sportangebote_teilnehmer INT NOT NULL,
    FOREIGN KEY (sportangebote_teilnehmer) REFERENCES mitglieder (mitglieder_id)
);

INSERT INTO sportangebote2 (sportangebote_sportart, sportangebote_teilnehmer) VALUES
	("Volleyball", 1),
    ("Volleyball", 3),
    ("Volleyball", 6),
    ("Volleyball", 7),
    ("Fußball", 3),
    ("Fußball", 5),
    ("Fußball", 8),
    ("Handball", 8);
SELECT * FROM sportangebote;
INSERT INTO sportangebote2 (sportangebote_sportart, sportangebote_teilnehmer) VALUES("Handball", 32);
ALTER TABLE sportangebote ADD FOREIGN KEY (sportangebote_teilnehmer) REFERENCES mitglieder (mitglieder_id);
ALTER TABLE sportangebote DROP FOREIGN KEY sportangebote_ibfk_3;
show create table sportangebote;
drop table sportangebote2;
SELECT sportangebote_teilnehmer.mitglieder_telefon FROM sportangebote WHERE sportangebote_sportart = "Volleyball";
DELETE FROM mitglieder WHERE mitglieder_id = 8;
UPDATE mitglieder SET mitglieder_id = 12 WHERE mitglieder_id = 6;
ALTER TABLE sportangebote ADD FOREIGN KEY (sportangebote_teilnehmer) REFERENCES mitglieder (mitglieder_id) ON UPDATE SET DEFAULT ON DELETE SET DEFAULT;
ALTER TABLE sportangebote MODIFY sportangebote_teilnehmer INT;

CREATE TABLE IF NOT EXISTS bestellungen(
	bestellungen_id INT PRIMARY KEY AUTO_INCREMENT,
	bestellungen_produktID INT,
	bestellungen_anzahl INT,
    FOREIGN KEY (bestellungen_produktID) REFERENCES sortiment (sortiment_ID)
);

ALTER TABLE lieferanten ADD FOREIGN KEY (lieferanten_produktID) REFERENCES sortiment (sortiment_ID);
show create table lieferanten;
show create table bestellungen;
ALTER TABLE lieferanten ADD FOREIGN KEY (lieferanten_produktID) REFERENCES sortiment (sortiment_ID);
ALTER TABLE bestellungen ADD FOREIGN KEY (bestellungen_produktID) REFERENCES sortiment (sortiment_ID) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE bestellungen DROP FOREIGN KEY bestellungen_ibfk_1;

SELECT mitglieder_telefon FROM mitglieder UNION SELECT sportangebote_sportart FROM sportangebote;
CREATE TABLE IF NOT EXISTS trainer(
	trainer_id INT PRIMARY KEY AUTO_INCREMENT,
	trainer_vorname VARCHAR(20) NOT NULL,
	trainer_name VARCHAR(20) NOT NULL,
    trainer_telefon INT
);
select * from trainer;
INSERT INTO trainer (trainer_vorname, trainer_name, trainer_telefon) VALUES
	('Carsten', 'Huber', 73648595),
    ('Peter', 'Schäfer', 98232445),
    ('Christina', 'Mayer', 3245612),
    ('Franziska', 'Heinze', 12345);
drop table trainer;
SELECT 'Mitglied' AS Funktion, mitglieder_telefon AS Telefonnummern FROM mitglieder
UNION ALL
SELECT 'Trainer', trainer_telefon FROM trainer;
SELECT mitglieder_vorname AS Vorname, mitglieder_telefon AS Telefonnummern FROM mitglieder
WHERE mitglieder_telefon NOT IN (SELECT trainer_telefon FROM trainer);
SELECT mitglieder_vorname AS Vorname, mitglieder_telefon AS Telefonnummern FROM mitglieder
WHERE mitglieder_vorname IN (SELECT trainer_vorname FROM trainer WHERE trainer_name IN (SELECT mitglieder_name FROM mitglieder));
select * from mitglieder;
INSERT INTO mitglieder (mitglieder_vorname, mitglieder_name, mitglieder_telefon, mitglieder_alter)
VALUES ('Christina', 'Schäfer', 72348344, 45);
SELECT mitglieder_vorname AS Vorname, mitglieder_telefon AS Telefonnummern FROM mitglieder
WHERE mitglieder_vorname IN (SELECT trainer_vorname FROM trainer)
AND mitglieder_name IN (SELECT trainer_name FROM trainer);
SELECT mitglieder_vorname AS Vorname, mitglieder_telefon AS Telefonnummern FROM mitglieder
WHERE mitglieder_vorname IN (SELECT trainer_vorname FROM trainer WHERE trainer_name = mitglieder_name);
SELECT mitglieder_vorname AS Vorname, mitglieder_telefon AS Telefonnummern FROM mitglieder
WHERE mitglieder_vorname NOT IN (SELECT trainer_vorname FROM trainer WHERE trainer_name = mitglieder_name);
SELECT mitglieder_vorname AS Vorname, mitglieder_telefon AS Telefonnummern FROM mitglieder
WHERE mitglieder_telefon NOT IN (SELECT trainer_telefon FROM trainer)
UNION
SELECT trainer_vorname, trainer_telefon FROM trainer
WHERE trainer_telefon NOT IN (SELECT mitglieder_telefon FROM mitglieder);

CREATE TABLE IF NOT EXISTS kunden(
	kunden_id INT PRIMARY KEY AUTO_INCREMENT,
	kunden_name VARCHAR(20) NOT NULL,
	kunden_vorname VARCHAR(20) NOT NULL,
    kunden_telefon INT NOT NULL
);
INSERT INTO kunden (kunden_name, kunden_vorname, kunden_telefon) VALUES
	('Baum', 'Jörg', 9237475),
    ('Roth', 'Stefanie', 7601323),
    ('Walther', 'Maria', 3324413),
    ('Seifert', 'Inga', 474246),
    ('Graf', 'Jens', 39963556),
    ('Hübner', 'Franz', 5252123);
select * from kunden;

SELECT 'Lieferant' AS Beziehung, lieferanten_telefon AS Telefonnummern FROM lieferanten
UNION ALL
SELECT 'Kunde', kunden_telefon FROM kunden;

SELECT lieferanten_id , lieferanten_telefon AS Telefonnummern FROM lieferanten
WHERE lieferanten_telefon NOT IN (SELECT kunden_telefon FROM kunden);

select * from sportangebote;
select * from mitglieder;
UPDATE sportangebote SET sportangebote_teilnehmer = 5 WHERE sportangebote_id = 8;
SELECT sportangebote_teilnehmer FROM sportangebote
	WHERE sportangebote_sportart = 'Fußball';
    
SELECT mitglieder_vorname, mitglieder_telefon FROM mitglieder
	WHERE mitglieder_id IN (5, 15);
    
SELECT sportangebote_teilnehmer, mitglieder_vorname, mitglieder_telefon
	WHERE sportangebote_sportart = 'Fußball';
    
SELECT sportangebote.sportangebote_teilnehmer, mitglieder.mitglieder_vorname, mitglieder.mitglieder_telefon
FROM sportangebote
JOIN mitglieder ON sportangebote.sportangebote_teilnehmer = mitglieder.mitglieder_id
AND sportangebote_sportart = 'Fußball';

SELECT sportangebote.sportangebote_teilnehmer, mitglieder.mitglieder_vorname, mitglieder.mitglieder_telefon
FROM sportangebote
LEFT JOIN mitglieder ON sportangebote.sportangebote_teilnehmer = mitglieder.mitglieder_id
AND sportangebote_sportart = 'Fußball'
UNION
SELECT sportangebote.sportangebote_teilnehmer, mitglieder.mitglieder_vorname, mitglieder.mitglieder_telefon
FROM sportangebote
RIGHT JOIN mitglieder ON sportangebote.sportangebote_teilnehmer = mitglieder.mitglieder_id
AND sportangebote_sportart = 'Fußball';
use onlineshopxy;
select * from lieferanten;
select * from sortiment;
SELECT sortiment.sortiment_produkttyp, sortiment.sortiment_preis, lieferanten.lieferanten_telefon
FROM sortiment
JOIN lieferanten ON sortiment.sortiment_id = lieferanten.lieferanten_produktID;

SELECT lieferanten.lieferanten_id, lieferanten.lieferanten_telefon, sortiment.sortiment_produkttyp
FROM lieferanten
JOIN sortiment ON sortiment.sortiment_id = lieferanten.lieferanten_produktID;

SELECT lieferanten.lieferanten_id, lieferanten.lieferanten_telefon, sortiment.sortiment_produkttyp
FROM lieferanten
LEFT JOIN sortiment ON sortiment.sortiment_id = lieferanten.lieferanten_produktID
AND lieferanten.lieferanten_produktID IN (2, 5, 7);

SELECT COUNT(*) FROM mitglieder;
select * from mitglieder;
select * from sportangebote;
SELECT COUNT(DISTINCT sportangebote_teilnehmer) FROM sportangebote;
update sportangebote set sportangebote_teilnehmer = 5 WHERE sportangebote_id = 8;
SELECT COUNT(sportangebote_id) FROM sportangebote WHERE sportangebote_sportart = 'Volleyball';
SELECT COUNT(mitglieder_id) FROM mitglieder WHERE mitglieder_alter BETWEEN 30 AND 50;
SELECT (SELECT COUNT(sportangebote_id) FROM sportangebote WHERE sportangebote_sportart = 'Volleyball'),
(SELECT COUNT(mitglieder_id) FROM mitglieder WHERE mitglieder_alter BETWEEN 30 AND 50);
show tables;
select * from trainer;
ALTER TABLE mitglieder ADD mitglieder_spenden DECIMAL(6,2);
UPDATE mitglieder SET mitglieder_spenden = 100 WHERE mitglieder_id = 5;
UPDATE mitglieder SET mitglieder_spenden = 22.50 WHERE mitglieder_id = 7;
UPDATE mitglieder SET mitglieder_spenden = 55 WHERE mitglieder_id = 15;
SELECT SUM(mitglieder_spenden) FROM mitglieder;
SELECT SUM(mitglieder_spenden) FROM mitglieder WHERE mitglieder_id IN (1, 7, 15);
SELECT AVG(mitglieder_alter) FROM mitglieder;
SELECT AVG(mitglieder_alter) FROM mitglieder WHERE mitglieder_id IN 
(SELECT sportangebote_teilnehmer FROM sportangebote WHERE sportangebote_sportart = 'Fußball');
SELECT AVG(mitglieder_spenden) FROM mitglieder;
SELECT AVG(COALESCE(mitglieder_spenden, 0)) FROM mitglieder;
SELECT MAX(mitglieder_spenden) FROM mitglieder;
SELECT mitglieder_spenden, mitglieder_vorname, mitglieder_name FROM mitglieder WHERE mitglieder_spenden = (SELECT MAX(mitglieder_spenden) FROM mitglieder);
SELECT mitglieder_spenden, mitglieder_vorname, mitglieder_name FROM mitglieder WHERE mitglieder_spenden = (SELECT MIN(mitglieder_spenden) FROM mitglieder);
UPDATE mitglieder SET mitglieder_spenden = 0 WHERE mitglieder_id IN (1, 12);


use onlineshopxy;
show tables;
select * from sortiment;
select * from lieferanten;
select * from kunden;
select kunden_id from kunden;
select * from bestellungen;
SELECT bestellungen_id FROM bestellungen;
show create table bestellungen;
SELECT COUNT(DISTINCT sortiment_produkttyp) FROM sortiment;
SELECT AVG(sortiment_anzahl) FROM sortiment;
SELECT MAX(sortiment_preis) FROM sortiment;
SELECT MIN(sortiment_preis) FROM sortiment;
SELECT sortiment_produkttyp, sortiment_id FROM sortiment WHERE sortiment_preis = (SELECT MAX(sortiment_preis) FROM sortiment);

INSERT INTO bestellungen (bestellungen_produktID, bestellungen_anzahl) VALUES
	(5, 3),(7, 1),(2, 4), (8, 2), (5, 2), (4, 4), (6, 2), (8, 3), (5, 2), (6, 1);
SELECT bestellungen_id, bestellungen_produktID, SUM(bestellungen_anzahl) FROM bestellungen GROUP BY bestellungen_produktID order by bestellungen_id;
SELECT bestellungen_ID, bestellungen_produktID, MAX(bestellungen_anzahl) FROM bestellungen GROUP BY bestellungen_produktID ORDER BY bestellungen_produktID;
SELECT bestellungen_id, bestellungen_produktID, SUM(bestellungen_anzahl) FROM bestellungen GROUP BY bestellungen_produktID ORDER BY SUM(bestellungen_anzahl) DESC;
SELECT * FROM bestellungen LIMIT 3;
drop table bestellungen;
CREATE TABLE IF NOT EXISTS bestellungen(
	bestellungen_id INT PRIMARY KEY AUTO_INCREMENT,
	bestellungen_produktID INT,
	bestellungen_anzahl INT,
    FOREIGN KEY (bestellungen_produktID) REFERENCES sortiment (sortiment_ID)
);

SELECT bestellungen_ID, bestellungen_produktID, MAX(bestellungen_anzahl) FROM bestellungen GROUP BY bestellungen_produktID HAVING COUNT(bestellungen_anzahl) > 1 ORDER BY bestellungen_produktID;
SELECT bestellungen_produktID, SUM(bestellungen_anzahl) FROM bestellungen GROUP BY bestellungen_produktID HAVING SUM(bestellungen_anzahl) >= 4;
SELECT bestellungen_ID, bestellungen_produktID, MAX(bestellungen_anzahl) FROM bestellungen GROUP BY bestellungen_produktID HAVING COUNT(bestellungen_anzahl) > 1 ORDER BY bestellungen_produktID;

use vereinDB;
show tables;
select * from mitglieder;
select * from sportangebote;
INSERT INTO mitglieder (mitglieder_vorname, mitglieder_name, mitglieder_telefon, mitglieder_alter) VALUES
	('Michaela', 'Wagner', 743555, 17),
    ('Jan', 'Schwartz', 324455, 14),
    ('Claudia', 'Stein', 7923455, 39),
    ('Joachim', 'Maurer', 9838333, 22);
INSERT INTO sportangebote (sportangebote_sportart, sportangebote_teilnehmer) VALUES
	('Volleyball', 16),
    ('Fußball', 17),
    ('Handball', 17),
    ('Handball', 18),
    ('Fußball', 19),
    ('Volleyball', 19);

SELECT sportangebote_sportart, COUNT(sportangebote_teilnehmer) FROM sportangebote GROUP BY sportangebote_sportart;
SELECT * FROM sportangebote GROUP BY sportangebote_sportart;
SELECT sportangebote_sportart, AVG(mitglieder.mitglieder_alter) FROM sportangebote 
JOIN mitglieder ON sportangebote.sportangebote_teilnehmer = mitglieder.mitglieder_id
GROUP BY sportangebote_sportart;
SELECT sportangebote_sportart, sportangebote_teilnehmer FROM sportangebote ORDER BY sportangebote_sportart DESC, sportangebote_teilnehmer;
SELECT sportangebote_sportart, COUNT(sportangebote_teilnehmer) FROM sportangebote GROUP BY sportangebote_sportart ORDER BY sportangebote_sportart;
SELECT * FROM mitglieder WHERE mitglieder_alter >= 30 LIMIT 4;

SELECT * FROM mitglieder;
SELECT mitglieder_name FROM mitglieder WHERE mitglieder_id = 5;
SELECT mitglieder_name FROM mitglieder HAVING mitglieder_id = 5;
SELECT mitglieder_id, mitglieder_name FROM mitglieder WHERE mitglieder_id = 5;
SELECT mitglieder_id, mitglieder_name FROM mitglieder HAVING mitglieder_id = 5;

SELECT mitglieder_spenden, mitglieder_vorname, mitglieder_name FROM mitglieder HAVING mitglieder_spenden < 100;
select * from mitglieder;

SELECT sportangebote_sportart, COUNT(sportangebote_teilnehmer) FROM sportangebote GROUP BY sportangebote_sportart HAVING COUNT(sportangebote_teilnehmer) > 3;
SELECT sportangebote_sportart, COUNT(sportangebote_teilnehmer) FROM sportangebote GROUP BY sportangebote_sportart WHERE COUNT(sportangebote_teilnehmer) > 3;
SELECT sportangebote_sportart, AVG(mitglieder.mitglieder_alter) FROM sportangebote 
JOIN mitglieder ON sportangebote.sportangebote_teilnehmer = mitglieder.mitglieder_id
GROUP BY sportangebote_sportart HAVING AVG(mitglieder.mitglieder_alter) < 30;
SELECT sportangebote_sportart, AVG(mitglieder.mitglieder_alter) FROM sportangebote 
JOIN mitglieder ON sportangebote.sportangebote_teilnehmer = mitglieder.mitglieder_id
GROUP BY sportangebote_sportart HAVING COUNT(sportangebote.sportangebote_teilnehmer) >3;
SELECT sportangebote_sportart, AVG(mitglieder.mitglieder_alter) FROM sportangebote 
JOIN mitglieder ON sportangebote.sportangebote_teilnehmer = mitglieder.mitglieder_id
GROUP BY sportangebote_sportart HAVING  AVG(mitglieder.mitglieder_alter) < 30 AND COUNT(sportangebote.sportangebote_teilnehmer) >3;

SELECT * FROM (SELECT sportangebote_sportart, COUNT(sportangebote_teilnehmer) AS anzahl FROM sportangebote GROUP BY sportangebote_sportart) AS ergebnis
WHERE anzahl > 3;
SELECT * FROM (SELECT sportangebote_sportart, COUNT(sportangebote_teilnehmer) AS anzahl FROM sportangebote GROUP BY sportangebote_sportart)
AS ergebnisliste WHERE ergebnisliste.anzahl > 3;
SELECT * FROM mitglieder WHERE mitglieder_alter LIKE '[3]_';
SELECT * FROM mitglieder WHERE mitglieder_name LIKE '%';
select * from trainer;
SELECT * FROM mitglieder WHERE mitglieder_vorname LIKE '%e%';
SELECT * FROM trainer WHERE trainer_name LIKE 'M__er' OR trainer_name LIKE 'M__r';
INSERT INTO trainer (trainer_vorname, trainer_name, trainer_telefon) VALUES
('Bettina', 'Ma_er', 2342345);
SELECT * FROM mitglieder WHERE mitglieder_name LIKE 'M%er';
SELECT * FROM trainer WHERE trainer_name LIKE 'Ma&_e_' ESCAPE '&';

SELECT * FROM mitglieder WHERE mitglieder_name REGEXP '^M';
SELECT * FROM mitglieder WHERE mitglieder_alter REGEXP '^3[1-5]$';
INSERT INTO mitglieder (mitglieder_vorname, mitglieder_name, mitglieder_telefon, mitglieder_alter) VALUES
('Bettina', 'Ma_er', 2342345, 300);
select * from mitglieder;
delete from trainer where trainer_id = 8;

select * from trainer;
SELECT * FROM trainer WHERE trainer_name REGEXP 'M[ae][iy]e|[iy]r$';

use onlineshopxy;
show tables;
select * from sortiment;
select * from kunden;
SELECT * FROM kunden WHERE kunden_vorname LIKE 'J%';
SELECT * FROM kunden WHERE kunden_vorname LIKE 'Ing_';
SELECT * FROM kunden WHERE kunden_telefon LIKE '_______';
SELECT * FROM kunden WHERE kunden_vorname REGEXP '^J';
SELECT * FROM kunden WHERE kunden_vorname REGEXP '^Ing[ae]$';


use vereinDB;
select * from mitglieder;
select * from sportangebote;
SELECT mitglieder_vorname, mitglieder_name,
CASE
WHEN mitglieder_spenden > 0 THEN mitglieder_spenden
ELSE 'Bitte einzahlen!'
END
FROM mitglieder;
ALTER TABLE mitglieder ADD COLUMN mitglieder_mail VARCHAR(30);
UPDATE mitglieder SET mitglieder_mail = 'fr_gruber@mailanbieter.com' WHERE mitglieder_id = 5;
UPDATE mitglieder SET mitglieder_mail = 'wolfi@mailanbieter.com' WHERE mitglieder_id = 12;
UPDATE mitglieder SET mitglieder_mail = 'm_w@mailanbieter.com' WHERE mitglieder_id = 16;

SELECT mitglieder_vorname, mitglieder_name,
CASE
WHEN mitglieder_mail IS NULL THEN mitglieder_telefon
WHEN mitglieder_mail IS NOT NULL THEN mitglieder_mail
END
FROM mitglieder;

SELECT mitglieder_vorname, mitglieder_name,
CASE
WHEN mitglieder_mail IS NULL THEN mitglieder_telefon
ELSE mitglieder_mail
END AS 'Kontakt'
FROM mitglieder;

UPDATE mitglieder SET mitglieder_mail = 
CASE
WHEN mitglieder_id = 7 THEN 'GG@mailanbieter.com'
WHEN mitglieder_id = 18 THEN 'ClaudiaS@mailanbieter.com'
ELSE 'JoMau@mailanbieter.com'
END
WHERE mitglieder_id in (7, 18, 19);
select * from mitglieder;
SELECT MAX(mitglieder_spenden), mitglieder_vorname, mitglieder_name FROM mitglieder;
SELECT mitglieder_spenden, mitglieder_vorname, mitglieder_name FROM mitglieder WHERE mitglieder_spenden = (SELECT MAX(mitglieder_spenden) FROM mitglieder);

show tables;
select * from sportangebote;
select * from mitglieder;

SELECT *,
CASE
WHEN mitglieder_spenden IS NULL OR mitglieder_spenden = 0 THEN 'Zu Spende auffordern!'
WHEN mitglieder_spenden = (SELECT MAX(mitglieder_spenden) FROM mitglieder) THEN 'Bei Mitgliederversammlung ehren!'
ELSE 'Dankesschreiben schicken!'
END AS 'Anmerkung'
FROM mitglieder;

SELECT * FROM mitglieder
ORDER BY 
CASE 
WHEN mitglieder_id IN (SELECT sportangebote_teilnehmer FROM sportangebote) THEN 0
ELSE 1
END, mitglieder_name;
select * from sportangebote;
DELETE FROM sportangebote WHERE sportangebote_id IN (4, 7, 10, 13);

SELECT * FROM mitglieder
ORDER BY
CASE 
WHEN mitglieder_id IN (SELECT sportangebote_teilnehmer FROM sportangebote) THEN 0
ELSE 1
END, mitglieder_name;

SELECT * FROM mitglieder
ORDER BY
CASE 
WHEN mitglieder_spenden IS NULL THEN 2
WHEN mitglieder_spenden = 0 THEN 1
ELSE 0
END, mitglieder_spenden;

use onlineshopxy;
select * from sortiment;
show tables;
select * from kunden;
select * from lieferanten;
select * from bestellungen group by bestellungen_produktid;


ALTER TABLE sortiment ADD COLUMN sortiment_beschreibung VARCHAR(200);
UPDATE sortiment SET sortiment_beschreibung = 
CASE
WHEN sortiment_id = 4 THEN 'Hochwertiger MP3-Player für Ihre Lieblingsmusik'
WHEN sortiment_id = 5 THEN 'Mit dieser LED-Taschenlampe bringen Sie Licht ins Dunkel!'
ELSE 'Mit diesem Kopfhörer genießen Sie ein herausragendes Hörerlebnis!'
END
WHERE sortiment_id in (4, 5, 7);


SELECT sortiment_id,
CASE
WHEN sortiment_beschreibung IS NULL THEN sortiment_produkttyp
ELSE sortiment_beschreibung
END
FROM sortiment;

SELECT *,
CASE
WHEN sortiment_anzahl < 20 THEN 'Dringend nachbestellen!'
WHEN sortiment_anzahl <= 40 THEN 'Bei der nächsten Bestellung ordern!'
ELSE ''
END AS 'Anmerkung'
FROM sortiment;

SELECT * FROM sortiment
ORDER BY
CASE
WHEN sortiment_produkttyp = 'LED-Taschenlampe' OR sortiment_produkttyp = 'Kopfhörer' THEN 0
ELSE 1
END, sortiment_preis;

use vereindb;

CREATE TABLE IF NOT EXISTS zugangsdaten(
	zugangsdaten_id INT PRIMARY KEY AUTO_INCREMENT,
	zugangsdaten_nutzername VARCHAR(20) NOT NULL,
	zugangsdaten_passwort VARCHAR(20) NOT NULL,
    zugangsdaten_mitglied INT NOT NULL UNIQUE,
	FOREIGN KEY (zugangsdaten_mitglied) REFERENCES mitglieder (mitglieder_id)
);

select * from mitglieder;

INSERT INTO zugangsdaten (zugangsdaten_nutzername, zugangsdaten_passwort, zugangsdaten_mitglied) VALUES
('frgr0104', 'kwl4j+5', 5),
('hermann', 'h67+g', 12),
('jan01', 'hdjwe7h3', 17),
('joM', 'abc123', 19);


CREATE TABLE IF NOT EXISTS sportveranstaltungen(
	sportveranstaltungen_id INT PRIMARY KEY AUTO_INCREMENT,
	sportveranstaltungen_sportart VARCHAR(20) NOT NULL,
	sportveranstaltungen_mindestalter INT,
	sportveranstaltungen_hoechstalter INT,
	sportveranstaltungen_termin VARCHAR(40) NOT NULL,
    sportveranstaltungen_trainer INT NOT NULL,
	FOREIGN KEY (sportveranstaltungen_trainer) REFERENCES trainer (trainer_id)
);
drop table sportveranstaltungen;
select * from trainer;

INSERT INTO sportveranstaltungen (sportveranstaltungen_sportart, sportveranstaltungen_mindestalter, sportveranstaltungen_hoechstalter, sportveranstaltungen_termin, sportveranstaltungen_trainer) VALUES
('Fußball', 6, 8, 'Mittwoch, 17:30 - 18:30', 6),
('Volleyball', 10, 14, 'Dienstag, 16:00 - 18:00', 3),
('Handball', 18, 35, 'Mittwoch, 19:30 - 21:00', 2),
('Fußball', 8, 10, 'Donnerstag, 18:00 - 19:30', 6);

update sportveranstaltungen set sportveranstaltungen_termin = 'Donnerstag, 18:00 - 19:30' where sportveranstaltungen_id = 4;

select * from sportveranstaltungen;
show tables;
select * from trainer;

INSERT INTO sportveranstaltungen (sportveranstaltungen_id, sportveranstaltungen_sportart, sportveranstaltungen_mindestalter, sportveranstaltungen_hoechstalter, sportveranstaltungen_termin, sportveranstaltungen_trainer) VALUES
(5, 'Fußball', 18, 35, 'Montag, 19:00 - 21:00', 2),
(6, 'Volleyball', 15, 99, 'Freitag, 18:00 - 20:00', 4),
(7, 'Fußball', 36, 99, 'Dienstag, 19:00 - 20:30', 1);

select * from sportangebote;
select * from mitglieder;
select * from sportveranstaltungen;
update sportveranstaltungen set sportveranstaltungen_mindestalter = 12, sportveranstaltungen_hoechstalter = 14 where sportveranstaltungen_id = 4;
delete from sportveranstaltungen where sportveranstaltungen_id in (5, 6, 7);

ALTER TABLE sportangebote ADD sportangebote_veranstaltung INT;

ALTER TABLE sportangebote ADD FOREIGN KEY (sportangebote_veranstaltung) REFERENCES sportveranstaltungen (sportveranstaltungen_id);
show create table sportangebote;

UPDATE sportangebote SET sportangebote_veranstaltung = 3 WHERE sportangebote_id in (8, 12);
UPDATE sportangebote SET sportangebote_veranstaltung = 6 WHERE sportangebote_id in (1, 3, 15);
UPDATE sportangebote SET sportangebote_veranstaltung = 5 WHERE sportangebote_id in (6, 14);
UPDATE sportangebote SET sportangebote_veranstaltung = 4 WHERE sportangebote_id = 11;

ALTER TABLE sportangebote DROP sportangebote_sportart;



use onlineshopxy;
show tables;

select * from lieferanten;
select * from sortiment;
select * from kunden;
select * from bestellungen;

show create table bestellungen;

CREATE TABLE IF NOT EXISTS zugangsdaten(
	zugangsdaten_id INT PRIMARY KEY AUTO_INCREMENT,
	zugangsdaten_nutzername VARCHAR(20) NOT NULL,
	zugangsdaten_passwort VARCHAR(20) NOT NULL,
    zugangsdaten_kunde INT NOT NULL UNIQUE,
	FOREIGN KEY (zugangsdaten_kunde) REFERENCES kunden (kunden_id)
);
drop table mitarbeiter;

CREATE TABLE IF NOT EXISTS mitarbeiter(
	mitarbeiter_id INT PRIMARY KEY AUTO_INCREMENT,
	mitarbeiter_name VARCHAR(20) NOT NULL,
	mitarbeiter_vorname VARCHAR(20) NOT NULL,
    mitarbeiter_abteilung INT NOT NULL,
    FOREIGN KEY (mitarbeiter_abteilung) REFERENCES abteilungen (abteilungen_id)
);

CREATE TABLE IF NOT EXISTS abteilungen(
	abteilungen_id INT PRIMARY KEY AUTO_INCREMENT,
	abteilungen_bezeichnung VARCHAR(20) NOT NULL
);

DROP TABLE bestellungen;
CREATE TABLE IF NOT EXISTS bestellungen(
	bestellungen_id INT PRIMARY KEY AUTO_INCREMENT,
	bestellungen_kunde INT NOT NULL,
	bestellungen_produkt INT NOT NULL,
    bestellungen_anzahl INT NOT NULL,
    FOREIGN KEY (bestellungen_kunde) REFERENCES kunden (kunden_id),
    FOREIGN KEY (bestellungen_produkt) REFERENCES sortiment (sortiment_id)
);


use vereindb;
select * from mitglieder;
select * from sportangebote;
select * from sportveranstaltungen;
SELECT mitglieder_id AS 'Mitgliedsnummer', mitglieder_vorname AS 'Vorname', mitglieder_name AS 'Nachname', mitglieder_telefon AS 'Telefonnummer', mitglieder_alter AS 'Alter', mitglieder_spenden AS 'Spendenbetrag', mitglieder_mail AS 'E-Mail' FROM mitglieder;

SELECT sportangebote_veranstaltung, AVG(mitglieder.mitglieder_alter) FROM sportangebote 
JOIN mitglieder ON sportangebote.sportangebote_teilnehmer = mitglieder.mitglieder_id
GROUP BY sportangebote_veranstaltung;
SELECT sportangebote_veranstaltung AS Veranstaltungsnummer, AVG(mitglieder.mitglieder_alter) AS Durchschnittsalter FROM sportangebote 
JOIN mitglieder ON sportangebote.sportangebote_teilnehmer = mitglieder.mitglieder_id
GROUP BY sportangebote_veranstaltung;

SELECT sportangebote.sportangebote_teilnehmer, mitglieder.mitglieder_vorname, mitglieder.mitglieder_telefon
FROM sportangebote
JOIN mitglieder ON sportangebote.sportangebote_teilnehmer = mitglieder.mitglieder_id
AND sportangebote.sportangebote_veranstaltung = 6;

SELECT s.sportangebote_teilnehmer, m.mitglieder_vorname, m.mitglieder_telefon
FROM sportangebote AS s
JOIN mitglieder AS m ON s.sportangebote_teilnehmer = m.mitglieder_id
AND s.sportangebote_veranstaltung = 6;

SELECT CONCAT(mitglieder_vorname, ' ', mitglieder_name), mitglieder_telefon FROM mitglieder;
SELECT CONCAT(mitglieder_vorname, ' ', mitglieder_name) AS Name, mitglieder_telefon AS Telefonnummer FROM mitglieder;

SELECT mitglieder_vorname, mitglieder_name,
CASE
WHEN mitglieder_mail IS NULL THEN mitglieder_telefon
ELSE mitglieder_mail
END AS Kontakt
FROM mitglieder WHERE mitglieder_alter > 30;

CREATE VIEW veranstaltung AS
SELECT mitglieder_vorname, mitglieder_name,
CASE
WHEN mitglieder_mail IS NULL THEN mitglieder_telefon
ELSE mitglieder_mail
END AS Kontakt
FROM mitglieder WHERE mitglieder_alter > 30;

SELECT * FROM veranstaltung;
SELECT mitglieder_name, Kontakt FROM veranstaltung WHERE mitglieder_vorname = 'Christina';
select * from mitglieder;
UPDATE mitglieder SET mitglieder_mail = 'christi@mailanbieter.com' WHERE mitglieder_id = 15;

CREATE OR REPLACE VIEW veranstaltung AS
SELECT mitglieder_vorname, mitglieder_name, mitglieder_alter,
CASE
WHEN mitglieder_mail IS NULL THEN mitglieder_telefon
ELSE mitglieder_mail
END AS Kontakt
FROM mitglieder WHERE mitglieder_alter > 30;

DROP VIEW veranstaltung;

SELECT mitglieder_vorname, mitglieder_name, CONCAT(mitglieder_alter, ' Jahre') AS 'Alter' FROM mitglieder;
SELECT mitglieder_vorname, UPPER(mitglieder_name) FROM mitglieder;
SELECT LENGTH(mitglieder_vorname) FROM mitglieder;
SELECT mitglieder_vorname FROM mitglieder WHERE LENGTH(mitglieder_vorname) = 7;
SELECT INSTR('Name', 'a');
SELECT SUBSTR('SQL-Kurs', 2, 4);
SELECT AVG(mitglieder_alter), FLOOR(AVG(mitglieder_alter)), CEIL(AVG(mitglieder_alter)), ROUND(AVG(mitglieder_alter)) FROM mitglieder;

use vereindb;
select * from mitglieder;
start transaction;
delete from mitglieder where mitglieder_id = 7;
select * from mitglieder;
rollback;

START TRANSACTION;
SELECT * FROM mitglieder;
show tables;
select * from zugangsdaten;
show create table sportangebote;
select * from sportangebote;
select * from mitglieder;

DELETE FROM mitglieder WHERE mitglieder_id = 1;

ROLLBACK;

create table t (id int primary key);
insert into t set id = 5;
select * from t;
START transaction;
drop table t;
select * from t;
rollback;
select * from t;
alter table t add column wert int;


START TRANSACTION;
DELETE FROM mitglieder WHERE mitglieder_id = 1;
COMMIT;

ROLLBACK;
SELECT * FROM mitglieder;
use vereindb;
show tables;
select * from zugangsdaten;
select * from mitglieder;
START TRANSACTION;
DELETE FROM zugangsdaten WHERE zugangsdaten_passwort = 'abcd123';
SAVEPOINT safepoint1;
DELETE FROM mitglieder WHERE mitglieder_id = 18;
ROLLBACK TO safepoint1;

CREATE TABLE test (id INT);
INSERT INTO test SET id = 4;
SELECT * FROM test;

START TRANSACTION;
DROP TABLE test;
ROLLBACK;

use onlineshopxy;
show tables;
select * from sortiment;
START TRANSACTION;
UPDATE sortiment SET sortiment_anzahl = 77 WHERE sortiment_id = 5;
SELECT * FROM sortiment;
ROLLBACK;
SELECT * FROM sortiment;


START TRANSACTION;
UPDATE sortiment SET sortiment_anzahl = 107 WHERE sortiment_id = 7;
SELECT * FROM sortiment;
COMMIT;
SELECT * FROM sortiment;


START TRANSACTION;
UPDATE sortiment SET sortiment_anzahl = 34 WHERE sortiment_id = 4;
SAVEPOINT sp1;
UPDATE sortiment SET sortiment_anzahl = 67 WHERE sortiment_id = 5;
SAVEPOINT sp2;
UPDATE sortiment SET sortiment_anzahl = 27 WHERE sortiment_id = 6;
SAVEPOINT sp3;
UPDATE sortiment SET sortiment_anzahl = 157 WHERE sortiment_id = 8;
SELECT * FROM sortiment;
ROLLBACK TO sp2;
UPDATE sortiment SET sortiment_anzahl = 163 WHERE sortiment_id = 2;
SAVEPOINT sp3;
UPDATE sortiment SET sortiment_anzahl = 157 WHERE sortiment_id = 8;
SELECT * FROM sortiment;
COMMIT;




use vereindb;
show tables;
select * from sportangebote;
select * from sportveranstaltungen;
ALTER TABLE sportveranstaltungen ADD (sportveranstaltungen_beginn TIME, sportveranstaltungen_ende TIME, sportveranstaltungen_wochentag INT);
UPDATE sportveranstaltungen SET sportveranstaltungen_beginn = '18:00:00' WHERE sportveranstaltungen_id = 4;
ALTER TABLE sportveranstaltungen DROP COLUMN sportveranstaltungen_termin;
SELECT  sportveranstaltungen_id, sportveranstaltungen_sportart, CASE
WHEN CURRENT_TIME() BETWEEN sportveranstaltungen_beginn AND sportveranstaltungen_ende AND DAYOFWEEK(CURRENT_TIMESTAMP()) = sportveranstaltungen_wochentag THEN 'Es findet gerade Training statt.'
ELSE 'Es findet gerade kein Training statt.'
END AS Anmerkung
FROM sportveranstaltungen;
update sportveranstaltungen set sportveranstaltungen_beginn = '09:00:00' where sportveranstaltungen_id = 1;

ALTER TABLE mitglieder ADD mitglieder_geburtsdatum DATE;
select * from mitglieder;
UPDATE mitglieder SET mitglieder_geburtsdatum = '1993-10-14' WHERE mitglieder_id = 5;

select current_timestamp();
select sysdate();
select current_date();
SELECT CURRENT_TIME();
SELECT CURRENT_DATE();
SELECT TIMESTAMPDIFF(MINUTE, '2020-02-08', '2020-02-12 12:35:07');
SELECT DATE_ADD('2020-02-08', INTERVAL 2 WEEK);
SELECT DAYOFWEEK(CURRENT_TIMESTAMP());
SELECT MONTH("2020-02-08");

select * from mitglieder;
UPDATE mitglieder SET mitglieder_geburtsdatum = '1972-11-18' WHERE mitglieder_id = 7;
UPDATE mitglieder SET mitglieder_geburtsdatum = '1967-01-05' WHERE mitglieder_id = 12;
UPDATE mitglieder SET mitglieder_geburtsdatum = '1974-06-07' WHERE mitglieder_id = 15;
UPDATE mitglieder SET mitglieder_geburtsdatum = '2002-12-12' WHERE mitglieder_id = 16;
UPDATE mitglieder SET mitglieder_geburtsdatum = '2005-05-01' WHERE mitglieder_id = 17;
UPDATE mitglieder SET mitglieder_geburtsdatum = '1980-11-13' WHERE mitglieder_id = 18;
UPDATE mitglieder SET mitglieder_geburtsdatum = '1997-04-14' WHERE mitglieder_id = 19;

SELECT mitglieder_vorname, mitglieder_name, TIMESTAMPDIFF(YEAR, mitglieder_geburtsdatum, CURRENT_TIMESTAMP()) AS 'Alter' FROM mitglieder;

alter table mitglieder drop mitglieder_alter;

SELECT DATE_FORMAT('2020-02-13 16:20:23', '%m %Y');
SELECT DATE_FORMAT('2020-02-13 16:20:23', '%d.%m.%Y %h:%m Uhr');
SELECT DATE_FORMAT("2017-06-15 12:03:23", "%d.%m.%Y %h:%m Uhr");

use onlineshopxy;
select * from bestellungen;
select * from sortiment;
ALTER TABLE bestellungen ADD (bestellungen_eingang TIMESTAMP, bestellungen_fertigstellung TIMESTAMP);
INSERT INTO bestellungen (bestellungen_kunde, bestellungen_produkt, bestellungen_anzahl, bestellungen_eingang, bestellungen_fertigstellung) VALUES
(1 , 4, 1, '2020-02-11 15:12:23', CURRENT_TIMESTAMP()),
(3 , 6, 3, '2020-02-10 11:33:54', CURRENT_TIMESTAMP()),
(4 , 8, 2, '2019-12-27 20:13:43', CURRENT_TIMESTAMP());
SELECT AVG(DATEDIFF(bestellungen_fertigstellung, bestellungen_eingang)) AS 'Durchschnittliche Bearbeitungsdauer' FROM bestellungen;




CREATE DATABASE krankenhaus;
USE krankenhaus;

CREATE TABLE IF NOT EXISTS zimmer(
	zimmer_id INT PRIMARY KEY AUTO_INCREMENT,
	zimmer_station VARCHAR (30)
);

CREATE TABLE IF NOT EXISTS bett(
	bett_id INT PRIMARY KEY AUTO_INCREMENT,
	bett_zimmer INT NOT NULL,
    FOREIGN KEY (bett_zimmer) REFERENCES zimmer (zimmer_id)
);

CREATE TABLE IF NOT EXISTS patienten(
	patienten_id INT PRIMARY KEY AUTO_INCREMENT,
	patienten_vorname VARCHAR (30),
	patienten_nachname VARCHAR (30),
    patienten_geschlecht CHAR (1),
    patienten_geburtsdatum DATE,
    patienten_anschrift VARCHAR (200),
    patienten_mail VARCHAR (100),
    patienten_telefon INT,
    patienten_angehörige VARCHAR (200),
    patienten_versicherung VARCHAR (40),
    patienten_versicherungsart VARCHAR (20),
    patienten_diagnose TEXT,
    patienten_behandlung TEXT,
    patienten_medikamente VARCHAR (200),
    patienten_pflege TEXT,
    patienten_einlieferung TIMESTAMP,
    patienten_voraussichtlicheEntlassung DATE,
    patienten_entlassung TIMESTAMP,
    patienten_bett INT NOT NULL,
    FOREIGN KEY (patienten_bett) REFERENCES bett (bett_id)
);

CREATE TABLE IF NOT EXISTS mitarbeiter(
	mitarbeiter_id INT PRIMARY KEY AUTO_INCREMENT,
	mitarbeiter_vorname VARCHAR (30),
	mitarbeiter_nachname VARCHAR (30),
    mitarbeiter_geschlecht CHAR (1),
    mitarbeiter_geburtsdatum DATE,
    mitarbeiter_anschrift VARCHAR (200),
    mitarbeiter_mail VARCHAR (100),
    mitarbeiter_telefon INT,
    mitarbeiter_gehalt DECIMAL (7,2),
    mitarbeiter_tätigkeit VARCHAR (40)
);

CREATE TABLE IF NOT EXISTS urlaub(
	urlaub_id INT PRIMARY KEY AUTO_INCREMENT,
	urlaub_beginn DATE NOT NULL,
    urlaub_ende DATE NOT NULL,
    urlaub_mitarbeiter INT NOT NULL,
    FOREIGN KEY (urlaub_mitarbeiter) REFERENCES mitarbeiter (mitarbeiter_id)
);

CREATE TABLE IF NOT EXISTS dienstplan(
	dienstplan_id INT PRIMARY KEY AUTO_INCREMENT,
	dienstplan_beginn TIMESTAMP NOT NULL,
    dienstplan_ende TIMESTAMP NOT NULL,
    dienstplan_mitarbeiter INT NOT NULL,
    FOREIGN KEY (dienstplan_mitarbeiter) REFERENCES mitarbeiter (mitarbeiter_id)
);

CREATE TABLE IF NOT EXISTS untersuchung(
	untersuchung_id INT PRIMARY KEY AUTO_INCREMENT,
	untersuchung_beginn TIMESTAMP NOT NULL,
    untersuchung_art VARCHAR (30) NOT NULL,
    untersuchung_patient INT NOT NULL,
    untersuchung_mitarbeiter INT NOT NULL,
    FOREIGN KEY (untersuchung_patient) REFERENCES patienten (patienten_id),
    FOREIGN KEY (untersuchung_mitarbeiter) REFERENCES mitarbeiter (mitarbeiter_id)
);

CREATE TABLE IF NOT EXISTS gerät(
	gerät_id INT PRIMARY KEY AUTO_INCREMENT,
	gerät_typ VARCHAR (30) NOT NULL
);

CREATE TABLE IF NOT EXISTS gerätebelegung(
	gerätebelegung_id INT PRIMARY KEY AUTO_INCREMENT,
	gerätebelegung_zeitpunkt TIMESTAMP NOT NULL,
    gerätebelegung_mitarbeiter INT NOT NULL,
    gerätebelegung_gerät INT NOT NULL,
    FOREIGN KEY (gerätebelegung_mitarbeiter) REFERENCES mitarbeiter (mitarbeiter_id),
    FOREIGN KEY (gerätebelegung_gerät) REFERENCES gerät (gerät_id)    
);

use onlineshopxy;
select * from sortiment;
select * from lieferanten;

SELECT l.lieferanten_id AS Lieferantennummer, l.lieferanten_telefon AS Telefonnummer, s.sortiment_produkttyp AS Produkttyp, s.sortiment_beschreibung AS Beschreibung
FROM lieferanten AS l
JOIN sortiment AS s ON l.lieferanten_produktID = s.sortiment_id;


CREATE VIEW preiswerteProdukte AS
SELECT sortiment_produkttyp, sortiment_preis FROM sortiment WHERE sortiment_preis < 20;

SELECT * FROM preiswerteProdukte;

SELECT SUBSTR(sortiment_beschreibung, 1, 20) FROM sortiment;

SELECT ROUND(AVG(sortiment_anzahl)) FROM sortiment;

use vereindb;

SELECT DATE_FORMAT('2020-02-13 16:20:23', '%d.%m.%Y %H:%i Uhr');