Some common interview questions related to SQLs.
Q1. What are constraints? Give some examples.
Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.
- Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY.
Q2. Give two examples of referential integrity constraints.
A primary key and a foreign key.
Q3. What is a join and explain different types of joins.
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
- Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
Q4. What is a self join?
Self join is just like any other join, except that two instances of the same table will be joined in the query.
Q5. What is a Cartesian product? What causes it?
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 caused by specifying a table in the FROM clause without joining it to another table.
Q6. What is the difference of a LEFT JOIN and an INNER JOIN statement?
- A LEFT JOIN will take ALL the 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.
Q7. What is a Procedure?
Procedures or Stored Procedures are procedural extensions or subroutines to standard SQLs provided by database systems. Generally compiled and stored inside the databases which enhances performance.
Q8. What is a Function?
A Function is a subprogram written to perform certain computations and return a single value. A function can be used in SELECT statements, provided they don’t do any data manipulation.
Q9. What are the advantages of using stored procedures?
Overhead: Because stored procedure statements are stored directly in the database, they may remove all or part of the compilation overhead that is typically required in situations where software applications send inline (dynamic) SQL queries to a database. (However, most database systems implement “statement caches” and other mechanisms to avoid repetitive compilation of dynamic SQL statements.) In addition, while they avoid some overhead, pre-compiled SQL statements add to the complexity of creating an optimal execution plan because not all arguments of the SQL statement are supplied at compile time. Depending on the specific database implementation and configuration, mixed performance results will be seen from stored procedures versus generic queries or user defined functions.
Avoidance of network traffic: A major advantage with stored procedures is that they can run directly within the database engine. In a production system, this typically means that the procedures run entirely on a specialized database server, which has direct access to the data being accessed. The benefit here is that network communication costs can be avoided completely. This becomes particularly important for complex series of SQL statements.
Encapsulation of business logic: Stored procedures allow for business logic to be embedded as an API in the database, which can simplify data management and reduce the need to encode the logic elsewhere in client programs. This may result in a lesser likelihood of data becoming corrupted through the use of faulty client programs. The database system can ensure data integrity and consistency with the help of stored procedures.
Delegation of access-rights: In many systems, stored-procedures can be granted access rights to the database which the users who will execute those procedures do not directly have.
Some protection from SQL injection attacks: Stored procedures can be used to protect against injection attacks. Stored procedure parameters will be treated as data even if an attacker inserts SQL commands. Also, some DBMSs will check the parameter’s type.
Q10. What are differences between functions and procedures?
- Functions must return a value (using the RETURN keyword), but for stored procedures this is not compulsory.
- Stored procedures can use RETURN keyword but without any value being passed.
- Functions could be used in SELECT statements, provided they don’t do any data manipulation. However, procedures cannot be included in SELECT statements.
- A function can have only IN parameters, while stored procedures may have OUT or INOUT parameters.
- A stored procedure can return multiple values using the OUT parameter or return no value at all.