Hacker Rank SQL Practice Solution 1-15

1. Revising the Select Query I

select * from CITY where population>100000 AND countrycode='USA';

2. Revising the Select Query II
SELECT NAME FROM CITY WHERE POPULATION>120000 AND COUNTRYCODE='USA'

3. Select By ID
SELECT * FROM CITY WHERE ID = 1661;

4. Japanese Cities' Attributes
SELECT * FROM CITY WHERE COUNTRYCODE LIKE '%JPN%';

5. Japanese Cities' Names

SELECT NAME FROM CITY WHERE COUNTRYCODE LIKE '%JPN%';

6. Weather Observation Station 1
SELECT CITY, STATE FROM STATION ORDER BY CITY DESC;

7. Weather Observation Station 2
SELECT ROUND(SUM(LAT_N),2), ROUND(SUM(LONG_W),2) FROM station

8. Weather Observation Station 3
SELECT DISTINCT CITY FROM STATION WHERE MOD(ID, 2) = 0;

9. Weather Observation Station 4
SELECT COUNT(CITY) - COUNT(DISTINCT CITY) FROM STATION;


10. Weather Observation Station 5
(
    SELECT CITY, LENGTH(CITY) FROM STATION
    ORDER BY LENGTH(CITY) ASC, CITY
    LIMIT 1
) UNION (
SELECT CITY, LENGTH(CITY) FROM STATION
    ORDER BY LENGTH(CITY) DESC, CITY
    LIMIT 1

)



11. Weather Observation Station 6
/*
Enter your query here.
*/

SELECT DISTINCT
CITY
FROM STATION
WHERE lower(substr(CITY,1,1)) in ('a','e','i','o','u') GROUP BY CITY

12. Weather Observation Station 7 MYSQL
/*
Enter your query here.
*/
SELECT DISTINCT
CITY
FROM STATION
WHERE RIGHT(CITY, 1) IN ('a', 'e', 'i', 'o', 'u');


13. Weather Observation Station 8 MYSQL
/*
Enter your query here.
*/
SELECT DISTINCT
CITY
FROM STATION
WHERE RIGHT(CITY, 1) IN ('a', 'e', 'i', 'o', 'u') and LEFT(CITY, 1) IN ('a', 'e', 'i', 'o', 'u');


14. Weather Observation Station 9 MYSQL
/*
Enter your query here.
*/
SELECT DISTINCT
CITY
FROM STATION
WHERE lower(substr(CITY,1,1)) in ('b', 'c', 'd', 'f', 'g', 'h', 'j', 'k', 'l', 'm', 'n', 'p', 'q', 'r', 's', 't', 'v', 'w', 'x', 'y','z') GROUP BY CITY

15. Weather Observation Station 9 MYSQL
/*
Enter your query here.
*/
SELECT DISTINCT
CITY
FROM STATION
WHERE RIGHT(CITY, 1) IN ('b', 'c', 'd', 'f', 'g', 'h', 'j', 'k', 'l', 'm', 'n', 'p', 'q', 'r', 's', 't', 'v', 'w', 'x', 'y','z');