ELIJAH NDETO SQL-test3.txt

/* ELIJAH NDETO 
TEST THREE SQL
DATASEALS SOFTWARE
*/
--QUESTION 1
-- CASE WHEN STATEMENT WITH A SUBQUERY
SELECT STORE_NAME,
CASE
    WHEN 'Q1' NOT IN (SELECT QUARTER FROM STORE S WHERE S.STORE_NAME = ST.STORE_NAME) THEN 'Q1'
    WHEN 'Q2' NOT IN (SELECT QUARTER FROM STORE S WHERE S.STORE_NAME = ST.STORE_NAME) THEN 'Q2'
    WHEN 'Q3' NOT IN (SELECT QUARTER FROM STORE S WHERE S.STORE_NAME = ST.STORE_NAME) THEN 'Q3'
    WHEN 'Q4' NOT IN (SELECT QUARTER FROM STORE S WHERE S.STORE_NAME = ST.STORE_NAME) THEN 'Q4'
    END AS MISSING_QUARTER
FROM (SELECT DISTINCT STORE_NAME FROM STORE) ST;
--END

-- OUTPUT
STORE_NAME  MISSING_QUATER
S2	Q2
S1	Q3
S3	Q4
-- END


-- QUESTION 2
-- USING SELF JOIN, WHERE CONDITION AND ORDER BY TO SORT
SELECT 
    H1.SEAT_NO AS START_SEAT,
    H3.SEAT_NO AS END_SEAT
FROM HALL H1
JOIN HALL H2 ON H1.SEAT_NO = H2.SEAT_NO - 1
JOIN HALL H3 ON H1.SEAT_NO = H3.SEAT_NO - 2
WHERE H1.IS_EMPTY = 'Y' 
AND H2.IS_EMPTY = 'Y' 
AND H3.IS_EMPTY = 'Y'
ORDER BY H1.SEAT_NO;
--END

-- OUTPUT
START_SEAT END_SEAT
4	6
8	10
9	11
-- END



-- QUESTION 3
-- COUNT(*), SUM(CASE WHEN STATEMENT) WITH UNION ALL
SELECT TEAM_NAME,
  COUNT(*) AS MATCHES_PLAYED,
  SUM(CASE WHEN TEAM_NAME = WINNER THEN 1 ELSE 0 END) AS NO_OF_MATCHES_WON,
  COUNT(*) - SUM(CASE WHEN TEAM_NAME = WINNER THEN 1 ELSE 0 END) AS NO_OF_MATCHES_LOST
FROM (SELECT TEAM_1 AS TEAM_NAME, WINNER FROM FIFA_WORLD_CUP
UNION ALL
SELECT TEAM_2 AS TEAM_NAME, WINNER FROM FIFA_WORLD_CUP) MATCHES
GROUP BY TEAM_NAME
ORDER BY NO_OF_MATCHES_WON DESC;
-- END

-- OUTPUT
TEAM_NAME MATCHES_PLAYED NO_OF_MATCHES_WON NO_OF_MATCHES_LOST
Kenya	2	2	0
Eng	2	1	1
Aus	2	1	1
NZ	1	1	0
Esp	2	0	2
SA	1	0	1
-- END