SQL Interview Questions

1. What is Database?

A database is an organized collection of data that is stored and managed on a computer. It is a system that allows data to be easily stored, accessed, and managed.

A database consists of one or more tables, which contain rows and columns of data. Each table is made up of fields or columns that define the types of data that can be stored in that table, and rows that represent individual instances of data.

Databases can be used to store a variety of types of information, including customer information, inventory data, financial data, and more. They are used in a wide range of applications, from websites and mobile apps to enterprise software and scientific research.

The management of databases involves designing and creating the tables and relationships between them, as well as defining rules for how the data is organized and accessed. This is typically done using a database management system (DBMS), which is a software tool designed to manage and manipulate data in a database.

2. What is RDBMS ?

RDBMS stands for Relational Database Management System. It is a type of database management system that is based on the relational model of data.

In an RDBMS, data is organized into tables, with each table containing a set of related data organized into rows and columns. Each table has a unique name, and each column within the table represents a specific data element. The rows in the table represent individual instances of the data, with each row containing a set of values that correspond to the columns in the table.

One of the key features of an RDBMS is the ability to define relationships between tables. These relationships can be used to enforce rules about how data can be stored and manipulated, and to ensure that data is consistent across different tables.

Popular RDBMSs include Oracle, MySQL, Microsoft SQL Server, PostgreSQL, and SQLite. They are widely used in enterprise applications, web applications, and many other types of software that require efficient and reliable management of large amounts of data.

3. What are constraints in SQL?

Constraints in SQL are rules that are defined on a table to ensure the accuracy, reliability, and consistency of the data stored in that table. Constraints are used to enforce business rules and data integrity by limiting the type of data that can be stored in a table or the relationships between tables.

Some common types of constraints in SQL include:

1. NOT NULL: This constraint ensures that a column cannot have a NULL value. This means that a value must be entered for the column when a new row is inserted into the table.
2. UNIQUE: This constraint ensures that the values in a column are unique, so that no two rows have the same value for that column.
3. PRIMARY KEY: This constraint identifies a column or set of columns that uniquely identify each row in the table. It ensures that the values in the column(s) are unique and not null.
4. FOREIGN KEY: This constraint defines a relationship between two tables by ensuring that the values in a column in one table match the values in a primary key column in another table.
5. CHECK: This constraint defines a condition that must be met for a row to be inserted or updated in the table. For example, a CHECK constraint could be used to ensure that a value in a column falls within a specific range or meets some other condition.

By defining constraints in SQL, it is possible to ensure that the data stored in a database is accurate, consistent, and reliable, which is essential for many types of applications.

4. What is self join?

In SQL, a self-join is a join operation that is performed on a single table, where the table is joined to itself based on a common column.

Self-joins can be useful when working with hierarchical data, such as organizational charts or tree structures, where each record in the table has a relationship with one or more other records in the same table. By performing a self-join, it is possible to retrieve information about the relationships between records in a table.

5. What is Cross join ?

In SQL, a cross join, also known as a Cartesian product, is a type of join operation that combines every row from one table with every row from another table, resulting in a new table with a number of rows equal to the product of the number of rows in each table.

A cross join does not require a join condition like other types of joins, such as inner join or outer join. Instead, it produces a table that contains every possible combination of rows between the two tables being joined.

Cross joins are not commonly used in practice, as they can quickly generate a large number of rows and cause performance issues. However, they can be useful in certain situations, such as when you need to generate all possible combinations of data or when you want to test the performance of a system by creating a large data set.

6. What is Index in SQL?

In SQL, an index is a database object that is used to improve the performance of queries by allowing the database to quickly locate rows in a table based on the values in one or more columns. An index is essentially a data structure that provides a fast way to look up rows in a table based on the values in one or more columns.

When a query is executed that includes a search condition on a column that has an index, the database can use the index to locate the rows that match the search condition, rather than having to scan the entire table. This can significantly improve the performance of the query, especially for large tables.

Indexes can be created on one or more columns of a table, and they can be either unique or non-unique. A unique index ensures that no two rows in a table have the same value in the indexed column(s), while a non-unique index allows duplicate values.

In general, indexes should be used on columns that are frequently used in search conditions, such as WHERE clauses or JOIN conditions. However, creating too many indexes can also have a negative impact on performance, as it can increase the time required to update the table when new rows are added or existing rows are modified.

Creating and maintaining indexes is an important part of database administration, as it can have a significant impact on the performance of the database. Most database management systems provide tools and utilities for creating and managing indexes.

7. What is Data integrity?

Data integrity is a fundamental concept in database management that refers to the accuracy, completeness, and consistency of data in a database.

In general, data integrity ensures that the data stored in a database is reliable and trustworthy and that it accurately reflects the real-world objects or events that it represents.

There are several types of data integrity, including:

1. Entity integrity: This ensures that each record in a table is unique and that it can be identified by a unique identifier or key.
2. Referential integrity: This ensures that the relationships between tables are maintained, and that any foreign key values in a table refer to valid primary key values in another table.
3. Domain integrity: This ensures that the values stored in a table are valid and consistent with the data type and range defined for the column.
4. User-defined integrity: This ensures that any additional business rules or constraints are applied to the data in the database to maintain its accuracy and consistency.

Maintaining data integrity is important because it ensures that the data stored in the database can be trusted and relied upon for decision making and analysis. It also helps to prevent data inconsistencies and errors that can lead to incorrect results and conclusions.

Database administrators can use a variety of tools and techniques, such as data validation checks and data auditing, to ensure that data integrity is maintained in the database.

8. What is Data Validation?

Data validation is a process in which data is checked for accuracy and consistency to ensure that it is valid and conforms to predefined rules or standards.

In database management, data validation is an important part of maintaining data integrity and ensuring that the data stored in a database is reliable and trustworthy.

Data validation can be performed in different ways, depending on the type of data and the requirements of the application. Some common methods of data validation include:

1. Field-level validation: This checks that the data entered into a specific field meets certain requirements, such as data type, length, and format.
2. Form-level validation: This checks that all the data entered into a form meets certain requirements, such as required fields, data type, and length.
3. Record-level validation: This checks that the data in a record meets certain requirements, such as uniqueness and referential integrity.
4. Batch-level validation: This checks that the data entered into a batch, such as a set of records, meets certain requirements, such as consistency and accuracy.

Data validation is typically performed using software tools and applications that automate the process of checking data against predefined rules and standards. Many database management systems provide built-in data validation features, such as constraints and triggers, that can be used to ensure data accuracy and consistency.

Data validation is important because it helps to prevent errors and inconsistencies in the data, which can have a negative impact on the performance and reliability of the database. It also helps to ensure that the data can be trusted and relied upon for decision making and analysis.

9. What is ACID and BASE?

ACID and BASE are two different sets of properties that are used to describe the consistency and reliability of distributed database systems.

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties describe a set of guarantees that a database system must provide to ensure data consistency and reliability.

1. Atomicity: This property guarantees that a transaction is treated as a single unit of work, and either all the changes made by the transaction are committed to the database, or none of them are.
2. Consistency: This property ensures that the database is always in a valid state and that any transaction must leave the database in a consistent state, regardless of any errors or failures that may occur during the transaction.
3. Isolation: This property ensures that each transaction is executed in isolation from other transactions, such that the concurrent execution of transactions does not lead to inconsistent or incorrect results.
4. Durability: This property guarantees that once a transaction is committed to the database, its changes are permanent and will survive any subsequent failures or system crashes.

On the other hand, BASE stands for Basically Available, Soft state, Eventually consistent. This set of properties is used to describe a different approach to distributed database systems that prioritize availability and partition tolerance over strong consistency.

1. Basically Available: This property means that the system must be available to provide some level of service even in the face of failures or network partitions.
2. Soft state: This property allows the system to have varying levels of consistency, allowing for temporary inconsistency or partial results.
3. Eventually Consistent: This property means that the system will eventually become consistent over time, without guaranteeing that all replicas of the data are consistent at any given moment.

In summary, ACID provides a set of strong guarantees for data consistency and reliability, while BASE provides a looser set of properties that prioritize availability and partition tolerance over strong consistency. The choice between ACID and BASE depends on the specific requirements of the application and the trade-offs between consistency, availability, and performance.

10. What is Normalization and Denormalization?

Normalization and denormalization are two opposite approaches to designing relational databases, each with their own benefits and trade-offs.

Normalization is the process of organizing data in a database to reduce redundancy and dependency. The goal of normalization is to eliminate duplicate data and ensure that each piece of data is stored in only one place, thereby reducing the risk of data inconsistencies and making the database more efficient to query and update.

Normalization is typically achieved through a series of steps called normal forms, each of which defines a set of rules for organizing data in a more normalized way. The most commonly used normal forms are:

1. First normal form (1NF): Each table cell should contain a single value, and each table should have a primary key.
2. Second normal form (2NF): Each non-key column should depend on the entire primary key, rather than only part of it.
3. Third normal form (3NF): Each non-key column should depend only on the primary key, and not on other non-key columns.

Normalization can improve database performance by reducing data redundancy, improving data integrity, and making it easier to manage data changes. However, normalization can also make queries more complex and time-consuming, especially when querying data from multiple tables.

Denormalization, on the other hand, is the process of intentionally introducing redundancy into a database in order to improve query performance. By duplicating data across multiple tables, denormalization can reduce the number of joins required to retrieve data, and can also help avoid expensive aggregate calculations.

Denormalization is often used in data warehousing and reporting applications, where query performance is critical and data integrity is less of a concern. However, denormalization can also lead to data inconsistencies and make it more difficult to manage changes to the database schema.

Overall, the choice between normalization and denormalization depends on the specific requirements of the application, and the trade-offs between data integrity, query performance, and database management.

11. What is View?

In SQL, a view is a virtual table that does not physically exist in the database but is created by a query and behaves like a table. A view is essentially a saved SELECT statement that can be treated like a table in subsequent queries, including SELECT, INSERT, UPDATE, and DELETE statements.

A view is created using a SELECT statement that retrieves data from one or more tables and defines the columns and criteria for the view. The resulting virtual table can then be used in queries just like a regular table, and any changes made to the underlying tables will be reflected in the view.

Views can be used for several purposes, including:

1. Simplifying complex queries: Views can be used to encapsulate complex queries and make them easier to read and understand by breaking them down into smaller, more manageable pieces.
2. Security and permissions: Views can be used to limit access to sensitive data by exposing only certain columns or rows of a table to specific users or roles.
3. Data abstraction and aggregation: Views can be used to present data in a different way than it is stored in the database, such as by aggregating data from multiple tables or combining columns in a specific way.
4. Performance optimization: Views can be used to improve query performance by precomputing complex joins or filters and storing the results in the view.

Overall, views provide a flexible and powerful tool for managing and querying data in SQL databases, and can be used in a variety of ways to simplify, secure, and optimize database operations.

12. What are Aggregate and Scalar functions?

Aggregate and scalar functions are two types of functions in SQL.

Aggregate functions are functions that operate on a group of rows and return a single value as output. These functions are typically used with the GROUP BY clause in a SELECT statement to perform calculations on subsets of data. Some common aggregate functions include:

• COUNT(): Returns the number of rows in a table or a group of rows.
• SUM(): Returns the sum of values in a column or a group of rows.
• AVG(): Returns the average value of a column or a group of rows.
• MIN(): Returns the minimum value in a column or a group of rows.
• MAX(): Returns the maximum value in a column or a group of rows.

Scalar functions, on the other hand, are functions that operate on a single value and return a single value as output. These functions can be used in a SELECT statement or in other SQL statements to perform operations on data values. Some common scalar functions include:

• CONCAT(): Concatenates two or more strings together.
• UPPER(): Converts a string to uppercase.
• LOWER(): Converts a string to lowercase.
• LEFT(): Returns a specified number of characters from the beginning of a string.
• RIGHT(): Returns a specified number of characters from the end of a string.

Both aggregate and scalar functions are important tools in SQL for performing calculations and transforming data. It's important to understand the differences between these types of functions and to use them appropriately depending on the task at hand.

13. What are different types of Normalizations in SQL ?

Normalization is the process of organizing data in a database in such a way that data redundancy is minimized and data integrity is maintained. There are different types of normalizations in SQL, which are as follows:

1. First Normal Form (1NF): In 1NF, a table should have a primary key, and all the columns in the table should be atomic (indivisible). This means that there should be no repeating groups or arrays of data in any of the columns.
2. Second Normal Form (2NF): In 2NF, a table should be in 1NF and all non-key attributes should be dependent on the primary key. This means that there should be no partial dependencies in the table.
3. Third Normal Form (3NF): In 3NF, a table should be in 2NF and all non-key attributes should be independent of each other. This means that there should be no transitive dependencies in the table.
4. Boyce-Codd Normal Form (BCNF): BCNF is a stronger form of 3NF in which all non-key attributes are dependent only on the primary key. This means that there should be no non-trivial dependencies in the table.
5. Fourth Normal Form (4NF): In 4NF, a table should be in BCNF and there should be no multi-valued dependencies.
6. Fifth Normal Form (5NF): 5NF is also known as Project-Join Normal Form (PJNF) and is used to handle some types of multi-valued dependencies that cannot be handled by 4NF.

Each normalization level builds upon the previous level, and higher levels of normalization lead to a more efficient and easier-to-maintain database. However, it is important to note that excessive normalization can lead to performance issues, and sometimes it may be necessary to denormalize a database to optimize performance.

14. What is Alias in SQL?

In SQL, an alias is a temporary name given to a database table or column. Aliases are used to make SQL queries more readable and to simplify the process of referencing tables and columns in the queries.

There are two types of aliases in SQL:

1. Table alias: A table alias is a temporary name given to a database table. It is used to simplify the process of referencing a table in a query, especially when multiple tables are involved in the query. Table aliases are created using the AS keyword, and are typically a single letter or a short abbreviation of the table name.
2. Column alias: A column alias is a temporary name given to a column in a database table. It is used to rename a column in the result set of a query, to make the output more readable or to provide a custom label for the data. Column aliases are created using the AS keyword, and are typically enclosed in quotes.

Aliases are especially useful when working with complex SQL queries that involve multiple tables and columns. By using aliases, you can simplify the process of writing and reading the query, and make the results more meaningful to the end user.

15. What are Delete, Truncate and Drop ?

In SQL, Delete, Truncate, and Drop are three commands used to remove data from a database or to remove database objects. While they all perform a similar function, they differ in their scope and functionality.

1. Delete: The DELETE command is used to remove rows from a table. It allows you to specify a WHERE clause to specify which rows should be removed.
2. Truncate: The TRUNCATE command is used to remove all rows from a table. Unlike the DELETE command, it does not allow you to specify a WHERE clause.
3. Drop: The DROP command is used to remove a database object, such as a table, view, or index. It completely removes the object from the database, along with all associated data and metadata.

In summary, the DELETE command is used to remove rows from a table based on a condition, the TRUNCATE command removes all rows from a table, and the DROP command removes an entire database object, such as a table, view, or index. It is important to use these commands carefully and to back up your data before making any changes to your database.