본문 바로가기

SQL

 (40)
[SQL] Mode 프로젝트 보호되어 있는 글입니다.
[SQL] 윈도우 함수-집계함수 사용 예시 보호되어 있는 글입니다.
[SQL] LeetCode: 177. Nth Highest Salary 177. Nth Highest Salary # 사용자 정의 함수 문제 문제 Write an SQL query to report the nth highest salary from the Employee table. If there is no nth highest salary, the query should report null. 풀이1) CASE문 + FROM절 서브쿼리 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 OR..
[SQL] HackerRank: Weather Observation Station 7, 8, 9 Weather Observation Station 7 문제 Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. Your result cannot contain duplicates. 풀이(정규표현식 사용) [ ]$ : [ ]안에 있는 문자 중에 하나로 끝난다 Weather Observation Station 8 문제 Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. Your result cannot contain duplicates. 풀이(정규표..
[SQL] HackerRank: Weather Observation Station 6 Weather Observation Station 6 문제 Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates. -중복없이 모음으로 시작하는 도시 이름 출력 풀이1) LIKE 활용 풀이2) 정규표현식 활용(Regular Expressions) ^ [aeiou] : [ ] 안 문자 중 하나로 시작하는 (^ 시작을 의미) .* : %와 같은 역할(와일드 카드)
[SQL] COUNT 함수 COUNT(컬럼1, 컬럼2) - 두 컬럼의 조합을 카운트
[SQL] LeetCode: 185. Department Top Three Salaries 185. Department Top Three Salaries 문제 A company's executives are interested in seeing who earns the most money in each of the company's departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department. Write an SQL query to find the employees who are high earners in each of the departments. Return the result table in any order. 풀이 # ..
[SQL] LeetCode: 184. Department Highest Salary (윈도우 함수) 184. Department Highest Salary 문제 Write an SQL query to find employees who have the highest salary in each of the departments. Return the result table in any order. 풀이1) 윈도우 함수 MAX( ) + FROM절 서브쿼리 사용 SELECT department , employee , salary FROM( SELECT d.name AS department , e.name AS employee , e.salary , MAX(salary) OVER (PARTITION BY e.departmentid) AS max_salary FROM employee e INNER JOIN depa..

티스토리툴바