SQL Statements For FIFA World Cup Matches Were Used To Analyze Data.

Write an SQL statement to display match ID for the matches that starts after 16:00. Sort the result by kick-Off in descending order.Write an SQL statement to display the country and its continent whose FIFA Rank within a range of 2 to 5. Sort the result by FIFA Rank in an ascending order. The country and the continent should be forced to be in lower case.

Write an SQL statement to display ticket ID and block number of the tickets that are either in block 'A' or in block 'B'. Sort the result by block number in an ascending order. Write an SQL statement to display match ID and kick off time of the matches played at stadium "KAZAN ARENA". Each kick off time should start with the text “starts at”.

Write an SQL statement to display the first 3 characters of a country name whose manager is blank ( change the alias to “COUNTRY INITIALS”), also display the match date and kick off time. Sort the result by match date in descending order.Write an SQL statement to display fan ID and fan name for those who’ve purchased at least one ticket. [JOIN MUST BE USED] Write an SQL statement to display fan ID and fan name for those who’ve purchased at least one ticket. Write an SQL statement to display fan categories, the number of tickets in each category the fan bought (change the alias to “NUMBER OF TICKETS PURCHASED”) along with the sum of prices (change the alias to 'TOTAL PRICES') in each category the fans paid. Sort the result by total price in an ascending order.

Write an SQL statement to display match ID and the count of tickets sold for each match ONLY if the number of tickets sold of this match is more than 1 and the open year of the stadium on which the match will be played is not 2013.Write an SQL statement to display the stadium name and the number of matches played in each stadium (change the alias to “NUMBER OF MATCHES”). Display only the top two stadiums where most matches were held. Sort the result by the number of matches played on each stadium in a descending order.

Write an SQL statement to display team name and its manager if Croatia is one of the teams in any match (either as a first or a second team). Manager’s name should be displayed as initialisation of first name followed by a dot and then the last name. The alias of the manager column should be changed to “Manager Initials”. For example, if manager’s name is David Smith, it will be displayed as D. Smith.

Display match ID for the matches that starts after 16:00 and sort by kick-off time

/******* TASK 1: AFTER THIS COMMENT, INSERT YOUR CODE TO CREATE THE FIVE TABLES HERE (PUT THEM IN THE CORRECT ORDER OF EXECUTION) ***********/
CREATE TABLE STADIUM
(
STADIUMID CHAR(3) PRIMARY KEY,
STADIUMNAME VARCHAR(30),
STADIUMCITY VARCHAR(20),
CAPACITY INT,
OPENYEAR YEAR
);

CREATE TABLE FAN
(
FANID CHAR(9) PRIMARY KEY,
FANNAME VARCHAR(25),
FANNATIONALITY VARCHAR(30),
FANCATEGORY CHAR(7)
);

CREATE TABLE TEAM
(
TEAMID CHAR(4) PRIMARY KEY,
COUNTRY VARCHAR(20),
CONTINENT VARCHAR(15),
FIFARANK TINYINT,
TEAMGROUP CHAR(1),
MANAGER VARCHAR(20),
NICKNAME VARCHAR(25)
);

CREATE TABLE MATCHES
(
MATCHID CHAR(5) PRIMARY KEY,
FIRSTTEAM  CHAR(4),
SECONDTEAM  CHAR(4),
MATCHDATE DATE,
KICKOFF TIME,
STADIUMID CHAR(3),
FOREIGN KEY (STADIUMID) REFERENCES STADIUM (STADIUMID),
FOREIGN KEY (FIRSTTEAM) REFERENCES TEAM(TEAMID),
FOREIGN KEY (SECONDTEAM) REFERENCES TEAM(TEAMID)
);

CREATE TABLE TICKET (
    TICKETID CHAR(15),
    BLOCKNO CHAR(4),
    ROWNO CHAR(4),
    SEATNO CHAR(4),
    PRICE SMALLINT,
    MATCHID CHAR(5),
    FANID CHAR(9),
    CONSTRAINT TICKET_PK PRIMARY KEY (TICKETID),
    CONSTRAINT TICKET_FK1 FOREIGN KEY (MATCHID)
        REFERENCES MATCHES (MATCHID),
    CONSTRAINT TICKET_FK2 FOREIGN KEY (FANID)
        REFERENCES FAN (FANID)
);

/******* TASK 2: AFTER THIS COMMENT, INSERT YOUR CODE TO POPULATE THE DATA FOR THE FIVE TABLES HERE  (PUT THEM IN THE CORRECT ORDER OF EXECUTION) ***********/

INSERT INTO STADIUM (STADIUMID,STADIUMNAME,STADIUMCITY,CAPACITY,OPENYEAR) VALUES
('S01','LUZHNIKI STADIUM','MOSCOW',80000,1956),
('S02','SAINT PETERSBURG STADIUM','SAINT PETERSBURG',67000,2017),
('S03','FISHT STADIUM','SOCHI',48000,2013),
('S04','KAZAN ARENA','KAZAN',45000,2013),
('S05','SAMARA ARENA','SAMARA',45000,2018),
('S06','SPARTAK STADIUM','MOSCOW',42000,2014);

INSERT INTO FAN(FANID,FANNAME,FANNATIONALITY,FANCATEGORY) VALUES 
('F001','MONICA JONES','AMERICAN','SILVER'),
('F002','DMITRIY VLADIMIR','RUSSIAN','GOLD'),
('F003','ELLEN SMITH','AUSTRALIAN','SILVER'),
('F004','MR. BEAN','BRISTISH','GOLD'),
('F005','IGOR MIKHAIL','RUSSIAN','BRONZE'),
('F006','ALBERTO HERNANDEZ','BRAZILIAN','SILVER'),
('F007','NATALIA PAKLINA','RUSSIAN','BRONZE'),
('F008','NANCY AJRAM','LEBANESE','SILVER'),
('F009','JIE CHEN','CHINESE','GOLD'),
('F010','MAX CAMERON','AMERICAN','SILVER');

INSERT INTO TEAM VALUES ("T1","ARGENTINA","SOUTH AMERICA",5,"D","JORGE SAMPAOLI","WHITE AND SKY BLUES");
INSERT INTO TEAM VALUES ("T2","GERMANY","EUROPE",1,"F","JOACHIM LOW",NULL);
INSERT INTO TEAM VALUES ("T3","RUSSIA","ASIA",70,"A","STANISLAV CHERCHESOV","SBORNAYA (TEAM)");
INSERT INTO TEAM VALUES ("T4","SOUTH KOREA","ASIA",57,"F","SHIN TAE-YONG","ASIAN TIGERS");
INSERT INTO TEAM VALUES ("T5","SPAIN","EUROPE",10,"B","JULEN LOPETEGUI",NULL);
INSERT INTO TEAM VALUES ("T6","SWEDEN","EUROPE",24,"F","JANNE ANDERSSON",NULL);
INSERT INTO TEAM VALUES ("T7","SWITZERLAND","EUROPE",6,"E","VLADIMIR PETKOVIC","THE RED CRUSADERS");
INSERT INTO TEAM VALUES ("T8","TUNISIA","AFRICA",21,"G","NABIL MAALOUL","THE EAGLES OF CARTHAGE");
INSERT INTO TEAM VALUES ("T9","URUGUAY","SOUTH AMERICA",14,"A","OSCAR TABAREZ","CHARRUAS");
INSERT INTO TEAM VALUES ("T10","AUSTRALIA","OCEANS",36,"C","BERT VAN MARWIJK","SOCCEROOS");
INSERT INTO TEAM VALUES ("T11","BELGIUM","EUROPE",3,"G","ROBERTO MARTÍNEZ","RED DEVILS");
INSERT INTO TEAM VALUES ("T12","BRAZIL","SOUTH AMERICA",2,"E","TITE ","THE SELECTION");
INSERT INTO TEAM VALUES ("T13","COLOMBIA","SOUTH AMERICA",16,"H","JOSE PEKERMAN","THE COFFEE GROWERS");
INSERT INTO TEAM VALUES ("T14","COSTA RICA","SOUTH AMERICA",23,"E",NULL,"LOS TICOS");
INSERT INTO TEAM VALUES ("T15","CROATIA","EUROPE",20,"D","ZLATKO DALIC ","THE BLAZERS");
INSERT INTO TEAM VALUES ("T16","DENMARK","EUROPE",12,"C","AGE HAREIDE","DANISH DYNAMITE");
INSERT INTO TEAM VALUES ("T17","EGYPT","AFRICA",45,"A","HECTOR CUPER","PHARAOHS");
INSERT INTO TEAM VALUES ("T18","ENGLAND","EUROPE",13,"G","GARETH SOUTHGATE ","THREE LIONS");
INSERT INTO TEAM VALUES ("T19","FRANCE","EUROPE",7,"C","DIDIER DESCHAMPS","THE BLUES");
INSERT INTO TEAM VALUES ("T20","ICELAND","EUROPE",22,"D","HEIMIR HALLGRIMSSON","OUR BOYS");
INSERT INTO TEAM VALUES ("T21","IRAN","ASIA",37,"B","CARLOS QUEIROZ","TEAM MELLI");
INSERT INTO TEAM VALUES ("T22","JAPAN","ASIA",61,"H","VAHID HALILHODZIC","BLUE SAMURAI");
INSERT INTO TEAM VALUES ("T23","MEXICO","SOUTH AMERICA",15,"F","JUAN CARLOS OSORIO ","EL TRICOLOR");
INSERT INTO TEAM VALUES ("T24","MOROCCO","AFRICA",41,"B","HERVE RENARD","ATLAS LIONS");
INSERT INTO TEAM VALUES ("T25","NIGERIA","AFRICA",48,"D","GERNOT ROHR","SUPER EAGLES");
INSERT INTO TEAM VALUES ("T26","PANAMA","SOUTH AMERICA",55,"G","HERNAN DARIO GOMEZ","THE RED TIDE");
INSERT INTO TEAM VALUES ("T27","PERU","SOUTH AMERICA",11,"C","RICARDO GARECA","LOS INCAS");
INSERT INTO TEAM VALUES ("T28","POLAND","EUROPE",8,"H","ADAM NAWALKA","THE POLISH EAGLES");
INSERT INTO TEAM VALUES ("T29","PORTUGAL","EUROPE",4,"B","FERNANDO SANTOS","TEAM OF THE CASTLES");
INSERT INTO TEAM VALUES ("T30","SAUDI ARABIA","ASIA",67,"A","BERT VAN MARWIJK","THE GREEN FALCONS");
INSERT INTO TEAM VALUES ("T31","SENEGAL","AFRICA",27,"H","ALIOU CISSE","THE LIONS OF TERANGA");
INSERT INTO TEAM VALUES ("T32","SERBIA","EUROPE",34,"E","MLADEN KRSTAJIC","THE EAGLES");

INSERT INTO MATCHES(MATCHID,FIRSTTEAM,SECONDTEAM,MATCHDATE,KICKOFF,STADIUMID) VALUES
('M01','T9','T17','2018-06-14','15:00:00','S01'),
('M02','T5','T29','2018-06-14','16:00:00','S02'),
('M03','T10','T19','2018-06-16','18:00:00','S03'),
('M04','T10','T16','2018-06-19','20:00:00','S02'),
('M05','T1','T15','2018-06-19','20:00:00','S04'),
('M06','T2','T4','2019-06-20','15:00:00','S06'),
('M07','T11','T19','2018-07-15','20:00:00','S01'),
('M08','T15','T19','2018-07-15','16:00:00','S02');

INSERT INTO TICKET(TICKETID,BLOCKNO,ROWNO,SEATNO,PRICE,MATCHID,FANID) VALUES
('Tic1','A112',23,19,150,'M01','F001'),
('Tic10','D109',9,20,500,'M06','F009'),
('Tic2','B75',45,11,185,'M02','F003'),
('Tic3','D109',7,21,200,'M02','F005'),
('Tic4','B80',41,8,250,'M03','F001'),
('Tic5','E123',23,15,300,'M03','F004'),
('Tic6','A266',17,24,375,'M05','F002'),
('Tic7','B45',25,11,350,'M05','F009'),
('Tic8','F11',23,14,300,'M06','F007'),
('Tic9','C233',7,23,750,'M08','F001');

/******* TASK 3: AFTER THIS COMMENT, INSERT YOUR QUERY HERE ***********/

SELECT MATCHID, KICKOFF FROM MATCHES 
WHERE KICKOFF>'16:00:00'
ORDER BY KICKOFF DESC;

/******* TASK 4: AFTER THIS COMMENT, INSERT YOUR QUERY HERE ***********/

SELECT LOWER(COUNTRY), LOWER(CONTINENT), FIFARANK 
FROM TEAM
WHERE FIFARANK BETWEEN 2 AND 5
ORDER BY FIFARANK;

/******* TASK 5: AFTER THIS COMMENT, INSERT YOUR QUERY HERE ***********/

SELECT TICKET.TICKETID, TICKET.BLOCKNO, TEAM.TEAMGROUP
FROM TICKET, MATCHES, TEAM
WHERE MATCHES.MATCHID=TICKET.MATCHID
AND TEAM.TEAMID=MATCHES.FIRSTTEAM
AND (TEAM.TEAMGROUP='A' 
OR TEAM.TEAMGROUP='B')
ORDER BY TICKET.BLOCKNO;

/******* TASK 6: AFTER THIS COMMENT, INSERT YOUR QUERY HERE ***********/

SELECT MATCHES.MATCHID, 
CONCAT('starts at',' ',MATCHES.KICKOFF) AS 'Kick Off time',
STADIUM.STADIUMNAME
FROM MATCHES, STADIUM
WHERE STADIUM.STADIUMID=MATCHES.STADIUMID
AND STADIUM.STADIUMNAME='KAZAN ARENA';

/******* TASK 7: AFTER THIS COMMENT, INSERT YOUR QUERY HERE ***********/

SELECT LEFT(T.COUNTRY,3) AS 'COUNTRY INITIALS', 
M.MATCHDATE,M.KICKOFF,T.MANAGER FROM MATCHES M, TEAM T WHERE
T.MANAGER IS NULL 
ORDER BY M.MATCHDATE DESC;

/******* TASK 8: AFTER THIS COMMENT, INSERT YOUR QUERY HERE ***********/

SELECT DISTINCT F.FANID, F.FANNAME FROM FAN F 
INNER JOIN TICKET T 
ON F.FANID =T.FANID;

/******* TASK 9: AFTER THIS COMMENT, INSERT YOUR QUERY HERE ***********/

SELECT F.FANID, F.FANNAME FROM FAN F 
WHERE F.FANID IN
(SELECT DISTINCT FANID FROM TICKET)
AND F.FANID IS NOT NULL;

/******* TASK 10: AFTER THIS COMMENT, INSERT YOUR QUERY HERE ***********/

SELECT F.FANCATEGORY, COUNT(T.TICKETID) AS 'NUMBER OF TICKETS PURCHASED',
SUM(T.PRICE) AS 'TOTAL PRICES' FROM TICKET T,FAN F
WHERE F.FANID=T.FANID
GROUP BY F.FANCATEGORY
ORDER BY SUM(T.PRICE);

/******* TASK 11: AFTER THIS COMMENT, INSERT YOUR QUERY HERE ***********/

SELECT T.MATCHID,COUNT(T.TICKETID) FROM TICKET T,MATCHES M,STADIUM S
WHERE S.STADIUMID=M.STADIUMID
AND M.MATCHID=T.MATCHID
AND S.OPENYEAR <>2013
GROUP BY T.MATCHID
HAVING COUNT(T.TICKETID)>1 ;

/******* TASK 12: AFTER THIS COMMENT, INSERT YOUR QUERY HERE ***********/

SELECT S.STADIUMNAME,COUNT(M.STADIUMID) 'NUMBER OF MATCHES'
FROM STADIUM S,MATCHES M
WHERE S.STADIUMID=M.STADIUMID
GROUP BY M.STADIUMID 
ORDER BY COUNT(M.STADIUMID) DESC LIMIT 2;

/******* TASK 13: AFTER THIS COMMENT, INSERT YOUR QUERY HERE ***********/

SELECT T.COUNTRY,
CONCAT(    CONCAT(
        LEFT(SUBSTRING_INDEX(T.MANAGER, ' ', 1),1)
    ,'.'),SUBSTRING_INDEX(T.MANAGER, ' ', 1)) AS 'Manager Initials',T1.COUNTRY,
    CONCAT(    CONCAT(
        LEFT(SUBSTRING_INDEX(T1.MANAGER, ' ', 1),1)
    ,'.'),SUBSTRING_INDEX(T1.MANAGER, ' ', 1)) AS 'Manager Initials'FROM TEAM T,MATCHES M ,TEAM T1
WHERE ((T.TEAMID=M.FIRSTTEAM AND M.SECONDTEAM=T1.TEAMID))
 AND(T.COUNTRY='CROATIA' OR T1.COUNTRY='CROATIA')
GROUP BY T.COUNTRY;

/******* TASK 14: AFTER THIS COMMENT, INSERT YOUR QUERY HERE ***********/

CREATE VIEW BRONZE AS 
SELECT F.FANNAME, M.MATCHID, S.STADIUMNAME FROM FAN F, MATCHES M, STADIUM S,TICKET T
WHERE 
M.STADIUMID=S.STADIUMID AND
T.FANID=F.FANID AND
T.MATCHID=M.MATCHID AND
F.FANCATEGORY='BRONZE';

/******* TASK 15: AFTER THIS COMMENT, INSERT YOUR QUERY HERE ***********/

CREATE PROCEDURE FANMATCHES(IN NAME VARCHAR(255))
 BEGIN
 SELECT M.MATCHID, S.STADIUMNAME, T.SEATNO, M.KICKOFF
 FROM MATCHES M, STADIUM S, TICKET T,FAN F
 WHERE M.STADIUMID=S.STADIUMID AND
 M.MATCHID=T.MATCHID AND
 F.FANID=T.FANID AND
 F.FANNAME=NAME;
 END //
DELIMITER ;

CALL FANMATCHES('MONICA JONES');