Common SQL Job Interview Questions

Common SQL Job Interview Questions

Congratulations! Your SQL skills were strong enough to get you that job interview! Now, if you only knew what SQL questions and practical exercises a recruiter might ask you to do…

In this article, we help you prepare for the SQL and database aspects of a job interview.

In a previous article, I explained how can you boost your career by learning SQL. It opens up opportunities in and out of IT, as this query language is used practically everywhere. Let’s say you finally made your decision to use your SQL skills in a new job. Before you can start, though, you need to get through the job interview.

Your interviewer may use several methods to gauge your SQL chops: sometimes you’ll be asked to describe the differences between two elements; sometimes you’ll have to tell them the result of a specific query; you may even have to write some simple code.

This sounds stressful, but don’t worry too much. In this article, I’ll help you prepare for it by going over some common questions and SQL exercises that recruiters use during the interview process.

Basic SQL Interview Questions

First, let’s focus on the more theoretical side of SQL. To assess your understanding here, the recruiter will likely ask you to verbally explain some simpler questions. These are usually mainly about the elementary parts of SQL. For example, you might be asked to answer some of the following:

  • Enumerate and explain all the basic elements of an SQL query.
  • What is the WHERE clause?
  • What do the LIKE and NOT LIKE operators do?
  • Explain the usage of AND, OR, and NOT clauses.
  • What is a NULL value?
  • What does a JOIN do? What are the different types of JOIN clauses?
  • Explain the GROUP BY clause.
  • What is the difference between the WHERE and HAVING clauses?
  • What does ORDER BY do?
  • What does UNION do? What is the difference between UNION and UNION ALL?
  • What do the INTERSECT and MINUS clauses do?
  • What is the role of the DISTINCT keyword?
  • Explain the use of aliases in queries.

Can you answer every question? Even basic questions need good preparation and a lot of practice. Nobody is perfect, and sometimes you need to refresh your knowledge. Vertabelo Academy’s online courses can help you brush up on any semi-forgotten skills.

Practical SQL Exercises

Technical job interviews often have a practical side. This is where you need to prove your SQL skills as you would use them in real life – by writing a SQL statement, checking a query for mistakes, or determining the result of a given query.

Below, I’ve copied ten SQL exercises of the types you might do on a technical job interview. They are sorted from easy to hard.

Exercise 1 – Write a Statement

Write an SQL statement that lists school names, student names, and their cities only if the school and the student are in the same city and the student is not from New York.

Table: school
school_id       school_name                    city  
-----------     ------------------       ----------  
1               Stanford                   Stanford    
2               University of Cali    San Francisco       
3               Harvard University         New York      
4               MIT                          Boston       
5               Yale                      New Haven        
Table: student
student_id   student_name  city           school_id
-----------  ------------  ----------   -----------
1001         Peter Brebec  New York              1
1002         John Goorgy   San Francisco         2
2003         Brad Smith    New York              3
1004         Fabian Johns  Boston                5
1005         Brad Cameron  Stanford              1
1006         Geoff Firby   Boston                5
1007         Johnny Blue   New Haven             2
1008         Johse Brook   Miami                 2

Exercise 2 – Write a Statement

Write an SQL statement that lists student names, subject names, subject lecturers, and the max amount of points for all subjects except Computer Science and any subjects with a max score between 100 and 200.

Table: subject
subject_id  subject_name          max_score    lecturer
----------  ----------            ----------   -----------  
 11         Math                  130          Christena Solem 
 12         Computer Science      150          Jaime Pille 
 13         Biology               300          Carrol Denmark 
 14         Geography             220          Yuette Galang 
 15         Physics               110          Colton Rather 
 16         Chemistry             400          Nan Mongeau 
Table: student
student_id   student_name    city           subject_id
-----------  ------------    ----------    -----------
2001         Thurman Thorn   New York               11
2002         Sharda Clemens  San Francisco          12
2003         Buck Elkins     New York               13
2004         Fabian Johns    Boston                 15
2005         Brad Cameron    Stanford               11
2006         Sofia Roles     Boston                 16
2007         Rory Pietila    New Haven              12

Exercise 3 – Find the Error

What is wrong with this SQL query?

SELECT Id, name, YEAR(BillingDate) AS Year 
FROM Records
WHERE Year >= 2010
SELECT id, name
FROM students
WHERE grades = 
             (SELECT MAX(grades)
              FROM students
              GROUP BY subject_id;);

Exercise 4 – Find the Result

Given the following tables …

Table: worker
id    name
---   -----------      
1     Guillermo Sparks
2     Gene	Roberts
3     Ally Jones
4     Bryant Summers
5     Candice Green
Table: departments
id    name            manager_id
---   -------------    --------- 
1     Financial               3        
2     Strategy                5        
3     IT                      1        
4     Marketing            NULL     

… What will be the result of the query below?

SELECT name 
FROM worker 
WHERE id NOT IN (SELECT manager_id FROM departments)

Exercise 5 – Write a Query

The EMPLOYEE table has the following attributes: NAME, DEPT_ID, and SALARY. Write a query that shows the highest salary in each department.

Exercise 6 – Write a Date Query

Write an SQL query that displays the current date.

Exercise 7 – Write a Query

Write an SQL query that checks whether a date (1/04/12) passed to the query is in a given format (MM/YY/DD).

 Exercise 8 – Find and Delete Duplicates

Write an SQL query to find duplicate rows in two tables (EMPLOYEE and WORKERS), and then write a query to delete the duplicates.

 Exercise 9 – Write a Complex Query

Write a query that lists courses’ subject names and the number of students taking the course only if the course has three or more students enrolled.

Table: subject
subject_id  subject_name       max_score        lecturer
----------  ----------         ----------       -----------  
 11         Math                    130         Christena Solem 
 12         Computer Science         50         Jaime Pille 
 13         Biology                 300         Carrol Denmark 
 14         Geography               220         Yuette Galang 
 15         Physics                 110         Colton Rather 
 16         Chemistry               400         Nan Mongeau
Table: student
student_id   student_name       city           subject_id
-----------  ------------       ----------    -----------
2001         Thurman Thorn      New York               11
2002         Sharda Clemens     San Francisco          12
2003         Buck Elkins        New York               13
2004         Fabian Johns       Boston                 15
2005         Brad Cameron       Stanford               11
2006         Sofia Roles        Boston                 16
2007         Rory Pietila       New Haven              12
2008         Cicely Weish       Tulsa                  14
2011         Richard Curtin     Boston                 11
2012         Kassy Ledger       Stanford               11
2013         Henry Ledger       Miami                  13
2014         Darius Fidzberg    San Francisco          12
2015         Darcey Fiorillo    Chicago                14

 Exercise 10 – Write a Complex Query

Write a query that displays the average age of workers in each company. The result should show the name of the company and the age of the company’s youngest worker.

Table: company
ID     NAME
------ -------------
    1  Amazon
    2  Samsung
    3  LG
    4  Kia
    5  Lenovo
Table: workers
ID      NAME                             AGE     COMPANY_ID
------- ------------------------- ---------- -------------
    1   Derick Davison                    20            5
    2   Bari Becnel                       50            1
    3   Maddie Mueller                    33            2
    4   Eva Emrich                        23            1
    5   Katelyn Kunze                     55            3
    6   Hue Hover                         26            2
    7   Lucas Lout                        57            4
    8   Granville Grande                  30            5
    9   Robt Rude                         39            3
   10   Lisha Lewin                       25            4
   11   Aron Atlas                        52            3
   12   Chester Coddington                44            4
   13   Diedre Dominy                     31            1
   14   Evie Edgell                       35            2
   15   Judy Johanson                     36            5

Advanced SQL Interview Questions

OK, so we know what to expect during an interview for a job that only requires SQL basics. But what about a more advanced job, like SQL developer or database administrator? Recruiters will be interested in more than just your SQL knowledge; they will also need to know how savvy you are with databases in general. In that case, see how well you can verbally answer questions like these:

  • What are primary and foreign keys?
  • What is normalization? What is denormalization?
  • What is an index? Can you briefly explain the different index types?
  • Can you explain database relationships and relationship types?
  • What is a database cursor?
  • What is a constraint?
  • What is a database transaction? A database lock?
  • How do you use the NULLIF function?
  • What is the purpose of the NVL function?
  • What is the difference between the RANK() and the DENSE_RANK() functions?
  • What is the difference between VARCHAR2 and CHAR datatypes?

Again, even if you know the answers to these questions, it pays to practice them. You may even want to practice answering them out loud!

So you see, SQL job interview questions and exercises are not as scary as you think. Of course, it’s also not a piece of cake, either. We’ve gone over some common skill-evaluation methods, but a lot depends on your interviewer’s imagination and experience. The most important thing is showing the recruiter that you understand how SQL works and that you can, if needed, find help on the Internet.

If you don’t have an interview on the horizon, now is the time to hone your SQL skills. Either way, I hope that this article has helped you learn what to expect and how to prepare for an SQL-related job interview when you get one!

  • Evelyn Pan

    hello i wonder where i can din these answer to these questions?

    • Marek

      Hey 🙂
      Sorry you had to wait 🙂
      If you want answers to theoretical questions, it’s very easy to find them on the web.
      Unfortunately we didn’t predict to include solutions to practise questions, but don’t worry ! There are many similar exercises at Vertabelo Academy, which will be very good practise! I can assure you – after that, you will not need any answer to article questions and be able to make them without hesitation 😉

      • Evelyn Pan

        thanks marek, already starting to take the lesson on the website and exericises were super helpful for me to grasp these concepts.

        • Marek

          I’m so glad to hear that ! Keep practise and good luck !

GET ACCESS TO EXPERT SQL CONTENT!