The 99 Top SQL Interview Questions and Answers for Beginners & Intermediate Practitioners

By /

Table of Contents

Top SQL Interview Questions and Answers


In the realm of data management and analysis, SQL (Structured Query Language) is a fundamental skill sought after by employers across various industries. Whether you’re just starting your journey in SQL or are looking to brush up on your skills, having a comprehensive understanding of common SQL interview questions and their answers can be invaluable. In this guide, we’ll cover 99 top SQL interview questions tailored for beginners and intermediate practitioners, along with detailed answers to help you ace your next SQL interview.

  1. What is SQL?
    SQL, or Structured Query Language, is a domain-specific programming language used for managing and manipulating relational databases. It provides a standardized way to interact with databases, allowing users to perform tasks such as querying data, inserting, updating, and deleting records, and managing database schema.
  2. What are the different types of SQL commands?
    SQL commands can be categorized into four main types:

Data Definition Language (DDL):

Data Definition Language (DDL) is a subset of SQL used to define, modify, and delete database schema objects. These objects include tables, views, indexes, sequences, synonyms, and more. DDL commands enable database administrators and developers to manage the structure of a database, defining how data is stored, organized, and accessed. Let’s delve deeper into some common DDL commands and their functionalities:

CREATE: The CREATE command is used to create new database objects, such as tables, views, indexes, or sequences. For example:

CREATE TABLE employees (

emp_id INT PRIMARY KEY,

emp_name VARCHAR(50),

emp_salary DECIMAL(10, 2)

);

This SQL statement creates a new table named employees with columns for employee ID, name, and salary.

ALTER: The ALTER command is used to modify existing database objects, such as tables, views, or constraints. It can be used to add, modify, or drop columns, constraints, or other object attributes. For example:

ALTER TABLE employees

ADD COLUMN emp_department VARCHAR(50);

This SQL statement adds a new column named emp_department to the employees table.

DROP: The DROP command is used to delete existing database objects, such as tables, views, indexes, or constraints. It permanently removes the specified object from the database. For example:

DROP TABLE employees;

This SQL statement deletes the employees table from the database.

TRUNCATE: The TRUNCATE command is used to remove all rows from a table, effectively resetting the table to its initial state. Unlike the DELETE statement, which removes rows one by one and generates transaction logs, TRUNCATE is faster and does not generate logs. For example:

TRUNCATE TABLE employees;

This SQL statement removes all rows from the employees table.

COMMENT: The COMMENT command is used to add comments or descriptions to database objects, such as tables, columns, or constraints. These comments can provide additional information about the purpose or usage of the object. For example:

COMMENT ON TABLE employees

IS ‘Table containing employee information’;

This SQL statement adds a comment to the employees table describing its purpose.

RENAME: The RENAME command is used to rename existing database objects, such as tables, views, columns, or constraints. It allows you to change the name of an object without altering its structure or contents. For example:

ALTER TABLE employees

RENAME COLUMN emp_name TO employee_name;

This SQL statement renames the emp_name column in the employees table to employee_name.

These are some of the common DDL commands used in SQL to define, modify, and delete database objects. Understanding these commands is essential for database administrators and developers to effectively manage the structure and integrity of a database.

Data Manipulation Language (DML):

Data Manipulation Language (DML) is a subset of SQL (Structured Query Language) used to retrieve, insert, update, and delete data in a database. These commands allow users to manipulate the data stored in tables, enabling them to perform various operations such as querying data, adding new records, modifying existing records, and removing unwanted data. Let’s explore some common DML commands and their functionalities:

SELECT: The SELECT statement is used to retrieve data from one or more tables in a database. It allows users to specify the columns they want to retrieve and apply filtering, sorting, and aggregation functions to the data. For example:

SELECT column1, column2

FROM table_name

WHERE condition;

This SQL statement retrieves values from column1 and column2 in the table_name table based on the specified condition.

INSERT: The INSERT statement is used to add new records or rows to a table in a database. It allows users to specify the values to be inserted into each column of the table. For example:

INSERT INTO table_name (column1, column2)

VALUES (value1, value2);

This SQL statement inserts a new record into the table_name table with values value1 and value2 in column1 and column2, respectively.

UPDATE: The UPDATE statement is used to modify existing records or rows in a table in a database. It allows users to specify the columns to be updated and the new values for those columns, along with optional filtering criteria to identify the rows to be updated. For example:

UPDATE table_name

SET column1 = new_value1, column2 = new_value2

WHERE condition;

This SQL statement updates the values of column1 and column2 in the table_name table with new_value1 and new_value2, respectively, for rows that satisfy the specified condition.

DELETE: The DELETE statement is used to remove records or rows from a table in a database. It allows users to specify optional filtering criteria to identify the rows to be deleted. For example:

DELETE FROM table_name

WHERE condition;

This SQL statement deletes rows from the table_name table that satisfy the specified condition.

MERGE: The MERGE statement, also known as “upsert,” is used to perform an “upsert” operation, which means inserting new rows into a table or updating existing rows if they already exist based on a specified condition. For example:

MERGE INTO target_table

USING source_table

ON condition

WHEN MATCHED THEN

UPDATE SET column1 = new_value1, column2 = new_value2

WHEN NOT MATCHED THEN

INSERT (column1, column2) VALUES (value1, value2);

This SQL statement merges data from source_table into target_table, updating existing rows or inserting new rows based on the specified condition.

These are some of the common DML commands used in SQL to retrieve, insert, update, and delete data in a database. Understanding these commands is essential for database administrators, developers, and data analysts to manipulate data effectively and perform various data operations in a database.

Data Control Language (DCL):

Data Control Language (DCL) is a subset of SQL (Structured Query Language) used to control access to data within a database. These commands enable database administrators to manage user privileges, permissions, and security settings, determining who can access, modify, or manipulate data in the database. Let’s explore some common DCL commands and their functionalities:

  1. GRANT: The GRANT command is used to give specific privileges or permissions to users or roles in a database. It allows administrators to grant various levels of access to database objects such as tables, views, procedures, or sequences. For example:
   GRANT SELECT, INSERT ON table_name TO user_name;

This SQL statement grants the SELECT and INSERT privileges on the table_name table to the user_name user.

  1. REVOKE: The REVOKE command is used to revoke or remove previously granted privileges or permissions from users or roles in a database. It allows administrators to restrict access to database objects that were previously granted. For example:
   REVOKE INSERT ON table_name FROM user_name;

This SQL statement revokes the INSERT privilege on the table_name table from the user_name user.

These are the two primary DCL commands used in SQL to control access to data within a database. By using GRANT and REVOKE commands effectively, database administrators can ensure that only authorized users have access to sensitive data and that access privileges are aligned with the security requirements of the organization. Understanding DCL commands is essential for maintaining data security and integrity in a database environment.

Transaction Control Language (TCL):

Transaction Control Language (TCL) is a subset of SQL (Structured Query Language) used to manage transactions within a database. Transactions are sequences of database operations that are treated as a single unit of work, ensuring data consistency and integrity. TCL commands enable users to control the execution and outcome of transactions, allowing them to commit changes, rollback transactions, and set savepoints for partial rollback. Let’s explore some common TCL commands and their functionalities:

  1. COMMIT: The COMMIT command is used to permanently save the changes made during the current transaction to the database. Once a transaction is committed, the changes become permanent and visible to other users. For example:
   COMMIT;

This SQL statement commits the changes made during the current transaction to the database.

  1. ROLLBACK: The ROLLBACK command is used to undo the changes made during the current transaction and restore the database to its state before the transaction began. It effectively cancels the transaction and discards any changes made. For example:
   ROLLBACK;

This SQL statement rolls back the changes made during the current transaction and restores the database to its previous state.

  1. SAVEPOINT: The SAVEPOINT command is used to set a named savepoint within a transaction, allowing users to create intermediate points in the transaction from which they can later rollback. It enables partial rollback of a transaction without rolling back the entire transaction. For example:
   SAVEPOINT savepoint_name;

This SQL statement sets a savepoint with the specified name within the current transaction.

  1. RELEASE SAVEPOINT: The RELEASE SAVEPOINT command is used to remove a previously set savepoint within a transaction. It indicates that the changes made up to the savepoint can no longer be rolled back. For example:
   RELEASE SAVEPOINT savepoint_name;

This SQL statement releases the specified savepoint within the current transaction.

  1. ROLLBACK TO SAVEPOINT: The ROLLBACK TO SAVEPOINT command is used to rollback a transaction to a previously set savepoint, discarding any changes made after the savepoint. It allows users to undo part of a transaction while preserving changes made before the savepoint. For example:
   ROLLBACK TO SAVEPOINT savepoint_name;

This SQL statement rolls back the transaction to the specified savepoint, undoing changes made after the savepoint was set.

These are some of the common TCL commands used in SQL to manage transactions within a database. Understanding TCL commands is essential for ensuring data consistency, integrity, and reliability in database operations.

  1. What is a primary key?
    A primary key is a column or a combination of columns that uniquely identifies each row in a table. It ensures data integrity and enforces entity integrity by preventing duplicate or null values in the key columns.
  2. What is a foreign key?
    A foreign key is a column or a set of columns in a table that establishes a relationship with a primary key or a unique key in another table. It enforces referential integrity by ensuring that values in the foreign key column(s) match values in the referenced primary key column(s) or unique key column(s).
  3. What is the difference between INNER JOIN and OUTER JOIN?
    INNER JOIN returns only the rows from both tables that satisfy the join condition, discarding rows where no match is found in either table. OUTER JOIN, on the other hand, returns all rows from both tables, pairing rows from one table with matching rows from the other table and filling in missing values with NULLs where no match is found.
  4. What is the difference between WHERE and HAVING clauses?
    The WHERE clause is used to filter rows based on a specified condition in a SELECT, INSERT, UPDATE, or DELETE statement. It is applied before the result set is grouped or aggregated. The HAVING clause, on the other hand, is used to filter grouped rows based on a specified condition in a SELECT statement that includes a GROUP BY clause. It is applied after the result set is grouped.
  5. What is a subquery?
    A subquery, also known as an inner query or nested query, is a query nested within another query. It can be used to return a set of rows as a single value, a list of values, or a table. Subqueries can be used in SELECT, INSERT, UPDATE, and DELETE statements to filter, manipulate, or aggregate data based on the results of the inner query.
  6. What is the difference between UNION and UNION ALL?
    UNION and UNION ALL are used to combine the results of two or more SELECT statements into a single result set. The main difference between them is that UNION removes duplicate rows from the result set, while UNION ALL retains all rows, including duplicates. As a result, UNION ALL is generally faster than UNION because it does not require the additional step of eliminating duplicates.
  7. What is normalization?
    Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves breaking down large tables into smaller, related tables and defining relationships between them. The goal of normalization is to eliminate data anomalies such as update anomalies, insertion anomalies, and deletion anomalies, ensuring that each piece of data is stored only once and is logically organized.
  8. What is denormalization?
    Denormalization is the process of intentionally introducing redundancy into a database design to improve performance or simplify query processing. It involves adding redundant data or duplicating data across multiple tables to eliminate the need for complex joins or aggregations. While denormalization can improve query performance by reducing the number of joins or simplifying query logic, it may also increase storage requirements and introduce the risk of data inconsistency.
  9. What is an index?
    An index is a database object used to speed up the retrieval of rows from a table by providing fast access to data based on the values of one or more columns. It works like an index in a book, allowing the database engine to quickly locate rows that match a given search criteria. Indexes can improve query performance but may incur overhead during data modification operations such as INSERT, UPDATE, and DELETE.
  10. What is a stored procedure?
    A stored procedure is a precompiled set of SQL statements that are stored in the database and can be executed on demand. It allows for code reusability, modularity, and security by encapsulating complex logic or business rules into a single unit. Stored procedures can accept input parameters, perform operations such as querying or modifying data, and return output parameters or result sets.
  11. What is a trigger?
    A trigger is a database object that automatically executes a set of SQL statements in response to certain events, such as INSERT, UPDATE, or DELETE operations on a table. Triggers are used to enforce data integrity constraints, audit changes to data, or perform custom business logic. They can be defined to execute either before or after the triggering event occurs and can be set to fire for each row affected by the event or for the entire operation.
  12. What is a view?
    A view is a virtual table that is based on the result set of a SELECT query. It does not store data itself but provides a way to present data from one or more tables in a customized or aggregated form. Views can be used to simplify complex queries, enforce security policies, or provide a consistent interface to underlying data. They are particularly useful for hiding sensitive data or abstracting complex joins from end users.
  13. What is ACID in the context of database transactions?
    ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability, which are the four properties that guarantee the reliability and integrity of database transactions:
  • Atomicity ensures that transactions are all or nothing, meaning that either all the operations within a transaction are executed successfully, or none of them are.
  • Consistency ensures that transactions bring the database from one valid state to another, preserving data integrity and enforcing constraints.
  • Isolation ensures that the execution of transactions is isolated from each other, preventing
Scroll to Top