Frame as a question
How to filter odd/even Numbers and use NOT LIKE "%text"
LOWER() LIKE %penis%
How can you round an expression to 3 decimal places?
ROUND(result, num_decimal_places)
How can you … Case When
How do you format a date into %Y-%m in SQL?
DATE_FORMAT
How can you prevent division by 0 in SQL?
NULLIF(number, 0) returns NULL if number happens to equal 0.
Note that you could simply change the second argument from 0 to any other number as well.
How could you only take the most recent order date
How would you merge a table named Animals containing various animal names, with another table named AnimalColors that includes columns <animal_id, color>, in order to produce a combined table that lists all animals with their corresponding colors, ensuring that all animals from the Animals table are included, even if their color information is not available in AnimalColors?
Do a left join
How mandatory is the semicolon ; at the end?
The use of the semicolon for single expressions/queries can also vary between different SQL database systems. However, if multiple SQL statements are being executed in a single batch or script, the semicolon is mandatory.
How can you subtract one day from all dates in order to find rows with consecutive dates?
The idea is to match rows
DATE_SUB(your_date, INTERVAL 1 DAY))
What is happening here: MyTable A, MyTable B
- A cross join (hence the comma)
- A self-join of
MyTable - Aliased to
AandB
↳ What happens when you cross join a table with itself?
When you join a table with itself (known as a self-join), you are essentially creating two separate copies or views of the table to compare or relate rows within the same table. This is useful when you want to compare rows within the same table or find relationships based on the columns of the same table. For example, in an employee table, a self-join can be used to find all pairs of employees working in the same department.
In a NATURAL JOIN, how does MySQL decide which column to join on?
In a NATURAL JOIN, MySQL automatically joins tables based on columns with the same names and compatible data types in both tables. It matches columns between the two tables:
- Columns with the same names in both tables are used as join keys.
- The query joins rows from both tables where the values in all the columns with the same names are equal.
- There is no need to specify the column names for the join explicitly.
- If there are no columns with the same name in both tables, the
NATURAL JOINbehaves like aCROSS JOIN.
##### ↳ What happens when you `NATURAL JOIN` a table with itself?
Since NATURAL JOIN joins on columns with the same name and datatype, this will essentially result in the same original table but with duplicated columns.