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

Golden Rule of becoming a native Cantonese speaker: Tones > Everything!

In this blog, we use Jyutping to indicate the pronunciation of Cantonese characters.

Previous lesson: Rimes with e

---

In this lesson, we talk about Rimes with u, yu. Here we use u similar to languages like German or Italian.

And yu is equivalent to “ü” in German. Let’s take a look.

Rimes with u

For u, ui, un, ut, u sounds like “oo” in “foo”.

For ung and uk, u sound like “one” in “tone”.

Jyutping	Sounds in English	Cantonese Example	Jyutping	Sounds in English	Cantonese Example
u	oo in foo	夫(fu1)	ut	oot in boot	闊(fut3)
ui	ewy in chewy1	灰(fui1)	ung	one in tone	風(fung1)
un	oon in cartoon	歡(fun1)	uk	ook in cook	福(fuk1)

Golden Rule of becoming a native Cantonese speaker: Tones > Everything!

In this blog, we use Jyutping to indicate the pronunciation of Cantonese characters.

Previous lesson: Rimes with e

---

In this lesson, we talk about Rimes with i and o. Both are pretty intuitive for English speakers.

Rimes with i

For i, iu, im, in, ip, it, i sounds like “yee”. For example, “ee” in “see” or “keen”.

For ing and ik, i sound like “egg”. For example, “i” in “ignore” or “sing”.

Jyutping	Sounds in English	Cantonese Example	Jyutping	Sounds in English	Cantonese Example
i	ee in see	思(si1)	ip	eep in weep	攝(sip3)
iu	eal in seal	燒(siu1)	it	eet in meet	舌(sit3)
im	eam in beam	閃(sim2)	ing	ing in sing	升(sing1)
in	een in seen	先(sin1)	ik	ic in acidic	識(sik1)

Rimes with o