SQL Interview Questions

Basics of DBMS

1. What is DBMS

The database management system – DBMS is a collection of programs that enables user to store, retrieve, update and delete information from a database. In other words, it is a collection of tables, views, constraints, indexes, data, triggers, packages, procedures, functions, synonyms, schemas etc.

2. What is RDBMS?

Relational Database Management system - RDBMS is a database management system (DBMS). It is based on the relational model. Data from relational database can be accessed or reassembled in many different ways without having to reorganize the database tables. Data from relational database can be accessed using an API, Structured Query Language (SQL).

3. What is SQL?

Structured Query Language (SQL) is a language designed specifically for communicating with databases. SQL is an ANSI (American National Standards Institute) standard.

4. What are the different types of SQL's statements?

There are mainly 4 types of SQL statements.

  • DDL – Data Definition Language

DDLs are used to define the metadata of the database. i.e.; using this, we create schema, tables, constraints, indexes in the database.  DDLs are also used to modify Schema, tables index etc. Basically, using DDL statements, we create skeleton of the database. It helps to store the metadata information like number of schemas and tables, their names, columns in each table, indexes, constraints etc in the database.

  • DML– Data Manipulation Language

When we have to insert records into table or get specific record from the table, or need to change some record, or delete some record or perform any other actions on records in the database, we need to have some media to perform it. DML helps to handle user requests. It helps to insert, delete, update, and retrieve the data from the database. Basically, DML is used for manipulating the data in the database tables.

  • DCL– Data Control Language

DCL languages are used to control the user access to the database, tables, views, procedures, functions and packages. They give different levels of access to the objects in the database. Example - Grant, Revoke access permission to the user to access data in database.

  • TCL – Transaction Control Language

Suppose we have inserted some records in to Employee table. Now we need to save them. How? Similarly, we have updated something wrong on the table. After updating we realized that its wrong. Now we need to unsave the changes that have been done. How? We have deleted something or inserted something which is not correct. It has to be undone. All these saving and undoing the tasks can be done by TCL. E.g.: - COMMIT, ROLLBACK, SAVEPOINT, AUTOCOMMIT etc.

5. Is SELECT Statement a DML?

Ideally SELECT Statement is not considered as BML. But it can be considered as a limited version of DML, since it can't change data in the database. But it can perform operations on data retrieved from DBMS, before the results are returned to the calling function.

6. What are the Advantages of SQL?

Advantages of SQL

High Speed

SQL queries are efficient enough to retrieve large number of records very quickly.

Well Defined Standard

SQL Database use standards like ANSI and ISO. Hence they have well defined standard for coding.

Less Coding

It provides most of the functionality by defining inbuilt functions. Hence we need not code much. E.g.: - Sorting can be done by using ORDER BY clause; no need to write any code for sorting.

Emergence of ORDBMS

Initially SQL DB was restricted to relational DB. But now it is updated to have Object Oriented features - ORDBMS, which makes SQL more powerful.

Not a proprietary Language

SQL is now supported by almost all the DBs like Oracle, SQL, MySQL etc. Hence it is not a proprietary language.

Easy to Learn

Queries are made up of plain English language. Hence it is easy to learn.

7. What is a field in a database?

Field is a specific data within the table. In other words, when we say a table, it will be composed of various real world data. Specific information that is present in the table is known as field or column in DB.

Examples: Employee Name, Employee ID etc are fields of EMPLOYEES table.

8. What is a Record in a database?

A record is a collection of values of fields / columns that as a whole represent one entity in the table.

For example, suppose JOBS table is composed of JOB_ID and JOB_NAME fields. Then their value (110, ‘CLERK’) is a record or row of JOBS table in DB.

9. What is a Table in a database?

A database is a collection of related data. Each of these datas is grouped into different related groups. Each of these groups is stored in the physical memory like disks in the form of bits. But when a user wants to see some specific data, if he is given in the form of bits, he will not understand what it is. Also, in the memory records are scattered in different data blocks and it will not be in any order. But user would always like to see a meaningful and formatted data. Hence the user is given a logical view of the data stored in the database. This is achieved by displaying the records in the form of a table with rows and columns. Each column in the table forms the related set of information of an object. Object can be anything in the real world –either living or non-living. For example, if we consider Pen as an object, we can create a table for it with columns like its name, type, pen color, ink color, orientation, weight, ease of use etc. Basically column contains the specification or details about the object with which a table is created. Rows of the table contain the value for these entire columns. Each row will have specific values related to single object. For example, a pen (Reynolds, Ballpoint, Blue, blue, linear, .5, Yes) will be specific to one type of pen. There can be multiple types of pen, and each specification of them forms different rows.

This is the logical view of the data or a collection of records of a specific type. For example, EMPLOYEES table, JOBS table etc.

Database Transactions

10. What is a database transaction?

A transaction is a set of actions – set of SQL statements that are executed either completely or not at all. A transaction is said to be complete only when the set of SQL statements within it are executed fully or not executed at all. A transaction consists of more than one SQL statement. For example, steps involved calculation of payroll for employees in the department, together form a transaction. This will have set of actions like retrieve the employee details, his job level, and pay level according to his job level, and then calculation of his actual salary. All these actions together constitute one transaction. For this transaction to be complete, all these actions should be performed or not performed at all.

Therefore, Database transaction is the one that takes database from one consistent state to another. At the end of the transaction the system must be in the prior state if the transaction fails or the status of the system should reflect the successful completion if the transaction goes through.

11. What are properties of a transaction?

In order to ensure that a transaction is completed fully, it defines four properties of it. In a database, each transaction should maintain ACID property to meet the consistency and integrity of the database.

Atomicity: - This property states that each transaction must be considered as a single unit and must be completed fully or not completed at all. No transaction in the database is left half completed. Database should be in a state either before the transaction execution or after the transaction execution. It should not be in a state ‘executing’.

In our example of calculating total marks above, the transaction should not be left at any one of the step above. All the 5 steps have to be either completed or none of the step has to be completed. If a transaction is failed to execute any step, then it has to rollback all the previous steps and come to the state before the transaction or it should try to complete the failed step and further steps to complete whole transaction.

This can be clearly visualized in our ATM example. If the transaction fails after (ii) or (iii), then the user will get money, but his account will not be updated. This should not happen. Whenever a transaction is executed, all the steps in it should be completed or not completed at all. Although ATM withdrawal has money single steps in it, all should be executed as if they are part of single transaction and completed. The state of DB should be either at before ATM withdrawal (of course, user without withdrawn money) or after ATM withdrawal (user with money and account updated). This will make the system in consistent state, and hence there will not be any incorrect data in DB.

Consistency: - Any transaction should not inject any incorrect or unwanted data into the database. It should maintain the consistency of the database.

In above example of total marks, while calculating the total, it should not perform any other action like inserting or updating or delete. It should also not pick marks for other students. It should be picking the marks for the same student and calculating the total. Hence it maintains the consistency of the database.

In the ATM withdrawal example, it should make sure withdrawn amount is 500 and deducted amount from the account is also 500. It should not give 500 to the user and deduct 100 from his account. It should always make sure system is well balanced before and after the transaction. There should not be any mistakes in DB because of the transaction.

One of the methods to achieve consistency of DB is to define primary and foreign keys. These keys will not restrict unwanted data insert/ delete/update, by checking the integrity of data in DB. Suppose the transaction was to delete a department for which employees are still working. Then the system will not allow deleting the department, unless all its employees are deleted from the system. This is because foreign key is defined on employee table for its department.

Another way is by maintaining the log for each transaction. It will make sure if there is any failure in middle of any transaction, the data will be recovered by seeing the log. Say, the system fails after step (ii) in ATM withdrawal. The system would have kept the log of transaction (ii) before executing it and after executing it. Hence when system recovers, it sees the log for last executed transaction, and understands that system has not been updated for the amount withdrawn and updates it.

Isolation: - If there are multiple transactions executing simultaneously, then all the transaction should be processed as if they are single transaction. But individual transaction in it should not alter or affect the other transaction. That means each transaction should be executed as if they are independent.

Suppose there are two transactions, updating Tom’s address and James’ phone number which are executed in parallel. Each transaction should be executed its own to complete their transaction and it should not affect the execution of other transaction.

Similarly, in money withdrawal from ATM, if manager checks his account balance while he is withdrawing the money, then manager should see the balance either before withdrawing the money or after withdrawing the money. Manger viewing his balance is considered as another transaction and user’s transaction should not give inconsistent data to manager’s transaction. Manager should not be able to see the amount after step (ii) above. Manager should see the balance only after, it is updated in the database. i.e.; each transaction should be executed in sequence rather than executing it in parallel. This will make sure that each individual transaction is completed and any other dependent transaction will get the consistent data out of it. Any failure to any transaction will not affect other transaction in this case. Hence it makes all the transaction consistent.

Durability: - The database should be strong enough to handle any system failure. It should not be working for single transaction alone. It should be able to handle multiple transactions too. If there is any set of insert /update, then it should be able to handle and commit to the database. If there is any failure, the database should be able to recover it to the consistent state.

Suppose if the system crashes after the transaction updates Tom’s address, but before committing the transaction to the database, then the database should be strong enough to recover Tom’s original address. It should not be updated to newer address.

In ATM withdrawal example, if the system failure happens after step (ii) or (iii), the system should be strong enough to update DB with his new balance, after system recovers. It should keep log of each transaction and its failure. So when the system recovers, it should be able to know when a system has failed and if there is any pending transaction, then it should be updated to DB.

12. What is a Database Lock?

When two sessions or users of database try to update or delete the same data in a table, then there will be a concurrent update problem. In order to avoid this problem, database locks the data for the first user and allows him to update/delete the data. Once he is done with his update/delete, he COMMITs or ROLLBACK the transaction, which will release the lock on the data. When lock on the data is released, another user can lock it for his changes.

Therefore, locking in the context of SQL is to hold the row or particular column which the user is going to update and not allowing any other session or user to insert/update/delete the data. It will not allow anyone to use the data until the lock on the data is released. Lock on the data will be released when the transaction is committed or rolled back.

13. What are the types of locks?

There are 2 types of locks:

1. Shared Lock: - In this type of locks, the data being locked can be read by other users / sessions. But it will not allow others to update the data that was being locked.

2. Exclusive Lock: - In this type of locks, data that is being locked cannot be read or updated by other users or sessions. It will be visible for read and write for the user who has locked it. Other users/session will have to wait till the lock is being released.

14. What are the techniques of locking?

Database lock can be placed at different levels – on single row or multiple rows or on particular column or on entire table. This database locking at different level is known as locking granularity. Let us discuss them one by one below:

  • Database Level Locking: - In this method, entire database is locked for update. Here, only one user or session will be active for any update and any other users cannot update the data. This method is not widely used, as it locks entire database. However, in Oracle the exclusive lock is same as Database lock and does not allow others to use entire database. It will be helpful when some support update is being executed like upgrading to new version of software etc.
  • File Level Locking: - Here, entire database file will be locked. When we say database file, it may include whole table, or part of a table or part of multiple tables. Since file lock can include either whole or partial data from different tables, this type of lock is less frequent.
  • Table Level Locking: -In this method, entire table will be locked. This will be useful when we need to update whole rows of the table. It will also be useful when we add/ remove some columns of the table where the changes affect entire table. Therefore, in Oracle this type of lock is also known as DDL lock.
  • Page or Block Level Locking: - In this method, page or block of the database file will be locked for update. A page or block might contain entire or partial data of the table. This page or block represents space in memory location where data is occupied. This may contain entire table data or partial data. Hence this type of locking is also less frequent.
  • Row Level Locking: - In this method entire row of a table is locked for update. It is most common type of locking mechanism.
  • Column Level Locking: - In this method some columns of a row of a table is locked for update. This type of lock requires lots of resource to manage and release locks. Hence it is very less frequently used locking type.

Database Normalization

14. What are the different types of normalization?

Normalization is a set of rules/guidelines/technique that is used while designing a database.  These rules help to remove all the anomalies and distribute the data among different related tables and query them efficiently and effectively. It removes all the duplication issues and incorrect data issues, helping to have a well designed database. Normalization is divided into following normal forms:

  • First Normal Form (1NF): - A table is said to be in First Normal Form (1NF) if and only if each attribute of the relation is atomic. That is,
    • Each row in a table should be identified by primary key (a unique column value or group of unique column values)
    • No rows of data should have repeating group of column values.
  • Second Normal Form (2NF): - A relation is said to be in a second normal form if and only if,
    • it's in first normal form
    • Every non-key attributes are identified by the use of primary key
    • All subset of data, which applies to have multiple rows in a table must be removed and placed in a new table. And this new table and the parent table should be related by the use of foreign key.
  • Third Normal Form (3NF): - For a relation to be in third normal form:
    • it should meet all the requirements of both 1NF and 2NF
    • If there are any columns which are not related to primary key, then remove them and put it in a separate table, relate both the table by means of foreign key i.e.; there should not be any transitive dependency.
  • Boyce-Codd Normal Form (3.5NF): - This normal form is also referred as 3.5 normal forms. This normal form
    • Meets all the requirement of 3NF
    • Any table is said to be in BCNF, if its candidate keys do not have any partial dependency on the other attributes. i.e.; in any table with (x, y, z) columns, if (x, y)->z and z->x then it's a violation of BCNF. If (x, y) are composite keys and (x, y)->z, then there should not be any reverse dependency, directly or partially.
  • Forth Normal Form (4NF): - In the fourth normal form,
    • It should meet all the requirement of 3NF
    • Attribute of one or more rows in the table should not result in more than one rows of the same table leading to multi-valued dependencies
  • Fifth Normal Form (5NF): - A database is said to be in 5NF, if and only if,
    • It's in 4NF
If we can decompose table further to eliminate redundancy and anomaly, and when we re-join the decomposed tables by means of candidate keys, we should not be losing the original data or any new record set should not arise. In simple words, joining two or more decomposed table should not lose records nor create new records.

Database Keys and Constraints

15. What is a primary key?

It is a key column in a table, composed of one or more columns of the same table. This primary key is the key which is used to uniquely identify each record in the table. This is the primary column with which we identify the records.
For example, in EMPLOYEES table, we use EMPLOYEE_ID to identify each and every employee in the department. Hence EMPLOYEE_ID is the unique column in the table which differentiates each employee from other. No two employees can have same ID. Hence it is considered as primary key column.

16. What is a Composite Key?

A composite key is a key where combinations of columns behave like a key. For example In a JOB_HISTORY table, JOB_ID and EMPLOYEE_ID together tells us which employee was performing which job. When any one of the IDs taken alone, it doesn’t give any meaningful information to the user. 

17. What is a Composite Primary Key?

It is a key column in a table, composed of more than one column of the same table. This primary key is the key which is used to uniquely identify each record in the table. This is the primary column with which we identify the records.

For example In a JOB_HISTORY table, JOB_ID and EMPLOYEE_ID together tells us which employee was performing which job. 

18. What is a Foreign Key?

In simple words, this is the column from another table present in current table. SQL is a relational database and each table in the DB is related to each other by means of parent-child relationship. Hence in order to represent or identify parent and child tables, we add a primary key column of the parent table into the child table – hence establishing the relationship. This parent key column in the child table is known as foreign key column. Using this column, we can identify, to which category or parent, the records in the child table belongs to.
Consider the same example of EMPLOYEES and DEPARTEMNTS. Each employee will be working for one particular department. That means each employee has a parent key column that represents his department. Hence DEPARTMENT_ID in the DEPARTMENTS table is its primary key and in the EMPLOYEES table is its foreign key.

19. What is a Unique Key?

Unique key is same as primary with the difference being the existence of null. Unique key field allows one value as NULL value.

20. Define SQL Insert Statement?

This statement is used to insert new records into the table. The general syntax for insert is as follows:


 (col1, col2, col3,...colN) VALUES (value1, value2, value3,...valueN);
It inserts value to each column from 1 to N. When we insert the data in this way, we need to make sure datatypes of each column matches with the value we are inserting. Else, data will not be inserted or will insert wrong values. Also, if there is any foreign key constraint on the table, then we have to make sure foreign key value already exists in the parent table.

21. Define SQL Update Statement?

Update statement is used to modify the data value in the table. General syntax for update is as below:

UPDATE table_name
SET column_name1 = value1,
column_name2 = value2,


column_nameN = valueN,
[WHERE condition]

22. Define SQL Delete Statement?

Using this statement, we can delete the records in the entire table or specific record by specifying the condition.

DELETE FROM table_name [WHERE condition];

23. What are wild cards used in database for Pattern Matching?

LIKE operator is used for pattern matching in SQL. Suppose we have to search all the employees whose name starts with ‘Ka’. How do we query this? SQL provides an operator LIKE, where we can compare only part of the column values.



In the above query, ‘%’ is used to indicate that it can be anything and any number of characters. It will display all the employees whose name starts with ‘Ka’
We can search the characters in any combination by using LIKE and %.
If we need to restrict the number of characters to one at the end of ‘Ka’, then we can use ‘-‘ instead of ‘%’.


Above query will return all the employees with 3 letter names starting with ‘Ka’.

Similar to ‘%’, ‘_’ can also be used in any combination in the query.



WHERE EMP_NAME LIKE ‘[KMR] %’; -- this will return the employees whose name starts with K, M or R


WHERE EMP_NAME LIKE ‘[K-N] %’;-- this will return the employees whose name starts with K, L, M, and N


WHERE EMP_NAME LIKE ‘[! KMR] %’; -- this will return the employees whose name DOES NOT start with K, M or R

SQL Joins

24. Define Join and explain different type of joins?

When we have to combine one or more column filters in the WHERE clause we can use them by using AND or OR operators. The AND operator will perform logical AND on the filter conditions specified and displays the result, where as OR will perform logical OR operations. Different types of joins are:

  • Inner Join: - This is the most common type of join and is similar to AND operation explained above. It combines the results of one or more tables and displays the results when all the filter conditions are met. There are two ways to represent the same.
  • Left Join: - This join retrieves all the records from the table which is on the LHS of ‘LEFT OUTER JOIN’ clause and only the matching records from RHS.
  • Right Join: - This join is opposite of left outer join. It retrieves all the records from the table which is on the RHS of ‘RIGHT OUTER JOIN’ clause and only the matching records from LHS.
  • Full Join: - This is combination of both left and outer join. It displays all the matching columns from both the tables, and if it does not find any matching row, it displays NULL.

25. What is Self-Join?

This is another joining technique where same table is joined with itself. Usually in a join, two different tables with parent – child relation are joined to get the matching data. In a self join same table is joined to get data. Usually this kind of join is useful to get the hierarchical data.
Suppose we need to find the details about the manager of EMPLOYEE_ID = 115. Here manager is also an employee. Hence we will have his details in EMPLOYEES table. But we do not know which manager. We want to get manager details of another employee whose id is 115. That means, first we need to get the manager id from the EMPLOYEES table for the employee 115. Then we again have to query EMPLOYEES table to get the details of the manager. That means we have to query twice EMPLOYEES table: first time to get manager Id of the employee, and next to get the details of the manager. Hence we need to use EMPLOYEES twice in our query as employee and manager. But when we write table name twice, the compiler will not understand for what purpose we have used twice or which table is fetching manager id and which table is giving manager details. Hence we have to use alias name for the table to differentiate both the tables. When we use alias to the tables, here even though both of them are referring to the same table, compiler considers them as two different tables – employee and manager. But we are going to join the same table to get the result. Hence it is a self join.



AND emp.EMPLOYEE_ID = 115;

26. What is Cross Join?

Here all the records from one table are joined with all the rows from other table. Here matching columns are not considered like we do in other joins. It is also known as cartesian join.

Database Views

27. What is a view?

Views are subset of table. View also has set of records in the form of rows and columns. But it is created based on the records in one or more tables. A query is written on table/s and is given a name. Such named query is called as a view.
A table will have large number of data and table will be fired with specific frequently. In such case, instead of rewriting the query again and again, a name is given to the query and it will be called whenever it is required. Hence view is also called as named query or stored query.  A view can be created as follows:


WHERE [Condition];

28. What is a materialized view?

As we have seen above, views are executed when we fire query on it. So, it does not speed up the execution of the query that is having views. But it simplifies the query. But when we write a query it is always expected it to run quickly. So what we can do is, run the frequently used query and store it under some name! This is similar to view but here we are executing the query and storing the results of query under name. This type of view is called materialized view. Using materialized view in complex query reduces the execution time for running repeated queries. It involves the time for running ‘SELECT * FROM  ...’. Unlike views, in this type of views, copies of table records are created and stored. Materialized views are not virtual tables. Hence there will be a storage space allocated to it.



  FROM EMPLOYEE e, DEPARTMENT d – two tables are used to create a view

  WHERE e.DEPT_ID = d.DEPT_ID -- Join condition

  AND d.DEPT_NAME = ‘DESIGN’; -- Filtering condition

29. What are the advantages and disadvantages of views in a database?


1. Views don't store data in a physical location.

2. The view can be used to hide some of the columns from the table.

3. Views can provide Access Restriction, since data insertion, update and deletion is not possible with the view.

  1. When a table is dropped, associated view become irrelevant.
  2. Since the view is created when a query requesting data from view is triggered, its a bit slow.
  3. When views are created for large tables, it occupies more memory.

Stored Procedures and Triggers 

30. What is a stored procedure?

A procedure is a named PL/SQL block which executes one or more related task. The procedure can take inputs, process them and send back output. Standard syntax for a procedure is as below:


    [ (parameter [,parameter]) ]







END [procedure_name];
31. What are the advantages a stored procedure?

Since stored procedures are precompiled and stored in the database, it can execute queries much faster. In addition, we can have multiple queries stored under single name, the time required to execute each query one by one is much reduced here.

32. What is a trigger?

Triggers are blocks of PL/SQL code, which are executed as a result of any insert / update / delete on the table. There will not be any explicit call for triggers. It is always automatically executed. It might perform single query or set of queries to meet the goal. But it will be always written as a PL/SQL block. It is always associated with insert / update / delete of a table. But the set of transaction inside the trigger can be performed on the same table or one or more other tables.
The general syntax of a trigger is as follows:

33. Explain the difference between DELETE, TRUNCATE and DROP commands?

DELETE: - It deletes whole or partial records from the table. Once record is deleted, it can be committed or rolled back. We can use WHERE clause in DELETE statement to delete partial records from the table.

TRUNCATE: - This command will remove all the records from the table. It does not require any COMMIT or ROLLBACK, once this command is issued. We cannot use WHERE clause in TRUNCATE statement.

DROP: - It will drop table, constraints and indexes from the database. That means, it will delete the data as well as the structure of the tables.

34. What is the difference between Cluster and Non cluster Index?

Clustered Index

Non-Clustered Index

Table records are sorted based on the column on which clustered index is created.

Order of Table records has no effect of index.

Since same records are stored at same place, retrieving the data is faster.

Records may or may not be scattered.

Only one clustered index can be created.

Multiple non-clustered index can be created.date

Any changes to the indexed column or new record entry may rewrite to change the order they appear in the disk.

Any changes to the indexed column or new record entry have no effect on its rest of the data in the disk.

Records of the table are sorted and stored in the disk. Therefore, it does not require to store them in index lookup table.

Stores key value pair combination in the index lookup table.

35. What is Union, Minus and Intersect commands?

UNION: - This command is used to combine the records from two or more queries. Here all the queries should have same number of columns and same datatype. Otherwise the query will fail to return the result.

MINUS: - This command is used to find the records that are present in first query but not in the second query. Here also, all columns should have same numbers and datatypes.

INTERSECT: - This command is used to find the records that are present in both the tables and queries. Here also, all columns should have same numbers and datatypes.

Next >
Scroll to Top