Top SQL Interview Questions

This article contains top SQL interview questions. These questions are frequently asked in interviews whether you are a fresher or an experienced candidate.

If you are going for an interview please go through these questions and it will clear your concepts quickly. This article contains questions from Basic to Advance.




So let’s start with the questions.

Top SQL Interview Questions

Q. 1 What is SQL?

SQL stands for Structured Query Language. It lets you access and manipulate the databases.

Q. 2 What do you mean by DDL?

DDL stands for Data Definition Language. It allows you to CREATE, DELETE and ALTER database objects such as tables ,views etc.

Q. 3 What do you mean by DML?

DML stands for Data Manipulation Language. It allows you to access and manipulate data. It allows user to INSERT, RETRIEVE, UPDATE and DELETE.

Q. 4 What do you mean by DCL?

DCL stands for Data Control Language. It allows you to control access to the database. It consists of two commands GRANT and REVOKE. GRANT command allow specific user to perform some specific task. REVOKE command is used to cancel the grant permission.

Q. 5 Tell me different types of keys in SQL?

Following are the different types of keys in SQL.

1) Super Key: It is a set one or more keys that can uniquely identify a record in a table.

2) Candidate Key:   It is a set of columns of a table that can be used uniquely identify a row in a table. There can be more than one candidate key.

3) Primary Key:  It is a set of columns of a table that can uniquely identify a row in a table. It cannot accepts null or duplicate value. Only one candidate key can be the primary key.

4) Alternate Key: It is a candidate key which is currently not a primary key.

5) Unique Key: It is a set of columns of a table which can uniquely identify a row in a table. It is like a primary key but it allows only one null value and no duplicate values.

6) Foreign Key: It is a column/field in a table which is primary key in another table. It can accept null as well as duplicate values.

Q. 6 What is trigger?

Trigger is procedural code which is executed automatically whenever a event occurs on a table or a view like insert, delete etc.
Syntax

CREATE [OR REPLACE ] TRIGGER trigger_name  
{BEFORE | AFTER | INSTEAD OF }  
{INSERT [OR] | UPDATE [OR] | DELETE}  
[OF col_name]  
ON table_name  
[REFERENCING OLD AS o NEW AS n]  
[FOR EACH ROW]  
WHEN (condition)   
DECLARE 
   Declaration-statements 
BEGIN  
   Executable-statements 
EXCEPTION 
   Exception-handling-statements 
END; 

Q. 7 What is Cursor?

A cursor is a pointer to the context area. It holds the the rows returned by a SQL statement.

Q. 8 What is a View?

A view is a virtual table which contains data from one or more tables. A view contains rows and columns just like a real table.

Q.9 What do you mean by transaction?

A transaction is a single logical unit of work which accesses and possibly modifies the contents of a database. It is a group of tasks. A single task cannot be divided further.

Q. 10 What is ACID property ?

ACID is an acronym for Atomicity, Consistency, Isolation, Durability.
In order to ensure data Integrity, consistency and accuracy, a transaction has to follow some property known as ACID property.

Atomicity: It means that a transaction should be consider as a single unit that is either all of its operation are executed or none. There must be no state where a database has partially completed transaction left.

Consistency: It means that database must be consistent before and after the transactions.

Isolation:  It ensures that multiple transactions can occur concurrently. It states that all the transaction will be carried out and will be executed as if it is the only transaction in the system. No transaction will interfere each other.

Durability: It states that database should be durable that is when a transaction is completed, the modification to the database are stored in and written on  disk and they persist even in system failure.



Q. 11 What do you mean by sub-query?

A query within another query is known as sub-query. The result of sub-query is used by the outer query. Also sub-query is executed first.

Q. 12 What are Indexes?

An index can be considered as a look up table that database search engine can use to speed up data retrieval. Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows.

Q. 13 What are clustered and non-clustered indexes?

A clustered index  defines the order in which data is physically stored in a table. There is only one clustered index per table. In SQL, the primary key constraint automatically creates a clustered index on that particular column.

A non-clustered index doesn’t sort the data. A non-clustered index and table data are stored in different places similar to the book content and its index. A table can have more than one non-clustered index.

A non-clustered index is slower than the clustered index.

Q. 14 What is difference between DROP, TRUNCATE and DELETE?

DELETE is a DML command which is used to delete a specific row from the table.

TRUNCATE  is a DDL command which is used to delete all rows from the table and it can’t be retrieved back.

DROP is a DDL command which is used to remove entire table from the database and can’t be retrieved back.

Q. 15 What is join?

Join is a keyword which is used to fetch data from two or more related tables. There must be a related column between tables.

Types of join: 
1. Right Join
2. Left Join
3. Full Join
4. Inner Join

Q. 16 What are SQL constraints?

SQL constraints are the set of rules that enforced restriction on inserting, deleting or updating of a data in a database.

Q. 17 What is the difference between UNIQUE KEY and PRIMARY KEY?

Primary key can’t have NULL values but a unique key can have NULL values. There is only one primary key whereas there can be multiple unique constraints. Clustered index is automatically created by Primary key but not by the unique key.

Q. 18 What is DBMS?

A Database Management System is a system software for creating and maintaining databases. It allows users to define, retrieve, manipulate and manage database.

Q. 19 Difference between RENAME and ALIAS?

Rename is used to give permanent name to a table or a column/field whereas ALIAS is used to give temporary name to table or column while displaying data.

Q. 20 What is Stored Procedure?

A stored procedure is a collection and set of SQL statements which is stored in database. It is used to save time to write code again and again.

Q. 21 What are aggregate functions?

An aggregate function performs calculation on a set of values and returns a single value.

Following are some of the aggregate functions in SQL

1) AVG() – It calculates average value of a set of value. It ignores NULL value.

2) COUNT() – It returns number of rows in a table.

3) SUM() – Returns sum of a set of values.

4) MAX() – Returns maximum value from set of values.

5) MIN() –  Returns minimum value from set of values.

Q. 22 What is the use of NVL function?

NVL function is used to convert NULL value to a actual value.

Q. 23 What is the difference between BETWEEN and IN condition?

The operator BETWEEN is used to compare data for a range of values whereas the IN operator is used to compare a column value with more than one value.

Q. 24 Define COMMIT?

COMMIT saves all changes made by DML statements.

Q. 25 What is query optimization?

It is a process  in which database system compares different query strategies and select the query with least cost.




You may also like: SQL Interview Questions based on table

 

If you want to share questions please leave it in comment box. We will put it in the post ASAP.

Leave a Reply

Your email address will not be published. Required fields are marked *