[SQL 코드카타] 121 ~ 123번(LeetCode)

121번 patients-with-a-condition

SELECT
    patient_id, patient_name, conditions
FROM
    Patients
WHERE
    conditions like '% DIAB1%'
    OR conditions like 'DIAB1%'

> 정규표현식으로 표현해보기

- \b는 단어 경계를 의미하는데, SQL에서 \는 이스케이프 문자로 표현되어 \\를 두번 입력한 것

SELECT
    patient_id, patient_name, conditions
FROM
    Patients
WHERE
    conditions regexp '\\bDIAB1'

 

122번 delete-duplicate-emails

DELETE 
    p1
FROM 
    person p1
    join person p2
    on p1.email = p2.email 
WHERE 
    p1.id > p2.id;

 

123번 second-highest-salary

SELECT
    max(emp.salary) AS SecondHighestSalary
FROM (
    SELECT
        ID,
        salary,
        DENSE_RANK() OVER(ORDER BY salary DESC) AS rn
    FROM
        Employee) emp
WHERE rn = 2

> 다른 답안

SELECT 
    MAX(salary) SecondHighestSalary 
FROM Employee 
WHERE salary < (SELECT MAX(salary) FROM Employee);

> FROM절이 없는 SELECT문 .?

SELECT 
    (SELECT DISTINCT salary
     FROM Employee
     ORDER BY salary DESC
     LIMIT 1 OFFSET 1) AS SecondHighestSalary;