Top 25 Common SQL Interview Questions and Answers

SQL interview questions are common, and you need to have a good understanding of how to work with databases in order not to fail. This list below presents the basic SQL questions and tasks that are common in job interviews and the answers to them.

Image for post
Image for post
Source: Pexels

📕Theory Q&A📕

👉1. What is DBMS?

Let’s say there is a large database that many employees use: someone is looking for information, and someone is changing or even deleting it. To properly process all these requests, you need special software, and it is this software that is called the database management system (DBMS).

👉 2. What types of DBMS do you know?

This SQL question is about not just naming but also giving a short description of each type.

There are two types of DBMS :

  1. Relational, which supports establishing relationships between tables using primary and foreign keys. An example is MySQL.
  2. In contrast, non-relational DBMSs assume the absence of such mechanisms for linking data from different segments. An example is MongoDB

👉3. What is a Primary Key?

The primary key is or is PRIMARY KEYintended to uniquely identify each record in the table and is strictly unique ( UNIQUE): two table records cannot have the same primary key values. Zero values ​​( NULL) in are PRIMARY KEYnot allowed. If PRIMARY KEYmultiple fields are used as , they are referred to as a composite key.

Example:

CREATE TABLE USERS (
id INT NOT NULL,
name VARCHAR (20) NOT NULL,
PRIMARY KEY (id)
);

Here the id field is used as the primary key.

👉4. When is the PRIMARY KEY used?

PRIMARY KEY is a primary key that is used as the primary key and can be used to link to a child table that contains a foreign key.

👉5. What is a foreign key?

A foreign key or is FOREIGN KEYalso a constraint attribute and provides a link between two tables. Basically, it is a field or multiple fields that are referenced PRIMARY KEYin the parent table.

Usage example:

CREATE TABLE order (
order_id INT NOT NULL,
user_id INT,
PRIMARY KEY (order_id),
FOREIGN KEY (user_id) REFERENCES users(id)
);

In this case, the foreign key bound to a field user_idin the table orderrefers to the primary key idin the table users, and it is these fields that are used to link the two tables.

👉6. What other restrictions do you know, how they work, and are indicated?

SQL constraints are specified when creating or modifying a table. These are rules for limiting the type of data that can be stored in a table. The action on the data will not be performed if the set restrictions are violated.

  • UNIQUE - guarantees uniqueness of values ​​in a column;
  • NOT NULL- the value cannot be NULL;
  • INDEX - creates indexes in the table for quick searches / queries;
  • CHECK - the column values ​​must meet the specified conditions;
  • DEFAULT - provides the column with default values.

👉7. What is the ORDER BY keyword used for?

To sort data in ascending ( ASC) or descending ( DESC) order.

Usage example:

SELECT * FROM user ORDER BY name DESC;

Users are selected to be sorted by name in descending order. Supplement the answer to this SQL question so that, without specifying, the DESCdata would be sorted by default - in ascending order:

SELECT * FROM user ORDER BY name;

👉 8. What are the four main types of joins in SQL

To combine two tables into one, use the operator JOIN. A table join can be an inner ( INNER) or an outer ( OUTER) join, and an outer join can be left ( LEFT), right ( RIGHT), or full ( FULL).

  • INNER JOIN- getting records with the same values ​​in both tables, i.e. getting the intersection of tables.
  • FULL OUTER JOIN- joins records from both tables (if the join condition is true) and supplements them with all records from both tables that do not have matches. For records that do not have a match from another table, the missing field will be meaningful NULL.
  • LEFT JOIN - Returns all records that satisfy the join condition plus any remaining records from the outer (left) table that do not satisfy the join condition.
  • RIGHT JOIN - works exactly the same as left join, only the right join will be used as the outer table.
four main types of joins in SQL
four main types of joins in SQL
Four main types of joins in SQL

Let’s consider an example of joining SQL tables using INNER JOIN. The following query selects all orders with customer information:

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID

👉 9. What is Self JOIN?

This question can also be asked in a SQL interview. This expression is used to make the table merge with itself as if they were two different tables. To implement this, one of these “tables” is temporarily renamed.

For example, the following SQL query combines customers from the same city:

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;

👉 10. What is the UNION operator for?

It is used to combine retrieved data from two or more queries that must have the same number of columns with the same data types and in the same order.

Usage example:

SELECT column(s) FROM first_table
UNION
SELECT column(s) FROM second_table;

👉 11. How do wildcards work?

These are special characters that are needed to replace any characters in the request. They are used in conjunction with an operator LIKE, with which you can filter the requested data.

👉 12. What are wildcards?

  • % - replace zero or more characters;
  • _ - replace one character.

Examples:

SELECT * FROM user WHERE name LIKE '%test%';

This query allows you to find the data of all users whose names contain “test”.

SELECT * FROM user WHERE name LIKE 't_est';

And in this case, the names of the searched for users begin with “t”, then contain any symbol and “est” at the end.

👉 13. What do Aliases aliases do?

SQL aliases are needed to give a temporary name to a table or column. This is necessary when the query contains two or more tables or columns with complex names. In this case, for convenience, pseudonyms are used in the query. The SQL alias only exists for the duration of the query.

Example:

SELECT very_long_column_name AS alias_name
FROM table;

👉 14. What is the INSERT INTO SELECT statement for?

This statement copies data from one table and inserts it into another, while the data types in both tables must match.

Usage example:

INSERT INTO second_table
SELECT * FROM first_table
WHERE condition;

👉 15. What are normalization and denormalization?

Relationship normalization in SQL is designed to organize information in the database in such a way that it does not take up much space and is convenient to work with. This is the removal of redundant data, elimination of duplicates, identification of sets of related data through PRIMARY KEY, etc.

Accordingly, denormalization is the reverse process, which introduces redundant data into the normalized table.

👉 16. Explain the difference between DELETE and TRUNCATE commands

A command DELETEis a DML operation that removes records from a table that match a given condition:

DELETE FROM table_name WHERE condition;

But the command TRUNCATEis a DDL operation that completely re-creates the table:

TRUNCATE TABLE table_name;

👉 17. How is VARCHAR different from NVARCHAR?

The main difference is that it VARCHARstores values ​​in ASCII format, where a character takes up one byte, and NVARCHARstores values ​​in Unicode format, where a character "weighs" 2 bytes. The type VARCHARshould be used when you are sure that the values ​​will not contain Unicode characters. For example, VARCHARit can be applied to email addresses that contain ASCII characters.

📕Practice Q&A📕

👉 18. How to select records with odd Ids?

One of the most common interview questions. The SQL query for selecting records with odd ones idshould look like this:

SELECT * FROM sample WHERE id % 2 != 0;

If the remainder of division idby 2 is zero, we have an even value, and vice versa.

👉 19. How to find duplicates in the email field?

SELECT email, COUNT(email)
FROM customers
GROUP BY email
HAVING COUNT(email) > 1;

The function COUNT()returns the number of rows from the field email. The operator HAVINGworks in much the same way WHERE, except that it is applied not for all columns, but for the set created by the operator GROUP BY.

👉 20. When selecting from the table, add 1 day to the date

SELECT DATE_ADD(date, 1 DAY) as new_date FROM table;

The function DATE_ADD()adds a specified period of time to the date. The syntax is as follows:

SELECT DATE_ADD(date, INTERVAL what_to_add) FROM table_name WHERE condi;

👉 21. Select user records with different names

SELECT DISTINCT name FROM users;

SELECT DISTINCT only returns different values, even if there are duplicates in the selected column.

👉 22. Find in the table the average salary of employees

SELECT AVG(salary) FROM workers;

The function AVG()applies only to numeric data types and returns the average over the column.

👉 23. Replace the employee’s salary in the table with 1000 if it is 900, and 1500 otherwise

Replacing values ​​is one of the most frequently encountered SQL interview tasks. It is not difficult to solve it:

UPDATE table SET salary =
CASE
WHEN salary = 900 THEN 1000
ELSE 1500
END;

The operator is UPDATEused to modify existing records. But the answers to such questions from SQL interviews should be more detailed. Clarify afterward UPDATEwhich records should be updated. Otherwise, all records in the table will be updated.

In our example, the condition is set through the CASE statement: if the current salary is 900, we change it by 1000, in other cases — by 1500.

👉 24. When fetching from the user's table, create a field that will include both names and salary

SELECT CONCAT(name, salary) AS new_field FROM users;

The function is CONCAT()used to concatenate (concatenate) strings, implicitly converting any data types to strings.

👉 25. Rename the table

ALTER TABLE first_table RENAME second_table;

Using the operator, ALTER TABLEyou can add, delete, change columns, and change the name of the table.

Read more

If you found this article helpful, click the💚 or 👏 button below or share the article on Facebook so your friends can benefit from it too.

Written by

Bioinformatician at Oncobox Inc. (@oncobox). Research Associate at Moscow Institute of Physics and Technology (@mipt_eng).

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store