SQL and RDBMS

What is SQL?

SQL (Structured Query Language) is a language used to manage and manipulate relational databases.

What is a relational database management system (RDBMS)?

An RDBMS organizes data into tables and provides tools for creating, reading, updating, and deleting data using SQL.

What is a database?

A database is a structured collection of data stored electronically for easy access, management, and updating.

What are the sublanguages of SQL?

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).

What is cardinality?

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.

What is a candidate key?

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.

What is referential integrity?

Referential integrity ensures that relationships between tables remain consistent, such as a foreign key always referencing an existing primary key.

What are primary keys and foreign keys?

Primary Key uniquely identifies each row in a table. Foreign Key references a primary key in another table to establish a relationship.

What are some of the different constraints on columns?

NOT NULL, UNIQUE, CHECK, DEFAULT, PRIMARY KEY, FOREIGN KEY.

What is an entity-relationship diagram (ERD)?

An ERD is a graphical representation of database entities and their relationships, used for database design.

What is the difference between WHERE and HAVING?

WHERE filters rows before grouping. HAVING filters grouped data after aggregation.

What is the difference between GROUP BY and ORDER BY?

GROUP BY groups rows with similar values for aggregate functions. ORDER BY sorts rows in a specified order.

What does LIKE do?

LIKE is used to search for patterns in text using wildcards such as % and _.

How do you use subqueries?

Subqueries are queries within another query, used for dynamic filtering or computations.

How does BETWEEN work?

BETWEEN filters rows within a specified range, inclusive of the boundary values.

What is the order of operations in an SQL statement?

SQL executes clauses in this order: FROM → WHERE → GROUP BY → HAVING → SELECT→ ORDER BY .

What is the difference between aggregate and scalar functions?

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).

What are examples of aggregate and scalar functions?

Aggregate: SUM, AVG, MAX, MIN, COUNT. Scalar: ABS, UPPER, LOWER, ROUND.

What are the different types of joins in SQL?

INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN.

What is a join condition?

The condition specified in the ON clause that defines how rows from two tables are related.

What are the different set operations in SQL?

UNION combines results, removing duplicates. UNION ALL includes duplicates. INTERSECT returns rows present in both result sets.

What is the difference between joins and set operations?

Joins combine columns from multiple tables. Set Operations combine rows from multiple result sets.

How can I create an alias in SQL?

Use the AS keyword to assign an alias to a table or column.

What does the AS keyword do?

Creates a temporary alias for a table or column for the duration of the query.

What is a transaction?

A transaction is a sequence of operations treated as a single logical unit, ensuring data consistency.

What are the properties of a transaction (ACID)?

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.

What are dirty reads, non-repeatable reads, and phantom reads?

Dirty Reads: Reading uncommitted changes. Non-Repeatable Reads: Data changes between reads in the same transaction. Phantom Reads: New rows added during a transaction.

What are transaction isolation levels?

Read Uncommitted, Read Committed, Repeatable Read, Serializable.

What is normalization?

Normalization organizes data to reduce redundancy and improve integrity by dividing it into related tables.

What are the requirements for normalization levels?

1NF ensures atomic values and no repeating groups; 2NF ensures all data should depend on the primary key; 3NF removes transitive dependencies, any non-primary-key column must depend solely on the primary key, not indirectly through another non-key column.

What is an index? What are its advantages and disadvantages?

An index improves query performance by speeding up lookups. Advantages: Faster retrieval. Disadvantages: Slower writes, increased storage.

What is CRUD?

CRUD stands for Create, Read, Update, and Delete — the basic operations for managing data.

What is a view and a materialized view?

View: A virtual table based on a query. Materialized View: A physical storage of query results, periodically refreshed.

What is a DAO?

A Data Access Object (DAO) abstracts database operations to separate application logic from database interaction.

What is the danger of putting values directly into queries?

It exposes the system to SQL injection, where malicious users can manipulate SQL queries to compromise data.

What is a port number? What is the default port for MySQL?

A port number is a communication endpoint. MySQL’s default port is 3306.

What is multiplicity?

Multiplicity defines the cardinality of relationships between entities: 1-to-1, 1-to-Many, Many-to-Many.

What is the CAP Theorem?

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

What does CAP mean during network problems?

During network partitions, distributed systems must sacrifice either consistency or availability.

What does it mean for an operation or transaction to be atomic?

An atomic operation is indivisible; it either completes entirely or does not occur at all.

What does ACID stand for?

ACID: Atomicity, Consistency, Isolation, Durability.

What does BASE stand for?

BASE: Basically Available, Soft-state, Eventual Consistency. This is a model used in distributed systems as an alternative to the ACID properties of traditional databases, emphasizing availability over strict consistency.