SQL and RDBMS

Question: What is SQL?
Answer: SQL (Structured Query Language) is a language used to manage and manipulate relational databases.

Question: What is a relational database management system (RDBMS)?
Answer: An RDBMS organizes data into tables and provides tools for creating, reading, updating, and deleting data using SQL.

Question: What is a database?
Answer: A database is a structured collection of data stored electronically for easy access, management, and updating.

Question: What are the sublanguages of SQL?
Answer: DDL, DML, DQL, TCL, DCL. DDL defines structures (CREATE, ALTER). DML manipulates data (INSERT, UPDATE). DQL retrieves data (SELECT). TCL manages transactions (COMMIT, ROLLBACK). DCL controls access (GRANT, REVOKE).

Question: What is cardinality?
Answer: Cardinality refers to the uniqueness of data in a column or the nature of relationships between tables, such as one-to-one, one-to-many, or many-to-many.

Question: What is a candidate key?
Answer: A candidate key is a column or combination of columns that can uniquely identify rows in a table. One candidate key is selected as the primary key.

Question: What is referential integrity?
Answer: Referential integrity ensures that relationships between tables remain consistent, such as a foreign key always referencing an existing primary key.

Question: What are primary keys and foreign keys?
Answer: Primary Key uniquely identifies each row in a table. Foreign Key references a primary key in another table to establish a relationship.

Question: What are some of the different constraints on columns?
Answer: NOT NULL, UNIQUE, CHECK, DEFAULT, PRIMARY KEY, FOREIGN KEY.

Question: What is an entity-relationship diagram (ERD)?
Answer: An ERD is a graphical representation of database entities and their relationships, used for database design.

Question: What is the difference between WHERE and HAVING?
Answer: WHERE filters rows before grouping. HAVING filters grouped data after aggregation.

Question: What is the difference between GROUP BY and ORDER BY?
Answer: GROUP BY groups rows with similar values for aggregate functions. ORDER BY sorts rows in a specified order.

Question: What does LIKE do?
Answer: LIKE is used to search for patterns in text using wildcards such as % and _.

Question: How do you use subqueries?
Answer: Subqueries are queries within another query, used for dynamic filtering or computations.

Question: How does BETWEEN work?
Answer: BETWEEN filters rows within a specified range, inclusive of the boundary values.

Question: What is the order of operations in an SQL statement?
Answer: SQL executes clauses in this order: FROM → WHERE → GROUP BY → HAVING → ORDER BY → SELECT.

Question: What is the difference between aggregate and scalar functions?
Answer: Aggregate functions operate on multiple rows and return a single result (e.g., SUM, AVG). Scalar functions operate on individual values (e.g., ABS, UPPER).

Question: What are examples of aggregate and scalar functions?
Answer: Aggregate: SUM, AVG, MAX, MIN, COUNT. Scalar: ABS, UPPER, LOWER, ROUND.

Question: What are the different types of joins in SQL?
Answer: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN.

Question: What is a join condition?
Answer: The condition specified in the ON clause that defines how rows from two tables are related.

Question: What are the different set operations in SQL?
Answer: UNION combines results, removing duplicates. UNION ALL includes duplicates. INTERSECT returns rows present in both result sets.

Question: What is the difference between joins and set operations?
Answer: Joins combine columns from multiple tables. Set Operations combine rows from multiple result sets.

Question: How can I create an alias in SQL?
Answer: Use the AS keyword to assign an alias to a table or column.

Question: What does the AS keyword do?
Answer: Creates a temporary alias for a table or column for the duration of the query.

Question: What is a transaction?
Answer: A transaction is a sequence of operations treated as a single logical unit, ensuring data consistency.

Question: What are the properties of a transaction (ACID)?
Answer: ACID: Atomicity ensures all operations succeed or fail as a unit. Consistency maintains database integrity. Isolation ensures transactions do not interfere. Durability ensures changes persist after completion.

Question: What are dirty reads, non-repeatable reads, and phantom reads?
Answer: Dirty Reads: Reading uncommitted changes. Non-Repeatable Reads: Data changes between reads in the same transaction. Phantom Reads: New rows added during a transaction.

Question: What are transaction isolation levels?
Answer: Read Uncommitted, Read Committed, Repeatable Read, Serializable.

Question: What is normalization?
Answer: Normalization organizes data to reduce redundancy and improve integrity by dividing it into related tables.

Question: What are the requirements for normalization levels?
Answer: 1NF: Atomic values; no repeating groups. 2NF: No partial dependencies. 3NF: No transitive dependencies.

Question: What is an index? What are its advantages and disadvantages?
Answer: An index improves query performance by speeding up lookups. Advantages: Faster retrieval. Disadvantages: Slower writes, increased storage.

Question: What is CRUD?
Answer: CRUD stands for Create, Read, Update, and Delete — the basic operations for managing data.

Question: What is a view and a materialized view?
Answer: View: A virtual table based on a query. Materialized View: A physical storage of query results, periodically refreshed.

Question: What is a DAO?
Answer: A Data Access Object (DAO) abstracts database operations to separate application logic from database interaction.

Question: What is the danger of putting values directly into queries?
Answer: It exposes the system to SQL injection, where malicious users can manipulate SQL queries to compromise data.

Question: What is a port number? What is the default port for MySQL?
Answer: A port number is a communication endpoint. MySQL’s default port is 3306.

Question: What is multiplicity?
Answer: Multiplicity defines the cardinality of relationships between entities: 1-to-1, 1-to-Many, Many-to-Many.

Question: What is the CAP Theorem?
Answer: The CAP Theorem states that a distributed system can guarantee at most two out of three properties: Consistency, Availability, Partition Tolerance.

Question: What does CAP mean during network problems?
Answer: During network partitions, distributed systems must sacrifice either consistency or availability.

Question: What does it mean for an operation or transaction to be atomic?
Answer: An atomic operation is indivisible; it either completes entirely or does not occur at all.

Question: What does ACID stand for?
Answer: ACID: Atomicity, Consistency, Isolation, Durability.

Question: What does BASE stand for?
Answer: BASE: Basically Available, Soft-state, Eventual Consistency.