Database Management Systems Quiz

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