SQL Interview Questions for Freshers - Interview Questions and Answers
SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases. It allows users to create, read, update, and delete data within a database.
SQL statements are categorized into:
- DDL (Data Definition Language):
CREATE
,ALTER
,DROP
- DML (Data Manipulation Language):
SELECT
,INSERT
,UPDATE
,DELETE
- DCL (Data Control Language):
GRANT
,REVOKE
- TCL (Transaction Control Language):
COMMIT
,ROLLBACK
,SAVEPOINT
A primary key is a column (or a set of columns) that uniquely identifies each row in a table. It must contain unique values and cannot contain NULLs.
A foreign key is a column in one table that references the primary key in another table, establishing a relationship between the two tables.
WHERE
is used to filter rows before grouping.HAVING
is used to filter groups after theGROUP BY
clause.
-- Using WHERE
SELECT * FROM Employees WHERE Salary > 50000;
-- Using HAVING
SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) > 60000;
The SELECT
statement is used to retrieve data from one or more tables.
Example:
SELECT FirstName, LastName FROM Employees;
Use the DISTINCT
keyword to fetch unique records.
Example:
SELECT DISTINCT DepartmentID FROM Employees;
The ORDER BY
clause is used to sort the result set in ascending (ASC
) or descending (DESC
) order.
Example:
SELECT * FROM Employees ORDER BY Salary DESC;
DELETE
removes specified rows and can be rolled back.TRUNCATE
removes all rows from a table and cannot be rolled back.
A JOIN
is used to combine rows from two or more tables based on a related column.
Types of JOINs:
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN
Example:
SELECT Employees.FirstName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
Used for pattern matching in WHERE
clause.
SELECT * FROM Employees WHERE FirstName LIKE 'J%';
%
matches any number of characters._
matches exactly one character.
A marker for missing or unknown data. Not the same as 0 or empty string.
SELECT * FROM Employees WHERE DepartmentID IS NULL;
Ascending (ASC
).
SELECT FirstName AS Name FROM Employees;
Used to filter values within a range.
SELECT * FROM Employees WHERE Salary BETWEEN 50000 AND 80000;
A temporary name given to a table or column.
SELECT COUNT(*) FROM Employees;
SELECT MAX(Salary) FROM Employees;
Groups rows with the same values.
SELECT DepartmentID, AVG(Salary) FROM Employees GROUP BY DepartmentID;
Functions that perform calculations on multiple rows (e.g., SUM
, COUNT
, AVG
, MAX
, MIN
).
UPDATE Employees SET Salary = 70000 WHERE EmployeeID = 1;
DELETE FROM Employees WHERE EmployeeID = 1;
A query nested inside another query.
Rules applied to table columns (NOT NULL
, UNIQUE
, CHECK
, PRIMARY KEY
, FOREIGN KEY
).
Assigns a default value when none is provided.
Combines results from two queries and removes duplicates.
UNION
: Removes duplicates.UNION ALL
: Keeps all rows including duplicates.
Organizing data to reduce redundancy and improve integrity.
Combining tables for read performance at the cost of redundancy.
- 1NF: Atomic values
- 2NF: No partial dependency
- 3NF: No transitive dependency
A virtual table based on a SQL query.
CREATE VIEW HighSalaries AS
SELECT * FROM Employees WHERE Salary > 80000;
Yes, if it's based on a single table and doesn't include aggregates.
Improves the speed of data retrieval.
- Unique Index
- Composite Index
- Clustered & Non-Clustered Index
A unit of work that is performed as a single logical operation.
- Atomicity
- Consistency
- Isolation
- Durability
Saves the current transaction to the database.
Undoes the changes in a transaction.
Marks a point in a transaction to rollback to.
Subquery that uses values from the outer query.
SELECT E1.FirstName, E2.FirstName
FROM Employees E1, Employees E2
WHERE E1.ManagerID = E2.EmployeeID;
Cartesian product of two tables.
A subquery used in the FROM
clause.
SELECT FirstName,
CASE
WHEN Salary > 80000 THEN 'High'
ELSE 'Normal'
END AS SalaryLevel
FROM Employees;
Precompiled SQL code that can be executed.
Returns a single value and can be used in SQL expressions.
- Procedures can return multiple values.
- Functions must return one value and are used in expressions.
Special procedures that automatically execute when a specific event occurs.
Replaces NULL with a specified value.
Returns the first non-null value in a list.
SELECT GETDATE();
Functionally equivalent; both return current system date and time.
SELECT FirstName + ' ' + LastName AS FullName FROM Employees;
SELECT COUNT(DISTINCT DepartmentID) FROM Employees;
SELECT TOP 5 * FROM Employees;
SELECT FirstName, COUNT(*)
FROM Employees
GROUP BY FirstName
HAVING COUNT(*) > 1;
A table created for the current session.
CREATE TABLE #TempEmployees (ID INT);
Conditional logic inside SQL.
SELECT Name, Salary,
CASE
WHEN Salary > 70000 THEN 'High'
WHEN Salary BETWEEN 40000 AND 70000 THEN 'Medium'
ELSE 'Low'
END AS SalaryRange
FROM Employees;
Used to filter aggregated/grouped data.
SELECT DepartmentID, AVG(Salary)
FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) > 50000;
Checks for the existence of rows returned by a subquery.
SELECT Name
FROM Employees E
WHERE EXISTS (
SELECT 1 FROM Departments D WHERE D.ManagerID = E.EmployeeID
);
EXISTS
: Stops searching once it finds a match (better for correlated subqueries).IN
: Retrieves all matching rows (can be less efficient for large data sets).
A primary key made up of two or more columns.
ALTER TABLE Employees ADD DateOfJoining DATE;
ALTER TABLE Employees DROP COLUMN DateOfJoining;
Deletes a table, view, or database permanently.
Command | Deletes Data | Can Rollback | Deletes Table Structure |
---|---|---|---|
DELETE | Yes | Yes | No |
TRUNCATE | Yes (All) | No | No |
DROP | Yes (All) | No | Yes |
INSERT INTO EmployeesBackup SELECT * FROM Employees;
SELECT MAX(Salary)
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);
SELECT Salary
FROM (
SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees
) AS RankedSalaries
WHERE Rank = 3;
Assigns ranks to rows, skipping duplicates.
Like RANK()
but doesn’t skip ranks for duplicates.
Assigns a unique sequential integer to each row.
WITH Ranked AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY ID) AS rn
FROM Employees
)
DELETE FROM Ranked WHERE rn > 1;
LEN()
, UPPER()
, LOWER()
, LTRIM()
, RTRIM()
, SUBSTRING()
, REPLACE()
Converts a value from one data type to another.
SELECT CAST(Salary AS VARCHAR(10)) FROM Employees;
Also converts data types. More flexible with formatting.
Operate on a single value and return a single result.
Examples: LEN()
, GETDATE()
, ISNULL()
Return a table instead of a single value.
Clustered Index | Non-Clustered Index |
---|---|
Alters table's data order | Separate from table data |
One per table | Many allowed |
SELECT DepartmentID, SUM(Salary)
FROM Employees
GROUP BY DepartmentID;
System views that let you query metadata (tables, columns, etc.).
SELECT * FROM INFORMATION_SCHEMA.TABLES;
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Employees';
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Employees')
PRINT 'Table Exists';
A system-generated unique identifier (e.g., auto-increment ID).
A real-world identifier like email
or SSN
.
A column that could be a primary key.
Validates data before insertion.
ALTER TABLE Employees ADD CONSTRAINT chk_Salary CHECK (Salary > 0);
CHAR
: Fixed-lengthVARCHAR
: Variable-length
Tables maintained by the DBMS to store metadata.
Always use IS NULL
because = NULL
will not return any result.
Temporary result sets used in complex queries.
WITH DeptCTE AS (
SELECT DepartmentID, COUNT(*) AS EmpCount
FROM Employees
GROUP BY DepartmentID
)
SELECT * FROM DeptCTE WHERE EmpCount > 5;
A CTE that refers to itself to handle hierarchical data (like org charts).
SELECT * INTO EmployeesBackup FROM Employees;
ALTER TABLE Employees ADD CONSTRAINT UQ_Email UNIQUE (Email);
Faster query performance, especially for SELECT
operations.
- Extra storage space
- Slower
INSERT
,UPDATE
,DELETE
operations
EmployeeID | FirstName | LastName | Salary | DepartmentID | ManagerID |
---|---|---|---|---|---|
1 | John | Smith | 50000 | 101 | NULL |
2 | Jane | Doe | 70000 | 101 | 1 |
3 | Tom | Lee | 90000 | 102 | 1 |
4 | Mary | Kim | 60000 | 103 | 2 |
Tutorials
Random Blogs
- OLTP vs. OLAP Databases: Advanced Insights and Query Optimization Techniques
- Where to Find Free Datasets for Your Next Machine Learning & Data Science Project
- Top 15 Recommended SEO Tools
- Understanding AI, ML, Data Science, and More: A Beginner's Guide to Choosing Your Career Path
- Why to learn Digital Marketing?
- Datasets for Natural Language Processing
- Python Challenging Programming Exercises Part 1
- Avoiding the Beginner’s Trap: Key Python Fundamentals You Shouldn't Skip
- Government Datasets from 50 Countries for Machine Learning Training
- The Ultimate Guide to Machine Learning (ML) for Beginners