CREATE DATABASE vorlesung2206;
USE vorlesung2206;


############################
# Erstellen von Relationen #
############################

-- Beispiel Freibad ERM siehe Unterlagen
-- Relationenschema
-- Stadt(name:string, BL:string)
CREATE TABLE Stadt(
stadtname VARCHAR(50), 
bundesland CHAR(2),
PRIMARY KEY (stadtname, bundesland)
);

DROP TABLE stadt;

CREATE OR REPLACE TABLE Stadt(
stadtname VARCHAR(50), 
bundesland CHAR(2),
PRIMARY KEY (stadtname, bundesland)
);

-- Einwohner(eid:int, name:string, gebjahr:int);
CREATE OR REPLACE TABLE Einwohner(
eid INT AUTO_INCREMENT PRIMARY KEY, 
familienname VARCHAR(255),
geburtsjahr INT(4)
);

SHOW TABLES;
DESCRIBE Einwohner;

/* Freibad(name:string, maxBesucher:int,  
adresse:string, kinderbecken:bool)*/

CREATE OR REPLACE TABLE Freibad(
freibadname VARCHAR(255),
maxBesucher INT DEFAULT 150,
adresse VARCHAR(255),
kinderbecken BOOL,
PRIMARY KEY (freibadname));

/* Einwohner wohnt in Stadt 
 EinwohnerStadt(stadtname: string, 
 bundesland:string,eid:int)
 */
 
 CREATE OR REPLACE TABLE Stadt(
stadtname VARCHAR(50), 
bundesland CHAR(2),
eid INT,
PRIMARY KEY (eid)
);
 
 
############################
# Einfügen von Daten       #
############################

# Beispiel ohne Angabe der Attribute
 
INSERT INTO stadt VALUES
('Mainz', 'RP'),
('Wiesbaden', 'HE'); 
 
 SELECT * FROM stadt;
 
#Beispiel für PK-Abfrage
INSERT INTO stadt VALUES
('Mainz', 'RP');

INSERT INTO einwohner(familienname, geburtsjahr)
VALUES
('Jefcoat', 1955),
('Thanasouk', 1917),
('Hastings', 2022),
('Mcrill', 1978),
('Lazo', 2014),
('Gasparino', 2020),
('Thomison', 2000),
('Fredericks', 2002),
('Kopper', 1998),
('Nelsen', 1995);

SELECT * FROM einwohner;

INSERT INTO einwohner VALUES (100, 'Iwer', 1981);
INSERT INTO einwohner(familienname, geburtsjahr)
VALUES
('Tiber', 1972);

SELECT * FROM einwohner;


INSERT INTO Freibad VALUES
('zum See', 200, 'am See 3', FALSE);

SELECT * FROM Freibad;

INSERT INTO Freibad(freibadname, adresse)
VALUES ('zum Sprungturm', 'hinterm Bahnhof');
SELECT * FROM Freibad;


 /* Stadt besitzt Freibad 
 FreibadGehoertZuStadt (freibadname: string, 
 stadtname:string, bundesland:string)
 */
 
 CREATE OR REPLACE TABLE FreibadGehoertZuStadt(
freibadname VARCHAR(255),
stadtname VARCHAR(50), 
bundesland CHAR(2),
PRIMARY KEY (freibadname),
FOREIGN KEY (stadtname, bundesland) REFERENCES 
Stadt(stadtname, bundesland)
ON DELETE NO ACTION
ON UPDATE CASCADE,
FOREIGN KEY (freibadname) REFERENCES
freibad(freibadname)
ON DELETE CASCADE
ON UPDATE CASCADE 
);

DESCRIBE FreibadGehoertZuStadt;

INSERT INTO FreibadGehoertZuStadt 
VALUES ('zum See', 'Wiesbaden', 'HE');

INSERT INTO FreibadGehoertZuStadt 
VALUES ('zum See', 'Mainz', 'HE');

INSERT INTO FreibadGehoertZuStadt 
VALUES ('zum Sprung', 'Mainz', 'HE');

INSERT INTO FreibadGehoertZuStadt 
VALUES ('zum Sprungturm', 'Mainz', 'HE');


INSERT INTO FreibadGehoertZuStadt 
VALUES ('zum Sprungturm', 'Wiesbaden', 'HE');

 /* Einwohner gehört zu Stadt
 EinwohnerWohntIn(eid:int, stadtname:string, bundesland:string)
 */
 
 CREATE TABLE EinwohnerWohntIn(
 eid INT PRIMARY KEY, 
 stadtname VARCHAR(50), 
 bundesland CHAR(2),
 FOREIGN KEY (eid) REFERENCES Einwohner(eid),
 FOREIGN KEY (stadtname, bundesland) REFERENCES 
Stadt(stadtname, bundesland)
);


INSERT INTO EinwohnerWohntIn 
(SELECT eid, 'Wiesbaden', 'HE' FROM einwohner WHERE eid < 100);

SELECT * FROM EinwohnerWohntIn;

INSERT INTO EinwohnerWohntIn 
(SELECT eid, 'Mainz', 'RP' FROM einwohner WHERE eid > 99);

####################
#Löschen				 #
###################

DELETE FROM EinwohnerWohntIn WHERE eid=2; 
SELECT * FROM EinwohnerWohntIn;

DELETE FROM Einwohner WHERE eid=1; 

################
# UPDATE			#
###############
SELECT * FROM einwohner;

UPDATE einwohner SET familienname = 'Lopper'
WHERE eid = 9;

UPDATE einwohner SET geburtsjahr = 2002
WHERE eid < 3;

##############
# ALTER 		 #
#############

SELECT * FROM einwohner;
ALTER TABLE einwohner ADD vorname VARCHAR(255);
SELECT * FROM einwohner;
ALTER TABLE einwohner DROP vorname;
SELECT * FROM einwohner;
ALTER TABLE einwohner ADD vorname VARCHAR(255) DEFAULT 'Kevin';
SELECT * FROM einwohner;
DESCRIBE einwohner;
ALTER TABLE einwohner ALTER vorname DROP DEFAULT;
DESCRIBE einwohner;

######################
# VIEWS					#
#####################

# Ansicht wieviele Menschen in einer Stadt wohnen

SELECT * FROM einwohnerwohntin NATURAL JOIN stadt;

SELECT stadtname, bundesland, COUNT(eid) as Anzahl
 FROM einwohnerwohntin NATURAL JOIN stadt
 GROUP BY stadtname, bundesland;


CREATE VIEW StadtEinwohner 
AS
SELECT stadtname, bundesland, COUNT(eid) as Anzahl
 FROM einwohnerwohntin NATURAL JOIN stadt
 GROUP BY stadtname, bundesland;
 
 SHOW TABLES;

SELECT * FROM stadteinwohner;
UPDATE stadteinwohner SET Anzahl = 10;

DROP database vorlesung2206;
