SQL Extended Flashcards
You can use TRUNCATE or DELETE FROM without a WHERE clause to delete ALL ROWS from a table. Which one is better and why?
TRUNCATE is better to delete all rows from a table as it is much faster than DELETE FROM and automatically resets any auto-incremented counters associated with the table so any new data inputted starts at 0.
What are aggregate functions in SQL?
Functions that perform a calculation on a set of values (e.g. find the SUM/MAX/AVG/MIN/COUNT) and return a single resulting value of that calculation.
Examples:
MIN() - returns the smallest value within the selected column
MAX() - returns the largest value within the selected column
COUNT() - returns the number of rows in a set
SUM() - returns the total sum of a numerical column
AVG() - returns the average value of a numerical column
How to get the highest value stored in a column called Prices in a table called Shopping?
SELECT MAX(Prices) FROM Shopping;
Generally you would give this an alias by using AS aliasName for clarity.
What is the HAVING statement and what is the difference between it and the WHERE statement?
The HAVING statement works exactly the same as the WHERE statement but is executed earlier by SQL compiler (before GROUP BY) whereas WHERE is executed after GROUP BY. This means that you can use HAVING to filter results in a GROUP BY query.
HAVING x
E.g. HAVING >1
So to filter by WHERE you have to write the WHERE before GROUP BY but HAVING is used AFTER a GROUP BY.
What is REGEXP in SQL?
REGEXP is a powerful way to pattern match/search for specific things at any point in a value/row.
How to search a range of letters N to Q with REGEXP?
WHERE columnName REGEXP ‘[N-Q]’
This will match any results containing any UPPERCASE letter from the range of N-Q.
How to search for any text that has three chars followed by a “n”?
REGEXP ‘___n’
The _ matches to any SINGLE character in SQL REGEXP. So “shn” “23n” “%!n” would all match.
How would you search a column for any value containing the word “field” followed by a 2, 7, 9?
REGEXP ‘field[279]’
Would match “field2321”, “field923124”, “field7sajhf”, etc
What symbol would you use as a logical OR operator inside a regexp to search for values containing cat OR dog?
|
The pipe symbol | represents OR inside a regex search. E.g.
REGEXP ‘cat|dog’
Returns any values from the selected data that have cat or dog in them at any point.
You can use it inside a () for multiple phrases starting/ending with x. E.g.
REGEXP ‘(cat|dog)man’ returns any values with specifically catman OR dogman
How would you pick the final 3 results from a selection of 10?
SELECT *
FROM x LIMIT 7, 3
The first number is the OFFSET (how many rows to skip) and the second number is how many results to retrieve/include in the SELECT.
LIMIT clause is always at the end of a statement.
Where is the LIMIT clause always found in the order of clauses?
LIMIT clause is always at the end of a statement.
E.G. after WHERE etc.
What is a self join?
Self join in SQL is just joining a table with itself (kind of duplicates it temporarily in memory to compare against itself). This is useful for comparing rows against each other inside the same table.
Normally you use an alias to differentiate the two joined table instances via AS keyword.
What is the USING clause?
USING is a way to simplify JOINs only where the column names in both tables you are trying to join are EXACTLY THE SAME.
E.g.
customer_id customer_name
1 Alice
2 Bob
3 Charlie
Orders Table
order_id customer_id order_date
101 1 2024-11-01
102 2 2024-11-02
103 1 2024-11-03
SELECT customer_name, order_id, order_date
FROM customers
JOIN orders USING (customer_id);
Result
customer_name order_id order_date
Alice 101 2024-11-01
Bob 102 2024-11-02
Alice 103 2024-11-03
What does the UNION operator do?
Does UNION include duplicate data rows?
The UNION operator returns the combined results of two or more SQL SELECT statements.
By default UNION removes any duplicate rows.
UNION ALL returns all rows including duplicates.
What is the purpose of the IN operator?
IN is used to select results/filter them based on a subquery or a list of columns.
So it is used for example when you want to find a name of an employee in one of multiple columns where another columns value (lets say the persons net worth) is above £200,000.
SELECT name, department_id, 2nd_department_id
FROM employees
WHERE department_id IN (
SELECT department_id, 2nd_department_id
FROM departments
WHERE net_worth > 200000
);
