Habe ich die Aufgabe richtig gelöst?
Drop TABLE Hält;
Drop TABLE Prof;
Drop TABLE Student;
Drop TABLE FACH;
CREATE TABLE FACH(
ID int primary KEY,
Name varchar(50));
CREATE TABLE Student(
ID1 int primary KEY,
ID int ,
Note int);
CREATE TABLE Prof(
ID2 int primary KEY,
Name1 varchar(50));
CREATE TABLE Hält(
ID2 int NOT NULL,
ID int NOT NULL,
Primary key (ID2,ID));
INSERT INTO Fach
VALUES (1,'Logistik');
INSERT INTO Fach
VALUES (2,'DatenBank');
INSERT INTO Fach
VALUES (4,'PM2');
INSERT INTO Fach
VALUES (3,'QM');
Select * FROM fach;
INSERT INTO Student
VALUES (1,1,2);
INSERT INTO Student
VALUES (3,1,1);
INSERT INTO Student
VALUES (2,2,3);
INSERT INTO Student
VALUES (4,2);
INSERT INTO Student
VALUES (5,2,1);
INSERT INTO Student
VALUES (6,3,2);
INSERT INTO Student
VALUES (7,2,2);
INSERT INTO Student
VALUES (8,2,4);
INSERT INTO Student
VALUES (9,2,5);
Select * FROM Student;
Select * FROM Student ORDER BY Note DESC;
Select ID1 FROM Student WHERE ID=2;
Select count(ID1) FROM Student WHERE ID=2;
Select AVG (note) FROM Student GROUP BY ID1;
Select AVG (note) AS Durchschnitt_Alle_Noten FROM Student WHERE ID=2;
INSERT INTO Prof
VALUES (1,'SCHMIDT');
INSERT INTO Prof
VALUES (2,'Makki');
Select * From Student,Fach,Prof,HÄLT WHERE Fach.ID=Student.ID AND Hält.ID2=Prof.ID2 AND Fach.ID=Hält.ID and ID2=SCHMIDT;////Wieder kommen
Select Name From Fach,Student WHERE Fach.ID=Student.ID GROUP BY Name HAVING count(note)>3 ;
Drop TABLE Hält;
Drop TABLE Prof;
Drop TABLE Student;
Drop TABLE FACH;
CREATE TABLE FACH(
ID int primary KEY,
Name varchar(50));
CREATE TABLE Student(
ID1 int primary KEY,
ID int ,
Note int);
CREATE TABLE Prof(
ID2 int primary KEY,
Name1 varchar(50));
CREATE TABLE Hält(
ID2 int NOT NULL,
ID int NOT NULL,
Primary key (ID2,ID));
INSERT INTO Fach
VALUES (1,'Logistik');
INSERT INTO Fach
VALUES (2,'DatenBank');
INSERT INTO Fach
VALUES (4,'PM2');
INSERT INTO Fach
VALUES (3,'QM');
Select * FROM fach;
INSERT INTO Student
VALUES (1,1,2);
INSERT INTO Student
VALUES (3,1,1);
INSERT INTO Student
VALUES (2,2,3);
INSERT INTO Student
VALUES (4,2);
INSERT INTO Student
VALUES (5,2,1);
INSERT INTO Student
VALUES (6,3,2);
INSERT INTO Student
VALUES (7,2,2);
INSERT INTO Student
VALUES (8,2,4);
INSERT INTO Student
VALUES (9,2,5);
Select * FROM Student;
Select * FROM Student ORDER BY Note DESC;
Select ID1 FROM Student WHERE ID=2;
Select count(ID1) FROM Student WHERE ID=2;
Select AVG (note) FROM Student GROUP BY ID1;
Select AVG (note) AS Durchschnitt_Alle_Noten FROM Student WHERE ID=2;
INSERT INTO Prof
VALUES (1,'SCHMIDT');
INSERT INTO Prof
VALUES (2,'Makki');
Select * From Student,Fach,Prof,HÄLT WHERE Fach.ID=Student.ID AND Hält.ID2=Prof.ID2 AND Fach.ID=Hält.ID and ID2=SCHMIDT;////Wieder kommen
Select Name From Fach,Student WHERE Fach.ID=Student.ID GROUP BY Name HAVING count(note)>3 ;