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.
👉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 :
- Relational, which supports establishing relationships between tables using primary and foreign keys. An example is MySQL.
- 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.
CREATE TABLE USERS (
id INT NOT NULL,
name VARCHAR (20) NOT NULL,
PRIMARY KEY (id)
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.
CREATE TABLE order (
order_id INT NOT NULL,
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
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 (
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 (
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
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.
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
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.
SELECT column(s) FROM first_table
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.
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.
SELECT very_long_column_name AS alias_name
👉 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.
INSERT INTO second_table
SELECT * FROM first_table
👉 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
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.
👉 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)
GROUP BY email
HAVING COUNT(email) > 1;
COUNT()returns the number of rows from the field
HAVINGworks in much the same way
WHERE, except that it is applied not for all columns, but for the set created by the operator
👉 20. When selecting from the table, add 1 day to the date
SELECT DATE_ADD(date, 1 DAY) as new_date FROM table;
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;
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 =
WHEN salary = 900 THEN 1000
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.
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.