Back to articles list May 13, 2020 - 12 minutes read Advanced SQL Interview Questions (and Answers) Tihomir Babic Tihomir is a financial and data analyst turned database designer from Zagreb, Croatia. He has extensive experience in the financial services industry, which helps him combine his finance background with his interest in data. When not tinkering with data, he likes to play the guitar in a band and pretend to be a musician. He also regularly writes columns for the Croatian LGBT news site CroL.hr and hopes to soon write his first novel. Tags: sql learn sql jobs and career 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? first_namelast_namecars_sold ClarabelleHanmer77 ChristianoOverstall51 WilhelmKopec38 RubieDing72 ArtemusWoolward100 EmileeNanetti84 RainaBedinn57 GlendonKnowlys116 CarlottaDytham106 JolyTschierasche114 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! rank_salesfirst_namelast_namecars_sold 1GlendonKnowlys116 2JolyTschierasche114 3CarlottaDytham106 4ArtemusWoolward100 5EmileeNanetti84 6ClarabelleHanmer77 7RubieDing72 8RainaBedinn57 9ChristianoOverstall51 10WilhelmKopec38 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: first_namelast_namecars_sold ClarabelleHanmer72 ChristianoOverstall84 WilhelmKopec38 RubieDing72 ArtemusWoolward100 EmileeNanetti84 RainaBedinn72 GlendonKnowlys116 CarlottaDytham106 JolyTschierasche114 If you run the code from the previous example, RANK() will get you this result: rank_salesfirst_namelast_namecars_sold 1GlendonKnowlys116 2JolyTschierasche114 3CarlottaDytham106 4ArtemusWoolward100 5EmileeNanetti84 5ChristianoOverstall84 7ClarabelleHanmer72 7RubieDing72 7RainaBedinn72 10WilhelmKopec38 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: rank_salesdense_rank_salesfirst_namelast_namecars_sold 11GlendonKnowlys116 22JolyTschierasche114 33CarlottaDytham106 44ArtemusWoolward100 55EmileeNanetti84 55ChristianoOverstall84 76ClarabelleHanmer72 76RubieDing72 76RainaBedinn72 107WilhelmKopec38 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: idfirst_namelast_name 1KristenYukhnev 2AngelicaHulson 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: idfirst_namelast_name 1KristenYukhnev 2AngelicaHulson 3RosaliaO'Towey 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: productmanufacturer X278 Hammer 2018Sledge Hammer M+S Tyres Z348 2020Goodtyre Paint red pearly 9R458PT12 2019PaintItBlack 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? productmanufactureryear_produced X278 Hammer 2018Sledge Hammer2018 M+S Tyres Z348 2020Goodtyre2020 Paint red pearly 9R458PT12 2019PaintItBlack2019 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. first_namelast_namesalary MikeHammer2780 JohnJohnson1600 KateWilliams3000 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! first_namelast_name MikeHammer JohnJohnson KateWilliams 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 course that you can use while you’re preparing for the interview. Feel free to comment in the comments section! Share your thoughts and interview experiences! Tags: sql learn sql jobs and career You may also like Advanced SQL Interview Questions (and Answers) Here are ten common questions you might hear during an advanced SQL interview. We also have the answers for you. Read more What SQL Practice Do You Need to Prepare for a Technical Job Interview? Need some SQL practice before a technical job interview? Here are six ways to prepare for various types of database and SQL jobs! Read more Types of Database Jobs: Choose One of Them and Start Being Awesome Do you want to work with databases but don't know what the options are? Check out the types of database jobs that use SQL. Read more Top 10 Websites That Will Help You Find the Perfect SQL Job Want to get an SQL job but don't know where to look? Here are the top 10 websites that will help you find the perfect fit. Read more Common Entry Level SQL Developer Interview Questions Learn about the eight interview questions usually asked for an entry-level SQL position and how to answer them well to make a good impression Read more SQL Interview: Tips From Recruiter and Sample Questions with Solutions Break up with boredom and take your career in different direction! Read proven tips on what's important to pass database-related interview process. Read more How to Become a Database Analyst See who a database analyst is, how to become one, and where to start from. Find out how much analysts earn and discover most commonly asked interview Q's. Read more Most Popular SQL Interview Questions for Business Analysts Finance, Banking – professions related to those industries more often require basic knowledge of SQL. Get to know the interview questions for a BA job. Read more The 5 Highest Paying Jobs That Use SQL Every year, many people look and aim for new & better jobs. SQL-related jobs are some of the most promising. Discover the top 5 highest SQL job salaries! Read more Complete SQL Practice for Interviews Go through this article if you’re looking for SQL practice for interviews—those related to data science and other professions! Read more A Day in the Life of a SQL Developer Describing a “typical” day for a SQL developer is not easy. What is a SQL developer? And what does a SQL developer do? Read more Finding the Perfect SQL Job How can you put skills to use in the job market? How much money can you make? What types of jobs are available? Find out with us! Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.