By
They say that practice makes perfect, and that's especially true when it comes to passing the dreaded technical interview.
During a technical interview, you'll need to show that you have an in-depth knowledge of the skills and languages that you claim to have. The point of a technical interview is to convince the employer that you can use the technology to solve practical, real-world problems.
If you're preparing to interview for any kind of job in data science or software, you've more than likely heard of SQL. In this article, we'll provide an in-depth guide for how to prepare for a technical interview for SQL jobs, including real SQL questions and answers.
What Kind of Jobs Use SQL?
SQL isn't just for programmers anymore. If your position requires you to work with databases on a daily or weekly basis, there's a good chance that you'll have to know SQL.
Some of the most common job titles that need SQL skills are:
-
Quality assurance tester
-
Project manager
-
IT consultant
-
IT technical support
Even if SQL isn't a must for the position, knowing SQL can be a key differentiator that helps get you noticed, or even wins you the job. If the title of the job you're applying for resembles any of the positions above, it's definitely a good idea to brush up on SQL.
Must-Know Basic SQL Interview Questions
Before you can start answering advanced technical questions about SQL, there are a few basics you need to get down. Interviewers often like to include one or more "easy" questions about
1. What is a relational database management system (RDMS)?
A relational database management system is a system for managing a relational database. The RDMS is responsible for creating, deploying, maintaining, and monitoring one or more databases within an enterprise environment.
What is a relational database, you might ask? It's a database that uses the relational model, which stores information in the form of tables with rows and columns. Each row represents a different record or entity. Each column represents a different field or attribute of that entity.
For example, a university could use an RDMS to store information about their students:
-
One table contains students' personal information. Each row in the table represents a different student. Each column represents a different attribute about the student: first name, last name, address, email, ID number, etc.
-
A second table contains information about the students in a given class. Each row in the table represents a different student. Each column represents a different attribute: first name, last name, ID number, grades on different homework assignments and exams, etc.
Are you familiar with spreadsheet programs such as Microsoft Excel? If so, you already understand the basic idea behind the relational model of data. A single Excel file has a structure very much like a single table in a relational database.
2. What is SQL?
SQL (Structured Query Language) is the standard language for storing, reading, updating, and deleting information in a relational database.
The terms "SQL database" and "relational database" are often used synonymously, although they're not quite the same thing. Although SQL is used for virtually every modern RDMS, there's no rule that requires it to be so.
A few experimental projects, such as Rel, seek to implement a relational database with a different query language.
3. What are the major enterprise relational database systems?
The four main enterprise options for SQL databases are:
-
MySQL
-
Oracle Database
-
IBM DB2
Most enterprise versions of SQL databases come with their own "dialect" of SQL. For example, Oracle has created the PL/SQL extension of SQL, while Microsoft SQL Server uses the Transact-SQL language.
These different SQL versions are usually fairly similar on the whole. However, each has its own features and quirks to distinguish it from other options.
4. What is the difference between SQL and NoSQL databases?
Relational databases aren't the only model for storing information in a database. Non-relational databases (also known as NoSQL databases) use a concept other than the relational model to store data.
There are several non-relational models for storing data, including:
-
Key-value: Each item in the database is a key paired with a corresponding value.
-
Document: Each item in the database is a document that stores data in a standard format, and is then associated with a unique key.
-
Column: Data is stored in columns instead of rows, in order to improve performance for certain types of queries.
-
Graph: Data is stored as a graph with nodes and edges that describe the relationships between different items.
Some of the most popular NoSQL databases are MongoDB, Apache Cassandra, HBase, and Redis.
Unlike SQL databases (which use SQL), NoSQL databases do not use a single, standard query language for interacting with data.
Must-Know Technical SQL Interview Questions
Once you've mastered the basic knowledge above, it's time to move on to some SQL interview questions that are a little more complex.
1. What are the 5 major types of SQL statements?
The 5 major types of SQL statements are:
-
Data Definition Language (DDL): Used to define or change the database's structure (e.g. the CREATE, ALTER, and DROP statements).
-
Data Manipulation Language (DML): Used to change the records present in a database (e.g. the INSERT, UPDATE, and DELETE statements).
-
Data Query Language (DQL): Used to query the database for information that matches the parameters of the request (e.g. the SELECT statement).
-
Data Control Language (DCL): Used to set privileges, roles, and permissions for different users of the database (e.g. the GRANT and REVOKE statements).
-
Transaction Control Language (TCL): Used to save or revert the changes made by DML statements (e.g. the COMMIT and ROLLBACK statements).
2. What is the difference between a primary key, a unique key, and a foreign key?
Primary keys, unique keys, and foreign keys are similar yet distinct concepts:
-
A primary key is an attribute or attributes serving as a unique identifier for each record. For example, the primary key for students at a university could be their student ID numbers, since each student has a unique ID number. Each table can have only one primary key.
-
A unique key is an attribute that must be different for each record. Unlike primary keys, there can be multiple unique keys in a table. For example, we may want students' phone number to be a unique key, since no two students should have the same number. However, this would not be a good primary key, since students' phone numbers may change during their studies.
-
A foreign key is an attribute in one table that refers to a primary key in a different table. For example, suppose we have a table that contains the students in a given class. We might insert a foreign key in this table that refers to the primary key in another table (such as the students' personal information).
3. What are the different types of relationships in a relational database?
The different types of relationships in a relational database are:
-
One-to-one: A relationship in which a single record of type A may be linked only to a single record of type B, and vice versa. For example, a student's name has a one-to-one relationship with the student's ID number. Each student has only one ID number, and each ID number is linked to only one student.
-
One-to-many: A relationship in which a single record of type A may be linked to multiple records of type B. For example, a professor may teach multiple courses.
-
Many-to-one: A relationship in which multiple records of type A may be linked to a single record of type B. For example, multiple courses may be taught by the same professor. This is the inverse of a one-to-many relationship.
-
Many-to-many: A relationship in which multiple records of type A may be linked to multiple records of type B. For example, a student may take multiple courses, and each course may be taken by multiple students.
4. What are the different types of clauses in SQL?
SQL clauses are used to qualify a database query by restricting or altering the values that it returns. The types of SQL clauses are:
-
FROM: Used to specify which tables the data will be pulled from.
-
WHERE: Used to filter results by requiring them to fulfill one or more conditions.
-
ORDER BY: Used to specify how the results of a query should be sorted. For example, if you are returning students' records, you may wish to order them by
last name or by ID number. -
GROUP BY: Used to group together rows that have the same values. For example, you may wish to group together students based on their major.
-
HAVING: Used in combination with the GROUP BY clause. It restricts the returned values to only those that fulfill a given condition.
-
DISTINCT: Used to retrieve a table containing records with the duplicate values removed. For example, you may wish to count the number of different states or countries that students are from.
5. What are the different types of JOIN clauses in SQL?
A JOIN clause combines records from multiple tables into a single table, based on the common values that they share between one or more columns.
The different types of JOIN clauses in SQL are:
-
INNER JOIN: Returns all records that have at least one match in both tables.
-
LEFT JOIN: Returns all records from the left table and all matching records from the right table.
-
RIGHT
JOIN: Returns all records from the right table, and all matching records from the left table. -
FULL JOIN: Returns all records that have at least one match in either table.
-
CROSS JOIN: Returns all possible combinations of rows from the left table and the right table. This is known as the Cartesian product.
You can think of the first four JOIN types in terms of a Venn diagram:
-
INNER JOIN represents the intersection of both circles.
-
LEFT JOIN represents the left circle in the diagram.
-
RIGHT JOIN represents the right circle in the diagram.
-
FULL JOIN represents the union of both circles.
6. What is the difference between the DELETE, TRUNCATE, and DROP commands in SQL?
Although closely related, the DELETE, TRUNCATE, and DROP commands in SQL aren't quite the same thing:
-
DELETE: Used on a given table to remove the record or records that match a certain condition (using the WHERE clause).
-
TRUNCATE: Used on a given table to delete all the records it contains (although not the table itself). Unlike the DELETE command, it is irreversible.
-
DROP: Used to delete an entire table.
7. What does a NULL value represent in SQL?
NULL is a special signifier in SQL that represents "no value." A field with a NULL value is not equivalent to a field with a value of
NULL values are common when adding a record with one or more optional fields. If you do not enter a value for an optional field, it will take on the value NULL until you change the field's value.
You can test whether a field has a NULL value by using the SQL operators IS NULL and IS NOT NULL.
8. Write a SQL query to retrieve the records of students whose first name begins with the letter D.
Suppose the table of student records is called Students, and the field containing their first names is called First_Name. Then the corresponding SQL query would be:
SELECT * FROM Students WHERE First_Name LIKE 'D%';
The % operator matches 0 or more characters. Meanwhile, the _ operator matches exactly one character. For example, the query:
SELECT * FROM Students WHERE First_Name LIKE 'Davi_';
would retrieve records of students whose first name is David or Davis.
9. Write a SQL query to remove all records of students who are 21 years old.
Suppose the field containing students' ages is called Age. Then the corresponding SQL query would be:
DELETE FROM Students WHERE Age = 21;
10. Write a SQL query to retrieve the first 10 records of students with ID numbers that end in 0.
Suppose the field containing students' ID numbers is called ID_Number. Then the corresponding SQL query would be:
SELECT TOP 10 ID_Number FROM Students WHERE ID_Number % 10 = 0 ORDER BY ID_Number;
To ensure that the number is evenly divisible by 10, we use the modulus operator %.
11. What is a SQL injection, and how can you prevent it?
A SQL injection is a type of malicious attack on a SQL database that can expose, alter, or delete sensitive data.
SQL injections occur when a user enters SQL code in a place where an application does not expect it, such as the input field on a form. The application then unintentionally executes this code. Depending on the contents of the query, the application might return sensitive information, such as usernames and passwords, that the user should not be able to access.
You can prevent SQL injections by sanitizing and validating all user input. Avoid constructing SQL queries based on user input as much as possible.
12. What are the ACID database properties?
The acronym ACID describes four properties that are desirable for any database: atomicity, consistency, isolation, and durability.
-
Atomicity: Every transaction is an "all or nothing" unit that either totally succeeds or totally fails. If one part of the transaction fails, the entire transaction must fail and the database must remain unchanged.
-
Consistency: The database must move from valid state to
valid state, obeying all defined rules and constraints. -
Isolation: Executing transactions at the same time must have the same result on the database as if they were run one after the other.
-
Durability: If a transaction is successfully committed to the database, then it must remain committed. This is true even if the system crashes or loses power.
13. What are the goals and methods of database testing?
Database testing is necessary to ensure that a database adheres to the ACID properties described above.
Some of the most important database elements to test are:
-
Transactions: Testers should verify that the database continues to fulfill the ACID properties after every transaction.
-
Schema: A schema is the formal structure of a database. It describes how the data is organized and what relations exist between the records and fields. Testers should verify that the schema mapping between the front end and back end is correct. In addition, the schema should contain common-sense restrictions on the contents of a field (for example, ensuring that the ID number field is a numeric value).
-
Triggers: A trigger is a database event that has been configured to occur once a given condition is fulfilled or another event takes place. Testers should verify that all triggers take place as expected.
Conclusion
No single guide can possibly capture all the SQL knowledge you might need for a technical interview. By knowing the information above, however, you'll be going a long way to prepare yourself.
Ready to start the hunt? Check out our article about 10 fantastic SQL jobs in 2019. And once you've landed that dream job, give SQLBot a try. SQLBot is a quick and simple SQL reporting tool for developers and business users. Try SQLBot for yourself and start getting the on-demand insights you need in your Slack and email inbox.
