SQL Interview Questions & Answers. SQL Interview Preparation

SQL Basics:
- What is SQL and what is it used for?
  - SQL (Structured Query Language) is a standard language used to manage relational databases. It is used to create, modify, and query databases.
- What are the basic commands in SQL?
  - The basic commands in SQL include SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, INSERT INTO, UPDATE, DELETE, and CREATE TABLE.
- What is a database management system (DBMS)?
  - A database management system (DBMS) is a software system that interacts with the user, other applications, and the database itself to capture and analyze the data.
- What is a relational database?
  - A relational database is a type of database that organizes data into one or more tables with columns and rows and uses relationships between the tables to extract data in a meaningful way.
- What is a database table and what are its components?
  - A database table is a collection of data stored in a structured format, with rows and columns. The components of a database table are the columns (which define the data types of the stored data) and the rows (which contain the actual data).
- What is a database query?
  - A database query is a request made to a database for specific information. Queries can be made using SQL commands.
- What is the difference between SQL and NoSQL?
  - SQL (Structured Query Language) is used for relational databases, which organize data into tables with columns and rows. NoSQL (Not Only SQL) is used for non-relational databases, which can store data in a variety of formats, including document-based, key-value, graph-based, or columnar.
SQL Data Types:
- What are the different data types in SQL?
  - The different data types in SQL include CHAR, VARCHAR, INT, BIGINT, NUMERIC, DECIMAL, DATE, and DATETIME.
- What is the difference between CHAR and VARCHAR data types?
  - CHAR is a fixed-length data type that takes up a specified number of bytes, regardless of the amount of data stored. VARCHAR is a variable-length data type that takes up only as much space as needed to store the data.
- What is the difference between INT and BIGINT data types?
  - INT is a 4-byte integer data type that can store values from -2147483648 to 2147483647. BIGINT is an 8-byte integer data type that can store values from -9223372036854775808 to 9223372036854775807.
- What is the difference between NUMERIC and DECIMAL data types?
  - NUMERIC and DECIMAL are both used to store decimal values, but NUMERIC has a higher precision and a smaller storage size.
- What is the difference between DATE and DATETIME data types?
  - The DATE data type stores only the date, while the DATETIME data type stores both the date and time.
SQL Operators:

What are the different types of SQL operators?
- The different types of SQL operators include arithmetic operators, comparison operators, logical operators, and set operators.
What is the difference between the WHERE and HAVING clauses?

The WHERE clause is used to filter rows from a table based on a specified condition before the data is grouped and summarized. The HAVING clause is used to filter groups from the result set based on a specified condition after the data has been grouped and summarized.

What is the difference between INNER JOIN and OUTER JOIN?
- An INNER JOIN returns only the rows that have matching values in both tables. An OUTER JOIN returns all the rows from one table and the matching rows from the other table. If there is no match, the non-matching rows will contain NULL values.
What is a CROSS JOIN?
- A CROSS JOIN returns the Cartesian product of the two tables, meaning it returns all possible combinations of rows from both tables.
What is a subquery?

A subquery is a query nested inside another query. The subquery runs first and its result is used by the outer query.

SQL Functions:
- What are SQL functions?
  - SQL functions are predefined, reusable blocks of code that perform specific tasks, such as calculating aggregate values, converting data types, or manipulating strings.
- What is the purpose of the AVG function in SQL?
  - The AVG function in SQL calculates the average of a set of values.
- What is the purpose of the COUNT function in SQL?
  - The COUNT function in SQL returns the number of rows in a table or the number of rows that match a specified condition.
- What is the purpose of the MAX function in SQL?
  - The MAX function in SQL returns the maximum value in a set of values.
- What is the purpose of the MIN function in SQL?
  - The MIN function in SQL returns the minimum value in a set of values.
- What is the purpose of the SUM function in SQL?
  - The SUM function in SQL calculates the sum of a set of values.
SQL Constraints:

What are SQL constraints?
- SQL constraints are used to enforce rules on data in a table. They help ensure data integrity and accuracy.
What is the purpose of the PRIMARY KEY constraint in SQL?
- The PRIMARY KEY constraint in SQL is used to uniquely identify each row in a table. It ensures that the data in the column(s) designated as the primary key is unique and cannot be repeated.
What is the purpose of the FOREIGN KEY constraint in SQL?
- The FOREIGN KEY constraint in SQL is used to establish a relationship between two tables. It enforces referential integrity, meaning it ensures that data in the foreign key column(s) matches the data in the primary key column(s) of another table.
What is the purpose of the NOT NULL constraint in SQL?
- The NOT NULL constraint in SQL is used to ensure that a column cannot contain a NULL value.
What is the purpose of the UNIQUE constraint in SQL?

The UNIQUE constraint in SQL is used to ensure that data in a column is unique and cannot be repeated.

SQL Indexes:
- What are SQL indexes?
  - SQL indexes are structures that improve the speed of data retrieval operations on a database table. They provide a fast and efficient way to look up rows based on the values in one or more columns.
- What is the purpose of an index in SQL?
  - The purpose of an index in SQL is to improve the performance of data retrieval operations by reducing the amount of data that needs to be scanned. An index allows the database management system to quickly find the rows that match a specified condition, without having to scan the entire table.
- What is the difference between a clustered index and a non-clustered index in SQL?
  - A clustered index determines the physical order of the data in a table, meaning that the rows in the table are stored in the order of the clustered index. A non-clustered index provides a quick way to look up rows based on the values in one or more columns, but the rows in the table are not stored in any particular order.
SQL Transactions:

What are SQL transactions?
- SQL transactions are a sequence of one or more SQL statements that are executed as a single unit of work. They are used to ensure the integrity and consistency of the data in a database, by allowing multiple changes to be made atomically (either all of them are applied or none of them are applied).
What is the purpose of the COMMIT statement in SQL?
- The COMMIT statement in SQL is used to end a transaction and make the changes permanent. Once a transaction is committed, the changes cannot be rolled back.
What is the purpose of the ROLLBACK statement in SQL?
- The ROLLBACK statement in SQL is used to undo the changes made during a transaction. If a transaction encounters an error or violates a constraint, a ROLLBACK statement can be used to undo the changes made during the transaction, preserving the integrity of the data in the database.
What is the purpose of the SAVEPOINT statement in SQL?

The SAVEPOINT statement in SQL is used to define a point within a transaction where the changes can be rolled back, without undoing the entire transaction. A savepoint allows you to divide a transaction into multiple smaller transactions, making it easier to manage the changes being made and resolve issues if they arise.

SQL Joins:
- What are SQL Joins?
  - SQL Joins are used to combine rows from two or more tables based on a related column between them. They allow you to retrieve data from multiple tables as if the data were contained in a single table.
- What is the difference between an inner join and an outer join in SQL?
  - An inner join returns only the rows that have matching values in both tables being joined. An outer join, on the other hand, returns all the rows from one table and the matching rows from the other table. If there are no matching rows in one of the tables, NULL values are returned for the columns in that table. There are three types of outer joins: LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.
- What is a self-join in SQL?
  - A self-join in SQL is a regular join, but the table is joined with itself. Self-joins are useful for comparing rows within a single table.
SQL Subqueries:

What are SQL subqueries?
- SQL subqueries are queries nested inside another query. They are used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.
What is the purpose of a subquery in SQL?
- The purpose of a subquery in SQL is to return data that will be used as a condition in the main query. Subqueries allow you to break down a complex query into smaller, more manageable parts, making it easier to write and maintain complex queries.
What is the difference between a correlated and a non-correlated subquery in SQL?

A correlated subquery in SQL is a subquery that is dependent on the outer query. It uses values from the outer query in its own condition. A non-correlated subquery is a subquery that can be executed independently of the outer query and returns a fixed result that is used by the outer query.

SQL Stored Procedures:

What are SQL stored procedures?
- SQL stored procedures are precompiled database objects that contain one or more SQL statements. They are stored in the database and can be executed by calling the procedure name.
What is the purpose of stored procedures in SQL?
- The purpose of stored procedures in SQL is to encapsulate a set of SQL statements into a single, reusable object. Stored procedures allow you to encapsulate complex logic into a single, reusable object, making it easier to manage and maintain your database code. They can also improve performance by allowing the database management system to reuse execution plans for commonly executed statements.
What are the benefits of using stored procedures in SQL?
- The benefits of using stored procedures in SQL include improved performance, increased security, and reduced database server load. Stored procedures can also make it easier to manage and maintain your database code, as well as encapsulate complex business logic in a single, reusable object.

SQL Triggers:

What are SQL triggers?
- SQL triggers are database objects that are automatically executed in response to certain events, such as an INSERT, UPDATE, or DELETE statement.
What is the purpose of triggers in SQL?
- The purpose of triggers in SQL is to automate certain tasks, such as enforcing business rules or auditing changes to the data in a table. Triggers can be used to automatically update related tables, enforce referential integrity constraints, or perform complex calculations in response to changes in the data.
What is the difference between a before and after trigger in SQL?

A trigger in SQL is executed before the data is changed, while a trigger is executed after the data has been changed. Before triggers are used to validate the data before it is changed, while after triggers are used to perform additional actions after the data has been changed, such as updating related tables or logging the changes.

SQL Transactions:

What are SQL transactions?
- SQL transactions are a sequence of one or more SQL statements that are executed as a single unit of work. If any of the statements fail, the entire transaction is rolled back, and the database is left in its original state.
What is the purpose of transactions in SQL?
- The purpose of transactions in SQL is to ensure data integrity and consistency. Transactions allow you to make multiple changes to the database and either commit all the changes or roll them back if any errors occur. This makes it easier to ensure that the database remains in a consistent state even if errors occur during the execution of a complex set of SQL statements.
What is the difference between a commit and a rollback in SQL?

A commit in SQL is used to make permanent changes made during a transaction. A rollback in SQL is used to undo the changes made during a transaction. If any errors occur during the execution of a transaction, a rollback will be executed to restore the database to its original state.

SQL Cursors:

What are SQL cursors?
- SQL cursors are database objects that allow you to retrieve and manipulate data one row at a time. Cursors are used to retrieve data from the database and process each row individually.
What is the purpose of cursors in SQL?
- The purpose of cursors in SQL is to allow you to process data one row at a time, rather than retrieving all the data at once. Cursors are often used when working with large amounts of data or when the data requires complex processing.
What are the disadvantages of using cursors in SQL?

The disadvantages of using cursors in SQL include decreased performance, increased complexity, and increased memory usage. Cursors can be slow and resource-intensive, and can also make your code more difficult to understand and maintain.

SQL Views:

What are SQL views?
- SQL views are virtual tables that provide a customized view of data from one or more tables. A view does not physically store data but instead retrieves data from the underlying tables when the view is queried.
What is the purpose of views in SQL?
- The purpose of views in SQL is to simplify complex queries and provide a custom view of data to users. Views can be used to hide complex SQL logic, limit access to sensitive data, and provide a consistent view of data across multiple applications and users.
What are the advantages of using views in SQL?
- The advantages of using views in SQL include increased security, improved performance, simplified data access, and improved data consistency. Views can be used to enforce security restrictions, optimize queries, and provide a consistent view of data to users.