SQL is one of the most popular coding languages today and its domain is relational database management systems. And with the extremely fast growth of data in the world today, it is not a secret that companies from all over the globe are looking to hiring the best specialists in this area. So, imagine you are at an interview for your ideal job and advanced professionals are sitting in front of you, interested in how you would perform. Such a meeting will be crucial for both sides.
However, there’s no reason to freak out! To reduce the stress, here are our top tips to answering 10 frequently encountered SQL interview questions.
What is SQL?
SQL is an acronym for Structured Query Language. It is a programming language specifically designed for working with databases. Of course, some may argue and say it is not exactly a programming language since it has not been created with the idea of using features of procedural languages such as conditional statements or “for” loops.
These people will insist on calling SQL a coding language because it is only about executing commands for querying, creating, inserting, updating, and deleting data in a database.
Nevertheless, it is more important to know what the domain of SQL is. But don’t rush to tell that to the interviewers, as this might be your next question! And in our exemplary excerpt with SQL interview questions, that’s exactly the case!
What is a Database? What is a DBMS?
A database, implying an electronic database, is data stored on a computer and organized in a way that makes it easy to access and manipulate. The software tool that allows the user to interact with the data stored in the database is called a database management system – DBMS.
You could wrap up the two questions by saying there are two types of database management systems – relational and non-relational. SQL is a language, designed only for working with relational DBMSs.
It is normal that the interviewers start with two fundamental questions that you feel at ease with. Thus you can relax and get ready to proceed with some more challenging ones.
More on the SQL language and database management systems you can read in our tutorial Why You Should Learn SQL.
What is the difference between DDL, DML, DCL, and TCL?
First of all, what do these acronyms mean?
“L” stands for “Language” in all of them. And this must help you remember that these are the four categories in which the SQL commands have been separated into.
DDL stands for Data Definition Language and includes commands which allow you to CREATE, DROP, ALTER, and TRUNCATE data structures. DML, instead, involves commands for manipulating information. It actually means “Data Manipulation Language”, and regards the possibility to SELECT, INSERT, UPDATE, and DELETE data.
If you are using SQL in the sphere of data science or business intelligence, it is this part of the language you will most use at work.
DCL, Data Control Language, consists of commands that are typically used by database administrators. This category allows the programmer to GRANT and REVOKE rights delineating how much control you can have over the information in the database.
Similarly, TCL, which is the Transaction Control Language, also contains commands applied by database administrators. They ensure the transactions occurring within the database will happen in such a way that minimalizes the danger of suffering from data loss.
What is the point of using a foreign key constraint?
After you go through the fundamental SQL interview questions, you are likely to be asked something more specific. Therefore, your next task won’t be about explaining what SQL constraints and keys mean in general, although you must be very familiar with the concept. You will rather be given the chance to demonstrate your ability to elaborate on a specific type of an SQL constraint – the foreign key constraint.
The foreign key constraint comprises a set of rules, or limits, that will ensure that the values in the child and parent tables match. Technically, this means that the foreign key constraint will maintain the referential integrity within the database.
If you want to dig deeper into this subject, here we explain primary, foreign, and unique keys in more detail
Define and provide an example of using an inner join.
It’s not all about theory. Using a hands-on approach to handling realistic tasks is often times way more important. That’s why you’ll have to deal with practical SQL interview questions, too.
Obviously, you must be aware that joins are one of the most frequently used tools in SQL, regardless of your job role. Particularly if you are working in the sphere of business intelligence, your work will be centred around understanding SQL joins in depth.
So, an SQL join is a tool that allows you to construct a relationship between objects in your database. Consequently, a join shows a result set containing fields derived from two or more tables.
For instance, assume that in one table you have data about the customer ID and fields related to the sales a customer has made, and in the other, you have data about the customer ID and their private information, such as first and last name and email address.
Therefore, an inner join allows you to obtain an output containing information from both tables only for the customer IDs found in the two tables that match. Provided that you set the customer ID field to be a matching column, of course.
Using the previous example, explain how to use a left join.
SQL joins is such an important topic that it could lead to a follow-up question. It is good to provide a sharp answer in this case.
You could say “Unlike an inner join, a left join will ensure that we extract information from both tables for all customer IDs we see in the left table. The customer IDs that match between the two tables could contain data from the right table as well, while the IDs that are only found in the left table will display null values in the place of the columns from the right table.
To expand your knowledge on this topic, check out this article
What is the difference between MySQL and PostgreSQL? How about between PL/SQL and SQL?
Now, this is a tricky one.
Basically, the reason for encountering an SQL interview question like this is that the interviewer wants to understand the extent you are acquainted with the fact that SQL has a few versions, each carrying specific characteristics.
You could say that MySQL and PostgreSQL are just two versions of the Structured Query Language. Since you’ve just been asked about joins, you could mention that PostgreSQL supports outer joins, while MySQL doesn’t – you’ll need to use UNION or UNION ALL to emulate an outer join in MySQL. And thus, you could perhaps impress the interviewers with additional knowledge in this subject.
PL/SQL is not a version of SQL, though, and that’s the tricky part of the question. PL/SQL is a complete procedural programming language and its scope of application is different. It is not strictly related to relational databases.
What is this query about?
SELECT emp_no, AVG(salary)
FROM salaries
GROUP BY emp_no
HAVING AVG(salary) > 120000
ORDER BY emp_no;The version of SQL in which this query has been written is MySQL, but you won’t really need to mention that. Even if you don’t recognize the version, then common sense, the keywords you see, and the names of the fields should convince you this query is about extracting the average salary obtained by employees only when the salary value is larger than 120,000 dollars.
And don’t be surprised if after you provide your answer, the interviewer asks: “And the database won’t throw an error?”. Read the query carefully before you reply. It is much better to double-check and be sure that in this situation, everything is correct.
More on the differences between using WHERE or HAVING you can find in this tutorial
The following two tables are part of the database you are working with. Write a query that will display the salaries received by the last contract of a given employee as a result. Limit the number of obtained records to 1,000.
SELECT s1.emp_no, s1.from_date, s1.salary
FROM salaries s1
WHERE s.from_date =
(SELECT MAX(s2.from_date)
FROM salaries s2
WHERE s2.emp_no = s1.emp_no GROUP BY emp_no)
LIMIT 1000;As a matter of fact, this is a question about using an SQL subquery – a subset of SELECT statements whose output sets the conditions which the data for the main query will be filtered upon. However, you might not be given this hint, so it is on you to remember that in such a situation a subquery is exactly what you need.
And this is a rather complex query, to be honest. However, by asking you to create one, the questioners can check your command of the SQL syntax, as well as the way in which you approach solving a problem. So, if you don’t manage to get to the right answer, you will probably be given time to think and can definitely catch their attention by how you try to solve the problem.
Curious to know more about using SQL Subqueries? Then go to this tutorial.
What is an SQL View?
To conclude the interview, your potential future employers may prefer to give a toned-down SQL interview question. That’s why they might ask you something that is not related and revert back to asking a general question.
A view is a virtual table whose contents are obtained from an existing table or tables, called base tables. The retrieval happens through an SQL statement, incorporated into the view. So, you can think of a view object as a view into the base table. The view itself does not contain any real data; the data is electronically stored in the base table. The view simply shows the data contained in the base table.
If you’re interested in learning more about this tool, check out our tutorial Introduction to SQL Views.
General tips.
Although you may have answers to all the SQL interview questions you’ve been asked, there are many other components that will determine whether you will land the job. The company you are applying for may have very strict requirements regarding work ethics, backgrounds of employees, and so on. And it all counts, trust me.
So, if you want to be fully prepared to make a great first impression, check out the most comprehensive article out there: Starting a Career in Data Science: The Ultimate Guide.
However, nothing else will really matter if you are not a good professional, right? That’s why you have to stay focused on SQL and learn as much as you can about it. If this is what you are eager to do next, check out the tutorials we provided above, or feel free to find more content about SQL on our blog.
Good luck!
References
- https://365datascience.com/why-learn-sql/
- https://365datascience.com/sql-relational-databases/
- https://365datascience.com/techniques-for-processing-traditional-and-big-data/
- https://365datascience.com/sql-why-databases/
Previously published at https://365datascience.com/sql-interview-questions/