SQL - Leetcode-03

Question 1

Write a query 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.

SQL Schema

For Reference: Create Users Table

Create table If Not Exists Users (user_id int, name varchar(40))
Truncate table Users
insert into Users (user_id, name) values ('1', 'aLice')
insert into Users (user_id, name) values ('2', 'bOB')

Using CONCAT:

SELECT user_id,
CONCAT(UPPER(SUBSTRING(name,1,1)), LOWER(SUBSTRING(name,2))) AS name
FROM Users ORDER BY user_id;

SUBSTRING

The SUBSTRING function allows you to extract a portion of a string, based on a starting position and a length.

Extract the first three characters of each name:

Expression: SUBSTRING(string, start, length)

SELECT SUBSTRING(name, 1, 3) AS extracted_name
FROM Users

QUESTION 2

Write a query to find for each date the number of different products sold and their names.

The sold products names for each date should be sorted lexicographically.

Return the result table ordered by sell_date.

SQL Schema

For Reference: Create Activities Table

Create table If Not Exists Activities (sell_date date, product varchar(20))
Truncate table Activities
insert into Activities (sell_date, product) values ('2020-05-30', 'Headphone')
insert into Activities (sell_date, product) values ('2020-06-01', 'Pencil')
insert into Activities (sell_date, product) values ('2020-06-02', 'Mask')
insert into Activities (sell_date, product) values ('2020-05-30', 'Basketball')
insert into Activities (sell_date, product) values ('2020-06-01', 'Bible')
insert into Activities (sell_date, product) values ('2020-06-02', 'Mask')
insert into Activities (sell_date, product) values ('2020-05-30', 'T-Shirt')

Solution

To aggregate the product names in one cell, we can use GROUP_CONCAT

Method 3:

SELECT sell_date, COUNT(DISTINCT product) as num_sold,
GROUP_CONCAT(DISTINCT product ORDER BY product) AS products
FROM Activities
GROUP BY sell_date ORDER BY sell_date;

QUESTION 3

Write a query to report 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.

SQL Schema

For Reference: Create Patients Table

Create table If Not Exists Patients (patient_id int, patient_name varchar(30), conditions varchar(100))
Truncate table Patients
insert into Patients (patient_id, patient_name, conditions) values ('1', 'Daniel', 'YFEV COUGH')
insert into Patients (patient_id, patient_name, conditions) values ('2', 'Alice', '')
insert into Patients (patient_id, patient_name, conditions) values ('3', 'Bob', 'DIAB100 MYOP')
insert into Patients (patient_id, patient_name, conditions) values ('4', 'George', 'ACNE DIAB100')
insert into Patients (patient_id, patient_name, conditions) values ('5', 'Alain', 'DIAB201')
insert into Activities (sell_date, product) values ('2020-05-30', 'T-Shirt')

Solution

Since the condition is that it must always start with DIAB1, to check for

DIAB1CCC (Should alwasy start with DIAB1), we use 'DIAB1%'
ABCDIAB1ABC (Should not have any character in front of DIAB1), we use '% DIAB1%'

SELECT patient_id, patient_name, conditions
FROM Patients
WHERE conditions LIKE '% DIAB1%'
OR conditions LIKE 'DIAB1%';