Questions in: Senior Database Administrator

Right Answer:
The SQL Insert Statement is used to add new rows of data to a table in a database. The basic syntax is:

```sql
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
```

Right Answer:
Wildcards used in databases for pattern matching include:

1. `%` - Represents zero or more characters.
2. `_` - Represents a single character.

Right Answer:
A primary key is a unique identifier for each record in a database table, ensuring that no two records can have the same value in that key column. It cannot contain null values and is used to establish relationships between tables.

Right Answer:
A database lock is a mechanism used to control access to a database resource, preventing multiple transactions from modifying the same data simultaneously, which helps maintain data integrity and consistency.

Right Answer:
A composite key in a database is a combination of two or more columns that together uniquely identify a row in a table.

Right Answer:
The types of database locks are:

1. **Shared Lock (S Lock)** - Allows multiple transactions to read a resource but not modify it.
2. **Exclusive Lock (X Lock)** - Allows a transaction to both read and modify a resource, preventing other transactions from accessing it.
3. **Update Lock (U Lock)** - Used to prevent deadlocks when a transaction intends to update a resource; it allows reading but not modifying until the lock is upgraded to an exclusive lock.
4. **Intent Lock** - Indicates that a transaction intends to acquire a more restrictive lock on a resource, used in hierarchical locking.
5. **Schema Lock** - Prevents changes to the database schema while a transaction is in progress.

Right Answer:
A join is a SQL operation that combines rows from two or more tables based on a related column between them. The different types of joins are:

1. **INNER JOIN**: Returns only the rows that have matching values in both tables.
2. **LEFT JOIN (or LEFT OUTER JOIN)**: Returns all rows from the left table and the matched rows from the right table; if there is no match, NULLs are returned for columns from the right table.
3. **RIGHT JOIN (or RIGHT OUTER JOIN)**: Returns all rows from the right table and the matched rows from the left table; if there is no match, NULLs are returned for columns from the left table.
4. **FULL JOIN (or FULL OUTER JOIN)**: Returns all rows when there is a match in either left or right table; unmatched rows will have NULLs in the columns of the table that does not have a match.
5. **CROSS JOIN**: Returns the Cartesian product of

Right Answer:
A database transaction is a sequence of one or more operations performed as a single logical unit of work. It must satisfy four properties, known as ACID: Atomicity (all operations succeed or none), Consistency (the database remains in a valid state), Isolation (transactions do not interfere with each other), and Durability (once a transaction is committed, it remains so even in case of a failure).

Right Answer:
A table in a database is a structured collection of data organized in rows and columns, where each row represents a record and each column represents a field or attribute of that record.

Right Answer:
The different types of normalization are:

1. First Normal Form (1NF)
2. Second Normal Form (2NF)
3. Third Normal Form (3NF)
4. Boyce-Codd Normal Form (BCNF)
5. Fourth Normal Form (4NF)
6. Fifth Normal Form (5NF)
7. Domain-Key Normal Form (DKNF)

Right Answer:
**Advantages of Views:**
1. Simplify complex queries by encapsulating them.
2. Enhance security by restricting access to specific data.
3. Provide a consistent interface to data, even if the underlying tables change.
4. Allow for data abstraction and hiding of complexity.

**Disadvantages of Views:**
1. Can introduce performance overhead, especially with complex views.
2. May not be updatable, limiting data manipulation.
3. Dependency on underlying tables can lead to issues if those tables change.
4. Can complicate debugging and understanding of queries.

Right Answer:
A clustered index determines the physical order of data in a table and can only be one per table, while a non-clustered index is a separate structure that points to the data and can be multiple per table.

Right Answer:
A materialized view is a database object that contains the results of a query and stores them physically on disk, allowing for faster access and retrieval of data compared to a regular view, which is computed on-the-fly.

Right Answer:
1. **Performance**: Stored procedures are precompiled, which can lead to faster execution.
2. **Reusability**: They can be reused across multiple applications, reducing code duplication.
3. **Security**: They provide a layer of security by restricting direct access to the underlying tables.
4. **Maintainability**: Changes can be made in one place without affecting the application code.
5. **Reduced Network Traffic**: They can execute multiple SQL statements in a single call, minimizing data transfer.
6. **Transaction Control**: They allow for better management of transactions and error handling.

Right Answer:
A trigger is a special type of stored procedure in a database that automatically executes or fires in response to certain events on a particular table or view, such as insertions, updates, or deletions.

Right Answer:
A stored procedure is a precompiled collection of one or more SQL statements that can be executed as a single unit to perform a specific task in a database.

Right Answer:
A self-join is a type of join in SQL where a table is joined with itself to compare rows within the same table. It is typically used to retrieve related data from the same table, often using an alias to differentiate the instances of the table.