[SQL : Leetcode] 182. Duplicate Emails / 175. Combine Two Tables

1] 182. Duplicate Emails

---

>> 문제

Write a SQL query to find all duplicate emails in a table named Person.

 

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

For example, your query should return the following for the above table:

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

 

 

>> 문제 해결 코드

select Email
from person
group by Email
having count(*) >= 2

 

※ 중복된 email을 구하는 방법으로 그룹바이의 count를 세서 2이상인것을 구했다. 

 

 

 https://leetcode.com/problems/duplicate-emails/

Duplicate Emails - LeetCode

 

2] 175. Combine Two Tables

---

>> 문제

 

Table: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId is the primary key column for this table.

 

Table: Address

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId is the primary key column for this table.

 

Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:

FirstName, LastName, City, State

 

>> 문제 해결 코드

select FirstName, LastName, City, State
from person
    left join Address on person.PersonId = Address.PersonId

 

※ 해결 결과로만 보면, 엄청 간단하다. 그러나 left join 을 써야지 ! 라는 생각이 들려면, 테이블 정의 뿐만 아니라, 어떻게 생겼는지 select를 해보고 출력해야하는 과정이 있어야 했다.

-> 문제를 풀때, 어떤 방식으로 문제에 접근할지 생각하는 것이 중요한 것 같다. 

 

https://leetcode.com/problems/combine-two-tables/

Combine Two Tables - LeetCode