2_Query Fundamentals

Question 1

Joins

There are four different types of JOIN, but in most cases, we only use INNER, LEFT and FULL JOIN, because the RIGHT JOIN is not very intuitive and can be easily rewritten using LEFT JOIN.

Note: CROSS JOIN results in a Cartesian product. This means it will return all the combinations of rows from one table with all the combinations of rows from the other table. It is usually avoided.

Question 2

Join Example

Find the total number of classes taken by each student. (Provide student id, name and number of classes taken.)

Not all students appearing in theclass_history table are present in thestudent table, which might be because those students are no longer enrolled. There is also a student (student_id = 6) with no class history. Depending on whether the interviewer wants to include inactive students and students who didn’t register for any classes in the results, we need to use either LEFT JOIN or INNER JOIN or FULL OUTER JOIN to combine two tables

Answer 2

Question 3

Aggregate Functions

The aggregate functions perform a calculation on a data set and return a single value as a result. Example of the aggregate functions are:

• COUNT()
• SUM()
• MIN()
• MAX()
• AVG()
• STDEV()
• VAR()

Question 4

GROUP BY

GROUP BY is the most essential function in SQL since it is widely used for data aggregation. If you see keywords such as sum, average, minimum, or maximum in a SQL question, it is a big hint that you should probably use GROUP BY in your query. A common pitfall is mixing WHERE and HAVING when filtering data along with GROUP BY

Question 5

HAVING

The HAVING clause also filters data according to the specified criteria. The difference compared to the WHERE clause is that the HAVING clause works with the aggregate functions. Therefore, if used, it always follows the GROUP BY clause and precedes the ORDER BY clause.

Question 6

UNION

This is an SQL command that will combine the result of one query with the result of another query. Therefore, it will show only unique records.

UNION ALL

This one also combines the results from two or more queries. The difference between UNION and UNION ALL is it will also include duplicates.

Question 7

SQL Execution Order

• FROM, JOIN
• WHERE
• GROUP BY
• HAVING
• SELECT
• DISTINCT
• ORDER BY
• LIMIT, OFFSET

Question 8

DELETE vs TRUNCATE

DELETE is a DML statement. TRUNCATE is a DDL statement. The DELETE statement can be used to delete all rows or only some rows. To delete some rows, you’ll have to use the WHERE clause. While doing this, every row removed will be logged as an activity by the database. On the other hand, TRUNCATE is used only for deleting the whole table, which will be logged as only one action. That’s why TRUNCATE is faster than DELETE, which shows when deleting a table with a huge amount of data. Also, you can’t use TRUNCATE if there’s a foreign key in the table.