SQL 문제

#1. 

Employees 테이블과 Departments 테이블을 조인하여 

모든 사원의 이름, 직책, 부서 이름, 부서 위치를 조회하세요. 
이때, 사원이 속한 부서가 존재하는 경우에만 결과를 반환해야 합니다.

 

[Employees 테이블]

EmployeeId	EmployeeName	DepartmentId	Position
101	Han	1	Leader
102	Lee	2	Team Member
103	David	1	Team Member

[Departments 테이블]

DepartmentId	DepartmentName	Location
1	Front	1F
2	Back	2F

 

[정답]

SELECT Employees.EmployeeName, Employees.Position, Departments.DepartmentName, Departments.Location
FROM Employees
LEFT JOIN Departments
ON Employees.DepartmentId = Departments.DepartmentId;
WHERE Departments.DepartmentId IS NOT NULL;

 

#2. 

Customers 테이블과 Orders 테이블을 이용하여, 

주문한 고객의 이름과 주문 일자를 조회하는 SQL 쿼리를 작성해보세요. 

이때, 주문한 고객의 이름과 주문 일자만 조회

 

[Customers 테이블]

CustomerId	CustomerName	Country	Email
1	Lee	KOR	lee@lee.com
2	Han	USA	han@han.com

[Orders 테이블]

OrderId	CustomerId	OrderDate	TotalAmount
101	1	2023-05-17	120
102	2	2023-05-18	80

 

[정답]

SELECT Customers.CustomerName, Orders.OrderDate
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerId = Orders.CustomerId;

 

#3. 

Employees 테이블과 Departments 테이블을 조인하여
EmployeeName, Position, DepartmentName, Location을 반환.
Employees 테이블의 DepartmentId와 Departments 테이블의 DepartmentId가 일치해야함.

 

[Employees 테이블]

EmployeeId	EmployeeName	DepartmentId	Position
101	Han	1	Leader
102	Lee	2	Team Member
103	David	1	Team Member

[Departments 테이블]

DepartmentId	DepartmentName	Location
1	Front	1F
2	Back	2F

[정답]

SELECT Employees.EmployeeName, Employees.Position, Departments.DepartmentName, Departments.Location
FROM Employees
LEFT JOIN Departments
ON Employees.DepartmentId = Departments.DepartmentId;