14th Mar 2017 8 minutes read Complete SQL Practice for Interviews Marek Pankowski how to SQL job market jobs and career sql interview questions Table of Contents Basic SQL Whiteboard Questions for Job Interviews Practical SQL Exercises for Interview SQL Exercise 1 – Write a Statement SQL Exercise 2 – Write a Statement SQL Exercise 3 – Find the Error SQL Exercise 4 – Find the Result SQL Exercise 5 – Write a Query SQL Exercise 6 – Write a Date Query SQL Exercise 7 – Write a Query SQL Exercise 8 – Find and Delete Duplicates SQL Exercise 9 – Write a Complex Query SQL Exercise 10 – Write a Complex Query Advanced SQL Whiteboard 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. 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. The best way to prepare for an interview is tons of practice. I recommend the SQL Practice track at LearnSQL.com. It contains over 600 interactive SQL exercises to help you review and practice SQL before an interview. In this article, I’ll help you prepare for the 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. our 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 SQL job market jobs and career sql interview questions