2. SQL and Databases Flashcards
What are the differences between OLTP and OLAP systems?
OLTP (Online Transaction Processing) systems are optimized for managing transaction-oriented applications, while OLAP (Online Analytical Processing) systems are designed for complex queries and data analysis.
What is indexing in databases, and how does it improve performance?
Indexing is a data structure technique that improves the speed of data retrieval operations on a database table. It allows the database to find data faster without scanning the entire table.
Explain the concept of normalization and denormalization.
Normalization is the process of organizing data to reduce redundancy, while denormalization is the process of combining tables to improve read performance at the cost of increased redundancy.
How do you optimize complex SQL queries?
Optimizing complex SQL queries can involve indexing, rewriting queries for efficiency, reducing the number of joins, and using appropriate filtering and aggregation.
What is a clustered vs. non-clustered index?
A clustered index determines the physical order of data in a table, while a non-clustered index is a separate structure that points to the physical data.
What is a materialized view, and how does it differ from a regular view?
A materialized view is a database object that contains the results of a query and is stored on disk, whereas a regular view is a virtual table that does not store data.
How do you handle duplicates in SQL data?
Duplicates in SQL data can be handled using the DISTINCT keyword, GROUP BY clause, or by using the ROW_NUMBER() function to identify and remove duplicates.
Explain ACID properties in a relational database.
ACID properties ensure that database transactions are processed reliably: Atomicity, Consistency, Isolation, and Durability.
What is the difference between UNION and UNION ALL?
UNION combines the results of two queries and removes duplicates, while UNION ALL combines the results and includes all duplicates.
How does partitioning improve query performance?
Partitioning improves query performance by dividing a large table into smaller, more manageable pieces, allowing for faster data access and maintenance.
