구름 아카이브

언젠가 활용될 지 모를 sql 고유값 만들기

아마 LEFT, INNER 등 조인을 통해 테이블 변환이 필요할 때 활용할 수 있을 것 같다.

CONCAT의 괄호 끝에 ' 넣어주고, 컬럼들 사이에 ,',', 넣어주면 끝이다.

전체 row갯수와 중복값을 제거한 row갯수가 같을 때만 활용해야한다.

ex)

SELECT ONLINE_SALE_ID
      ,USER_ID
      ,PRODUCT_ID
      ,SALES_AMOUNT
      ,concat(online_sale_id,',',user_id,',',PRODUCT_ID,',',SALES_AMOUNT) AS new
FROM ONLINE_SALE

전체 row 갯수 122

새로운 컬럼 new가 생성되고, 문자열 조합으로 이루어진 값을 확인할 수 있다.

SQL Aggregate 함수 = SUM, COUNT, AVG...   AVG, COUNT는 null은 세지 않음


COUNT
--------------------------------------------------------------------------
SELECT COUNT(*) FROM Products  
>>> 프로덕트의 행들, 데이터 레코드의 개수 세기, null 값 포함

SELECT COUNT(Price) FROM Products AS COUNT(Price) 
>>> 특정 컬럼의 행 갯수

SELECT COUNT(DISTINCT Price) FROM Products AS COUNT(Price)
>>> 중복값은 제외하고 셀 때  + null 값 미포함 


SUM, AVG(null 계산에서 제외처리됨 )
--------------------------------------------------------------------------
SELECT SUM(Price) FROM Products

SELECT AVG(Price) FROM Products



평균 구할 때, null 값을 0으로 처리해주고 싶을 때
--------------------------------------------------------------------------
SELECT SUM(Price)/COUNT(*) FROM Products
>>> 전체 로우 수로 나누기


최대값, 최소값
--------------------------------------------------------------------------
SELECT MAX(Price) FROM Products

SELECT MIN(Price) FROM Products


SELECT COUNT(Price), MAX(Price), MIN(Price) FROM Products
--------------------------------------------------------------------------


공급자 기준, 평균가격 
--------------------------------------------------------------------------
SELECT SupplierID, AVG(Price) FROM Products
GROUP BY SupplierID


+ 기준 1개 추가
--------------------------------------------------------------------------
SELECT SupplierID, CategoryID, AVG(Price) AS AVG_Price FROM Products
GROUP BY SupplierID, CategoryID


더 나은 코드 스타일 + 코드 가독성
--------------------------------------------------------------------------
SELECT SupplierID
 , CategoryID
 , AVG(Price) AS AVG_Price 
FROM Products
GROUP BY SupplierID, CategoryID


오름차순 기준 정렬, Sorting
--------------------------------------------------------------------------
SELECT SupplierID
 , CategoryID
 , AVG(Price) AS AVG_Price 
FROM Products
GROUP BY SupplierID, CategoryID
ORDER BY AVG(Price)


내림차순 기준 정렬
--------------------------------------------------------------------------
SELECT SupplierID
 , CategoryID
 , AVG(Price) AS AVG_Price 
FROM Products
GROUP BY SupplierID, CategoryID
ORDER BY AVG(Price) DESC


집계 함수가 SELECT 문에 들어가도 상관없음
--------------------------------------------------------------------------
SELECT SupplierID
 , CategoryID
 , AVG(Price) AS AVG_Price 
FROM Products


WHERE 은 GROUP BY 이전에
HAVING 은 이후에


--------------------------------------------------------------------------
100불 이상 평균 가격 내림차순 정렬

SELECT SupplierID
 , CategoryID
 , AVG(Price) AS AVG_Price 
FROM Products
GROUP BY SupplierID, CategoryID
HAVING AVG_Price >= 100
ORDER BY AVG(Price) DESC


Alias 활용
--------------------------------------------------------------------------
SELECT SupplierID
 , CategoryID
 , AVG(Price) AS avg_price
FROM Products
GROUP BY SupplierID, CategoryID
HAVING avg_price >= 100


소수점 처리
--------------------------------------------------------------------------
CEIL() >>> 올림

FLOOR() >>> 내림

ROUND() >>> 반올림



CASE
--------------------------------------------------------------------------
SELECT CASE
WHEN categoryid = 1 THEN '음료'
WHEN categoryid = 2 THEN '조미료'
ELSE '기타'
END AS 'category name', *

FROM Products



AND 조건 1개 추가
--------------------------------------------------------------------------

SELECT CASE
WHEN categoryid = 1 AND supplierID = 2 THEN '음료'
WHEN categoryid = 2 THEN '조미료'
ELSE '기타'
END AS 'category name', *

FROM Products


CASE + GROUP BY
--------------------------------------------------------------------------
SELECT CASE
WHEN categoryid = 1 AND supplierID = 2 THEN '음료'
WHEN categoryid = 2 THEN '소스'
ELSE '기타'
   END AS 'NEW_CATEGORY', *
       
FROM Products
GROUP BY NEW_CATEGORY


--------------------------------------------------------------------------


SELECT 
CASE
WHEN categoryid = 1 AND supplierID = 2 THEN '음료'
WHEN categoryid = 2 THEN '소스'
ELSE '기타'
END AS 'NEW_CATEGORY', AVG(Price)        >>> 반올림 포함하는 
       
FROM Products
GROUP BY NEW_CATEGORY






WHEN절의 순서 중요,
정삼각형 데이터 분리된 후 다음 케이스 분리하는 방식
--------------------------------------------------------------------------
SELECT
      CASE 
           WHEN A=B AND B=C THEN 'Equilateral' 
      
           WHEN A+B<=C OR A+C<=B OR B+C<=A THEN 'Not A Triangle'

           WHEN A=B OR A=C OR B=C THEN 'Isosceles'
           
           ELSE 'Scalene'
           
      END
      
FROM TRIANGLES



행렬 역변환, 데이터 피벗팅
--------------------------------------------------------------------------

SELECT 
CASE 
WHEN CategoryID = 1 THEN price
ELSE NULL
       END AS Category1_price, *       
      

FROM Products





SELECT 
      CASE 
    WHEN CategoryID = 1 THEN price
    ELSE NULL
      END AS Category1_price,
      
      CASE 
       WHEN CategoryID = 2 THEN price
    ELSE NULL
      END AS Category2_price,
      
      CASE 
    WHEN CategoryID = 3 THEN price
    ELSE NULL
      END AS Category3_price

FROM Products





>>> CASE문에 다른 함수 추가할 때, CASE부터 END까지 괄호 씌워야 


SELECT 
      AVG(CASE 
 WHEN CategoryID = 1 THEN price
        ELSE NULL
          END) AS Category1_price,
      
      AVG(CASE 
WHEN CategoryID = 2 THEN price
ELSE NULL
          END) AS Category2_price

FROM Products



Category1_price Category2_price
37.979166666666664 23.0625


차이 확인하기

SELECT CategoryID, AVG(Price)
FROM Products
GROUP BY CategoryID


CategoryID     AVG(Price)
1              39.2
2              40.3



-------------------------------------------------------------------------




INNER 조인 - 교집합
--------------------------------------------------------------------------

SELECT *
FROM Users

     INNER JOIN Orders ON Users.ID = Orders.userID


JOIN Orders  >>> 붙일 테이블

ON Users.ID = Orders.userID
>>> Users의 ID컬럼과 Orders의 userID 컬럼이 같을때, 
이것을 기준으로 해서 조인, 조인이 되는 기준 컬럼




SELECT * FROM Orders
  INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID



SELECT * FROM Orders
  INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
  INNER JOIN Shippers ON Orders.ShippersID = Shippers.ShippersID


이너 중첩
--------------------------------------------------------------------------

SELECT * FROM Orders
  INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
  INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID


LEFT JOIN - Null 값이 포함될 수 있음.
--------------------------------------------------------------------------
SELECT *
FROM Users >>> 왼쪽에 두고 싶은 테이블
     LEFT JOIN Orders ON Users.Id = Orders.UserId    >>> User.Id 기준으로
              >>> 오른쪽에 두고 싶은 테이블


LEFT JOIN - 한번도 주문하지 않은 고객들
--------------------------------------------------------------------------
SELECT * FROM Customers
  LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID

WHERE OrderID is null

시간 더하기, 빼기
--------------------------------------------------------------------------
DATE_ADD

SELECT DATE_ADD(NOW(), INTERVAL 1 SECOND)
SELECT DATE_ADD(NOW(), INTERVAL 1 MINUTE)
SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR)
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY)
SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH)
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR)
SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR)


DATE_SUB
SELECT DATE_SUB(NOW(), INTERVAL 1 SECOND)





UNION(중복 제거된 값), UNION ALL
--------------------------------------------------------------------------
 *** 위 테이블에서 아래 테이블결과를 뺄때 >>> EXCEPT(ORACLE DB용) or MINUS(ORACLE DB용)
 *** 교집합(INTERSECT, Mysql 지원X )
 FULL OUTER JOIN을 Mysql로 구현하고 싶을 때는, LEFT, RIGHT 조인을 모두 활용





문자열 다루기   -> % / IN / NOT IN / BETWEEN A AND B / NOT BETWEEN A AND B
--------------------------------------------------------------------------
A로 시작하는 문자열 -> A %

B로 끝나는 문자열 -> % B
 
C가 들어있는 문자열 -> % C %




문자열 앞 뒤 글자 색출 활용

LEFT('문자열 or 컬럼명', 왼쪽으로부터 컬럼명 인덱스 )  -> ex) LEFT('giraffi',1) -> 'g'
RIGHT('문자열 or 컬럼명', 오른쪽으롭터 컬럼명 인덱스 )  -> ex) RIGHT('giraffi',1) -> 'I'

WHERE LEFT(CITY,1) NOT IN ('a','e','i','o','u')  -> 도시이름 문자열 첫 문자가 모음이 아닌 것
OR RIGHT(CITY,1) NOT IN ('a','e','i','o','u')  -> 도시이름 문자열 마지막 문자가 모음이 아닌 것




% 활용
WHERE ~~~ LIKE 'A%'   -> 따옴표 반드시


_ 은 한칸으로 취

_ _ %A -> 3번째 문자가 A인 문자열

_ _ A _ _ -> 총 5글자, 그 중 3번째 문자가 A인 문자열 





IN 활용 -> OR이 여러개 일때 활용, 여러번 적기 귀찮으니

SELECT * FROM lake
WHERE destination=10 OR destination=20 OR destination=30

두개 다 같은 구문

SELECT * FROM lake
WHERE destination IN(10,20,30)

***** NOT IN 도 응용해서 활용 가능





BETWEEN ~ AND 활용 예시

SELECT * FROM lake
WHERE destination BETWEEN 10 AND 30

***** NOT BETWEEN 도 응용해서 활용 가능





대/소문자 변환함수
--------------------------------------------------------------------------
UPPER
LOWER

INITCAP('문자열') - 첫 글자만 대문자 변환





제어함수
--------------------------------------------------------------------------
1) SUBSTR - 문자열 자르기

SUBSTR('문자열',시작위치,길이) -> 오라클 기준 숫자시작은 1

ex) SUBSTR('HELLO_KITTY',3,6) -> LLO_KI
              123456



2) INSTR - 내가 찾고자하는 문자열이 전체 문자열에서 몇번째 위치에 있는지 위치를 알려줌

INSTR('문자열','비교하고자 하는 값',시작위치, 몇 번째 비교값인지)

ex) INSTR('HELLO_KITTY',T,1,2) -> 두번째 T가 시작 위치로부터 몇 번째에 위치해 있는지?
           12345678910
=> 10

*** 시작위치가 1이 아닐경우, 시작위치 전의 문자는 비교하지 않음, 번호는 시작위치가 1일때와 동일

INSTR('HELLO_KIOTTY','K',1,1) => 7
INSTR('HELLO_KIOTTY','K',5,1) => 7

INSTR('HELLO_KIOTTY','O',6,2) => 0   >>> 6 번째부터 검색하기 때문에 2번쨰 O가 없다고 생각
INSTR('HELLO_KIOTTY','O',6,1) => 9



3) CONCAT - 문자열 병합
CONCAT('안녕','하세요')


보통 CONCAT 보다는 || 를 많이 활용
'안녕'||'하세요'  -> '안녕하세요'



4) REPLACE - 특정문자를 다른문자로 변환

REPLACE('문자열','찾을문자','변환문자')
ex) REPLACE('HELLO_KITTY','H','C') => 'CELLO_KITTY'





문자열 채우기 함수
--------------------------------------------------------------------------

1) LPAD - 지정한 길이만큼 왼쪽부터 특정문자로 채우기
LPAD('문자열',총 문자길이,'채움문자')
ex) LPAD('admin',10,'*') => *****admin



2) RPAD - 지정한 길이만큼 오른쪽부터 특정문자로 채우기
ex) RPAD('admin',10,'*') => admin*****





제거함수(두번째 argument 생략가능, 생략시 단순 공백제거 기능)
--------------------------------------------------------------------------

1) LTRIM - 문자열의 왼쪽 공백을 제거하거나, 문자열 왼쪽의 반복적인 문자를 제거

LTRIM('문자열','반복되는 문자')

ex) LTRIM('AAABBB','A') => 'BBB'

ex) LTRIM('  AAABBB') => 'AAABBB'

ex) LTRIM('ABABBBBC','AB') => 'BBBC' -> 왼쪽 반복문자열만 제거

ex) LTRIM('AB  ABBBBC','AB') => '  ABBBBC' -> 왼쪽 반복문자열만 제거
             12                  12


ex) LTRIM('ABCAB DCCCBB','ABC') 
=> ABC가 하나의 세트로 되어있는 구성을 제거하는 것이 아님
=> 반복되는 문자 여러개를 입력한 경우, 왼쪽에 개별 문자가 있는지 하나씩 체크해서 없앰
=> 왼쪽에서부터 'A','B','C' 가 있는지 각각을 검사해서 제거
'ABCAB' 까지 삭제


=> ' DCCCBB'
    1             >> 위치


2) RTRIM - 문자열의 오른쪽 공백을 제거하거나, 문자열 오른쪽의 반복적인 문자를 제거

"
"


3) TRIM - 문자열의 양쪽 공백을 제거하거나, 문자열 양쪽에서 지정된 문자를 제거

TRIM('문자열')
TRIM('제거할 문자' FROM '문자열')


TRIM('  HELLO_KITTY  ') => 'HELLO_KITTY'
TRIM('H' from 'HHELLO_KITTYH') => 'ELLO_KITTY'
TRIM('A' from 'AACDEFA') => 'CDEF'


TRIM(옵션, '제거할 문자' FROM '문자열')의 형태로도 사용가능

** 옵션을 쓰지 않으면 both로 인식
- 옵션: leading, trailing, both

1) leading - 문자열 앞 반복 문자 제거

ex) TRIM(LEADING 'A' FROM 'AAACDEAAA') = > 'CDEAAA'

2) trailing - 문자열 뒤 반복 문자 제거

ex) TRIM(TRAILING 'A' FROM 'AAACDEAAA') = > 'AAACDE'


3) both - 문자열 앞/뒤 반복 문자 제거 = 안써도 됨, 생략하면 both 로 인식

ex) TRIM(BOTH 'A' FROM 'AAACDEAAA') = > 'CDE'

https://leetcode.com/problems/department-top-three-salaries/

-- RANK(), DENSE_RANK()


SELECT t.department
     , t.employee
     , t.salary
     
FROM(
    SELECT department.name AS dapartment
        , employee.name AS employee
        , employee.salary
        , DENSE_RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC) AS dr

    FROM Employee
        INNER JOIN dapartment ON employee.departmentId = dapartment.id
    ) t
WHERE t.dr <=3

https://leetcode.com/problems/department-highest-salary/

*** SELECT에서 연산한 결과물은 WHERE 절에서 사용할 수 없음

SELECT department.name AS Department
     , sub.name AS Employee
     , sub.max_salary AS Salary
FROM( 
    SELECT id 
         , name
         , salary
         , departmentid
         , MAX(salary) OVER (PARTITION BY departmentId) AS max_salary
    FROM employee
    -- WHERE salary = max_salary >>> SELECT에서 연산한 결과물은 WHERE 절에서 사용할 수 없음
    ) as sub
    INNER JOIN Department ON sub.departmentid = department.id
WHERE sub.salary = sub.max_salary

https://leetcode.com/problems/consecutive-numbers/

-- Use Window function

-- *LEAD,  *LAG



SELECT DISTINCT l.NUM AS ConsecutiveNums
     SELECT NUM
          , LEAD(NUM,1) OVER (ORDER BY id) AS next
          , LEAD(NUM,2) OVER (ORDER BY id ) AS afternext

     FROM logs
 )l
 WHERE l.Num=next AND l.next=l.afternext

Leetcode 177 - Nth Highest Salary

https://leetcode.com/problems/nth-highest-salary/description/

CASE 문

CREATE FUNCTION getNthHighestSalary (N INT) 
RETURNS INT

BEGIN
  RETURN (
            SELECT 
                  CASE WHEN COUNT(sub.Salary) < N THEN NULL
                      ELSE MIN(sub.Salary) -- 가장 작은 값
                  END
            FROM(
                  SELECT DISTINCT Salary
                  FROM Employee
                  ORDER BY Salary DESC
                  LIMIT N
                ) sub
  
        );
END

IF 문  >>>  건이 순차적으로 실행되어야 하는 상황에는 CASE가 적합 (구간을 특정 기준으로 나눠야 할 때)

CREATE FUNCTION getNthHighestSalary (N INT) 
RETURNS INT

BEGIN
  RETURN (
            SELECT 
                  IF(COUNT(sub.Salary) <N, NULL, MIN(sub.Salary))

            FROM(
                  SELECT DISTINCT Salary
                  FROM Employee
                  ORDER BY Salary DESC
                  LIMIT N
                ) sub
        );
END

LIMIT 심화 
- return 구문에 서브쿼리를 활용하지 않는다면, 쿼리의 결과가 나오지 않을 시 자동으로 NULL 값 반환

CREATE FUNCTION getNthHighestSalary (N INT) 
RETURNS INT

BEGIN

  DECLARE A INT;
  SET A = N -1;      -- equal SET N = N-1;   >>> LIMIT N-1,1
  RETURN (

            SELECT DISTINCT Salary
            FROM Employee
            ORDER BY Salary DESC
            # LIMIT A, 1       >>> 맨 앞에서 A개를 날리고 그 다음 1개를 가져와
            LIMIT 1 OFFSET A.  >>> 맨 앞에서 A개를 날리고 그 다음 1개를 가져와

        );
END

regxone.com/lesson/introdution_abcs. >>> 연습사이트
regxr.com

Weather Observation Station 6
https://www.hackerrank.com/challenges/weather-observation-station-6/problem?h_r=internal-search

기존 풀이

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%'


--------------------------------------------------------------------------------

정규표현식 활용

SELECT DISTINCT city
FROM station
WHERE city REGEXP '^[aeiou].*'       >>> regular expression 
a,e,i,o,u 로 시작해야 하고, 그 뒤는 어떤 문자열이 와도 상관이 없음.


[aeiou] >>> a,e,i,o,u 중 어떤 문자중에 하나라도 들어가면
^문자열 시작위치 >>> 맨 앞
.* 임의의 문자열. = %
. >>> 어떤 문자열도 상관 없음
* >>> .이 몇번 반복될 것 인지?

-----------------------------------------------------------------
Weather Observation Station 7

https://www.hackerrank.com/challenges/weather-observation-station-7/problem?h_r=internal-search

SELECT DISTINCT city
FROM station
WHERE city REGEXP '.*[aeiouAEIOU]$'.  >>> ~로 끝나야 할떄 괄호 끝 $표시 >>> [~]$

Weather Observation Station 8

https://www.hackerrank.com/challenges/weather-observation-station-8/problem?h_r=internal-search

SELECT DISTINCT city
FROM station
WHERE city REGEXP '^[aeiouAEIOU].*[aeiouAEIOU]$' >>> 모음으로 시작하고 모음으로 끝나는 경우

Weather Observation Station 9
https://www.hackerrank.com/challenges/weather-observation-station-9/problem?h_r=internal-search

SELECT DISTINCT city
FROM station
WHERE city NOT REGEXP '^[aeiouAEIOU].*'.  >>> 모음으로 시작하지 않아야 할 때.  NOT 추가

https://www.hackerrank.com/challenges/what-type-of-triangle/problem

SELECT
      CASE 
           WHEN A=B AND B=C THEN 'Equilateral' 
      
           WHEN A+B<=C OR A+C<=B OR B+C<=A THEN 'Not A Triangle'

           WHEN A=B OR A=C OR B=C THEN 'Isosceles'
           
           ELSE 'Scalene'
           
      END
      
FROM TRIANGLES

Symmetric Pairs - Medium 
https://www.hackerrank.com/challenges/symmetric-pairs/problem

SELECT X, YG
FROM Functions
WHERE X=Y
GROUP BY X,Y
HAVING COUNT(*)>=2

UNION

SELECT s1.X, s1.Y
FROM functions AS s1
     INNER JOIN functions AS s2 ON s1.X = s2.Y AND s1.Y = s2.X
WHERE s1.X < s1.Y
ORDER BY X

 African Cities 
https://www.hackerrank.com/challenges/african-cities/problem

SELECT city.name FROM city
      INNER JOIN COUNTRY ON CITY.CountryCode = COUNTRY.CODE
WHERE continent = 'Africa'

Population Census
https://www.hackerrank.com/challenges/asian-population/problem

SELECT SUM(city.population) FROM city
       INNER JOIN country ON  city.CountryCode = Country.Code
WHERE CONTINENT = 'Asia'

New Companies - Medium 
https://www.hackerrank.com/challenges/the-company/problem

SELECT C.company_code 
       ,C.founder
       ,COUNT(DISTINCT LM.lead_Manager_code)
       ,COUNT(DISTINCT SM.senior_Manager_code)
       ,COUNT(DISTINCT M.manager_code)
       ,COUNT(DISTINCT E.employee_code)
       
FROM Company C
     LEFT JOIN Lead_Manager LM ON C.company_code = LM.company_code
     LEFT JOIN Senior_Manager SM ON LM.lead_manager_code = SM.lead_manager_code
     LEFT JOIN Manager M ON SM.senior_manager_code = M.senior_manager_code
     LEFT JOIN Employee E ON M.manager_code = E.manager_code

GROUP BY C.company_code, C.founder
ORDER BY C.Company_code

Top Competitors - Medium
https://www.hackerrank.com/challenges/full-score/problem

SELECT H.hacker_id
      ,H.name
FROM Submissions S
    INNER JOIN Challenges C ON C.Challenge_id = S.Challenge_id
    INNER JOIN Difficulty D ON C.Difficulty_level = D.Difficulty_level
    INNER JOIN Hackers H ON S.hacker_id =  H.hacker_id
WHERE S.score = D.score
GROUP BY H.hacker_id, H.name
HAVING COUNT(DISTINCT S.submission_id)>1     
ORDER BY COUNT(DISTINCT S.submission_id) DESC, H.hacker_id

Placements - Medium
https://www.hackerrank.com/challenges/placements/problem

SELECT NAME
FROM Friends F
     INNER JOIN Students S ON S.ID=F.ID
     INNER JOIN Packages P1 ON F.ID = P1.ID
     INNER JOIN Packages P2 ON F.Friend_ID = P2.ID
WHERE P2.salary > P1.salary
ORDER BY P2.salary

* CEIL - 소수점 첫째 자리에서 올림

* FLOOR - 소수점 첫째 자리에서 내림

* ROUND - 숫자를 각 자리에서 반올림

* TRUNCATE(숫자, 버릴 자릿수) - 숫자를 버릴 자릿수 아래로 버림

* ABS - 절대값

* POWER - 제곱

* SQRT - 양수 x 에 대한 제곱근을 반환

Average Population
https://www.hackerrank.com/challenges/average-population/problem

SELECT Floor(AVG(population))
FROM CITY

Weather Observation Station 2
https://www.hackerrank.com/challenges/weather-observation-station-2/submissions/database/293205884

SELECT  ROUND(SUM(LAT_N),2) AS lat  
       ,ROUND(SUM(LONG_W),2) AS lon
FROM STATION

Weather Observation Station 18 - Medium 
https://www.hackerrank.com/challenges/weather-observation-station-18/problem

SELECT ROUND(ABS(MIN(LAT_N)-MAX(LAT_N)) + ABS(MIN(LONG_W)-MAX(LONG_W)),4)
FROM STATION

Weather Observation Station 19 - Medium 
https://www.hackerrank.com/challenges/weather-observation-station-19/problem

SELECT TRUNCATE(SQRT( POWER(MAX(LAT_N)-MIN(LAT_N),2) + POWER(MAX(LONG_W)-MIN(LONG_W),2) ),4)
FROM STATION

Average Population of Each Continent
https://www.hackerrank.com/challenges/average-population-of-each-continent/problem

SELECT COUNTRY.CONTINENT, FLOOR(AVG(city.population)) FROM CITY
       INNER JOIN COUNTRY ON COUNTRY.Code = CITY.CountryCode
       
GROUP BY COUNTRY.CONTINENT

Revising Aggregations - Averages 
https://www.hackerrank.com/challenges/revising-aggregations-the-average-function/problem

SELECT AVG(population)
FROM CITY
WHERE District = "California"

Revising Aggregations - The Sum Function
https://www.hackerrank.com/challenges/revising-aggregations-sum/problem

SELECT SUM(population)
FROM CITY
WHERE District = "California"

Revising Aggregations - The Count Function
https://www.hackerrank.com/challenges/revising-aggregations-the-count-function/problem

SELECT COUNT(COUNTRYCODE)
FROM CITY
WHERE POPULATION > 100000

Weather Observation Station 4
https://www.hackerrank.com/challenges/weather-observation-station-4/problem

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

Top Earners 

https://www.hackerrank.com/challenges/earnings-of-employees/problem

SELECT salary * months AS earnings , COUNT(*)
FROM Employee
GROUP BY earnings
ORDER BY earnings DESC
LIMIT 1

Japan Population
https://www.hackerrank.com/challenges/japan-population/problem

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

Weather Observation Station 13 
https://www.hackerrank.com/challenges/weather-observation-station-13/problem

SELECT TRUNCATE(SUM(LAT_N),4)
FROM STATION
WHERE LAT_N>=38.7880 AND LAT_N<=137.2345

Weather Observation Station 3
https://www.hackerrank.com/challenges/weather-observation-station-3/problem

SELECT CITY
FROM STATION
WHERE ID%2=0
GROUP BY CITY

https://www.hackerrank.com/challenges/the-report/problem?h_r=internal-search 

- SELECT 구문에서 CASE 활용해서 구분점 만들기
- 정렬기준 여러개
- INNER 조인 조건에 BETWEEN 사용할 수 있음.hacker_id

SELECT CASE WHEN g.grade <8 THEN NULL ELSE s.name END AS name
     , g.grade
     , s.marks
FROM students AS s
     INNER JOIN grades AS g ON s.marks BETWEEN g.min_mark AND g.max_mark
     
ORDER BY g.grade DESC, name ASC, s.marks

https://www.hackerrank.com/challenges/challenges/submissions/database/331359810

SELECT h.hacker_id
     , h.name
     , count(*) Challenges_created
FROM Challenges c 
     INNER JOIN Hackers h ON h.hacker_id = c.hacker_id

GROUP BY h.hacker_id, h.name
HAVING Challenges_created = (SELECT MAX(Challenges_created)             --Challeges_created MAX 값이 50인 것         
                                FROM(    
                                    SELECT hacker_id
                                         , count(*) Challenges_created
                                    FROM Challenges
                                    GROUP BY hacker_id
                                    ) sub)
                    
OR Challenges_created IN (SELECT Challenges_created             --만들어진 챌린지수의 묶음이 각각 몇개인지 ex) 몇명의 사람들이 12개의 챌린지를 만들었는지?       
                            FROM(
                                SELECT hacker_id
                                     , COUNT(*) AS Challenges_created               --해커 한명 당 몇개의 챌린지를 만들었는지?
                                FROM Challenges
                                GROUP BY hacker_id
                                )sub
                            GROUP BY Challenges_created
                            HAVING COUNT(*)=1               --12개를 만든 사람이 딱 한명일 때만 목록에 보존
                         )                     


ORDER BY challenges_created DESC, hacker_id



-----------------------------------------------------------------
-----------------------------------------------------------------



Challenges - WITH 문 활용  - Medium
https://www.hackerrank.com/challenges/challenges/submissions/database/331359810
-----------------------------------------------------------------

WITH counter AS (
    SELECT hackers.hacker_id
         , hackers.name
         , count(*) AS Challenges_created
    FROM Challenges
        INNER JOIN Hackers ON Challenges.hacker_id = Hackers.hacker_id
    GROUP BY hackers.hacker_id, hackers.name
) 

SELECT counter.hacker_id
     , counter.name
     , counter.Challenges_created
FROM counter
WHERE Challenges_created = (SELECT MAX(Challenges_created) FROM counter)
OR Challenges_created IN (SELECT Challenges_created        -- if count(*) is in HAVING, then exclude "count(*)" in SELECT clause
                          FROM counter
                          GROUP BY Challenges_created
                          HAVING count(*)=1 )
ORDER BY counter.Challenges_created DESC, counter.hacker_id

Top earners - 서브쿼리
https://www.hackerrank.com/challenges/earnings-of-employees/problem

SELECT months*salary as earnings
     , count(*)
FROM employee

WHERE months*salary = (SELECT MAX(months*salary) FROM employee)
GROUP BY earnings


-- Having >>> GROUP BY 결과물을 다시 한 번 필터링 해줄 때


SELECT months*salary AS earnings
     , count(*)
FROM employee
GROUP BY earnings
HAVING earnings = (SELECT MAX(months*salary) FROM employee)  -- 전체 임직원 중에서 최대 값을 가진 사람만



-- FROM >>> FROM 절을 테이블로 활용
 
SELECT *
FROM (

        SELECT months*salary AS earnings
             , count(*)
        FROM employee
        GROUP BY earnings
    ) sub
ORDER BY earnings DESC
limit 1;

*** 다시 풀어볼 것

184. Department Highest Salary
https://leetcode.com/problems/Department-Highest-Salary/

# 각각의 부서에서 누가 가장 고소득자인지
# sel 부서, 직원, 급여 
# 순서 상관 x

SELECT
       d.name AS Department
     , e.name AS Employee
     , e.salary AS Salary
FROM employee as e
     INNER JOIN (
          SELECT departmentID, MAX(salary) AS max_salary
          FROM Employee
          GROUP BY departmentID
              ) AS dh ON e.departmentID = dh.departmentID
                    AND e.salary = dh.max_salary
     INNER JOIN department AS d ON d.id = e.departmentId

Consecutive Numbers
https://leetcode.com/problems/consecutive-numbers/

# 최소 3번이상 연속적으로 나타나는 숫자 찾기
# 순서는 상관없음
 

 SELECT DISTINCT l.num AS ConsecutiveNums   -- 중복제거
 FROM logs AS l
      INNER JOIN logs AS l_next ON l.id + 1 = l_next.id
      INNER JOIN logs AS l_next2 ON l.id + 2 =  l_next2.id
 
 # three times consecutively
 WHERE l.num = l_next.num AND l_next.num = l_next2.num

Reformat Department Table
https://leetcode.com/problems/reformat-department-table/

SELECT id,
      SUM(CASE WHEN month='Jan' THEN revenue ELSE NULL END) AS JAN_Revenue,
      SUM(CASE WHEN month='Feb' THEN revenue ELSE NULL END) AS Feb_Revenue,
      SUM(CASE WHEN month='Mar' THEN revenue ELSE NULL END) AS Mar_Revenue,
      SUM(CASE WHEN month='Apr' THEN revenue ELSE NULL END) AS Apr_Revenue,
      SUM(CASE WHEN month='May' THEN revenue ELSE NULL END) AS May_Revenue,
      SUM(CASE WHEN month='Jun' THEN revenue ELSE NULL END) AS Jun_Revenue,
      SUM(CASE WHEN month='Jul' THEN revenue ELSE NULL END) AS Jul_Revenue,
      SUM(CASE WHEN month='Aug' THEN revenue ELSE NULL END) AS Aug_Revenue,
      SUM(CASE WHEN month='Sep' THEN revenue ELSE NULL END) AS Sep_Revenue,
      SUM(CASE WHEN month='Oct' THEN revenue ELSE NULL END) AS Oct_Revenue,
      SUM(CASE WHEN month='Nov' THEN revenue ELSE NULL END) AS Nov_Revenue,
      SUM(CASE WHEN month='Dec' THEN revenue ELSE NULL END) AS Dec_Revenue
     
        
FROM Department
GROUP BY id

Customers Who Never Order
https://leetcode.com/problems/Customers-Who-Never-Order/

SELECT name AS Customers FROM Customers AS c
       LEFT JOIN Orders AS o ON o.customerId  = c.id
WHERE o.id is null

Employees Earning More Than Their Managers
https://leetcode.com/problems/Employees-Earning-More-Than-Their-Managers/

SELECT employee.name AS Employee
FROM employee
     INNER JOIN employee as Mannager ON employee.managerID = Mannager.id

WHERE employee.salary > Mannager.salary

Rising Temperature
https://leetcode.com/problems/Rising-Temperature/

SELECT today.id
FROM weather AS today
     INNER JOIN weather AS yesterday ON DATE_ADD(yesterday.recordDate, INTERVAL 1 DAY) = today.recordDate
WHERE today.temperature > yesterday.temperature


# # {"headers": ["id", "recordDate", "temperature", "id", "recordDate", "temperature"], 

# # "values": 

# # [[2, "2015-01-02", 25, 1, "2015-01-01", 10], 
# #  [4, "2015-01-04", 30, 3, "2015-01-03", 20]]}

# {"headers": ["id", "recordDate", "temperature", "id", "recordDate", "temperature"], "values": 

# [[2, "2015-01-02", 25, 1, "2015-01-01", 10], 
#  [3, "2015-01-03", 20, 2, "2015-01-02", 25], 
#  [4, "2015-01-04", 30, 3, "2015-01-03", 20]]}
 
#  # SELECT today.id AS today_id
#  #        ,today.recordDate AS today_RecordDate
#  #        ,today.Temperature AS today_Temperature
#  #        ,yesterday.id AS yesterday_id
#  #        ,yesterday.recordDate AS yesterday_RecordDate
#  #        ,yesterday.Temperature AS yesterdayy_Temperature
 
#  yesterday에 1을 더했을때 매칭되는 today 값

Big Countries
https://leetcode.com/problems/Big-Countries/

SELECT name, population, area
FROM WORLD
WHERE area >=3000000 OR population >=25000000

Not Boring Movies
https://leetcode.com/problems/Not-Boring-Movies/

SELECT id, movie, description, rating
FROM Cinema
WHERE MOD(id,2)= 1 AND description != 'boring'
ORDER BY rating DESC