Database Management Systems Quiz

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What is the primary goal of database normalization?

To improve query performance by combining tables.

To increase data redundancy.

To create complex, hard to understand tables.

To reduce data redundancy and improve data integrity. (correct)

In what situation is denormalization typically used?

When data integrity and consistency are the top priorities.

When you need to minimize storage space.

When the database is small and simple.

When complex queries and joins are slowing down data retrieval. (correct)

Which SQL statement is primarily used for fetching data from a database?

SELECT (correct)

DELETE

INSERT

UPDATE

Which of the following is NOT a type of SQL operator?

Spatial Operators (C) Signup and view all the answers

What is a SQL view?

A virtual table created by a SELECT query, without storing data directly. (B) Signup and view all the answers

What is the function of the UNIQUE constraint in SQL?

To prevent duplicate values in a column. (C) Signup and view all the answers

Which of the following SQL JOIN types only returns rows that have matching values in both tables?

INNER JOIN (A) Signup and view all the answers

Which SQL join returns all rows from the left table and matching rows from the right table?

LEFT JOIN (D) Signup and view all the answers

What benefit does an index provide when querying a database?

Allows for direct access to specific rows based on query conditions (D) Signup and view all the answers

What is a PRIMARY KEY in the context of database tables?

A unique identifier for each row in a table (B) Signup and view all the answers

Which of the following is NOT a disadvantage of using indexes in SQL databases?

Faster read operations (B) Signup and view all the answers

How does a clustered index affect the physical storage of data in a table?

It organizes the data based on the order of indexed columns (D) Signup and view all the answers

Which statement accurately describes a FOREIGN KEY?

It establishes a relationship with another table to maintain referential integrity (B) Signup and view all the answers

What is a primary drawback when using indexes during data manipulation operations such as INSERT and UPDATE?

Indexes must be updated, creating additional overhead (B) Signup and view all the answers

In terms of SQL indexing, how many clustered indexes can exist on a table?

One (A) Signup and view all the answers

Which operation benefits most from the presence of an index?

SELECT queries with filtering conditions (D) Signup and view all the answers

What is one strategy to handle deadlocks in SQL databases?

Consistent ordering of resource access (A) Signup and view all the answers

Which characteristic best describes OLTP systems?

Handles large volumes of simple transactions (C) Signup and view all the answers

How can reducing lock contention help manage deadlocks?

By breaking transactions into smaller steps (C) Signup and view all the answers

What is a primary use of a database snapshot?

To provide a stable dataset for testing (C) Signup and view all the answers

What is one advantage of using proper isolation levels in SQL?

They can help reduce deadlock risks (D) Signup and view all the answers

What effect does a denormalized schema have in OLAP systems?

It improves read-heavy workloads (B) Signup and view all the answers

What does deadlock detection in SQL databases involve?

Detecting and terminating one of the conflicting transactions (D) Signup and view all the answers

Which of the following is NOT a method to reduce deadlocks?

Maximizing lock duration (C) Signup and view all the answers

What is a primary function of a non-clustered index in a database?

To reference the physical data while allowing multiple index structures. (C) Signup and view all the answers

Which statement about local temporary tables is true?

They are prefixed with # and are only visible to the session that creates them. (D) Signup and view all the answers

How does a materialized view differ from a standard view in SQL?

A materialized view physically stores data; a standard view does not store data. (A) Signup and view all the answers

What is the primary purpose of a sequence in SQL?

To generate unique numeric values. (B) Signup and view all the answers

What distinguishing feature do global temporary tables have?

They can be accessed by multiple sessions concurrently. (D) Signup and view all the answers

What is a key advantage of using sequences over identity columns in SQL?

Sequences allow for both ascending and descending order values. (B) Signup and view all the answers

Which of the following accurately describes a standard view?

It executes the underlying query each time it is accessed. (C) Signup and view all the answers

What happens to local temporary tables after the session that created them ends?

They are dropped automatically. (A) Signup and view all the answers

When would you consider denormalization in database design?

When you want to improve performance for read-intensive workloads with frequent complex joins. (C) Signup and view all the answers

What is a major disadvantage of denormalization?

It can lead to increased complexity in maintaining data consistency. (D) Signup and view all the answers

Which of these is NOT a key component of a recursive CTE?

Index Member (B) Signup and view all the answers

What is the purpose of the 'Termination Condition' in a recursive CTE?

To ensure that the recursive query eventually stops processing data. (B) Signup and view all the answers

Which type of query is primarily focused on maintaining data integrity and supporting day-to-day operations?

Transactional Query (D) Signup and view all the answers

Which of these is NOT a characteristic of analytical queries?

They prioritize high-throughput and low-latency. (D) Signup and view all the answers

What is a common approach to ensuring data consistency across distributed databases that requires all participating databases to commit changes simultaneously or roll back if any part fails?

Two-Phase Commit (2PC) (A) Signup and view all the answers

Which approach to data consistency in distributed databases allows data to become consistent over time, making it suitable for situations where strong consistency isn't a critical requirement?

Eventual Consistency (A) Signup and view all the answers

Which of these options are considered a key performance metric for database monitoring?

Query duration (C) Signup and view all the answers

What is a major disadvantage of using indexing?

Slower write operations (A) Signup and view all the answers

Which of these tools is primarily used to monitor query performance in MySQL?

EXPLAIN (C) Signup and view all the answers

What is the primary advantage of breaking down complex queries into smaller, staged queries?

Increased query speed (B) Signup and view all the answers

Which of these is NOT a common approach to monitor query performance?

Using a data warehouse for historical analysis (B) Signup and view all the answers

Before you optimize query performance, what is the most important thing to do first?

Identify the causes of slow performance (B) Signup and view all the answers

Denormalization is most beneficial when you need to...

Simplify query logic (C) Signup and view all the answers

When is it best to consider using denormalization instead of indexing?

When complex joins are required for queries (A) Signup and view all the answers

Study Notes

SQL Basic Interview Questions

SQL is a standard programming language used to communicate with relational databases. It allows users to create, read, update, and delete data, as well as define database schemas and manage security.
A database is an organized collection of electronically stored data, typically structured in tables with rows and columns. It's managed by a DBMS (Database Management System).
SQL commands are broadly categorized:
- DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE.
- DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE.
- DCL (Data Control Language): GRANT, REVOKE.
- TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT.
CHAR data type stores fixed-length data, padding with spaces if needed.
VARCHAR2 data type stores variable-length data, saving space when data is shorter than the defined length.
A primary key is a unique identifier for each record in a table. It ensures no duplicate values and does not allow NULLS
A foreign key is a column (or set of columns) in one table referring to the primary key in another table. This enforces relationships and ensures data integrity.
The DEFAULT constraint sets a default value for a column when no other value is provided during insertion.

Database Normalization

Normalization is organizing data to reduce redundancy and improve data integrity.
Denormalization combines normalized tables into larger tables, sometimes to improve performance if the performance benefits outweigh the redundancy.

SQL Operators

Arithmetic operators include (+, -, *, /, %).
Comparison operators include (=, !=, <>, >, <, >=, <=).
Logical operators include (AND, OR, NOT).
Set operators include (UNION, INTERSECT, EXCEPT).
Special operators include (BETWEEN, IN, LIKE, IS NULL).

SQL Views

Views are virtual tables created from SELECT queries. They don't store data, but present data from one or more source tables structured in a way simpler than the source(s). Views improve readability and security.

UNIQUE Constraints

The UNIQUE constraint ensures all values in a column are distinct. It helps maintain data integrity by preventing duplicate values.

SQL Joins

INNER JOIN returns only matching rows from both tables.
OUTER JOIN (LEFT, RIGHT, FULL) returns all rows from one table and matching rows from another.
CROSS JOIN returns the Cartesian product of two tables.

Aggregate Functions

Aggregate functions perform calculations on a set of values and return a single value. Common examples include COUNT(), SUM(), AVG(), MIN(), and MAX().

Subqueries

Subqueries are queries nested within another query. They are useful for filtering data based on the results of another query.

WHERE vs. HAVING

WHERE filters rows before grouping (in a GROUP BY).
HAVING filters grouped data (after a GROUP BY).

Indexes

Indexes are database objects that improve query performance by allowing faster data retrieval. Think of them as a book's index.

DELETE vs. TRUNCATE

DELETE removes rows one-at-a-time.
TRUNCATE removes all rows at once and is faster.

ORDER BY

The ORDER BY clause sorts the result set of a query either ascending or descending based on one or more columns.

SQL and NoSQL Databases

SQL databases use structured tables with rows and columns, and rely on fixed schemas. They offer ACID properties.
NoSQL databases use flexible, schema-less structures (e.g. key-value stores, document stores) and are designed for horizontal scaling.

Constraints

NOT NULL ensures columns cannot have NULL values.
UNIQUE ensures unique values in a column.
PRIMARY KEY uniquely identifies each row.
FOREIGN KEY enforces referential integrity, connecting to a primary key in another table.
CHECK validates data against conditions.
DEFAULT sets a default value for columns.

Cursors

Cursors are database objects used to retrieve, manipulate, traverse rows one at a time.

Triggers

Triggers are sets of SQL statements that automatically execute in response to events on a table.

Stored Procedures

Stored procedures are precompiled and compiled SQL statements that are stored in the database.

SQL Data Types

Data types define the kind and format of data that can be stored. (e.g. INTEGER, VARCHAR, DATE).

SQL SELECT Statements

SELECT retrieves data from one or more tables, offering filtering, sorting, and display options.

NULL Values

NULL in SQL represents a missing or unknown value. It is different from zero or an empty string.

Materialized Views

Materialized views are physical tables storing results of queries.
Compared to standard views, materialized views improve performance, but data needs to be refreshed periodically.

Sequences

Sequences are database objects that generate a series of unique numeric values.

Temporary Tables

Temporary tables exist only for a session or transaction.

Partitioned Tables

Partitioned tables divide data into smaller, more manageable pieces.

ACID Properties

Atomicity: All operations in a transaction succeed or fail completely.
Consistency: Transactions preserve all database constraints.
Isolation: Transactions don't affect each other undesirably
Durability: Committed transactions permanently affect results, even in failures

Isolation Levels

Read uncommitted: Allows dirty reads.
Read committed: Prevents dirty reads but allows non-repeatable reads.
Repeatable read: Doesn't allow dirty reads or non-repeatable reads.
Serializable: The highest isolation level, preventing all issues above.

Window Functions

Window functions perform calculations across a set of related rows, without collapsing the results into aggregate values (unlike aggregate functions).

Data Integrity

Data integrity refers to the accuracy, consistency, and reliability of data within the database, and is often maintained using integrity constraints and rules.

SQL EXCEPT

The EXCEPT operator returns rows present in the first query (source) but not present in the second query (comparison dataset).

SQL PIVOT

The PIVOT operator transforms rows into columns, often used for summarizing data.

Bitmap vs B-Tree Indexes

Bitmap indexes work efficiently with low-cardinality data (e.g., yes/no)
B-Tree indexes excel with high-cardinality data (e.g., large ranges of numbers).

Query Optimization Techniques

Appropriate indexing
Avoiding unnecessary joins/subqueries
Avoiding SELECT * (select only required columns).
Using appropriate operators.
Careful use of CTEs/temporary tables.
Using appropriate query structure for query type.

Trade-offs of Indexing

Faster read speed
Slower write speed
Additional storage space

Trade-offs of Denormalization

Simpler query logic
Increased data redundancy/storage space
Slower write speed
Potentially more complex maintenance

Common Table Expressions (CTEs)

temporary reusable in-memory result sets.

Dynamic SQL

Code that constructs SQL during execution, instead of being pre-defined.
Useful for situations requiring adaptability during runtime
Increases flexibility but carries increased risk of vulnerabilities. (e.g. SQL Injection attacks)

Views

Virtual, not physical, tables.
Create views to hide complexity and improve query organization.

Data Partitioning and Sharding

Partitioning: Splits data within a single database to improve query performance. Partitioning remains within a single database.
Sharding: Divides data across multiple separate databases for horizontal scaling for high volume, often large, datasets.

Transaction Isolation levels (SQL Server)

Read Uncommitted
Read Committed
Repeatable Read
Serializable

Deadlocks

A deadlock occurs when two or more transactions wait indefinitely for each other to release locks, preventing further progress.

Databases Snapshots

Read-only static views of a database at a specific point in time.

OLTP vs OLAP systems

OLTP (Online Transaction Processing): Focus on handling frequent, relatively simple transactions.
OLAP (Online Analytical Processing): Emphasis on complex query processing spanning large datasets for data analysis.

SQL Server Hints

Using database hints (e.g., WITH (NOLOCK)) can offer speed improvements, but might lead to data inconsistency.

General SQL Best practices

Write simple, maintainable queries, minimizing complex structures.
Filter early to reduce processing load.
Select only necessary columns (avoid selecting *).
Choose appropriate indexes to improve query response times.
Optimize for frequent operations and lookups.
Plan for maintenance and tuning of indexes during database operation periods. (e.g., off-peak hours)

Specific SQL Query Examples (from the text)

Find employees earning more than the average salary
Fetch duplicate values from a column
Find employees who joined within 30 days
Fetch top 3 highest earners
Delete duplicate rows (without using a ROWID column)
Find common records in two tables.
Find employees with names starting and ending with ‘A’
Display departments with fewer than five employees, ordered by that count (in descending order)
Find employees with no manager.
Fetch the third and fourth highest-paid employees
Fetch records updated during the past hour

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Description

Test your knowledge on key concepts in database management systems, focusing on SQL normalization, indexing, and various SQL operations. This quiz covers important topics that are crucial for understanding how databases operate and manage data efficiently.