WEATHER OBSERVATION STATION 6
WEATHER OBSERVATION STATION 7
SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[aeiou]';
WEATHER OBSERVATION STATION 8
SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(CITY, '[aeiou]$');
WEATHER OBSERVATION STATION 9
SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(CITY, '^[aeiou].*[aeiou]$');
WEATHER OBSERVATION STATION 10
SELECT DISTINCT CITY FROM STATION WHERE NOT REGEXP_LIKE(CITY, '^[aeiou]');
WEATHER OBSERVATION STATION 11
SELECT DISTINCT CITY FROM STATION WHERE CITY NOT REGEXP '[aeiou]$'
WEATHER OBSERVATION STATION 12
SELECT DISTINCT CITY FROM STATION WHERE CITY NOT REGEXP '^[aeiou].*[aeiou]$';
Higher Than 75 Marks
SELECT DISTINCT CITY FROM STATION WHERE CITY NOT REGEXP '[aeiou]$' AND CITY NOT REGEXP '^[aeiou]';
EMPLOYEE Names
SELECT NAME FROM STUDENTS WHERE MARKS > 75 ORDER BY RIGHT(NAME,3), ID;
EMPLOYEE Salaries
SELECT NAME FROM EMPLOYEE ORDER BY NAME;
Type of Triangle
SELECT NAME FROM EMPLOYEE WHERE SALARY > 2000 AND MONTHS < 10 ORDER BY EMPLOYEE_ID;
The PADS
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;
Revising Aggregations - The Count Function
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 Sum Function
SELECT COUNT(ID) FROM CITY
WHERE POPULATION > 100000;
Revising Aggregations - Averages
SELECT SUM(POPULATION) FROM CITY
WHERE DISTRICT = 'CALIFORNIA';
Average Population
SELECT AVG(POPULATION) FROM CITY
WHERE DISTRICT = 'CALIFORNIA';
Japan Population
SELECT FLOOR(AVG(POPULATION)) FROM CITY;
Population Density Difference
SELECT SUM(POPULATION) FROM CITY
WHERE COUNTRYCODE = 'JPN';
The Blunder
SELECT MAX(POPULATION) - MIN(POPULATION) FROM CITY;
Top Earners
SELECT CEIL(AVG(SALARY) - AVG(REPLACE(SALARY, '0', ''))) FROM EMPLOYEES;
Weather Observation Station 2
SELECT MAX(SALARY * MONTHS), COUNT(*) FROM EMPLOYEE
WHERE (SALARY * MONTHS) = (SELECT MAX(SALARY * MONTHS) FROM EMPLOYEE);
Weather Observation Station 13
SELECT ROUND(SUM(LAT_N), 2), ROUND(SUM(LONG_W), 2) FROM STATION;
Weather Observation Station 14
SELECT TRUNCATE(SUM(LAT_N), 4) FROM STATION
WHERE LAT_N > 38.7880 AND LAT_N < 137.2345;
Weather Observation Station 15
SELECT TRUNCATE(MAX(LAT_N), 4) FROM STATION
WHERE LAT_N < 137.2345;
Weather Observation Station 16
SELECT ROUND(LONG_W, 4) FROM STATION
WHERE LAT_N = (SELECT MAX(LAT_N) FROM STATION WHERE LAT_N < 137.2345);
Weather Observation Station 17
SELECT MIN(ROUND(LAT_N, 4)) FROM STATION WHERE LAT_N > 38.7780;
Weather Observation Station 18
SELECT ROUND(LONG_W, 4) FROM STATION WHERE LAT_N = (SELECT MIN(LAT_N) FROM STATION WHERE LAT_N > 38.7780);
Weather Observation Station 19
SELECT ROUND((MAX(LONG_W) - MIN(LONG_W)) + (MAX(LAT_N) - MIN(LAT_N)),4 ) FROM STATION;
Population Census
SELECT TRUNCATE(
SQRT(
POW(MAX(LAT_N) - MIN(LAT_N), 2 ) +
POW(MAX(LONG_W) - MIN(LONG_W), 2 )
), 4) FROM STATION;
African Cities
SELECT SUM(CITY.POPULATION) FROM CITY
INNER JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
WHERE COUNTRY.CONTINENT = 'Asia';
Average Population of Each Continent
SELECT DISTINCT CITY.NAME FROM CITY
LEFT JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
WHERE COUNTRY.CONTINENT = 'Africa';
Weather Observation Station 5
SELECT COUNTRY.CONTINENT, FLOOR(AVG(CITY.POPULATION)) FROM CITY
INNER JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE GROUP BY COUNTRY.CONTINENT;
Binary Tree Nodes
(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 ) ;
New Companies
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;
Weather Observation Station 20
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;
The Report
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
);
Top Competitors
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;
Ollivander's Inventory
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;
Challenges
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;
Contest Leaderboard
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
);
SQL Project Planning
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;
Placements
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);
Symmetric Pairs
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;
Interviews
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;
Occupations
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;
Draw The Triangle 1
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 2
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();
Print Prime Numbers
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();
15 Days of Learning SQL
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;
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;