Liuye Notebook
0%

Algorithm-SQL

发表于 更新于 分类于 ， 阅读次数： Valine：

阅读更多

1 Question-175[★★★]

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Table: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+

Table: Address
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
1
2
3
SELECT Person.FirstName, Person.LastName, Address.City, Address.State
FROM Person LEFT JOIN Address
ON Person.PersonId = Address.PersonId

2 Question-182[★★★★★]

Duplicate Emails

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Table: Person
+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

-->

+---------+
| Email   |
+---------+
| a@b.com |
+---------+
1
2
3
SELECT Email FROM Person
GROUP BY Email
HAVING COUNT(*) > 1
  1. 1 Question-175[★★★]
  2. 2 Question-182[★★★★★]