2024-01-27~2024-01-28

SQL 코드카타

 

1327. List the Products Ordered in a Period(SQL)(년월로 조건, LEFT JOIN, BETWEEN, AND &&, s년월의 조건하에서 추가 그룹지었을 때 조건으로 만족하는 것에 대응되는 다른 테이블에 있는 정보 출력)

LeetCode - The World's Leading Online Programming Learning Platform

2020년 2월 동안 unit이 100개 이상 인 제품에 대하여 제품이름과 unit을 출력하는 문제이다.

SELECT p.product_name,  SUM(o.unit) unit
FROM Orders o
LEFT JOIN Products p ON o.product_id = p.product_id
WHERE o.order_date BETWEEN '2020-02-01' AND '2020-02-29'
GROUP BY o.product_id
HAVING unit >= 100

 아래는 다른사람 쿼리이다

# Write your MySQL query statement below
select a.product_name,
				sum(b.unit) as unit 
from Products a join Orders b on a.product_id = b.product_id 
where  Month(order_date)=02 && Year(order_date)=2020 
group by b.product_id having sum(b.unit)>=100  ;

# Write your MySQL query statement below

SELECT p.product_name AS product_name, sum(o.unit) AS unit FROM Products p
JOIN Orders o USING (product_id)
WHERE YEAR(o.order_date)='2020' AND MONTH(o.order_date)='02'
GROUP BY p.product_id
HAVING SUM(o.unit)>=100

# Write your MySQL query statement below

WITH CTE AS (
        SELECT
            T2.product_name,
            SUM(T1.unit) unit
        FROM
            Orders T1
        INNER JOIN
            Products T2 ON T1.product_id = T2.product_id
        WHERE
            order_date > '2020-01-31' AND 
            order_date < '2020-03-01'
        GROUP BY
            T2.product_name
)
SELECT
    *
FROM
    CTE 
WHERE
    unit > 99

AND와 &&같은 의미, OR과 ||같은 의미, NOT과 !같은 의미
추가로 mysql에도 XOR이 있다고 한다(둘 다 1일 때만 0리턴 나머진 1리턴)

그리고 아래의 궁금증이 생겨 질문을 통해 확인해봐야 할 듯하다
그런데 null은 0판정인지? →아마 그냥 무슨 연산자를 만나든 null로 나오는 듯한데?
그리고 데이터가 정수단위라고 가정할 때 ≥100과 >99 성능차이가 혹시 날지?
그리고 and와 &&성능차이 날지?
날짜 조건 방식 위 상황의 경우인데 데이터가 클경우 between과 month and year 방식 크게 차이날 수 있을지?

 

1517. Find Users With Valid E-Mails(SQL)(like, regexp, substring_index , regexp 더 연습이 필요할듯, 유효한 이메일 찾기)

LeetCode - The World's Leading Online Programming Learning Platform

이메일이 유효한 메일일 경우에 해당하는 유저의 이메일, 이름, 메일주소를 출력하는 문제이다

유효한 이메일의 조건으로는 이메일은 prefix name(접두사 이름)과 도메인으로 이루어져 있는데도메인이 @leetcode.com이여야하고, 접두사는 첫 글자는 letter(아마 알파벳으로 유츄됨, 한글도 포함 되겠지만 leetcode의 환경상 한글은 고려 안 했을 듯하기에 제외하면)로 시작해야하며, 접두사는 영어 대소문자, 숫자, _ , . , - 만으로 이루어져 있어야 한다

우선 이 문제에 관련하여 파이썬으로 개인 과제 중 한 문제와 매우 유사하였는데 SQL로 풀려하니 살짝 멈칫했다

SELECT u.user_id, u.name, u.mail
FROM Users u
WHERE u.mail LIKE '%@leetcode.com'
    AND 
        SUBSTRING_INDEX(u.mail,'@leetcode.com',1) REGEXP '^[a-zA-Z]'
    AND 
        SUBSTRING_INDEX(u.mail,'@leetcode.com',1) 
                                        NOT REGEXP '[^a-zA-Z0-9_.-]'

쿼리에 대해 간단시 설명하면 우선 User 테이블로부터 mail이 @leetcode.com로 끝나고, @leetcode.com로 나눴을 때 첫번째 문자열(내가 생각하기로 접두사에 해당하는 부분 @leetcode.com가 여러번 있지 않을 것이라는 전제하에 그렇게 생각하였다)이 알파벳대소문자로 시작해야하며(leetcode라서 한글의 경우는 생각해주지 않았다 한글을 추가해줄려면 [가-힇]으로 하면 된다고 한다), 그리고 그 문자열이 추가로 영어 대소문자, 숫자, _ , . , -만으로 이루어져 있도록 조건을 걸어주었다 (마지막 조건에 관하여 조금 더 설명을 덧 붙이자면 문자열이 ^를 []안에 붙이면 안에 있는 것들을 포함하지 않은 경우라는 의미인데 [^허용되는 문자들]로 작성하여 허용되는 문자들에 포함되지 않는 문자를 포함하는 경우가 아닌(NOT) 조건(허용되는 문자들만을 포함하는 조건)으로 설정해 주었다.) 조금 의미가 복잡한듯하여 조금 더 풀어서 생각해보면 문자열 NOT REGEXP[^허용할 문자들]에서 REGEXP[^허용할 문자들] 부분에 대하여 허용할 문자들을 제외한 문자가 포함되어 있지 않은가 판단하여 포함되어 있을 경우 True, 포함되어 있지 않을 경우 False로 나오는데 NOT을 붙이면 허용할 문자들을 제외한 나머지 문자가 포함되어 있을 경우 False, 포함되어있지 않아야 True로 나오기 때문에 원하는 결과를 얻을 수 있었다

regexp관련하여

여기에는 [^문자리스트들] 을 괄호 안의 문자를 포함하지 않은 문자열을 찾음이라고 적어뒀는데 테스트 해본 결과로 정확히는 괄호 안의 문자를 제외한 문자가 포함된 문자열을 찾는 의미 인듯하다

regexp은 정규표현식이라는 의미인데 like 보다 더 복잡한 문자열 조건을 걸어 검색할 수 있는데 아직 미숙하고 적혀있는 정보글만으로는 명확히 알기 힘든 부분이 있는 듯하여 여유있을 때 테스트를 더 해봐야할 듯하다
그리고 추가로 이메일은 파고들 허점을 많이 찾아낼 수도 있기에 대체로 그냥 암묵적인 어느정도의 틀안에서만 조건을 매겨 작성해주었는데 생각을 더 해서 더 꼼꼼히 따져봐야 할 수 도 있겠다는 생각을 했다(주어진 문제에선 그걸 요구하진 않는 것 같지만, 실제로 유효한 이메일인지 판단하는 일을 해야한다면 그럴듯하다)

그리고 아래는 다른사람 쿼리인데

SELECT
  *
FROM
  Users
WHERE
  mail REGEXP '^[a-zA-Z][a-zA-Z0-9_.-]*@leetcode[.]com$';

 나처럼 AND로 여러 조건 안걸고 한줄에도 가능했음을 확인할 수 있었다

 

Revising the Select Query I(SQL) (hackerrank 첫 문제)

Revising the Select Query I | HackerRank

미국 도시이고 인구가 10만이상인 경우에 대해 모든 컬럼에 대해 출력해주는 문제이다

SELECT c.id, c.name, c.countrycode, c.district, c.population
FROM CITY c
WHERE c.countrycode LIKE 'USA%'
    AND c.population >100000

leetcode환경 맘에 들었는데 갑자기 제출했던 내용 저장되지도 않고(저장은 되는 것 같은데 submission에 들어가서 직접 확인해봐야한다), 컬럼에 대한 설명도 없는 듯하고, ui도 별로인 것 같고, 테스트 해보기도 힘들고 테스트 결과도 확인하기 힘들게 표시가 되어 맘에 들지 않는다…ㅠ

일단 따로 leetcode나 프로그래머스처럼 바로 연동해서 자동 커밋해주는 확장프로그램 등이 없는 듯하여 간단히 문제 링크주소와 작성한 코드 정도로 하여 새로 만들어준 repository인 HackerRank에 올려줄 예정이다.

쿼리 자체는 간단하였기에 설명을 생략하도록 하겠다

 

Revising the Select Query II(SQL)(간단)

Revising the Select Query II | HackerRank

인구가 12만보다 많은 미국의 도시 이름을 출력하는 문제이다

SELECT c.name
FROM city c
WHERE c.countrycode = 'USA'
    AND c.population > 120000

마찬가지로 쿼리가 간단하므로 설명은 생략하도록 하겠다

 

Select All(SQL)(간단)

Select All | HackerRank

그냥 테이블의 모든 컬럼을 출력해주는 문제이다

이런 문제는 왜 있는지 모르겠다..(제일 첫 문제도 마찬가지로 모든 컬럼에 대해 출력하는 것이지 않았나?(조건은 있었지만) 아마 완전 순서대로 푸는게 아니라서 그런 것일 수도 있겠다는 생각을 하였다)

SELECT *
FROM city c

마찬가지로 쿼리가 간단하니 설명은 생략하도록 하겠다

 

Select By ID(SQL)(간단)

Select By ID | HackerRank

아이디가 1661인 도시에 대해 모든 컬럼을 출력하는 문제이다

SELECT *
FROM city c
WHERE c.id = 1661;

 

Japanese Cities' Attributes(SQL)(간단)

Japanese Cities' Attributes | HackerRank

일본의 모든 도시에 대하여 모든 컬럼을 출력하는 문제이다

SELECT *
FROM city c
WHERE c.countrycode = 'JPN'

 

Japanese Cities' Names(SQL)(간단)

Japanese Cities' Names | HackerRank

일본 도시의 이름을 출력하는 문제이다.

SELECT c.name FROM city c WHERE c.countrycode = 'JPN'

슬슬 언제까지 이런 간단한 문제만 나올 것인지 의문이 든다..

 

Weather Observation Station 1(SQL)(간단)

Weather Observation Station 1 | HackerRank

테이블에서 city와 state를 출력하는 문제이다

/*
Enter your query here.
*/
SELECT s.city, s.state
FROM station s

 

Weather Observation Station 2(SQL)(간단)

Weather Observation Station 2 | HackerRank

모든 lat의 총합과 lon의 총합을 소수 둘째자리까지 반올림하여 표시하는 문제이다

/*
Enter your query here.
*/
SELECT ROUND(SUM(lat_n),2) lat, ROUND(SUM(long_w),2) lon
FROM station s

 

Weather Observation Station 3(SQL)(간단)

Weather Observation Station 3 | HackerRank

아이디가 짝수인 모든 도시이름을 중복되지 않게 출력하는 문제이다

/*
Enter your query here.
*/
SELECT DISTINCT s.city
FROM station s
WHERE s.id%2 = 0

tc가 어떻게 입력되는지도 안 알려줘서 test삼아서 distinct빼고 해봤는데 실패했다고는 나오는데 확인하기 힘들었다

 

Weather Observation Station 4(SQL)(간단)

Weather Observation Station 4 | HackerRank

전체 데이터의 city행의 수와 중복되지 않는 도시의 종류 수의 차를 구하는 문제이다

/*
Enter your query here.
*/
SELECT COUNT(s.city)-COUNT(DISTINCT s.city)
FROM station s

 

Weather Observation Station 5(SQL)(limit, union, 두개의 서브쿼리를 union으로 합치는데 각 서브쿼리는 limit로 특정갯수의 행을 잘라놓은 서브쿼리)

Weather Observation Station 5 | HackerRank

나라이름이 가장 짧은 나라와 긴 나라의 이름과 글자수를 출력하는 문제이다.

(SELECT s.city, LENGTH(s.city)
FROM station s
ORDER BY LENGTH(s.city), s.city
LIMIT 1)
UNION
(SELECT s.city, LENGTH(s.city)
FROM station s
ORDER BY LENGTH(s.city) DESC, s.city
LIMIT 1)

몇 일 전에 유사하게 각 테이블에 대해 구하고 첫번째에 해당하는 행만 가져와 union으로 합치는 방법을 사용하였다 각 서브쿼리로 첫번째는 각 도시, 글자수로 하여 글자수로 먼저 오름차순하여 첫번째 값으로 최소값을 가져왔고, 두번째는 각 도시, 글자수로 하여 글자수로 먼저 내림차순하여 첫번째 값으로 최대값을 가져왔다(그리고 정렬기준에 도시이름도 추가해주어 만약 동일글자수가 여러 개이면 알파벳순으로 먼저 오게 정렬해주었다)

다른 사람들은 어떻게 했나 궁금해서 보니 이것조차도 보기 불편하게 되어있었다…ㅠ

그리고 풀이를 봐도 나랑 크게 다르지 않게 하여서 따로 기록할 필요를 못 느꼈다

 

Weather Observation Station 6(SQL)(substr, distinct, in)

Weather Observation Station 6 | HackerRank

도시이름이 모음으로 시작하는 도시를 중복없이 모두 출력하는 문제이다.

SELECT DISTINCT s.city
FROM station s
WHERE SUBSTR(s.city,1,1) IN ('a', 'e', 'i', 'o', 'u')

첫글자를 추출해서 ()안에 있는 경우에 대해서만 출력하도록 조건을 걸어주었다

 

Weather Observation Station 7(SQL)(substr -1(끝 글자), distinct, in)

Weather Observation Station 7 | HackerRank

도시이름이 모음으로 끝나는 모든 도시를 중복없이 출력하는 문제이다

SELECT DISTINCT s.city
FROM station s
WHERE SUBSTR(s.city,-1,1) IN ('a', 'e', 'i', 'o', 'u')

이전 문제와 동일하지만 추출하는 것을 -1로 하여 끝부터 첫 글자로 하여 바꿔주었다

 

Weather Observation Station 8(SQL)(substr -1(끝 글자), distinct, in, and)

Weather Observation Station 8 | HackerRank

도시이름의 첫글자와 끝글자가 모음인 모든 도시를 중복없이 출력하는 문제이다

SELECT DISTINCT s.city
FROM station s
WHERE SUBSTR(s.city,1,1) IN ('a', 'e', 'i', 'o', 'u')
    AND SUBSTR(s.city,-1,1) IN ('a', 'e', 'i', 'o', 'u')

이전 문제들과 거의 동일하게 and로 합친 쿼리라 생각하면 될듯하다

 

Weather Observation Station 9(SQL)(substr, distinct, not in)

Weather Observation Station 9 | HackerRank

첫 글자가 모음으로 시작하지 않는 모든 도시의 이름을 중복없이 출력하는 문제이다

SELECT DISTINCT s.city
FROM station s
WHERE SUBSTR(s.city,1,1) NOT IN ('a', 'e', 'i', 'o', 'u')

 

Weather Observation Station 10(SQL)(substr -1(끝 글자), distinct, not in)

Weather Observation Station 10 | HackerRank

끝 글자가 모음으로 끝나지 않는 모든 도시의 이름을 중복없이 출력하는 문제이다

SELECT DISTINCT s.city
FROM station s
WHERE SUBSTR(s.city,-1,1) NOT IN ('a', 'e', 'i', 'o', 'u')

계속 큰 의미없이 조금씩 바꾸다보니 이젠 다음 문제가 뭐일지 예상될 정도이다.. 언제까지 반복이 될지 모르겠다..

 

Weather Observation Station 11(SQL)(substr -1(끝 글자), distinct, not in, or)

Weather Observation Station 11 | HackerRank

첫 글자가 모음으로 시작하지않거나 끝글자가 모음으로 끝나지 않는 모든 도시의 이름을 중복없이 출력하는 문제이다.

SELECT DISTINCT s.city
FROM station s
WHERE SUBSTR(s.city,1,1) NOT IN ('a', 'e', 'i', 'o', 'u')
    OR SUBSTR(s.city,-1,1) NOT IN ('a', 'e', 'i', 'o', 'u')

 

Weather Observation Station 12(SQL)(substr -1(끝 글자), distinct, not in, and)

Weather Observation Station 12 | HackerRank

첫 글자와 끝글자가 모음이 아닌 모든 도시의 이름을 중복없이 출력하는 문제이다.

SELECT DISTINCT s.city
FROM station s
WHERE SUBSTR(s.city,1,1) NOT IN ('a', 'e', 'i', 'o', 'u')
    AND SUBSTR(s.city,-1,1) NOT IN ('a', 'e', 'i', 'o', 'u')

 

Higher Than 75 Marks(SQL)(substr, 끝3글자로 정렬하기)(간단)

Higher Than 75 Marks | HackerRank

75점 보다 높은 학생들의 이름을 출력하는데 이름 끝 세글자로 오름차순정렬하고 만약 같은 게 있을 경우 id로 추가적으로 오름차순 정렬해주는 문제이다.

SELECT s.name
FROM students s
WHERE s.marks >75
ORDER BY SUBSTR(s.name,-3),s.id;

 

Employee Names(SQL)(간단)

Employee Names | HackerRank

직원들 이름을 출력하는데 알파벳순으로 정렬하는 문제이다

SELECT e.name
FROM employee e
ORDER BY e.name;

이제 다른 프로그래머스와 leetcode에서 처럼 데이터가 어떤 의미를 가진 데이터로 되어있는지와 비록 좀 보기불편하고 투박하긴 해도 input sample과 output sample가 주어졌다

 

Employee Salaries(SQL)(간단)

Employee Salaries | HackerRank

10개월 미만의 직원인 월급이 2000 이상인 직원의 직원 이름 목록을 출력하는 문제이며, 결과를 오름차순으로 정렬하는 문제이다.

SELECT e.name
FROM employee e
WHERE e.salary >2000 AND e.months <10
ORDER BY e.employee_id;

이번 주말은 평소보다 SQL 코드카타 문제를 많이 풀었는데 (leetcode 2문제까지 포함하여 총 20문제?정도 푼것으로 기억한다) 이유가 HackerRank에서 문제가 진짜 SQL 처음 시작할 때나 풀어봄직한 문제들이 엄청 우르르 있고 단순 반복문제 느낌으로 있어서 좀 의미있는 문제를 풀고 싶어서 주말동안 미리 좀 단순한 의미없어보이는 문제를 다 해치워버리자 하는 생각으로 몰아 풀어놓았다 조금 지나니 그래도 조금은 의미가 있을 법한 문제들이 보이는 듯해서 잘 해치워버린듯하다