SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE SPORTISTA ( ID int, PRIMARY KEY (ID), LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255), Plata int ); CREATE TABLE KLUB ( ID int, PRIMARY KEY (ID), Naziv varchar(255), Lokacija varchar(255), Sport varchar(255), Starost int ); CREATE TABLE CLAN ( ID int, PRIMARY KEY (ID), ID_SPORTISTE int, ID_KLUBA int, Tim varchar(255), FOREIGN KEY (ID_KLUBA) REFERENCES KLUB(ID), FOREIGN KEY (ID_SPORTISTE) REFERENCES SPORTISTA(ID) ); INSERT INTO SPORTISTA(ID,LastName,FirstName,Address,City,Plata) Values(18135,'Dojcinovic','Filip','Strahinjica Bana','Leskovac',500); INSERT INTO SPORTISTA(ID,LastName,FirstName,Address,City,Plata) Values(18136,'Stojkovic','Aleksandar','Strahinjica Bana','Vlasotince',1000); INSERT INTO SPORTISTA(ID,LastName,FirstName,Address,City,Plata) Values(18137,'Stankovic','Stefan','Strahinjica Bana','Nis',800); INSERT INTO SPORTISTA(ID,LastName,FirstName,Address,City,Plata) Values(18138,'Stojiljkovic','Nikola','Strahinjica Bana','Nis',750); INSERT INTO SPORTISTA(ID,LastName,FirstName,Address,City,Plata) Values(18139,'Trajkovic','Filip','Strahinjica Bana','Leskovac',350); INSERT INTO SPORTISTA(ID,LastName,FirstName,Address,City,Plata) Values(18140,'Milenovic','Stefan','Strahinjica Bana','Paracin',900); INSERT INTO SPORTISTA(ID,LastName,FirstName,Address,City,Plata) Values(18141,'Tarenceo','Tao','Strahinjica Bana','Madrid',1500); INSERT INTO SPORTISTA(ID,LastName,FirstName,Address,City,Plata) Values(18142,'Edward','Witten','Strahinjica Bana','America',1500); INSERT INTO KLUB(ID,Naziv,Lokacija,Sport,Starost) Values(111,'FK Crvena Zvezda','Ljutice Bogdana','Fudbal',75); INSERT INTO KLUB(ID,Naziv,Lokacija,Sport,Starost) Values(222,'FK Partizan','Ljutice Bogdana','Fudbal',70); INSERT INTO KLUB(ID,Naziv,Lokacija,Sport,Starost) Values(333,'FK Real Madrid','Madrid','Fudbal',110); INSERT INTO CLAN(ID,ID_SPORTISTE,ID_KLUBA) Values(101,18135,111); INSERT INTO CLAN(ID,ID_SPORTISTE,ID_KLUBA) Values(105,18136,111); INSERT INTO CLAN(ID,ID_SPORTISTE,ID_KLUBA) Values(158,18137,111); INSERT INTO CLAN(ID,ID_SPORTISTE,ID_KLUBA) Values(100,18138,111); INSERT INTO CLAN(ID,ID_SPORTISTE,ID_KLUBA) Values(1,18139,111); INSERT INTO CLAN(ID,ID_SPORTISTE,ID_KLUBA) Values(1010101,18140,111); INSERT INTO CLAN(ID,ID_SPORTISTE,ID_KLUBA) Values(102131,18141,333); INSERT INTO CLAN(ID,ID_SPORTISTE,ID_KLUBA) Values(551,18142,333); INSERT INTO CLAN(ID,ID_SPORTISTE,ID_KLUBA) Values(442,18135,333); INSERT INTO CLAN(ID,ID_SPORTISTE,ID_KLUBA) Values(211211,18136,333); UPDATE SPORTISTA SET PLATA=PLATA-PLATA*0.03 WHERE LastName='Dojcinovic' OR FirstName='Dojcinovic'; SELECT Naziv,Lokacija,Sport FROM KLUB INNER JOIN CLAN ON CLAN.ID_KLUBA=KLUB.ID INNER JOIN SPORTISTA ON CLAN.ID_SPORTISTE=SPORTISTA.ID GROUP BY Naziv,Lokacija,Sport HAVING COUNT(*)=( SELECT MAX(COUNT(*)) FROM KLUB INNER JOIN CLAN ON CLAN.ID_KLUBA=KLUB.ID INNER JOIN SPORTISTA ON CLAN.ID_SPORTISTE=SPORTISTA.ID GROUP BY Naziv); SELECT Naziv,Sport,COUNT(*) FROM KLUB INNER JOIN CLAN ON CLAN.ID_KLUBA=KLUB.ID INNER JOIN SPORTISTA ON CLAN.ID_SPORTISTE=SPORTISTA.ID GROUP BY NAZIV, SPORT HAVING COUNT(*)=( SELECT MAX(COUNT(*)) FROM KLUB INNER JOIN CLAN ON CLAN.ID_KLUBA=KLUB.ID INNER JOIN SPORTISTA ON CLAN.ID_SPORTISTE=SPORTISTA.ID WHERE SPORTISTA.City='Nis' or SPORTISTA.City='Vlasotince' GROUP BY Naziv); SELECT LastName,FirstName,COUNT (*),Plata FROM SPORTISTA INNER JOIN CLAN ON CLAN.ID_SPORTISTE=SPORTISTA.ID INNER JOIN KLUB ON CLAN.ID_KLUBA=KLUB.ID GROUP BY LastName,FirstName,Plata; CREATE VIEW SPORTISTA_KLUB AS SELECT LastName,Naziv,Plata FROM SPORTISTA,KLUB WHERE SPORTISTA.City!=KLUB.Lokacija; SELECT DISTINCT LastName FROM SPORTISTA_KLUB Where Plata=1000; SELECT LastName FROM SPORTISTA WHERE SPORTISTA.ID IN (SELECT ID_SPORTISTE FROM CLAN GROUP BY ID_SPORTISTE HAVING COUNT(*)>1); DELETE SPORTISTA FROM SPORTISTA INNER JOIN CLAN ON CLAN.ID_SPORTISTE=SPORTISTA.ID INNER JOIN KLUB ON CLAN.ID_KLUBA=KLUB.ID WHERE COUNT(*)>1 GROUP BY LastName; DELETE * FROM SPORTISTA;