Consecutive Numbers
문제 설명
Create table If Not Exists Employee (id int, name varchar(255), salary int, departmentId int)
Create table If Not Exists Department (id int, name varchar(255))
Truncate table Employee
insert into Employee (id, name, salary, departmentId) values ('1', 'Joe', '70000', '1')
insert into Employee (id, name, salary, departmentId) values ('2', 'Jim', '90000', '1')
insert into Employee (id, name, salary, departmentId) values ('3', 'Henry', '80000', '2')
insert into Employee (id, name, salary, departmentId) values ('4', 'Sam', '60000', '2')
insert into Employee (id, name, salary, departmentId) values ('5', 'Max', '90000', '1')
Truncate table Department
insert into Department (id, name) values ('1', 'IT')
insert into Department (id, name) values ('2', 'Sales')
Table: Employee
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| id | int |
| name | varchar |
| salary | int |
| departmentId | int |
+--------------+---------+
id is the primary key column for this table.
departmentId is a foreign key of the ID from the Department table.
Each row of this table indicates the ID, name, and salary of an employee. It also contains the ID of their department.
Table: Department
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
id is the primary key column for this table.
Each row of this table indicates the ID of a department and its name.
입출력 예
Example 1:
Input:
Employee table:
+----+-------+--------+--------------+
| id | name | salary | departmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department table:
+----+-------+
| id | name |
+----+-------+
| 1 | IT |
| 2 | Sales |
+----+-------+
Output:
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
| IT | Max | 90000 |
+------------+----------+--------+Explanation: Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.
Oracle Query
select
D.Name as Department, E.Name as Employee, Salary
from Employee E inner join Department D
on E.DepartmentID = D.ID
where Salary = (select max(Salary)
from Employee
where E.DepartmentID = DepartmentID);
출처
'코딩테스트 > Leet Code' 카테고리의 다른 글
| [Leet Code SQL] 595. Big Countries (0) | 2022.03.04 |
|---|---|
| [Leet Code] 217. Contains Duplicate python (0) | 2022.02.25 |
| [Leet Code] 762. Prime Number of Set Bits in Binary Representation (0) | 2022.02.11 |
| [Leet Code] 342_Power of Four (0) | 2022.02.11 |
| [Leet Code] 622. Design Circular Queue (0) | 2022.01.21 |