Introduction to Databases & SELECT Basics

Concept

Tables, Columns, Rows, SELECT statement, WHERE filter.

🧠 Story

Restaurant Menu Analogy

You walk into a restaurant — the menu is your table.
Rows = Dishes, Columns = Properties.
You “query” it by asking:

“Show me all dishes under ₹200.”

💡 Example Data

Dish_ID Dish_Name Category Price

1 Paneer Tikka Starter 180

2 Veg Biryani Main Course 250

3 Gulab Jamun Dessert 120

4 Soup Starter 150

💻 SQL

SELECT dish_name, price

FROM menu

WHERE price < 200;

Output

DISH_NAME PRICE

Paneer Tikka 180

Gulab Jamun 120

Soup 150

💪 Practice

1. Show dish names & categories.

 2. List dishes above ₹150.

3. Sort dishes by price descending.

WHERE, AND, OR, BETWEEN, LIKE, ORDER BY

🧠 Story

Amazon Filter Analogy

You’re buying a phone → Filter by brand, price range, ratings — that’s
WHERE, BETWEEN, and ORDER BY.

💡 Example Data

Product Brand Price Rating

iPhone 14 Apple 70000 4.8

Galaxy S23 Samsung 55000 4.7

Pixel 8 Google 48000 4.5

Redmi Note Xiaomi 18000 4.2

OnePlus 12 OnePlus 45000 4.6

💻 SQL

SELECT product, brand, price

FROM mobiles

WHERE brand IN ('Samsung','OnePlus')

AND price BETWEEN 40000 AND 60000

ORDER BY rating DESC;

Output

PRODUCT BRAND PRICE

Galaxy S23 Samsung 55000

OnePlus 12 OnePlus 45000

PRODUCT BRAND PRICE

💪 Practice

1. Mobiles below ₹50,000.

2. Names containing ‘Pro’.

3. Sort by brand → price.

Practice

1. Fetch all employees in ‘Active’ status.

2. Filter employees in department ‘Finance’.

3. Show assignment numbers starting with ‘E’.

DUAL is a special table in Oracle that exists just to help you run a query
when you don’t really have any table to select data from.

💡 Concept Summary
Concept Description Example

DUAL Special one-row, one-column table SELECT 'Hello SQL

Table present in Oracle by default World' FROM dual;

Pseudo Virtual columns Oracle gives ROWNUM, SYSDATE,

Columns automatically (not stored physically) USER, LEVEL, ROWID

Imagine you have an empty Excel sheet with only one cell.
You just type a formula in A1 like =2+3.
You get 5 — no data source needed.
That one-cell sheet in Excel = the DUAL table in Oracle.

🧱 Step 1 – Using the DUAL Table

💻 SQL Example

SELECT 'Hello Oracle Learners!' AS greeting

FROM dual;

Output

GREETING

Hello Oracle
Learners!

💬 Trainer Tip:

“DUAL = Oracle’s test dummy 🧍‍♂️— always ready when you don’t have a
real table!”

🧩 Why DUAL?

Because Oracle requires a FROM clause — so if you just want to

calculate or print something, you can’t say:

SELECT 2 + 2; -- Error in Oracle

SELECT 2 + 2 FROM dual;

Output
2+
2

Think of DUAL as a dummy table — a one-row helper that allows you to

run a query “from somewhere” when there’s no real table.

🧱 Step 2 – Pseudo Columns

These are automatic columns Oracle provides for every table.

They don’t physically exist, but you can use them in queries.

🔹 1. ROWNUM

Gives a unique sequence number for rows (based on fetch order).

ID NAME SALARY

1 Ravi 5000

2 Priya 7000

3 Arjun 9000

SELECT ROWNUM, name, salary

FROM employees;

Output

ROWNUM NAME SALARY

1 Ravi 5000

2 Priya 7000

3 Arjun 9000

💬 “ROWNUM is like giving everyone a token number in the queue 🧾.”

🔹 2. ROWID

Unique physical address of each row in Oracle memory.

SELECT ROWID, name, salary FROM employees;

Output
 NAM SALAR
ROWID
E Y

AAARz9AABAAAKWq
Ravi 5000
AAA

AAARz9AABAAAKWq
Priya 7000
AAB

💬 “ROWID = GPS location of your data (handy for debugging or deletes).”

🔹 3. SYSDATE

Returns current system date & time.

SELECT SYSDATE AS today FROM dual;

Output

TODAY

11-NOV-2025
15:45:12

💬 “SYSDATE = Oracle’s wristwatch .”

🔹 4. USER

Shows the current Oracle user logged in.

SELECT USER FROM dual;

Output

USER

FUSION_APPS

💬 “USER = Oracle’s way of saying — Who are

you? ”

🔹 5. LEVEL

Used with hierarchical queries or recursion.

Example: Generate a series of numbers (1 to 5):

SELECT LEVEL AS num

FROM dual

CONNECT BY LEVEL <= 5;

Output

NU
M

🧠 Real-Life Use Cases in Oracle Fusion / BIP

Use Case SQL Example

Get current date in a BIP data model SELECT SYSDATE FROM dual

Create a static header (e.g., “BIP

`SELECT 'Report Generated on '
Report Generated On”)

SELECT * FROM employees WHERE

Limit top N rows
ROWNUM <= 10

SELECT LEVEL FROM dual CONNECT

Generate sequence of numbers
BY LEVEL <= 12

💪 Hands-On Exercises

1. Show today’s date and current user from dual.

2. Generate a list of numbers from 1–20 using LEVEL.

3. Display top 3 employees by salary using ROWNUM.

4. Print a custom message:

 5. SELECT 'Welcome ' || USER || '! Today is ' || TO_CHAR(SYSDATE, 'Day,
DD-Mon-YYYY')

6. FROM dual;

7. Fetch rowid and name for all employees — delete one row and rerun
to observe change.

DISTINCT, NULL, NVL

🧠 Story

Attendance Sheet Analogy 🧾

Some employees didn’t enter their city — you fill blanks as “Unknown”.

Student_ID Name City

1 Ravi Delhi

2 Priya Mumbai

3 Arjun NULL
Student_ID Name City

4 Sneha NULL

💻 SQL

SELECT DISTINCT NVL(city, 'Unknown') AS city

FROM students;

Output

CITY

Delhi

Mumbai

Unknow
n

💪 Practice

1. Replace nulls with “N/A”.

2. Show all unique cities.

3. Count how many from each city.

SELECT DISTINCT vendor_name

FROM ap_suppliers

WHERE creation_date BETWEEN '01-JAN-2024' AND '31-MAR-2024'

ORDER BY vendor_name;

Practice

1. Fetch unique currencies from AP_INVOICES_ALL.

2. Get all suppliers created in 2025 Q1.

 3. Find all employees whose name starts with ‘A’.

String Functions

🧠 Story

Guest List Cleanup

Some names lowercase, some uppercase — you fix before printing
badges.

Name

john doe

PRIYA
Name

SHARMA

Arjun Singh

💻 SQL

SELECT INITCAP(name) AS proper_name, LENGTH(name) AS name_length

FROM guests;

Output

PROPER_NAME NAME_LENGTH

John Doe 8

Priya Sharma 12

Arjun Singh 11

💪 Practice

1. Show names in uppercase.

2. Extract first 3 characters.

3. Names longer than 10 chars.

Number & Date Functions

🧠 Story

Anniversary Tracker 🎉
HR wants to know who completes 1 year this month.

Name Join_Date Salary

John 12-Jan-22 5000

Priya 10-Feb-22 8000

Name Join_Date Salary

Arjun 02-Mar-23 7500

💻 SQL

SELECT name,

MONTHS_BETWEEN(SYSDATE, join_date) AS months_completed,

ROUND(salary * 1.05, 0) AS revised_salary

FROM employees_details;

Output

NAME MONTHS_COMPLETED REVISED_SALARY

John 34.2 5250

Priya 33.3 8400

Arjun 20.1 7875

💪 Practice

1. Employees joined in 2023.

2. Add 10% hike to all salaries.

3. Show next appraisal date (add 365 days).

Aggregates + GROUP BY + HAVING

🧠 Story

Sales by Region 💰
You want total sales per region, only if > ₹20,000.

Region Amount

North 10000

North 35000
Region Amount

South 27000

East 15000

💻 SQL

SELECT region, SUM(amount) AS total_sales

FROM sales

GROUP BY region

HAVING SUM(amount) > 20000;

Output

REGION TOTAL_SALES

North 45000

South 27000

💪 Practice

1. Total per region.

2. Count of transactions per region.

3. Show regions with total > 25000.

Show department wise total salary

SELECT pd.department_name, SUM(paaf.salary_amount) total_salary

FROM per_all_people_f ppf, per_all_assignments_f paaf, per_departments

pd

WHERE ppf.person_id = paaf.person_id

AND paaf.department_id = pd.department_id

GROUP BY pd.department_name

HAVING SUM(paaf.salary_amount) > 100000;

🧩 Practice

Show total invoice amount by supplier (AP).

Show number of employees by business unit (HCM).

Show total PO amount by buyer (PO).

SQL JOINS

🎯 Objective:

🧠 Real-Life Story: “The Party Invitation Analogy” 🎉

Imagine two lists in Excel:

Invited List (List A) Attended List (List B)

Ravi Ravi

Sneha Priya
Invited List (List A) Attended List (List B)

Arjun Arjun

Priya —

Now let’s relate these lists to JOIN types

Join Type Real-Life Meaning SQL Concept

Only people who were invited and Matching rows in both

INNER JOIN
attended the party. tables

LEFT OUTER All people invited, even if they didn’t All from left +
JOIN attend. matches from right

RIGHT All from right +

All who attended, even if not invited.
OUTER JOIN matches from left

FULL OUTER
Everyone — invited or attended. All from both sides
JOIN

You comparing yourself with other

SELF JOIN Table joined with itself
colleagues (same table)

🧾 EMPLOYEES Table

EMP_ID EMP_NAME DEPT_ID

1 Ravi 10

2 Sneha 20

3 Arjun 30

4 Priya NULL

🧾 DEPARTMENTS Table
DEPT_ID DEPT_NAME

10 HR

20 IT

40 Finance

🧩 1. INNER JOIN (Old Style)

Meaning: Only employees that belong to existing departments.

SELECT e.emp_name, d.dept_name

FROM employees e, departments d

WHERE e.dept_id = d.dept_id;

Output

EMP_NA DEPT_NA
ME ME

Ravi HR

Sneha IT

💬 Analogy:
Only those who were invited and attended the party. 🎉

🧩 2. LEFT OUTER JOIN (Old Style)

Meaning: All employees — even if their department is missing.

Oracle syntax uses (+) on the right-side table for LEFT JOIN.

SELECT e.emp_name, d.dept_name

FROM employees e, departments d

WHERE e.dept_id = d.dept_id(+);

Output

EMP_NAME DEPT_NAME

Ravi HR
EMP_NAME DEPT_NAME

Sneha IT

Arjun NULL

Priya NULL

💬 Analogy:
All who were invited, even if they didn’t show up.

🧩 3. RIGHT OUTER JOIN (Old Style)

Meaning: All departments, even if no employee belongs to them.

Use (+) on the left-side table.

SELECT e.emp_name, d.dept_name

FROM employees e, departments d

WHERE e.dept_id(+) = d.dept_id;

Output

EMP_NAME DEPT_NAME

Ravi HR

Sneha IT

NULL Finance

💬 Analogy:
All who attended, even if they weren’t on the invite list. 😄

🧩 4. FULL OUTER JOIN

SELECT e.emp_name, d.dept_name

FROM employees e, departments d

WHERE e.dept_id = d.dept_id(+)

UNION

SELECT e.emp_name, d.dept_name

FROM employees e, departments d

WHERE e.dept_id(+) = d.dept_id;

Output
EMP_NAME DEPT_NAME

Ravi HR

Sneha IT

Arjun NULL

Priya NULL

NULL Finance

💬 “Everyone — invited or not, attended or not — is on the final photo!” 📸

🧩 5. SELF JOIN

Meaning: Comparing rows within the same table (e.g., manager and
employee).

Example Data

EMP_ID EMP_NAME MANAGER_ID

1 Ravi NULL

2 Sneha 1

3 Arjun 1

4 Priya 2

SELECT e.emp_name AS employee,

m.emp_name AS manager

FROM employees e, employees m

WHERE e.manager_id = m.emp_id;

Output

EMPLOYEE MANAGER

Sneha Ravi

Arjun Ravi

Priya Sneha

💬 “Self Join = when you talk to your manager in the same org chart.”
🧩 6. CROSS JOIN (Cartesian Product)

Meaning: Every employee matched with every department — used

rarely, usually by mistake 😅

SELECT e.emp_name, d.dept_name

FROM employees e, departments d;

Output (partial)

EMP_NAME DEPT_NAME

Ravi HR

Ravi IT

Ravi Finance

Sneha HR

💬 “Cross join = everyone invited to every party — chaos guaranteed!” 🎊

🧠 Hands-On Exercises

1️⃣ Write a query to show all employees and their departments (LEFT
OUTER JOIN).
2️⃣ Show all departments even if no employees exist (RIGHT OUTER JOIN).
3️⃣ Show only employees working in valid departments (INNER JOIN).
4️⃣ Combine LEFT and RIGHT to simulate FULL JOIN.
5️⃣ Perform a SELF JOIN to show employee–manager pairs.
6️⃣ Try a CROSS JOIN and count total rows (COUNT(*)).

-- LEFT OUTER JOIN Example

SELECT ppf.full_name, pd.department_name

FROM per_all_people_f ppf, per_departments pd

WHERE ppf.department_id = pd.department_id(+)

AND SYSDATE BETWEEN ppf.effective_start_date AND
ppf.effective_end_date;

-- RIGHT OUTER JOIN Example (PO)

SELECT pha.po_number, pov.vendor_name

FROM po_headers_all pha, po_vendors pov

WHERE pha.vendor_id(+) = pov.vendor_id;

Practice

Fetch employees and their departments (LEFT JOIN).

Fetch all vendors even if they don’t have POs (RIGHT JOIN).

Join AP_INVOICES_ALL with AP_SUPPLIERS to show supplier name and

invoice amount.

Subqueries & EXISTS

🧠 Story

Above Average Performers 🏆

Name Salary

Ravi 4000

Priya 8000

Arjun 7000

Sneha 9000

💻 SQL

SELECT name, salary

FROM employee_salary

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

Output

NAME SALARY

Priya 8000

Sneha 9000

💬 “Subquery = like referencing another data into one!”

💪 Practice

1. Salary > avg salary.

2. Dept with salary > avg dept salary.

3. EXISTS → Show only depts with employees

CASE / DECODE

🧠 Story

Employee Performance Bonus 🎯

Name Score

Ravi 90

Priya 75

Arjun 50

💻 SQL

SELECT name, score,

CASE

WHEN score >= 80 THEN 'Excellent'

WHEN score >= 60 THEN 'Good'

 ELSE 'Needs Improvement'

END AS performance

FROM emp_performance;

Output

NAME SCORE PERFORMANCE

Ravi 90 Excellent

Priya 75 Good

Arjun 50 Needs Improvement

Analytical (Window) Functions

“The magic tricks of SQL — see all rows but calculate smartly!

🧠 Real-Life Story: “Sports Tournament Ranking” 🏏

Imagine an IPL leaderboard table:

Team Matches Points

CSK 14 18

MI 14 14

RCB 14 16

KKR 14 12

GT 14 20

You want to rank teams by points — that’s RANK() and DENSE_RANK().

💻 SQL Example #1 – RANK()

SELECT team, points,

RANK() OVER (ORDER BY points DESC) AS rank_position

FROM ipl_points;

Output

TEAM POINTS RANK_POSITION

GT 20 1

CSK 18 2

RCB 16 3

MI 14 4

KKR 12 5

💻 SQL Example #2 – DENSE_RANK()

SELECT team, points,

DENSE_RANK() OVER (ORDER BY points DESC) AS dense_rank

FROM ipl_points;

If two teams have the same points, DENSE_RANK skips no numbers.

💬 “DENSE_RANK = no gaps, like strict parents counting kids’ scores.” 😄

💻 SQL Example #3 – ROW_NUMBER()

SELECT team, points,

ROW_NUMBER() OVER (ORDER BY points DESC) AS serial_no

FROM ipl_points;

💬 “ROW_NUMBER = Just give me a serial number, I’m not into ranking

politics!” 😆

💻 SQL Example #4 – SUM() OVER (PARTITION BY)

Use case: Calculate running totals or totals by group.

Employee Dept Salary

Ravi HR 5000

Priya HR 7000

Arjun IT 8000

Sneha IT 9000

SELECT dept, employee, salary,

SUM(salary) OVER (PARTITION BY dept) AS total_by_dept,

ROUND(100 * salary / SUM(salary) OVER (PARTITION BY dept), 1) AS

pct_share

FROM employees;

Output

DEPT EMPLOYEE SALARY TOTAL_BY_DEPT PCT_SHARE

HR Ravi 5000 12000 41.7

HR Priya 7000 12000 58.3

IT Arjun 8000 17000 47.1

IT Sneha 9000 17000 52.9

💬 “Partition By = group inside a group — like family salary totals.” 👨‍👩‍👧‍👦

💻 SQL Example #5 – LAG() / LEAD()

Use case: Compare a row with its previous or next row (time series trend).

Month Sales

Jan 10000

Feb 12000

Mar 9000

Apr 15000
SELECT month, sales,

LAG(sales) OVER (ORDER BY month) AS prev_month_sales,

LEAD(sales) OVER (ORDER BY month) AS next_month_sales,

sales - LAG(sales) OVER (ORDER BY month) AS sales_diff

FROM monthly_sales;

Output

MONT SALE PREV_MONTH_SA NEXT_MONTH_SA SALES_DI

H S LES LES FF

1000
Jan NULL 12000 NULL
0

1200
Feb 10000 9000 2000
0

Mar 9000 12000 15000 -3000

1500
Apr 9000 NULL 6000
0

💬 “LAG = memory of last month; LEAD = peek into the future 🔮”

💪 Hands-On Practice

1. Rank employees by salary in each department.

2. Show each employee’s previous salary using LAG().

3. Calculate each employee’s % of department total.

4. Find cumulative revenue per region using SUM() OVER (ORDER BY

…)
🧱 DAY 11 – WITH Clause (Common Table Expressions)

“Your SQL deserves better structure — WITH = temporary mini-tables!”

🧠 Real-Life Story: “Pizza Preparation Line” 🍕

Think of making pizza:

 Step 1: Prepare dough

 Step 2: Add toppings

 Step 3: Bake

Each step depends on the previous one — that’s how WITH clauses work
in SQL!

💡 Excel-Like Data: ORDERS

Order_ID Customer Amount Region

101 Ravi 1000 North

102 Priya 1200 North

103 Arjun 800 South

104 Sneha 1500 South

105 John 900 East

Order_ID Customer Amount Region

💻 SQL Example #1 – Simple WITH

WITH region_totals AS (

SELECT region, SUM(amount) AS total_amount

FROM orders

GROUP BY region

SELECT region, total_amount

FROM region_totals

WHERE total_amount > 1500;

Output

REGION TOTAL_AMOUNT

North 2200

South 2300

💬 “WITH = staging area — build first, use later!” 🎯

💻 SQL Example #2 – WITH + Analytical

WITH sales_ranked AS (

SELECT region, customer, amount,

RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS

rank_in_region

FROM orders

SELECT * FROM sales_ranked WHERE rank_in_region <= 2;

Output

REGION CUSTOMER AMOUNT RANK_IN_REGION

North Priya 1200 1

REGION CUSTOMER AMOUNT RANK_IN_REGION

North Ravi 1000 2

South Sneha 1500 1

South Arjun 800 2

💬 “WITH clause = Excel helper sheet before final pivot table.” 😎

💻 SQL Example #3 – Recursive WITH (if needed)

To explain hierarchies (e.g., org chart):

WITH org_hierarchy (emp_id, emp_name, manager_id, level_no) AS (

SELECT emp_id, emp_name, manager_id, 1

FROM employees

WHERE manager_id IS NULL

UNION ALL

SELECT e.emp_id, e.emp_name, e.manager_id, o.level_no + 1

FROM employees e

org_hierarchy o

WHERE e.manager_id = o.emp_id

SELECT * FROM org_hierarchy;