LeetCode SQL Study Plan 3rd Day

1st Question:

Question:
user_id is the primary key for this table.
This table contains the ID and the name of the user. The name consists of only lowercase and uppercase characters.

Write an SQL 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.

Answer:

select user_id, CONCAT(UPPER(LEFT(name,1)), LOWER(SUBSTR(name,2))) AS name
from Users
order by user_id ASC;

In this query that selects the user ID and a calculated “name” column from the “Users” table, and orders the results by the user ID in ascending order.

The query is selecting the user_id and a calculated column name. The calculated column is using the CONCAT() function to combine multiple string manipulation functions together.

It’s using the UPPER() function to convert the first letter of the name to uppercase, LEFT() function to select first letter of the name, LOWER() function to convert the remaining letters of the name to lowercase, and the SUBSTR() function to select the substring starting from second letter of the name.

Then the CONCAT() function is used to join these string manipulations and create a new name column.

The ORDER BY statement is used to sort the results by user_id in ascending order (ASC).

This query is returning the user_id and the modified name, with the first letter of the name being in uppercase and the remaining letters in lowercase.

2nd question:

1484. Group Sold Products By The Date

Question:
There is no primary key for this table, it may contain duplicates.
Each row of this table contains the product name and the date it was sold in a market.

Write an SQL 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.

Answer:

select sell_date,count(distinct product) as num_sold,Group_concat(distinct product order by product asc) as products
from Activities
group by sell_date;

In this query that selects the sell date, the number of distinct products sold, and a list of the distinct products sold on each date from the “Activities” table, and groups the results by sell date.

Get Ashutoshgole’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

The query is selecting the sell_date, a count of distinct product sold on that date and a list of distinct product sold on that date.

The COUNT() function is used to count the number of distinct product sold on each date, and GROUP_CONCAT() function is used to concatenate all distinct products sold on each date into a single string, separated by a comma.

It’s using the DISTINCT keyword to make sure that it only counts and lists unique product names on each date.

The GROUP BY statement is used to group the results by sell_date, and the GROUP_CONCAT() function have an ORDER BY clause to sort the products before concatenating them.

This query is returning the sell_date, the number of unique products sold on each date and the list of the unique products sold on each date, all grouped by sell_date.

3rd question:

1527. Patients With a Condition

Question:
patient_id is the primary key for this table.’conditions’ contains 0 or more code separated by spaces. This table contains information of the patients in the hospital.

Write an SQL 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.

Answer:

SELECT * FROM Patients
WHERE conditions REGEXP '^DIAB1| DIAB1';

In this query that selects all columns from the “Patients” table where the “conditions” column matches a specific pattern.

The query is using the SELECT statement to select all columns from the Patients table.

The WHERE clause is using the REGEXP operator to perform a regular expression match against the conditions column. The regular expression being used is ‘^DIAB1| DIAB1’, which is matching the start of the string ‘^DIAB1’ or matches a space followed by DIAB1 ‘ DIAB1’.

The ^ symbol is used to match the start of the string, | is used as an OR logical operator and ‘ ‘ (space) is used to match the space before DIAB1.

This query is returning all rows from the Patients table where the conditions column starts with the string “DIAB1” or contains the string “ DIAB1” (with a space in front).