SQL

Question 1

What does SQL stand for?

Answer 1

Structured Query Language

Question 2

LIKE operator syntax

Answer 2

Used to search for a specified pattern in a column. % represents zero, one, or multiple characters. _ represents a single character.
(SELECT * FROM employees WHERE name LIKE ‘Joh%’; - retrieves all employees whose names start with “Joh”)

Question 3

Brackets order of operation

Answer 3

Brackets prioritise everything within the brackets over the code outside the brackets. This means the combination of filters within the brackets will run first and the remaining filter conditions will run after

Question 4

Grouping data in SQL

Answer 4

Columns in the SELECT statement either need an aggregation function applied to them OR need to be included in the GROUP BY statement

Question 5

CASE WHEN structure

Answer 5

Allows you to create custom columns based on conditions. You can group your data based on a custom column created within the same query

Question 6

HAVING VS WHERE

Answer 6

Only apply HAVING if you need to filter aggregated data, otherwise use WHERE.
GROUP BY always gets executed before HAVING. Because of that, we can only filter in HAVING using columns available in GROUP BY

Question 7

How to explore a table quickly (syntax)

Answer 7

SELECT * FROM new_table LIMIT 5;

Question 8

Query order of operations

Answer 8

FROM - Get data from the table(s)
WHERE - Filter rows
GROUP BY - Group the data
HAVING - Filter grouped data
ORDER BY - Order the data
SELECT - Select columns
LIMIT - Limit the number of rows

Question 9

What is a primary key?

Answer 9

A column that is the unique identifier of a specific table. One value of the column represents one unique row in the table

Question 10

What is a foreign key?

Answer 10

A column that is not the unique identifier of the table it is stored in, but it is a unique identifier of another table. A foreign key value can appear more than once in the table

Question 11

When do we get duplicated rows?

Answer 11

When both join keys used in the JOIN are not unique identifiers of the tables

Question 12

Types of JOIN

Answer 12

INNER JOIN - only includes rows where the join keys match
LEFT JOIN - includes all rows of the “left” table and the matches from the right table
RIGHT JOIN - includes all rows of the “right” table and the matches from the left table
FULL JOIN - includes all rows from both tables even if there are no matches in the join key

Question 13

JOIN syntax

Answer 13

SELECT
<table_1.column_1> ,
<table_1.column_2>
FROM
<table_1>
_ JOIN -- _ can be replaced with INNER, or any of the other JOIN types (LEFT, RIGHT, FULL)
<table_2> ON <table_1.join_key> = <table_2.join_key>
WHERE …;</table_2.join_key></table_1.join_key></table_2></table_1></table_1.column_2></table_1.column_1>

Question 14

Do join keys need to be the same between joins?

Answer 14

No

Question 15

Multiple join syntax with multiple join keys

Answer 15

SELECT
<table_1.column_1> ,
<table_1.column_2>
FROM
<table_1>
_ JOIN -- _ can be replaced with INNER, or any of the other JOIN types (LEFT, RIGHT, FULL)
<table_2>
ON
<table_1.join_key_1> = <table_2.join_key_1>
AND -- the AND keyword let us use JOIN tables using more than one join key
<table_1.join_key_2> = <table_2.join_key_2></table_2.join_key_2></table_1.join_key_2></table_2.join_key_1></table_1.join_key_1></table_2></table_1></table_1.column_2></table_1.column_1>

Question 16

What does UNION allow to do?

Answer 16

UNION allows us to write two queries, which get combined into a final query result

Question 17

Only requirement for UNION to work

Answer 17

The alignment of the number of columns and the data types of each column between the two queries

Question 18

UNION ALL VS UNION DISTINCT

Answer 18

UNION ALL shows all rows from the two queries combined, whereas UNION DISTINCT only shows unique rows

Question 19

COALESCE()

Answer 19

Allows us to fill NULL values in a column (works with any data type):
– COALESCE(<column>, value if <column> is NULL)
SELECT COALESCE(country, 'Other') AS country,
sales_team
FROM meta_clients;
You can actually use another column to fill populate the NULL value as well:
SELECT COALESCE(country, sales_team) AS country,
sales_team
FROM meta_clients;
You can actually use more than one possible fill value - if the first two columns have a NULL value, the third one will populate the column:
SELECT COALESCE(country, sales_team, 'Other') AS country,
sales_team
FROM meta_clients;</column></column>

Question 20

LOWER() and UPPER()

Answer 20

To handle capitalisation in text

Question 21

TRIM()

Answer 21

Removes all whitespace before and after the text in a cell

Question 22

REPLACE()

Answer 22

To change or remove unwanted text from a string column:
SELECT region,
REPLACE(region, ‘.’, ‘’) AS cleaned_region
FROM meta_clients;

Question 23

SUBSTRING()

Answer 23

Used to extract a substring from a string:
SELECT SUBSTRING(‘String expression’, start position, length) AS extracted_string;

Question 24

REGEXP_LIKE

Answer 24

Checks if a string matches a regular expression pattern:
SELECT * FROM meta_clients WHERE REGEXP_LIKE(industry, ‘Retail|Consumer Goods’, ‘i’); – use ‘i’ for case insensitive, which will search for all the statements that are presenting the same words within the specified column instead of having to specify all the capitals/lower case, etc.

Question 25

REGEXP_REPLACE

Answer 25

Replaces substrings in a string based on a regular expression pattern:
SELECT client_id,REGEXP_REPLACE(client_id, ‘[0-9_]’, ‘’, ‘g’)
– g for global, which means all the patterns identified will be replaced

Question 26

CAST()

Answer 26

Used to convert an expression from one data type to another:
SELECT CAST(‘123’ AS INT);
You can also cast like this: ::numeric
Syntax: SELECT expression::new_data_type AS new_column_name
FROM your_table;

Question 27

ROUND()

Answer 27

Used to round a numeric value to a specified number of decimal places:
Syntax: ROUND(number, decimals)
ROUND(revenue, 2)

Question 28

EXTRACT()

Answer 28

To extract individual components from dates or timestamps:
EXTRACT(YEAR FROM last_updated_date) AS year
EXTRACT(MONTH FROM last_updated_date) AS month
EXTRACT(DAY FROM last_updated_date) AS day

Question 29

Filtering dates using EXTRACT()

Answer 29

Very effective because it removes the need to use the entire date format:
WHERE EXTRACT(MONTH FROM last_updated_date) = 5
AND EXTRACT(YEAR FROM last_updated_date) = 2023

Question 30

TO_DATE()

Answer 30

Used to convert a string representing a date into a date data type:
SELECT TO_DATE(‘2023-01-01’, ‘YYYY-MM-DD’) AS custom_date;

Question 31

CURRENT_DATE()

Answer 31

Returns the current date as a date data type:
SELECT CURRENT_DATE AS today;

Question 32

Why are WITH statements so useful?

Answer 32

Allows you to write a series of simple SQL queries by defining CTE (Common Table Expressions) to solve more complex problems. Simple blocks of code for easy troubleshooting

Question 33

WITH statement syntax

Answer 33

WITH my_new_table AS ( – we give the CTE the alias “my_new_table”

SELECT * FROM table

)

SELECT * FROM my_new_table; – we reference the CTE we created using WITH in our final query

Between multiple CTEs any time another table is coming, you must include a comma. Only after your final table you will not use a comma

Question 34

Subqueries

Answer 34

Created by nesting a SELECT … FROM query within brackets - everything inside the brackets gets executed first

Question 35

Window functions

Answer 35

Allow you to apply functions over a set of rows relative to the current row in a table

Question 36

GROUP BY VS Window functions

Answer 36

The aggregation function together with GROUP BY will aggregate all the rows based on the GROUP BY. The outcome of this is an aggregated table with fewer rows than the original table.
A window function applies OVER the entire table (e.g. ROW_NUMBER) and the query result will contain the same number of rows as the original table.

Question 37

Most used window functions:
ROW_NUMBER()

Answer 37

Assigns a unique integer to rows within a partition of a result set, starting at 1 for the first row in each partition.
ROW_NUMBER() OVER (ORDER BY created_at DESC) AS row_number

Question 38

Most used window functions:
LAG(column_name)

Answer 38

Accesses data from a previous row in the same result set
LAG(created_at) OVER (ORDER BY created_at ASC) AS previous_value

Question 39

Most used window functions:
LEAD(column_name)

Answer 39

Accesses data from a subsequent row in the same result set

Question 40

Most used window functions:
FIRST_VALUE(column_name)

Answer 40

Returns the first value in an ordered set of values

Question 41

Most used window functions:
LAST_VALUE(column_name)

Answer 41

Returns the last value in an ordered set of values

Question 42

PARTITION BY

Answer 42

Can be applied within the window function to reset the window function within a specified group. Allows you to run functions within a particular group and reset the calculation between groups:
ROW_NUMBER() OVER (PARTITION BY state ORDER BY num_customers DESC) AS rank

Question 43

ROLLUP

Answer 43

Can be applied within the GROUP BY to create total and subtotal lines in your query result:
SELECT state AS state,
age_group AS age_group,
COUNT(customer_id) AS num_customers
FROM customers
GROUP BY ROLLUP(state, age_group)
ORDER BY state;