[MySql] HackerRank challenges - BASIC 1

BASIC

 

 

Revising the Select Query 1

Select * 
from CITY 
where POPULATION >100000 AND COUNTRYCODE = "USA";

 

select All

Select * From CITY

 

select by id

Select * FROM CITY
WHERE ID=1661;

 

Japanese Cities' attributes

Select * FROM CITY
WHERE COUNTRYCODE = 'JPN';

 

Japanese Cities' Name

SELECT NAME FROM CITY
WHERE COUNTRYCODE='JPN';

 

Weather Observation Station 1

SELECT CITY, STATE FROM STATION;

 

Weather Observation Station 3

SELECT distinct CITY FROM STATION
WHERE MOD(ID,2)=0;

 

 

Weather Observation Station 4

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

 

Weather Observation Station 6

SELECT DISTINCT CITY
FROM STATION
WHERE (CITY LIKE 'a%'
       OR CITY LIKE 'e%'
       OR CITY LIKE 'i%'
       OR CITY LIKE 'o%'
       OR CITY LIKE 'u%');

 

• REGEXP : 정규표현식 사용

SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '^[aeiou]';

참고

https://codingspooning.tistory.com/entry/MySQL-%EC%A0%95%EA%B7%9C%ED%91%9C%ED%98%84%EC%8B%9D-%EA%B2%80%EC%83%89%ED%95%98%EA%B8%B0-REGEXP-LIKE

[MySQL] 정규표현식 검색하기 REGEXP, LIKE

 

 

Weather Observation Station 7

SELECT DISTINCT CITY FROM STATION
WHERE CITY REGEXP '[aeiou]$';

 

Weather Observation Station 8

SELECT DISTINCT CITY FROM STATION
WHERE CITY REGEXP '^[aeiou]' 
    AND CITY REGEXP '[aeiou]$';

 

Weather Observation Station 9

SELECT DISTINCT CITY FROM STATION
WHERE CITY NOT REGEXP '^[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]$'
OR CITY NOT REGEXP '^[aeiou]';

 

Weather Observation Station 12

SELECT DISTINCT CITY FROM STATION
WHERE CITY NOT REGEXP '[aeiou]$'
AND CITY NOT REGEXP '^[aeiou]';

 

Higher Than 75 Marks

Check point 

• 이름의 마지막 3글자로 정렬 => ORDER BY RIGHT (NAME, 3)
• + 이름 중복 시 ID 로 정렬 => ORDER BY RIGHT (NAME,3), ID  //두개 다 만족시

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

• case 문 사용하기

SELECT CASE
    WHEN A+B>C AND B+C>A AND A+C>B THEN
        CASE 
            WHEN A=B AND B=C THEN 'Equilateral'
            WHEN A=B OR B=C OR A=C THEN 'Isosceles'
            ELSE 'Scalene'
        END
    ELSE 'Not A Triangle'
    END
FROM TRIANGLES;

 

Revising Aggregations - The Count Function

SELECT COUNT(COUNTRYCODE)
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 ROUND(AVG(POPULATION))
FROM CITY;

 

Japan Population

SELECT SUM(POPULATION) FROM CITY
WHERE COUNTRYCODE = 'JPN';

 

Population Density Difference

SELECT MAX(POPULATION)-MIN(POPULATION)
FROM CITY;