These are some cool SQL programs:

    WEATHER OBSERVATION STATION 6

                                        
                                            SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[aeiou]';
                                        
                                    
    WEATHER OBSERVATION STATION 7
                                        
                                            SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(CITY, '[aeiou]$');
                                        
                                    
    WEATHER OBSERVATION STATION 8
                                        
                                            SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(CITY, '^[aeiou].*[aeiou]$');
                                        
                                    
    WEATHER OBSERVATION STATION 9
                                        
                                            SELECT DISTINCT CITY FROM STATION WHERE NOT REGEXP_LIKE(CITY, '^[aeiou]');
                                        
                                    
    WEATHER OBSERVATION STATION 10
                                        
                                            SELECT DISTINCT CITY FROM STATION WHERE CITY NOT REGEXP '[aeiou]$'
                                        
                                    
    WEATHER OBSERVATION STATION 11
                                        
                                            SELECT DISTINCT CITY FROM STATION  WHERE CITY NOT REGEXP '^[aeiou].*[aeiou]$';
                                        
                                    
    WEATHER OBSERVATION STATION 12
                                        
                                            SELECT DISTINCT CITY FROM STATION  WHERE CITY NOT REGEXP '[aeiou]$'  AND CITY NOT REGEXP '^[aeiou]';
                                        
                                    
    Higher Than 75 Marks
                                        
                                            SELECT NAME FROM STUDENTS WHERE MARKS > 75 ORDER BY RIGHT(NAME,3), ID;
                                        
                                    
    EMPLOYEE Names
                                    
                                        SELECT NAME FROM EMPLOYEE ORDER BY NAME;
                                    
                                    
    EMPLOYEE Salaries
                                        
                                            SELECT NAME FROM EMPLOYEE WHERE SALARY > 2000 AND MONTHS < 10 ORDER BY EMPLOYEE_ID;
                                        
                                    
    Type of Triangle
                                        
                                            SELECT 
                                            CASE 
                                                WHEN (A >= B + C) OR (B >= A + C) OR (C >= A + B) THEN 'Not A Triangle' 
                                                WHEN ((A = B) AND (A != C)) OR ((A = C) AND (A != B)) OR ((B = C )AND( A != B)) THEN 'Isosceles'
                                                WHEN A = B AND A = C AND B = C THEN 'Equilateral'
                                                ELSE 'Scalene'
                                            END 
                                            FROM TRIANGLES;
                                        
                                    
    The PADS
                                        
                                            SELECT CONCAT(NAME, '(', LEFT(OCCUPATION, 1), ')') FROM OCCUPATIONS
                                            UNION ALL 
                                            SELECT CONCAT('There are a total of ', COUNT(OCCUPATION), ' ', LOWER(OCCUPATION),'s','.')  FROM OCCUPATIONS GROUP BY OCCUPATION ORDER BY 1;
                                        
                                    
    The PADS
                                        
                                            SELECT CONCAT(NAME, '(', LEFT(OCCUPATION, 1), ')') FROM OCCUPATIONS
                                            UNION ALL 
                                            SELECT CONCAT('There are a total of ', COUNT(OCCUPATION), ' ', LOWER(OCCUPATION),'s','.')  FROM OCCUPATIONS GROUP BY OCCUPATION ORDER BY 1;
                                        
                                    
    Revising Aggregations - The Count Function
                                        
                                            SELECT COUNT(ID) FROM CITY 
                                            WHERE POPULATION > 100000;
                                        
                                    
    Revising Aggregations - The Sum Function
                                        
                                            SELECT SUM(POPULATION) FROM CITY 
                                            WHERE DISTRICT = 'CALIFORNIA';
                                        
                                    
    Revising Aggregations - Averages
                                        
                                            SELECT AVG(POPULATION) FROM CITY 
                                            WHERE DISTRICT = 'CALIFORNIA';
                                        
                                    
    Average Population
                                        
                                            SELECT FLOOR(AVG(POPULATION)) FROM CITY;
                                        
                                    
    Japan Population
                                        
                                            SELECT SUM(POPULATION) FROM CITY 
                                            WHERE COUNTRYCODE = 'JPN';
                                        
                                    
    Population Density Difference
                                        
                                            SELECT MAX(POPULATION) - MIN(POPULATION) FROM CITY;
                                        
                                    
    The Blunder
                                        
                                            SELECT CEIL(AVG(SALARY) - AVG(REPLACE(SALARY, '0', ''))) FROM EMPLOYEES;
                                        
                                    
    Top Earners
                                        
                                            SELECT MAX(SALARY * MONTHS), COUNT(*) FROM EMPLOYEE
                                            WHERE (SALARY * MONTHS) = (SELECT MAX(SALARY * MONTHS) FROM EMPLOYEE);
                                        
                                    
    Weather Observation Station 2
                                        
                                            SELECT ROUND(SUM(LAT_N), 2), ROUND(SUM(LONG_W), 2) FROM STATION;
                                        
                                    
    Weather Observation Station 13
                                        
                                            SELECT TRUNCATE(SUM(LAT_N), 4) FROM STATION
                                            WHERE LAT_N > 38.7880 AND LAT_N < 137.2345;
                                        
                                    
    Weather Observation Station 14
                                        
                                            SELECT TRUNCATE(MAX(LAT_N), 4) FROM STATION 
                                            WHERE LAT_N < 137.2345;
                                        
                                    
    Weather Observation Station 15
                                        
                                            SELECT ROUND(LONG_W, 4) FROM STATION
                                            WHERE LAT_N = (SELECT MAX(LAT_N) FROM STATION WHERE LAT_N < 137.2345);
                                        
                                    
    Weather Observation Station 16
                                        
                                            SELECT MIN(ROUND(LAT_N, 4)) FROM STATION  WHERE LAT_N > 38.7780;
                                        
                                    
    Weather Observation Station 17
                                        
                                            SELECT ROUND(LONG_W, 4) FROM STATION WHERE LAT_N = (SELECT MIN(LAT_N) FROM STATION WHERE LAT_N > 38.7780);
                                        
                                    
    Weather Observation Station 18
                                        
                                            SELECT ROUND((MAX(LONG_W) - MIN(LONG_W)) + (MAX(LAT_N) - MIN(LAT_N)),4 ) FROM STATION;
                                        
                                    
    Weather Observation Station 19
                                        
                                            SELECT TRUNCATE(
                                                SQRT( 
                                                    POW(MAX(LAT_N) - MIN(LAT_N), 2 ) + 
                                                    POW(MAX(LONG_W) - MIN(LONG_W), 2 ) 
                                            ), 4) FROM STATION;
                                        
                                    
    Population Census
                                    
                                        SELECT SUM(CITY.POPULATION) FROM CITY
                                        INNER JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE 
                                        WHERE COUNTRY.CONTINENT = 'Asia';
                                    
                                
    African Cities
                                        
                                            SELECT DISTINCT CITY.NAME FROM CITY 
                                            LEFT JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
                                            WHERE COUNTRY.CONTINENT = 'Africa';
                                        
                                    
    Average Population of Each Continent
                                        
                                            SELECT COUNTRY.CONTINENT, FLOOR(AVG(CITY.POPULATION)) FROM CITY 
                                            INNER JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE GROUP BY COUNTRY.CONTINENT;
                                        
                                    
    Weather Observation Station 5
                                        
                                            (SELECT CITY, LENGTH(CITY)
                                            FROM STATION
                                            WHERE LENGTH(CITY) = (SELECT MIN(LENGTH(CITY)) FROM STATION)  ORDER BY CITY
                                            LIMIT 1)
                                            UNION
                                            (SELECT CITY, LENGTH(CITY)
                                            FROM STATION
                                            WHERE LENGTH(CITY) = (SELECT MAX(LENGTH(CITY)) FROM STATION) ORDER BY CITY
                                            LIMIT 1 ) ;
                                        
                                    
    Binary Tree Nodes
                                        
                                            SELECT N, 
                                            CASE 
                                                WHEN P IS NULL THEN 'Root'
                                                WHEN N IN (SELECT P FROM BST) THEN 'Inner'
                                                ELSE 'Leaf'
                                            END AS P
                                            FROM BST ORDER BY N;
                                        
                                    
    New Companies
                                        
                                            SELECT 
                                            COMPANY.COMPANY_CODE, 
                                            COMPANY.FOUNDER, 
                                            COUNT(DISTINCT LEAD_MANAGER.LEAD_MANAGER_CODE), 
                                            COUNT(DISTINCT SENIOR_MANAGER.SENIOR_MANAGER_CODE), 
                                            COUNT(DISTINCT MANAGER.MANAGER_CODE), 
                                            COUNT(DISTINCT EMPLOYEE.EMPLOYEE_CODE) FROM COMPANY JOIN LEAD_MANAGER ON COMPANY.COMPANY_CODE = LEAD_MANAGER.COMPANY_CODE 
                                            JOIN SENIOR_MANAGER ON 
                                            LEAD_MANAGER.COMPANY_CODE = SENIOR_MANAGER.COMPANY_CODE 
                                            JOIN MANAGER ON 
                                            SENIOR_MANAGER.COMPANY_CODE = MANAGER.COMPANY_CODE 
                                            JOIN EMPLOYEE ON 
                                            MANAGER.COMPANY_CODE = EMPLOYEE.COMPANY_CODE 
                                            GROUP BY COMPANY.COMPANY_CODE, COMPANY.FOUNDER ORDER BY COMPANY.COMPANY_CODE,COMPANY.FOUNDER;
                                        
                                    
    Weather Observation Station 20
                                        
                                            WITH RANKS AS (
                                                SELECT LAT_N, RANK() OVER (ORDER BY LAT_N) AS RANKING FROM STATION
                                            )
                                            SELECT ROUND(LAT_N, 4) FROM RANKS
                                            WHERE RANKING IN (
                                                SELECT ROUND(COUNT(*) / 2)
                                                FROM STATION 
                                                WHERE LAT_N IS NOT NULL
                                            );
                                        
                                    
    The Report
                                        
                                            WITH GRADES AS (
                                                SELECT 
                                                CASE 
                                                    WHEN MARKS >= 70 THEN NAME
                                                    ELSE NULL
                                                END AS NAME
                                                , 
                                                CASE 
                                                    WHEN MARKS >= 90 THEN 10
                                                    WHEN MARKS >= 80 THEN 9
                                                    WHEN MARKS >= 70 THEN 8
                                                    WHEN MARKS >= 60 THEN 7
                                                    WHEN MARKS >= 50 THEN 6
                                                    WHEN MARKS >= 40 THEN 5
                                                    WHEN MARKS >= 30 THEN 4
                                                    WHEN MARKS >= 20 THEN 3
                                                    WHEN MARKS >= 10 THEN 2
                                                    WHEN MARKS >= 10 THEN 1
                                                END AS GRADE,
                                                MARKS  FROM STUDENTS
                                            )
                                            SELECT * FROM GRADES ORDER BY GRADE DESC, NAME ASC, MARKS ASC;
                                        
                                    
    Top Competitors
                                        
                                            SELECT HACKER_ID, HACKER_NAME
                                            FROM (
                                                SELECT S.HACKER_ID AS HACKER_ID, 
                                                H.NAME AS HACKER_NAME, 
                                                COUNT(S.SUBMISSION_ID) AS GREATS 
                                                FROM SUBMISSIONS S
                                                JOIN  CHALLENGES C 
                                                ON S.CHALLENGE_ID = C.CHALLENGE_ID 
                                                JOIN DIFFICULTY D
                                                ON C.DIFFICULTY_LEVEL = D.DIFFICULTY_LEVEL
                                                JOIN HACKERS H
                                                ON S.HACKER_ID = H.HACKER_ID 
                                                WHERE S.SCORE = D.SCORE 
                                                GROUP BY S.HACKER_ID, H.NAME
                                            ) AS TEMP_RESULT
                                            WHERE GREATS > 1 
                                            ORDER BY GREATS DESC, HACKER_ID ASC; 
                                        
                                    
    Ollivander's Inventory
                                        
                                            SELECT W.ID, WP.AGE, W.COINS_NEEDED, W.POWER FROM WANDS W
                                            JOIN WANDS_PROPERTY WP ON W.CODE = WP.CODE
                                            JOIN(
                                                SELECT AGE, MIN(COINS_NEEDED) AS MIN_GOLD, POWER FROM WANDS W 
                                                JOIN 
                                                WANDS_PROPERTY WP ON W.CODE = WP.CODE 
                                                WHERE IS_EVIL = 0
                                                GROUP BY WP.AGE, W.POWER
                                            ) AS NON_EVIL ON WP.AGE = NON_EVIL.AGE AND 
                                                W.POWER = NON_EVIL.POWER AND 
                                                W.COINS_NEEDED = NON_EVIL.MIN_GOLD 
                                            ORDER BY W.POWER DESC, WP.AGE DESC;
                                        
                                    
    Challenges
                                        
                                            WITH TEMP_TABLE AS (
                                            SELECT H.HACKER_ID, H.NAME, COUNT(*) AS CHALLENGES_CREATED FROM HACKERS H
                                            INNER JOIN CHALLENGES C ON H.HACKER_ID = C.HACKER_ID GROUP BY H.HACKER_ID, H.NAME
                                            ORDER BY CHALLENGES_CREATED DESC, H.HACKER_ID ASC)
                                            SELECT  HACKER_ID, NAME, CHALLENGES_CREATED FROM TEMP_TABLE
                                            WHERE CHALLENGES_CREATED = (SELECT MAX(CHALLENGES_CREATED) FROM TEMP_TABLE) 
                                            OR
                                            CHALLENGES_CREATED IN
                                            (   SELECT CHALLENGES_CREATED FROM TEMP_TABLE 
                                                    GROUP BY CHALLENGES_CREATED 
                                                    HAVING COUNT(CHALLENGES_CREATED) = 1
                                            );
                                        
                                    
    Contest Leaderboard
                                        
                                            SELECT S.HACKER_ID, H.NAME, SUM(S.MAX_SCORE) AS TOTAL_SCORE 
                                            FROM (
                                                SELECT HACKER_ID, CHALLENGE_ID, MAX(SCORE) AS MAX_SCORE 
                                                FROM SUBMISSIONS
                                                GROUP BY HACKER_ID, CHALLENGE_ID
                                            ) S 
                                            JOIN HACKERS H ON S.HACKER_ID = H.HACKER_ID 
                                            GROUP BY S.HACKER_ID, H.NAME 
                                            HAVING SUM(S.MAX_SCORE) > 0
                                            ORDER BY TOTAL_SCORE DESC, S.HACKER_ID;
                                        
                                    
    SQL Project Planning
                                        
                                            WITH TEMP_TABLE AS (
                                            SELECT START_DATE, END_DATE, 
                                                    ROW_NUMBER() OVER (ORDER BY END_DATE) AS RN 
                                            FROM PROJECTS
                                            )
                                            SELECT MIN(START_DATE) AS MIN_START_DATE,
                                                MAX(END_DATE) AS MAX_END_DATE
                                                FROM TEMP_TABLE 
                                            GROUP BY DATE_SUB(END_DATE, INTERVAL RN DAY)
                                            ORDER BY (MAX_END_DATE - MIN_START_DATE);
                                        
                                    
    Placements
                                        
                                            SELECT S.NAME FROM STUDENTS S 
                                            INNER JOIN FRIENDS F
                                            ON F.ID = S.ID 
                                            INNER JOIN PACKAGES P
                                            ON P.ID = F.FRIEND_ID
                                            INNER JOIN PACKAGES PP 
                                            ON PP.ID = S.ID
                                            WHERE PP.SALARY < P.SALARY
                                            ORDER BY P.SALARY;
                                            
                                        
                                    
    Symmetric Pairs
                                        
                                            WITH TB1 AS ( 
                                                SELECT X, Y , ROW_NUMBER() OVER (ORDER BY X) AS RN  FROM FUNCTIONS F
                                            ),
                                            TB2 AS (
                                                SELECT X, Y, ROW_NUMBER() OVER (ORDER BY X) AS RN2
                                                FROM FUNCTIONS FF
                                            )
                                            SELECT TB1.X, TB1.Y
                                            FROM TB1
                                            INNER JOIN TB2 ON TB1.X = TB2.Y AND TB2.X = TB1.Y AND TB1.RN != TB2.RN2
                                            WHERE TB1.X <= TB1.Y
                                            GROUP BY TB1.X, TB1.Y  ORDER BY TB1.X;
                                        
                                    
    Interviews
                                        
                                            SELECT 
                                                T_T1.CONTEST_ID,
                                                T_T1.HACKER_ID,
                                                T_T1.NAME,
                                                T_T2.TOTAL_SUBMISSIONS,
                                                T_T2.TOTAL_ACCEPTED_SUBMISSIONS,
                                                T_T1.TOTAL_VIEWS,
                                                T_T1.TOTAL_UNIQUE_VIEWS 
                                            FROM (
                                                SELECT 
                                                    C.CONTEST_ID,
                                                    C.HACKER_ID, 
                                                    C.NAME, 
                                                    SUM(VS.TOTAL_VIEWS) AS TOTAL_VIEWS, 
                                                    SUM(VS.TOTAL_UNIQUE_VIEWS) AS TOTAL_UNIQUE_VIEWS 
                                                FROM CONTESTS C 
                                                JOIN COLLEGES COL ON C.CONTEST_ID = COL.CONTEST_ID 
                                                JOIN CHALLENGES CH ON COL.COLLEGE_ID = CH.COLLEGE_ID
                                                JOIN VIEW_STATS VS ON CH.CHALLENGE_ID = VS.CHALLENGE_ID 
                                                GROUP BY C.CONTEST_ID, C.HACKER_ID, C.NAME 
                                                HAVING SUM(VS.TOTAL_VIEWS) > 0 OR SUM(VS.TOTAL_UNIQUE_VIEWS) > 0 
                                            ) AS T_T1
                                            JOIN (
                                                SELECT 
                                                    C.CONTEST_ID, 
                                                    C.HACKER_ID, 
                                                    C.NAME, 
                                                    SUM(SS.TOTAL_SUBMISSIONS) AS TOTAL_SUBMISSIONS, 
                                                    SUM(SS.TOTAL_ACCEPTED_SUBMISSIONS) AS TOTAL_ACCEPTED_SUBMISSIONS
                                                FROM CONTESTS C 
                                                JOIN COLLEGES COL ON C.CONTEST_ID = COL.CONTEST_ID 
                                                JOIN CHALLENGES CH ON COL.COLLEGE_ID = CH.COLLEGE_ID
                                                JOIN SUBMISSION_STATS SS ON CH.CHALLENGE_ID = SS.CHALLENGE_ID 
                                                GROUP BY C.CONTEST_ID, C.HACKER_ID, C.NAME 
                                                HAVING SUM(SS.TOTAL_SUBMISSIONS) > 0 OR SUM(SS.TOTAL_ACCEPTED_SUBMISSIONS) > 0 
                                            ) AS T_T2 ON T_T1.CONTEST_ID = T_T2.CONTEST_ID
                                            ORDER BY T_T1.CONTEST_ID;
                                        
                                    
    Occupations
                                        
                                            SELECT 
                                                MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) AS Doctor,
                                                MAX(CASE WHEN Occupation = 'Professor' THEN Name END) AS Professor,
                                                MAX(CASE WHEN Occupation = 'Singer' THEN Name END) AS Singer,
                                                MAX(CASE WHEN Occupation = 'Actor' THEN Name END) AS Actor
                                            FROM (
                                                SELECT Name, Occupation,
                                                    ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS RowNum
                                                FROM OCCUPATIONS
                                            ) AS NumberedOccupations
                                            GROUP BY RowNum
                                            ORDER BY RowNum;
                                        
                                    
    Draw The Triangle 1
                                        
                                            DELIMITER $$
    
                                            CREATE PROCEDURE PrintStars() BEGIN DECLARE r INT DEFAULT 20;
    
                                            WHILE r > 0 DO
                                                SELECT REPEAT('* ', r) AS pattern;
                                                SET r = r - 1;
                                            END WHILE;
                                            END$$
    
                                            DELIMITER ;
    
                                            CALL PrintStars();
                                        
                                    
    Draw The Triangle 2
                                        
                                            DELIMITER $$ 
                                            CREATE PROCEDURE PrintStarsReverse() BEGIN DECLARE r INT DEFAULT 1;
                                            WHILE r <= 20 DO
                                                SELECT REPEAT('* ', r) as pattern;
                                                SET r = r + 1;
                                            END WHILE;
                                            END$$
                                            
                                            DELIMITER ;
                                            
                                            CALL PrintStarsReverse();
                                        
                                    
    Print Prime Numbers
                                        
                                            WITH RECURSIVE Numbers AS (
                                                SELECT 2 AS L 
                                                UNION
                                                SELECT L + 1
                                                FROM Numbers
                                                WHERE L < 1000
                                            )
                                            SELECT GROUP_CONCAT(L SEPARATOR '&') AS PrimeNumbers
                                            FROM Numbers
                                            WHERE NOT EXISTS (
                                                SELECT 1
                                                FROM Numbers AS N2
                                                WHERE N2.L > 1 AND N2.L < Numbers.L AND Numbers.L % N2.L = 0
                                            ) AND L <= 1000;
                                        
                                    
    15 Days of Learning SQL
                                        
                                            SELECT SUBMISSION_DATE,
                                            (SELECT COUNT(DISTINCT HACKER_ID)
                                                FROM SUBMISSIONS S2
                                            WHERE S2.SUBMISSION_DATE = S1.SUBMISSION_DATE AND
                                                (SELECT COUNT(DISTINCT S3.SUBMISSION_DATE)
                                            FROM SUBMISSIONS S3 WHERE S3.HACKER_ID = S2.HACKER_ID AND S3.SUBMISSION_DATE < S1.SUBMISSION_DATE) = DATEDIFF(S1.SUBMISSION_DATE , '2016-03-01')),
                                                (SELECT HACKER_ID FROM SUBMISSIONS S2 WHERE S2.SUBMISSION_DATE = S1.SUBMISSION_DATE
                                            GROUP BY HACKER_ID ORDER BY COUNT(SUBMISSION_ID) DESC, HACKER_ID LIMIT 1) AS TMP,
                                                (SELECT NAME FROM HACKERS WHERE HACKER_ID = TMP)
                                            FROM
                                                (SELECT DISTINCT SUBMISSION_DATE FROM SUBMISSIONS) S1
                                            GROUP BY SUBMISSION_DATE;