Database

Nth Highest Salary [ MySql ]

Nth Highest Salary - LeetCode Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview. leetcode.com 사용자 정의 함수를 사용 하는 문제입니다. 아래와 같은 형태로 함수를 작성할 수 있습니다. CREATE FUNCTION 'function name'('parameter name', 'datatype') RETURNS 'datatype' (DETERMINISTIC) BEGIN DECLARE 'variable name' 'datatype'; SET ; RETURN (QUERY) / ..

Weather Observation Station 9 [ MySql ]

Weather Observation Station 9 | HackerRank Query an alphabetically ordered list of CITY names not starting with vowels. www.hackerrank.com 모음으로 시작하는 정규식을 찾아 NOT 키워드를 사용해 걸렀습니다. SELECT DISTINCT city FROM station WHERE city NOT REGEXP '^[aeiou]'

Weather Observation Station 8 [ MySql ]

Weather Observation Station 8 | HackerRank Query CITY names that start AND end with vowels. www.hackerrank.com ^: ~로 시작 $: ~로 끝 .*: 어떤 글자가 없거나 하나 이상 있거나 []: 범위안에 들어있는가 SELECT DISTINCT city FROM station WHERE city REGEXP '^[aeiou].*[aeiou]$'

Weather Observation Station 7 [ MySql ]

Weather Observation Station 7 | HackerRank Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. www.hackerrank.com $: ~로 끝나는지 []: 범위에 들어가는지 정규식을 사용하여 해결하였습니다. SELECT DISTINCT city FROM station WHERE city REGEXP '[aeiou]$'

Weather Observation Station 6 [ MySql ]

Weather Observation Station 6 | HackerRank Query a list of CITY names beginning with vowels (a, e, i, o, u). www.hackerrank.com 정규표현식 ^: ~로 시작 []: 포함여부 .*: 어떤 문자뒤에 하나도 없거나 한 개 이상 모두 가능 SELECT DISTINCT city FROM station WHERE city REGEXP '^[aeiou].*'

Department Top Three Salaries [ MSSQL ]

Department Top Three Salaries - LeetCode Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview. leetcode.com WINDOW 함수인 DENSE_RANK()를 사용하여 동일한 값은 동일한 랭크를 주게 하였고 SELECT 절에 연산한 것을 WHERE절에서 필터링 조건으로 사용할 수 없기 때문에 서브쿼리로 묶어 값을 뽑아냈습니다. SELECT t.department, t.employee, t.salary FROM ( SELECT d.name AS department , e.n..

Department Highest Salary [ MSSQL ]

Department Highest Salary - LeetCode Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview. leetcode.com WINDOW FUNCTION을 사용해 departmentId 기준으로 그룹을 묶어 max_salary값을 구한 후 FROM절 서브쿼리를 사용해 max_salary와 같은 salary를 갖고있는 row만 추출했습니다. SELECT Department , Employee , Salary FROM ( SELECT d.name AS Department , e.name AS ..

LeetCode Consecutive Numbers [ MySQL ]

Consecutive Numbers - LeetCode Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview. leetcode.com 연속적인 id를 확인하기 위해 아이디 + 1과 SELF JOIN을 두 번 하였습니다. 이후에 중복 제거를 통해 답을 출력했습니다. SELECT DISTINCT l1.num AS ConsecutiveNums FROM Logs l1 INNER JOIN Logs l2 ON l1.num = l2.num AND l1.id + 1 = l2.id INNER JOIN Logs l3 ON l2..

HackerRank The Report [ MySQL ]

The Report | HackerRank Write a query to generate a report containing three columns: Name, Grade and Mark. www.hackerrank.com 조건문과 조인 Between을 사용하였습니다. 문제에 맞게 정렬 순서를 정하였습니다. SELECT CASE WHEN g.grade < 8 THEN NULL ELSE s.name END AS name , g.grade , s.marks FROM students s INNER JOIN grades g ON s.marks between g.min_mark AND g.max_mark ORDER BY g.grade DESC, name, s.marks

LeetCode Department Highest Salary [ MySQL ]

Department Highest Salary - LeetCode Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview. leetcode.com Subquery를 사용하여 가장 높은 salary를 구해 department 단위로 조인을 했습니다. 그리고 department name을 출력하기 위해 department와 inner join을 하였습니다. SELECT d.name AS department , e.name AS employee , e.salary FROM employee AS e INNER JOIN..