01Apr 2022 by ganesh

SQL Interview Questions And Answers

In this article, We are going to learn and discussed about one of the most important questions asked on SQL and the answers to those questions. We are going to understand the most frequently asked interview questions on SQL Server. We are covering the top SQL Server questions as well as commonly asked SQL query while your interview.

Q 1. What is SQL

SQL stands for ‘Structured Query Language’ and is used for communicating with databases. It is the standard language for relational database management systems. SQL is used to retrieve and manipulate structured databases. SQL is used to perform the operations on the database:

Q 2.What are different types of SQL commands or subset of SQL

1. Data Definition Language (DDL)

DDL is used for creating and modifying database objects such as tables, indices, and users. It is mainly used to create and restructure database objects. Commands in DDL are:

- - Create: This command is used to create the database or its objects (like table, index, function, views, store procedure, and triggers).
  - Drop: This command is used to delete objects from the database.
  - Alter: This command is used to alter the object structure in the database.
  - Truncate: This command is used to remove all records from a table.
  - Rename: This command is used to rename an object which exists in the database.

2. Data Manipulation Language (DML)

The SQL data manipulation language (DML) is used to query and modify database data. It deals with the manipulation of data present in the database. It is used to perform operations such as:

- - Insert: It is used to insert data into a table.
  - Update: It is used to update existing data within a table.
  - Delete: It is used to delete records from a database table.

3. Data Query Language (DQL)

DQL statements are used for performing queries on the data within schema objects. The purpose of the DQL Command is to get some schema relation based on the query passed to it.

- - Select: It is used to retrieve or fetch data from the database.

4. Data Control Language (DCL)

DCL is used to control access to the data in the database. This command is used to give or remove access to the user for a particular object or operations.

- - Grant: This command gives access privileges to the users for a particular database.
  - Revoke: This command removes the user’s access privileges given by using the GRANT command.

5. Transaction Control Language(TCL)

TCL is used to manage changes that happen on an object and the commands for that.

- - Commit
  - Rollback
  - Savepoint
  - Begin
  - Transaction

Q 3. What is the difference between a Primary key, Unique Key & Foreign Key?

Primary Key	Unique Key	Foreign Key
A Primary key in SQL is a column that acts as a unique identifier for each record in the table.	The unique key is also a unique identifier for records when the primary key is not present in the table.	The foreign key is used to link one or more tables together and its know as known as the referencing key.
The primary key column does not allow the null value.	The unique key column allows the null value.	The foreign key column allows the null value.
It must contain UNIQUE values and has an implicit NOT NULL constraint	This provides uniqueness for the column(s) and helps identify each row uniquely.	The foreign key in the child table references the primary key in the parent table.
A table in SQL is strictly restricted to having one and only one primary key.	We can have multiple unique constraints defined per table.	We can have multiple foreign keys defined per table.
		The foreign key constraint prevents actions that would destroy links between the child and parent tables.

Q 4. What is a Join and Types of Joins?

The SQL Join clause is used to combine records from two or more tables in a SQL database based on a related column between the two tables. SQL Joins are used to merge two tables or retrieve data from tables. It depends on the relationship between tables. Below are the types of joins

1. - Inner Join: Inner join returns matching records from the tables based on specified conditions.
2. - Left Outer Join: Left Join in SQL is used to return all the rows from the left table but only the matching rows from the right table where the join condition is matched.
3. - Right Outer Join: Right Join in SQL is used to return all the rows from the right table but only the matching rows from the left table where the join condition is matched.
4. - Full Outer Join: Return all the records where there is a match in either the left or right table.
5. - Sefl Join: A self JOIN is a case of regular join where a table is joined to itself based on some relation between its own column(s). Self-join uses the INNER JOIN or LEFT JOIN clause and a table alias is used to assign different names to the table within the query.
6. - Cross Join: The cross join produces the cross product or Cartesian product of two tables

Q 5. What is an Index? What it's different types

An index is a data structure associated with a table or view that provides a quick lookup of data in a column or columns of a table. It reduces the cost of the query because the query’s high cost will lead to a fall in its performance. It is used to increase the performance and allow faster retrieval of records from the table.

There are different types of indexes that can be created for different purposes:

Unique indexes

These are indexes that help maintain data integrity by ensuring that no two rows of data in a table have identical key values. Once a unique index has been defined for a table, uniqueness is enforced whenever keys are added or changed within the index.

Non-unique indexes

Non-unique indexes, on the other hand, are not used to enforce constraints on the tables with which they are associated. Instead, non-unique indexes are used solely to improve query performance by maintaining a sorted order of data values that are used frequently.

Clustered Index

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

Non-Clustered Index

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on the disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Q 6. What is the difference between clustered and non-clustered indexes in SQL

Clustered Index	Non-Clustered Index
A clustered index is physically stored on a table or view where the data for the rows are stored. If the table column contains a primary key, MySQL automatically creates a clustered index named PRIMARY.	The indexes other than PRIMARY indexes (clustered indexes) are called non-clustered indexes.
Clustered indexes store the data information and the data itself on the same table or view.	Non-clustered indexes stores only the information, and then it will refer you to the data stored in clustered data.
We can have only one clustered index per table.	We can have one or more non-clustered indexes in a table.
A clustered index defines how data is stored physically in the table. reading from a clustered index is faster	It stores the data into sperate place. Therefore, reading from a clustered index is slower.
A clustered index always contains an index id of 0.	A non-clustered index always contains an index id>0.

Q 7. What is difference between DELETE & TRUNCATE commands?

DELETE	TRUNCATE
DELETE statement is used to delete rows from a table.	A TRUNCATE command is used to delete all the rows from the table
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause.	Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.	TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain.
DELETE Can be Rolled back.	TRUNCATE can not be Rolled back.
DELETE is DML Command.	TRUNCATE is DDL Command.
DELETE does not reset identity of the table.	TRUNCATE Resets identity of the table.

Q 8. Explain the difference between Functions and Stored Procedures?

Store Procedure	Functions
A stored procedure is a group of SQL statements that have been previously created , compiled, and stored in the server database.	SQL functions are simple code snippets that are frequently used and re-used in database systems for data processing and manipulation
Stored Procedure can return zero or n values.	Function must return single value
Stored Procedures can have input/output parameters	Functions can have only input parameters
Stored Procedures cannot be called from Function.	Functions can be called from Stored Procedure.
Exception can be handled by try-catch block in a stored procedure	Exception try-catch block cannot be used in a function
Stored Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it	Function allows only SELECT statement in it
Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section	Function can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section
We can use Transaction in Stored Procedure.	We can not use Transaction in Functions.
Store procedure can execute Dynamic SQL	Function cant not be execute Dynamic SQL

Q 9. What is View?

A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with.

it is a virtual table that contains a subset of data within a table. It looks like an actual table containing rows and columns, but it takes less space because it is not present physically. The results of using a view are not permanently stored in the database.

The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

The primary use of a view is to implement the security mechanism. It is the searchable object where we can use a query to search the view as we use for the table. It only shows the data returned by the query that was declared when the view was created.

Q 10. What is a Subquery? What are its types?

Sub-queries are often referred to as sub-selects. A subquery is a query within another query, also known as a nested query or inner query. A subquery SELECT statement can standalone and is not dependent on the statement in which it is nested.

A subquery SELECT statement can return any number of values and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement.

Types of subquery

- - Correlated Subquery: it cannot be considered an independent query, but it can refer to the column in a table listed in the FROM of the main query.
  - A non-Correlated Subquery: it can be considered as an independent query and the output of the subquery is substituted in the main query.

Properties of Sub-Query

- - A subquery must be enclosed in the parenthesis.
  - A subquery must be put in the right hand of the comparison operator, and
  - A subquery cannot contain an ORDER-BY clause.
  - A query can contain more than one sub-queries.

Q 11. What is a Recursive Stored Procedure?

A stored procedure that calls itself until a set condition is reached, is called a recursive stored procedure. This recursive function helps the programmers to deploy the same set of code several times as and when required. You can write stored procedures that call themselves.

Q 12. What is SQL Profiler?

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performance by executing too slowly.

Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time

Q 13. What are the different types default databases?

Master Database: It holds information for all databases located on the SQL Server instance and is the
glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.

msdb Database: It stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.

tempdb Database: It holds temporary objects such as global and local temporary tables and stored procedures.

model Database: It is essentially a template database used in the creation of any new user database created in
the instance.

Q 14. How would you improve a performance of an SQL Query?

You should clearly define your requirements before writing the query, enabling you to receive only the information you need, potentially reduce runtime, and optimize SQL queries.

Reduce Table Size

SQL table optimization is an essential part of MS SQL database query optimization. To ensure you only receive the information you need, you can filter your data. Filtering data will reduce table size and optimize SQL queries’ runtime.

Simplify Joins

Sometimes, when a query joins tables, it drastically increases the result set’s row count, which can lead to a slow execution time. Before joining tables, try to reduce their size, as explained above.

Use SELECT Fields FROM Instead of SELECT * FROM

By using SELECT fields FROM instead of SELECT * FROM, you can narrow down the data fetched from the table during a query, increasing your query’s speed. The command SELECT * will fetch all the data from your table, whereas specifying fields can reduce query runtime by ensuring you only receive the necessary data.

Use EXISTS() Instead of COUNT()

We can use both EXIST() and COUNT() to discover whether the table has a specific record, using EXIST() is more effective. While COUNT() will search the entire table to provide the total number of matching records, EXIST() will only run until it locates the record’s first entry in the table, saving you time and computing power and enabling you to optimize SQL queries.

Use WHERE Instead of HAVING

Another SQL query optimization technique is using WHERE instead of HAVING. WHERE queries execute more quickly than HAVING queries. WHERE queries filter records before groups are created while HAVING queries filter data from groups. As a result, using WHERE in place of HAVING is an easy strategy for SQL query optimization.

Create SQL Server Indexes

You can retrieve data faster and optimize SQL queries by using clustered and non-clustered SQL Server indexes. Indexes can reduce runtime, but it’s also important to consider how much disk space they require.

Avoid Running Queries in a Loop

Running queries in a loop can slow your runtime. In some cases, you may be able to bulk insert and update data, which is far more efficient than using loops.

Q 15. What are Aggregate and Scalar functions?

An aggregate function performs operations on a collection of values to return a single scalar value

- - AVG() – Calculates the mean of a collection of values.
  - COUNT() – Counts the total number of records in a specific table or view.
  - MIN() – Calculates the minimum of a collection of values.
  - MAX() – Calculates the maximum of a collection of values.
  - SUM() – Calculates the sum of a collection of values.
  - FIRST() – Fetches the first element in a collection of values.
  - LAST() – Fetches the last element in a collection of values.

A scalar function returns a single value based on the input value. Following are the widely used SQL scalar functions:

- - LEN() – Calculates the total length of the given field (column).
  - UCASE() – Converts a collection of string values to uppercase characters.
  - LCASE() – Converts a collection of string values to lowercase characters.
  - MID() – Extracts substrings from a collection of string values in a table.
  - CONCAT() – Concatenates two or more strings.
  - RAND() – Generates a random collection of numbers of a given length.
  - ROUND() – Calculates the round-off integer value for a numeric field (or decimal point values).
  - NOW() – Returns the current date & time.
  - FORMAT() – Sets the format to display a collection of values.

Q 16. What is the difference between a local and a global variable?

A local temporary table exists only for the duration of a connection or if defined inside a compound statement, for the duration of the compound statement.

A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connections are closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when the database is opened next time.

Q 17. How do you load large data to the SQL server database?

BulkCopy is a tool used to copy a huge amounts of data from tables. BULK INSERT command helps to
Imports a data file into a database table or view in a user-specified format.

Q 18. What is a Scheduled Jobs or What is a Scheduled Tasks?

Scheduled tasks let users automate processes that run on regular or predictable cycles. Users can schedule administrative tasks, such as cube processing, to run during times of slow business activity.

Users can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. E.g. Back up the database, Update the Stats of Tables. Job steps give users control over the flow of execution. If one job fails, the user can configure SQL Server Agent to continue to run the remaining tasks or to stop
execution.

Q 19. What command do we use to rename a db

We can use sp_renamedb ‘oldname’ , ‘newname’

Q 20. What is Raiseerror?

Stored procedures report errors to client applications via the RAISERROR command. RAISERROR doesn’t change the flow of a procedure; it merely displays an error message, sets the @@ERROR automatic variable, and optionally writes the message to the SQL Server error log and the NT application event log.

Mostly Asked SQL Queries

Q 1. How to know which index a table is using?

we can use get the details table name and index name from user constraints table

SELECT table_name,index_name FROM user_constraints

Q 2. Write an SQL query to fetch the number of empoyees working in department ‘Dev’.?

we can use the aggregate function count() with the SQL WHERE clause.

SELECT COUNT(*) FROM Employees WHERE Department = 'D1';

Q 3. Write an SQL query for fetching duplicate records from a table?

To find duplicate records from the table, we can use GROUP BY clause on all the fields and then we have to use HAVING clause to return only those fields whose count is greater than one, i.e. the rows having duplicate records.

SELECT EmployeeID, Department, COUNT() FROM Employeed GROUP BY EmployeeID, Department HAVING COUNT() > 1;

Q 4. Write an SQL query for creating an empty table with the same structure as some other table.?

We can use MySQL LIKE clause with CREATE statement.

CREATE TABLE newTable LIKE Employees;

Q 5. Write the SQL query to find the nth highest salary from the table.