TOP 20 SQL interview questions for 2025

 

TOP 20 SQL interview questions for 2025

Hey Candidates here are the Top 20 SQL, that will help you crack the SQL interview

1. What is SQL?

SQL (Structured Query Language) is a standard language for accessing and manipulating databases. It is used for querying, updating, and managing data stored in a relational database.

2. What are the differences between SQL and MySQL?

• SQL is a standard language for accessing and manipulating databases, while MySQL is a relational database management system (RDBMS) that uses SQL as its standard database language.
• SQL is a query language, and MySQL is software for managing databases.
• SQL commands are used in various database systems, whereas MySQL is a specific implementation of an RDBMS.

3. What are DDL and DML commands? Give examples.

• DDL (Data Definition Language) commands are used to define the database structure. Examples include CREATE, ALTER, and DROP.
• DML (Data Manipulation Language) commands are used for managing data within the database. Examples include SELECT, INSERT, UPDATE, and DELETE.

4. Write a query to retrieve all records from a table.

SELECT * FROM table_name;

5. How do you Join 2 tables

Tables can be joined using various types of joins, such as INNER JOIN, LEFT JOIN, RIGHT JOIN, 

and FULL JOIN. Here's an example using INNER JOIN:
SELECT *
FROM table1
INNER JOIN table2 ON table1.common_column = table2.common_column;

6. What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN?

• INNER JOIN returns records that have matching values in both tables.
• LEFT JOIN returns all records from the left table and matched records from the right table. If no match, NULL values are returned for columns of the right table.
• RIGHT JOIN returns all records from the right table and matched records from the left table. If no match, NULL values are returned for columns of the left table.
• FULL JOIN returns all records when there is a match in either left or right table. If there is no match, the result is NULL from the side where there is no match.

7. Explain the concept of normalization. 

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller tables and defining relationships between them. Common forms of normalization are 1NF, 2NF, 3NF, and BCNF.

8. What is indexing? How does it improve query performance? 

Indexing involves creating a data structure (index) that improves the speed of data retrieval operations on a database table. Indexes provide a quick lookup for matching rows in a table based on indexed columns, thus reducing the time required for data access.

9. Write a query to find the second highest salary from an Employee table.

SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);

10. What is a primary key and a foreign key?
A primary key is a column (or a combination of columns) that uniquely identifies each row in a table.
A foreign key is a column (or a combination of columns) that establishes a relationship between two 

tables by referencing the primary key of another table.

11. Explain the difference between DELETE and TRUNCATE.

DELETE is a DML command used to delete specific rows from a table based on a condition.

 It can be rolled back.

TRUNCATE is a DDL command used to remove all rows from a table, resetting it to an empty state. 

It cannot be rolled back and is faster than DELETE.

12. What is a subquery? Provide an example.
A subquery is a query within another query. It is used to return data that will be used in the main query 

as a condition.

SELECT employee_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments
WHERE department_name = 'Sales');

13. How do you handle transactions in SQL?
Transactions are handled using the following commands:
BEGIN TRANSACTION: Starts a transaction.
COMMIT: Saves all changes made during the transaction.
ROLLBACK: Reverts all changes made during the transaction.
14. What are stored procedures and functions?
A stored procedure is a set of SQL statements that can be executed as a single unit. It can perform 
actions such as data modification and return multiple values.
A function is a set of SQL statements that perform a specific task and return a single value. 
Functions are typically used for calculations and data manipulation.

15. Explain the concept of ACID properties in databases.
ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure reliable
 processing of database transactions:
Atomicity: Ensures that all operations within a transaction are completed; if not, the transaction
 is aborted.
Consistency: Ensures that the database remains in a consistent state before and after a transaction.
Isolation: Ensures that transactions are isolated from each other, preventing concurrent 
transactions from interfering with each other.
Durability: Ensures that once a transaction is committed, it remains permanent, even in the case 
of a system failure.
16. What is the difference between a VIEW and a TABLE?
A TABLE is a database object that stores data in rows and columns.
A VIEW is a virtual table based on the result set of a SQL query. It does not store data 
physically but provides a way to simplify complex queries and enhance security by restricting access 
to specific data.
17. How do you optimize SQL queries?
Use indexes to speed up data retrieval.
Avoid using SELECT * and only select required columns.
Use JOINs instead of subqueries when possible.
Write efficient WHERE clauses to filter data early.
Analyze query execution plans to identify performance bottlenecks.
18. What is the difference between DISTINCT and GROUP BY?
DISTINCT is used to remove duplicate rows from a result set.
GROUP BY is used to group rows that have the same values in specified columns and perform
 aggregate functions (e.g., COUNT, SUM) on them.

19. Explain the concept of database normalization.
Normalization is the process of organizing data in a database to reduce redundancy and improve 
data integrity. It involves dividing large tables into smaller tables and defining relationships between 
them. Common forms of normalization are 1NF, 2NF, 3NF, and BCNF.

20. What is the purpose of the ORDER BY clause?
The ORDER BY clause is used to sort the result set of a query by one or more columns. 
It can be used to sort data in ascending (ASC) or descending (DESC) order.

BEST LUCK !!!

Find out who Delete your data from SQL table

Find out who Delete your data from SQL table

Have you lost your data from SQL table ? Have you lost your SQL table ? want to know who ran DELETE or DROP Query on your database ? Then go through this step by step article, it will help you to find the culprit

Introduction

Last month my friend has called me and said, some has delete his important data from SQL table and now no one confess it. He asked me to search a way to find culprit. After couple of here and there i got a way and now want to share with you. So, let's enjoy this article.

This article will help you find the user who fire DELETE or DROP statement on your table or database

Things we need

To search culprit, we need to read transaction log entries of database. Yes...You heard is right, you can read SQL transaction log data (i.e. LDF file).  let's begin with the steps

1.  We will create some sample table with data
2. Delete rows from it
3. Try to track the user who delete (soft or hard) data entries (Here Soft Delete means delete records using Query and Hard delete indicates delete data using 'DEL' button (or may be with mouse) from SQL table directly)

LDF :
         (Those who don't know what is LDF) LDF is a file extension for log data files these files are exist with MDF files (which contains actual data). LDF file store all transactions with time stamp and help to recover database in case of data loss.

         Now, to read LDF file we need to use 'fn_dblog' function, (which is undocumented function of SQL), after executing this function on particular database you will able to see live transaction logs and operations executed on that database.

Let's create sample database and table, with the help of following Query

CREATE DATABASE [Sample] ON  PRIMARY
( NAME = N'Sample_dat', FILENAME = N'D:\Sample\Sample.mdf' , SIZE = 13760KB , MAXSIZE = UNLIMITED, 
FILEGROWTH = 10%)
 LOG ON
( NAME = N'Sample_log', FILENAME = N'D:\Sample\Sample.ldf' , SIZE = 9216KB , MAXSIZE = UNLIMITED, 
FILEGROWTH = 10%)
GO

sample table:

USE [Sample]
GO
CREATE TABLE [Emp] (
    [No] INT ,
    [Name] VARCHAR (50),
    [Address] VARCHAR (50)
);

Now let's check what has been recorded in LDF logs

run 'fn_dblog' function in Sample database

select * from fn_dblog(null,null)

If you see above result pane, there are almost 35 rows are recorded for just CREATE DATABASE and CREATE TABLE script

Let's insert some rows in table

Insert into Emp values(1,'name1', 'address1')
Insert into Emp values(2,'name2', 'address2')
Insert into Emp values(3,'name3', 'address3')
Insert into Emp values(4,'name4', 'address4') 

Track DELETE Activity

Now, just go and delete all rows from database, use below simple query

Delete from Emp

Our Emp table is now empty as, we have delete all the queries

Let's examine the log table, having operation type is 'LOP_DELETE_ROWS', fire fn_dblob function again and see what you get

select * from fn_dblog(null,null) where Operation = 'LOP_DELETE_ROWS'

Result:



Above result pane show us, all the transaction rows which are having 'DELETE' entries on specific database table, you need to search for the your 'specific' table (from where you have lost your data), check out column 'AllocUnitName', this column contains your table name on which 'DELETE' statement has fired.

In our case, Table name is 'Emp', now get the transaction ID for that particular 'table' entry record, execute below query to get record of particular table

select Operation, [Transaction ID], AllocUnitName,  * from fn_dblog(null,null) 
where Operation = 'LOP_DELETE_ROWS' and allocUnitName = 'dbo.emp'

Result:



in our case, transaction ID is same, as all entries are deleted with single 'DELETE' statement (at once) (e.g.  0000:0000079f)

with the help of above transaction ID, we will find when our entries are deleted from database. for that purpose we need to search record with operation LOP_BEGIN_XACT, fire below query on database

select  [Operation], [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
 
FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = '0000:0000079f' AND [Operation] = 'LOP_BEGIN_XACT'

above query will give you Start time of the transaction

Now we got the exact time when someone fire DELETE query on database, to know the activity End Time, you can try below query

SELECT
   [Begin Time], [End Time]
FROM
    fn_dblog(NULL, NULL)
WHERE
[transaction id] = '0000:000007a1' and [Operation] = 'LOP_BEGIN_XACT' or [operation] = 'LOP_COMMIT_XACT'

Here is the result of above query



Now let's find who is the culprit, we will find the real database user who fire delete query

Transaction SID column contains encrypted Hexa decimal text which is nothing but the user name who fire 'Delete' query

Fire below query to get [Transaction SID] column with the help of Transaction ID and Operation = 'DELETE'

select  [Operation], [Transaction Name], [Transaction SID]
FROM fn_dblog(NULL, NULL) where [Transaction ID] = '0000:000007a1' and [Transaction Name]='DELETE'

Output of above query is



Just you need to copy, encrypted hexadecimal contents from [Transaction SID] column and execute below query on master database, as per our result my Hexadecimal string is 0x01

SELECT SUSER_SNAME(0x01)

**SUSER_SNAME is the inbuilt function, it just checks security identification number (SID) and back with the login name associated it.

when i run above query i got below output



Yes...we finally got real culprit who fire Delete query

Track DROP activity

Similarly, if anyone DROP your table from database we can track that activity by using following queries,
Let's Drop table with below simple query

Drop table Emp

Now track activity using Transaction Name 'DROPOBJ'
check below query

SELECT [Transaction Name], Operation, [Transaction Id], [Transaction SID],  [Begin Time] 
FROM fn_dblog (NULL, NULL) WHERE [Transaction Name] = 'DROPOBJ'

i got below result


Same as DELETE scenario execute below query on master database, as per our result Hexadecimal string is 0x01

SELECT SUSER_SNAME(0x01)

which is the same user 'sa'

So, to conclude

SQL store all its transactions in log table, we can read transaction log file using fn_dblob function, we can do more research on each transaction with the help of this function.  All transactions are logged with different operations,  With the help of SUSER_SNAME function we can easily trace out encrypted user name.

In my next article i will cover deep dive points on 'Reading Transaction Log of SQL (LDF)', so Please stay tuned

and Enjoy this article
**DO NOT alter enties of fn_dblog or DO NOT run these command on production unless you have backup.

*Suggestions and comments are always welcome

-Happy Tracing
Koolprasadd

FIX : A network-related or instance-specific error occurred while establishing a connection to SQL

In this article i explained you to Fix SQL error occurred while establishing a connection to remote computer, so we can say we can resolve error "Network-related or instance-specific error occurred while establishing a connection to SQL Server [2005/2008/2012]"

Background

Couple of years back when i was not much familier with SQL, i try to connect to my SQL Server from my client. I put servername and try to login with SQL Server authentication with user name and password and i got error "A network-related or instance-specific error occurred while establishing a connection to SQL Server...", after spending couple of hours on it i was able to get rid of it, this article is for those who got same error and still they are put their head in SQL to resolve the issue.

Introduction

Many times we are trying to connect to SQL server with SQL management studio either by express version or by enterprise version and we got following error
"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) "
Above error is really headache for sql users.
Here is the snap of error

Fix it

To fix the issue we might need to do some configuration changes in SQL management studio (Here i use SQL 2008 for it)

Allow remote connections 
The very first thing you want to check is, your Remote Connections are enabled on your SQL Server database, to check it go through below steps
1. Select SQL server on object explorer -> Right click and click on properties -> select 'connections' -> under Remote server connections sections -> check 'Allow Remote connections to this server'
see below snap

This is the basic and simple check it may resolve your error, if not then continue with this article

Enable TCP/IP protocol
Next step is the enable the TCP/IP, just navigate to 'the SQL Server Configuration Manager' -> in left pane Open 'SQL server network configuration' -> select protocol for SQLEXPRESS in right pane select TCP/IP and make it 'enable'
see below snap

This is one of the helpful step and resolve many users issue, if the problem still continues then continue with this article

Handle Firewall port
Many times your firewall security settings does not allow SQL to pass through, you need to handle that firewall port to work with SQL stuff. To allow in firewall there need to configure couple of settings 
1. set 1433 port in TCP/IP proerpties
2. set Inbound and Outbound rules in windows firewall settings (Applicable for window 7 and above operating system)

set TCP port 1433 in configuration window as below snap shows

Now to set Inbound and Outbound rule we need to navigate to windows firewall, Just Open the Control Panel and navigate to Windows Firewall.
Click on Advanced Settings on the left hand side and you should see the Windows Firewall with Advanced Security. Select the Inboud Rules on the left hand side and click on New Rule… on the right hand side.
go through the following snap sequence it will help you to enable and set the windows firewall settings
Open 'windows firewall with advanced security' screen, select 'Inbound Rule' and click on 'New Rule', see below snap

New Inbound wizard screen' will open, Now in left hand side of the window select 'Rule Types', in right hand side select 'Port' now click 'Next'

In protocol and ports section select 'specific local ports' and give port number as '1433' (default sql port), now click 'Next'

In action section, select 'Allow the connection', and click on Next

In Name section give name and description of the rule, which help us to identify the rule in firewall, now click on Finish

Yes, we are almost done with the settings now close SQL server and restart it again and try to connect, it will resolve your issue.

Summing up

Above error is very basic Many time occur due to different conditions, if you have database installed on SQL 2008 and if you try to connect it with SQL 2005 management console then also same error occurred, when you have install a new instance of SQL server on machine then also this error arise, above are some settings that help you to get rid of the error

Suggestion and Queries most welcome

Thanks

Prasad

Tricky SQL Server Interview Question and answers

Are you preparing for a SQL server interview? want some tricky questions and answers that will give you break through ? Then you are at the correct place. Here i have some bunch of tricky SQL Server interview questions and answers that will really help you to go through the interview

1.With is the Sixth normal form in SQL server? - Ohhh...You may think that, i have heard about first to forth only but what is this SIXTH? Friends, do not try to impress the interviewer just skip this question.
Sixth normal form does exist but use it when you want a relational system in conjunction with time. At this moment SQL Server does not support it directly.

2. In which Files does SQL Server Actually Store Data? - SQL server has 2 data files associated with it
1. MDF : Which is actual data file storage
2. LDF : (Log data files) which stores transaction log

3. Do you know How many locks are exist in SQL Server ? - We have these many locks exist here, see below
1. Intent
2. Shared
3. Update
4. Exclusive
5. Schema
6. Bulk Update

4.What is SQL Profiler?- It is a tool that allows administrator to monitor different events and transaction of SQL server instance, You can capture and save data about each event to a file or SQL Server table to analyze later
it is not available with EXPRESS edition

5. Can i insert into a table having just one IDENTITY column?- Yes you can, Use following Query

INSERT INTO TABLE1 DEFAULT VALUES;

6. How to drop primary key from a column using Query
- See below query to drop primary key constraint

ALTER TABLE Table1 DROP CONSTRAINT PK_Table1_Col1

7. What is the difference between a DDL trigger and a DML trigger?- DDL commands are always deal with table structures, these command can update/modify table structure (CREATE, ALTER, DROP)
DMS commands are always deal with data/records not with table structure (INSERT, UPDATE, DELETE)

8. What the difference between UNION and UNIONALL?
- Union will remove the duplicate rows from the result set while UNIONALL does not remove them. So to avoid data redundancy you need to use UNION

9. What is the clustered and a non-clustered index?
- Clustered index is the physical index that reorders the way records in the table are physically stored, In non-clustered index logical order of the index does not match the physical stored order of the rows on disk

10. What's the difference between a primary key and a unique key?
- Both keys are not allow duplicate entries but primary key create a cluster index where as unique key create a non clustered index. Secondly primary key doesn't allow NULLs, but unique key allows one NULL only.
11. How to change the data type of a column- The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
Here is the query to change column datataype
ALTER TABLE table_name ALTER COLUMN column_name data type

12. How to check the version of SQL server and operating system?
- With the help of the following query we can get it
SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition').

13. What is CHECK Constraint?
- CHECK Constraint used to limit the values that can be placed in a column

14. How can you delete duplicate records in a table where there is no primary key?
- You can take Use of the SET ROWCOUNT command. Here if you had 2 duplicate rows you would issue SET ROWCOUNT 1, then your DELETE command then SET ROWCOUNT 0.
15. What is NOT NULL Constraint?- This constraint force the column to not accept NULL values

16. How to copy data from one table to another table?
- With the help of 'INSERT INTO SELECT' or 'SELECT INTO' queries you can copy data from a datatable to another

17. What is PIVOT
- To automatically sort, count, and total the data stored in one table we use PIVOT. It will also rotate table as rows to columns and vice versa

18. What are DBCC commands?
- These are the Database Consistency Checker commands; They will Check disk allocation consistency, Display information about recent transactions.
19. Find the 3rd MAX salary in the emp table

Select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);

20. What is the difference between Trigger and Stored Procedure?- Triggers cannot be called directly they need to associated with different quires

Finisher
Suggestion/Queries always welcome

Happy Interview
- Prasad