|
Database management interview questions
1. What
is a Cartesian product? What causes it?
Expected answer:
A Cartesian product is the result of an unrestricted
join of two or more tables. The result set of a three
table Cartesian product will have x * y * z number of
rows where x, y, z correspond to the number of rows in
each table involved in the join. It is causes by
specifying a table in the FROM clause without joining it
to another table.
2. What is an advantage to using a stored procedure as
opposed to passing an SQL query from an application.
Expected answer:
A stored procedure is pre-loaded in memory for faster
execution. It allows the DBMS control of permissions for
security purposes. It also eliminates the need to
recompile components when minor changes occur to the
database.
3. What is the difference of a LEFT JOIN and an INNER
JOIN statement?
Expected answer:
A LEFT JOIN will take ALL values from the first declared
table and matching values from the second declared table
based on the column the join has been declared on. An
INNER JOIN will take only matching values from both
tables
4. When a query is sent to the database and an index is
not being used, what type of execution is taking place?
Expected answer:
A table scan.
5. What are the pros and cons of using triggers?
Expected answer:
A trigger is one or more statements of SQL that are
being executed in event of data modification in a table
to which the trigger belongs.
Triggers enhance the security, efficiency, and
standardization of databases.
Triggers can be beneficial when used:
– to check or modify values before they are actually
updated or inserted in the database. This is useful if
you need to transform data from the way the user sees it
to some internal database format.
– to run other non-database operations coded in
user-defined functions
– to update data in other tables. This is useful for
maintaining relationships between data or in keeping
audit trail information.
– to check against other data in the table or in other
tables. This is useful to ensure data integrity when
referential integrity constraints aren’t appropriate, or
when table check constraints limit checking to the
current table only.
6. What are the pros and cons of using stored
procedures. When would you use them?
7. What are the pros and cons of using cursors? When
would you use them? |
|
