SQL Interview Questions: Common Mistakes and How to Avoid Them
Table of Contents
1. General tips
2. Common Mistakes
3. Personal questions
4. Technical questions
5. Summary
6. Conclusion
Discover the most likely SQL interview questions and common interview mistakes to avoid. This guide covers essential tips and strategies to help you succeed in your SQL interviews and secure your next tech job.
General tips
- Be authentic. While there is no need to be “modest”, an authentic person gives of an impression of someone who’s confident enough not to mask their personality.
- Be straightforward and concise. Don’t use vocabulary you are not used to. Try to say as much in the fewest sentences possible. Remember, your goal is to engage the interviewer.
- Learn about the company and their products. Use the products, if possible.
- Be prepared. Have your references, resume and work examples ready.
- Wear a professional outfit, but remember that the fit and finish of the outfit is more important than the nature of the outfit. A good fitting, simple outfit of a buttoned shirt and pants is better than an ill-fitted suit. Remember to also coordinate colours appropriately.
- Be kind and respectful to everyone. While this is a good practice to follow in life in general, remember that it is possible that interviewers ask for feedback from other staff, such as front desk staff and security.
- Body language – Be confident, and show it. Sit or stand upright. Build self-confidence and release anxiety and stress by taking deep, slow breaths. When the interviewer offers a handshake, stand upright, look at the person confidently and smile. Be firm in your handshake.
- Know your strengths and focus on them. Emphasizing your strengths gives your interviewer a better understanding of how you could contribute to the organization.
- Remember that at a human level, you are no lesser than your interviewer.
- Smiling – A warm and genuine smile makes a big impression, particularly when entering and leaving the interview.
Common mistakes
- Speaking negatively about your former employers
- Trying to use a level of vocabulary above what you’re used to.
- Arriving late to the interview. Remember to aim to arrive at least 10-15 minutes early.
- Waffling – Take a few seconds to think about the question before responding. Avoid going on tangents, or ending your answer abruptly without a clear, rounded conclusion.
- Displaying too little – or too much energy. A lack of enthusiasm shows a lack of interest. Showing too much excitement can also convey a lack of composure and unsettle the interviewer.
- Body language –
Crossing your arms and legs makes you look unapproachable and defensive
Fidgeting – Avoid excessive, unnecessary movement. It can convey anxiety to leave or disinterest.
Scanning the room – Fidgeting is not just limited to your body movements. If your eyes are scanning the room, it shows disinterest.
- Distractions – Remember to keep your mobile phone on silent, and in your pocket. Simple gestures like touching your fingertips together, moving your fingers while speaking, or clasping your palms can signal openness and honesty. Hand gestures often happen subconsciously, and trying to control them can be stressful without practice. If you're unsure, keep your hands gently clasped in your lap. This not only prevents fidgeting but also conveys a sense of calmness and ease in the situation.
Personal questions
These questions are questions only you can answer, but be ready for them, even if they are already mentioned in your resume.
- What versions of SQL are you familiar with?
- What is your level of proficiency in SQL?
- How long have you been working with SQL?
- What are your strengths and weaknesses?
Technical questions
What are the types of commands/queries in SQL?
The types of SQL commands are:
DDL – Data Definition Language commands – These commands are used to change the structure of database objects. E.g. Create Table, Alter Table, Drop Table
DML – Data Manipulation Language commands – These commands are used to enter, modify or delete the data stored in tables. E.g. Insert, Update, Delete
DCL – Data Control Language commands – These commands are used by database administrators to control the level of access of different users to the database. E.g. Grant, revoke
TCL – Transaction Control Language commands - In a SQL Server, commands can be set to auto-commit. This means that after each operation or transaction—like inserting, updating, or deleting data—the changes are immediately saved and cannot be undone. When auto-commit mode is not enabled, TCL commands are used to manually commit transactions.
What is normalization?
Normalization is the process of organizing data in a database with the goals of
- Eliminate redundancy
- Enhance data integrity
The steps in normalization are:
- Ensure that no single cell has multiple values in a table, splitting the records into 2 or creating another table if necessary.
- Ensure that every foreign key in the table is dependent on a primary key. Move columns that are only partially dependent on the primary key to a new table.
- Ensure that non-primary key columns are not dependent on other non-primary key columns. Look for columns that depend on another column that isn't a primary key and store this data in a new table.
What database system does SQL use?
SQL uses a relational database system (RDBMS)
What are the components of a database?
The components of a database are:
- Schema – the blueprints of various data structures in the database
- Tables – a framework to store data, arranged in rows and columns
- Views – a virtual table that does not store data itself. Instead, it provides a way to present data from one or more tables in a specific format or structure that is based on the result of a ‘SELECT’ query.
- Indexes - Indexes are special lookup tables that the database search engine uses to speed up data retrieval.
- Stored Procedures – A set of queries that can be executed as a single command
- Triggers – SQL code that automatically runs in response to certain events
- Constraints – rules/restrictions that apply to columns in a database to enforce data integrity, accuracy and reliability
What is a function?
Functions are blocks of code that are designed to perform a calculation on an input value, known as a parameter, to return an output value.
What are user-defined functions?
User-defined functions are functions designed to provide functionality that built-in SQL functions cannot.
What is pattern matching in SQL?
Pattern matching is a technique in SQL that allows you to find a certain pattern using the LIKE operator and the WHERE clause
What is a join? What are the types of joins?
A join is a clause used to combine and retrieve records from two or more tables.
The types of joins are:
- INNER JOIN – returns only records that satisfy a defined join condition on BOTH or all tables. It is the default SQL join.
- LEFT JOIN – returns all tables from the left table and returns only records that match the join conditions in the right table
- RIGHT JOIN – returns all tables from the right table and returns only records that match the join conditions in the left table.
- FULL OUTER JOIN – Returns all values from all tables
- SELF JOIN – joins a table with itself
- CROSS JOIN – joins each row of the first table with each row of the second table. It’s considered a Cartesian product of 2 tables and is also known as a Cartesian join
What is an Index?
An index can be described as a list of values, similar to the index in a book which allows faster data retrieval. Indexes are used when the data set is large, or when the columns are frequently searched against.
What are UNION, MINUS and INTERSECT commands?
These commands, called Set Operations are used to combined two or more select queries into a single result. These are similar to mathematical set operators.
UNION
Combines the result of two SELECT queries and removes any duplicate rows (unless UNION ALL is used)
MINUS or EXCEPT
Returns the rows in the first SELECT statement that are not in the second SELECT statement. Removes duplicates (unless MINUS ALL is used)
INTERSECT
Only returns rows that are present in both SELECT statements, without duplicates (unless INTERSECT ALL is used).
What is ETL?
It's a process used in data integration to move data from various sources into a destination system, typically a data warehouse. The steps are:
- Extract – Get data from various sources
- Transform – Convert the data or transform it into a format that is suitable for analysis or storage
- Load – Loading the transformed data into the target database or data warehouse.
What are ACID properties?
ACID properties are a set of four key principles that ensure reliable and consistent transactions in a database. These principles are:
- Atomicity - This means that a transaction (a group of operations) is all or nothing. If any part of the transaction fails, the entire transaction fails, and the database is left unchanged.
- Consistency - Consistency ensures that a transaction brings the database from one valid state to another. The rules of the database (like data types, constraints, etc.) must always be followed, so the database never ends up in an invalid state.
- Isolation - Isolation ensures that the operations within a transaction are invisible to other transactions until the transaction is complete.
- Durability - Durability means that once a transaction is completed, its effects are permanent, even if the system crashes afterward.
What is the difference between CHAR and VARCHAR data types?
CHAR and VARCHAR are both data types that are used to store strings, however the way they do this is different.
CHAR data types are used to store fixed length strings. This means that if you define a CHAR column with a length of 10, it will always take up 10 bytes of storage, regardless of the actual length of the string stored. CHAR has slightly better performance than VARCHAR and is used when the data is always the same size.
VARCHAR data type is used to store variable-length strings. Unlike CHAR, it only uses as much storage as needed for the string, plus a small amount of extra space to store the string's length. It is considered more space-efficient than CHAR.
Summary
Here is a list of SQL topics that will give you a birds-eye view of what to prepare on:
- What is SQL?
- What is a DBMS?
- What is a Schema?
- What are the types of SQL commands?
- Functions in SQL – aggregate, scalar, built-in and user-defined functions
- Database design – Normalization, denormalization
- Advanced queries
- Constraints – what they are, why they exist, and the function of each constraint
- Joins – what they are, their function and the types of joins
- Indexes – definition and types of indexes
- Set Operators
- GROUP BY and ORDER BY
- WHERE and HAVING
- Null functions
- What is a View and why is it used?
- Stored Procedures
- Triggers – the types of triggers and their use
Conclusion
This guide is certainly not a detailed guide to learn SQL. For that you can check out our <SQL course>. Rather, this guide assumes that you’re familiar with SQL at least on a foundational level and is designed to help you with interviews in the field. A SQL certification goes a long way in convincing your potential employers about your ability.