HackerRank SQL solution

1. Basic select
   

   Revising the select Query I	Revising the select Query II	Select All	Select by ID
   Japanese Cities Attribute	Japanese Cities name	Weather observation station 1	Weather observation station 3
   Weather observation station 4	Weather observation station 5	Weather observation station 6	Weather observation station 7
   Weather observation station 8	Weather observation station 9	Weather observation station 10	Weather observation station 11
   Weather observation station 12	Higher than 75 marks	Employee names	Employee salaries

2. Advanced select
3. Aggregation

Basic Select Solutions

1. Revising the Select Query I Solution

           SELECT *
           FROM CITY
           WHERE COUNTRYCODE='USA'
           AND POPULATION>100000;
       

2. Revising the Select Query II Solution

           SELECT NAME
           FROM CITY
           WHERE COUNTRYCODE='USA'
           AND POPULATION>120000;
       

3. Select All Solution

           SELECT *
           FROM CITY;
       

4. Select By ID Solution

           SELECT *
           FROM CITY
           WHERE ID=1661;
       

5. Japanese Cities Attribute Solution

           SELECT *
           FROM CITY
           WHERE COUNTRYCODE='JPN';
       

6. Japanese Cities Name Solution

           SELECT NAME
           FROM CITY
           WHERE COUNTRYCODE='JPN';
       

7. Weather Observation Station 1 Solution

           SELECT CITY,STATE
           FROM STATION;
       

8. Weather Observation Station 3 Solution

           SELECT DISTINCT CITY
           FROM STATION
           WHERE ID % 2 =0;
       

9. Weather Observation Station 4 Solution

           SELECT COUNT(CITY) - COUNT(DISTINCT CITY)
           FROM STATION;
       

10. Weather Observation Station 5 Solution

            select city,length(city) from station order by length(city) asc,city asc limit 1;
            select city,length(city) from station order by length(city) desc,city asc limit 1;
        

11. Weather Observation Station 6 Solution

            select city
            from station
            where city REGEXP '^(A|E|I|O|U)';
        

12. Weather Observation Station 7 Solution

            select distinct city
            from station
            where city REGEXP '(A|E|I|O|U|a|e|i|o|u)$';
        

13. Weather Observation Station 8 Solution

            select distinct city
            from station
            where city REGEXP '^[AEIOUaeiou].*[AEIOUaeiou]$';
        

14. Weather Observation Station 9 Solution

            select distinct city
            from station
            where city REGEXP '^[^AEIOUaeiou]';
        

15. Weather Observation Station 10 Solution

            select distinct city
            from station
            where city REGEXP '[^AEIOUaeiou]$';
        

16. Weather Observation Station 11 Solution

            SELECT distinct city FROM station WHERE lower(city) not REGEXP '[aeiou]$' or
            city not REGEXP '^[aeiou]' order by city;
        

17. Weather Observation Station 12 Solution

            SELECT distinct city FROM station WHERE lower(city) not REGEXP '[aeiou]$' 
            AND city not REGEXP '^[aeiou]' order by city;
        

18. Higher than 75 marks Solution

            select Name
            from students
            Where Marks>75
            order by 
            right(name,3),
            id;    
        

19. Employee Names Solution

            select name
            from Employee
            order by name;
        

20. Employee salaries Solution

            select name
            from Employee
            where salary > 2000 AND months<10
            order by employee_id;