For non-coding SQL interviews usually happen in the first one or two rounds of technical phone screens.
Some of the topics are more on the data engineering side; if you are interviewing for a data scientist role inside a data engineering or software engineering organization, you might need to know all of them.
If you are interviewing in a product analytics team, you are not required to know all of them.
Acknowledgment: more technical details can be found on https://www.postgresql.org/docs/13/
Interested in video lectures? Check out our SQL Master course, which covers most of those topics in this article.
1. What is a Primary Key?
The PRIMARY KEY uniquely identifies each row in a table. It could be a single column or a combination of columns.
2. What is a foreign key?
A foreign key is a column or a group of columns in a table that reference the primary key of another table.
3. What is an OUTER JOIN? What are the different types of OUTER JOIN?
The SQL OUTER JOIN combines records (rows) from two or more tables based on a related column.
LEFT JOIN (most commonly used) kept all the records on the left side table, RIGHT JOIN, and FULL OUTER JOIN.
4. What is the difference between JOIN and UNION?
An inner join is the connection of two "tables" to create rows consisting of columns from both tables.
A union is the concatenation of two queries with the same columns (with duplicates removed by default.
UNION is a row-wise operation: stacking things on top of each other, and the other is more of a column-wise process: stacking things horizontally. (edited)
5. What is the difference between UNION and UNION ALL?
UNION removes duplicated, and UNION ALL does not.
6. What is the difference between HAVING and WHERE?
HAVING adds a condition on GROUP statements;
WHERE cannot be used on GROUP statements.
7. What is a Subquery?
A subquery is a query within another query, also known as a nested query or inner query. It is used to restrict or enhance the data to be queried by the main question, thus limiting or improving the output of the main query respectively.
8. What is a SELF JOIN?
A SELF JOIN is a case of regular join where a table is joined to itself based on some relation between its column(s). Self-join uses the INNER JOIN or LEFT JOIN clause, and a table alias is used to assign different names to the table within the query.
8. What are the different types of indexes, and when to use which?
A database index is a data structure that provides a quick lookup of data in a column or columns of a table. It enhances the speed of operations accessing data from a database table at the cost of additional writes and memory to maintain the index data structure.
9. What is CTE (common table expression)?
WITH provides a way to write auxiliary statements for use in a larger query. These statements, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query. It's a syntax sugar that makes it much easier to break down a very long query, making your SQL statements much easier to understand, especially during an interview.
10. What is Normalization?
Normalization represents the way of organizing structured data in the database efficiently. It includes the creation of tables, establishing relationships between them, and defining rules for those relationships. Inconsistency and redundancy can be kept in check based on these rules, hence, adding flexibility to the database.
11. What is Denormalization?
Denormalization is the inverse process of normalization, where the normalized schema is converted into a schema with redundant information. The performance is improved by using redundancy and keeping the redundant data consistent. The reason for performing denormalization is the overheads produced in the query processor by an over-normalized structure.