Fundamental SQL Interview Questions


What are joins in SQL?
A join is an operation that is used to combine data from multiple tables into a new table. Different types of joins specify how data between tables are matched into the new table. When you need to retrieve data from multiple tables in a single query, there’s a good chance that you’ll be using a join operation.

Example: We would want to store a table of customers (with their name, address, company they represent, etc.) separately from a table of transactions (with the items purchased, when the transaction was made, how much items cost, who made the purchase, etc.).

When we store the information of who made the purchase, we would not want to duplicate all of the customer’s information into every row of the transactions table, and we would only want to store the customer ID. In order to answer a question such as “how many purchases were made by customers who live in a specific zip code?”, we would need to join the two tables to get this answer:

SELECT COUNT(*) FROM customers c JOIN transactions t ON t.customer_id = c.id WHERE c.zipcode = 94107;

What is the difference between DELETE and TRUNCATE statements?
This is one of those SQL interview questions designed to gauge your candidate’s understanding of how database rows are stored and managed internally. While DELETE and TRUNCATE can both be used to remove all data from a table, the database processes these queries differently. DELETE operations can filter rows that are targeted as it supports a WHERE clause, whereas a TRUNCATE operation removes an entire table.

When deleting a whole table with these two operations, TRUNCATE performs faster at the expense of being unable to perform some operations that rely on the transaction log. For example, some SQL servers can rollback DELETE operations and not TRUNCATE operations.

In general, you should opt to use the DELETE operation due to benefits such as a history in the transaction log. In situations where a DELETE operation on an entire table takes too long, you can look into using the TRUNCATE operation. 

What is the difference between a primary key and unique key?
Primary keys must be unique and are used to identify table records, whereas unique keys serve as constraints in the table’s data. Understanding unique keys shows the interviewer that you understand some of the important ways to maintain a database table’s data integrity.

For example, when you’re designing a table to track users, you may have a column for user_id as the primary key. user_id will be unique and is used to reference the rest of the data in a row. To maintain data quality, you may have a column for phone_number as a unique key to ensure that only one of each phone number can exist in the table.

What is the purpose of a foreign key in SQL?
Foreign keys are columns used to reference primary keys in other tables. This helps set a constraint on the column to enforce referential integrity across tables. Foreign keys are a fundamental concept in database table design, and interviewers will want to verify that you understand them and have used them before in the past. Understanding them shows that your candidate knows how to design schemas that span across multiple database tables.

For example, imagine that you have two tables: orders and users. Every order should have been created by a user so the orders table can have a foreign key to the user table’s primary key. This constraint ensures that every user defined in the orders table is referencing a valid row.

What are some ways to optimize a query?
Query plans are a useful way to examine how a query will be performed, and can help you better understand what is making your query slow.

For example, prepending a query in PostgreSQL with EXPLAIN will show the query plan for the command. This will reveal the table scans that will be involved in the query.

So if we wanted to check the behavior of the query:
SELECT * FROM table_1;
we can run the query
EXPLAIN SELECT * FROM table_1;
From there, the query plan will break down the various steps that will be taken to execute the query. It’s a common way to identify unnecessary full table scans that could be alleviated with setting up proper indices.

These techniques are important because interviewers want to understand how you may approach troubleshooting database queries. If a candidate only knows how to run a query against the database to measure performance, it shows a lack of familiarity and industry experience with using relational databases.

What are some ways to identify how a query can be optimized?
Queries can be optimized in many ways. A few common examples are as follows:

  • Reduce amount of data to query with WHERE clauses.
  • Limit the amount of useful rows the database needs to query with a LIMIT clause.
  • Add an index on columns that are frequently queried.

What is normalization and what are the advantages of it?
Database normalization is a strategy to efficiently organize data in a database. The goal is to reduce redundant data so that the same data is not stored across multiple tables. Instead, data will be referenced with a primary key.

For example, a table named orders may have a column named user_id. Rather than maintaining a copy of user data in the orders table, we can simply reference that data from another table with a join.

What are entities and relationships?
An entity is an abstraction of a set of related data and are represented as tables. Relationships define how entities are associated with one another.

For example, let’s say we have two tables named orders and users. Our orders and users are our entities. We can imagine that one user could possibly have many orders. Therefore, users can have a “one-to-many” relationship with orders.

How can you set up a table so that queries for certain rows won’t result in full table scans?
Full table scans can be possibly avoided by querying on indexed columns and using limits. Indexed columns help the database optimize how it performs lookups on the tables.
Advanced SQL Interview Questions and Answers

Explain some different types of indexes in SQL.
A clustered index has data physically stored in the same area on a table so that they can be efficiently queried and retrieved together. A non-clustered index is often used for key-based queries whereas a clustered index is often used for ranges.


    What is a scenario when you would choose to use a zero or blank space over a NULL value in a row?
    Using a zero or blank space over a NULL value is a design decision. NULL can represent the absence of data.

    An example can be made with middle names: if a user has not provided a middle name, then their middle name would be NULL. If a user has indicated that they have no middle name, then their middle name would be an empty string.

    If we know we will never care for differentiating between a zero / blank space value and the absence of a value, then we can go ahead and default a column to a zero or blank space value. This might be useful in a table that keeps track of how many times something has occurred (e.g. number of website visits). We have no need to differentiate between 0 and absence of data as the absence of data implies 0, so we can simplify our application code by defaulting to 0.

    What is the difference between a cross join and natural join?
    When unspecified, a join will default to using an inner join. While there’s a chance that you may not have used a cross join or natural join before, understanding different join types can show your interviewer that you are familiar with different ways to combine data across tables.

    A cross join will produce the Cartesian product between two tables. It is typically used when you want to create a combination using every row from two tables. For example, if you want to find every combination of colors between various pieces of clothing, you would use a cross join to retrieve every combination.

    A natural join will produce a table that joins columns with the same names and types. Any shared column between tables will be considered for a join. This is different from the commonly-used inner join where joins are explicitly done on specified columns. What’s important to note is that if a natural join does not find any matching columns, it will essentially produce the same result as a cross join.

    What are UNION, MINUS, and INTERSECT commands?
    These three commands are known as set operations. 

    UNION will produce a table that has the data contained in two tables.
    MINUS will produce a table that has data from the first table minus that of the second table.
    INTERSECT will produce a table that has shared data between the two tables.

      If we imagine a Venn diagram with two intersecting circles and three distinct parts, UNION represents all three parts, MINUS represents the left part, and INTERSECT represents the middle part.

      Write a SQL query to get the third-highest salary of an employee from employee_table.
      SELECT salary FROM employee_table ORDER BY salary DESC LIMIT 1 OFFSET 2;

      SELECT salary specifies that we only want to return the salary and not to include other columns.
      ORDER BY salary DESC returns all of the results ordered from the greatest to least salaries.
      LIMIT 1 specifies that we only want to retrieve a single row. Since the results are already ordered by the ORDER BY filter, this will return the greatest salary.
      OFFSET 2 specifies that we want to skip the first 2 rows.

      What is the need for group functions in SQL?
      Group functions are one of the key ways to perform data analysis with SQL. 
      Group functions help aggregate a set of rows into one group of data represented by the rows. These functions are often used to analyze tables to better make sense of the data they represent.

      For example, let’s say that we have a users table that has a row named country that specifies the country the user is from. By using GROUP BY, we can determine a count of the number of users we have for each country in our table.

      Why are SQL functions used?
      SQL functions provide ways to perform calculations on the database. These can include aggregations that are often used for analytics. An example of an aggregation function would be the AVG function that returns the average prices paid for a purchase:
      SELECT AVG(price) AS average_price FROM purchases;

      What is the difference between HAVING clause and a WHERE clause?
      WHERE is used to filter rows before they are possibly grouped. HAVING is used to filter out rows after they have been grouped. HAVING functions very similar to WHERE but is used after some form of aggregation. WHERE clauses should be preferred when possible as our query will be faster if we filter out data pre-aggregation than post, but some filters can only be done post-aggregation and so require the use of a WHERE clause.

      Example: Given a table of orders, return the customer id and how many orders they’ve made among customers who have made at least 10 purchases. The only way to know how many orders a customer made is to first count (aggregate) all of their orders before filtering. We could write this query as
      SELECT customer_id FROM (SELECT COUNT(*) AS count, customer_id from orders GROUP BY 2) WHERE COUNT >= 10;
      or we can simplify it with a HAVING clause:
      SELECT COUNT(*) AS count, customer_id from orders GROUP BY 2 HAVING count >= 10;

      How can you fetch alternate records from a table?
      There are several ways to retrieve alternating records. 

      It’s common for tables to have auto-incrementing primary keys. We can use the modulus operator % to retrieve alternating rows. This works because a number divided by 2 will always return either a 0 or 1.

      SELECT * FROM <table_name> WHERE <table_id> % 2 = 0;
      SELECT * FROM <table_name> WHERE <table_id> % 2 = 1;

      Name the operator which is used in the query for pattern matching.
      LIKE is used for string matching. Different forms of LIKE operations can be available in different SQL database implementations. For example, LIKE can often be used to perform case-insensitive pattern matching.

      If we have a table named products with a column named name, we can search across every row for products with names that contain the word toy:
      SELECT * FROM products WHERE name LIKE '%toy%;

      Pattern matching is often used to search for specific text in a column across many rows. 

      When would it be more appropriate to use a Materialized View over a View?
      Both Materialized Views and Views make it easier to query data by transforming some table data into its own structure. Materialized Views persist data whereas Views compute its data each time it is queried.

      This means that Materialized Views have better read performance because the data is persisted. When performance is sufficient, it’s sometimes recommended to go with a View to reduce the data that would be stored with a Materialized View.

      List some advantages and disadvantages of a stored procedure.
      Stored procedures are useful for SQL queries that are used repeatedly. In practice, we can construct a stored procedure instead of keeping a list of repeatedly-used SQL queries. This reduces room for error and standardizes a set of operations for reproducibility.

      However, stored procedures are often very use case-specific and not portable. A stored procedure will not work in another database table with similar structure and will need to be recreated to make it work.

      As they are custom, they are difficult to test and integrate with other tools. Developers often use tools that sit on top of databases to make application development easier — stored procedures are known to have incompatibilities with some of these tools.

      What are the differences between OLTP (ex. MySQL) and OLAP (ex. data warehouse) databases?
      This is another one of those SQL developer interview questions designed to help you gauge whether a candidate understands the different SQL databases that may exist and how they differ from one another. Different use cases often dictate the type of SQL database to use. 

      OLTP databases are designed for fast queries with strong data integrity. They are typically optimized for handling day-to-day business operations that involve real-time reads and writes.

      OLAP databases are optimized for offline analytics and typically involve complex aggregations. Compared to OLTP databases, they often have lower volumes of queries, contain more historical data, and involve significantly fewer edits.

      If we illustrate this with a web application, an OLTP database probably handles the metadata for the web application. User activity and data is stored and retrieved for the OLTP database. OLAP databases will probably store the same type of data but be leveraged to understand key metrics such as user retention and behavior.