Top 40 SQL Interview Questions for Beginners

Intro

Whether you’re a job hunter looking for a new opportunity to apply your SQL skills or a hiring manager who is going to interrogate a candidate for a job opening in your company, knowing common SQL interview questions and answers is a must. Next, we’ll examine 40 essential SQL interview questions and answers for beginners. These will cover the basics of SQL, such as what it is, its applications, SQL statements, SQL commands, and types of SQL queries, among others.

40 Technical SQL Interview Questions

Now, let’s move on to the technical SQL interview questions and some potential answers to them. When answering technical questions, the best strategy is to give as precise answers as possible. It may look like an attempt to deviate from the main topic. In addition, it may provoke additional questions about which you can feel less confident.

1. What is SQL?

SQL stands for Structured Query Language, and it’s a programming language used for interaction with relational database management systems (RDBMS). This includes fetching, updating, inserting, and removing data from tables.

2. What are SQL dialects?

The various versions of SQL, both free and paid, are also called SQL dialects. All the flavors of SQL have a very similar syntax and vary insignificantly only in additional functionality. Some examples are Microsoft SQL Server, PostgreSQL, MySQL, SQLite, T-SQL, Oracle, and MongoDB.

3. What are the main applications of SQL?

Using SQL, we can: (1) create, delete, and update tables in a database; (2) access, manipulate, and modify data in a table; (3) retrieve and summarize the necessary information from a table or several tables; (4) add or remove certain rows or columns from a table. Overall, SQL allows querying a database in multiple ways. In addition, it easily integrates with other programming languages, such as Python or R, so we can use their combined power.

4. What is an SQL statement?

An SQL statement is also known as an SQL command. It’s a string of characters interpreted by the SQL engine as a legal command and executed accordingly. Some examples of SQL statements are SELECT, CREATE, DELETE, DROP, REVOKE, and so on.

5. What types of SQL commands do you know?

• Data Definition Language (DDL): Define and modify the structure of a database.
• Data Manipulation Language (DML): Access, manipulate, and modify data in a database.
• Data Query Language (DQL): Perform queries on the data in a database to retrieve the necessary information from it.
• Data Control Language (DCL): Control user access to the data in the database and give or revoke privileges to a specific user or a group of users.
• Transaction Control Language (TCL): Control transactions in a database.

6. Give some examples of common SQL commands of each type.

• Data Definition Language (DDL): CREATE, ALTER TABLE, DROP, TRUNCATE, and ADD COLUMN.
• Data Manipulation Language (DML): UPDATE, DELETE, and INSERT.
• Data Query Language (DQL): SELECT.
• Data Control Language (DCL): GRANT and REVOKE.
• Transaction Control Language (TCL): COMMIT, SET TRANSACTION, ROLLBACK, and SAVEPOINT.

7. What is a database?

A database is a structured storage space, where the data is kept in many tables and organized so that the necessary information can be easily fetched, manipulated, and summarized.

8. What is DBMS, and what types of DBMS do you know?

DBMS stands for Database Management System, a software package used to perform various operations on the data stored in a database, such as accessing, updating, wrangling, inserting, and removing data. There are various types of DBMS, such as relational, hierarchical, network, graph, or object-oriented. These types are based on the way the data is organized, structured, and stored in the system.

9. What is RDBMS? Give some examples of RDBMS.

RDBMS stands for Relational Database Management System. It’s the most common type of DBMS used for working with data stored in multiple tables related to each other by means of shared keys. The SQL programming language is designed to interact with RDBMS. Some examples of RDBMS are MySQL, PostgreSQL, Oracle, MariaDB, etc.

10. What are tables and fields in SQL?

A table is an organized set of related data stored in a tabular form, i.e., in rows and columns. A field is another term for a column of a table.

11. What is an SQL query, and what types of queries do you know?

A query is a piece of code written in SQL to access or modify data from a database. There are two types of SQL queries:

• Select queries: Retrieve the necessary data (including limiting, grouping, ordering, and extracting the data from multiple tables, etc.).
• Action queries: Create, add, delete, update, rename the data, etc.

12. What is a subquery?

A subquery is also called an inner query, a query placed inside another query, or an outer query. A subquery may occur in the clauses such as SELECT, FROM, WHERE, UPDATE, etc. It’s also possible to have a subquery inside another subquery. The innermost subquery is run first, and its result is passed to the containing query (or subquery).

13. What types of SQL subqueries do you know?

• Single-row: Returns at most one row.
• Multi-row: Returns at least two rows.
• Multi-column: Returns at least two columns.
• Correlated: A subquery related to the information from the outer query.
• Nested: A subquery inside another subquery.

14. What is a constraint, and why use constraints?

A set of conditions defining the type of data that can be input into each column of a table. Constraints ensure data integrity in a table and block undesired actions.

15. What SQL constraints do you know?

• DEFAULT: Provides a default value for a column.
• UNIQUE: Allows only unique values.
• NOT NULL: Allows only non-null values.
• PRIMARY KEY: Allows only unique and strictly non-null values (NOT NULL and UNIQUE).
• FOREIGN KEY: Provides shared keys between two or more tables.

16. What is a join?

A clause used to combine and retrieve records from two or multiple tables. SQL tables can be joined based on the relationship between the columns of those tables.

17. What types of joins do you know?

• (INNER) JOIN: Returns only those records that satisfy a defined join condition in both (or all) tables. It’s a default SQL join.
• LEFT (OUTER) JOIN: Returns all records from the left table and those records from the right table that satisfy a defined join condition.
• RIGHT (OUTER) JOIN: Returns all records from the right table and those records from the left table that satisfy a defined join condition.
• FULL (OUTER) JOIN: Returns all records from both (or all) tables. It can be considered as a combination of left and right joins.

18. What is a primary key?

A column (or multiple columns) of a table to which the PRIMARY KEY constraint was imposed to ensure unique and non-null values in that column. In other words, a primary key is a combination of the NOT NULL and UNIQUE constraints. The primary key uniquely identifies each record of the table. Each table should contain a primary key and can’t contain more than one primary key.

19. What is a unique key?

A column (or multiple columns) of a table to which the UNIQUE constraint was imposed to ensure unique values in that column, including a possible NULL value (the only one).

20. What is a foreign key?

A column (or multiple columns) of a table to which the FOREIGN KEY constraint was imposed to link this column to the primary key in another table (or several tables). The purpose of foreign keys is to keep connected various tables of a database.

21. What is an index?

A special data structure related to a database table and used for storing its important parts and enabling faster data search and retrieval. Indexes are especially efficient for large databases, where they significantly enhance query performance.

22. What types of indexes do you know?

• Unique index: Doesn’t allow duplicates in a table column and hence helps maintain data integrity.
• Clustered index: Defines the physical order of records of a database table and performs data searching based on the key values. A table can have only one clustered index.
• Non-clustered index: Keeps the order of the table records that don’t match the physical order of the actual data on the disk. It means that the data is stored in one place and a non-clustered index – in another one. A table can have multiple non-clustered indexes.

23. What is a schema?

A collection of database structural elements such as tables, stored procedures, indexes, functions, and triggers. It shows the overall database architecture, specifies the relationships between various objects of a database, and defines different access permissions for them.

24. What is a SQL comment?

A human-readable clarification of what a particular piece of code does. SQL code comments can be single-line (preceded by a double dash –) or span over multiple lines (as follows: /comment_text/). When the SQL engine runs, it ignores code comments. The purpose of adding SQL code comments is to make the code more comprehensive for those people who will read it in the future.

25. What is a SQL operator?

A reserved character, a combination of characters, or a keyword used in SQL queries to perform a specific operation. SQL operators are commonly used with the WHERE clause to set a condition (or conditions) for filtering the data.

26. What types of SQL operators do you know?

• Arithmetic (+, -, *, /, etc.)
• Comparison (>, <, =, >=, etc.)
• Compound (+=, -=, *=, /=, etc.)
• Logical (AND, OR, NOT, BETWEEN, etc.)
• String (%, _, +, ^, etc.)
• Set (UNION, UNION ALL, INTERSECT, and MINUS (or EXCEPT))

27. What is an alias?

A temporary name given to a table (or a column in a table) while executing a certain SQL query. Aliases are used to improve the code readability and make the code more compact. An alias is introduced with the AS keyword:

1
2

SELECT col_1 AS column
FROM table_name;

28. What is a clause?

A condition imposed on a SQL query to filter the data to obtain the desired result. Some examples are WHERE, LIMIT, HAVING, LIKE, AND, OR, ORDER BY, etc.

29. What are some common statements used with the SELECT query?

The most common ones are FROM, GROUP BY, JOIN, WHERE, ORDER BY, LIMIT, and HAVING.

30. How to create a table?

Using the CREATE TABLE statement. For example, to create a table with three columns of predefined datatypes, we apply the following syntax:

1
2
3
4

CREATE TABLE table_name 
(col_1 datatype,
col_2 datatype,
col_3 datatype);

31. How to update a table?

Using the UPDATE statement. The syntax is:

1
2
3

UPDATE table_name
SET col_1 = value_1, col_2 = value_2
WHERE condition;

32. How to delete a table from a database?

Using the DROP TABLE statement. The syntax is:

1

DROP TABLE table_name;

33. How to get the count of records in a table?

Using the COUNT() aggregate function with the asterisk passed as its argument:

1

SELECT COUNT(*) FROM table_name;

34. How to sort records in a table?

Using the ORDER BY statement:

1
2

SELECT * FROM table_name
ORDER BY col_1;

We can specify that we need a descending order using the DESC keyword. Otherwise, the order will be ascending by default. Also, we can sort by more than one column and specify for each one, ascending or descending order separately. For example:

1
2

SELECT * FROM table_name
ORDER BY col_1 DESC, col_3, col_6 DESC;

35. How to select all columns from a table?

Using the asterisk * with the SELECT statement. The syntax is:

1

SELECT * FROM table_name;

36. How to select common records from two tables?

Using the INTERSECT statement:

1
2
3

SELECT * FROM table_1
INTERSECT
SELECT * FROM table_1;

37. What is the DISTINCT statement and how do you use it?

This statement is used with the SELECT statement to filter out duplicates and return only unique values from a column of a table. The syntax is:

1
2

SELECT DISTINCT col_1
FROM table_name;

38. What are entities?

An entity is a real-world object, creature, place, or phenomenon for which the data can be gathered and stored in a database table. Each entity corresponds to a row in a table, while the table’s columns describe its properties. Some examples of entities are bank transactions, students in a school, cars sold, etc.

39. What are relationships?

Relationships are the connections and correlations between entities, basically meaning how two or more tables of a database are related to one another. For example, we can find an ID of the same client in a table on sales data and in a customer table.

40. What is NULL value? How is it different from zero or a blank space?

A NULL value indicates the absence of data for a certain cell of a table. Instead, zero is a valid numeric value, and an empty string is a legal string of zero length.

Database, Interview, SQL — Nov 8, 2024