Advanced SQL interview questions and answers

1. What is View?

Keyword:
virtual table, does not contain actual data.
contains columns and rows from one or more tables.

Answer:
A View is a virtual table that contains columns and rows from one or more tables. A view does not contain actual data, it is a set of queries that are applied to one or more tables that is stored within the database as an object. After creating a view from some table(s), it used as a reference of those tables and when executed, it shows only those data which are already mentioned in the query during the creation of the View.

2. How to set auto increment column in SQL?

Answer:
In SQL Server, we use the IDENTITY keyword:
CREATE TABLE employee (
    employee_id int IDENTITY(1,1) PRIMARY KEY,
    first_name varchar(64) NOT NULL,
    last_name varchar(64)
);

In MySQL, we use the AUTO_INCREMENT keyword:
CREATE TABLE employee (
    employee_id int NOT NULL AUTO_INCREMENT,
    first_name varchar(64) NOT NULL,
    last_name varchar(64),
    PRIMARY KEY(employee_id)
);

In Oracle, we need to create a sequence and then use it in DML:
CREATE SEQUENCE seq_employee MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20;

INSERT INTO employee(employee_id, first_name, last_name)
VALUES (seq_employee.NEXTVAL,'Jack','Smith’);

3. What are the advantages and disadvantages of using stored procedure?

Keyword:
Reduce server overhead, Reduce network traffic and latency, Encapsulate business logic, Delegate access-rights, Protect against SQL injection attacks
vendor-specific, lack of tool support, track versions.

Answer:
Advantages:
a. Reduce server overhead. Stored procedure statements are stored directly in the database, which may remove all or part of the compilation overhead. Stored procedure execution plans also can be reused and cached.
b. Reduce network traffic and latency. Stored procedures run directly within the database engine and have direct access to the data being accessed, which can reduce network communication costs.
c. Encapsulate business logic. Stored procedures allow programmers to embed business logic as an API in the database, which can simplify data management and reduce the need to encode the logic elsewhere in client programs. The database system can ensure data integrity and consistency with the help of stored procedures.
d. Delegate access-rights. Stored procedures can be granted access rights to the database that users who execute those procedures do not directly have.
e. Protect against SQL 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.

Disadvantages:
a. Stored procedure languages are quite often vendor-specific. Switching to another vendor's database most likely requires rewriting any existing stored procedures.
b. Tool support for writing and debugging stored procedures is often not as good as for other programming languages.
c. Changes to stored procedures are more difficult to keep track of within a version control system than other code. Changes must be reproduced as scripts to be stored in the project history to be included, and differences in procedures can be more difficult to merge and track correctly.

4.  Explain the general steps of using Cursor in SQL.

Answer:
To use cursors in SQL, there are the following steps:
a. Declare a cursor
DECLARE cursor_employee CURSOR FOR SELECT employee_id, first_name, last_name FROM employee;

b. Open the cursor
OPEN cursor_employee;

c. Fetch the data into local variables as needed from the cursor, one row at a time
FETCH cursor_employee INTO ......;

d. Close the cursor
CLOSE cursor_employee;

5. What is Trigger?

Keyword:
auto execute in response to certain events: INSERT, DELETE or UPDATE

Answer:

A trigger is procedural code that is automatically executed in response to certain events (such as INSERT, DELETE or UPDATE) on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database.

For example, when a new record is added to the employee table, new records should also be created in the tables of the taxes, vacations and salaries.

6. What are the differences between Triggers and Stored Procedures?

Answer:
a. Stored procedures can be explicitly called and executed, while triggers can only be executed when an event (insert, delete, and update) is fired on the table on which the trigger is defined.
b. Stored procedures can be scheduled through a job, while triggers cannot be scheduled.
c. Stored procedure can take input parameters, while triggers cannot.
d. Stored procedures can return values, while triggers annot return a value.
e. Transaction statements like begin transaction, commit transaction, and rollback can be used inside stored procedures, but cannot be used inside triggers.

7. What is ACID?

Keyword:
Atomicity, Consistency, Isolation, Durability

Answer:
ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably.

Atomicity:
A transaction must be an atomic unit of work; either all of its data modifications are performed, or none of them is performed.

Consistency:
When completed, a transaction must leave all data in a consistent state. For example, all rules must be applied to the transaction's modifications to maintain all data integrity.

Isolation:
Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. A transaction either recognizes data in the state it was in before another concurrent transaction modified it, or it recognizes the data after the second transaction has completed, but it does not recognize an intermediate state.

Durability:
After a transaction has completed, its effects are permanently in place in the system. The modifications persist even in the event of a system failure.

8. What is Two-Phase Commit?

Keyword:
ensure the integrity of data in a distributed transaction
commit-request phase, commit phase

Answer:
Two-Phase Commit (2PC) is designed to ensure the integrity of data in a distributed transaction.
Two-Phase Commit mechanism consists of two phases:
a. The commit-request phase (or voting phase), in which a coordinator process attempts to prepare all the transaction's participating processes (named participants) to take the necessary steps for either committing or aborting the transaction and to vote, either "Yes": commit (if the transaction participant's local portion execution has ended properly), or "No": abort (if a problem has been detected with the local portion).
b. The commit phase, in which, based on voting of the participants, the coordinator decides whether to commit (only if all have voted "Yes") or abort the transaction (otherwise), and notifies the result to all the participants. The participants then follow with the needed actions (commit or abort) with their local transactional resources and their respective portions.

More Advanced SQL interview questions and answers: SQL Interview Notes

• How to create a View from multiple tables?
• What are the advantages and disadvantages of views?
• What is Sequence in SQL?
• What is Cursor in SQL?
• What is the difference between Stored Procedure and Function in SQL?
• Explain some ways to optimize stored procedures.
• How to invoke a trigger on demand?
• What is a Transaction in SQL?
• What is Lock in SQL?
• What is Isolation Level in SQL?
• ......

SQL Interview Notes

100+ frequently asked SQL and Database interview questions with concise summaries and detailed answers.  

Topics include: SQL Basic Concepts, SQL DDL & DML, Advanced SQL, Database Design and Performance Tuning.  

Download on the AppStore    Get it On Google Play

JavaScript Interview Notes

100+ frequently asked JavaScript interview questions with concise summaries and detailed answers. Topics include: JavaScript Basics, DOM, BOM, Object-Oriented JavaScript, Function, Scope, Closure, JSON, XML, Ajax, jQuery. 
Download on the AppStore    Get it On Google Play

Java Interview Notes

300+ frequently asked Java interview questions with concise summaries and detailed answers. Topics include: Java & OOP, Strings & Collections, IO JVM & GC, Multithreading, Generics Reflection & Annotations, Design Patterns, Java EE, Spring, JPA & Hibernate.

Download on the AppStore    Get it On Google Play