Sapana Pal

Posted on Nov 18

Master SQL with 100 Challenging Questions: Test Your Database Skills

#beginners #sql #sqlquestions #interview

Certainly! Here are 100 SQL questions covering a wide range of topics, from basic to advanced, to help you practice and improve your SQL skills:

1. What is SQL?

SQL (Structured Query Language) is a standardized programming language used to manage and manipulate relational databases. It allows you to create, retrieve, update, and delete data within a database.

2. What are the different types of SQL commands?

SQL commands are broadly categorized into:

DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE
DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
DCL (Data Control Language): GRANT, REVOKE
TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

3. Write a query to retrieve all records from a table called `Employees`.

SELECT * FROM Employees;

4. How do you select specific columns from a table?

Suppose you want to select only EmployeeID and FirstName:

SELECT EmployeeID, FirstName FROM Employees;

5. How do you filter records in SQL?

Use the WHERE clause:

SELECT * FROM Employees WHERE Salary &gt; 50000;

6. Write a query to find employees with a salary greater than 50,000.

SELECT * FROM Employees WHERE Salary &gt; 50000;

7. How do you sort query results?

Use the ORDER BY clause:

SELECT * FROM Employees ORDER BY Salary DESC; -- Highest salary first

8. Write a query to retrieve the top 5 highest-paid employees.

SELECT * FROM Employees ORDER BY Salary DESC LIMIT 5; -- For MySQL
-- For SQL Server
SELECT TOP 5 * FROM Employees ORDER BY Salary DESC;

9. How do you perform a case-insensitive search?

In many SQL databases, string comparisons are case-insensitive by default. If not, use functions like LOWER():

SELECT * FROM Employees WHERE LOWER(FirstName) = LOWER('john');

10. What is the difference between `WHERE` and `HAVING`?

WHERE filters rows before grouping.
HAVING filters groups after GROUP BY.

Example:

-- Using WHERE
SELECT * FROM Employees WHERE Salary &gt; 50000;

-- Using HAVING
SELECT Department, COUNT(*) FROM Employees GROUP BY Department HAVING COUNT(*) &gt; 10;

11. Write a query to find the total number of employees.

SELECT COUNT(*) AS TotalEmployees FROM Employees;

12. How do you count the number of records in a table?

Same as above:

SELECT COUNT(*) FROM Employees;

13. Write a query to find the minimum and maximum salary.

SELECT MIN(Salary) AS MinSalary, MAX(Salary) AS MaxSalary FROM Employees;

14. How do you calculate the average salary?

SELECT AVG(Salary) AS AverageSalary FROM Employees;

15. How do you retrieve unique values from a column?

Use DISTINCT:

SELECT DISTINCT Department FROM Employees;

16. Write a query to find duplicate records in a table.

Suppose you want to find duplicate Email entries:

SELECT Email, COUNT(*) 
FROM Employees 
GROUP BY Email 
HAVING COUNT(*) &gt; 1;

17. How do you add a new column to an existing table?

ALTER TABLE Employees ADD COLUMN DateOfJoining DATE;

18. How do you delete a column from a table?

ALTER TABLE Employees DROP COLUMN DateOfJoining;

19. How do you rename a column?

In SQL Server:

EXEC sp_rename 'Employees.OldColumnName', 'NewColumnName', 'COLUMN';

In MySQL:

ALTER TABLE Employees CHANGE OldColumnName NewColumnName DataType;

20. How do you delete records from a table?

DELETE FROM Employees WHERE EmployeeID = 101;

1. What is a primary key?

A primary key is a column or a set of columns in a table that uniquely identifies each row in that table. It must contain unique values and cannot contain NULLs.

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Salary DECIMAL(10,2)
);

Here, EmployeeID uniquely identifies each employee.

2. What is a foreign key?

A foreign key is a column or set of columns in one table that references the primary key of another table. It enforces referential integrity between two tables.

Example:

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

DepartmentID in Employees references Departments.

3. Write a query to join two tables.

Suppose we want to list employees with their department names:

SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

4. What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only the matching records from both tables.
LEFT JOIN returns all records from the left table and matched records from the right table; unmatched right table records are NULL.

Example:

-- INNER JOIN
SELECT * FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

-- LEFT JOIN
SELECT * FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

5. Write a query to retrieve data from two tables using a JOIN.

SELECT E.FirstName, E.LastName, D.DepartmentName
FROM Employees E
JOIN Departments D ON E.DepartmentID = D.DepartmentID;

6. How do you perform a self-join?

A self-join is joining a table with itself to compare rows within the same table.

Example:
Find employees and their managers (assuming ManagerID references EmployeeID):

SELECT E1.FirstName AS Employee, E2.FirstName AS Manager
FROM Employees E1
LEFT JOIN Employees E2 ON E1.ManagerID = E2.EmployeeID;

7. What is a subquery? Write an example.

A subquery is a query nested inside another query.

Example:
Find employees earning more than the average salary:

SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

8. How do you use EXISTS and NOT EXISTS?

EXISTS checks for the existence of rows returned by a subquery.
NOT EXISTS checks for the absence of such rows.

Example:
Find employees who have completed training:

SELECT FirstName, LastName
FROM Employees E
WHERE EXISTS (
    SELECT 1 FROM Training T WHERE T.EmployeeID = E.EmployeeID AND T.Completed = 'Yes'
);

9. Write a query to find employees who have not completed training.

SELECT FirstName, LastName
FROM Employees E
WHERE NOT EXISTS (
    SELECT 1 FROM Training T WHERE T.EmployeeID = E.EmployeeID AND T.Completed = 'Yes'
);

10. How do you use GROUP BY?

GROUP BY groups rows sharing a property so aggregate functions can be applied.

Example:
Count employees per department:

SELECT DepartmentID, COUNT(*) AS NumEmployees
FROM Employees
GROUP BY DepartmentID;

11. Write a query to find the number of employees in each department.

SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID;

12. How do you filter groups using HAVING?

HAVING filters groups based on aggregate functions.

Example:
Find departments with more than 5 employees:

SELECT DepartmentID, COUNT(*) AS NumEmployees
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) > 5;

13. Write a query to find the department with the most employees.

SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID
ORDER BY EmployeeCount DESC
LIMIT 1; -- For MySQL
-- For SQL Server:
-- SELECT TOP 1 DepartmentID, COUNT(*) AS EmployeeCount FROM Employees GROUP BY DepartmentID ORDER BY EmployeeCount DESC;

14. How do you update records in SQL?

UPDATE Employees
SET Salary = Salary * 1.10
WHERE DepartmentID = 3;

15. Write a query to increase all salaries by 10%.

UPDATE Employees
SET Salary = Salary * 1.10;

16. How do you delete duplicate records?

Suppose duplicates are based on Email, keeping the one with the smallest EmployeeID:

DELETE E1
FROM Employees E1
INNER JOIN Employees E2 
ON E1.Email = E2.Email AND E1.EmployeeID > E2.EmployeeID;

17. What is indexing? When should you create an index?

Indexing improves query performance by allowing faster data retrieval. Create indexes on columns frequently used in WHERE, JOIN, or ORDER BY clauses.

Example:

CREATE INDEX idx_lastname ON Employees(LastName);

18. How do you create a new table?

CREATE TABLE Projects (
    ProjectID INT PRIMARY KEY,
    ProjectName VARCHAR(100),
    StartDate DATE,
    EndDate DATE
);

19. How do you modify a table's structure?

To add a column:

ALTER TABLE Employees ADD COLUMN DateOfBirth DATE;

To modify a column:

ALTER TABLE Employees ALTER COLUMN Salary DECIMAL(12,2);

20. How do you drop a table?

DROP TABLE Projects;

1. What are stored procedures?

Stored procedures are precompiled collections of SQL statements stored in the database. They can accept parameters, perform operations, and return results, enabling code reuse and improved performance.

Example:

CREATE PROCEDURE InsertEmployee
    @FirstName VARCHAR(50),
    @LastName VARCHAR(50),
    @Salary DECIMAL(10,2)
AS
BEGIN
    INSERT INTO Employees (FirstName, LastName, Salary)
    VALUES (@FirstName, @LastName, @Salary);
END;

2. What are functions in SQL?

Functions are routines that accept input parameters, perform calculations or operations, and return a single value. They are useful for reusable calculations within queries.

Example:

CREATE FUNCTION GetEmployeeFullName (@EmployeeID INT)
RETURNS VARCHAR(100)
AS
BEGIN
    DECLARE @FullName VARCHAR(100);
    SELECT @FullName = FirstName + ' ' + LastName FROM Employees WHERE EmployeeID = @EmployeeID;
    RETURN @FullName;
END;

3. Write a stored procedure to insert a new record.

CREATE PROCEDURE AddProduct
    @ProductName VARCHAR(50),
    @Price DECIMAL(10,2)
AS
BEGIN
    INSERT INTO Products (ProductName, Price)
    VALUES (@ProductName, @Price);
END;

4. What is transaction management in SQL?

Transaction management ensures that a sequence of SQL operations are executed as a single unit, maintaining data integrity. Transactions follow ACID properties.

5. What are ACID properties?

Atomicity: All operations in a transaction are completed or none are.
Consistency: Transactions bring the database from one valid state to another.
Isolation: Transactions do not interfere with each other.
Durability: Once committed, changes are permanent.

6. How do you implement rollback in SQL?

Use BEGIN TRAN, COMMIT, and ROLLBACK:

BEGIN TRAN;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
-- Suppose some condition fails
ROLLBACK; -- Undo changes

7. What is normalization? Explain different normal forms.

Normalization organizes data to reduce redundancy.

1NF: No repeating groups, atomic columns.
2NF: No partial dependencies.
3NF: No transitive dependencies.
Boyce-Codd (BCNF): Every determinant is a candidate key.

8. What is denormalization? When should it be used?

Denormalization introduces redundancy intentionally to improve read performance, especially in data warehousing. Use it when read operations outweigh write complexity.

9. How do you handle errors in SQL?

Use TRY...CATCH blocks:

BEGIN TRY
    -- risky statement
    INSERT INTO Employees (FirstName) VALUES (NULL);
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

10. Write a query using window functions (ROW_NUMBER(), RANK(), DENSE_RANK()).

SELECT EmployeeID, Salary,
       ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum,
       RANK() OVER (ORDER BY Salary DESC) AS Rnk,
       DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees;

11. What are CTEs (Common Table Expressions)?

CTEs are temporary result sets defined within a query, improving readability and enabling recursion.

Example:

WITH EmployeeCTE AS (
    SELECT EmployeeID, FirstName, ManagerID
    FROM Employees
)
SELECT * FROM EmployeeCTE WHERE ManagerID IS NULL;

12. Write a recursive query using CTE.

Find employee hierarchy:

WITH EmployeeHierarchy AS (
    SELECT EmployeeID, FirstName, ManagerID, 1 AS Level
    FROM Employees WHERE ManagerID IS NULL
    UNION ALL
    SELECT E.EmployeeID, E.FirstName, E.ManagerID, EH.Level + 1
    FROM Employees E
    INNER JOIN EmployeeHierarchy EH ON E.ManagerID = EH.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

13. How do you optimize SQL queries?

Use indexes on columns involved in WHERE, JOIN, ORDER BY.
Avoid SELECT *; select only needed columns.
Use proper joins and avoid nested queries if possible.
Analyze execution plans.

14. What is execution plan analysis?

It helps understand how SQL Server executes a query, revealing index usage, joins, and scan operations, guiding optimization efforts.

15. Explain the concept of indexing and its types.

Indexing improves data retrieval speed.

Clustered Index: Determines physical order.
Non-clustered Index: Separate structure pointing to data.
Unique Index: Ensures unique values.
Full-text Index: For text searching.

16. How do you perform full-text search?

Create a full-text index and use CONTAINS:

-- Create full-text index
CREATE FULLTEXT INDEX ON Articles(Content) KEY INDEX PK_Articles;

-- Search
SELECT * FROM Articles WHERE CONTAINS(Content, 'SQL');

17. What is a trigger? Write an example.

A trigger automatically executes on events like INSERT, UPDATE, or DELETE.

Example:

CREATE TRIGGER trg_Audit
ON Employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO AuditLog (EmployeeID, ChangeDate)
    SELECT EmployeeID, GETDATE() FROM inserted;
END;

18. How do you implement security in SQL?

Use user roles and permissions.
Encrypt sensitive data.
Use parameterized queries to prevent SQL injection.
Enable auditing.

19. What is dynamic SQL?

SQL code generated at runtime, useful for flexible queries.

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 'SELECT * FROM ' + QUOTENAME(@TableName);
EXEC sp_executesql @SQL;

20. How do you perform pivot and unpivot operations?

Pivot:

SELECT * FROM
(SELECT Year, Quarter, Revenue FROM Sales) AS SourceTable
PIVOT (
    SUM(Revenue) FOR Quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS PivotTable;

Unpivot:

SELECT Year, Quarter, Revenue
FROM
(SELECT Year, Q1, Q2, Q3, Q4 FROM SalesData) AS SourceTable
UNPIVOT (
    Revenue FOR Quarter IN (Q1, Q2, Q3, Q4)
) AS UnpivotTable;

1. Retrieve employees hired in the last 3 months

SELECT * FROM Employees
WHERE HireDate >= DATEADD(MONTH, -3, GETDATE());

2. Find the second highest salary in the Employees table

SELECT DISTINCT Salary
FROM Employees e1
WHERE 2 = (
    SELECT COUNT(DISTINCT Salary)
    FROM Employees e2
    WHERE e2.Salary >= e1.Salary
);

Or using window functions (if supported):

SELECT Salary FROM (
    SELECT Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank
    FROM Employees
) AS RankedSalaries
WHERE Rank = 2;

3. List employees whose names start with 'A'

SELECT * FROM Employees
WHERE FirstName LIKE 'A%';

4. Find employees with salaries between 40,000 and 60,000

SELECT * FROM Employees
WHERE Salary BETWEEN 40000 AND 60000;

5. Calculate total sales for each product

SELECT ProductID, SUM(Quantity * UnitPrice) AS TotalSales
FROM OrderDetails
GROUP BY ProductID;

6. Find customers who have made more than 3 orders

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) > 3;

7. List products that have not been ordered

SELECT p.ProductID, p.ProductName
FROM Products p
LEFT JOIN OrderDetails od ON p.ProductID = od.ProductID
WHERE od.ProductID IS NULL;

8. Find the average order value per customer

SELECT CustomerID, AVG(OrderTotal) AS AvgOrderValue
FROM (
    SELECT OrderID, CustomerID, SUM(Quantity * UnitPrice) AS OrderTotal
    FROM OrderDetails
    GROUP BY OrderID, CustomerID
) AS OrderSums
GROUP BY CustomerID;

9. Retrieve the top 3 customers based on total purchase amount

SELECT CustomerID, SUM(Quantity * UnitPrice) AS TotalSpent
FROM OrderDetails od
JOIN Orders o ON od.OrderID = o.OrderID
GROUP BY CustomerID
ORDER BY TotalSpent DESC
LIMIT 3; -- For SQL Server use TOP 3 or SELECT TOP 3 ...

10. Find employees whose department is 'Sales' and salary is above 60,000

SELECT * FROM Employees
WHERE Department = 'Sales' AND Salary > 60000;

11. List employees who are managers (assuming a `ManagerID` column)

SELECT DISTINCT m.EmployeeID, m.FirstName, m.LastName
FROM Employees e
JOIN Employees m ON e.ManagerID = m.EmployeeID;

12. Find the number of orders placed each month

SELECT YEAR(OrderDate) AS Year, MONTH(OrderDate) AS Month, COUNT(*) AS OrdersCount
FROM Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY Year, Month;

13. Identify employees who work in multiple departments

Assuming a separate EmployeeDepartments table for many-to-many:

SELECT EmployeeID
FROM EmployeeDepartments
GROUP BY EmployeeID
HAVING COUNT(DISTINCT DepartmentID) > 1;

14. Retrieve products with stock quantity less than 10

SELECT * FROM Products
WHERE StockQuantity < 10;

15. Find the date when the highest sales occurred

SELECT TOP 1 SaleDate, SUM(Quantity * UnitPrice) AS TotalSales
FROM Sales
GROUP BY SaleDate
ORDER BY TotalSales DESC;

16. List employees with no manager

SELECT * FROM Employees
WHERE ManagerID IS NULL;

17. Find the total number of orders and total revenue

SELECT COUNT(*) AS TotalOrders,
       SUM(Quantity * UnitPrice) AS TotalRevenue
FROM OrderDetails;

18. List products that are discontinued

SELECT * FROM Products
WHERE Discontinued = 1; -- assuming a boolean or flag

19. Find the oldest customer in the database

SELECT TOP 1 * FROM Customers
ORDER BY BirthDate ASC;

20. Count the number of products in each category

SELECT CategoryID, COUNT(*) AS ProductCount
FROM Products
GROUP BY CategoryID;

1. Difference between DELETE, TRUNCATE, and DROP

Operation	Description	Usage Example	Impact
DELETE	Removes specific rows; can include WHERE clause	`DELETE FROM Employees WHERE EmployeeID = 10;`	Deletes data; logs each row; can be rolled back if in transaction
TRUNCATE	Removes all rows from a table; faster; cannot include WHERE	`TRUNCATE TABLE Employees;`	Deletes all data; minimal logging; cannot be rolled back in some DBs
DROP	Deletes the entire table and its structure	`DROP TABLE Employees;`	Removes table schema and data permanently

2. How do you write a CASE statement in SQL?

SELECT EmployeeID, FirstName,
       CASE 
           WHEN Salary > 70000 THEN 'High'
           WHEN Salary BETWEEN 40000 AND 70000 THEN 'Medium'
           ELSE 'Low'
       END AS SalaryCategory
FROM Employees;

3. Difference between clustered and non-clustered indexes

Aspect	Clustered Index	Non-Clustered Index
Data Storage	Alters physical order of table data	Creates a separate structure pointing to data
Number per table	Usually 1	Multiple allowed
Use case	On primary key or frequently sorted columns	On columns used in search conditions

Example:

CREATE CLUSTERED INDEX idx_emp_id ON Employees(EmployeeID);
CREATE NONCLUSTERED INDEX idx_salary ON Employees(Salary);

4. Normalize data with examples

Normalization reduces redundancy.

1NF: Atomic columns
2NF: No partial dependencies
3NF: No transitive dependencies

Example:
Suppose you have a table:
| OrderID | CustomerName | CustomerAddress | ProductName | Quantity |
|---------|--------------|-----------------|-------------|----------|

Normalized:

Customer table (CustomerID, CustomerName, CustomerAddress)
Orders table (OrderID, CustomerID)
OrderDetails (OrderID, ProductName, Quantity)

5. Query to get the third highest salary

SELECT DISTINCT Salary FROM Employees e1
WHERE 3 = (
    SELECT COUNT(DISTINCT Salary) FROM Employees e2 WHERE e2.Salary >= e1.Salary
);

Or with window functions:

SELECT Salary FROM (
    SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank
    FROM Employees
) AS RankedSalaries
WHERE Rank = 3;

6. Aggregate functions and examples

SUM() — total sum
AVG() — average
COUNT() — total count
MAX() — maximum value
MIN() — minimum value

Example:

SELECT COUNT(*) AS TotalEmployees, AVG(Salary) AS AvgSalary FROM Employees;

7. How to perform a cross join

SELECT A.*, B.*
FROM TableA A
CROSS JOIN TableB B;

Returns Cartesian product of all combinations.

8. Differences between UNION and UNION ALL

Aspect	UNION	UNION ALL
Removes duplicates	Yes	No
Performance	Slightly slower	Faster

Example:

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers;

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers;

9. Handling NULL values

Use functions like IS NULL, COALESCE(), or ISNULL():

SELECT FirstName, COALESCE(Phone, 'No Phone') AS ContactNumber
FROM Employees;

10. Find most recent order for each customer

WITH RankedOrders AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS rn
    FROM Orders
)
SELECT * FROM RankedOrders WHERE rn = 1;

11. Implement pagination in SQL

Using OFFSET-FETCH (SQL Server, PostgreSQL, etc.):

SELECT * FROM Employees
ORDER BY EmployeeID
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

12. Purpose of `WITH (NOLOCK)`

Used to read data without acquiring shared locks
Improves performance but risks dirty reads

SELECT * FROM Employees WITH (NOLOCK);

13. Data warehousing

A data warehouse consolidates large amounts of historical data for analysis, often using star/snowflake schemas for optimized querying.

14. Aggregate and scalar functions

Aggregate functions: operate on multiple rows (e.g., SUM, AVG)
Scalar functions: operate on a single value (e.g., UPPER, GETDATE())

15. Implement role-based security

Create roles and grant permissions:

CREATE ROLE SalesTeam;
GRANT SELECT, INSERT ON Employees TO SalesTeam;
EXEC sp_addrolemember 'SalesTeam', 'JohnDoe';

16. Calculate running total

SELECT OrderID, CustomerID, Quantity,
       SUM(Quantity) OVER (ORDER BY OrderDate ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM Orders;

17. Difference between view and table

Aspect	View	Table
Definition	Virtual table based on query	Physical storage of data
Usage	Simplify complex queries	Store data permanently

18. Create a temporary table

CREATE TABLE #TempOrders (
    OrderID INT,
    CustomerID INT,
    OrderDate DATE
);

19. Data integrity

Ensuring accuracy, consistency, and validity of data through constraints (PRIMARY KEY, FOREIGN KEY, CHECK).

20. Best practices for writing efficient SQL

Use proper indexing
Avoid SELECT *
Write SARGable WHERE clauses
Use joins instead of subqueries when possible
Analyze execution plans
Normalize data appropriately
Limit result sets with pagination

Top comments (2)

DreamFrameAI • Nov 18

Love these challenging SQL questions! They’re a great way to stay sharp and deepen my understanding of databases. Keep them coming! 🙌✨ #SQLSkills #DataEnthusiast #ContinuousLearning

NextCut Trailers • Nov 18

Good practice for SQL skills. Thanks for sharing.✨👏⏳

Basic SQL Questions

Intermediate SQL Questions

Advanced SQL Questions

SQL Practice Scenarios

SQL Certification and Interview Questions

1. What is SQL?

2. What are the different types of SQL commands?

3. Write a query to retrieve all records from a table called Employees.

4. How do you select specific columns from a table?

5. How do you filter records in SQL?

6. Write a query to find employees with a salary greater than 50,000.

7. How do you sort query results?

8. Write a query to retrieve the top 5 highest-paid employees.

9. How do you perform a case-insensitive search?

10. What is the difference between WHERE and HAVING?

11. Write a query to find the total number of employees.

12. How do you count the number of records in a table?

13. Write a query to find the minimum and maximum salary.

14. How do you calculate the average salary?

15. How do you retrieve unique values from a column?

16. Write a query to find duplicate records in a table.

17. How do you add a new column to an existing table?

18. How do you delete a column from a table?

19. How do you rename a column?

20. How do you delete records from a table?

1. What is a primary key?

2. What is a foreign key?

3. Write a query to join two tables.

4. What is the difference between INNER JOIN and LEFT JOIN?

5. Write a query to retrieve data from two tables using a JOIN.

6. How do you perform a self-join?

7. What is a subquery? Write an example.

8. How do you use EXISTS and NOT EXISTS?

9. Write a query to find employees who have not completed training.

10. How do you use GROUP BY?

11. Write a query to find the number of employees in each department.

12. How do you filter groups using HAVING?

13. Write a query to find the department with the most employees.

14. How do you update records in SQL?

15. Write a query to increase all salaries by 10%.

16. How do you delete duplicate records?

17. What is indexing? When should you create an index?

18. How do you create a new table?

19. How do you modify a table's structure?

20. How do you drop a table?

1. What are stored procedures?

2. What are functions in SQL?

3. Write a stored procedure to insert a new record.

4. What is transaction management in SQL?

5. What are ACID properties?

6. How do you implement rollback in SQL?

7. What is normalization? Explain different normal forms.

8. What is denormalization? When should it be used?

9. How do you handle errors in SQL?

10. Write a query using window functions (ROW_NUMBER(), RANK(), DENSE_RANK()).

11. What are CTEs (Common Table Expressions)?

12. Write a recursive query using CTE.

13. How do you optimize SQL queries?

14. What is execution plan analysis?

15. Explain the concept of indexing and its types.

16. How do you perform full-text search?

17. What is a trigger? Write an example.

18. How do you implement security in SQL?

19. What is dynamic SQL?

20. How do you perform pivot and unpivot operations?

1. Retrieve employees hired in the last 3 months

2. Find the second highest salary in the Employees table

3. List employees whose names start with 'A'

4. Find employees with salaries between 40,000 and 60,000

5. Calculate total sales for each product

6. Find customers who have made more than 3 orders

7. List products that have not been ordered

8. Find the average order value per customer

9. Retrieve the top 3 customers based on total purchase amount

10. Find employees whose department is 'Sales' and salary is above 60,000

11. List employees who are managers (assuming a ManagerID column)

12. Find the number of orders placed each month

13. Identify employees who work in multiple departments

14. Retrieve products with stock quantity less than 10

15. Find the date when the highest sales occurred

3. Write a query to retrieve all records from a table called `Employees`.

10. What is the difference between `WHERE` and `HAVING`?

11. List employees who are managers (assuming a `ManagerID` column)

12. Purpose of `WITH (NOLOCK)`