Databases final

What is referential integrity?

A database concept ensuring foreign keys correctly correspond to primary keys in related tables, maintaining consistent relationships between tables. It prevents orphaned records and enforces data accuracy.

What is a primary key?

A unique identifier for each record in a table. It cannot be NULL.

What is a foreign key?

A field in one table that refers to the primary key in another table, used to establish a relationship between tables.

Name common SQL query clauses

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.

What is the correct order of SQL query clauses?

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT.

What does GROUP BY do?

Groups rows that have the same values in specified columns and allows aggregate functions (e.g., COUNT, SUM) to be applied.

What’s the difference between WHERE and HAVING?

WHERE filters rows before grouping; HAVING filters groups after GROUP BY.WHERE applies conditions to individual rows; HAVING applies conditions to groups.

What does the IN predicate do in SQL?

Tests whether a value matches any value in a list or subquery (e.g., WHERE id IN (1, 2, 3)).

What are the 3 main types of relationships in databases?

One-to-one (1:1), one-to-many (1:m), and many-to-many (m:n).

What’s the difference between IFNULL() and NULLIF()?

IFNULL(expr1, expr2) returns expr2 if expr1 is NULL; NULLIF(expr1, expr2) returns NULL if expr1 = expr2.

What is the syntax of an IF statement in SQL?

IF(condition, true_value, false_value)

What does a CASE statement do in SQL?

The CASE statement evaluates a list of conditions and returns a value based on the first condition that is met. It is used for implementing conditional logic in SQL queries.

What are the first three normal forms?

• 1NF: No repeating groups; atomic values.

• 2NF: 1NF + no partial dependencies.

• 3NF: 2NF + no transitive dependencies. These forms ensure database normalization by structuring data to reduce redundancy and improve data integrity.

What’s the difference between linear and binary search?

Linear checks each item one by one; binary search divides sorted data in halves to find a value faster (logarithmic time).

What types of indexes does MySQL support?

PRIMARY, UNIQUE, FULLTEXT, SPATIAL, and regular INDEX.

What’s the difference?

Deterministic functions always return the same result for the same input. Non-deterministic functions can return different results (e.g., NOW(), RAND()).

Can SQL functions update data?

No, functions are read-only and cannot modify database state or data directly. They are used for calculations and retrieval of information.

Why do we use custom delimiters like DELIMITER @@?

To allow semicolons inside stored procedures without prematurely ending the statement.Custom delimiters prevent syntax errors by indicating the end of a statement.

Name advantages and disadvantages of stored procedures.

• Pros: Reusable logic, improved performance, security.

• Cons: Harder to debug/maintain, vendor-specific syntax.

What are IN, OUT, and INOUT parameters in stored procedures?

• IN: Passes a value into the procedure.

• OUT: Returns a value from the procedure.

• INOUT: Passes and returns a value.

What’s the difference between a function and a stored procedure?

Functions return a value and are read-only. Stored procedures can return multiple results and modify data.Functions can be used in SQL expressions while stored procedures cannot. Stored procedures may have side effects like modifying database state.

What is autocommit?

A mode where each SQL statement is automatically committed unless explicitly wrapped in a transaction.

What does ROLLBACK do in a transaction?

Reverts changes made during the current transaction to the last COMMIT.