Advanced SQL Interview Questions (and Answers)

Do you want to move to a job that uses SQL? Are you unsure about what to expect during the job interview or how to benchmark your knowledge? This article will give you a good idea of where you stand.

If you feel stuck in your job, you’re not the only one. The modern division of work pushes people to perform very specific tasks, expecting them not to look at what's on the other side. Workers only need to be focused on their (often repetitive and boring) responsibilities and should not question anything beyond that. It can often feel like you’re working on an assembly line. Yes, an assembly line – even in a nice office with huge windows and free coffee and fresh fruit. But still, an assembly line. Or maybe you just want to do your job faster, not losing time by inefficiently handling data.

Changing your job or improving your performance can significantly increase the feeling of doing something useful. This article will list some advanced SQL questions that you might get in an interview. And we’ll also tell you the answers! That way, you can benchmark your current knowledge and maybe learn something new.

Whether you apply this to your new or current job, it’s up to you! Let’s start our ‘job interview’!

(Are you looking for entry-level SQL questions? This article is the one you need.)

10 Advanced SQL Job Interview Questions and Answers

Question 1: What is an index? What are the two main index types?

An index is a structure in a database that can help retrieve data faster. When you search table data with an SQL query, it will search the whole table and return the result. An unindexed table is called a heap. The data stored in such tables are usually not arranged in any particular way. It is stored in the order it was entered. Therefore, searching for data can be very slow and frustrating.

When you query an indexed table, the database will go to the index first and retrieve the corresponding records directly. The two main index types are:

• Clustered
• Non-clustered

A clustered index defines the exact order of the data stored in the table. There can be only one clustered index per table, since the table can only be ordered in one way.

A non-clustered index simply points to the data, with the order of the data in the index not being the same as the physical order of the actual data. The data is stored in one location, while the index is stored in another location.

Question 2: What’s the code for creating an index?

Now we get to a practical example! Suppose we have the table employee, which has the following columns:

• name – The employee’s first name.
• surname – The employee’s last name.
• nin – The employee’s national identification number (e.g. social security number).

Your task is to create a clustered index on the nin column and a non-clustered index on the surname column. How do you do that?

To create the clustered index, the code will be:

CREATE CLUSTERED INDEX CL_nin
ON employee(nin);

This will create the clustered index with the name CL_nin, on the table employee and the column nin.

To create the non-clustered index, the code will be:

CREATE NONCLUSTERED INDEX NCL_surname
ON employee(surname);

Run this code and you will create a non-clustered index named NCL_surname on the column surname in the table employee.

Important: If you don’t specify a type (clustered or non-clustered), you’ll create a non-clustered index by default.

Question 3: How do you add ranking to rows using RANK()?

Monitoring sales numbers is essential for every company. Take the example of a car dealer. There can be many salespeople selling cars every day. Imagine that the dealership management is in terested in comparing their salespeople. They want to know who is performing well and who just hangs around. You have the table salespeople with the following information:

• first_name - The employee’s first name.
• last_name - The employee’s last name.
• cars_sold - The number of cars sold by this employee.

How do you put a rank on this table, shown below?

The code for this problem is:

SELECT	RANK() OVER(ORDER BY cars_sold DESC) AS rank_sales,
		first_name,
		last_name,
		cars_sold
FROM salespeople;

The code uses the RANK() window function. Since no window is defined, the function will use the whole table. It will rank the data according to the column cars_sold and the rank will be added to the new column rank_sales. Run the code and you will get a nice table – which will get you a point on your interview!

I’ve written a more detailed article about window functions and their use (with examples) if you want to dig deeper. There’s also the handy LearnSQL.com cookbook from which I stole this code. You can do that too! The cookbook section is basically a list of common problems solved by using SQL. You can find a solution very quickly, read the explanation, and get a code snippet that you can use to solve your tasks.

Question 4: What’s the difference between RANK() and DENSE_RANK()?

The main difference is that RANK() will give all rows with the same values (in the ranking criteria) the same rank. It will also skip ranks if more than one row has the same rank; the number of ranks skipped will depend on how many rows share the same value. This creates non-consecutive ranks.

With DENSE_RANK(), rows with the same values will also be ranked the same. However, this function will not skip any ranks, so it will lead to consecutive ranks.

Here’s an example to help clarify the difference. Let’s modify the table from the previous example. The table looks like this:

If you run the code from the previous example, RANK() will get you this result:

You’ll notice that rank 5 is allocated twice, then the ranking skips 6 and goes directly to 7. Rank 7 is allocated three times, after which the ranking goes directly to 10.

If you want to see how DENSE_RANK() ranks the rows, run the following code:

SELECT	RANK() OVER(ORDER BY cars_sold DESC) AS rank_sales,
		DENSE_RANK () OVER (ORDER BY cars_sold DESC) AS dense_rank_sales,
		first_name,
		last_name,
		cars_sold
FROM salespeople;

It will give you a result that looks like this:

Knowing window functions is essential for any job that requires advanced SQL knowledge. To practice more SQL and see how window functions can help you, feel free to try LearnSQL.com’s Window Functions course.

Question 5: What is an auto-increment?

Any type of database job will require this knowledge. Auto-increment is a SQL function that automatically and sequentially creates a unique number whenever a new record is added to the table.

The keyword that’ll give you this function is AUTO_INCREMENT.

Here’s the example. The code below will create the table names with the values defined by INSERT INTO:

create table names (
	id INT NOT NULL AUTO_INCREMENT,
	first_name VARCHAR(50),
	last_name VARCHAR(50)
);
INSERT INTO names(first_name, last_name) VALUES ('Kristen', 'Yukhnev');
INSERT INTO names(first_name, last_name) VALUES ('Angelica', 'Hulson');

The table will look like this:

To see how the auto-increment function works, add a new record to the table above:

INSERT INTO names (first_name, last_name)
VALUES ('Rosalia', 'O''Towey');

This will add a new record to a table. Select all the data to see how the table has changed:

SELECT *
FROM names;

Now the table looks like this:

When we added a new record to the table, AUTO_INCREMENT automatically added a new sequential number. As 1 and 2 previously existed in the table, with AUTO_INCREMENT the database knows that the next value will be 3.

Question 6: What is a subquery?

A subquery (also called an inner query or nested query) is a query placed within a query. It returns data that will be used by the main query. You’ll usually find it in the WHERE clause.

Question 7: What will the following code return?

The code below is an example of a subquery:

SELECT	first_name,
		last_name,
		cars_sold
FROM cars
WHERE cars_sold >	(SELECT AVG (cars_sold)
					FROM cars);

Running the code will return the columns first_name, last_name, and cars_sold from the table cars, but only where cars_sold is greater than the average number of cars sold.

Question 8: Is there a difference between a NULL value and zero?

Yes! A NULL value is the absence of the data/information. It has a quantitative character in that it represents the absence of the quantity. Simply put, NULL in SQL means the value is unknown or missing; we don't know what the value is. On the other hand, zero means there is a value which equals, well, zero. Therefore, zero has a qualitative character.

Question 8: How would you filter data using JOIN?

The basic meaning behind JOIN is that it will return data from one table when that data is equal to the data in a second table. If you combine it with the WHERE clause, JOIN can be used for filtering data.

Here is an example of another car dealership. The first table is named cars, and it consists of the following data:

• model_id - The car model’s ID number.
• model_name - The name of that model.
• brand_id - The ID of the car brand.
• brand_name - The name of the car brand.
• year_id - The year when the model was produced. (Uses data from the table production_year.)

The second table is production_year, which contains the following columns:

• year_id - An ID number for each year.
• year - The actual year of the production.

What would you do if you are asked to find all the models that were produced before 2016? Here’s the code:

SELECT	model_name,
		brand_name
FROM	cars JOIN production_year ON cars.year_id = production_year.year_id
WHERE	year_id < 2016;

Let me explain the code. It selects the column model_name and brand_name from the table cars. This table is joined with the table production_year using the column year_id, which is a connection between the cars and the production_year tables. Because of the WHERE clause, this code returns only the cars that are produced before 2016, i.e. WHERE year_id < 2016.

If you’re interested in learning more about JOINs or if you want to practice what you’ve learned, the LearnSQL.com SQL Practice Track can help you with that.

Question 10: How would you extract the last four characters from a string?

To do that, you would need the RIGHT() function.

For example, there’s the products table, which consists of the following data:

• product - The name of the product.
• manufacturer - The company that makes the product.

The table looks like this:

You need to find the year when each product was produced. But whoever created the database did a lousy job. There’s no column with the production year! There’s no such data available – except as the last four characters of the product name. To extract the year from that field, here’s the code you’d use:

SELECT	product,
		manufacturer,
		RIGHT(product,4) AS year_produced
FROM products;

And here’s the result! Simple, right?

Let me explain what we just did. The code, of course, selects the columns product and manufacturer. Then, using the RIGHT() function, we’ve instructed the query to take the strings in the column product and return the last four characters starting from the right. We’ll put these results shown in the new column year_produced.

Question 11: What is a view? How do you create one?

A view is a virtual table or a stored SQL statement that uses data from one or more existing tables. The view is called a ‘virtual table’ because the data is used like a table, but it is retrieved whenever the view is run. (The result of a view is not stored as a table. )

Let’s say there’s a table called salary that contains the following columns:

• first_name - The employee’s first name.
• last_name - The employee’s last name.
• salary - The employee’s salary.

Employees’ salary information is not available to everybody in the company. However, at this company everybody needs to be able to access the list of employees. How would you allow them to do that and, at the same time, not break any confidentiality rules?

You would not allow them access to the whole table, but you could create a view for them. That way, they would always be able to access the latest data without seeing anything confidential. Here’s how to do it:

CREATE VIEW		employee_list AS
SELECT		first_name,
			last_name
FROM salary;

Running this code will create a view named employee_list, which will retrieve the first_name and last_name information from the table salary. It’s simple, isn’t it? You create it using the command CREATE VIEW, then you just write a regular query.

OK, but this just created a view. Creating it didn’t retrieve any data. So how do you run the view? You simply pretend it is a regular table. The code below will run the view:

SELECT *
FROM employee_list;

And the resulting table is here! Beautiful! No salaries are shown, so you didn’t screw anything up!

Do You Think You’re Ready for an Advanced SQL Job?

Article length doesn’t allow us to cover all the advanced SQL topics you might run into in a job interview. However, I’ve tried to give you a good overview of what you might expect when applying for a job that uses SQL. This should only be a place for you to start researching and see where your knowledge needs improvement. If you’re interested in learning more, there’s an interactive advanced SQL track that you can use while you’re preparing for the interview. And in this guide you can find out how to practice advanced SQL with our platform.