SQL Most Important Interview Questions You Must Prepare In 2024

This article will help you to prepare for SQL Interviews. basically this PL SQL Most Important Interview Questions and answers is prepared for freshers and experienced candidates.

Q. What is SQL?

SQL is Structured Query Language designed for inserting and modifying in a relational database management system.

Q. What are the differences between DDL, DML and DCL in SQL?

• DDL stands for Data Definition Language. SQL queries like CREATE, ALTER, DROP and RENAME come under this.
• DML stands for Data Manipulation Language. SQL queries like SELECT, INSERT and UPDATE come under this.
• DCL stands for Data Control Language. SQL queries like GRANT and REVOKE come under this.

Q. What is Join?

An SQL Join is used to combine data from two or more tables, based on a common field between them. For example, consider the following two tables.

Student Table

StudentCourse Table

Following is join query that shows names of students enrolled in different courseIDs.

SELECT StudentCourse.CourseID, Student.StudentName
 FROM StudentCourse
 INNER JOIN Customers
 ON StudentCourse.EnrollNo = Student.EnrollNo
 ORDER BY StudentCourse.CourseID;

The above query would produce following result.

Q. What is Identity?

Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers; the value of this cannot be controlled. Identity/GUID columns do not need to be indexed.

Q. What is a view in SQL? How to create one

A view is a virtual table based on the result-set of an SQL statement. We can create using create view syntax.

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Q. What are the uses of view?

• Views can represent a subset of the data contained in a table; consequently, a view can
  limit the degree of exposure of the underlying tables to the outer world: a given user may
  have permission to query the view, while denied access to the rest of the base table.
• Views can join and simplify multiple tables into a single virtual table
• Views can act as aggregated tables, where the database engine aggregates data (sum, average etc.) and presents the calculated results as part of the data
• Views can hide the complexity of data; for example a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table.
• Depending on the SQL engine used, views can provide extra security.

Also, check What is a Resume and Why it is So Important?

Q. What is a Trigger?

A Trigger is a code that associated with insert, update or delete operations. The code is executed automatically whenever the associated query is executed on a table. Triggers can be useful to maintain integrity in database.

Q. What is a stored procedure?

• A stored procedure is like a function that contains a set of operations compiled together. It contains a set of operations that are commonly used in an application to do some common database tasks.

Also, check Why Am I Not Getting Interview Calls?

Q. What is the difference between Trigger and Stored Procedure?

Unlike Stored Procedures, Triggers cannot be called directly. They can only be
associated with queries

Q. What is a transaction? What are ACID properties?

• A Database Transaction is a set of database operations that must be treated as whole, means either all operations are executed or none of them.
• An example can be bank transaction from one account to another account. Either both debit and credit operations must be executed or none of them.
• ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably.

Also, check 17 Essential Manual Testing Interview Questions and Answers (Updated 2024)

Q. What are indexes?

• A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data.
• Data can be stored only in one order on disk. To support faster access according to different values, faster search like binary search for different values is desired, For this purpose, indexes are created on tables.
• These indexes need extra space on disk, but they allow faster search according to different frequently searched values.

Q. What are the Advantages of SQL?

1. SQL is not a proprietary language used by specific database vendors. Almost every major DBMS supports SQL, so learning this one language will enable programmer to interact with any database like ORACLE, SQL ,MYSQL etc.
2. SQL is easy to learn. The statements are all made up of descriptive English words, and there aren’t that many of them.
3. SQL is actually a very powerful language and by using its language elements you can perform very complex and sophisticated database operations.

Also, check Top 11 Automation Testing Interview Questions and Answers

Q. what is a field in a database ?

A field is an area within a record reserved for a specific piece of data.
Examples: Employee Name , Employee ID etc

Q. What is a Record in a database ?

A record is the collection of values / fields of a specific entity: i.e. a Employee , Salary etc.

Q. What is a Table in a database ?

A table is a collection of records of a specific type. For example, employee table , salary table etc.

Q. What is a database transaction?

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

Also, check Most Important Tosca Interview Questions and Answers

Q. What are properties of a transaction?

Properties of the transaction can be summarized as ACID Properties.

1. Atomicity
   A transaction consists of many steps. When all the steps in a transaction gets completed, it will get reflected in DB or if any step fails, all the transactions are rolled back.
2. Consistency
   The database will move from one consistent state to another, if the transaction succeeds and remain in the original state, if the transaction fails.
3. Isolation
   Every transaction should operate as if it is the only transaction in the system
4. Durability
   Once a transaction has completed successfully, the updated rows/records must be available for all other transactions on a permanent basis

Q. What is a Database Lock ?

Database lock tell a transaction, if the data item in questions is currently being used by other transactions.

Also, check Most Asked Performance Testing Interview Questions Jmeter

Q. What are the type of locks ?

1. Shared Lock
   When a shared lock is applied on data item, other transactions can only read the item, but can’t write into it.
2. Exclusive Lock
   When a exclusive lock is applied on data item, other transactions can’t read or write into the data item.

Q. What are the different type of normalization?

In database design , we start with one single table, with all possible columns. Lot of redundant data would be present since it’s a single table. The process of removing the redundant data, by splitting up the table in a well defined fashion is called normalization.

1. First Normal Form (1NF)
   A relation is said to be in first normal form if and only if all underlying domains contain atomic values only. After 1NF, we can still have redundant data
2. Second Normal Form (2NF)
   A relation is said to be in 2NF if and only if it is in 1NF and every non key attribute is fully dependent on the primary key. After 2NF, we can still have redundant data
3. Third Normal Form (3NF)
   A relation is said to be in 3NF, if and only if it is in 2NF and every non key attribute is nontransitively dependent on the primary key

Also, check Selenium Interview Questions and Answers

Q. What is a primary key?

A primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. If a row is deleted from the table, its primary key may not be assigned to any new rows in the future. To define a field as primary key, following conditions had to be met :

1. No two rows can have the same primary key value.
2. Every row must have a primary key value
3. Primary key field cannot be null
4. Values in primary key columns can never be modified or updated

Q. What is a Composite Key ?

A Composite primary key is a type of candidate key, which represents a set of columns whose values uniquely identify every row in a table.
For example – if “Employee_ID” and “Employee Name” in a table is combined to uniquely identifies a row its called a Composite Key

Also, check 6 Quick Tips For How To Sell Yourself And Advance Your Career

Q. What is a Composite Primary Key ?

A Composite primary key is a set of columns whose values uniquely identify every row in a table. What it means is that, table which contains composite primary key will be indexed based on columns specified in the primary key. This key will be referred in Foreign Key tables.
For example – if combined effect of columns, “Employee_ID” and “Employee Name” in a table is required to uniquely identifies a row, its called a Composite Primary Key. In this case, both the columns will be represented as primary key.

Q. What is a Foreign Key ?

When a “one” table’s primary key field is added to a related “many” table in order to create the common field which relates the two tables, it is called a foreign key in the “many” table.
For example, salary of an employee is stored in salary table. Relation is established via foreign key column “Employee_ID_Ref” which refers “Employee_ID” field in Employee table

Also, check What questions are asked in software testing interview?

Q. What is a Unique Key ?

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

Q. .Define SQL Insert Statement ?

SQL INSERT statement is used to add rows to a table. For a full row insert , SQL Query should start with “insert into “ statement followed by table name and values command, followed by the values that need to be inserted into the table. Insert can be used in several ways:

1. To insert a single complete row
2. To insert a single partial row

Q. Define SQL Update Statement ?

SQL Update is used to update data in a row or set of rows specified in the filter condition. The basic format of an SQL UPDATE statement is ,Update command followed by table to be updated and SET command followed by column names and their new values followed by filter condition that determines which rows should be updated

Also, Read Core Java Interview Questions and Answers

Q. Define SQL Delete Statement ?

SQL Delete is used to delete a row or set of rows specified in the filter condition.
The basic format of an SQL DELETE statement is, DELETE FROM command followed by table name followed by filter condition that determines which rows should be updated.

Q. What is the difference between the “DELETE” and “TRUNCATE” commands?

• The DELETE command is used to remove rows from a table based on a WHERE condition whereas TRUNCATE removes all rows from a table.
• So we can use a where clause with DELETE to filter and delete specific records whereas we cannot use a Where clause with TRUNCATE.
• DELETE is executed using a row lock, each row in the table is locked for deletion whereas TRUNCATE is executed using a table lock and the entire table is locked for removal of all records.
• DELETE is a DML command whereas TRUNCATE is a DDL command.
• DELETE retains the identity of the column value whereas in TRUNCATE, the Identify column is reset to its seed value if the table contains any identity column.
• To use Delete you need DELETE permission on the table whereas to use Truncate on a table you need at least ALTER permission on the table.
• DELETE uses more transaction space than the TRUNCATE statement whereas
  Truncate uses less transaction space than DELETE statement.
• DELETE can be used with indexed views whereas TRUNCATE cannot be used with indexed views.
• The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row whereas TRUNCATE TABLE removes the data
  by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
• Delete activates a trigger because the operation is logged individually whereas TRUNCATE TABLE can’t activate a trigger because the operation does not log individual row deletions.

Q. What is the difference between “Stored Procedure” and “Function”?

• A procedure can have both input and output parameters, but a function can only have input parameters.
• Inside a procedure we can use DML (INSERT/UPDATE/DELETE) statements. But inside a function we can’t use DML statements.
• We can’t utilize a Stored Procedure in a Select statement. But we can use a function in a Select statement.
• We can use a Try-Catch Block in a Stored Procedure but inside a function we can’t use a Try-Catch block.
• We can use transaction management in a procedure but we can’t in a function.
• We can’t join a Stored Procedure but we can join functions.
• Stored Procedures cannot be used in the SQL statements anywhere in the
  WHERE/HAVING/SELECT section. But we can use a function anywhere.
• A procedure can return 0 or n values (max 1024). But a function can return only 1 value that is mandatory.
• A procedure can’t be called from a function but we can call a function from a procedure.

Q. What is difference between “Clustered Index” and “Non Clustered Index”?

• A Clustered Index physically stores the data of the table in the order of the keys values and the data is resorted every time whenever a new value is inserted or a value is updated in the column on which it is defined, whereas a non-clustered index creates a separate list of key values (or creates a table of pointers) that points towards the location of the data in the data pages.
• A Clustered Index requires no separate storage than the table storage. It forces the rows to be stored sorted on the index key whereas a non-clustered index requires
  separate storage than the table storage to store the index information.
• A table with a Clustered Index is called a Clustered Table. Its rows are stored in a BTree structure sorted whereas a table without any clustered indexes is called a nonclustered table. Its rows are stored in a heap structure unsorted.
• The default index is created as part of the primary key column as a Clustered Index.
• In a Clustered Index, the leaf node contains the actual data whereas in a nonclustered index, the leaf node contains the pointer to the data rows of the table.
• A Clustered Index always has an Index Id of 1 whereas non-clustered indexes have Index Ids > 1.
• A Table can have only 1 Clustered Index whereas prior to SQL Server 2008 only 249 non-clustered indexes can be created. With SQL Server 2008 and above 999 nonclustered indexes can be created.
• A Primary Key constraint creates a Clustered Index by default whereas A Unique Key constraint creates a non-clustered index by default.

Q. What is the difference between the “WHERE” clause and the “HAVING” clause?

• WHERE clause can be used with a Select, Update and Delete Statement Clause but the HAVING clause can be used only with a Select statement.
• We can’t use an aggregate functions in the WHERE clause unless it is in a sub-query contained in a HAVING clause whereas we can use an aggregate function in the HAVING clause. We can use a column name in the HAVING clause but the column must be contained in the group by clause.
• WHERE is used before the GROUP BY clause whereas a HAVING clause is used to impose a condition on the GROUP Function and is used after the GROUP BY clause in the query.
• A WHERE clause applies to each and every row whereas a HAVING clause applies to summarized rows (summarized with GROUP BY).
• In the WHERE clause the data that is fetched from memory depending on a condition whereas in HAVING the completed data is first fetched and then separated depending on the condition.

Q. What is the difference between “Primary Key” and “Unique Key”?

• We can have only one Primary Key in a table whereas we can have more than one Unique Key in a table.
• The Primary Key cannot have a NULL value whereas a Unique Key may have only one null value.
• By default, a Primary Key is a Clustered Index whereas by default, a Unique Key is a unique non-clustered index.
• A Primary Key supports an Auto Increment value whereas a Unique Key doesn’t support an Auto Increment value.

Q. What is the difference between a “Local Temporary Table” and “Global Temporary Table”?

• A Local Temporary Table is created by giving it a prefix of # whereas a Global Temporary Table is created by giving it a prefix of ##.
• A Local Temporary Table cannot be shared among multiple users whereas a Global Temporary Table can be shared among multiple users.
• A Local Temporary Table is only available to the current DB connection for the current user and are cleared when the connection is closed whereas a Global Temporary Table is available to any connection once created. They are cleared when the last connection is closed.

Q. What are super, primary, candidate and foreign keys?

• A super key is a set of attributes of a relation schema upon which all attributes of the schema are functionally dependent. No two rows can have the same value of super key attributes.
• A Candidate key is minimal super key, i.e., no proper subset of Candidate key attributes can be a super key.
• A Primary Key is one of the candidate keys. One of the candidate keys is selected as most important and becomes the primary key. There cannot be more that one primary keys in a table.
• Foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table.

Q. What is the difference between primary key and unique constraints?

Primary key cannot have NULL value, the unique constraints can have NULL values.
There is only one primary key in a table, but there can be multiple unique constrains

Q. What is database normalization?

It is a process of analyzing the given relation schemas based on their functional
dependencies and primary keys to achieve the following desirable properties:
1) Minimizing Redundancy
2) Minimizing the Insertion, Deletion, And Update Anomalies
Relation schemas that do not meet the properties are decomposed into smaller relation schemas that could meet desirable properties.

Frequently Asked Questions on SQL

For Reference check