Question

Table name: Person

Column Name	Type
id	int
email	varchar

Each row of this table contains an email. The emails will not contain uppercase letters.

Write a solution to delete all duplicate emails, keeping only one unique email with the smallest id.
For SQL users, please note that you are supposed to write a DELETE statement and not a SELECT one.
The final order of the table does not matter.

Explaination

Usually, people will simply replace the old table with the new table using SELECT to remove the duplicates.
The tricky part of this question is that you cannot create a new table, as the system only checks the original table Person.

Since we cannot use SELECT, the only way to filter out unwanted rows is to use WHERE or HAVING.

First of all, we can call the same table twice by giving them different name temporarily, so that we can compare between rows,

We need to keep the same email which has smaller id, so p.Id > q_Id, then we also need to nake sure that there are acutally duplicate email, so p.Email=q.Email

Question

Table name: Patients

Column Name	Type
patient_id	int
patient_name	varchar
conditions	varchar

conditions contains zero or more code separated by spaces.
This table contains information of the patients in the hospital.

Write a solution to find the patient_id, patient_name, and conditions of the patients who have Type I Diabetes.
Type I Diabetes always starts with DIAB1 prefix. Return the result table in any order.

Explaination

SQL LIKE Operator

In SQL, to extract data that contain certain specified pattern, we can use LIKE in the WHERE clause.
For example, we can use

SELECT * FROM Patients WHERE conditions LIKE 'DIAB1%'

In here, % is a wildcard, refers to any number of characters can exist after the string DIAB1.
DIAB1, DIAB1234, DIAB1eg are valid outputs.

Question

Table name: Users

Column Name	Type
user_id	int
name	varchar

This table contains the user_id and the name of the user. The name consists of only lowercase and uppercase characters.

Write a solution to fix the names so that only the first character is uppercase and the rest are lowercase.
Return the result table ordered by user_id.
The result format is in the following example.

Explaination

We can split the tasks into three part.

1. Split the name into first character and the remaining characters → SUBSTRING()
2. Modify them into upper case and lower case respectively → UPPER() and LOWER()
3. Order the result by user_id → ORDER BY

SQL SUBSTRING() Function

For extracting characters from a string, we can use the function SUBSTRING(<string>, start, end).
For example,

Question

Table name: Employees

Column Name	Type
id	int
name	varchar

id is the primary key (column with unique values) for this table.
Each row of this table contains the id and the name of an employee in a company.

Table name: EmployeeUNI

Column Name	Type
id	int
unique_id	int

(id, unique_id) is the primary key (combination of columns with unique values) for this table.
Each row of this table contains the id and the corresponding unique id of an employee in the company.

Write a solution to show the unique ID of each user,
If a user does not have a unique ID replace just show null.
Return the result table in any order.

Explaination

In this question, we need to use JOIN:

Question

Table name: Tweets

Column Name	Type
tweet_id	int
content	varchar

tweet_id is the primary key (column with unique values) for this table.
This table contains all the tweets in a social media app.

Write a solution to find the IDs of the invalid tweets.
The tweet is invalid if the number of characters used in the content of the tweet is strictly greater than 15.
Return the result table in any order.

Explaination

To check the number of character, we can use the LENGTH(<string>) function.

If you use this function on numbers (e.g. int), it will turn it into string and return number of digits.

Solution

SELECT tweet_id FROM Tweets WHERE LENGTH(content)>15;

Question

Table name: Views

Column Name	Type
article_id	int
author_id	int
viewer_id	int
view_date	date

There is no primary key (column with unique values) for this table, the table may have duplicate rows.

Each row of this table indicates that some viewer viewed an article (written by some author) on some date.

Note that equal author_id and viewer_id indicate the same person.

Write a solution to find all the authors that viewed at least one of their own articles.
Return the result table sorted by id in ascending order.

Explaination

We have two tasks:

1. Authors viewed their own articles → WHERE author_id = viewer_id
2. Sort table by id in ascending order → ORDER BY author_id ASC

Question

Table name: World

Column Name	Type
name	varchar
continent	varchar
area	int
population	int
gdp	bigint

name is the primary key (column with unique values) for this table.

Each row of this table gives information about the name of a country,
the continent to which it belongs, its area, the population, and its GDP value.

Big Country

A country is big if:

• it has an area of at least three million (i.e., 3000000 km2), or
• it has a population of at least twenty-five million (i.e., 25000000).

Write a solution to find the name, population, and area of the big countries.
Return the result table in any order.

Explaination

Question

Table name: Customer

Column Name	Type
id	int
name	varchar
referee_id	int

id is the primary key column for this table.
Each row of this table indicates the id of a customer, their name, and the id of the customer who referred them.

Find the names of the customer that are not referred by the customer with id = 2.
Return the result table in any order.

Explaination

Again we use SELECT <column_name> FROM <table_name> WHERE <conditions> to get the solution.

The tricky part is some of the referee_id may contain NULL value.

NULL handling

We may simply think that the solution is

Question

Table name: Products

Column Name	Type
product_id	int
low_fats	enum
recyclable	enum

product_id is the primary key (column with unique values) for this table.

low_fats is an ENUM of type (Y, N) where Y means this product is low fat and N means it is not.

recyclable is an ENUM of types (Y, N) where Y means this product is recyclable and N means it is not.

Write a solution to find the ids of products that are both low fat and recyclable.
Return the result table in any order.

Explaination

This is one the most basic SQL SELECT query scenario,
by using SELECT <column_name> FROM <table_name> WHERE <conditions> you may get the solution easily.

Solution

This is a portal for my walkthroughs of SQL 50 on Leetcode

Link of Leetcode SQL 50

Select
1757. Recyclable and Low Fat Products
584. Find Customer Referee
595. Big Countries
1148. Article Views I
1683. Invalid Tweets

Basic Joins
1378. Replace Employee ID With The Unique Identifier
1068. Product Sales Analysis I
1581. Customer Who Visited but Did Not Make Any Transactions
197. Rising Temperature

Advanced String Functions / Regex / Clause
1667. Fix Names in a Table
1527. Patients With a Condition
196. Delete Duplicate Emails
176. Second Highest Salary
1484. Group Sold Products By The Date
1327. List the Products Ordered in a Period
1517. Find Users With Valid E-Mails