SQL Interview Questions
SQL interview questions, especially if done during a live session, can be challenging; however, knowing in advanced which are the most common ones allows you to organize your thoughts and expose structured answers that will clearly show to your interviewer an high level of preparation. Based on my experience, these are the most common one you can’t miss if you want to increase a chance to ace your next SQL interview.
1. What’s the difference between Delete, Truncate and Drop?
This is probably the most asked question for junior positions and not… and there is also a very good reason for that: confusing Delete with Truncate and Drop might lead you to a very hard time in your dream company ;)
Even though the risk of misuse Truncate and Drop commands is quite low, as just people with an high level of privilege in the organization are allow to use them, it’s always a good idea to know exactly what they do and what’s their differences.
DELETE: this command removes row/s based on specific conditions and it’s particularity is that generally it can be rolled back if for any reason we have to; this retrieve the deleted records restoring so the table conditions as it was initially.
DELETE FROM employees WHERE department = 'Sales'TRUNCATE: this command removes all rows in the table but it retains the table structure for future reuse. Unlike DELETE, TRUNCATE is very fast in executing the command as it doesn’t generate individual row delete logs and so it cannot be rolled back
TRUNCATE TABLE employeesDROP: this command instead, completely remove a table from a database and so also its content and structure. DROP command is not reversible which means that once executed it cannot be recovered.
DROP TABLE employees2. What is a Primary Key and a Foreign Key?
Primary Keys (or PK) and a Foreign Keys are fundamental concept of a relational database which allow tables to communicate to each other; more specifically:
a. A PK is a unique identifier for a record in a table and it cannot be NULL while
b. A Foreign Key (FK) is a field in one table that references the Primary Key (PK) in another table. This relationship ensures that the value/s in the FK column corresponds to an existing value in the PK column, maintaining referential integrity. The FK values can be repeated and are used to link records between tables.
3. Difference between WHERE and HAVING?
While the WHERE clause is used to filter rows before aggregation, the HAVING clause filters data after they have been aggregated.
For example, using the code below, we first filter out all rows with a sales value greater than 160 and then aggregate the remaining rows by region to get the total sales for each region.
SELECT
region,
SUM(sales) AS Tot_Sales
FROM employee AS e
WHERE sales < 160
GROUP BY Region
ORDER BY Tot_Sales
In this other scenario instead, we use all rows in the original dataset to calculate the total sales for each region; just after the aggregation the HAVING clause filters in only the rows where the total sales are less than 160.
SELECT
region,
SUM(sales) AS Tot_Sales
FROM employee AS e
GROUP BY Region
HAVING SUM(sales) < 160
ORDER BY Tot_Sales
4. What are the most common JOINs in SQL?
JOINS are used to combine rows from two or more tables based on related columns. The most common joins are:
a. Inner join — return just rows contained in both tables
b. Full join — return all rows in both tables regardless there is a match between tables or not
c. Left join — returns all rows from the left table and just only matching rows from the right table
d. Right join — Returns all rows from the right table and just only matching rows from the left table
5. Let’s say you have a Sales table and you want to filter transactions from the previous 90 days till yesterday (included), how would you do it?
One way to filter sales transactions starting from the last 90 days till yesterday is to use the “dateadd” functions like the following code:
SELECT *
FROM Employee
WHERE date >= DATEADD(day, -90, CAST(GETDATE() AS Date))
AND date < CAST(GETDATE() AS Date);The following piece of code first calculates the current date using the GETDATE function and casts it as a date (removing the time part). Then, using the DATEADD function, it subtracts 90 days from the current date and returns the corresponding date (current date - 90 days).
DATEADD(day, -90, CAST(GETDATE() AS Date))In summary, it filters in all dates which are greater than or equal to “current date minus 90 days” AND less than today (which includes yesterday).
6.What is a subquery, and what are its types?
A subquery is a query within another query. Types of subqueries:
a. Correlated Subquery: Depends on the outer query for its values. It is evaluated once for each row processed by the outer query.
SELECT employee_id, name
FROM employees e1
WHERE salary > (SELECT AVG(salary)
FROM employees e2
WHERE e1.department_id = e2.department_id);b. Non-correlated Subquery: Independent of the outer query. It can be executed on its own and is evaluated only once.
SELECT employee_id, name
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments
WHERE location = 'New York');7. In what order does SQL process query commands?
Even though SQL commands are written in a way that makes logical sense to humans, enhancing readability (SELECT, FROM, WHERE, GROUP BY, HAVING, etc.), they are processed by SQL engines in a different, specific sequence to optimize performance and ensure accurate results. The machine’s logical order is:
FROM: Specifies the tables to retrieve data from.
WHERE: Filters rows based on conditions.
GROUP BY: Groups rows that share a common attribute.
HAVING: Filters groups based on conditions.
SELECT: Selects the columns to display.
ORDER BY: Sorts the result set.
LIMIT: Limits the number of rows returned (specific to some SQL dialects).
8. How can we optimize our query to run as fast as possible?
Optimizing SQL queries is essential for improving performance and ensuring efficient data retrieval. Some good practice to achieve this result are:
a. Joins: Ensure proper use of JOINs and avoid unnecessary complex joins. Simplify join conditions and make sure to join only the necessary tables.
b. Filters: Apply all applicable filters under the WHERE clause to ensure that non-essential records are filtered out early in the query process. This reduces the amount of data the database engine needs to process.
c. Granularity of Data: Consider using pre-aggregated tables if the level of detail required for your analysis allows it. For example, use a monthly aggregated sales table instead of a daily one if monthly data suffices.
d. Avoid SELECT : Avoid using the SELECT statement, as it retrieves all columns in the table, which can be computationally expensive. Instead, specify only the columns needed.
e. Subqueries: Use subqueries wisely and avoid them in the SELECT statement if they can be replaced with JOINs, which are generally more efficient.
f. Temporary Tables: When working with large datasets, consider using temporary tables. Apply all applicable filters to each temporary table to reduce its size, then join them if necessary to achieve the desired outcome.
9. Difference between a Group By and a Window function
GROUP BY and Window functions are used for aggregating data but serve different purposes:
a. GROUP BY: Aggregates data by grouping rows that have the same values in specified columns. As consequences, it reduces the number of rows in the result set.
SELECT
Region,
SUM(sales) AS Tot_Sales
FROM employee AS e
GROUP BY Region
ORDER BY Tot_Salesb. Window Functions: Perform calculations across a set of table rows related to the current row. They do not reduce the number of rows in the result set; indeed the dataset structure and granularity remain the same as before
SELECT
*,
SUM(Sales) OVER(PARTITION BY Region) as Tot_Sales_By_Region
FROM Employee
ORDER BY Tot_Sales_By_Region
Thanks for being a part of our community!
If you found this article helpful and would like to show your support, don’t hesitate to:
Clap on this story
Leave a comment below telling me what you think. This will help me with the next articles
Support my work on Buy Me a Coffee ☕️
These actions really really help me out, and are much appreciated!
Comments