Back to articles list March 14, 2017 - 8 minutes read Complete SQL Practice for Interviews Marek Pankowski Tags: how to in sql SQL job market jobs and career sql job market career in data science sql 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… This article is meant as a SQL practice for interviews. I’ll help you prepare for the SQL and database aspects of your 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. This is why I created this SQL practice for interviews. Your interviewer may use several kinds of SQL whiteboard questions: 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 Whiteboard Questions for Job Interviews 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 SQL whiteboard questions: 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 from my SQL practice for interviews? 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 for Interview 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 – apart from answering typical SQL whiteboard questions, you’ll be asked to write a SQL statement, check a query for mistakes, or determine 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. SQL 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 SQL 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 SQL 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;); SQL 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) SQL 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. SQL Exercise 6 – Write a Date Query Write an SQL query that displays the current date. SQL 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). SQL 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. SQL 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 SQL 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 Whiteboard Questions OK, so we’ve already had some SQL practice for interviews but our exercises covered 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 SQL whiteboard 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 whiteboard 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 given you a substantial portion of SQL practice for interviews and helped you learn what to expect and how to prepare for an SQL-related job interview when you get one! Tags: how to in sql SQL job market jobs and career sql job market career in data science sql interview questions You may also like Is it Difficult to Learn SQL? Wondering if it’s difficult to learn SQL? The answer is a definite “no”! Here’s how to learn SQL quickly and easily. 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 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 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 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 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 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 You Want to Learn SQL? You've Come to the Right Place! If you want to learn SQL basics or enhance your SQL skills, check out LearnSQL.com for a wide range of SQL courses and tracks. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.