# Blatt 03

## Aufgabe 1
<!---```SQL
SELECT s.Name, a.Name, c.Title
FROM Student as s, attend as d, Assistent as a, Course as c
WHERE s.StuNo = d.StuNo
AND d.CouNo=c.CouNo
AND a.Boss = c.TaughtBy

###### tags: `todo` Reihenfolge der Joins ändern und gucken, ob das optimiert

SELECT *
FROM Student as s
INNER JOIN attend as d ON d.StuNo=s.StuNo
INNER JOIN Course as c on c.CouNo=d.CouNo
INNER JOIN Assistent as a ON a.Boss=c.TaughtBy

## Aufgabe 2
### 1
###### tags: `todo` Auf inserts abändern
CREATE TABLE email_Adressen (
email VARCHAR(255) NOT NULL ,
details VARCHAR(255) ,
PRIMARY KEY(email_Adressen)
);

ALTER TABLE Professor
ADD COLUMN email VARCHAR(255) ,
FOREIGN KEY(email) REFERENCES email_Adressen(email) ON DELETE SET NULL;

ALTER TABLE Student
ADD COLUMN email VARCHAR(255), 
FOREIGN KEY(email) REFERENCES email_Adressen(email) ON DELETE SET NULL;

        
### 2
CREATE TABLE unterrichtet (
PerNo INT NOT NULL,
CouNo INT NOT NULL UNIQUE,
FOREIGN KEY(PerNo) REFERENCES Professor(PerNo),
FOREIGN KEY(CouNo) REFERENCES Course(CouNo)
);

        
ALTER TABLE Course
DROP COLUMN TaughtBy;


## Aufgabe 3
### 1

USE dbs2022;

DESCRIBE student_mat;
LOAD DATA INFILE "/var/lib/mysql/student-mat.csv"
INTO TABLE student_mat
FIELDS
TERMINATED BY ';'
ENCLOSED BY '"'
IGNORE 1 ROWS;

### 2

USE dbs2022;

SELECT COUNT(G1) as G1Part, COUNT(G2) as G2Part, COUNT(G3) as G3Part, MAX(G1) as G1Best, MAX(G2) as G2Best, MAX(G3) as G3Best, AVG(G1) as G1Avg, AVG(G2) as G2Avg, AVG(G3) as G3Avg
FROM student_mat;

SELECT GREATEST(G1, G2, G3) as Best, (G1 + G2 + G3) / 3 as AvgG
FROM student_mat;