Data Knowledge – SQL Master in Data Analyst

Data Knowledge

SQL

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

1. What is SQL?
SQL (Structured Query Language) is a standard programming language used to
interact with databases. It is used to create, read, update, and delete (CRUD
operations) data in a database. SQL allows users to query and manipulate structured
data stored in tables.

2. What is a Database?
A database is an organized collection of data that can be easily accessed, managed,
and updated. Think of it as an electronic filing system where data is stored in tables
consisting of rows and columns.

Example of a Database:
A school database stores data about students, teachers, and classes in separate
tables.

3. What is RDBMS?
A Relational Database Management System (RDBMS) is software used to manage
and operate relational databases. In an RDBMS, data is stored in tables with
relationships between them. It ensures data integrity and supports SQL for database
operations.
Popular RDBMS software includes MySQL, PostgreSQL, SQL Server, and Oracle
Database.

Imagine a database for a company storing employee details.

Employee Table (Database table):

EmployeeID Name Department Salary

1 Alice HR 50,000

2 Bob IT 70,000

3 Charlie Sales 60,000

4. How to create Database.

Creating a database involves using SQL commands, and the exact method can vary
depending on the RDBMS you’re using (e.g., MSSQL , SQL Server, PostgreSQL).

General SQL Syntax:

CREATE DATABASE database_name;

Ex. CREATE DATABASE School;

Select the Database

Before performing any operations, you need to select the database you want to use.
USE database_name;
Ex. USE School;

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

5. What are Data types?

In SQL, data types define the type of data that can be stored in a column. Choosing
the correct data type ensures data integrity, efficiency, and proper functionality. Below
is a list of commonly used data types grouped into categories.

Category Data Type Description Example

Integer value (whole

Numeric INT 42
number).

Smaller range
SMALLINT 1000
integer.

BIGINT Large range integer. 9.22337E+18

Fixed-point number
DECIMAL(p, s) with precision p and 12345.67
scale s.

NUMERIC(p, s) Same as DECIMAL. 1000.99

Approximate
FLOAT floating-point 3.14159
number.

Smaller floating-
REAL 2.71
point number.

Very small integer

TINYINT (commonly 0 to 127
255).

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

Fixed-length string
String (Character) CHAR(n) 'Hello'
of size n.

Variable-length
VARCHAR(n) string with a max 'World'
size n.

Large variable-
TEXT 'Lorem Ipsum...'
length string.

Stores date in the

Date and Time DATE format YYYY-MM- '2024-12-10'
DD.

Stores time in the

TIME '14:30:00'
format HH:MM:SS.

Stores both date '2024-12-10

DATETIME
and time. 14:30:00'

Similar to
DATETIME, often '2024-12-10
TIMESTAMP
includes time zone 14:30:00'
info.

Stores year as a 4-
YEAR 2024
digit number.

Fixed-length binary
Binary BINARY(n) 101010
data of size n.

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

Variable-length
VARBINARY(n) binary data of max 1010101
size n.

Large binary data

BLOB (Binary Large Image data
Object).

Stores TRUE or
Other BOOLEAN TRUE
FALSE.

String object with

ENUM 'Male', 'Female'
predefined values.

Stores JSON-
JSON '{"key":"value"}'
formatted data.

6. key features of SQL regarding its syntax, behavior, and usage:

Case Insensitivity
• SQL commands are case-insensitive, meaning you can write keywords in
uppercase or lowercase.
SELECT * FROM Employees;
select * from employees;
Semicolon at the End of a Query:
Semicolon (;) is used to terminate SQL statements.
SELECT * FROM Employees;
Supports Multiple Queries:
You can run multiple SQL queries at once by separating them with
semicolons (;)
INSERT INTO Employees (EmployeeID, Name) VALUES (1, 'Alice');
INSERT INTO Employees (EmployeeID, Name) VALUES (2, 'Bob');

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

7. How to create a table?

Syntax:

CREATE TABLE table_name (

column1 datatype,
column2 datatype,
column3 datatype,
...
);

Example:

CREATE TABLE Employees (

EmployeeID INT, -- Integer for unique employee ID
FullName VARCHAR(100), -- Variable-length string for the employee's full
name
Gender CHAR(1), -- Fixed-length character for gender (e.g., 'M', 'F')
DateOfBirth DATE, -- Date of birth
JoiningTime TIME, -- Time when the employee joined
IsActive BOOLEAN -- Boolean to indicate if the employee is active
(TRUE/FALSE)
);

8. How to Insert Records in table:

The INSERT INTO statement in SQL is used to add rows of data into a table. Here’s
the syntax, explanation, and an example:

1. Insert Specific Columns:

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

2. Insert All Columns:

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

3. Insert Data for Specific Columns:

INSERT INTO Employees (EmployeeID, FullName, Gender)
VALUES (1, 'Alice Johnson', 'F');

4. To insert data from one table to another:

To insert all records from one table into another, you can use the INSERT INTO ...
SELECT statement in SQL. This allows you to copy all the rows from one table and
insert them into another table, optionally transforming the data during the copy
process.

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

INSERT INTO target_table

SELECT * FROM source_table;

target_table: The table where data will be inserted.

source_table: The table from which the data will be selected.

Ex.
INSERT INTO NewEmployees
SELECT * FROM Employees;

9. Subset of SQL:
SQL is divided into various subsets based on its functionality. Each subset is
designed for specific types of tasks within a database. Here's an overview of the key
subsets of SQL:

Subset Purpose Examples

Define and modify database CREATE, ALTER, DROP

DDL
structure
Manipulate data within INSERT, UPDATE, DELETE
DML
database objects
COMMIT, ROLLBACK,
TCL Manage database transactions SAVEPOINT

Manage access control and GRANT, REVOKE

DCL
permissions

10. What is Where clause?

The WHERE clause in SQL is used to filter records in a table based on specified conditions.
It allows you to retrieve only those rows that satisfy the given condition(s).

SELECT column1, column2, ...

FROM table_name
WHERE condition;

1. Filter by Equality

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

Retrieve employees with EmployeeID = 1;

SELECT * FROM Employees
WHERE EmployeeID = 1;

11. Operators in SQL

Operator Type Operator Description Example

Adds two SELECT 5 + 3; →
Arithmetic Operators +
values 8

Subtracts
the right
SELECT 10 - 4; →
- value from
6
the left
value
Multiplies SELECT 2 * 3; →
*
two values 6

Divides the
left value by SELECT 10 / 2; →
/
the right 5
value

Returns the
remainder SELECT 10 % 3;
%
of a division →1
(modulus)
SELECT * FROM
Comparison Employees
= Equal to WHERE Salary =
Operators
50000;

SELECT * FROM
Employees
<> or != Not equal to WHERE Salary <>
50000;

SELECT * FROM
Greater
> Employees
than WHERE Age > 30;

SELECT * FROM
< Less than Employees
WHERE Age < 30;

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

SELECT * FROM
Greater
Employees
>= than or WHERE Age >=
equal to 30;

SELECT * FROM
Less than or Employees
<=
equal to WHERE Age <=
30;

Combines
conditions, SELECT * FROM
Employees
returns true
Logical Operators AND WHERE Age > 30
if all AND IsActive =
conditions TRUE;
are true

Combines
conditions, SELECT * FROM
returns true Employees
OR if at least WHERE Age > 30
one OR Salary >
condition is 50000;
true

SELECT * FROM
Negates a Employees
NOT
condition WHERE NOT
IsActive = TRUE;

Bitwise SELECT 5 & 3; →

Bitwise Operators &
AND 1

` ` Bitwise OR

Bitwise SELECT 5 ^ 3; →
^
XOR 6

SELECT * FROM
Checks if a Employees
Set Operators IN value exists WHERE
in a list Department IN
('HR', 'Finance');

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

SELECT * FROM
Checks if a Employees
value does WHERE
NOT IN
not exist in Department NOT
a list IN ('HR',
'Finance');

SELECT * FROM
Employees
Checks for WHERE Name
Pattern Matching LIKE a pattern in LIKE 'A%'; →
a string Names starting
with 'A'

Checks for SELECT * FROM

absence of Employees
NOT LIKE
a pattern in WHERE Name
a string NOT LIKE 'A%';

Checks if a SELECT * FROM

Employees
Null Operators IS NULL value is WHERE Address
NULL IS NULL;

SELECT * FROM
Checks if a
Employees
IS NOT NULL value is not WHERE Address
NULL IS NOT NULL;

Checks if a SELECT * FROM

Employees
value lies
Range Operators BETWEEN WHERE Salary
within a BETWEEN 30000
range AND 60000;

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

SELECT * FROM
Checks if a Employees
value is WHERE Salary
NOT BETWEEN
outside a NOT BETWEEN
range 30000 AND
60000;

12. Update:

The UPDATE statement in SQL is used to modify existing records in a table. It allows
you to update one or more columns in one or multiple rows based on specified
conditions.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Key Points
1. Conditional Update: Use the WHERE clause to specify the rows to be updated.
2. Update All Rows: Without the WHERE clause, the UPDATE statement modifies all
rows in the table.
3. Data Types: Ensure the new values match the data type of the columns.

1. Update a Single Column

UPDATE Employees
SET Salary = 60000
WHERE EmployeeID = 1;

2. Update Multiple Columns

UPDATE Employees
SET Salary = 75000, Department = 'HR'
WHERE EmployeeID = 2;
3. Transaction Control: Use COMMIT or ROLLBACK to manage changes in
case of errors:
BEGIN TRANSACTION;
UPDATE Employees SET Salary = 80000 WHERE EmployeeID = 1;
ROLLBACK; -- Undo changes if necessary

13. Delete

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

The DELETE statement is used to remove specific rows from a table while keeping
the table structure intact.
DELETE FROM table_name
WHERE condition;

Key Points
1. Deletes specific rows based on the condition in the WHERE clause.
2. If no WHERE clause is used, all rows in the table are deleted, but the table structure
remains.
3. It can be rolled back if used within a transaction.
4. Slower compared to TRUNCATE as it logs each row deletion.

DELETE FROM Employees

WHERE Department = 'HR';

14. Drop:

The DROP statement is used to completely remove a database object (like a table,
view, or database) from the database.

DROP TABLE table_name;

Key Points
1. Completely removes the table, including its structure and data.
2. Cannot be rolled back once executed.
3. Faster than DELETE as it doesn’t log individual row deletions.
4. Use with caution, as the table is permanently removed.

Drop a Table:
DROP TABLE Employees;

Drop a Database:
DROP DATABASE CompanyDB;

15. TRUNCATE:
The TRUNCATE statement removes all rows from a table but retains the table
structure for future use.
TRUNCATE TABLE table_name;
Key Points
1. Removes all rows from the table.

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

2. Cannot include a WHERE clause (you cannot truncate specific rows).

3. Cannot be rolled back in most databases (some support transactional truncates).
4. Resets any auto-increment counters to their initial value.
5. Faster than DELETE as it doesn’t log individual row deletions.

Feature DELETE DROP TRUNCATE

Deletes specific Deletes the

Purpose Deletes all rows in the table
rows or all rows entire table

Keeps
Table Yes No Yes
Structure

Conditional Yes (WHERE clause No (Deletes all rows

No
Deletion supported) unconditionally)

Transaction
Yes No No (in most databases)
Rollback

Resets
Auto- No - Yes
Increment

Slower (logs
Speed individual row Fast Faster than DELETE
deletions)

DELETE FROM table DROP TABLE

Example WHERE id=1; table_name;
TRUNCATE TABLE table_name;

16. Order by:

The ORDER BY clause in SQL is used to sort the result set of a query in either
ascending (ASC) or descending (DESC) order based on one or more columns.

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

SELECT column1, column2, ...

FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

SELECT * FROM Employees

ORDER BY Salary;

17. ALTER Statement

the ALTER statement is used to modify an existing database object, such as a table,
view, stored procedure, or column. The ALTER statement allows you to make
changes without having to drop and recreate the object.

1. Add a Column:
ALTER TABLE table_name
ADD column_name datatype [constraint];

2. Modify a Column:
ALTER TABLE table_name
ALTER COLUMN column_name datatype;

3. Drop a Column:
ALTER TABLE table_name
DROP COLUMN column_name;

18. GROUP BY

The GROUP BY clause in SQL is used to group rows that have the same values in
specified columns into summary rows, like "total sales per region" or "number of
employees in each department." It is commonly used with aggregate functions like
COUNT, SUM, AVG, MAX, or MIN to perform operations on each group.

SELECT column1, aggregate_function(column2)

FROM table_name
GROUP BY column1;

HAVING:

The HAVING clause is used to filter groups created by the GROUP BY clause. Unlike
the WHERE clause, which filters individual rows before grouping, HAVING filters
aggregated data after the grouping has been performed.

SELECT column1, aggregate_function(column2)

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

FROM table_name
GROUP BY column1
HAVING aggregate_function(column2) condition;

Key Differences: WHERE vs HAVING

1. WHERE filters rows before grouping.
2. HAVING filters groups after grouping.

SELECT Region, SUM(Sales) AS TotalSales

FROM Sales
GROUP BY Region;

Find regions with total sales greater than 200:

SELECT Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY Region
HAVING SUM(Sales) > 200;

IMP
find duplicate records in a table

SELECT column1, column2, ..., COUNT(*)

FROM table_name
GROUP BY column1, column2, ...
HAVING COUNT(*) > 1;

Find Duplicates in Name and Department

SELECT Name, Department, COUNT(*) AS DuplicateCount

FROM Employees
GROUP BY Name, Department
HAVING COUNT(*) > 1;

19. Join
JOIN is used to combine data from two or more tables based on a related column
between them. It allows you to retrieve data from multiple tables in a relational database
by specifying a condition that links them.

Types of Joins:
1. INNER JOIN
Combines rows from two tables where the condition matches. Rows that do not meet
the condition are excluded.
SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

SELECT [Link], [Link], [Link]

FROM Employees
INNER JOIN Departments
ON [Link] = [Link];

2. LEFT JOIN (LEFT OUTER JOIN)

Retrieves all rows from the left table and matching rows from the right table. If there is no
match, NULL is returned for the right table.

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

SELECT [Link], [Link], [Link]

FROM Employees
LEFT JOIN Departments
ON [Link] = [Link];

3. RIGHT JOIN (RIGHT OUTER JOIN)

Retrieves all rows from the right table and matching rows from the left table. If there
is no match, NULL is returned for the left table.

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

SELECT [Link], [Link], [Link]

FROM Employees
RIGHT JOIN Departments
ON [Link] = [Link];

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

4. FULL JOIN (FULL OUTER JOIN)

Combines results of both LEFT JOIN and RIGHT JOIN. Retrieves all rows from both
tables, with NULL in unmatched rows.
SELECT columns
FROM table1
FULL JOIN table2
ON table1.common_column = table2.common_column;

SELECT [Link], [Link], [Link]

FROM Employees
FULL JOIN Departments
ON [Link] = [Link];

5. CROSS JOIN
Produces a Cartesian product by combining every row from the first table with every
row in the second table.
SELECT columns
FROM table1
CROSS JOIN table2;

SELECT [Link], [Link]

FROM Employees
CROSS JOIN Departments;

6. SELF JOIN
A table is joined with itself. Useful for hierarchical or relational data.

SELECT [Link], [Link]

FROM table_name A, table_name B
WHERE condition;

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

SELECT [Link] AS Employee, [Link] AS Manager

FROM Employees E1
INNER JOIN Employees E2
ON [Link] = [Link];

Key Points to Remember

1. JOIN Condition: Typically uses the ON keyword to define the relationship between
tables (e.g., foreign keys).
2. Alias: Table aliases (e.g., A, B) make queries more readable.
3. Performance: Ensure proper indexing on columns used in JOIN conditions to
improve performance.

20. what is Alias?

Alias in SQL is a temporary name that you give to a column or table to make your
query easier to read or understand.

1. Aliases don’t change the actual table or column names.

2. They’re only used during that specific query.

Example: SELECT first_name AS name FROM employees;

21. What is Distinct?

Distinct in SQL is used to remove duplicate values from the result of a SELECT
query. It returns only unique values.
Example: SELECT DISTINCT city FROM students;

22. What is a Constraint in SQL?

A Constraint in SQL is a rule or restriction applied to a column or a table that helps

maintain accuracy, integrity, and reliability of the data in the database.

[Link] Constraints Are Important:

• To prevent invalid or corrupt data entry

• To enforce business rules at the database level
• To maintain relationships between tables
• To ensure data accuracy and consistency automatically

[Link] of Constraints in SQL (with Examples):

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

Constraint Description Example

Ensures a column cannot name VARCHAR(50)

NOT NULL NOT NULL
have NULL (empty) values.
Ensures all values in a email VARCHAR(100)
UNIQUE column are unique (no UNIQUE
duplicates).
Uniquely identifies each
PRIMARY id INT PRIMARY KEY
record. It combines NOT
KEY
NULL + UNIQUE.
Maintains a relationship FOREIGN KEY (dept_id)
FOREIGN between two tables. Must REFERENCES
KEY match a primary key in the department(id)
other table.
Validates that data meets a age INT CHECK (age >=
CHECK 18)
specific condition.
Sets a default value for a city VARCHAR(50)
DEFAULT column if no value is DEFAULT 'Mumbai'
provided.

3 Step1: Create a students table with constraints:

CREATE TABLE students (

student_id INT PRIMARY KEY, -- PRIMARY KEY (unique & not null)
name VARCHAR(100) NOT NULL, -- NOT NULL (cannot be empty)
email VARCHAR(100) UNIQUE, -- UNIQUE (no duplicate emails)
age INT CHECK (age >= 18), -- CHECK (age must be 18 or older)
city VARCHAR(50) DEFAULT 'Mumbai', -- DEFAULT (if no city is given)
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id) -- FOREIGN KEY (links to
another table)
);

What each constraint does:

• PRIMARY KEY: Makes student_id unique and not null.
• NOT NULL: Ensures name cannot be left empty.
• UNIQUE: Ensures email is not duplicated.
• CHECK: Ensures only students aged 18 or above are added.

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

• DEFAULT: If no city is provided, it will automatically be 'Mumbai'.

• FOREIGN KEY: Links dept_id to a department in another table (e.g., departments
table).

Step 2: Create the departments table:

CREATE TABLE departments (

dept_id INT PRIMARY KEY,
dept_name VARCHAR(100) NOT NULL
);

23. What is a CASE Statement in SQL?

The CASE statement in SQL works like IF–THEN–ELSE logic.
It lets you create conditional logic inside your SELECT, UPDATE, or other SQL statements.

Use of CASE Statement:

• To show different values based on conditions
• To group or label data conditionally
• To simplify complex IF-ELSE logic in queries

SELECT column_name,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default_result
END AS alias_name
FROM table_name;

Query using CASE to assign grade:

student_id name marks

1 Ravi 85

2 Sneha 42

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

student_id name marks

3 Aman 75

4 Pooja 30

SELECT name, marks,

CASE
WHEN marks >= 75 THEN 'Distinction'
WHEN marks >= 50 THEN 'Pass'
ELSE 'Fail'
END AS grade
FROM students;
Output ->

name marks grade

Ravi 85 Distinction

Sneha 42 Fail

Aman 75 Distinction

Pooja 30 Fail

➢ CASE is used when you want to return different values based on conditions.
➢ It can be used in SELECT, WHERE, ORDER BY, or even inside aggregates.

24. OLTP vs OLAP

OLTP (Online Transaction Processing):

OLTP is a type of database system used to handle real-time, day-to-day transactions like
inserting, updating, or deleting data.
It is optimized for speed, accuracy, and consistency, making it ideal for systems like
banking, online shopping, and airline bookings.
Example: When you buy a product online, OLTP processes your order instantly.

OLAP (Online Analytical Processing):

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

OLAP is a system designed for analyzing large volumes of historical data to help in
decision-making and business intelligence.
It is optimized for complex queries, reporting, and data analysis, not for frequent updates.
Example: A company manager uses OLAP to view a monthly sales report by region or
product category.

OLTP (Online Transaction OLAP (Online Analytical

Feature
Processing) Processing)

Analyze historical data for decision

Purpose Run day-to-day business transactions
making

Type of
Real-time, current data Historical, aggregated data
Data

Data analysts, managers,

Users Clerks, cashiers, front-end staff
executives

INSERT, UPDATE, DELETE, simple Complex queries with GROUP BY,

Operations
SELECT JOINs, aggregations

Database Highly normalized (many small De-normalized (fewer tables,

Design tables, avoids data repetition) faster for queries)

Response Very fast for transactions Slower (seconds to minutes) for

Time (milliseconds) big reports

Data Very important (supports ACID Less critical (read-heavy, rarely

Integrity properties) changes data)

Banking, ticket booking, shopping Sales reporting, forecasting,

Examples
apps business intelligence

Real-life Example:
• OLTP: You buy a shirt from an online store — that transaction (adding order,
updating stock) is handled by OLTP.
• OLAP: The company’s manager wants to see monthly sales trends — that analysis
is handled by OLAP.

25. What is a Subquery in SQL?

A subquery (also called an inner query or nested query) is a query inside another query.
It is used to return data that will be used in the main (outer) query.

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

When to Use a Subquery:

• When you want to filter, compare, or fetch data based on results from another table
or query.
• Used in SELECT, FROM, WHERE, HAVING, etc.

➢ Basic Syntax:

SELECT column1
FROM table1
WHERE column2 = (
SELECT column2
FROM table2
WHERE condition
);

Example: Find employees who earn more than the average salary
Assume a table employees

emp_id name salary

1 Ravi 30000

2 Sneha 45000

3 Aman 55000

4 Pooja 25000

SELECT name, salary

FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);

Contact: +91 74837 41501 Email: [Link]@[Link]

 Data Knowledge – SQL Master in Data Analyst

This query:

• First calculates the average salary using a subquery.

• Then fetches employees whose salary is greater than that average.

Types of Subqueries:

Type Description

Single-row Returns one value (e.g., =, <, > operators)

Multi-row Returns multiple values (e.g., IN, ANY, ALL)

Correlated Refers to outer query row-by-row (runs multiple times)

Nested in SELECT Subquery in the SELECT clause to compute values

Subquery Use Query Example Purpose / What it Does

sql UPDATE employees SET salary = salary +

Updates employees earning less
Subquery in UPDATE 2000 WHERE salary < (SELECT AVG(salary)
than average salary
FROM employees);

sql SELECT dept, avg_salary FROM (SELECT

dept, AVG(salary) AS avg_salary FROM Calculates and filters departments
Subquery in FROM
employees GROUP BY dept) AS dept_avg with high avg salary
WHERE avg_salary > 50000;

sql SELECT name FROM employees WHERE

Selects employees in Mumbai
Subquery with IN dept_id IN (SELECT dept_id FROM
departments
departments WHERE location = 'Mumbai');

sql SELECT name FROM employees e

WHERE EXISTS (SELECT 1 FROM Returns employees only if they
Subquery with EXISTS
departments d WHERE e.dept_id = d.dept_id belong to HR department
AND d.dept_name = 'HR');

sql SELECT name, salary, (SELECT Displays each employee’s salary

Subquery in SELECT AVG(salary) FROM employees) AS avg_salary with the company’s average salary
FROM employees; next to it

26. What Are SQL SET Operators?

Set operators allow you to combine results from two or more SELECT queries.
They must have:

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

• The same number of columns

• The same data type in each column position

1 UNION

• Combines two result sets and removes duplicates.

SELECT city FROM Customers

UNION
SELECT city FROM Suppliers;

Output: Unique list of cities from both tables.

2 UNION ALL
Combines all rows from both queries, including duplicates.

SELECT city FROM Customers

UNION ALL
SELECT city FROM Suppliers;
Output: Shows all cities — even if repeated.

3 INTERSECT
Returns only common rows present in both result sets.

SELECT city FROM Customers

INTERSECT
SELECT city FROM Suppliers;

4. EXCEPT (Also known as MINUS in Oracle)

Returns rows from the first query that are not present in the second.
SELECT city FROM Customers
EXCEPT
SELECT city FROM Suppliers;

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

Returns
Removes Shows Returns
Operator Description Only
Duplicates Duplicates Difference
Common

Combines and
UNION removes Yes No No No
duplicates

Combines and
UNION ALL keeps No Yes No No
duplicates

Returns only
INTERSECT Yes No Yes No
common rows

Returns rows
EXCEPT from 1st query Yes No No Yes
not in 2nd

27. What is a Stored Procedure in SQL?

A Stored Procedure is a pre-written block of SQL statements that is saved in

the database and can be executed whenever needed.

It’s like a function in programming — you write it once, and call it many
times.

Why Use Stored Procedures?

• Reusability: Use the same logic without rewriting code

• Performance: Compiled once and runs faster
• Security: Hide sensitive logic from users
• Easy Maintenance: Changes are centralized

Syntax:

CREATE PROCEDURE procedure_name

AS

BEGIN

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

-- SQL statements

END;

Example: Simple Stored Procedure

Let’s create a stored procedure that returns all employees in the "Sales"
department:

CREATE PROCEDURE GetSalesEmployees

AS

BEGIN

SELECT * FROM Employees

WHERE Department = 'Sales';

END;

Run the procedure using:

EXEC GetSalesEmployees;

Example with Input Parameter

CREATE PROCEDURE GetEmployeesByDept

@DeptName VARCHAR(50)

AS

BEGIN

SELECT * FROM Employees

WHERE Department = @DeptName;

END;

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

Call it like:

EXEC GetEmployeesByDept 'HR';

Common Use Cases:

• Generate reports
• Perform batch updates
• Encapsulate complex business logic
• Maintain data integrity

28. What is a Window Function in SQL?

A Window Function performs a calculation across a set of table rows that are
related to the current row, without collapsing rows like GROUP BY does.

It lets you add extra info (like running totals, ranks, averages) while keeping
all original rows.

Syntax:

function_name (column) OVER (

[PARTITION BY col1]

[ORDER BY col2]

Common Window Functions:

Function Use Case

ROW_NUMBER() Gives unique row number per group
RANK() Ranks rows with gaps
DENSE_RANK() Ranks without gaps
NTILE(n) Divides rows into n buckets
SUM(), AVG() Running totals, moving averages
LEAD(), LAG() Next or previous row values

Example Table: Employees

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

ID Name Dept Salary

1 Ravi HR 30000
2 Pooja HR 40000
3 Aman IT 35000
4 Sneha IT 45000

1.ROW_NUMBER() Example

SELECT Name, Dept, Salary,

ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY Salary

DESC) AS RowNum

FROM Employees;

Gives each employee a row number within their department, ordered by

salary.

Name Dept Salary RowNum

Pooja HR 40000 1
Ravi HR 30000 2
Sneha IT 45000 1
Aman IT 35000 2

[Link]() OVER (ORDER BY Salary DESC)

SELECT Name, Dept, Salary,

RANK() OVER (ORDER BY Salary DESC) AS SalaryRank

FROM Employees;

Name Dept Salary SalaryRank

Sneha IT 45000 1
Pooja HR 40000 2

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

Name Dept Salary SalaryRank

Aman IT 35000 3
Ravi HR 30000 4

3. SUM(Salary) OVER (ORDER BY ID)

SELECT Name, Dept, Salary,

SUM(Salary) OVER (ORDER BY ID) AS RunningTotal

FROM Employees;

Name Dept Salary RunningTotal

Ravi HR 30000 30000
Pooja HR 40000 70000
Aman IT 35000 105000
Sneha IT 45000 150000

4. What is DENSE_RANK()

DENSE_RANK() is a window function that assigns ranks to rows in a result

set without skipping any rank when there are ties.

Syntax:

DENSE_RANK() OVER (ORDER BY column ASC|DESC)

SELECT Name, Salary,

DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank

FROM Employees;

Name Salary SalaryRank

Pooja 60000 1
Aman 60000 1
Ravi 50000 2
Sneha 45000 3

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

If we used RANK() instead of DENSE_RANK(), Ravi would get rank 3 (since

2nd rank is skipped), but with DENSE_RANK(), he gets rank 2 (no skip)

29. What is a VIEW in SQL?

A View is a virtual table based on the result of an SQL query.

It doesn’t store data physically but shows data from one or more tables.

Think of a view as a saved SQL SELECT query that you can treat like a
table.

Why Use Views?

• Simplify complex queries

• Improve security by hiding specific columns
• Reuse logic across multiple places
• Maintain consistency

Syntax to Create a View

CREATE VIEW view_name AS

SELECT column1, column2

FROM table_name

WHERE condition;

Suppose you have a table Employees:

ID Name Dept Salary

1 Ravi HR 30000
2 Pooja HR 40000
3 Aman IT 35000
4 Sneha IT 45000

Create a view to see only HR department employees:

CREATE VIEW HR_Employees AS

SELECT Name, Salary

FROM Employees

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

WHERE Dept = 'HR';

Now use the view like a table:

SELECT * FROM HR_Employees;

To Update a View:

CREATE OR ALTER VIEW HR_Employees AS

SELECT Name, Salary

FROM Employees

WHERE Dept = 'HR';

To Drop a View:

DROP VIEW HR_Employees;

30. What is an ERD (Entity-Relationship Diagram)?

An ERD (Entity-Relationship Diagram) is a visual representation of a

database structure, showing how tables (entities) are related to each other
through relationships.

It’s used in database design to plan and understand the structure before
building the database.

Key Components of an ERD:

Component Description
Entity A table in the database (e.g., Customer, Order)
Attribute A column in a table (e.g., CustomerID, Name)
Primary Key A unique identifier for each row in a table
A key in one table that links to a primary key in another
Foreign Key
table

Contact: +91 74837 41501 Email: [Link]@[Link]

 Data Knowledge – SQL Master in Data Analyst

Component Description
The connection between tables (one-to-one, one-to-
Relationship
many, many-to-many)

31 .What is a Star Schema in SQL/Data Warehousing?

A Star Schema is a database schema design commonly used in data

warehouses. It organizes data into fact and dimension tables in a way that
looks like a star when diagrammed.

It's optimized for query performance, especially for reporting and

analytics.

Components of Star Schema:

Component Description
Central table that stores quantitative data
Fact Table
(e.g., sales, profit)
Dimension Surrounding tables that store descriptive
Table attributes (e.g., product, time, region)

Example: Sales Star Schema

Fact Table: SalesFact

Quantit TotalA
SaleID ProductID CustomerID DateID
y mount

Dimension Tables:

1. ProductDim
| ProductID | ProductName | Category | Price |
2. CustomerDim
| CustomerID | Name | Region |
3. DateDim
| DateID | Date | Month | Year |

Star Schema Diagram:

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

Benefits of Star Schema:

• Simplifies complex queries

• Fast aggregations (ideal for OLAP)
• Easy for BI tools and reporting
• Intuitive and user-friendly

[Link] is CTE (Common Table Expression) in SQL?

A CTE (Common Table Expression) is a temporary result set that you can
reference within a SELECT, INSERT, UPDATE, or DELETE statement.

It improves readability, allows recursion, and simplifies complex joins

and subqueries.

Syntax:

WITH CTE_Name AS (

SELECT column1, column2

FROM TableName

WHERE condition

SELECT * FROM CTE_Name;

Example

Suppose you have a table called Employees

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

ID Name Dept Salary

1 Ravi HR 30000
2 Pooja HR 40000
3 Aman IT 35000
4 Sneha IT 45000

Basic CTE Example

Get employees with salary > 35000 using a CTE:

WITH HighSalary AS (

SELECT Name, Salary

FROM Employees

WHERE Salary > 35000

SELECT * FROM HighSalary;

Output:

Name Salary
Pooja 40000
Sneha 45000

Ex. Get the highest-paid employee(s) from each department —

including duplicates if salaries tie.

ID Name Dept Salary

1 Ravi HR 30000
2 Pooja HR 40000
3 Aman IT 35000
4 Sneha IT 45000
5 Manish IT 45000
6 Komal Admin 25000

WITH RankedEmployees AS (

SELECT Name, Dept, Salary,

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

RANK() OVER (PARTITION BY Dept ORDER BY Salary DESC)

AS SalaryRank

FROM Employees

SELECT Name, Dept, Salary

FROM RankedEmployees

WHERE SalaryRank = 1;

Output:

Name Dept Salary

Pooja HR 40000
Sneha IT 45000
Manish IT 45000
Komal Admin 25000

33. How to Handel NULL values

-- 1. Check NULL and NOT NULL

SELECT *

FROM Employees

WHERE ManagerID IS NULL;

SELECT *

FROM Employees

WHERE ManagerID IS NOT NULL;

-- 2. Replace NULL with default (ISNULL)

SELECT EmployeeName,

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

ISNULL(ManagerID, 0) AS ManagerID

FROM Employees;

-- 3. Replace NULL with first non-null (COALESCE)

SELECT EmployeeName,

COALESCE(PhoneNumber, Email, 'Not Available') AS Contact

FROM Employees;

-- 4. Turn equal values into NULL (NULLIF)

SELECT NULLIF(10, 10) AS Result; -- Returns NULL

SELECT NULLIF(10, 20) AS Result; -- Returns 10

-- Avoid divide by zero

SELECT SalesAmount / NULLIF(Quantity, 0) AS AvgPrice

FROM Sales;

-- 5. Handle NULL with CASE

SELECT EmployeeName,

CASE

WHEN ManagerID IS NULL THEN 'No Manager'

ELSE CAST(ManagerID AS VARCHAR)

END AS ManagerInfo

FROM Employees;

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

-- 6. Aggregates ignore NULLs automatically

SELECT COUNT(*) AS TotalRows, -- counts all rows

COUNT(ManagerID) AS NonNullManagers -- ignores NULLs

FROM Employees;

35. Query Optimization

--------------------------------------------------

-- 1. Select Only Needed Columns

--------------------------------------------------

-- Bad

SELECT * FROM Employees;

-- Good

SELECT EmployeeID, EmployeeName, Salary

FROM Employees;

--------------------------------------------------

-- 2. Create Indexes

--------------------------------------------------

-- Non-clustered index on ManagerID

CREATE NONCLUSTERED INDEX IX_Employees_ManagerID

ON Employees (ManagerID);

--------------------------------------------------

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

-- 3. Avoid Functions on Indexed Columns

--------------------------------------------------

-- Bad (index not used)

SELECT * FROM Employees

WHERE YEAR(JoinDate) = 2020;

-- Good (index friendly)

SELECT * FROM Employees

WHERE JoinDate >= '2020-01-01'

AND JoinDate < '2021-01-01';

--------------------------------------------------

-- 4. EXISTS Instead of IN

--------------------------------------------------

-- Bad

SELECT * FROM Employees

WHERE DeptID IN (SELECT DeptID FROM Departments);

-- Good

SELECT * FROM Employees e

WHERE EXISTS (

SELECT 1 FROM Departments d

WHERE [Link] = [Link]

);

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

--------------------------------------------------

-- 5. Avoid DISTINCT / ORDER BY if Not Needed

--------------------------------------------------

SELECT EmployeeName

FROM Employees; -- Only sort when required

--------------------------------------------------

-- 6. JOIN Instead of Subquery

--------------------------------------------------

-- Bad

SELECT [Link],

(SELECT [Link]

FROM Departments d

WHERE [Link] = [Link]) AS DeptName

FROM Employees e;

-- Good

SELECT [Link], [Link]

FROM Employees e

JOIN Departments d ON [Link] = [Link];

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

--------------------------------------------------

-- 7. Use SET NOCOUNT ON in Stored Procs

--------------------------------------------------

CREATE PROCEDURE GetEmployees

AS

BEGIN

SET NOCOUNT ON;

SELECT EmployeeID, EmployeeName

FROM Employees;

END;

--------------------------------------------------

-- 8. Execution Plan

--------------------------------------------------

-- In SSMS, press CTRL + M before running query

-- Look for Index Seek , avoid Table Scan

--------------------------------------------------

-- 9. Break Large Queries (CTE Example)

--------------------------------------------------

;WITH EmployeeCTE AS (

SELECT EmployeeID, Salary

FROM Employees

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

WHERE Salary > 50000

SELECT *

FROM EmployeeCTE

WHERE EmployeeID < 1000;

--------------------------------------------------

-- 10. Keep Statistics Updated

--------------------------------------------------

UPDATE STATISTICS Employees;

36. JOIN vs Subquery

Feature /
JOIN Subquery
Aspect
A query inside another
Combines rows from two
query, used to fetch
Definition or more tables based on
results for the main
a related column.
query.
Retrieves data from
Retrieves data from
Data one table and uses it
multiple tables in a single
Combination as input for another
result set.
query.
Usually faster (especially Sometimes slower,
with proper indexing) especially correlated
Performance
because SQL Server can subqueries (executed
optimize joins well. row by row).
Easier to read when Easier when you just
Readability combining multiple need to filter with one
related tables. extra condition.
Best for filtering,
Best for showing related
checking existence, or
data side by side (e.g.,
Use Case calculations (e.g.,
Employees with
salaries greater than
Department names).
average).

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

Feature /
JOIN Subquery
Aspect
Can be independent
Performed in a single
(non-correlated) or run
Execution execution plan (set-
multiple times
based).
(correlated).
sql SELECT
sql SELECT EmployeeName
[Link], FROM Employees
[Link] WHERE DeptID IN
Example
FROM Employees e (SELECT DeptID
JOIN Departments d ON FROM Departments
[Link] = [Link]; WHERE Location =
'NY');
INNER JOIN, LEFT Scalar subquery,
Types JOIN, RIGHT JOIN, correlated subquery,
FULL JOIN. nested subquery.

[Link] SQL code that might ask in interview

Code with Useful SQL

Question
No. Concepts
SELECT DISTINCT Salary
FROM (SELECT Salary,
Find the top 3
DENSE_RANK() OVER
1 highest
(ORDER BY Salary DESC) AS
salaries
rnk FROM Employees) AS
ranked WHERE rnk <= 3;
Find duplicate SELECT Name, Dept,
records based COUNT(*) AS count FROM
2
on Name and Employees GROUP BY Name,
Dept Dept HAVING COUNT(*) > 1;
WITH CTE AS ( SELECT *,
ROW_NUMBER()
Delete OVER(PARTITION BY Name,
3 duplicate rows Dept, Salary ORDER BY ID)
(keep 1 copy) AS rn FROM Employees)
DELETE FROM CTE WHERE
rn > 1;
Remove
SELECT DISTINCT * INTO
duplicates
4 Employees_Clean FROM
from table into
Employees;
new table

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

Code with Useful SQL

Question
No. Concepts
Find total
SELECT Dept, SUM(Salary)
salary
5 AS TotalSalary FROM
department-
Employees GROUP BY Dept;
wise
SELECT * FROM Employees e
Employees
WHERE Salary > (SELECT
earning more
6 AVG(Salary) FROM
than dept
Employees WHERE Dept =
average
[Link]);
Second SELECT MAX(Salary) FROM
highest salary Employees WHERE Salary <
7
using (SELECT MAX(Salary) FROM
subquery Employees);
Employees
SELECT * FROM Employees
8 with NULL
WHERE Dept IS NULL;
departments
Count of SELECT Dept, COUNT(*) AS
9 employees per EmpCount FROM Employees
department GROUP BY Dept;
Update UPDATE Employees SET
10 salaries by Salary = Salary * 1.10 WHERE
10% in IT Dept = 'IT';
Employees SELECT Salary FROM
11 with same Employees GROUP BY Salary
salary HAVING COUNT(*) > 1;
SELECT *, RANK()
Rank
OVER(PARTITION BY Dept
12 employees by
ORDER BY Salary DESC) AS
salary in dept
SalaryRank FROM Employees;
SELECT * FROM Employees
Employees
WHERE JoinDate >=
13 joined in last 6
DATEADD(MONTH, -6,
months
GETDATE()); (SQL Server)
WITH CTE AS ( SELECT *,
Max salary RANK() OVER(PARTITION BY
employee in Dept ORDER BY Salary
14
each dept (tie DESC) AS rnk FROM
cases) Employees) SELECT * FROM
CTE WHERE rnk = 1;
SELECT SUM(Salary),
Total, Min,
MIN(Salary), MAX(Salary),
15 Max, Avg
AVG(Salary) FROM
salary
Employees;

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

38. 2nd highest salary

--------------------------------------------------
-- Sample Table
--------------------------------------------------
CREATE TABLE Employees (
EmpID INT,
EmpName VARCHAR(50),
Salary INT
);

INSERT INTO Employees VALUES

(1, 'Amit', 50000),
(2, 'Ravi', 60000),
(3, 'Sita', 55000),
(4, 'Meena', 70000),
(5, 'Arjun', 60000);

--------------------------------------------------
-- 1. TOP with ORDER BY
--------------------------------------------------
SELECT TOP 1 Salary
FROM (
SELECT DISTINCT TOP 2 Salary
FROM Employees
ORDER BY Salary DESC
) AS Temp
ORDER BY Salary ASC;

--------------------------------------------------

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

-- 2. MAX with WHERE

--------------------------------------------------
SELECT MAX(Salary) AS SecondHighest
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

--------------------------------------------------
-- 3. ROW_NUMBER()
--------------------------------------------------
;WITH SalaryRank AS (
SELECT Salary,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS rn
FROM (SELECT DISTINCT Salary FROM Employees) AS s
)
SELECT Salary
FROM SalaryRank
WHERE rn = 2;

--------------------------------------------------
-- 4. RANK()
--------------------------------------------------
;WITH SalaryRank AS (
SELECT Salary,
RANK() OVER (ORDER BY Salary DESC) AS rnk
FROM Employees
)
SELECT DISTINCT Salary
FROM SalaryRank
WHERE rnk = 2;

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

--------------------------------------------------
-- 5. OFFSET FETCH (SQL Server 2012+)
--------------------------------------------------
-- Get 2nd highest salary
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
OFFSET 1 ROW FETCH NEXT 1 ROW ONLY;

39. Top N Salaries

--------------------------------------------------
-- Sample Table
--------------------------------------------------
CREATE TABLE Employees (
EmpID INT,
EmpName VARCHAR(50),
Salary INT
);

INSERT INTO Employees VALUES

(1, 'Amit', 50000),
(2, 'Ravi', 60000),
(3, 'Sita', 55000),
(4, 'Meena', 70000),
(5, 'Arjun', 60000);

--------------------------------------------------
-- 1. TOP N Salaries (Without Duplicates)
--------------------------------------------------
-- Example: Top 3 distinct salaries

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

SELECT DISTINCT TOP 3 Salary

FROM Employees
ORDER BY Salary DESC;

--------------------------------------------------
-- 2. ROW_NUMBER() Method
--------------------------------------------------
-- Example: Top 3 salaries
;WITH SalaryRank AS (
SELECT Salary,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS rn
FROM (SELECT DISTINCT Salary FROM Employees) AS s
)
SELECT Salary
FROM SalaryRank
WHERE rn <= 3;

--------------------------------------------------
-- 3. RANK() Method (Handles ties)
--------------------------------------------------
;WITH SalaryRank AS (
SELECT Salary,
RANK() OVER (ORDER BY Salary DESC) AS rnk
FROM Employees
)
SELECT DISTINCT Salary
FROM SalaryRank
WHERE rnk <= 3;

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

--------------------------------------------------
-- 4. OFFSET FETCH (SQL Server 2012+)
--------------------------------------------------
-- Get Top N salaries with skip
-- Example: Skip 0 (start from highest), fetch 3 salaries
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

40. SQL Practice / Interview Questions

Salary-Based (Common in Interviews)

1. Find the highest salary from the Employees table.
2. Find the 2nd highest salary (give at least 2 different approaches).
3. Find the Nth highest salary (generic query).
4. Find the top 3 salaries (handle duplicates using RANK).
5. Find all employees who earn the maximum salary.
6. Find employees who earn the same salary.
7. Find the difference between highest and lowest salary.
8. Find the average salary of each department.
9. Find departments where the average salary is greater than 60,000.
10. Find the minimum salary of each department using GROUP BY.
Ranking Functions (Frequently Asked)
11. Show each employee’s salary along with their rank (RANK()).
12. Show employee salary ranking without skipping ranks (DENSE_RANK()).
13. Show employee salary ranking strictly (ROW_NUMBER()).
14. Display the 2nd highest salary using OFFSET FETCH.
15. Display the 3rd highest salary using DENSE_RANK().
Joins (Common in Real Projects)
Assume: Employees(EmpID, EmpName, Salary, DeptID)
and Departments(DeptID, DeptName)
16. List all employees with their department names.

Contact: +91 74837 41501 Email: [Link]@[Link]

Data Knowledge – SQL Master in Data Analyst

17. List employees who don’t belong to any department.

18. Find departments with no employees.
19. Show the highest paid employee in each department.
20. Show the total salary department-wise.
NULL & Data Handling
21. Show employees who don’t have a manager (ManagerID IS NULL).
22. Replace NULL salaries with 0 using ISNULL.
23. Replace NULL values in PhoneNumber with "Not Available".
24. Count total employees vs employees with assigned departments.
25. Handle divide-by-zero using NULLIF.
Tricky/Scenario-Based
26. Find employees whose salary is greater than the average salary.
27. Find the second highest salary in each department.
28. Find employees who earn more than their manager.
29. Find duplicate salaries in the Employees table.
30. Retrieve the Nth highest salary dynamically using a variable (e.g., @N = 4).

Contact: +91 74837 41501 Email: [Link]@[Link]