What is the difference between a view and a materialized view?
A view is a virtual table that is based on the results of a query, while a materialized view is a pre computed table that is based on the results of a query. Views are used to simplify complex queries and to present data in a customized format, while materialized views are used to improve query performance and to reduce network traffic.
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only the matched rows between two tables, while LEFT JOIN returns all the rows from the left table and the matched rows from the right table. If there is no match in the right table, NULL values are returned.
What is the difference between GROUP BY and ORDER BY?
GROUP BY groups the results by one or more columns and aggregates the results, while ORDER BY sorts the results by one or more columns. GROUP BY is typically used with aggregate functions, such as SUM, AVG, COUNT, MIN, and MAX.
What is the difference between UNION and UNION ALL?
UNION combines the results of two or more SELECT statements and removes duplicate rows, while UNION ALL combines the results of two or more SELECT statements but does not remove duplicate rows. UNION ALL is faster than UNION but may return duplicate rows.
What is a self-join?
A self-join is a join operation in which a table is joined with itself. This is useful when you want to compare rows within the same table. For example, you might use a self-join to find all the employees who have the same manager.
What is a subquery?
A subquery is a query that is embedded within another query. It is often used to perform calculations or to filter results based on the results of another query. For example, you might use a subquery to find all the orders that have a total amount greater than the average total amount of all orders.
What is the difference between a view and a table?
A table is a physical database object that stores data, while a view is a virtual table that is defined by a query. A view does not store data; instead, it retrieves data from one or more tables. Views can be used to simplify complex queries, to restrict access to sensitive data, or to present data in a customized format.
What is a deadlock?
A deadlock is a situation where two or more transactions are blocked and waiting for resources that are held by each other. For example, if Transaction A has a lock on Table 1 and is waiting for a lock on Table 2, while Transaction B has a lock on Table 2 and is waiting for a lock on Table 1, a deadlock occurs. Deadlocks can cause database performance problems and require manual intervention to resolve.
What is normalization?
Normalization is a process of organizing data in a database in such a way that it reduces redundancy and dependency. This helps to improve data integrity and efficiency of the database. There are different levels of normalization, from first normal form (1NF) to fifth normal form (5NF).
What is a trigger?
A trigger is a special type of stored procedure that is automatically executed in response to certain database events, such as insert, update, or delete operations on a table. Triggers can be used to enforce business rules, to audit data changes, or to perform complex calculations.
What is a correlated subquery?
A correlated subquery is a subquery that refers to a column from the outer query. This allows the subquery to filter results based on the values of the outer query. Correlated subqueries can be used to perform complex calculations or to filter results based on complex conditions.
What is a temporary table?
A temporary table is a table that is created and used for a specific purpose within a session or a transaction. Temporary tables can be used to store intermediate results of a complex query, to partition large data sets, or to perform batch updates on a subset of data. Temporary tables are automatically dropped when the session or transaction ends.
What is the difference between a primary key and a foreign key?
A primary key is a column or a set of columns that uniquely identify each row in a table, while a foreign key is a column or a set of columns that references a primary key in another table. A foreign key is used to enforce referential integrity between tables.
What is a stored procedure?
A stored procedure is a precompiled set of SQL statements that can be executed with a single call from a client application. Stored procedures can be used to improve performance, to encapsulate business logic, or to enforce security policies.
What is a materialized view?
A materialized view is a precomputed table that is based on the results of a query. Materialized views can be used to improve query performance, to reduce network traffic, or to provide a snapshot of data at a particular point in time. Materialized views can be refreshed manually or automatically on a regular basis.
What is the difference between a clustered index and a non-clustered index?
A clustered index is a type of index that determines the physical order of data in a table, while a non-clustered index is a type of index that is stored separately from the data and provides a logical order of data. A table can have only one clustered index but can have multiple non-clustered indexes.
What is a cursor?
A cursor is a database object that allows you to retrieve and manipulate a set of rows one at a time. Cursors can be used to perform complex calculations, to update or delete rows based on complex criteria, or to process large data sets in batches.
What is a constraint?
A constraint is a rule that is enforced on a database table to ensure data integrity. There are different types of constraints, such as primary key, foreign key, unique, check, and not null. Constraints can be used to prevent data duplication, to enforce business rules, or to improve database performance.
What is a transaction?
A transaction is a sequence of one or more database operations that are performed as a single unit of work. Transactions can be used to ensure data consistency, to maintain database integrity, or to recover from errors or system failures. Transactions can be committed or rolled back to ensure data consistency.
What is a join?
A join is a database operation that combines rows from two or more tables based on a related column or set of columns. There are different types of joins, such as inner join, left join, right join, and full outer join. Joins can be used to retrieve data from multiple tables and to perform complex calculations based on related data.
