SQL Interview Questions and Answers

100+ Interview Questions for Freshers 1 -2 Experienced Candidates

SQL Interview Questions and Answers

1. Define SQL?

SQL, or Structured Query Language, is a programming language utilized for interacting with relational database management systems (RDBMS). It encompasses operations such as fetching, updating, inserting, and removing data from tables.

2 Define SQL dialects with examples?

SQL dialects refer to various versions of SQL, both free and paid, which are also known as flavors. Examples include Microsoft SQL Server, PostgreSQL, MySQL, SQLite, T-SQL, Oracle, and MongoDB. Despite minor differences, these dialects share a similar syntax.

3. Define the main applications of SQL?

SQL is used to create, delete, and update tables in a database, access and modify data in a table, retrieve and summarize information from tables, and add or remove rows or columns. It enables diverse ways of querying a database and integrates seamlessly with programming languages like Python and R.

4. Define an SQL statement with examples?

An SQL statement, also known as an SQL command, is a string of characters interpreted by the SQL engine as a legal command and executed accordingly. Examples include SELECT, CREATE, DELETE, DROP, REVOKE, among others.

5. What are the types of SQL commands/SQL subsets)?

DDL (Data Definition Language)
DML (Data Manipulation Language)
DCL (Data Control Language)
TCL (Transaction Control Language)
DQL (Data Query Language)

6. Provide examples of typical SQL commands for each category.

DDL: CREATE, ALTER TABLE, DROP, TRUNCATE, ADD COLUMN
DML: UPDATE, DELETE, INSERT
DCL: GRANT, REVOKE
TCL: COMMIT, SET TRANSACTION, ROLLBACK, SAVEPOINT
DQL: SELECT

7. Define a database?

A database is a structured storage space where data is organized in tables, allowing for easy retrieval, manipulation, and summarization of information.

8. What does DBMS entail, and what categories of DBMS are you familiar with?

DBMS, or Database Management System, is software used to perform operations on stored data. Types of DBMS include relational, hierarchical, network, graph, or object-oriented, depending on how data is organized.

9. Define RDBMS and provide examples of RDBMS systems.

RDBMS (Relational Database Management System) is a type of DBMS commonly used for data stored in multiple related tables. Examples include MySQL, PostgreSQL, Oracle, and MariaDB.

10. Explain tables and fields in SQL.

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

11. Define an SQL query and enumerate different types of queries.

An SQL query is a piece of code written to access or modify data in a database. Types of queries include select queries (retrieve data) and action queries (modify data).

12. What is a subquery in SQL?

A subquery, or inner query, is a query placed inside another query, providing results to the outer query.

13. What are the various types of SQL subqueries.

Single-row
Multi-row
Multi-column
Correlated
Nested

14. Define a constraint and elucidate the purpose of using constraints.

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

15. List and explain various SQL constraints.

DEFAULT
UNIQUE
NOT NULL
PRIMARY KEY
FOREIGN KEY

16. Define a join in SQL.

A join is a clause used to combine and retrieve records from two or more tables based on the relationship between their columns.

17. Identify different types of joins in SQL.

(INNER) JOIN
LEFT (OUTER) JOIN
RIGHT (OUTER) JOIN
FULL (OUTER) JOIN

18. What is a primary key?

A primary key is a column (or columns) with the PRIMARY KEY constraint, ensuring unique and non-null values, uniquely identifying each record in a table.

19. Define a unique key.

A unique key is a column (or columns) with the UNIQUE constraint, ensuring unique values, including one NULL value.

20. Explain the concept of a foreign key.

A foreign key is a column (or columns) with the FOREIGN KEY constraint, linking to the primary key in another table, establishing relationships between tables in a database.

21. Define an index in the context of a database?

An index is a specialized data structure associated with a database table, designed to store essential components of the table and optimize the speed of data retrieval and searches. Indexes prove particularly effective in large databases, significantly improving query performance.

22. Explain the types of indexes you are familiar with.

Unique index – prohibits duplicates in a table column, ensuring data integrity.
Clustered index – dictates the physical order of records in a database table and facilitates data searching based on key values. A table can have only one clustered index.
Non-clustered index – maintains the order of table records that does not align with the physical order of the actual data on disk. This implies that data is stored in one location and a non-clustered index in another. A table can have multiple non-clustered indexes.

23. Define what a schema is?

A schema is a collection of structural elements in a database, encompassing tables, stored procedures, indexes, functions, and triggers. It illustrates the overall architecture of a database, outlines relationships between various objects, and establishes diverse access permissions for these elements.

24. Explain the concept of a SQL comment.

A SQL comment is a human-readable annotation providing insight into the functionality of a specific piece of code. SQL code comments can be single-line (indicated by a double dash –) or span multiple lines (enclosed in /comment_text/). During execution, the SQL engine disregards code comments. Adding SQL code comments aims to enhance the code’s comprehensibility for future readers.

25. Define a SQL operator.

A SQL operator is a reserved character, a combination of characters, or a keyword employed in SQL queries to execute a specific operation. SQL operators are commonly utilized with the WHERE clause to establish conditions for filtering data.

26. Define the types of SQL operators you are familiar with?

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 does the term "alias" refer to?

An alias is a temporary designation assigned to a table or column during the execution of a specific SQL query. Utilized to enhance code readability and compactness. An alias is introduced with the AS keyword.

SELECT col_1 AS column

FROM table_name;

28. What does the term "clause" mean in SQL?

A clause is a condition applied to a SQL query to filter the data and achieve the desired outcome. Examples of clauses include WHERE, LIMIT, HAVING, LIKE, AND, OR, ORDER BY, and more.

29. What are some frequently used statements in conjunction with the SELECT query?

Common statements used with the SELECT query include FROM, GROUP BY, JOIN, WHERE, ORDER BY, LIMIT, and HAVING.

30. How can you create a table in SQL?

To create a table in SQL, you use the CREATE TABLE statement. For instance, to create a table with three columns of predefined datatypes, the following syntax is applied:

CREATE TABLE table_name (col_1 datatype,

col_2 datatype,

col_3 datatype);

31. How can a table be updated?

Using the UPDATE statement. The syntax is:

UPDATE table_name

SET col_1 = value_1, column_2 = value_2

WHERE condition;

32. How can a table be removed from a database?

Using the DROP TABLE statement. The syntax is: DROP TABLE table_name;.

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

Using the COUNT() aggregate function with the asterisk passed as its argument: SELECT COUNT(*) FROM table_name;.

34. How can records in a table be sorted?

Using the ORDER BY statement:

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:

SELECT * FROM table_name

ORDER BY col_1 DESC, col_3, col_6 DESC;

35. How to retrieve all columns from a table?

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

36. How to retrieve shared records from two tables?

Using the INTERSECT statement:

SELECT * FROM table_1

INTERSECT

SELECT * FROM table_1;

37 What is the DISTINCT statement, and how is it utilized?

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:

SELECT DISTINCT col_1

FROM table_name;

38. What does the term "entities" encompass explain with examples.

An entity is a tangible real-world object, living being, location, or observable phenomenon for which data can be collected and stored within a database table. Each entity corresponds to a row in a table, while the columns of the table describe its various properties. Examples of entities include bank transactions, students in a school, and cars sold.

39. Define relationships with examples.

Relationships represent the connections and associations between entities, indicating how two or more tables in a database are interrelated. For instance, an ID belonging to the same client can be found in both a sales data table and a customer table.

40. What does the NULL value signify, and how does it differ from zero or a blank space?

A NULL value indicates the absence of data for a specific cell in a table. In contrast, zero is a valid numeric value, and an empty string is a legitimate string with zero length.

Intermediate Interview Questions and Answers

1. What is a function in SQL, and what is the purpose of using functions?

A function in SQL is a database object that represents a collection of SQL statements designed for a specific task. It takes input parameters, performs calculations or other manipulations on them, and returns the result. Functions contribute to improved code readability and prevent the repetition of identical code segments.

2. What types of SQL functions are you familiar with?

There are two main types of SQL functions:

Aggregate functions – These operate on multiple records, usually grouped by columns of a table, and return a single value, often per group.
Scalar functions – These operate on individual values, producing a single result.
SQL functions can be further categorized as built-in (defined by the system) or user-defined (created by the user for their specific requirements).

3. What types of aggregate functions are you acquainted with?

AVG() – returns the average value
SUM() – returns the sum of values
MIN() – returns the minimum value
MAX() – returns the maximum value
COUNT() – returns the number of rows, including those with null values
FIRST() – returns the first value from a column
LAST()– returns the last value from a column

4. Which types of scalar functions are you familiar with?

LEN() (in other SQL flavors – LENGTH()) – returns the length of a string, including the blank spaces
UCASE() (in other SQL flavors – UPPER()) – returns a string converted to the upper case
LCASE() (in other SQL flavors – LOWER()) – returns a string converted to the lower case
INITCAP() – returns a string converted to the title case (i.e., each word of the string starts from a capital letter)
MID() (in other SQL flavors – SUBSTR()) – extracts a substring from a string
ROUND() – returns the numerical value rounded to a specified number of decimals
NOW() – returns the current date and time

5. What are case manipulation functions, and can you provide examples of their usage?

Case manipulation functions represent a subset of character functions, and they’re used to change the case of the text data. With these functions, we can convert the data into the upper, lower, or title case.

UCASE() (in other SQL flavors – UPPER()) – returns a string converted to the upper case
- LCASE() (in other SQL flavors – LOWER()) – returns a string converted to the lower case
- INITCAP() – returns a string converted to the title case (i.e., each word of the string starts from a capital letter)

6. What are examples of character manipulation functions, and how are they utilized?

Character manipulation functions form a subset of character functions, designed to alter text data.

- CONCAT() – joins two or more string values appending the second string to the end of the first one
- SUBSTR() – returns a part of a string satisfying the provided start and end points
- LENGTH() (in other SQL flavors – LEN()) – returns the length of a string, including the blank spaces
- REPLACE() – replaces all occurrences of a defined substring in a provided string with another substring
- INSTR() – returns the numeric position of a defined substring in a provided string
- LPAD() and RPAD() – return the padding of the left-side/right-side character for right-justified/left-justified value
- TRIM() – removes all the defined characters, as well as white spaces, from the left, right, or both ends of a provided string

7. What distinguishes local variables from global variables?

Local variables are confined to the function where they are declared, while global variables, declared outside any function, exist in fixed memory structures and can be accessed throughout the entire program.

8. What is the default data ordering governed by the ORDER BY statement, and how can you alter it?

By default, the order is ascending. To change it to descending, we need to add the DESC keyword as follows:

SELECT * FROM table_name

ORDER BY col_1 DESC;

9. Explain the types of set operators do you know?

UNION – returns the records obtained by at least one of two queries (excluding duplicates)
UNION ALL – returns the records obtained by at least one of two queries (including duplicates)
INTERSECT – returns the records obtained by both queries
EXCEPT (called MINUS in MySQL and Oracle) – returns only the records obtained by the first query but not the second one

10. Which operator is employed in the query to facilitate pattern matching?

The LIKE operator is utilized, along with the % and _ wildcards. The % wildcard signifies any number of characters, including zero, whereas _ denotes precisely one character.

11. What sets apart a primary key from a unique key?

Both keys serve to ensure uniqueness in a table column, but a primary key uniquely identifies each record in the table, whereas a unique key prevents duplicates in the column.

12. How would you define a composite primary key?

A composite primary key is formed from multiple columns, constituting the primary key of a table.

13. What is the sequence of appearance for common statements in the SELECT query?

SELECT – FROM – JOIN – ON – WHERE – GROUP BY – HAVING – ORDER BY – LIMIT

14. In which order does the interpreter execute these common statements in the SELECT query?

FROM – JOIN – ON – WHERE – GROUP BY – HAVING – SELECT – ORDER BY – LIMIT

15. What is A view, and why use it?

A view is a virtual table containing a subset of data retrieved from one or more database tables or other views. Views occupy minimal space, simplify complex queries, restrict data access for security, enable data independence, and summarize data from multiple tables.

16. Can a view be created based on another view?

Yes, this is known as nested views. However, nesting multiple views should be avoided, as it can make the code challenging to read and debug.

17. Is it possible to use a view if the original table is deleted?

No, any views based on that table will become invalid after deleting the base table. Attempting to use such a view will result in an error message.

18. What types of SQL relationships are known?

One-to-one relationships mean each record in one table corresponds to only one record in another. One-to-many relationships imply each record in one table corresponds to several records in another. Many-to-many relationships signify each record in both tables corresponds to several records in another table.

19. What are the potential values of a BOOLEAN data field?

In certain SQL flavors like PostgreSQL, BOOLEAN is a distinct data type with values TRUE, FALSE, or NULL. In other flavors like Microsoft SQL Server, the BIT datatype is used, storing Boolean values as integers (1 for true, 0 for false).

20. What is normalization in SQL, and why is it used?

Normalization is a database design process that involves organizing and restructuring data to minimize redundancy, dependency, duplication, and inconsistency. It enhances data integrity, allows for more efficient data access and security control, and provides greater query flexibility by introducing more tables into the database.

21. What is denormalization in SQL, and why is it used?

Denormalization is the opposite of normalization, introducing data redundancy and combining data from multiple tables. It optimizes database performance when read operations are more critical than write operations, helping avoid complex joins and reducing query running time.

22. How does renaming a column differ from giving it an alias?

Renaming a column involves permanently changing its actual name in the original table. Giving an alias to a column means assigning it a temporary name during the execution of an SQL query to enhance code readability and compactness.

23. What distinguishes nested and correlated subqueries?

A correlated subquery is nested within a larger (outer) query and relies on values from the outer query for execution. In contrast, a non-correlated subquery operates independently of the outer query, without depending on its data.

24. Define the difference between clustered and non-clustered indexes?

A clustered index defines the physical order of records in a table and facilitates data searching based on key values. On the other hand, a non-clustered index maintains the order of records that doesn’t match the physical order of the data on the disk. A table can have only one clustered index but multiple non-clustered indexes.

25. Define the CASE() function?

To implement if-then-else logic in SQL, this function evaluates conditions in the WHEN clauses sequentially. It returns the value from the corresponding THEN clause when the first condition is satisfied. If none of the conditions is met, the function returns the value from the ELSE clause if provided; otherwise, it returns NULL. The syntax is as follows:

CASE

WHEN condition_1 THEN value_1

WHEN condition_2 THEN value_2

WHEN condition_3 THEN value_3

…

ELSE value

END;

26. Define the difference between the DELETE and TRUNCATE statements?

The DELETE command, a reversible DML (Data Manipulation Language) operation, removes one or more rows from a table based on conditions specified in the WHERE clause. In contrast, TRUNCATE is an irreversible DDL (Data Definition Language) command that deletes all rows from a table. DELETE tends to be slower than TRUNCATE. Additionally, the TRUNCATE statement cannot be used on a table containing a foreign key.

27. Define the difference between the DROP and TRUNCATE statements?

DROP eradicates a table entirely from the database, encompassing the table structure, associated constraints, relationships with other tables, and access privileges. Conversely, TRUNCATE removes all rows from a table without impacting the table structure and constraints. DROP operates more slowly than TRUNCATE. Both are irreversible DDL (Data Definition Language) commands.

28. Define the difference between the HAVING and WHERE statements?

The initial one operates on aggregated data after grouping, whereas the second one assesses each row individually. When both statements are included in a query, they follow this order: WHERE – GROUP BY – HAVING. The SQL engine also interprets them in the same sequence.

29. How to add a record to a table?

Using the INSERT INTO statement in combination with VALUES. The syntax is:

INSERT INTO table_name

VALUES (value_1, value_2, …);

30. How can you remove a record from a table?

Using the DELETE statement. The syntax is:

DELETE FROM table_name

WHERE condition;

In this way, we can also delete multiple records if they satisfy the provided condition.

31. How do you add a column to a table?

Using the ALTER TABLE statement in combination with ADD. The syntax is:

ALTER TABLE table_name

ADD column_name datatype;

32. How do you rename a column of a table?

Using the ALTER TABLE statement in combination with RENAME COLUMN … TO … The syntax is:

ALTER TABLE table_name

RENAME COLUMN old_column_name TO new_column_name;

33. How do you delete a column from a table?

Using the ALTER TABLE statement in combination with DROP COLUMN. The syntax is:

ALTER TABLE table_name

DROP COLUMN column_name;

34. How do you choose all records with even or odd values in a table?

You can achieve this by examining the remainder of the division by 2. In certain SQL versions, such as PostgreSQL and MySQL, you utilize the MOD function, while in others like Microsoft SQL Server and SQLite, you use the modulo operator (%). To select all even records using MOD:

SELECT * FROM table_name

WHERE MOD(ID_column, 2) = 0;

To select all even records using %:

SELECT * FROM table_name

WHERE ID_column % 2 = 0;

To select all odd records, the syntax is identical in both cases, only that we would use the inequality operator <> instead of =.

35. How can you avoid duplicate records when executing a query?

Using the DISTINCT statement in combination with SELECT or creating a unique key for that table.

36. How can you insert multiple rows into a table?

Using the INSERT INTO statement in combination with VALUES. The syntax is:

INSERT INTO table_name

VALUES (value_1, value_2, …),

(value_3, value_4, …),

(value_5, value_6, …),

…;

37. How do you identify the nth highest value in a column of a table?

Using the OFFSET clause. For example, to find the 6th highest value from a column, we would use the following syntax:

SELECT * FROM table_name

ORDER BY column_name DESC

LIMIT 1

OFFSET 5;

38. How can you retrieve values from a text column in a table that begin with a specific letter?

Using the LIKE operator in combination with the % and _ wildcards. For example, we need to find all surnames in a table that start with “A”. The query is:

SELECT * FROM table_name

WHERE surname LIKE ‘A_’;

Here, we assume that a surname must contain at least two letters. Without this assumption (meaning that a surname can be just A), the query is as follows:

SELECT * FROM table_name

WHERE surname LIKE ‘A%’;

39. How do you determine the last id in a table?

Using the MAX() function. Otherwise, in many SQL versions, we can use the following syntax:

SELECT id

FROM table_name

ORDER BY id DESC

LIMIT 1;

or in Microsoft SQL Server:

SELECT TOP 1 id

FROM table_name

ORDER BY id DESC

40. How can you choose random rows from a table?

By utilizing the RAND() function along with ORDER BY and LIMIT. In certain SQL dialects, like PostgreSQL, this function may be referred to as RANDOM(). For e.g.- the code below will fetch five random rows from a table in MySQL:

SELECT * FROM table_name

ORDER BY RAND()

LIMIT 5;