SQL: Hacker Rank Questions 1

Q1. Weather Observation Station 3

SELECT DISTINCT(CITY)
FROM STATION
WHERE ID % 2 = 0

Q2. Weather Observation Station 4

SELECT (COUNT(CITY) - COUNT(DISTINCT(CITY) )) AS DIFFERENCE
FROM STATION

Q3. Weather Observation Station 5

SELECT CITY, LENGTH(CITY) AS LENGTH
FROM STATION
ORDER BY LENGTH, CITY
LIMIT 1;

SELECT CITY, LENGTH(CITY) AS LENGTH
FROM STATION
ORDER BY LENGTH DESC, CITY
LIMIT 1;

Q4. Weather Observation Station 6

SELECT DISTINCT CITY 
FROM STATION 
WHERE LEFT(CITY, 1) IN ("A", "E", "I", "O", "U")
-- WHERE RIGHT(CITY, 1) IN ("A", "E", "I", "O", "U")

Q5. Weather Observation Station 8

SELECT DISTINCT CITY 
FROM STATION 
WHERE LEFT(CITY, 1) IN ("A", "E", "I", "O", "U")
    AND
    RIGHT(CITY, 1) IN ("A", "E", "I", "O", "U")

Q6. Weather Observation Station 9

SELECT DISTINCT CITY 
FROM STATION 
WHERE LEFT(CITY, 1) NOT IN ("A", "E", "I", "O", "U")

Q7. Weather Observation Station 11

SELECT DISTINCT CITY 
FROM STATION 
WHERE LEFT(CITY, 1) NOT IN ("A", "E", "I", "O", "U")
    OR 
    RIGHT(CITY, 1) NOT IN ("A", "E", "I", "O", "U")
    
-- !AND = OR

Q8. Higher Than 75 Marks

SELECT NAME
FROM STUDENTS
WHERE MARKS >75
ORDER BY RIGHT(NAME, 3), ID

Q9. Type of Triangle

SELECT CASE
    WHEN (a=b and b=c) THEN 'Equilateral' 
    WHEN (a+b<=c or b+c<=a or c+a<=b) THEN 'Not A Triangle' 
    WHEN (a!=b and b!=c and c!=a) THEN 'Scalene' 
ELSE 'Isosceles' END
FROM triangles

SELECT CONCAT( Name,'(', LEFT(occupation,1),')' ) FROM OCCUPATIONS ORDER BY Name ASC;

SELECT CONCAT("There are a total of ",COUNT(occupation)," ",LOWER(occupation),"s.") FROM OCCUPATIONS GROUP BY occupation ORDER BY COUNT(occupation) ASC, LOWER(occupation) ASC;