Short for Structured Query Language, SQL is one of the most sought after skills that recruiter look for while hiring in the tech industry such as that of analytics and data science. The language, which is used to communicate with databases is necessary for analysing large data sets, especially in case of relational database.
Given its importance in the tech world, it is not unlikely for the companies in analytics, business intelligence, software engineering, and others to conduct SQL interviews before they hire the right candidate. Right from entry level to experienced professionals, candidates are expected to know quite a deal about this language, despite having a good hand at in other languages such as Python, Java, C++ and others.
Right from theoretical questions to writing SQL queries, there can be questions of all sorts that can be asked in these interview. We picked up 10 entry level questions that are frequently asked to analytics aspirants. Take a look.
1. What is SQL and relational database?
This could probably be the most inevitable question in any tech interview about SQL, and while you may have written the bookish definition many a times in your exams, what recruiters are looking here is the simplest explanation of the term.
The most interesting and impressive way to answer this could be by citing examples. You could begin with telling what relational database is, such as it is a set of data tables that are linked and related to each other, and is used to store different information that can be pulled to answer specific analytical question. And SQL is a language to communicate with this relational database.
For instance, for an online retailer customers and orders could be created in the form of relational database and show how different pieces of data are linked together.
2. SQL JOIN clauses and their uses.
As a part of your SQL course, you must have come across the term JOIN clause, which is used to return a table that merges the contents of two or more other tables together. It can be used for instance, to join two different tables such as of customers and orders to create a new table with all necessary information. Various types of JOIN clauses are Inner Join, Left Join, Right Join, Full Join and Cross Join, joining the results from left, right and all the results depending on the need.
3. Using INSERT, UPDATE and DELETE in SQL databases.
As the name suggests, these options let you insert new rows, make changes to existing records and permanently remove records from a database. A recruiter might further ask you to write query using the same, which may typically be written using INSERT, UPDATE, and DELETE functions. A typical query with INSERT function might look like:
INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);
4. Comparison operators used in SQL.
Found within the WHERE clauses, Comparison operators are used to refine queries to keep the data that matches certain criteria. Equal to (=), not equal to (<>), greater than (>), greater than or equal to (>=), less than (<), less than or equal to (<=) are some of the the comparison operators that are widely used in SQL. Few others are IN() that matches a value within another list, NOT that defines the opposite of given condition amongst others.
5. Cursors and different type of cursors in SQL.
This option in SQL lets you retrieve data from a result row by row and is especially required when it is required to update records in a database table one row at a time. There are various types of cursor options available such as static, dynamic, forward only and keyset driven cursors, and it would be a good idea to have a detailed understanding of what these cursors do, as this can be one of the frequently asked SQL questions in a tech interview.
6. Write a query: such as write a query to find and remove duplicates in a table without creating another table.
Running an SQL query may be a tricky job such as that of cleaning up, de-duplication and much more. Recruiters are keen on hiring candidates who are well versed with writing a query as basic as finding and removing duplicates in a table. A table with a primary key doesn’t usually need duplicates but if due to some reasons, the keys are disabled, it may require removing duplicates.
7. Writing query related to employees such as writing a query to show employees who have higher salaries than their manager, finding the second highest salary of the employee, listing all the departments in a company with total number of employees etc.
8. Figuring out what is wrong in a given query. For instance,
The interviewer here could give a query with few errors in it and you have to get it right by identifying ‘what’ and ‘how’ is it wrong. Suppose an interviewer gives a query;
SELECT UserId, AVG(Total) AS AvgOrderTotal
FROM Invoices
HAVING COUNT(OrderId) >= 1
The issue here is the missing ‘Group By’ clause, which would get the average order amount by customer where the customer has at least one order.
So, the correct query could be:
SELECT UserId, AVG(Total) AS AvgOrderTotal
FROM Invoices
GROUP BY Userid
HAVING COUNT(OrderId) >= 1
9. How have you made use of SQL previously?
This question could be the most frequently asked questions in an interview as it would give the interviewer an idea of how well versed are you with the applications and usage of SQL. A wide range of professions have large datasets and they all make use of databases—online stores, libraries, salons and even the government agencies, being a few. The recruiters here are interested in knowing if you have a practical experience in SQL and if yes, where!
10. What is normalisation and denormalisation in SQL?
To answer this question, the best way could be defining these terminologies by followed by a few examples of how to use it in SQL. This would suggest that not only you know these terms but are well versed with its use. Normalisation is the process that organises data into a related table, establishes relationship between table and thus reduces redundancy. De-normalisation, on the other hand is a contrary process of adding redundant data to get rid of complex join to optimise database performance.
