SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE Bands( Bandname VARCHAR(255) PRIMARY KEY NOT NULL, Gründungsjahr INT, Gründungsland VARCHAR(255), Mitgliederanzahl INT, CONSTRAINT manzahl CHECK ( Mitgliederanzahl > 2 AND Mitgliederanzahl < 100 ) ); CREATE TABLE Persons( Name VARCHAR(255) PRIMARY KEY, Bandname VARCHAR(255) REFERENCES Band(Bandname), InstrumentenID INT REFERENCES Instrument(InstrumentenID), Stimmlage VARCHAR(12), Geburtsdatum DATE, Heimatland VARCHAR(255) ); CREATE TABLE Instrumente( InstrumentenID INT NOT NULL, Firma VARCHAR(255), Modell VARCHAR(255), Baujahr INT(4), PRIMARY KEY (InstrumentenID) ); CREATE TABLE Gigs( EventID INT NOT NULL, Bandname VARCHAR(255) REFERENCES Bands(Bandname), Land VARCHAR(255), Stadt VARCHAR(255), PRIMARY KEY (EventID) ); INSERT INTO Bands ( Bandname, Gründungsjahr, Gründungsland, Mitgliederanzahl ) VALUES ('BAAB', 1972, 'Schweden', 4), ( 'Wildecker Herzbuben', 1964, 'USA', 9 ), ('Sum 42', 1996, 'Kanada', 99), ( 'dwafdef', 2001, 'Deutschland', 95 ); INSERT INTO Persons ( Name, Stimmlage, Geburtsdatum, Heimatland, Bandname, InstrumentenID ) VALUES ( "Max Mustermann", "Mezzosopran", "1990-03-02", "Kanada", "Wildecker Herzbuben", 152 ), ( "Helga", "Bass", "1967-04-01", "Deutschland", "BAAB", 0 ), ( "Horst", "Sopran", "1954-12-01", "Deutschland", "Sum 42", 12 ), ( "Eike", "Bass", "1955-01-01", "Deutschland", "BAAB", 12 ); INSERT INTO Gigs (EventID, Bandname, Land, Stadt) VALUES ( 0, "Wildecker Herzbuben", "Kanada", "Ajax" ), ( 1, "Wildecker Herzbuben", "Kanada", "Toronto" ), (2, "BAAB", "USA", "Los Angeles"); INSERT INTO Instrumente ( InstrumentenID, Firma, Modell, Baujahr ) VALUES (152, "Yamaha", "SVC-110", 1993), (0, "Roland", "HDI-28-J2", 2012); SELECT * FROM Bands; SELECT * FROM Persons; SELECT * FROM Gigs; SELECT * FROM (SELECT A.Name AS Person1, B.Name AS Person2, A.Bandname AS Band1, B.Bandname AS Band2, A.Heimatland FROM Persons A, Persons B WHERE A.Name <> B.Name AND A.Bandname = B.Bandname AND A.Heimatland = B.Heimatland) a JOIN Bands b ON a.Heimatland = b.Gründungsland; SELECT COUNT(*)/2 FROM (SELECT A.Name AS Person1, B.Name AS Person2, A.Heimatland FROM Persons A, Persons B WHERE A.Name <> B.Name AND A.Heimatland = B.Heimatland) a;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear