10th Sep 2021 9 minutes read

Frequently Asked Questions About SQL

Ten SQL questions that you should be able to answer if you’re applying for an SQL job.

SQL is a vast topic and a source of endless questions from its users. However, some questions are asked more frequently – usually about concepts that most SQL users will need to know. It’s not surprising that interviewers tend to ask these same questions to SQL job candidates.

Either way, knowing these questions is a first step to learning SQL. The second step is, of course, knowing the answers!

You can use this guide in your learning or in preparation for a job interview. If you’re preparing for an SQL job interview, you might be interested in knowing what these interviews usually cover.

Let’s see what these frequently-asked SQL questions are!

10 SQL Questions and Answers You Should Know

1. What Are SQL’s Data Types?

When you work in SQL, you’ll always be working with data. That’s why you have to know what data types SQL offers and which ones are most commonly used.

To answer the question: Data types are attributes specifying the kind of data a certain value should hold.

Some of the most common data types are integers, strings, characters, and date/time types. Every data type has its own purpose, and having the details on as many data types as possible allows you to choose the most suitable ones when you’re creating a database.

Knowing how various data types behave is important when you’re performing certain actions and analyses in SQL. Otherwise, you may not get the desired result.

While you don’t need to know everything about every data type, a decent overview always comes in handy. Here’s a great overview of all SQL’s data types and specifications, along with examples of how a certain data type will show data.

Databases differ regarding what data types they support and their data type names, so you should always check the documentation of the database you’re using. However, the most-used SQL data types are usually the same in most popular databases. The overview mentioned above goes into these differences.

2. Explain the Different Types of Database Relationships.

Relationships show how one table can be connected with another table in a relational database, thus enabling us to get the data from both tables.

The relationship types are:

A one-to-one relationship means one record from the first table relates only to one record in the second table, and vice versa.

A one-to-many relationship means one record from the first table is related to one or more records in the second table.

A many-to-many relationship means there are one or multiple records from the first table corresponding to one or multiple records from the second table.

3. What Is the Difference Between DELETE and TRUNCATE?

The DELETE statement is a Data Manipulation Language (DML) command that removes one or multiple rows from the table, doing it row by row and based on the (optional) criteria stated in the WHERE clause.

On the other hand, TRUNCATE is a Data Definition Language (DDL) command which removes all rows from the table while keeping the table structure. It’s faster than the DELETE statement. The time difference between DELETE and TRUNCATE is not that important when you’re deleting smaller tables; you won’t notice the difference. But when you’re deleting a vast amount of data, the difference could be significant.

While TRUNCATE is faster, you can’t roll back its changes in some databases, which you can do with DELETE. Also, using TRUNCATE locks the table you’re deleting from; no other user can access the table. This is important to know when you choose between DELETE and TRUNCATE. The syntax, differences, and similarities of these two commands are nicely outlined in this article. More detailed information is available in this comparison of DELETE, TRUNCATE, and the DROP TABLE statement.

By answering this question, you’re unlocking a much wider area that also gets frequently asked about—namely, SQL’s sub-languages and the main keywords used in every language. Guru99 has done a really good overview of SQL sub-languages and keywords. Learn that, and you’ll be able to answer all those questions about deleting, inserting, and updating data in your databases.

4. What Is a Constraint? What Are Some Commonly Used Constraints?

A constraint is a column- or table-level rule limiting the type of data allowed. The most commonly used constraints in SQL are:

NOT NULL – Doesn’t allow NULL values in the column.
UNIQUE – Doesn’t allow duplicate values; all the values in the column are different.
PRIMARY KEY – Uniquely identifies each row in a table; column values must be UNIQUE and NOT NULL.
FOREIGN KEY – Allows the linking of two tables by referencing the primary key of one table in another table.
CHECK – Checks if the values in a column satisfy a condition you specify (e.g. an age value that’s over 21).
DEFAULT – If no value is specified, the default value will be added to new records.
CREATE INDEX – Creates an index to facilitate faster data retrieval.

If you want to learn more, read this article that uses examples to explain what SQL constraints are.

5. What Is a JOIN? Explain the Join Types.

Some of SQL’s most frequently asked questions are about JOINs. This is something you can’t avoid using or being asked about, so you’d better be as comfortable with this topic as possible.

A JOIN is an SQL clause used to combine data from two or more tables. The four main JOIN types used in SQL are:

JOIN (also called INNER JOIN).
LEFT JOIN (also called LEFT OUTER JOIN).
RIGHT JOIN (also called RIGHT OUTER JOIN).
FULL JOIN (also called FULL OUTER JOIN).

An INNER JOIN will return only the matching data from both tables. The non-matching data won’t be shown at all.

The LEFT JOIN returns all the data from the left table and only the corresponding data from the right table. For any unmatched rows from the left table, NULLs will be shown for the right table's columns.

Exactly the opposite will happen with the RIGHT JOIN. You’ll get all the data from the right table and only matched data from the left one.

A FULL JOIN combines the LEFT JOIN and RIGHT JOIN to get all the rows from one table and all the rows from the other.

There is also a CROSS JOIN (or a Cartesian join), which results in a Cartesian product: it returns every possible combination of rows from all the tables joined.

Also, you should be aware of the self-join. A self-join is any join type (JOIN, LEFT JOIN, RIGHT JOIN, or FULL JOIN) that joins a table to itself.

This illustrated guide will help you understand what every JOIN does.

6. What Is a WHERE Clause? How Is It Different from the HAVING Clause?

WHERE is an SQL clause used to filter data in SELECT, INSERT, UPDATE, or DELETE statements.

The HAVING clause is also used to filter data. But the main difference between them is that the WHERE clause is used before the GROUP BY clause, while HAVING is used after the GROUP BY clause. That's why when you want to filter the rows using aggregate functions, you need to use HAVING – you need the rows to be grouped to use the aggregate functions.

There are some other differences between WHERE and HAVING, but the differences mentioned above are the ones usually referred to in the interview questions. Of course, you can use WHERE and HAVING in the same query, too.

7. Describe the GROUP BY Clause.

If you’re discussing the WHERE and HAVING clauses, GROUP BY is sure to follow.

What is the GROUP BY clause? It’s an SQL clause used in the SELECT statement to divide rows into groups based on common value(s) in the specified column(s). Here are some practical examples of using GROUP BY.

A related question has to do with the order of the clauses in the SELECT statement. The correct order is as follows:

SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY

Having (no pun intended) this order in mind, you can correctly filter the data you need in your result and order it in your desired way. You should practice the SQL Order of Operations using these examples so you really get this in mind.

One additional thing to know about GROUP BY is that it has extensions: ROLLUP, CUBE, and GROUPING SETS. The ROLLUP extension lets you show the subtotals together with the total sum, like in Excel pivot tables. CUBE does the same, but it allows getting subtotals for all combinations of the columns specified in the GROUP BY. Finally, GROUPING SETS basically allows you to group data like you have several GROUP BY clauses in one statement.

8. What Are Aggregate Functions?

In SQL, aggregate functions perform a calculation on a dataset and return a single value. Common SQL aggregate functions are:

COUNT() – Counts the number of records within a group.
SUM() – Sums the values within a group.
AVG() – Returns an average value within a group.
MIN() – Finds the minimum value in a group.
MAX() – Finds the maximum value in a group.

It’s also important to note that aggregate functions (except for COUNT(*)) ignore NULL values. However, when all the values (or all the values in the group) are NULL, then the aggregate function will return NULL.

In this article, you’ll find a good overview of aggregate functions with examples.

10. Explain Subqueries and Their Different Types.

Subqueries are queries nested into a main, larger query. In SQL, the subquery types are:

Single-row subquery – Returns one or zero rows as a result.
Multiple-row subquery – Returns one or more rows as a result.
Multiple-column subquery – Returns one or more columns as a result.
Correlated subquery – References one or more columns in the main query.
Nested subquery – A subquery within another subquery.

One of the important things about subqueries is the SQL clauses you can use them in:

HAVING
FROM
WHERE

You can also subqueries in the following statements:

SELECT
UPDATE
INSERT
DELETE

Usually, you’ll find subqueries in the WHERE clause of a SELECT statement.

That’s not everything you need to know about SQL subqueries. I’d recommend this article on subqueries for further reading. There are more details about when and where you can use subqueries, plus an explanation of the syntax and general guidelines. There’s also a graphical presentation of how subqueries work.

Do You Have More SQL Questions?

Ten questions are not all the questions you can ask or be asked about SQL. However, these ten questions cover the main SQL concepts. Your knowledge about these concepts will certainly be tested in an SQL interview.

Most of the above questions are covered in the SQL Basics course. In it, you’ll find more detailed explanations and a platform to practice what you learn. If you want to read more SQL questions and answers, check out our article on the top 15 SQL interview questions in 2021.

If you want to make sure you have well-rounded SQL knowledge – including advanced and less-frequent SQL topics – then the SQL from A to Z course is for you.

Tags: