📘Analytics

Module 1: Introduction to Data

🔷 1. Introduction to Course
This course will introduce you to the core tools and steps used in Data Analytics, including hands-
on learning with:

📊 Microsoft Excel
🗃️ SQL
🐍 Python
You will learn how to analyze, visualize, and understand data to help in informed decision-making.

📊 2. What is Data Analytics?

🔹 Data Analytics is the process of examining raw data to uncover patterns, draw conclusions, and
support decision-making.

🎯 Purpose:
Gain meaningful insights from data.

Drive informed business decisions.

Identify trends and patterns.

🧠 3. Why is Data Analytics Important?

✅ Helps in:
Making data-driven decisions.

Understanding customer behavior.

Detecting fraud or anomalies.

Optimizing business operations.

Enhancing marketing strategies.

📘 Module 1: Introduction to Data Analytics 1

 🪜 4. Steps to Analyze Datasets
Each step plays a critical role in ensuring accurate and insightful analysis:

🔢 Step 💡 Description
📥 1. Data Collection Gathering raw data from different sources like surveys, sensors, logs, databases.
💾 2. Data Storage Storing data securely in formats like CSV, databases, cloud, etc.
⚙️ 3. Data Processing Transforming raw data into usable formats (e.g., converting dates, parsing values).
🧹 4. Data Cleansing Removing errors, duplicates, and filling missing values.
📈 5. Data Analysis Applying techniques to interpret the data (visuals, statistics, patterns).

🛠️ 5. Tools Used in This Course

Tool Purpose

🟩 Microsoft Excel For quick analysis, charts, pivot tables, basic data cleaning.

🟦 SQL (Structured Query To retrieve, filter, and manage data from relational databases.
Language)

🐍 Python For advanced analytics, automation, and visualizations using libraries like
pandas , matplotlib .

🧠 Key Points to Remember

📌 Data is an asset – Better data leads to better decisions
📌 Clean data = Reliable results
📌 Choose the right tool (Excel, SQL, Python) depending on the task
📌 Always understand the source and structure of your data before analysis
📌 Data analysis is an iterative process – refining and revisiting is normal

📘 Module 1: Introduction to Data Analytics 2

 📘 Module 2: Excel Basics
1️⃣ BASICS
🖥 Installing Excel
Go to [Link] or use the app store

Sign in with a Microsoft account

Install and launch Excel

📂 Opening Excel
Search "Excel" from the taskbar

Click to open

Choose recent files or browse via Open

➕ Creating a New Workbook

Select Blank Workbook or a template

Excel file = Workbook

Tabs inside = Worksheets

🧾 Workbook Components
Element Layout Description

Rows 1, 2, 3… Horizontal

Columns A, B, C… Vertical

Cell A1, B2, etc. Row + Column intersection

📌 Ribbon Tabs Overview

Tab Use

Home Formatting, font, cut/copy/paste

Insert Charts, images, tables

Page Layout Margins, themes

Formulas SUM, AVERAGE, etc.

Data Sorting, filtering

Review Spelling, comments

View Zoom, Freeze Panes

💡 Tip: Ctrl + N → New Workbook

📘 Module 2: Excel Basics 1

 2️⃣ WORKSHEET BASICS
🔹 What is a Worksheet?
A sheet/page inside an Excel file

Use multiple sheets for organised data

🛠 Sheet Operations
Action How-To

Rename Double-click the tab name

Add Click ➕ next to last sheet

Delete Right-click tab → Delete

Move Drag tab left or right

Color tab Right-click → Tab Color

View full name Hover if cut off

📊 Example Usage
Sheet 1: Marks

Sheet 2: Attendance

Sheet 3: Summary

3️⃣ DATA FORMATS

📝 Editing Cells
Double-click a cell or use the formula bar

🔢 Types of Data
Type Example Use

Text "Name", "Class A" Labels or headings

Number 150, -20, 99.50 For calculations

Date 15 May 2025 Format in Date style

Formula =A1+B1 Starts with =

📊 Format Options (Home → Number group)

General, Number, Currency, Accounting

Date, Time, Percentage, Fraction

Scientific, Text, Special, Custom

💡 Excel dates start from 01 Jan 1900

📘 Module 2: Excel Basics 2

 4️⃣ DATA HANDLING BASICS (CUT, COPY & PASTE)
⌨️ Navigation Shortcuts
Action Shortcut

Jump to last filled cell Ctrl + Arrow key

Select data step-by-step Shift + Arrow

Select all in direction Ctrl + Shift + Arrow

🔁 Cut, Copy, Paste

Action Shortcut

Cut Ctrl + X

Copy Ctrl + C

Paste Ctrl + V

Undo Ctrl + Z

Redo Ctrl + Y

📎 Paste Options
Option Use

Paste All data & formatting

Formulas Only formulas

Values Only result values

Formatting Style only

Transpose Switch rows ↔ columns

Picture Paste as an image

Keep Source Maintain original formatting

❌ Deleting
Delete content: Select cell → Press Delete

Delete row/column: Right-click → Delete

Clear: Removes content only (not structure)

5️⃣ SAVING AND PRINTING BASICS

💾 Save Options
Option Shortcut Use

Save Ctrl + S Save current file

Save As F12 Save new file or copy

📘 Module 2: Excel Basics 3

 🛡 Protect Workbook
Go to: File → Info → Protect Workbook → Set Password

This allows you to add password protection to your workbook for security.

🖨 Printing
File → Print or Ctrl + P

This opens the print dialogue to select your printer and configure settings.

📑 Views
Located at the bottom right corner.

View Name Use

Normal View Default view for editing

Page Layout View Adjust margins and page layout

Page Break Preview View how data splits across pages

📘 Module 2: Excel Basics 4

 📘 Module 3: Essential Formulas
🎯 Purpose of Formulas in Excel
Formulas in Excel allow users to perform automatic calculations using values in cells. They're used
to:

Analyse and summarise data

Automate repetitive calculations

Maintain data consistency and reduce human error

✍ Where to Write a Formula

You can type a formula:

Directly in the cell (start with = )

Or in the formula bar (top of the Excel window)

✅ All formulas must start with an equal sign (=)

✅ BASIC FORMULA OPERATIONS
📌 Why Are We Using Cell Referencing?
Using cell references in formulas helps:

Auto-update results if input values change

Apply the same logic across rows/columns

Avoid manual errors and save time

📌 Apply Formula to Multiple Rows

1. Enter the formula in the first row.

2. Hover at the bottom-right corner → shows a + symbol

3. Drag down to copy the formula across cells

👣 Apply Across Records (AutoFill):

1. Enter =SUM(A2:A5) in the first row.

2. Select the cell.

3. Drag the bottom-right corner (the small + sign) down to apply the formula across rows.

This auto-updates cell references if relative referencing is used.

📘 Module 3: Essential Formulas 1

 📎 Types of Cell References
Type Example What it does

Relative A1 Changes as you copy the formula

Absolute $A$1 Fixed column and row

Mixed (Column Lock) $A1 Column stays fixed, row changes

Mixed (Row Lock) A$1 Row stays fixed, column changes

➕ SUM FORMULA
🧾 Syntax: =SUM(number1, number2, ...)
Or for a range: =SUM(D2:D10)

✅ Basic Usage:
Adds up all numeric values in the specified cells or range.

➕ Adding a Constant Value to Each Cell in a Column

Scenario:
Add ₹50 delivery charge to every product price.
Assume:

Prices in A2:A10

Delivery fee in cell B1 = 50

Formula in C2 : =A2 + $B$1

Now drag down the formula from C2 . The A2 part changes (A3, A4...), but $B$1 stays constant.

🧠 Tip: Use $ for constants or fixed references in repeated formulas.

📘 Module 3: Essential Formulas 2

 ✖ PRODUCT FORMULA
🧾 Syntax: =PRODUCT(number1, number2, ...)
Or: =PRODUCT(A1:A3)

✅ Usage:
Multiplies all values in the specified range.

Example:

➗ AVERAGE FORMULA
🧾 Syntax: =AVERAGE(number1, number2, ...)
Or: =AVERAGE(A1:A5)

✅ Usage:
Calculates the mean value from the range.

Example:

🧠 Tip: Use with relative references unless averaging with a fixed bonus (e.g., =AVERAGE(A2, $B$1) ).

📘 Module 3: Essential Formulas 3

 🧠 KEY TAKEAWAYS
Use Relative Reference for formulas that adapt per row/column.

Use Absolute Reference ( $ ) to fix a cell in all copied formulas.

Use Mixed Reference for semi-dynamic formulas in tables.

SUM, PRODUCT, and AVERAGE work similarly and support ranges, constants, and
relative/absolute referencing.

Drag Down (Fill Handle) is your best friend for repeating patterns efficiently.

🧮 Part 2: Mathematical Functions in Excel

🧾 Before You Begin: Formula Tips
✅ Formula Suggestion Feature
When you type = followed by letters (e.g., =S ), Excel automatically shows a dropdown list of
matching functions, such as:

=SUM
=SUBTOTAL
=SQRT

You can press Tab to autocomplete a function from the list.

🔍 Use of : in Formulas
The colon : is used to define a range between two cells.

For example:

A1:A5 → refers to cells A1, A2, A3, A4, and A5

B2:D2 → includes B2, C2, and D2

Used in almost all functions like SUM , MIN , MAX , etc.

➕ SUM()
📘 Definition:
Adds values in a selected range.

🧾 Example: =SUM(D2:D10)
✅ Use Case:
Find total marks, total sales, etc.

📘 Module 3: Essential Formulas 4

 🔁 Fill Down:
Use fill handle (bottom-right corner) to apply the formula across rows.

Excel automatically adjusts the cell references (if not using $ ).

🔻 MIN() and 🔺 MAX()

📘 Definitions:
MIN() returns the smallest value in the range.

MAX() returns the largest value in the range.

🧾 Example:
=MIN(D5:D16)
=MAX(D5:D16)

✅ Use Case:
Find the lowest/highest score in a test.

Identify the minimum or maximum sales in a month.

📘 Module 3: Essential Formulas 5

 📊 AVERAGE() / AVG()
📘 Definition:
Returns the arithmetic mean (sum ÷ count).

🧾 Syntax: =AVERAGE(A1:A5)
Note: AVG() is not a valid function. Use AVERAGE() .

✅ Use Case:
Calculate average marks.

Analyze trends like monthly sales averages.

📘 Module 3: Essential Formulas 6

 🏅 RANK()
📘 Definition:
Displays the rank of a number in a list.

🧾 Syntax: =RANK(number, ref_range, [order])

number : the value to rank.

ref_range : list of values.

order : 0 for descending (default), 1 for ascending.

✅ Example: =RANK(A2, A2:A10, 0)

Ranks the value in A2 among A2 to A10.

📦 SUMPRODUCT()
📘 Definition:
Multiplies corresponding elements in two (or more) arrays and returns their sum.

🧾 Syntax: =SUMPRODUCT(array1, array2)

✅ Use Case:
Total cost: Multiply price × quantity row-wise.

=SUMPRODUCT(C3:C11, D3:D11)/SUM(D3:D11)

🎲 RAND() & RANDBETWEEN()

📘 RAND()
Returns a random decimal between 0 and 1.

Changes every time the worksheet recalculates.

🧾 Syntax: =RAND()

📘 Module 3: Essential Formulas 7

 📘 RANDBETWEEN()
Returns a random integer between two numbers (inclusive).

🧾 Syntax: =RANDBETWEEN(bottom, top)

✅ Example: =RANDBETWEEN(B5,C5)

⚠ Issues with RAND/RANDBETWEEN:

Dynamic updates: The value keeps changing whenever the sheet recalculates (pressing
Enter, editing a cell, etc.).

❗ If you need a fixed result, use:

Paste Special → Values to lock the current result.

📘 Module 3: Essential Formulas 8

 📋 PASTE OPTIONS (for RAND/RANDBETWEEN or any function)
When copying a formula like =RANDBETWEEN(1, 100) :

Right-click ➝ Paste Options:

Option Icon Use

Formulas fx Pastes only the formula (e.g., =RANDBETWEEN(...) )

Values 123 Pastes the result, not the formula

Formatting brush Pastes the visual style, not the data

Transpose ↕↔ Switches row to column or vice versa

Paste All 📋 Pastes everything (formulas, values, formatting)

🧠 How to Paste as Values:

1. Copy the cell with the formula ( Ctrl+C )

2. Right-click the destination cell

3. Select Paste Values (123 icon)

This will lock the random number so it doesn’t change.

💡 The Power of $ in Mathematical Functions

Use $ to fix cells when applying formulas across multiple rows/columns.

✅ Example in SUMPRODUCT with fixed price column:

=SUMPRODUCT(A2:A5, $B$2:$B$5)

This ensures the second range doesn’t change when copied.

🧠 Key Takeaways
Use : to define a range of cells.

Function suggestions appear as you type (use Tab to select).

Use RAND/RANDBETWEEN with care — they change on recalculation!

Use Paste as Values to lock random or formula-generated results.

$ is essential to fix cell references in any function (especially when copying).

📘 Module 3: Essential Formulas 9

 ✍️ Part 3: Textual Functions in Excel
🎯 Why Use Text Functions?
Excel is not just for numbers — text functions help you clean, analyze, or manipulate text data like
names, addresses, codes, etc.

Text functions help you:

Clean messy data

Join names or codes

Format text (like UPPER/lowercase)

Extract part of a string (like first name, last 4 digits, etc.)

🧭 Where to Find These?

📍 Formulas Ribbon
Go to: Formulas Tab → Function Library

✂ TRIM()
📘 What It Does:
Removes all extra spaces from a text string — leading, trailing, and additional spaces in between
words.
Cleans up text from left, right, and middle (keeps single spaces between words).

🧾 Syntax: =TRIM(text)
✅ Example:
=TRIM(" Hello World ") ➝ Hello World

⚠ Note:
The output is a formula. If you want plain text, use:

Copy ➝ Paste Special ➝ Values

📘 Module 3: Essential Formulas 10

 🧩 CONCATENATE / & / TEXTJOIN

📘 Purpose:
To join multiple text strings into one.

✅ Methods:
🔹 1. Using : =A1 & " " & B1
&

🔹 2. Using (newer): =CONCAT(A1, " ", B1)

CONCAT()

🔹C1)3. Using (best for skipping blanks): =TEXTJOIN(" ", TRUE, A1, B1,
TEXTJOIN()

🔁 SUBSTITUTE()
📘 Purpose:
Replaces specific text or characters with something else.

📘 Module 3: Essential Formulas 11

 🧾 Syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num])
instance_num is optional — use it to replace only the nth occurrence.

✅ Examples:
=SUBSTITUTE(B5, "t", "b")

🔠 UPPER(), LOWER(), PROPER()

📘 What They Do:
Function Result
=UPPER("hello") HELLO

=LOWER("HELLO") hello

=PROPER("hello world") Hello World

📏 LEN()
📘 Purpose:
Counts the number of characters (including spaces) in a text string.

🧾 Syntax: =LEN("Hello") ➝ 5
✅ Use:
Validate data (e.g., 10-digit phone numbers)

Count letters in a name

📘 Module 3: Essential Formulas 12

 🔄 LEFT(), RIGHT(), MID()
📘 Purpose:
To extract part of a string from the left, right, or middle.

🧾 Syntax & Examples:

🔹 LEFT: =LEFT("Excel", 2) ➝ "Ex"
🔹 RIGHT: =RIGHT("Excel", 2) ➝ "el"
🔹 MID: =MID("ExcelFun", 6, 3) ➝ "Fun"
Starts at character 6, takes 3 characters.

🧾 Example

🔧 Common Task: Combine Functions

Example:
Trim, Proper Case & Combine: =PROPER(TRIM(A1)) & " " & PROPER(TRIM(B1))

Use when names/emails are messy:

" joHN " + " doe " → John Doe

⚠ Paste Special: Formula to Plain Text

When using functions like TRIM , SUBSTITUTE , etc., the result stays as a formula.

To convert it to plain text:

🧾 Steps:
1. Copy the result column

2. Right-click on the target cell. Choose Paste Special → Values ( 📋123 icon)
This locks the value and removes the formula.

📘 Module 3: Essential Formulas 13

 🧠 Tips & Takeaways
Use & or TEXTJOIN() to merge cells with custom separators.

Use TRIM() to clean up unwanted spaces.

Use SUBSTITUTE() for smart text replacements — with optional instance control.

Always use Paste Special → Values when you want to freeze the result.

LEFT , RIGHT , and MID are essential for code/data extraction.

All major functions are available under the Formulas Ribbon > Text.

🧠 Part 4: Logical Functions

🎯 Why Use Logical Functions?
Logical functions help Excel make decisions based on conditions. They're powerful for creating
dynamic spreadsheets, such as:

Checking if marks are above a pass level

Giving results like "Pass"/"Fail", "Yes"/"No"

Counting/summing values based on conditions

✅ IF() Function
📘 Definition:
Checks a condition, and returns one value if TRUE, another if FALSE.

🧾 Syntax: =IF(condition, value_if_true, value_if_false)

✅ Examples:
With Text:
=IF(A2>=40, "Pass", "Fail")

If value in A2 is 40 or more, result = "Pass", else "Fail".

With Numbers:
=IF(C5≥70, “Pass”, “Fail”)

📘 Module 3: Essential Formulas 14

 🧪 AND() Function
📘 Definition:
Returns TRUE only if all conditions are TRUE.

🧾 Syntax:
=AND(condition1, condition2, ...)

✅ Example:
=AND(A2>=40, B2>=40)

Returns TRUE only if both A2 and B2 ≥ 40

Can be used inside IF:

📘 Module 3: Essential Formulas 15

 🔁 OR() Function
📘 Definition:
Returns TRUE if at least one condition is TRUE.

🧾 Syntax:
=OR(condition1, condition2, ...)

✅ Example:
=IF(OR(A2="Math", A2="Science"), "STEM", "Other")

🔢 COUNTIF()
📘 Definition:
Counts the number of cells that meet a condition.

🧾 Syntax:
=COUNTIF(range, criteria)

✅ Examples:
Count students with marks ≥ 40:
=COUNTIF(A2:A10, ">=40")

Count how many are from "Delhi":

=COUNTIF(B2:B20, "Delhi")

➕ SUMIF()
📘 Definition:
Adds up values only if they meet a condition.

🧾 Syntax:
=SUMIF(range_to_check, criteria, [sum_range])

If sum_range is omitted, Excel sums values in range_to_check .

✅ Examples:
Sum all sales above ₹10,000:
=SUMIF(A2:A20, ">10000")

Sum sales from Region "North":

=SUMIF(B5:B14, F5, D5:D14)

📘 Module 3: Essential Formulas 16

 🧠 Tips & Best Practices
Always use quotation marks around text criteria (e.g., "Pass" , ">=40" ).

Combine IF , AND , OR for complex logic.

Use COUNTIF/SUMIF to perform conditional analysis on large datasets.

You can use absolute referencing ( $ ) in logical formulas when copying across rows.

📍 Logical Functions Quick Summary

Function Use

IF() Make decision: one result for TRUE, another for FALSE

AND() TRUE only if all conditions are met

OR() TRUE if any condition is met

COUNTIF() Count how many cells meet a condition

SUMIF() Add only those values that meet a condition

⏰ Part 5: Date and Time Functions (Complete with DAYS() )

🎯 Why Use Date & Time Functions?

Date and time functions allow Excel to:

Insert real-time date/time values

Extract parts like day, month, or year

Calculate differences between two dates

Create timelines, deadlines, schedules, and reports

🗓 TODAY() and NOW()

Function Description Output Example
=TODAY() Returns current date 21-May-2025

=NOW() Returns current date & time 21-May-2025 14:35

📘 Module 3: Essential Formulas 17

 🔄 Auto-Update:
These functions recalculate automatically when the workbook changes or opens.

🧠 Freeze Result: Use Copy → Paste Special → Values to stop auto-update.

🧾 Date Formatting
To change how a date appears:

1. Select the cell

2. Go to: Home → Number Group → Format Cells (Ctrl + 1)

3. Choose from:

Short Date → 21/05/2025

Long Date → Wednesday, May 21, 2025

Time formats like 1:30 PM

🔍 DAY(), MONTH(), YEAR()

Extracts parts of a date:

Formula Result Purpose

=DAY("21-May-2025") 21 Extracts day
=MONTH("21-May-2025") 5 Extracts month (1–12)
=YEAR("21-May-2025") 2025 Extracts year

🧠 Useful in sorting or filtering by month/year.

📏 DATEDIF()
Calculates difference between two dates.

🧾 Syntax:
=DATEDIF(start_date, end_date, unit)

Unit Meaning
"d" Days
"m" Months
"y" Years

✅ Example:
=DATEDIF(B5, C5, "y") ➝ Age in years

📘 Module 3: Essential Formulas 18

 🔢 DAYS() Function
📘 Definition:
Returns the number of days between two dates — simple and direct.

🧾 Syntax:
=DAYS(end_date, start_date)

✅ Example:
=DAYS("31-Dec-2025", "01-Jan-2025") ➝ 364

⚠ Make sure the end date is second, or you’ll get a negative result.

🔄 Comparison:
Function Use Case
DATEDIF() Multiple units (years, months)
DAYS() Simple day count between 2 dates

📘 Module 3: Essential Formulas 19

 📅 NETWORKDAYS()
📘 Purpose:
Returns number of working days between two dates (excludes weekends, optionally holidays).

🧾 Syntax: =NETWORKDAYS(start_date, end_date, [holidays])

Add a range of holidays if needed.

🧠 Examples Using Today()

Task Formula

Days till new year =DAYS(DATE(2025,12,31), TODAY())

Age in full years =DATEDIF(DOB, TODAY(), "y")

Current month =MONTH(TODAY())

Days since joining date =DAYS(TODAY(), A2) (where A2 is join date)

🧠 Key Tips
Dates are stored as serial numbers (e.g., 21-May-2025 = 45141)

Use custom formats for readable output

Use DAYS() for quick differences, DATEDIF() for flexibility

Paste TODAY() or NOW() as values to freeze

🔍 Part 6: Lookup Functions

🎯 Why Use Lookup Functions?
Lookup functions allow Excel to search for a value in a range or table and return a corresponding
result. They are essential for:

Matching product codes to prices

Fetching student names, marks, or data from reference tables

Dynamic dashboard/report generation

🔎 1. VLOOKUP()
📘 Definition:
Searches vertically in the first column of a range and returns a value from a specified column.

🧾 Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

📘 Module 3: Essential Formulas 20

 Parameter Description
lookup_value The value to find
table_array The table range to search
col_index_num The column number (starting from 1) to return value from
range_lookup TRUE = Approximate match, FALSE = Exact match

✅ Example:

📉 2. HLOOKUP()
📘 Definition:
Searches horizontally in the top row of a range and returns a value from a specified row.

🧾 Syntax:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

✅ Example:

📘 Module 3: Essential Formulas 21

 🧩 3. INDEX()
📘 Definition:
Returns the value of a cell based on row and column numbers from a defined range.

🧾 Syntax:
=INDEX(array, row_num, [column_num])

✅ Example:
=INDEX(B5::E13, 5, 3)

Returns the value from 5nd row, 3rd column of range A2:C6.

🧠 Very useful when paired with MATCH() to create dynamic lookups.

🔢 4. MATCH()
📘 Definition:
Returns the position number of a value within a row or column.

🧾 Syntax:
=MATCH(lookup_value, lookup_array, [match_type])

Match Type Description

0 Exact match

1 Less than or equal

-1 Greater than or equal

✅ Example:

📘 Module 3: Essential Formulas 22

 🧠 5. INDEX + MATCH (Better than VLOOKUP)
Combines INDEX() and MATCH() to create a more flexible and powerful lookup (especially when
lookup column is not the first).

✅ Example:
=INDEX(C2:C10, MATCH("A102", A2:A10, 0))

MATCH("A102", A2:A10, 0) returns the row number

INDEX(C2:C10, …) returns the value from C column in that row

💪 Advantages over VLOOKUP:

Doesn’t require the lookup column to be the first

Faster and more efficient with large data. Supports left lookups (VLOOKUP cannot)

🧠 Use of $ in Lookup Functions

Use Case Example Why Use $

Lock the lookup table $A$2:$D$10 Prevents the range from shifting when copied

Lock the lookup column $A2 Fix column only

Lock the lookup row A$2 Fix row only

🧪 Lookup Functions Quick Summary

Function Use Case
VLOOKUP() Vertical search in first column
HLOOKUP() Horizontal search in top row
INDEX() Get value by row/column index
MATCH() Find position of a value
INDEX + MATCH Flexible, efficient lookup alternative

📘 Module 3: Essential Formulas 23

 💡 Real-Life Example
You have a product code in A2 , and a table in F2:H10 where:

Column F: Product Code

Column G: Product Name

Column H: Price

=VLOOKUP(A2, $F$2:$H$10, 3, FALSE)

Returns the price for the matching product code.

📌 BEST PRACTICES
Use Named Ranges for easier formula reading.

Avoid hardcoded values in formulas—use cell references.

Use $ (absolute referencing) for fixed references in reusable formulas.

Apply Paste Special → Values when you want to freeze result values.

Use IF + AND/OR for multi-condition logic.

Combine TEXT + DATE + LOGIC + LOOKUP functions for powerful dashboards.

📘 Module 3: Essential Formulas 24

 📘ExcelModule 4: XLOOKUP – Only for
2021 and Office 365
🔷 1. What is XLOOKUP?
✅ XLOOKUP is a powerful Excel function that replaces older lookup functions like VLOOKUP,
HLOOKUP, and LOOKUP.

📌 Syntax:
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

📘 Key Arguments:
Parameter Description
lookup_value Value to search for
lookup_array Where to look (column or row)
return_array What to return (column or row parallel to lookup_array)
if_not_found Optional – what to return if not found (e.g., "Not Found")

Optional – 0=Exact (default), -1=Exact or next smaller, 1=Exact or next larger,

match_mode
2=Wildcard match
search_mode Optional – 1=First-to-last (default), -1=Last-to-first

🆚 How XLOOKUP is Better than VLOOKUP

Feature VLOOKUP XLOOKUP

🔄 Direction Only left-to-right Left, right, up, down

🔁 Column No. Requires hardcoded column index Uses direct range

❌ NA Handling Needs IFERROR Has built-in if_not_found

✅ Dynamic Not dynamic if columns are changed Fully dynamic

🔎 Approx Match Limited, defaults to approx Exact by default

🔁 Reverse Lookup ❌ Not possible ✅ Possible

🧪 Example:

📘 Module 4: XLOOKUP – Only for Excel 2021 and Office 365 1

 ❗ 2. Handling #N/A and Approximate Matches in XLOOKUP
✅ Problem:
When lookup value is not found, #N/A is returned by default.

🧑‍💻 Handling #N/A Using if_not_found Argument:

🪜 Steps:
=XLOOKUP("Neha", A2:A10, B2:B10, "Not Found")

If “Neha” is not found, it returns "Not Found" instead of #N/A .

✅ Approximate Match in XLOOKUP:

Use match_mode:

0 → Exact match (default)

1 → Exact match or next smaller item

1 → Exact match or next larger item

🧑‍💻 Example:

📘 Module 4: XLOOKUP – Only for Excel 2021 and Office 365 2

 🧩 3. Wildcard Matching in XLOOKUP
✅ Match_mode 2 enables wildcard search using:
→ any number of characters

? → any single character

🪜 Steps to Use Wildcards:

=XLOOKUP("*an", A2:A10, B2:B10, "Not Found", 2)

This will find any name ending with “an” (e.g., “Mohan”, “Kiran”).

✅ You must set match_mode = 2 to activate wildcard logic.

🧑‍💻 Example:

📘 Module 4: XLOOKUP – Only for Excel 2021 and Office 365 3

 🔃 4. Search Modes in XLOOKUP
✅ Search_mode defines the direction of the lookup:
Mode Description
1 Search from top to bottom (default)
-1 Search from bottom to top (last match)
2 Binary search (sorted ascending)
-2 Binary search (sorted descending)

🧑‍💻 Example: Search from bottom (last match)

📘 Module 4: XLOOKUP – Only for Excel 2021 and Office 365 4

 🧰 Best Practices
✅ Practice 📌 Reason
Use Named Ranges To keep formula clean and avoid manual range update

Always set [if_not_found] To avoid showing #N/A to users

Use structured tables Works better with expanding datasets

Use match_mode = 2 only if wildcards are intended Avoid unexpected matches

Keep lookup and return arrays aligned Prevents formula errors and confusion

📌 Key Points / Important Notes

✅ XLOOKUP is more powerful and flexible than older lookup functions.
🔄 It can search in any direction – left, right, top, or bottom.
🚫 You don’t need column numbers – it uses ranges directly.
🧠 You can handle errors, match modes, and search direction inside the same function.
💡 Available only in Excel 2021 and Office 365.

📘 Module 4: XLOOKUP – Only for Excel 2021 and Office 365 5

 📘 Module 5: Data Tools
🔹 1. Sorting, Filtering & Data Validation
🔁 Sorting in Excel
🔹 Purpose:
To arrange data alphabetically, numerically, or by custom order.

✅ Types:
A → Z / Z → A (Text)

Smallest → Largest / Largest → Smallest (Numbers)

Custom (User-defined order)

🪜 Steps to Sort Data:

1. Click any cell in the column you want to sort.

2. Press Ctrl + Shift + ↓ to select full column.

3. Go to Home → Sort & Filter or Data → Sort.

4. Choose one of the following:

Sort A to Z (ascending)

Sort Z to A (descending)

Custom Sort → Add multiple levels (e.g., sort by Region, then by Sales)

5. Click OK.

🔍 Filtering in Excel
🔹 Purpose:
To temporarily hide rows that don’t meet specific criteria.

✅ Types:
Text Filter (Contains, Begins with)

Number Filter (Greater Than, Between)

Date Filter

Custom Filter (Multiple conditions)

🪜 Steps to Apply a Filter:

1. Select the header row of your data.

2. Go to Data → Filter (or Home → Sort & Filter → Filter).

📘 Module 5: Data Tools 1

 3. Drop-down arrows will appear in each column header.

4. Click the arrow on a column and apply your filter:

Use checkboxes or filter by condition.

5. View the filtered results instantly.

✅ Data Validation in Excel

🔹 Purpose:
To restrict user input and prevent invalid entries.

✅ Common Validation Rules:

Whole numbers only

A list of items (dropdown)

Maximum character length

Custom formulas

🪜 Steps to Apply Data Validation:

1. Select the cell(s) you want to validate.

2. Go to Data → Data Validation.

3. In the dialog box:

Under Allow, choose type (e.g., List, Whole Number, Text Length).

Define criteria (e.g., min = 1, max = 100).

4. (Optional) Add:

Input Message: Tooltip to guide the user.

Error Alert: Message shown when invalid data is entered.

5. Click OK.

📘 Module 5: Data Tools 2

 🔹 2. Text to Columns & Remove Duplicates
✂️ Text to Columns
🔹 Purpose:
Split data in a single column into multiple columns.

✅ Two Options:
Delimited (separated by comma, space, tab)

Fixed Width (split by character position)

🪜 Steps to Use Text to Columns:

1. Select the column containing the data you want to split.

2. Go to Data → Text to Columns.

3. Choose:

Delimited → click Next → choose delimiter (comma, tab, etc.)

Fixed Width → click Next → set the break lines

4. Click Finish.

🧹 Remove Duplicates
🔹 Purpose:
Remove exact duplicate rows based on selected columns.

🪜 Steps to Remove Duplicates:

1. Select your full data range.

2. Go to Data → Remove Duplicates.

3. In the dialog:

Tick the columns you want to check for duplicates.

4. Click OK → Excel will remove duplicates and show how many were removed.

📘 Module 5: Data Tools 3

 🔹 3. Advanced Filter Options
🎯 Purpose:
Filter data using complex conditions, wildcards, or show unique records, with output in a new
location.

✅ Special Features:
AND/OR logic using criteria range

Copy result to new location

Use of wildcards (, ? )

Show only unique records

🪜 Steps to Use Advanced Filter:

1. Create a criteria range above or beside your dataset with headers and conditions.

2. Select your original dataset.

3. Go to Data → Advanced under the "Sort & Filter" group.

4. In the dialog:

Set List Range (your main data)

Set Criteria Range (your condition area)

Choose to filter in place or copy to another location

Check Unique records only if needed

5. Click OK → The filtered result will appear.

📘 Module 5: Data Tools 4

 🔹 4. Formatting Data as Tables
🎯 Purpose:
Convert your data into a structured Excel table with enhanced features.

🪜 Steps to Format as Table:

1. Select the full dataset.

2. Go to Home → Format as Table.

3. Choose a table style from the dropdown.

4. Check “My table has headers” if your data has headers.

5. Click OK.

📌 Table Tools Available:

Enable Total Row (adds auto-calculated summary)

Add filters automatically

Auto-expand when new rows/columns are added

Use structured references in formulas

📘 Module 5: Data Tools 5

 🌟 Best Practices
Tool Best Practice

Sorting Always include headers; avoid sorting partial tables

Filtering Avoid filtering across merged cells

Data Validation Use for controlling user input (e.g., restrict to list only)

Text to Columns Backup data before splitting

Remove Duplicates Select only columns that should be checked

Advanced Filter Test logic first with small dataset

Format as Table Use for cleaner formatting + dynamic tables

📌 Key Points to Remember

✅ Use Ctrl + Shift + Arrow keys to select large datasets quickly
⚠️ Removing duplicates cannot be undone – always back up data
🔎 Use Data Validation to prevent entry mistakes
🧠 Advanced Filter is more powerful than normal filter (supports multiple rules)
📈 Tables automatically support filtering, sorting, and totals
🎨 Formatting as a table makes data easier to manage and present

📘 Module 5: Data Tools 6

 📘 Module 6: Excel Charts
📊 1. Importance of Data Visualization
Data Visualization means displaying data in a visual format (charts/graphs) to:

Quickly identify patterns and trends

Simplify complex data

Improve decision-making

Communicate insights effectively

✅ “A picture is worth a thousand numbers.”

📌 2. Elements of a Chart
Element Description

📍 Chart Title Describes what the chart represents

📊 Plot Area Area where the actual chart (bars, lines, etc.) is
displayed

🧭 Legend Explains what each color or symbol represents

📈 Axis (X & Y) X-axis = categories, Y-axis = values

🔢 Data Labels Show exact values on chart elements

🎨 Chart Area Entire area including title, legend, and plot

🔺 Gridlines Help in reading values on the chart

📎 Axis Titles Labels for X-axis and Y-axis to clarify data

🧑‍💻 3. Easy Way to Create Charts

🪜 Steps:
1. Select the data range (including headers).

2. Go to Insert → Charts group.

3. Choose a chart type (Column, Bar, Line, etc.).

4. Customize using Chart Design and Format tabs.

📊 4. Bar and Column Charts

✅ Explanation:
Column Chart: Vertical bars – best for comparisons.

Bar Chart: Horizontal bars – ideal when labels are long.

📘 Module 6: Excel Charts 1

 🔹 Types:
Clustered

Stacked

100% Stacked

🪜 Steps:
1. Select data

2. Go to Insert → Column or Bar Chart

3. Choose chart style

4. Use Chart Design → Add Chart Element for title, labels

5. Format using Format Chart Area

📍 Use for:
Comparing sales by region

Product-wise profit analysis

📘 Module 6: Excel Charts 2

 🎨 5. Formatting Charts
✅ Use “Format Chart Area” to change:
Area What You Can Do

Chart Area Background color, border

Plot Area Fill color, transparency

Legend Position, font style

Axis Label format, scale

Data Labels Add/remove values, customize size/color

Series Change color, outline, data shape

🪜 To Open Format Chart Area:

1. Right-click any chart area or element

2. Click Format Chart Area

3. Use the right-hand pane to apply formatting

✅ You can also use Chart Design tab to:

Change chart type

Switch row/column

Apply quick layouts

Change colors

📈 6. Line Charts
✅ Explanation:
Shows trends over time using lines.

Best for continuous data (time series).

🔹 Types:
Line

Line with markers

Stacked Line

🪜 Steps:
1. Select data with time/date on X-axis.

2. Go to Insert → Line Chart

3. Choose line type

4. Use Chart Design for layout

5. Format chart area and elements as needed

📘 Module 6: Excel Charts 3

 📍 Use for:
Monthly sales trends

Website traffic over weeks

⛰️ 7. Area Charts
✅ Explanation:
Similar to Line Charts but shaded area under the line.

Highlights volume along with trends.

🔹 Types:
Area

Stacked Area

100% Stacked Area

🪜 Steps:
1. Select data

2. Go to Insert → Area Chart

3. Pick style

4. Use Chart Design for layout, labels

5. Format with color fills and effects

📘 Module 6: Excel Charts 4

 📍 Use for:
Budget allocation over years

Product mix analysis

🥧 8. Pie and Doughnut Charts

✅ Explanation:
Pie: Shows parts of a whole using slices.

Doughnut: Like pie but with a hole in the center (can show multiple series).

🔹 Types:
Pie

3D Pie

Doughnut

🪜 Steps:
1. Select a single series of categorical data

2. Go to Insert → Pie or Doughnut

3. Choose style

4. Add data labels and legends via Chart Design

5. Format chart area and slices

📘 Module 6: Excel Charts 5

 🔁 To Change Chart Type:
1. Right-click chart → Change Chart Type

2. Select a different chart (e.g., switch Pie to Bar)

📍 Use for:
Market share

Budget distribution

📉 9. Scatter (XY) Chart

✅ Explanation:
Plots two numeric values (X and Y).

Ideal for scientific or statistical data.

🔹 Types:
Scatter only

Scatter with lines or markers

Bubble chart (advanced)

🪜 Steps:
1. Select data (X in one column, Y in another)

2. Go to Insert → Scatter Chart

3. Choose style. Format axis, gridlines, markers

4. Use Chart Design to enhance

📘 Module 6: Excel Charts 6

 📍 Use for:
Correlation between two variables (e.g., hours studied vs. score)

💧 10. Waterfall Charts

✅ Explanation:
Shows how a value increases or decreases across categories.

Helps explain change over time.

🪜 Steps:
1. Select data including start, changes, and end

2. Go to Insert → Waterfall Chart

3. Add data labels

4. Use Chart Design to color positive, negative, and total values

5. Use “Set as total” for subtotal columns

📘 Module 6: Excel Charts 7

 📍 Use for:
Profit/loss breakdown

Revenue changes

🔦 11. Sparklines
✅ Explanation:
Tiny charts inside a cell showing mini trends.

🔹 Types:
Line

Column

Win/Loss

🪜 Steps:
1. Select cell where sparkline should appear

2. Go to Insert → Sparklines

3. Choose type and select data range

4. Format using Sparkline Tools → color, marker, axis

📘 Module 6: Excel Charts 8

 📍 Use for:
Performance trends in tables

Monthly comparisons for individual rows

✅ Best Practices for Excel Charts

Tip Benefit

Always include labels & titles Improves clarity

Use appropriate chart type Don't force-fit a pie chart

Avoid 3D unless necessary Can mislead visual representation

Use light gridlines Helps readability

Choose clear contrasting colors Enhances understanding

Keep charts clean Avoid overcrowding with too many series

Use chart templates for consistency Save time and maintain branding

📌 Key Points to Remember

📊 Choose the right chart type for your message
✍️ Customize chart elements for better communication
🧩 Use Chart Design tab for layout, styles, and color schemes
🧠 Know when to use each chart (trend, part of whole, comparison, etc.)
🎯 Don’t over-style — simplicity wins

📘 Module 6: Excel Charts 9

 📘Charts
Module 7: Pivot Tables and Pivot

🔹 1. Pivot Tables
📌 What is a Pivot Table?
A Pivot Table is a powerful Excel tool used to summarize, analyze, explore, and present data.

It lets you:

Rearrange data dynamically

Group, filter, and sort data without formulas

Quickly create reports and summaries from large datasets

✅ No need for complex formulas like SUMIF, COUNTIF, AVERAGEIFS—a

Pivot Table can handle it all!

🎯 Why Use a Pivot Table?

Benefit Description

🔍 Quick Summary Create totals, averages, counts in seconds

📊 Interactive Analysis Rearrange fields without changing data

🧱 Organized View Display hierarchical or grouped data clearly

🔁 Dynamic Easily refresh data after updates

📉 Chart Integration Combine with Pivot Charts for visual insights

🧑‍💻 How to Create a Pivot Table

Step-by-Step:
Step Action

1️⃣ Select any cell inside your dataset

2️⃣ Use shortcut Ctrl + Shift + → + ↓ to select full data

3️⃣ Go to Insert → PivotTable (first icon on left)

4️⃣ In the dialog box:• Ensure full table range is selected• Choose “New Worksheet” or
“Existing Worksheet”

5️⃣ Click OK – the Pivot Table Fields pane will appear

6️⃣ Drag and drop fields to build your table

📘 Module 7: Pivot Tables and Pivot Charts 1

 🧩 Pivot Table Fields – Explained
The PivotTable Fields pane has 4 key areas:

Area Function Example

🧭 Filters Adds dropdown filter to the entire pivot table Year, Product Category

🧮 Values What you want to summarize (numbers) Sales, Profit, Quantity

📑 Rows Becomes row labels Region, City

📊 Columns Becomes column labels Month, Year

📌 You can drag the same field to different zones for different views (e.g.,
Sales in both Values and Filters)

📘 Module 7: Pivot Tables and Pivot Charts 2

 🛠️ PivotTable Analyze Tab – Explained
Tool Description

🔄 Refresh Update pivot after data changes

➕ Insert Slicer Add interactive filters

🧩 Field List Show/hide the Pivot Fields pane

🧾 Field Headers Toggle row/column headers

➕ Calculated Field Create custom formulas

📋 Actions → Move PivotTable Move table to another location

⬆ Show Details Double-click cell to see source data

Accessible only when the Pivot Table is selected.

🧩 Example
Dataset:

Pivot Table:

📘 Module 7: Pivot Tables and Pivot Charts 3

 📈 2. Pivot Charts
📌 What is a Pivot Chart?
A Pivot Chart is a visual representation of a Pivot Table.

It updates automatically when the Pivot Table changes.

✅ Why Use Pivot Charts?

Benefit Description

📊 Dynamic Updates with pivot table filters

🔎 Clear Visuals Converts summaries into charts

🔄 Interactive Linked to slicers & filters

🧠 Easy to Understand Great for reports & presentations

🧑‍💻 How to Create a Pivot Chart

Step Action

1️⃣ Create a Pivot Table first

2️⃣ Select the Pivot Table

3️⃣ Go to Insert → Charts → Recommended Chart or pick any chart

4️⃣ The chart will be created and linked to the Pivot Table

5️⃣ Use filters/slicers to explore data interactively

You can use column, bar, line, pie, and combo charts.

🧩 Example
Pivot Chart for above example:

📘 Module 7: Pivot Tables and Pivot Charts 4

 📝 Additional Notes
⚙️ Options in Pivot Table → Right-Click Menu:
Option Function

Sort Sort data (A-Z, Z-A, Ascending, Descending)

Value Field Settings Change from Sum to Average, Count, Max, etc.

Number Format Format as currency, percentage, decimal

Group Group data (e.g., by months or years)

Ungroup Remove grouping

Show Values As Show % of total, % difference, running total etc.

📌 Key Points / Important Reminders

✅ Use keys to quickly select full datasets
Ctrl + Shift + Arrow

📊 Always check that your table/range is fully selected before inserting

🔁 Use Refresh to update after editing source data
🧮 You can drag one field to both Rows and Values (e.g., count of product names)
📈 Pivot Charts are linked to Pivot Tables – changes reflect instantly
⚙️ Customize summary types using Value Field Settings
🎯 Use Slicers and Filters for interactive dashboards

📘 Module 7: Pivot Tables and Pivot Charts 5

 📘ExcelModule 8: Map Charts – Only for
2019 & Above
🗺️ 1. What is a Map Chart?
A Map Chart allows you to visually represent geographic data such as:

Country-level GDP

State-wise sales

Region-wise growth

📌 Internet connection is required to use map charts in Excel.

📥 2. How to Insert a Map Chart
Step Action

1️⃣ Select your dataset (must include a geographic column like "Country", "State", or
"Region")

2️⃣ Go to Insert → Maps → Filled Map

3️⃣ Excel will auto-detect geographic values and display a map

4️⃣ Use the Chart Elements button to add titles, legends, and labels

5️⃣ Use the Format Chart Area to customize appearance

🎨 3. Formatting Map Charts (via Format Chart Area)

You can open the Format Chart Area by right-clicking the chart → Format Chart Area .

Feature Description

🎨 Fill Change background: Solid , Gradient , Picture , or Pattern

✏️ Border Add or remove borders, change color and thickness

🔠 Title Edit Chart Title using Chart Elements +

📋 Legend Add/Remove chart legends

🏷 Data Labels Show actual values or percentages on the map

🌈 Color Gradient Controls intensity of values (e.g., GDP, population)

🌐 4. Important Limitations
Limitation Explanation

📌 State/Country only Excel supports States, Countries, and Regions – NOT districts, villages, or cities

🔁 Duplicate Names If two places have the same name, add a helper column with distinguishing numeric
values

🌍 Regional Settings Map data must match regional names (e.g., use “United States” not “USA”)

📶 Internet Required Excel uses Bing Maps API to generate map visuals, so internet is necessary

📘 Module 8: Map Charts – Only for Excel 2019 & Above 1

 🔧 5. Handling Duplicate or Conflicting Locations
If Excel fails to map places correctly (e.g., two entries for "Punjab"):

Insert a new helper column to the left of the main data with different numeric values or
grouping labels.

This helps Excel distinguish locations.

🧪 6. Example Data Structures

🌍 Country-wise Economy (in Millions USD):

→ Insert → Map → Filled Map

→ Format using chart tools (color, border, label, etc.)

📘 Module 8: Map Charts – Only for Excel 2019 & Above 2

 🇮🇳 State-wise Growth Example:

→ Insert → Map → Filled Map

→ Gradient will reflect growth levels.

📌 Key Points / Important Notes

📶 Internet is required to generate maps (uses Bing API).
🧾 Data must include State, Country, or Region columns.
❌ Districts, cities, and villages are not supported.
🧩 Use helper columns for duplicate names or to guide Excel.
🎨 Customize the chart using Format Chart Area.
📊 Map Charts are best for geographical comparisons and region-wise analysis.

📘 Module 8: Map Charts – Only for Excel 2019 & Above 3

 📘 Module 9: Using ChatGPT with Excel
🤖 What is ChatGPT?
ChatGPT is an AI assistant developed by OpenAI. It can understand natural language and help you
with:

💡 Solving Excel formulas

🛠 Writing Macros (VBA code)
📊 Explaining functions or Excel errors
📚 Learning Excel step-by-step
⚡ Automating repetitive tasks
🧭 How to Use ChatGPT for Excel
🔹 Step-by-step:
1. Go to: [Link]

2. Login or Sign up

3. Start typing your question or task

✨ Write a clear and detailed prompt explaining what you need.
🎯 Examples of Using ChatGPT in Excel
📌 Example 1: You Don’t Know the Formula
🔢 Dataset:
Name English Maths

A 85 90

💬 Prompt:
"I have marks in columns B and C. I want to calculate the average marks in
column D. What formula should I use?"

✅ ChatGPT will respond with:

=AVERAGE(B2, C2)

📘 Module 9: Using ChatGPT with Excel 1

 📌 Example 2: Conditional Formula
💬 Prompt:
"I want to assign grades in Excel: 'A' if marks > 90, 'B' if > 75, otherwise 'C'.
What's the formula?"

✅ ChatGPT will provide:

=IF(A2>90,"A",IF(A2>75,"B","C"))

📌 Example 3: Lookup Function Help

💬 Prompt:
"In Sheet1, I have Employee IDs in column A and Departments in column B. In
Sheet2, I want to look up the department by ID. Which formula should I use?"

✅ ChatGPT will provide:

=VLOOKUP(A2, Sheet1!A:B, 2, FALSE)

You can also ask:

"Difference between VLOOKUP and XLOOKUP ?"

"How do I use INDEX and MATCH together?"

🧩 Macros and ChatGPT

🔹 What is a Macro?
A macro is a recorded or coded set of steps that automates tasks in Excel.
You’ll learn full details in Module 10.

✅ How ChatGPT Helps with Macros

You can ask ChatGPT to:

Write custom macro code (VBA)

Explain what each line of code does

Help with macro errors or editing code

Add buttons to run macros

📘 Module 9: Using ChatGPT with Excel 2

 📌 Macro Examples with ChatGPT
🧪 Example 1: Highlight Scores Above 90
💬 Prompt:
"Write a macro to highlight all numbers above 90 in yellow in the selected
cells."

✅ ChatGPT responds with:

vba
CopyEdit
Sub HighlightTopScores()
Dim cell As Range
For Each cell In Selection
If IsNumeric([Link]) And [Link] > 90 Then
[Link] = RGB(255, 255, 0)
End If
Next cell
End Sub

📌 Use: Select your data, then run this macro to highlight cells.
🧪 Example 2: Save Sheet1 as a PDF
🎯 Objective:
Automatically save Sheet1 as a PDF in the same folder as your Excel file using a macro.

✅ Step-by-Step: Using VBA to Create This Macro

🔹 Step 1: Open the Developer Tab
If not visible:

1. Right-click on the ribbon → Click Customise the Ribbon

2. On the right pane, check "Developer" → Click OK

🔹 Step 2: Open the VBA Editor

1. Click on the Developer tab

2. Click Visual Basic

(Or press Alt + F11 )

🔹 Step 3: Insert a New Module

1. In the VBA window, go to Insert → Module

2. A blank code window will appear

📘 Module 9: Using ChatGPT with Excel 3

 🔹 Step 4: Paste the VBA Code
💬 Prompt:
"Write a macro to save Sheet1 as a PDF in the same folder as the Excel file."

✅ ChatGPT gives:
vba
CopyEdit
Sub SaveSheet1AsPDF()
Dim ws As Worksheet
Set ws = [Link]("Sheet1")

Dim filePath As String

filePath = [Link] & "\" & [Link] & ".pdf"

[Link] Type:=xlTypePDF, Filename:=filePath

End Sub

📌 Just run the macro—Sheet1 will be saved as a PDF in your workbook folder.
🔹 Step 5: Run the Macro
1. Close the VBA Editor ( Alt + Q )

2. Go back to Excel

3. On the Developer tab, click Macros

4. Select SaveSheet1AsPDF → Click Run

✅ Sheet 1 will be saved as a PDF in the same location as your Excel file.
💾 Don't Forget: Save Your File Properly!
Go to File → Save As

Choose Excel Macro-Enabled Workbook

Save with the extension: .xlsm

💡 Tips:
Make sure your workbook is saved first (so [Link] works correctly)

Sheet 1 should contain printable data

You can customise the filename or path inside the code if needed

💬 Prompt:
"Write a macro to save Sheet1 as a PDF in the same folder as the Excel file."

📘 Module 9: Using ChatGPT with Excel 4

 💬 Best Practices for Asking ChatGPT
Tip Why it Helps

Be specific Helps ChatGPT give accurate code

Mention column/row locations E.g., “Data is in columns A to C”

Say what Excel version you use Some features differ in older versions

Describe the goal Explain what result you want

⚡ Quick Tips
✅ Use ChatGPT to build custom formulas
🔁 Ask for formula explanations if you're confused
🧠 Use it as a personal Excel tutor
🧩 Take help with writing and understanding VBA macros
💾 Save macro-enabled files as (Excel Macro-Enabled Workbook)
.xlsm

📘 Module 9: Using ChatGPT with Excel 5

 📘 Module 10: Macros in Excel
🔹 What are Macros?
A Macro is a recorded sequence of actions that can be automated and reused.
It allows you to perform repetitive tasks (like formatting, calculations, and data cleaning) with a
single click or shortcut key.
✅ Macros save time and reduce errors in Excel.
⌨ Shortcut to Apply a Macro
Once recorded, macros can be:

Run using a shortcut key (e.g., Ctrl + Shift + M )

Clicked through a button on the sheet or from the Developer tab

🧪 Sample Data Set (Before Recording a Macro)

🧪 Sample Data Set (After Applying a Macro)

🧰 Turning on the Developer Tab

1. Right-click anywhere on the Ribbon → Click Customise the Ribbon

2. On the right pane, tick the checkbox for Developer

3. Click OK

📘 Module 10: Macros in Excel 1

 You’ll now see the Developer tab in the Ribbon.

🛠 Developer Tab Overview

Button Use

Record Macro Start recording a new macro

Macros View or run existing macros

Use Relative References Record macros using relative cell positioning

Insert Add buttons or form controls

Visual Basic Open the VBA Editor

Macro Security Set macro execution settings

🎥 Recording a Macro
1. Go to Developer tab → Record Macro

2. Fill in the details:

Macro Name: Enter a short, meaningful name without spaces. E.g., FormatGrades

Shortcut Key: Create your shortcut, like Ctrl + Shift + F , to run the macro quickly

Store Macro in: Select: This Workbook (for the current file) or Personal Macro Workbook
(for all Excel files)

Description: Write a short note, E.g., applies formatting to student grades

3. Click OK to start recording

📘 Module 10: Macros in Excel 2

 💡 While Recording, You Can:
Apply Bold, Colour, Font, Borders

Use Conditional Formatting

Use Ctrl + Shift + Arrow to select full data ranges

Delete unwanted rows/columns

Sort and filter data

Set column widths

Align text

🔴 After completing the steps, click Stop Recording on the Developer tab.
📄 Test Data Set (Apply Your Macro)

▶ How to Apply the Macro

Method 1: Using Shortcut Key
Click any cell in your target data set

Press your defined shortcut (e.g., Ctrl + Shift + F )

📘 Module 10: Macros in Excel 3

 Method 2: Run from the Developer Tab
Click Macros

Select your macro → Click Run

Method 3: Add a Button

1. Go to Developer → Insert → Button (Form Control)

2. Draw a button on the sheet

3. Assign your macro

4. Rename the button (e.g., "Format Grades")

💡 Buttons are helpful for non-technical users who prefer clicking over shortcuts.
💾 Saving a Macro-Enabled Workbook
To save your workbook with macros, choose:

File → Save As → Excel Macro-Enabled Workbook (*.xlsm)

Format Extension Can Store Macros

Regular Excel .xlsx ❌ No

Macro-Enabled .xlsm ✅ Yes
🔍 What is VBA?
VBA (Visual Basic for Applications) is the programming language behind macros.

It allows you to write, edit, or customise macros manually.

How to Check Your Macro Code:

1. Go to Developer tab → Visual Basic

2. In the VBA Editor, look under Modules

3. You'll see the code auto-generated from your recording

4. You can edit it or write advanced logic here

📘 Module 10: Macros in Excel 4

 ⚡ Quick Tips for Macro Recording
Use Ctrl + Shift + ↓ to select entire columns

Use Ctrl + Shift + → to select all columns

Keep macro steps short and repeatable

Use Relative References if applying a macro in different positions

Avoid using hardcoded cell addresses when not needed

Always test macros on sample data first

📘 Module 10: Macros in Excel 5

 📘External
Module 11: Importing Data from
Sources
🌐 1. Importing Data from a Website into Excel (Excel 2019 / 365)
You can directly fetch live web data into Excel using built-in features like Power Query.

🔹 Example Source:
📎 Use this as an example:
“List of countries by population (United Nations) - Wikipedia”
➡️ [Link]
✅ Steps to Import Web Data:
1. Copy the URL of the webpage with the data.

2. Open Excel.

3. Go to the Data tab → click Get Data.

4. Select:
➤ From Other Sources → From Web

5. Paste the URL → Click OK.

6. The Navigator Dialogue Box appears.

Browse and select the table or data you want.

7. Choose:

Load → To bring data directly into Excel

Transform Data → Opens Power Query Editor

📘 Module 11: Importing Data from External Sources 1

 🔧 In Power Query Editor:
You can clean and customise your data before importing.

Some common actions:

Action Use

Remove Columns Delete unnecessary fields

Remove Rows Delete headers, blank, or duplicate rows

Sort Ascending or descending

Filter Based on specific values

Rename Columns For clarity and ease of use

Change Data Type Text, Number, Date, etc.

Split Column By delimiter or position

Replace Values Clean up or modify data

📘 Module 11: Importing Data from External Sources 2

 After cleanup:

Click Close & Load (Top-left)

Data appears in the Excel sheet as a connected table

🔁 2. Automatically Update Data When Webpage Changes

If the data on the webpage is updated and you want your Excel file to reflect those updates:

🔄 Steps to Refresh Web Data:

1. Click any cell in your imported table.

2. Go to the Data tab.

3. Click Refresh All

➤ This will re-fetch the latest data from the source.

🛠 Optional: Auto-Refresh Settings

1. After importing your data (e.g., using Get Data → From Web), go to the Data tab.

2. Click on Queries & Connections (if the right-side pane isn’t already open).

3. In the Queries & Connections pane, right-click on your query name (like Table 1 or Query1).

4. Select Properties.

5. In the Query Properties window:

✅ Tick Refresh every X minutes (e.g., 60)

✅ Tick Refresh data when opening the file
6. Click OK to apply changes.

💡 Tip:
Make sure the source website’s structure doesn't change—if it does, the query may break.

Power Query is powerful for cleaning and reshaping imported data.

📘 Module 11: Importing Data from External Sources 3

 📘 Module 12: SQL Introduction
🔹 Introduction to SQL
SQL (Structured Query Language) is used to manage and manipulate relational databases.
It helps users store, retrieve, and analyze data using simple commands.

❓ Why Not Excel? Why SQL?

Feature Excel SQL

Data Volume Limited rows (~1M max) Handles millions of records

Multi-user Difficult Designed for multiple users

Security Basic password protection Robust access control

Automation Manual Highly scriptable and programmable

Use Excel for small, personal datasets.

Use SQL for large, structured, scalable databases.

🗂 What is a Relational Database?

A Relational Database stores data in tables (rows & columns).

Tables are related using keys (Primary Key, Foreign Key).

Example:

Students (StudentID, Name, Age)

Marks (StudentID, Subject, Score)

StudentID links both tables.

🎯 What You'll Learn Ahead

✅ SQL & DB Basics
Writing SQL queries

Creating/modifying database tables

📘 Module 12: SQL Introduction 1

 🛠 Fundamental SQL Commands
Command Purpose
CREATE Create tables/databases
SELECT Retrieve data
INSERT Add new data
ALTER Modify table structure
UPDATE Modify existing records

🔎 Data Filtering & Sorting

Keyword Use
WHERE Filter rows
ORDER BY Sort results

AND , OR Combine multiple conditions

NOT , IN , BETWEEN , LIKE Advanced filters

🧮 Aggregates & Grouping

Function Use
SUM() Total values
COUNT() Number of rows
MIN() Minimum value
MAX() Maximum value
GROUP BY Group data
HAVING Filter groups

🔗 JOINS – Combine Data from Tables

Join Type Description
INNER JOIN Common data in both tables
LEFT JOIN All left + matching right data
RIGHT JOIN All right + matching left data
FULL OUTER JOIN All data, match or not
CROSS JOIN Cartesian product

UNION / EXCEPT Combine / subtract datasets

🧠 Advanced Topics
Subqueries – Nested SELECT statements

Views – Virtual tables

Indexes – Faster data access

📘 Module 12: SQL Introduction 2

 🔤 String Functions
Function Use

UPPER() / LOWER() Case conversion

TRIM() , LTRIM() , RTRIM() Remove spaces

SUBSTRING() Extract text
REPLACE() Replace characters
CONCAT() Join strings
STRING_AGG() Combine string rows

🔢 Mathematical Functions
Function Use
CEIL() Round up
FLOOR() Round down
ROUND() Round to decimal
RANDOM() Generate random
POWER() Exponents

📅 Date & Time Functions

Function Use

CURRENT_DATE , CURRENT_TIME Get now

DATEDIFF() Date difference

MONTH() , YEAR() Extract parts of date

🔄 Data Type Conversion

To Type Function Examples

String CAST(... AS VARCHAR)

Date CAST(... AS DATE)

Time CAST(... AS TIME)

🔍 Pattern Matching
Technique Use

LIKE Match patterns using % or _

REGEXP Match complex regex patterns

📘 Module 12: SQL Introduction 3

 📌 Summary
SQL is essential for managing structured data in relational databases.

It helps retrieve, filter, organize, and analyze large datasets efficiently.

You’ve seen core commands ( SELECT , INSERT , UPDATE , etc.), joins, filters, aggregates, functions,
and more.

As we move forward, you’ll learn to apply these concepts hands-on with real datasets.

💡 Pro Tip:
Just like Excel formulas, SQL is best learned by doing. Practice daily, build queries, and try out
challenges to improve.

📘 Module 12: SQL Introduction 4

 📘Started
Module 13: Installation and Getting

🖥 Installing SQL Server & SSMS (SQL Server Management Studio)

This module walks you through setting up the tools needed to run SQL queries on your system.

🔽 Step 1: Download SQL Server (Express Edition)

1. Go to: [Link]

2. Choose Express version (free).

3. Save the installer to your Downloads folder or any preferred location.

⚙ Step 2: Install SQL Server

1. Run the downloaded setup file.

2. Choose Basic Installation.

3. Click Accept to agree to license terms.

4. Choose a location to install the files.

5. Click Install to begin.

6. Wait for the process to complete.

7. On success, you’ll see:

✅ "Installation has completed successfully" message.

📘 Module 13: Installation and Getting Started 1

📘 Module 13: Installation and Getting Started 2
📘 Module 13: Installation and Getting Started 3
 🧩 Step 3: Install SSMS (SQL Server Management Studio)
1. After SQL Server installs, click Install SSMS or visit:

[Link]

2. Download the SSMS setup file.

3. Run the file and select an installation location.

4. Click Install and wait for it to finish.

📘 Module 13: Installation and Getting Started 4

📘 Module 13: Installation and Getting Started 5
 🚀 Step 4: Launch SSMS and Connect
1. Open Start Menu → Search "SSMS".

2. Launch SQL Server Management Studio.

3. In the Connect to Server window:

Server type: Database Engine

Authentication: Windows Authentication

4. Click Connect.

You’re now ready to write and run SQL queries! ✅

📘 Module 13: Installation and Getting Started 6

 💡 Tip:
Pin SSMS to your taskbar for quick access.

📘 Module 13: Installation and Getting Started 7

 📘 Module 14: Database Basics
🔹 What is SQL?
SQL stands for Structured Query Language.
It is a standard programming language for managing and interacting with relational databases.

📌 Key Capabilities:
Create and modify databases and tables

Insert, update, and delete records

Retrieve and filter data

Set user permissions and ensure data security

Aggregate, sort, and organise data for analysis

💡 SQL Trivia & Facts

Trivia Details

Pronunciation Often pronounced as “Sequel”

Used across all major databases (MySQL, SQL Server, Oracle,

Universal
PostgreSQL)

SQL engines may be open-source (e.g., MySQL) or paid (e.g.,

Paid + Free
Oracle)

Security Supports role-based access, user permissions, and data encryption

Compatibility Works with popular tools like Python, Excel, Power BI, and Java

Declarative You tell what to do, not how (unlike most programming languages)

👥 Who Uses SQL?

SQL is used by a wide range of professionals and industries:

User Type Role

📊 Data Analyst Uses SQL to extract and analyze data for insights

💼 Business Analyst Generates reports and dashboards using SQL

👩‍💻 Software Developer Integrates SQL into apps for storing and accessing
data

🛠️ Database Administrator (DBA) Manages database structure, users, backups, and

performance

📈 Data Scientist Uses SQL to prepare and filter data before applying
ML models

🧪 Tester / QA Engineer Verifies data correctness and performs backend

testing

📚 Students / Learners Learn SQL to build a strong foundation in data

handling

📘 Module 14: Database Basics 1

 🗄 What is a Database?
A database is an organised collection of data that can be easily accessed, managed, and updated.
Databases help store information digitally for quick retrieval and processing.
🔹 Example:
A school's database might store:

Students’ names and IDs

Subjects and marks

Attendance records

🔗 What is a Relational Database?

A Relational Database stores data in the form of tables, where data is related to other data using
keys.
Each table represents a different entity, and relationships are defined using Primary and Foreign
Keys.

🔹 Example:
Students Table

Marks Table

Linked by StudentID

✅ Data is:
Organized

Easy to update

Non-redundant

📋 Tables: Structure & Terminology

Term Description Example

Table Grid to store related data Students , Employees , Orders

Attribute (Column) Field name Name , Gender , Age

Record (Row) One entry of data ('Piya','Female', 25)

Cell Intersection of row and column 25 in column Age for Piya

📌 Each table has a unique name

📌 Each column has a data type (e.g., INT, VARCHAR, DATE)

📘 Module 14: Database Basics 2

 🧠 What is a DBMS?
DBMS = Database Management System

A DBMS is software used to store, retrieve, and manage databases efficiently.

Feature Description

Storage Manages how and where data is saved

Querying Allows users to ask questions using SQL

Security Controls access (users, roles, privileges)

Backup/Recovery Ensures data is safe in case of failures

Multi-user Access Many users can work at the same time

🔍 Examples of Popular DBMS

DBMS Type Notes

MySQL Open-source Popular for web apps

SQL Server Paid Microsoft-developed

PostgreSQL Open-source Advanced & feature-rich

Oracle Paid Enterprise-grade

SQLite Lightweight Used in mobile apps

📑 Types of SQL Commands

SQL commands are grouped into 5 main categories, based on their purpose. Each category serves
different roles in the database system.

📘 Module 14: Database Basics 3

 🔧 1. DDL – Data Definition Language
DDL commands are used to define and modify the structure of database objects like tables,
schemas, indexes, etc.

🔹 Common DDL Commands:

Command Description
CREATE Create a new table or database
ALTER Modify table structure
DROP Delete a table or database
TRUNCATE Remove all records (structure remains)

👷 Used by:
Database Architects

Designers

DBAs (Database Admins) during initial design and schema changes

🛠 2. DML – Data Manipulation Language

DML commands are used to manipulate data stored in tables (insert, update, delete records).

🔹 Common DML Commands:

Command Description

INSERT Add new data

UPDATE Modify existing data

DELETE Remove data

👤 Used by:
Data Analysts

Application Developers

Data Entry Operators

🔎 3. DQL – Data Query Language

DQL is used to query and retrieve data from databases.

🔹 Common DQL Command:

Command Description
SELECT Fetch data from tables

📘 Module 14: Database Basics 4

 👤 Used by:
Data Analysts

BI Professionals

Students

Data Scientists

🔐 4. DCL – Data Control Language

DCL is used to control access and permissions in a database.

🔹 Common DCL Commands:

Command Description
GRANT Give access/privileges to users
REVOKE Remove access/privileges

👤 Used by:
Database Administrators (DBAs)

Security Engineers

🔄 5. TCL – Transaction Control Language

TCL commands are used to manage changes made by DML statements. They help maintain data
integrity and allow rollback in case of errors.

🔹 Common TCL Commands:

Command Description
COMMIT Save changes
ROLLBACK Undo changes
SAVEPOINT Set a point to rollback to

BEGIN / END Start/close a transaction block

👤 Used by:
Application Developers

Backend Engineers

DBAs

📘 Module 14: Database Basics 5

 🧭 Summary Table
Type Full Form Purpose Users

DB Designers, Architects,
DDL Data Definition Language Define/modify schema
DBAs

Data Manipulation
DML Add/change/delete data Developers, Analysts
Language

DQL Data Query Language Fetch/read data Analysts, Students, Scientists

DCL Data Control Language Control access/permissions DBAs, Security Teams

Transaction Control Manage transaction

TCL Developers, DBAs
Language consistency

⚡ Quick Tips – SQL & Database Basics

✅ Start with – It’s the most common and useful SQL command.
SELECT

🔍 Use to filter specific rows from a table.

WHERE

🔗 Learn early – it's essential for working with multiple tables.

JOIN

🧱 Always define Primary Keys to ensure data uniqueness.

🗃 Use carefully – design your schema before inserting data.
CREATE TABLE

✏ Use and
UPDATE with a
DELETE clause to avoid accidental data loss.
WHERE

🔐 Use and GRANT to control who can access or modify data.

REVOKE

🔄 Use if something goes wrong in a transaction – TCL saves your data.

ROLLBACK

📂 Keep your DBMS backed up regularly to prevent data loss.

🧠 Practice DDL, DML, and DQL together for complete understanding.

📘 Module 14: Database Basics 6

 📘Statements
Module 15: Fundamental SQL

🧾 Reference Tables Used in This Module

We will use the following sample tables to demonstrate all SQL statements in this module:

✅ Professors

✅ Department

✅ Register

📸 Image Reference:

🔹 How to Create a Database

✅ Definition:
Creating a database means setting up a named space to store related tables and data objects.

✅ Syntax:
CREATE DATABASE database_name;

✅ Method 1: Using SSMS

1. Open SQL Server Management Studio

2. In Object Explorer, right-click on Databases → New Database

3. Name your database (e.g., CollegeDB ) → Click OK

📘 Module 15: Fundamental SQL Statements 1

 ✅ Method 2: Using Query
CREATE DATABASE database_name;

📌 Example:
CREATE DATABASE DBNAME1;

🧭 How to Select a Database

✅ Definition:
You must select the active database you want to work with.

✅ Method 1: Dropdown in SSMS

Use the dropdown menu on the toolbar to select your database (e.g., CollegeDB )

✅ Method 2: Using Query

USE database_name;

📌 Example:
USE CollegeDB;

📘 Module 15: Fundamental SQL Statements 2

 🔐 SQL Constraints
✅ Definition:
Constraints are rules applied to table columns to enforce data integrity.

Constraint Purpose
PRIMARY KEY Uniquely identifies each row in a table
FOREIGN KEY Links a column to another table’s primary key
NOT NULL Prevents null (empty) values
UNIQUE Ensures all values are different
CHECK Ensures column value meets a condition
DEFAULT Sets a default value

🔢 SQL Data Types

Data Type Description
INT Whole numbers
NVARCHAR(n) Variable-length text (Unicode)
DATE Stores date values

🔨 1. CREATE Statement

✅ Definition:
Used to create new tables or databases in SQL.

✅ Syntax:
CREATE TABLE table_name (
column_name datatype constraints,
... );

📌 Examples:
1.

CREATE TABLE Department (

DeptID NVARCHAR(20) PRIMARY KEY,
DeptName NVARCHAR(20) NOT NULL );

2.
CREATE TABLE Professors (
PID NVARCHAR(20) PRIMARY KEY,
Fullname NVARCHAR(50) NOT NULL,
Age INT,
DeptID NVARCHAR(20),
FOREIGN KEY (DeptID) REFERENCES Department(DeptID) );

📘 Module 15: Fundamental SQL Statements 3

 ✍ 2. INSERT Statement

✅ Definition:
Used to insert data into a table.

✅ Syntax (Recommended):
INSERT INTO table_name (col1, col2, ...)
VALUES (val1, val2, ...);

✅ Syntax (Less preferred):

INSERT INTO table_name
VALUES (val1, val2, ...);

✅ Why use column names?

✔ Safer, clearer, prevents column mismatch errors.
📌 Example (from your image):
INSERT INTO Department VALUES
('D1', 'Computer Science'),
('D2', 'English'),
('D3', 'Statistics'),
('D4', 'Geography');

📘 Module 15: Fundamental SQL Statements 4

 INSERT INTO Professors VALUES
('P1', 'Tom Jones', 40, 'D2'),
('P2', 'Erica Sharma', 38, 'D1'),
('P3', 'Cole David', 42, 'D1'),
('P4', 'Yash Sharma', 35, 'D3'),
('P5', 'Tarini Mittal', 33, 'D4'),
('P6', 'Riya Kapoor', 31, 'D3');

🔗 3. Primary Key & Foreign Key

✅ Primary Key:
A column that uniquely identifies each row in a table.

✅ Foreign Key:
A column that links to another table’s primary key.

✅ Syntax:
-- Inline with CREATE TABLE
PID NVARCHAR(20) PRIMARY KEY,

-- Foreign Key
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)

🧩 3 Relationships (from your image):

1. Each professor belongs to one department (via DeptID).

2. Each department may have multiple professors.

3. Register shows combinations of multiple professors in departments (many-to-many).

📘 Module 15: Fundamental SQL Statements 5

 📥 4. Import Data from File (CSV)
✅ Steps:
1. Right-click database → Tasks → Import Flat File

📘 Module 15: Fundamental SQL Statements 6

 2. Select the CSV file → Click Next

📘 Module 15: Fundamental SQL Statements 7

 3. Define column names and types

📘 Module 15: Fundamental SQL Statements 8

📘 Module 15: Fundamental SQL Statements 9
 4. Preview and finish

✔ The table is created and data is imported automatically.

📘 Module 15: Fundamental SQL Statements 10

 📄 5. SELECT Statement

✅ Definition:
Used to retrieve data from a table.

✅ Syntax:
SELECT column1, column2 FROM table_name;
SELECT * FROM table_name; -- All columns

📌 Example:
1. SELECT * FROM Professors;

2. SELECT DISTINCT Fullname FROM Professors;

📘 Module 15: Fundamental SQL Statements 11

 📊 6. SELECT DISTINCT

✅ Definition:
Returns only unique values, removing duplicates.

✅ Syntax:
SELECT DISTINCT column FROM table_name;

📌 Example:
1. SELECT DISTINCT Fullname, DeptID
FROM Professors
WHERE Age < 30;

🔍 7. WHERE Clause

✅ Definition:
Filters rows based on a given condition.

✅ Syntax:
SELECT column1 FROM table_name WHERE condition;

📌 Example:
1. SELECT DISTINCT Fullname
FROM Professors
WHERE Age < 30;

📘 Module 15: Fundamental SQL Statements 12

 ⚖ 8. Logical Operators
✅ Definition:
Used in WHERE clause to combine conditions.

Operator Meaning
AND All conditions true
OR At least one true
NOT Negates a condition

✅ Syntax:
SELECT * FROM table_name WHERE condition1 LogicalOperator condition2;

📌 Examples:
1. SELECT * FROM Professors WHERE Age = 30 AND DeptID = 'D2';

📘 Module 15: Fundamental SQL Statements 13

 2. SELECT * FROM Professors WHERE Age = 30 OR DeptID = 'D2';

3. SELECT * FROM Professors WHERE NOT DeptID = 'D2';

✏ 9. UPDATE Statement

✅ Definition:
Used to modify existing records in a table.

✅ Syntax:
UPDATE table_name
SET column = value
WHERE condition;

📘 Module 15: Fundamental SQL Statements 14

 📌 Example:
1. UPDATE Professors
SET Email = 'demo@[Link]';

📘 Module 15: Fundamental SQL Statements 15

 🗑 10. DELETE Statement

✅ Definition:
Used to remove records from a table.

✅ Syntax:
DELETE FROM table_name WHERE condition;

📌 Examples:
1. DELETE FROM Professors WHERE DeptID = 'D3';

2. DELETE FROM Professors WHERE Fullname = 'Tarini Mittal' AND DeptID = 'D4';

📘 Module 15: Fundamental SQL Statements 16

 3. DELETE FROM Professors; -- Deletes all rows

🔁 11. ALTER Statement

✅ Definition:
Used to modify the structure of an existing table.

✅ Syntax:
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP COLUMN column_name;

📌 Examples:
1. ALTER TABLE Professors ADD Address NVARCHAR(30);

📘 Module 15: Fundamental SQL Statements 17

 2. ALTER TABLE Professors DROP COLUMN Address;

🔄 Difference: DROP vs DELETE vs TRUNCATE

Command What It Does Structure? Reversible?

DROP Deletes entire table ✅ Yes ❌ No
DELETE Deletes rows (with condition) ❌ No ✅ Yes
TRUNCATE Deletes all rows (fast) ❌ No ❌ No

✅ Final Notes for Students

Use CREATE , INSERT , SELECT , WHERE , UPDATE , DELETE to manage data.

Learn PRIMARY and FOREIGN KEY to link tables properly.

Always use column names while inserting data.

WHERE filters rows; DISTINCT removes duplicates.

Use ALTER to adjust tables without deleting them.

📘 Module 15: Fundamental SQL Statements 18

 📘 Module 16: Restore and Backup
🧰 1. What is Backup and Restore in SQL Server?
💾 Backup:
Creates a copy of your database, which can be used later in case of data loss, corruption, or
transfer to another server.
♻️ Restore:
Takes the backup file and brings the database back to its previous state.

🛠️ 2. How to Take a Backup in SQL Server (Using SSMS)

🖱️ Step-by-step:
1. Right-click on the database you want to back up

2. Click Tasks > Back Up...

3. Select backup type: Full, Differential, or Transaction Log

4. Choose the destination path ( .bak file)

5. Click OK

✅ Query to Take Backup:

BACKUP DATABASE YourDatabaseName
TO DISK = 'C:\Backup\[Link]'
WITH FORMAT;

📘 Module 16: Restore and Backup 1

 ♻️ 3. How to Restore a Database in SQL Server (Using SSMS)
🖱️ Step-by-step:
1. Right-click on Databases

2. Select Restore Database...

3. Choose Device → Browse for .bak file

4. Select the file, click OK

5. Optionally change the destination database name

6. Click OK

📘 Module 16: Restore and Backup 2

 ✅ Query to Restore:
RESTORE DATABASE YourDatabaseName
FROM DISK = 'C:\Backup\[Link]'
WITH REPLACE;

🌐 4. How to Get a Sample Database – AdventureWorks

📍 Step-by-step to download:
1. Search: AdventureWorks sample database

2. Click on the first Microsoft Docs link

3. Download the [Link] file

4. Follow Microsoft documentation to restore it in SSMS

🔗 Microsoft Official AdventureWorks Page

📘 Module 16: Restore and Backup 3

 📄 5. Creating Tables Using CSV Files (Data Import)
📥 Steps using SQL Server Import Wizard:
1. Right-click on the Database → Tasks → Import Data

2. The SQL Server Import and Export Wizard will open

3. Choose Data Source (e.g., Flat File for CSV)

4. Browse and select your CSV file

5. Choose Destination (SQL Server DB)

6. Map columns (edit if required)

7. Click Next and Finish

🎯 Your table will be created and filled with the CSV data!

📘 Module 16: Restore and Backup 4

📘 Module 16: Restore and Backup 5
 📝 Key Points to Remember
✅ Backup is a must-have safety net.
✅ file is the standard SQL Server backup format.
.bak

✅ Always check the correct destination and file paths.

✅ Use in restore only when you're sure to overwrite.
WITH REPLACE

✅ For large databases, use compression in backup options.

✅ Keep backup files in a secure and non-local location if possible.
✅ Use the Import Wizard for quickly creating tables from external sources like Excel/CSV.

💡 Bonus Tips
🔐 For sensitive data, consider encrypted backups.
🕒 Schedule regular backups via SQL Server Agent Jobs.
🗂️ Use or
SELECT INTO for importing data via T-SQL if not using the wizard.
BULK INSERT

📘 Module 16: Restore and Backup 6

 📘Filtering
Module 17: Selection Commands –

This module focuses on using SQL filtering techniques to retrieve specific records using IN ,
BETWEEN , and LIKE with real examples from the Sales table.

🧾 Reference Table Used in This Module

🔹 1. IN Operator
✅ Definition:
The IN operator allows you to filter rows where a column matches any value in a specified list.

✅ Syntax:
SELECT column1, column2
FROM table_name
WHERE column_name IN (value1, value2, ...) ;

📘 Module 17: Selection Commands – Filtering 1

 📌 Example:
SELECT DISTINCT Manager, Salesman
FROM Sales
WHERE Region IN ('East', 'Central');

✔ Returns unique combinations of Manager and SalesMan where the Region is either East or
Central.

🔹 2. BETWEEN Operator
✅ Definition:
BETWEEN filters rows where a value is within a range (inclusive). You can use it on numbers, dates,
or text.

✅ Syntax:
SELECT * FROM table_name
WHERE column_name BETWEEN value1 AND value2;

📌 Examples:
a) Filter by Units:
SELECT * FROM Sales
WHERE Units BETWEEN 50 AND 70;

📘 Module 17: Selection Commands – Filtering 2

 ✔ Gets rows with unit values between 50 and 70.
b) Not Between:
SELECT * FROM Sales
WHERE Units NOT BETWEEN 50 AND 100;

✔ Gets rows where units are either < 50 or > 100.

c) Filter by Order Date:
SELECT * FROM Sales
WHERE OrderDate BETWEEN '2018-03-01' AND '2018-04-30';

📘 Module 17: Selection Commands – Filtering 3

 ✔ Returns sales made between March and April 2018.
🔹 3. LIKE Operator (Pattern Matching)
✅ Definition:
LIKE is used to search for a pattern in a column (usually VARCHAR or TEXT ).

✅ Syntax:
SELECT * FROM table_name
WHERE column_name LIKE 'pattern';

📌 Examples:
a) Starts with 'M':
SELECT DISTINCT * FROM Sales
WHERE SalesMan LIKE 'M%';

✔ Matches names like Michael, Martha, etc.

📘 Module 17: Selection Commands – Filtering 4

 b) Exactly 7-character names:
SELECT DISTINCT * FROM Sales
WHERE SalesMan LIKE '_______';

✔ Matches names that are exactly 7 characters long.

c) Third character is 'a':

SELECT DISTINCT * FROM Sales

WHERE SalesMan LIKE '__a%';

✔ Matches names where 3rd character is 'a', like Diana.

📘 Module 17: Selection Commands – Filtering 5

 🎯 Wildcards in LIKE
Wildcard Meaning Example Pattern Matches Examples
% Any number of characters 'M%' Martha, Michael
_ Exactly one character '__a%' Diana, Alexander
[a-c] Any single char in the range '[A-C]%' Alice, Bob, Carl
[^a-c] Any char not in range '[^A-C]%' Names not starting with A, B, or C

🔔 Note: The last two patterns ([ ]) work in SQL Server but not in all database
engines.

✅ Summary – Key Learnings

Operator Use Example
IN Match values from a list WHERE Region IN ('East', 'West')

BETWEEN Match values within a range WHERE Units BETWEEN 50 AND 100

LIKE Match text patterns with wildcards WHERE SalesMan LIKE 'M%'

📘 Module 17: Selection Commands – Filtering 6

 📘Ordering
Module 18: Selection Commands –

🗨 1. Commenting in SQL
✅ Purpose:
Comments are used to add notes or explanations inside your SQL queries. These are ignored by
the SQL engine.

✅ Types of Comments:
Type Syntax Example Use Case

Single Line -- This is a comment Quick notes or explanations

Multi-line /* This is a multi-line comment */ Document blocks or disable code

📌 Visual Tip: Comments appear green in SSMS (SQL Server Management Studio).

📘 Module 18: Selection Commands – Ordering 1

 🧾 Reference Table Used in This Module

🔽 2. ORDER BY Clause
✅ Definition:
ORDER BY is used to sort result rows by one or more columns.

ASC = Ascending (default)

DESC = Descending

✅ Syntax:
SELECT column1, column2
FROM table_name
ORDER BY column1 ASC, column2 DESC;

📌 Examples (Using Sales Table):

a) Sort by Order Date (Oldest to Newest):
SELECT * FROM Sales
ORDER BY OrderDate ASC;

📘 Module 18: Selection Commands – Ordering 2

 b) Sort by Date Ascending, Units Descending:
SELECT * FROM Sales
ORDER BY OrderDate ASC, Units DESC;

📘 Module 18: Selection Commands – Ordering 3

 c) Filter and Sort by Units:
SELECT * FROM Sales
WHERE Units > 50
ORDER BY Units DESC;

🔝 3. TOP Keyword (SQL Server Equivalent of LIMIT)

✅ Definition:
TOP is used to limit the number of rows returned.

TOP N → Return N rows

TOP N PERCENT → Return N% of rows

✅ Syntax:
SELECT TOP N * FROM table_name;
SELECT TOP N PERCENT * FROM table_name;

📌 Examples:
a) Top 3 Sales Records:
SELECT TOP 3 *
FROM Sales;

📘 Module 18: Selection Commands – Ordering 4

 b) Top 5 Percent Records:
SELECT TOP 5 PERCENT *
FROM Sales;

📘 Module 18: Selection Commands – Ordering 5

 c) Top 5 Sales where Units > 60:
SELECT TOP 5 * FROM Sales
WHERE Units > 60;

📝 Summary Table
Command Purpose Example
-- Single-line comment -- This filters sales

/* */ Multi-line comment /* Ignore this block */

ORDER BY Sort results ORDER BY Units DESC

ASC Ascending order ORDER BY OrderDate ASC

DESC Descending order ORDER BY Sale_amt DESC

TOP Return top N records SELECT TOP 3 * FROM Sales

TOP N PERCENT Return top % of rows SELECT TOP 10 PERCENT * FROM Sales

📘 Module 18: Selection Commands – Ordering 6

 📘 Module 19: Aliasing in SQL
🔹 What is Aliasing?
Aliasing in SQL means giving a temporary alternate name to a column or table using the AS

keyword.

Makes column names more readable or meaningful

Aliases are used only for the duration of the query

They do not change the actual table or database

🔧 Keyword: AS
Used to define an alias (a temporary name) for a column or table

Aliases are especially useful in reports and output formatting

✅ Syntax:
SELECT column_name AS alias_name
FROM table_name;

(Optional) You can skip AS and just write the alias:

SELECT column_name alias_name
FROM table_name;

🧾 Reference Table Used in This Module

📘 Module 19: Aliasing in SQL 1

 🧪 Example Using the Sales Table
SELECT
OrderDate AS DATE, SalesMan AS Salesperson, Item, Units AS UNITS_SOLD
FROM Sales;

📌 Output Headers Would Look Like:

🧩 More Examples:
1. Alias for Expressions:
SELECT SalesMan, Units * Unit_price AS Total_Amount
FROM Sales;

2. Alias for Tables:

SELECT [Link], [Link]
FROM Sales AS S;

✅ S is now a temporary name for the Sales table in this query.

✅ Quick Recap:
Feature Description Example

Column Alias Temporary name for a column OrderDate AS DATE

Table Alias Temporary name for a table Sales AS S

Expressions Alias used to rename calculated values Units * Unit_price AS Total

Scope Aliases are temporary and only visible in query output No changes to actual table

📘 Module 19: Aliasing in SQL 2

 📘 Module 20: Aggregate Commands
Aggregate functions perform calculations on multiple rows and return a single value (e.g., sum,
count, average, etc.).

🔢 1. COUNT() – Count Records

✅ Definition:
Returns the number of rows or non-null values in a column.

✅ Syntax:
SELECT COUNT(*) FROM table_name;
SELECT COUNT(column_name) FROM table_name WHERE condition;

💡 Why Use Aliasing?

Makes the output column meaningful

Helps label the result in reports or dashboards

🧾 Reference Table Used in This Module

📘 Module 20: Aggregate Commands 1

 📌 Examples:
SELECT COUNT(*) AS Total_records FROM Sales;
✔ Counts all rows in the Sales table.

SELECT COUNT(Manager) AS Total_managers

FROM Sales
WHERE Region = 'Central';
✔ Counts rows where the Manager is not NULL in the Central region.

📘 Module 20: Aggregate Commands 2

 SELECT COUNT(DISTINCT Manager) AS Unique_managers
FROM Sales
WHERE Region = 'Central';
✔ Counts unique managers from the Central region.

➕ 2. SUM() – Total of Values

✅ Definition:
Returns the sum of numeric values in a column.

✅ Syntax:
SELECT SUM(column_name) FROM table_name WHERE condition;

📌 Examples:
SELECT SUM(Units) FROM Sales;
✔ Adds up all values in the
Units column.

📘 Module 20: Aggregate Commands 3

 SELECT SUM(Units) AS Total_units_sold
FROM Sales
WHERE SalesMan = 'Alexander';

✔ Adds Units sold by Alexander only.

📊 3. AVG() – Average Value

✅ Definition:
Returns the average (mean) of numeric values.

✅ Syntax:
SELECT AVG(column_name) FROM table_name WHERE condition;

📌 Examples:
SELECT AVG(Units) AS Avg_Units
FROM Sales WHERE Region = 'East';
✔ Averages
Units where Region = 'East'.

📘 Module 20: Aggregate Commands 4

 SELECT AVG(Units) AS Avg_Units
FROM Sales
WHERE Item = 'Home Theater';
✔ Average
Units for all rows where Item = 'Home Theatre'.

📈 4. MIN() and MAX() – Minimum & Maximum

✅ Definition:
MIN() returns the lowest value.

MAX() returns the highest value.

✅ Syntax:
SELECT MIN(column_name) FROM table_name WHERE condition;
SELECT MAX(column_name) FROM table_name WHERE condition;

📌 Examples:
1.

SELECT MAX(Sale_amt) AS Max_Sales

FROM Sales
WHERE OrderDate BETWEEN '2018-03-01' AND '2018-04-30';
✔ Gets the
maximum Sale_amt during March–April 2018.

📘 Module 20: Aggregate Commands 5

 2.

SELECT Sale_amt FROM Sales

WHERE OrderDate BETWEEN '2018-03-01' AND '2018-04-30'
ORDER BY Sale_amt ASC;
✔ Lists all sale amounts within the specified date range,
sorted from lowest to highest.

SELECT MIN(Sale_amt) AS Min_Sales FROM Sales

WHERE OrderDate BETWEEN '2018-03-01' AND '2018-04-30';
✔ Gets the
lowest Sale_amt during March–April 2018.

✔Similarity: Both filter data between the same OrderDate range and work on the Sale_amt
column.

✔Difference: One shows all values sorted( Order By), while the other shows only the minimum
using MIN().

📘 Module 20: Aggregate Commands 6

 ✅ Summary of Aggregate Functions
Function Description Example
COUNT() Count rows or values COUNT(*) , COUNT(DISTINCT column)

SUM() Add total values SUM(Units)

AVG() Calculate average AVG(Sale_amt)

MAX() Highest value MAX(Unit_price)

MIN() Lowest value MIN(Unit_price)

📘 Module 20: Aggregate Commands 7

 📘 Module 21: GROUP BY
🔹 1. GROUP BY

✅ Definition:
is used to group rows that have the same values in specified columns and perform
GROUP BY

aggregate functions on each group.

✅ Syntax:
SELECT column, AGG_FUNC(column2)
FROM table
GROUP BY column;

🎯 Common Use:
Used with aggregate functions like SUM() , AVG() , COUNT() , MAX() , MIN()

🧾 Reference Table Used in This Module

📘 Module 21: GROUP BY 1

 📌 Examples:
SELECT Manager, MAX(Sale_amt) AS Max_Sale
FROM Sales
GROUP BY Manager
ORDER BY Manager;
✔ Groups records by Manager and shows each Manager’s highest sale amount.

SELECT Region, AVG(Units) AS Avg_units

FROM Sales
GROUP BY Region;
✔ Groups rows by Region and shows average units sold in each.

📘 Module 21: GROUP BY 2

 🔹 2. HAVING

✅ Definition:
HAVING is used to filter grouped results based on conditions on aggregate functions.

✅ Syntax:
SELECT column, AGG_FUNC(column2)
FROM table
WHERE condition
GROUP BY column
HAVING aggregate_condition;

🎯 Common Use:
WHERE filters rows before grouping

HAVING filters groups after aggregation

📌 Example:
SELECT Manager, AVG(Units) AS Average_Units
FROM Sales
WHERE OrderDate >= '2018-04-01'
GROUP BY Manager
HAVING COUNT(Units) > 2;

✔ Shows only those Managers with more than 2 records after April 1, 2018.

📘 Module 21: GROUP BY 3

 🔎 What Are Aggregate Functions?
Aggregate functions perform calculations across multiple rows:

Function Use
SUM() Adds values
AVG() Finds average
COUNT() Counts rows
MAX() Highest value
MIN() Lowest value

🚫 Why Not Use WHERE with Aggregates?

WHERE works before grouping, and cannot handle aggregate functions like AVG() , COUNT() , etc.

Use HAVING to filter based on grouped/aggregated values

✅ Key Points to Remember

✔ Use with columns you want to group by
GROUP BY

✔ Use to filter aggregated groups

HAVING

✔ filters rows before grouping

WHERE

✔ You must include the grouped column(s) in the statementSELECT

✔ Aggregate functions like , , work best with

SUM AVG COUNT GROUP BY

📘 Module 21: GROUP BY 4

 📘 Module 22: Conditional Statement
🔹 What is CASE WHEN ?
The CASE statement lets you apply conditional logic in SQL — like an IF-ELSE system.

You can create new values based on different conditions in your data.

✅ Syntax:
SELECT column,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default_result
END AS new_column_name
FROM table;

🧪 Example (Student Grades):

Let’s say we have a Students table with columns StudentName and Marks .

SELECT StudentName, Marks,

CASE
WHEN Marks >= 90 THEN 'A+'
WHEN Marks >= 80 THEN 'A'
WHEN Marks >= 70 THEN 'B'
WHEN Marks >= 60 THEN 'C'
ELSE 'Fail'
END AS Grade
FROM Students;

✔ This query assigns a grade to each student based on their marks.

Marks Grade

92 A+

85 A

74 B

63 C

50 Fail

📘 Module 22: Conditional Statement 1

 🧾 Example (Salesman Grades):
SELECT *,
CASE
WHEN UNITS >= 90 THEN 'Tier 1'
WHEN UNITS >= 75 AND UNITS < 90 THEN 'Tier 2'
WHEN UNITS >= 60 THEN 'Tier 3'
ELSE 'DEROGATORY'
END AS SALESMAN_GRADE
FROM SALES;

✔ This adds a new column SALESMAN_GRADE based on the Units:

Units Range Grade

≥ 90 Tier 1

75 to < 90 Tier 2

60 to < 75 Tier 3

< 60 DEROGATORY

📘 Module 22: Conditional Statement 2

 📌 Key / Important Points to Remember
✅ must end with
CASE END

✅ defines the output for a matched condition

THEN

✅ is optional but useful for default values

ELSE

✅ You can use in , CASE SELECT ,

ORDER BY , or even in
GROUP BY WHERE (with care)

✅ It helps in categorizing data without changing the actual table

✅ Be careful with condition order: SQL checks conditions top to bottom

📘 Module 22: Conditional Statement 3

 📘QUERIES
Module 23: JOINS & COMBINING

🧾 Reference Tables Used:

👤 Table: Customers Orders

👤 Table: Table1 Table2

📘 Module 23: JOINS & COMBINING QUERIES 1

 🔗 1. What are JOINS?
🧩 Definition:
A JOIN is used in SQL to combine data from two or more tables based on a related column (usually
a primary and foreign key).

📌 Why use JOINs?

To fetch data spread across multiple tables

For combining customer info, order data, employee details, etc.

📚 Types of Joins:
Type Description

INNER JOIN Returns only matching rows from both tables

LEFT JOIN All rows from the left table + matched rows from the right

RIGHT JOIN All rows from the right table + matched rows from the left

FULL JOIN All rows when there is a match in either table

CROSS JOIN Returns the Cartesian product (every row from one joined with every row from another)

🧱 🔁 Common SQL JOIN Syntax

SELECT
A.column1, A.column2,
B.column1, B.column2
FROM
TableA AS A
<JOIN_TYPE> JOIN TableB AS B
ON A.common_column = B.common_column;

✅ Example for All Joins (with tables: customers and orders )

JOIN Type Example

FROM customers INNER JOIN orders ON [Link] =
INNER JOIN
[Link]

FROM customers LEFT JOIN orders ON [Link] =

LEFT JOIN
[Link]

FROM customers RIGHT JOIN orders ON [Link] =

RIGHT JOIN
[Link]

FROM customers FULL OUTER JOIN orders ON [Link] =

FULL JOIN
[Link]

CROSS JOIN FROM customers CROSS JOIN orders (no ON clause required)

📘 Module 23: JOINS & COMBINING QUERIES 2

 🔄 2. INNER JOIN
💡 Definition:
Returns only the matching rows from both tables.

🔣 Syntax | Example:
SELECT [Link], [Link],
[Link], [Link], [Link]
FROM customers INNER JOIN orders
ON [Link] = [Link];
🧪 Result: Only customers who placed orders.

👈 3. LEFT JOIN (LEFT OUTER JOIN)

💡 Definition:
Returns all rows from the left table (customers) and matching rows from the right table (orders).

If no match, NULLs are shown for right table columns.

🔣 Syntax | Example:
SELECT [Link], [Link],
[Link], [Link], [Link]
FROM customers LEFT JOIN orders
ON [Link] = [Link];

🧪 Result: All customers, even those with no orders.

📘 Module 23: JOINS & COMBINING QUERIES 3

 👉 4. RIGHT JOIN (RIGHT OUTER JOIN)
💡 Definition:
Returns all rows from the right table (orders) and matching rows from the left table (customers).

🔣 Syntax | Example:
SELECT [Link], [Link],
[Link], [Link], [Link]
FROM customers
RIGHT JOIN orders
ON [Link] = [Link];

🧪 Result: All orders, even if some customers are missing.

🔄 5. FULL OUTER JOIN

💡 Definition:
Returns all rows when there is a match in either left or right table.
If no match, NULLs are filled accordingly.

🔣 Syntax | Example:
SELECT [Link], [Link],
[Link], [Link], [Link]
FROM customers
FULL OUTER JOIN orders
ON [Link] = [Link];

🧪 Result: All customers and all orders, matching where possible.

📘 Module 23: JOINS & COMBINING QUERIES 4

 🔁 6. CROSS JOIN
💡 Definition:
Returns the Cartesian product — every row from first table with every row from second.

🔣 Syntax | Example:
SELECT [Link] AS CustID, [Link] AS CustName,
[Link] AS OrdID, [Link] AS OrdDate, [Link] AS OrdAmount
FROM customers
CROSS JOIN orders;

🧪 Result: If 3 customers × 4 orders = 12 rows.

⚠️ Use carefully on large data!

📘 Module 23: JOINS & COMBINING QUERIES 5

 🔀 Combining Queries (Set Operators)
🟰 7. INTERSECT
💡 Definition:
Returns common rows from both queries.
🔣 Syntax | Example:
SELECT * FROM table1
INTERSECT
SELECT * FROM table2;

🧪 Result: Only rows present in both table1 and table2 .

➖ 8. EXCEPT
💡 Definition:
Returns rows from first query that are not present in second.

🔣 Syntax | Example:
SELECT * FROM table1
EXCEPT
SELECT * FROM table2;

🧪 Result: Data unique to table1 .

📘 Module 23: JOINS & COMBINING QUERIES 6

 ➕ 9. UNION / UNION ALL
💡 Definition:
UNION returns unique records from both queries.

UNION ALL includes duplicates.

🔣 Syntax | Example:
SELECT * FROM table1
UNION
SELECT * FROM table2;

-- OR with duplicates:

SELECT * FROM table1

UNION ALL
SELECT * FROM table2;

🧪 Tables must have a same number of columns and compatible data types.

🧠 Key Points to Remember

✅ Use ON for join conditions, not just WHERE

✅ LEFT JOIN brings unmatched right rows as NULL

✅ FULL JOIN = all matched + unmatched rows

✅ INTERSECT = common only

✅ EXCEPT = remove common

✅ UNION = combine

✅ UNION ALL = combine + duplicates

📘 Module 23: JOINS & COMBINING QUERIES 7

 💡 Additional Tips
🔹 Always use aliases for cleaner joins in big queries
🔹 Use JOIN conditions properly to avoid unexpected cartesian results
🔹 Prefer INNER JOIN if only matching rows are needed — more performance-friendly
🔹 Use IS NULL to detect non-matching rows after OUTER JOIN
🔹 Use ORDER BY at the end of combined queries if needed

📘 Module 23: JOINS & COMBINING QUERIES 8

 📘 Module 24: Subqueries
🧾 Reference Tables Used in This Module
The following two tables are used for examples in this module:
📸 Image Preview – Customers and Orders Tables

🔹 What is a Subquery?
A subquery is a query that is written inside another SQL statement.

It is also known as a nested query and helps solve complex questions by breaking them into
simpler steps.

🧩 Part 1 – Subquery in WHERE Clause

✅ Definition:
Used to filter results in the outer query using a condition based on a result from the inner query.

✅ Syntax:
SELECT column1
FROM table1
WHERE column2 IN (SELECT column2 FROM table2 WHERE condition);

📘 Module 24: Subqueries 1

 📌 Example:
SELECT * FROM Customers
WHERE CustomerID IN (
SELECT CustomerID
FROM Orders
WHERE Amount > 250
);

🧾 Explanation:
Inner query selects CustomerIDs with order amounts > 250.

Outer query shows customer details who placed such orders.

🧾 Expected Result:

🧩 Part 2 – Subquery in FROM Clause

✅ Definition:
Used to create a temporary result set that can be used as a table by the main query.

✅ Syntax:
SELECT column1
FROM (SELECT ... FROM table WHERE ...) AS temp_table;

📌 Example:
SELECT [Link], [Link], [Link],
COALESCE([Link], 0) AS TotalOrderAmount
FROM Customers c
LEFT JOIN (
SELECT CustomerID, SUM(Amount) AS TotalAmount
FROM Orders
GROUP BY CustomerID
) o ON [Link] = [Link];

📘 Module 24: Subqueries 2

 🧾 Explanation:
The subquery ( o ) calculates total amount per customer.

The outer query joins it with Customers to show each customer and their total order amount.

COALESCE(..., 0) returns 0 if no orders exist for that customer.

🧾 Expected Result:

🧩 Part 3 – Subquery in SELECT Clause

✅ Definition:
Used to return a value in a column by running a subquery for each row in the outer query.

✅ Syntax:
SELECT column1,
(SELECT ... FROM table2 WHERE [Link] = [Link]) AS alias
FROM table1;

📘 Module 24: Subqueries 3

 📌 Example:
SELECT [Link], [Link], [Link],

(SELECT COUNT(*)
FROM Orders o
WHERE [Link] = [Link]) AS TotalOrders,

(SELECT SUM([Link])
FROM Orders o
WHERE [Link] = [Link]) AS TotalAmountSpent

FROM Customers c;

🧾 Explanation:
Two subqueries run for each customer:

One to count how many orders they placed.

Another way to sum how much they spent.

All values are shown in the result for each customer.

🧾 Expected Result:

📘 Module 24: Subqueries 4

 📝 Key Points to Remember
✔ A subquery returns data that is used by the outer/main query.
✔ You can use subqueries in:
WHERE clause (to filter rows)

FROM clause (as a virtual table)

SELECT clause (to calculate values per row)

✔ Subqueries can return:

A single value

A list of values

A table

✔ Always alias subqueries in the clause. FROM

✔ Use to handle NULLs when using subqueries with outer joins.

COALESCE()

✔ Subqueries can be correlated (use values from outer query) or independent.

📘 Module 24: Subqueries 5

 📘 Module 25: Views and Indexes
🔹 1. VIEW
✅ What is a View?
A view is a virtual table that is based on the result of a SQL query.

It does not store data itself but presents data from one or more tables.
Views are used to:

Simplify complex queries

Improve data security (show limited data)

Help standardise reporting

✅ Syntax:
1. CREATE VIEW view_name AS
SELECT column1, column2 FROM table
WHERE condition;

2. DROP VIEW view_name;

3. ALTER VIEW view_name AS

SELECT ...

📌 Example:
CREATE VIEW CustomerOrders AS
SELECT [Link], [Link], [Link] FROM Customers c
JOIN Orders o ON [Link] = [Link];
✔ Now you can run:
SELECT * FROM CustomerOrders;

📘 Module 25: Views and Indexes 1

 💡 Example:
CREATE VIEW CustomerOrderSummary AS
SELECT [Link], [Link], [Link],
-- Total number of orders per customer
(SELECT COUNT(*) FROM Orders o
WHERE [Link] = [Link]) AS TotalOrders,

-- Total amount spent by the customer

(SELECT SUM([Link]) FROM Orders o
WHERE [Link] = [Link]) AS TotalAmountSpent,

-- Average amount per order by the customer

(SELECT AVG([Link]) FROM Orders o
WHERE [Link] = [Link]) AS AverageOrderAmount,

-- Date of the latest order by the customer

(SELECT MAX([Link]) FROM Orders o
WHERE [Link] = [Link]) AS LastOrderDate

FROM Customers c;

✔ Now you can run:

SELECT * FROM CustomerOrderSummary;

📘 Module 25: Views and Indexes 2

 🔁 Can a View Be Updated?
✔ Yes, if:
It refers to only one base table

It has no aggregates, joins, DISTINCT , GROUP BY , etc.

❌ No, if:
It includes multiple tables, joins, calculations, etc.

📝 Important Points to Remember About Views:

Views are not physical tables – they store SQL logic.

Data shown in a view is always the latest from the base tables.

You can use views in SELECT , JOIN , and even in other views.

Use WITH SCHEMABINDING to lock the structure if needed.

🔹 2. INDEX
✅ What is an Index?
An index in SQL Server is like an index in a book — it helps speed up searches and queries on
large tables.

Indexes are created on columns to make data retrieval faster, especially for:

WHERE filters

ORDER BY clauses

JOIN conditions

✅ Syntax:
1. CREATE INDEX index_name
ON table_name (column1, column2);

2. DROP INDEX index_name ON table_name;

3. Alter

- Rebuild (fix fragmentation)

ALTER INDEX index_name ON table_name REBUILD;

- Reorganise (light maintenance)

ALTER INDEX index_name ON table_name REORGANIZE;

📘 Module 25: Views and Indexes 3

 📌 Example:
CREATE INDEX idx_CustomerName
ON Customers (CustomerName);

✔ This will speed up queries like:

SELECT * FROM Customers WHERE CustomerName = 'Alice Johnson';

🔍 Types of Indexes
Type Description

Clustered Reorders the actual data rows — only one per table

Non-Clustered A pointer index — stores sorted pointers to the actual data rows

Unique Index Ensures that all values in the indexed column are unique

Composite Index Indexes multiple columns together

Full-text Index Used for fast searching in large text columns (e.g., articles, descriptions)

✅ Good Practices for Indexing

✔ Create indexes on columns used in:
WHERE

JOIN

ORDER BY

GROUP BY

✔ Don’t over-index — too many indexes slow down INSERT, UPDATE, DELETE
✔ Always name your indexes clearly ( )
idx_tablename_columnname

✔ Use the Composite Index if you frequently filter by multiple columns

✔ Analyse performance using SQL Server's Execution Plan

📘 Module 25: Views and Indexes 4

 ✅ Summary
Concept Purpose Key Commands

VIEW Virtual table for simplifying data CREATE VIEW , SELECT FROM view

INDEX Speed up search performance CREATE INDEX , DROP INDEX

📘 Module 25: Views and Indexes 5

 📘 Module 26: String Functions
🧾 What are String Functions?
String functions in SQL are used to manipulate text data (also called character or varchar data).
These functions help format, clean, extract, or combine text values in your queries.

1️⃣ LEN() – Length

✅ Definition:
Returns the number of characters in a string (excluding trailing spaces).

✅ Syntax:
LEN(string)

📌 Example:
1. SELECT LEN('Alexander') AS NameLength;

2. SELECT LEN(customername) AS NLength

from Customers order by len(customername);

📘 Module 26: String Functions 1

 2️⃣ UPPER() and LOWER()

✅ Definition:
UPPER() converts text to uppercase

LOWER() converts text to lowercase

✅ Syntax:
UPPER(string)
LOWER(string)

📌 Example:
1. SELECT UPPER('hello') AS UpperCase, LOWER('HELLO') AS LowerCase;

2. SELECT upper(customername)

from Customers;

📘 Module 26: String Functions 2

 3️⃣ REPLACE()

✅ Definition:
Replaces all occurrences of a substring within a string.

✅ Syntax:
REPLACE(original_string, string_to_replace, replacement_string)

📌 Example:
SELECT REPLACE('Data Science', 'Science', 'Analytics') AS Result;
-- Output: 'Data Analytics'

4️⃣ TRIM() , LTRIM() , RTRIM()

✅ Definitions:
TRIM() – removes both leading and trailing spaces

LTRIM() – removes spaces from the left

RTRIM() – removes spaces from the right

✅ Syntax:
TRIM(string)
LTRIM(string)
RTRIM(string)

📌 Example:
SELECT
TRIM(' SQL ') AS Trimmed,
LTRIM(' SQL') AS LeftTrimmed,
RTRIM('SQL ') AS RightTrimmed;

📘 Module 26: String Functions 3

 5️⃣ String Concatenation – + or CONCAT()

✅ Definition:
Combines two or more strings into one.

✅ Syntax:
-- Method 1: Using +
string1 + string2

-- Method 2: Using CONCAT()

CONCAT(string1, string2, ...)

📌 Example:
SELECT 'Data' + 'Science' AS Combined;
-- Output: 'DataScience'

SELECT CONCAT('Hello ', 'World') AS Greeting;

-- Output: 'Hello World'

📘 Module 26: String Functions 4

 6️⃣ SUBSTRING()

✅ Definition:
Extracts a part of a string starting from a specific position.

✅ Syntax:
SUBSTRING(string, start_position, length)

📌 Example:
SELECT SUBSTRING('DataScience', 5, 7) AS Extracted;

7️⃣ STRING_AGG() – String Aggregation

✅ Definition:
Combines multiple string values from rows into a single string, separated by a delimiter (SQL
Server 2017+).

✅ Syntax:
STRING_AGG(column_name, 'separator') AS alias

📌 Example:
SELECT
[Link],
STRING_AGG([Link], ', ') AS CustomerNames
FROM Orders o
JOIN Customers c ON [Link] = [Link]
GROUP BY [Link];

🧾 Explanation:
STRING_AGG([Link], ', ') combines customer names with a comma and space.

Grouped by CustomerID to show which customers placed orders.

Even if a customer placed multiple orders, their name appears once per ID (because of
grouping).

📘 Module 26: String Functions 5

 🧠 Key Points to Remember
✔ does not count trailing spaces
LEN()

✔ Use to clean messy data

TRIM()

✔ works for partial substitutions inside strings

REPLACE()

✔ Use for simple joins and

+ CONCAT() when working with NULLs (it handles NULL safely)

✔ uses 1-based indexing (starts at 1, not 0)

SUBSTRING()

✔ is very useful in reports – shows grouped data in a single row

STRING_AGG()

✔ Combine string functions for more powerful results (e.g., )

UPPER(SUBSTRING(...))

📘 Module 26: String Functions 6

 📘 Module 27: Mathematical Functions
🧠 Why Use Math Functions?
Mathematical functions in SQL help perform calculations like rounding, generating random
numbers, and power/exponents — useful in reporting, simulations, discount logic, and more.

1️⃣ CEILING() and FLOOR()

✅ Definitions:
CEILING() returns the next highest integer (rounds up).

FLOOR() returns the next lowest integer (rounds down).

✅ Syntax:
CEILING(numeric_expression)
FLOOR(numeric_expression)

📌 Example:
SELECT CEILING(45.23) AS CeilValue, FLOOR(45.23) AS FloorValue;
-- Output: CeilValue = 46, FloorValue = 45

2️⃣ RAND() – Random Number Generator

✅ Definition:
Returns a random float value between 0 (inclusive) and 1 (exclusive).

✅ Syntax:
RAND() -- no arguments = random decimal between 0 and 1

📘 Module 27: Mathematical Functions 1

 📌 Example:
SELECT RAND();

✅ Custom Range Example:

-- Random value between 4 and 10 (float)
SELECT RAND() * (10 - 4) + 4 AS rand_;

-- Random integer between 4 and 10

SELECT FLOOR(RAND() * (10 - 4 + 1)) + 4 AS floor_rand;

🧾 Explanation:
Multiply by range size → RAND() * (high - low)

Add the lower bound → + low

Use FLOOR() to convert to an integer (optional)

📘 Module 27: Mathematical Functions 2

 3️⃣ ROUND()

✅ Definition:
Rounds a number to a specific number of decimal places.

✅ Syntax:
ROUND(numeric_expression, decimal_places)

📌 Example:
SELECT ROUND(123.45678, 2) AS RoundedVal;

4️⃣ POWER()

✅ Definition:
Returns the result of a number raised to a power (exponentiation).

✅ Syntax:
POWER(base, exponent)

📌 Example:
SELECT POWER(5, 3) AS Result;

📘 Module 27: Mathematical Functions 3

 📝 Key Points to Remember
Function Use Case
CEILING() Always rounds up
FLOOR() Always rounds down
RAND() Returns a float between 0 and 1
ROUND() Useful for formatting decimal output
POWER() Calculate exponents (e.g., square, cube)
FLOOR(RAND()*N)+M Formula for random integers in range

✔ CEILING , FLOOR , and ROUND are good for billing, formatting, and data cleaning
✔ RAND() helps in generating test data or random IDs

✔ POWER() can be used for growth calculations, interest, etc.

📘 Module 27: Mathematical Functions 4

 📘 Module 28: DATE & TIME Functions
🔍 Why Use Date/Time Functions?
Date and time functions help you work with timestamps, durations, and date parts, which are
essential in reporting, logging, scheduling, and calculating age or duration.

🕒 1. Getting the Current Date and Time

✅ Functions:
Function Description
CURRENT_TIMESTAMP Returns the current date and time
GETDATE() Returns current date and time (datetime)
SYSDATETIME() Returns higher-precision datetime2
GETUTCDATE() Returns current UTC date and time

✅ Syntax & Example:

SELECT CURRENT_TIMESTAMP AS CurrentTime,
GETDATE() AS GetDateTime,
SYSDATETIME() AS SystemTime,
GETUTCDATE() AS UTCTime;

📅 2. Calculating Age or Duration – DATEDIFF()

✅ Definition:
Returns the difference between two dates in specified units like years, months, days, etc.

📘 Module 28: DATE & TIME Functions 1

 ✅ Syntax:
DATEDIFF(unit, start_date, end_date)

Unit Description

year yy or yyyy

month mm

day dd

hour hh

minute mi

📌 Example:
SELECT DATEDIFF(YEAR, '2000-01-01', GETDATE()) AS Age, DATEDIFF(HOUR, '2025-05-31
08:00:00', '2025-05-31 14:30:00') AS HoursDifference;

✔ You can also calculate days, hours, etc., between any two timestamps.
🔎 3. Extracting Date and Time Parts
You can extract parts of a date like year, month, day, weekday, hour, etc.

✅ DATEPART() – Returns numeric part

SELECT DATEPART(YEAR, GETDATE()) AS CurrentYear,
DATEPART(MONTH, GETDATE()) AS CurrentMonth;

📘 Module 28: DATE & TIME Functions 2

 ✅ DATENAME() – Returns text part
SELECT DATENAME(WEEKDAY, GETDATE()) AS DayName,
DATENAME(MONTH, GETDATE()) AS MonthName;

✅ Other useful functions:

DAY(date) – Returns day of the month

MONTH(date) – Returns month number

YEAR(date) – Returns year number

ISDATE(string) – Returns 1 if input is a valid date

📌 Example:
SELECT
DAY(GETDATE()) AS DayNum,
MONTH(GETDATE()) AS MonthNum,
YEAR(GETDATE()) AS YearNum,
ISDATE('2024-12-01') AS IsValidDate;

📘 Module 28: DATE & TIME Functions 3

 📝 Key Points to Remember
Topic Notes
GETDATE() Returns current local system date & time
SYSDATETIME() Higher precision, useful in logs
DATEDIFF() Use to calculate age, tenure, duration
DATEPART() Returns numeric values (e.g., 1 for January)
DATENAME() Returns string values (e.g., 'January')
ISDATE() Helps validate dynamic string inputs as valid dates

Date formats SQL Server default is yyyy-mm-dd for compatibility

Always test with GETDATE() Useful for reports, timestamps, filtering

📘 Module 28: DATE & TIME Functions 4

 📘Matching
Module 29: Pattern (String)

🔍 🧠 What is Pattern Matching?

Pattern matching is used to search, filter, or identify data that matches a specific text format using
symbols and functions.

It is especially useful when:

You don’t know the full value

You want to filter by starting, ending, or partial text

🔡 Pattern Matching Using LIKE and Wildcards

✅ Remember:
The LIKE operator is the most basic tool for pattern matching.

Use wildcards like % , _ , and [] to define your pattern.

✅ Common Wildcards:
Wildcard Meaning Example Matches
% Any number of characters 'A%' A, Alex, Apple
_ Exactly one character 'J_n' Jan, Jon, Jim
[] One character from a set/range '[A-C]%' Apple, Beta, Cat
[^ ] Not in the given set '[^A-C]%' Mango, Zebra

📌 Example:
SELECT * FROM Customers
WHERE CustomerName LIKE 'A%';

📘 Module 29: Pattern (String) Matching 1

 🔍 PATINDEX() – Pattern Index

✅ Definition:
Returns the starting position of a pattern in a string using wildcards ( % ).

✅ Syntax:
PATINDEX('%pattern%', string)

📌 Example:
SELECT PATINDEX('%son%', 'Jackson') AS Position;
✔ Returns 0 if the pattern is not found.
✔ Case-insensitive by default in SQL Server

🔎 CHARINDEX() – Character Index

✅ Definition:
Finds the position of a substring in a string.

Unlike PATINDEX , it doesn’t support % wildcards.

✅ Syntax:
CHARINDEX('substring', string)

📌 Example:
SELECT CHARINDEX('a', 'Tarun') AS Position;

✔ Returns position of first match

✔ Returns 0 if substring not found

📘 Module 29: Pattern (String) Matching 2

 ⬅️➡️ LEFT() and RIGHT() – Extracting Substrings

✅ Definitions:
LEFT() extracts characters from the start of a string.

RIGHT() extracts characters from the end of a string.

✅ Syntax:
LEFT(string, number_of_characters)
RIGHT(string, number_of_characters)

📌 Example:
SELECT
LEFT('DataScience', 4) AS StartPart,
RIGHT('DataScience', 7) AS EndPart;

✔ Use to split or format columns like name, IDs, phone numbers, etc.

📘 Module 29: Pattern (String) Matching 3

 📝 Key Points / Important Notes
Concept Tip / Usage
LIKE Use % , _ for flexible search
PATINDEX() Supports wildcard % , returns position
CHARINDEX() Exact substring match, no wildcards
LEFT()/RIGHT() Use to trim/focus on part of string

Case-sensitivity LIKE is case-insensitive in SQL Server

Combination Combine functions (e.g., LEFT(FullName, 1) )

🧠 Bonus Tips:
Use ISNULL() or COALESCE() when working with string functions on nullable fields

Use TRIM() before pattern matching to clean extra spaces

Combine LIKE + LEFT/RIGHT + CHARINDEX() for powerful custom filters

📘 Module 29: Pattern (String) Matching 4

 📘 Module 30: Window Functions
This module introduces window functions in SQL that let you perform calculations across rows
while keeping individual row details. These are powerful for advanced analytics like ranking,
running totals, comparisons, etc.

🧾 Reference Tables Used in This Module:

📊 Table 1: Sales

🧑‍💼 Table 2: Employees

📘 Module 30: Window Functions 1

 🌐 1. 💡 What are Window Functions
🔍 Window functions perform calculations across a group of rows (a "window") that are related to
the current row.
They do not collapse the rows like GROUP BY . Instead, they return values for every row.

🧩 Syntax:
FUNCTION_NAME(column)
OVER (
PARTITION BY column_name
ORDER BY column_name
ROWS BETWEEN ... -- optional
)

💡 Components:
🧭 PARTITION BY – Divides the result into groups (like departments).

🪜 ORDER BY – Sorts rows inside each group (e.g., by salary).

🧱 ROWS or RANGE – Controls which rows are included in the window (optional, advanced use).

🔢 2. 🎯 ROW_NUMBER()
🚦 Gives a unique serial number to each row within a partition.
🔣 Syntax:
ROW_NUMBER() OVER(PARTITION BY column ORDER BY column)

📌 Example:
SELECT SalesID, SalesDate, Amount,
ROW_NUMBER() OVER(PARTITION BY SalesDate ORDER BY SalesID) AS RowNum
FROM Sales;

📘 Module 30: Window Functions 2

 👑 3. 🧮 Row Number Implementation (Use Case)
Use this to get top earners in each department.
📌 Example 1:
SELECT employeeid, employeename, department, salary,
ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

📌 Example 2 (Only Top Earners):

SELECT * FROM (
SELECT employeeid, employeename, department, salary,
ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees
) AS subquery
WHERE row_num = 1;

📘 Module 30: Window Functions 3

 🥇 4. 🎖️ RANK() vs DENSE_RANK()
🗂 Used to rank rows within a partition.
🔣 Syntax:
RANK() OVER(PARTITION BY column ORDER BY column)
DENSE_RANK() OVER(PARTITION BY column ORDER BY column)

📌 Examples:
SELECT *, RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

SELECT *, DENSE_RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS rank

FROM employees;

📘 Module 30: Window Functions 4

 🧠 Key Difference:
Function Handles Ties Skips Rank

ROW_NUMBER ❌ No ❌ No
RANK ✅ Yes ✅ Yes
DENSE_RANK ✅ Yes ❌ No

🎯 5. 🪜 NTILE(n)
📊 Divides data into equal parts or buckets (for percentile-based grouping).
🔣 Syntax:
NTILE(n) OVER(ORDER BY column)
📌 Example:
SELECT employeeid, employeename, salary,
NTILE(3) OVER(ORDER BY salary DESC) AS ntile_group
FROM employees;

📘 Module 30: Window Functions 5

 ⚖️ 6. 📉 AVG() (Window Average)
📐 Calculates average for a group but keeps the row.
🔣 Syntax:
AVG(column) OVER(PARTITION BY column)

📌 Examples:
SELECT *, AVG(salary) OVER(PARTITION BY department) AS avg_salary
FROM employees;

-- Rounded version
SELECT *, FLOOR(AVG(salary) OVER(PARTITION BY department)) AS avg_salary
FROM employees;

📘 Module 30: Window Functions 6

 🔢 7. 🔁 COUNT() (Window Count)
📋 Counts how many rows are there in the partition.
🔣 Syntax:
COUNT(column) OVER(PARTITION BY column)

📌 Example:
SELECT *, COUNT(EmployeeID) OVER(PARTITION BY department) AS employee_count
FROM employees;

💰 8. ➕ SUM() (Window Total)

💸 Gives total sum of values in a group.
🔣 Syntax:
SUM(column) OVER(PARTITION BY column)
📌 Example:
SELECT *, SUM(Salary) OVER(PARTITION BY department) AS total_salary
FROM employees;

📘 Module 30: Window Functions 7

 📈 9. 🔂 Running Total
📊 Running total is a cumulative sum row by row.
🔣 Syntax:
SUM(column) OVER(PARTITION BY column ORDER BY column)

📌 Example:
SELECT *,
SUM(Salary) OVER(PARTITION BY department ORDER BY Salary DESC) AS cumulative_salary
FROM employees
ORDER BY department, salary DESC;

🔁 10. ⬅️➡️ LAG() and LEAD()

⏮ LAG() → Gets value from previous row

⏭ LEAD() → Gets value from next row

🔣 Syntax:
LAG(column) OVER(PARTITION BY col ORDER BY col)
LEAD(column) OVER(PARTITION BY col ORDER BY col)

📌 Example:
SELECT *,
LAG(Salary) OVER(PARTITION BY department ORDER BY salary DESC) AS previous_salary,
LEAD(Salary) OVER(PARTITION BY department ORDER BY salary DESC) AS next_salary
FROM employees;

📘 Module 30: Window Functions 8

 🧠 Key Points to Remember
✅ Window functions do not group data likeGROUP BY .

✅ You can use to group, but rows are still individual.

PARTITION BY

✅ Always use for meaningful results in ranking, cumulative total, etc.

ORDER BY

✅ LAG/LEAD are useful for comparing rows side by side.

✅ ROWS BETWEEN can be used for moving average, running max, min (advanced).

🧠 Additional Tips
🔹 You can use WHERE row_num = 1 trick with ROW_NUMBER to filter top entries per group
🔹 Can also be used in CTEs (Common Table Expressions)
🔹 Great for reporting, pagination, and analytical dashboards

📘 Module 30: Window Functions 9

 📘Functions
Module 31: Data Type Conversion

🔄 Why Convert Data Types?

In SQL, data sometimes needs to be converted from one type to another, such as:

Numbers → Strings

Strings → Dates

Decimals → Integers

Money → Text, etc.

SQL provides two main functions for type conversion:

CAST()

CONVERT()

1️⃣ CAST() Function

✅ Definition:
CAST() is used to convert a value from one data type to another.
It is ANSI-standard SQL, and it works on most databases.

✅ Syntax:
CAST(expression AS target_data_type)

📌 Examples:
1. Convert decimal to integer:

SELECT CAST(45.8765 AS INT);

-- Output: 45

2. Convert string to datetime:

SELECT CAST('2025-03-05' AS SMALLDATETIME) AS DATE_TIME;

-- Output: 2025-03-05 00:00:00

3. Convert number to string:

SELECT CAST(45.87 AS VARCHAR) AS Casted_Val;

-- Output: '45.87'

📘 Module 31: Data Type Conversion Functions 1

 2️⃣ CONVERT() Function

✅ Definition:
CONVERT() is used to convert a value from one data type to another, just like CAST() , but it also
allows style formatting, especially for date and money conversions.

✅ Syntax:
CONVERT(target_data_type, expression [, style])

📌 Examples:
1. Convert string to datetime:

SELECT CONVERT(SMALLDATETIME, '2025-03-05') AS DATE_TIME;

-- Output: 2025-03-05 00:00:00

2. Convert number to string:

SELECT CONVERT(VARCHAR, 45.87);

-- Output: '45.87'

📘 Module 31: Data Type Conversion Functions 2

 3. Convert money to a formatted string:

SELECT CONVERT(VARCHAR(30), $120, 2);

-- Output: '120.00'
🧾 Explanation:
$120 is treated as money, and style code 2 formats it to 2 decimal places, then the result is
converted to a varchar(30) string.

🔹 Example with a Variable:

DECLARE @price MONEY = 120.4567;
SELECT CAST(@price AS VARCHAR) AS CastedPrice,
CONVERT(VARCHAR, @price, 1) AS ConvertedPrice;

✔
CAST gives a plain string, while CONVERT with style (e.g., 1 ) adds formatting like commas or decimal
styling.

📘 Module 31: Data Type Conversion Functions 3

 🔹 Example with a Column:
If you have a column like OrderDate in a table Orders (Reference Table in Module 24):

SELECT
CAST(OrderDate AS VARCHAR) AS OrderDateText,
CONVERT(VARCHAR, OrderDate, 103) AS FormattedDate
FROM Orders;

✔ Style 103 returns date in dd/mm/yyyy format.

🧠 Expression in CAST/CONVERT Can Be:

Expression Type Example

Literal CAST(45.67 AS INT)

Column CONVERT(VARCHAR, OrderDate, 103)

Variable CAST(@price AS VARCHAR)

📘 Module 31: Data Type Conversion Functions 4

 ⭐ Key Points to Remember
Point Explanation
CAST() ANSI standard, portable across most SQL systems
CONVERT() SQL Server-specific, supports style formatting

Use for Changing data types, preparing output, formatting reports

Styles Only supported in CONVERT() (especially for dates, money)

Common types int , varchar , datetime , float , money

Errors If conversion isn’t possible (e.g., text to int), SQL will throw an error

🔁 Quick Reference Table

Function From → To Example
CAST() Float → Int CAST(45.87 AS INT) → 45

CAST() String → Date CAST('2025-03-05' AS DATE) → 2025-03-05

CONVERT() Money → String CONVERT(VARCHAR, $120, 2) → '120.00'

CONVERT() Date Format Style CONVERT(VARCHAR, GETDATE(), 103) → 31/05/2025

📘 Module 31: Data Type Conversion Functions 5

 📘Welcome
Module 32: Python Course –
and Setup
🔰 1. Welcome to the Python Course
This module provides an overview of Python programming—a powerful, easy-to-learn language
used in data analytics, web development, AI, automation, and more.

🔷 1. Python Overview
✅ Python is a high-level, interpreted, and object-oriented programming language known for its
simplicity and readability.

📌 Used for:
Data Analysis

Web Development

Automation

Machine Learning & AI

Scripting & APIs

App/Game Development

🌟 3. Advantages of Python
💡 Advantage 🔍 Description
✅ Simple & Easy Clean syntax, readable like English. Great for beginners.

🧩 Open Source Free to use and community-supported.

🕒 Short Development Time Fewer lines of code, faster development.

📖 Readable Indentation-based structure improves clarity.

🔄 Interpreted Language Executes line-by-line, great for debugging.

🔧 Portable & Versatile Works on Windows, Linux, macOS.
🧠 Large Library Support Built-in and external libraries make complex tasks simple.
🧠 4. Key Characteristics of Python
🔹 Term 📘 Meaning
🧾 Interpreted Code runs line-by-line; no need to compile first.

🧱 Object-Oriented Uses objects and classes to structure code.

🏗️ High-Level Closer to human language than machine language.

🔄 Dynamically Typed Data type of a variable is inferred at runtime.

📘 Module 32: Python Course – Welcome and Setup 1

 🔍 5. Difference: Interpreted vs. Compiled Languages
⚙️ Feature 🧪 Interpreted 🏗️ Compiled
Execution Line-by-line Whole code at once

Speed Slower Faster

Debugging Easier (shows line-wise error) Harder (error shown after compiling)

Example Python, JavaScript C, C++

💡 6. Dynamic Semantics in Python

🔸 Dynamic semantics means the meaning of variables, types, or expressions is determined
during runtime, not during compilation.
🔹 This makes Python flexible but may also lead to runtime errors if not handled carefully.
🔁 7. Object-Oriented Programming in Python
Python supports:

📦 Classes & Objects

🔁 Inheritance
🔐 Encapsulation
🔄 Polymorphism
This helps in building modular, reusable, and structured code.

🧪 8. Uses of Python
📌 Python is used in:
📊 Data Science
🤖 Artificial Intelligence & Machine Learning
🌐 Web Development (Flask, Django)
🧪 Scientific Computing
📈 Data Visualization
🤖 Automation & Scripting
📱 App & Game Development
📦 9. Python Libraries
✅ Built-in (Standard) Libraries:
math , datetime , os , random , re , json

📘 Module 32: Python Course – Welcome and Setup 2

 🧰 External Libraries (Common in Data & Analytics):
📚 Library 🔍 Purpose
🔢 NumPy Numerical operations, multi-dimensional arrays

📊 Pandas Data manipulation and analysis (DataFrames)

📈 Matplotlib Basic data visualization

🎨 Seaborn Advanced statistical data visualization

🧠 Scikit-learn Machine learning algorithms

🔍 BeautifulSoup Web scraping
🌐 Requests HTTP requests
⚠️ Install external libraries using pip install <library-name>
🧠 Key Points to Remember
📌 Python is beginner-friendly but powerful enough for advanced use cases
📌 It supports multi-paradigm programming (procedural, object-oriented, functional)
📌 Use external libraries to speed up development and avoid writing everything from scratch
📌 Interpreted nature makes debugging easier but may be slower than compiled languages
📌 Popular among developers, analysts, scientists, and AI engineers

📘 Module 32: Python Course – Welcome and Setup 3

 📘DataModule
Types
33: Objects, Variables and

🛠️ Setting Up Python
▶️ Option 1: Practice Online with Jupyter (No Installation Needed)
Use Python directly in your browser.

Great for quick practice, learning, and experiments.

🌐 Website: [Link]
Features:
No installation or sign-in required

Supports markdown + code

Works on PC, tablet, or mobile browser

Temporary sessions (data not saved permanently)

▶️ Option 2: Install Python Locally

1. Download from: [Link]

2. Run installer (tick ✅ “Add Python to PATH”)

3. Use with any code editor (IDLE, VS Code, PyCharm, etc.)

🔹 1. Objects and Variables

In Python, everything is an object

A variable is a name that refers to an object (like a label)

x = 10
name = "Ajay"

🔧 Methods
Methods are actions associated with objects. Example:

📘 Module 33: Objects, Variables and Data Types 1

 📘 Use help() to explore object capabilities:

✅ Good Coding Practices

Use meaningful variable names

Use snake_case for variables (e.g. student_name )

Always maintain proper indentation (4 spaces)

Use comments ( # ) to explain steps

🔢 2. Numbers in Python
🧮 Main Number Types:
Type Description Example

Integer Whole numbers 10 , -3

Float Decimal numbers 3.14

Complex Real + Imaginary 2 + 3j

🔗 Arithmetic Operations:

✅ Python follows PEDMAS (Parentheses, Exponent, Division/Multiplication, Addition/Subtraction)

📘 Module 33: Objects, Variables and Data Types 2

 🔤 3. Strings in Python
Strings are sequences of characters.

📝 Ways to Declare Strings:

'Single quotes'
"Double quotes"
'''Triple quotes for multiline'''

🔁 Special Characters:
Symbol Purpose
\n New line
\' Single quote
\" Double quote
\\ Backslash

✂️ 4. String Operations
🔢 String Indexing

🔪 String Slicing

📘 Module 33: Objects, Variables and Data Types 3

 🧰 5. String Methods & Properties
✅ Strings are immutable (cannot be changed after creation)
Method Description Syntax Example
upper() Converts to UPPERCASE [Link]() 'abc'.upper() → 'ABC'

lower() Converts to lowercase [Link]() 'XYZ'.lower() → 'xyz'

find() Finds index of a char [Link]('x') 'box'.find('x') → 2

replace() Replace part of string [Link](a,b) 'car'.replace('a','u')

split() Break into list [Link]() 'a b c'.split() → list

islower() Check all lowercase [Link]() 'abc'.islower() → True

isupper() Check all uppercase [Link]() 'ABC'.isupper() → True

strip() Remove outer whitespace [Link]() ' abc '.strip() → 'abc'

➕ 6. String Concatenation & Formatting

🔗 Concatenation using +

📘 Module 33: Objects, Variables and Data Types 4

 🧩 Formatting using f-strings

🧾 7. List – Ordered & Mutable Collection

🔍 What is a List?
A list is a sequence of items.

It is ordered and mutable

Can contain mixed types: numbers, strings, lists, etc.

✅ Why Lists Are Useful:

Dynamic resizing

Ideal for iteration, collections, queues, and loops

Nesting possible: lists inside lists

🔢 Example:

📘 Module 33: Objects, Variables and Data Types 5

 ➕ Nested List Example:

🧰 List Methods:
Method Description Syntax Example
append() Add item to end [Link](x) [Link](4)

extend() Add multiple items [Link]([x,y]) [Link]([5,6])

insert() Add item at index [Link](i,x) [Link](1, 99)

remove() Remove item by value [Link](x) [Link](3)

pop() Remove item by index [Link]() [Link]()

clear() Empty the list [Link]()

sort() Sort items (ascending) [Link]()

reverse() Reverse the list [Link]()

index() Get index of item [Link](x)

📘 8. Dictionary – Key-Value Mapping

🔍 What is a Dictionary?
Unordered (in < 3.7), now ordered (3.7+)

Stores key-value pairs

Keys must be unique & immutable

✅ Why Dictionaries are Useful:

Fast data retrieval

Real-life mapping (student data, user profiles)

Can hold any type of value, including lists & other dictionaries

🧪 Example:

📘 Module 33: Objects, Variables and Data Types 6

 🔗 Nested Dictionary:

🧰 Dictionary Methods:
Method Description Example
get() Get value from key [Link]("name")

keys() Get all keys [Link]()

values() Get all values [Link]()

items() Get key-value pairs [Link]()

update() Add another dictionary [Link]({'grade':10})

pop() Remove a key [Link]("age")

🧱 9. Tuples and Sets

🔸 Tuples – Immutable, Ordered
Cannot change once created

Used for safe, fixed data

Supports indexing and nesting

📘 Module 33: Objects, Variables and Data Types 7

 ⚠️ Note: Use a comma for single-element tuple → (5,)
🔸 Sets – Unordered, Unique
No duplicate values

Cannot be indexed

Good for membership tests & uniqueness

🔁 Set Operations:

📘 Module 33: Objects, Variables and Data Types 8

 🔁 10. Booleans
Two values: True and False (capital T/F)

✅ Example:

⚙️ Boolean Operators:
and

or

not

🧠 Key Points to Remember

Data Type Ordered Mutable Duplicates Common Use

List ✅ ✅ ✅ Sequences, stacks, queues

Tuple ✅ ❌ ✅ Fixed collections (e.g., coordinates)

Set ❌ ✅ ❌ Uniqueness, fast membership check

Dict ✅ (3.7+) ✅ ❌ (Keys) Labelled data, lookups

🔹 Practice with all types

🔹 Strings are immutable, lists/dictionaries are mutable
🔹 Use the correct data type based on your goal
🔹 Strings, lists, dictionaries, tuples, and sets are the core of Python

📘 Module 33: Objects, Variables and Data Types 9

 📘 Module 34: Control Flow and Loops
🔰 1. Python Operators
Python provides various operators for performing operations on variables and values. Operators
are categorized as follows:

🔹 Arithmetic Operators
Operator Description Example Result
+ Addition 5+2 7

- Subtraction 5-2 3

* Multiplication 5*2 10

/ Division 5/2 2.5

// Floor Division 5 // 2 2

% Modulus 5%2 1

** Exponentiation 2 ** 3 8

🔹 Assignment Operators
Operator Description Example Meaning
= Assign x=5 x=5

+= Add and assign x += 2 x=x+2

-= Subtract and assign x -= 2 x=x-2

*= Multiply and assign x *= 2 x=x*2

/= Divide and assign x /= 2 x=x/2

//= Floor divide and assign x //= 2 x = x // 2

🔹 Comparison Operators
Operator Description Example Result
== Equal to x == 5 True/False

!= Not equal to x != 3 True/False

> Greater than x>3 True/False

< Less than x<3 True/False

>= Greater or equal x >= 3 True/False

<= Less or equal x <= 3 True/False

📘 Module 34: Control Flow and Loops 1

 🔹 Logical Operators
Operator Description Example Result
and True if both are True x > 2 and x < 10 True/False

or True if one is True x < 2 or x > 10 True/False

not Reverse the result not(x > 3) True/False

🔹 Identity Operators
Operator Description Example Result
is True if same object x is y True/False

is not True if not same obj x is not y True/False

🔹 Membership Operators
Operator Description Example Result
in Checks if value in sequence "a" in "apple" True

not in Checks if not in sequence "x" not in "box" True

📝 Commenting in Python
Single-line comment: Use #

# This is a comment

Multi-line comment: Use triple quotes (as docstring or block comment)

"""
This is a
multi-line comment
"""

🔁 2. Loops in Python
Python supports two types of loops: for and while .

🔹 For Loop
Used for iterating over a sequence (list, string, range, etc.)

📘 Module 34: Control Flow and Loops 2

 🔹 While Loop
Repeats as long as the condition is True .

🔸 Loop Control Statements

Statement Description
break Exits the loop immediately
continue Skips current iteration
pass Placeholder (does nothing)

🔄 3. List Comprehension
A concise way to create lists in a single line.

✅ You can also use conditions:

📘 Module 34: Control Flow and Loops 3

 🔍 4. in and not in
Used to check membership in sequences (lists, strings, tuples, etc.)

🔹 Examples:

✅ Commonly used in conditions and loops.

📌 Key Points to Remember
Operators are essential building blocks for any logic.

Loops reduce repetition and automate tasks.

List comprehension makes code shorter and faster.

in and not in are membership checks, useful in conditionals.

Comments are critical for making your code readable.

Python's simplicity in control flow makes it ideal for beginners and professionals alike.

📘 Module 34: Control Flow and Loops 4