Data Analytics

Data Analytics

Definition:
Data Analytics is the process of collecting, cleaning, analyzing, and interpreting data to find useful
information, make decisions, and solve problems.

How it works (Steps):

1. Data Collection – Gathering data from different sources (databases, surveys, social media,
IoT devices).

2. Data Cleaning – Removing errors, duplicates, or missing values to make data accurate.

3. Data Analysis – Applying statistical methods, tools, or algorithms to find patterns and trends.

4. Data Visualization – Representing data using charts, graphs, or dashboards (e.g., in Power BI,
Tableau).

5. Decision-Making – Using insights to take action (business, healthcare, finance, sports, etc.).

Types of Data Analytics:

1. Descriptive Analytics – What happened? (Example: Sales dropped 10% last month).

2. Diagnostic Analytics – Why did it happen? (Example: Sales dropped because ad budget was
reduced).

3. Predictive Analytics – What might happen in the future? (Example: Predicting which
customers are likely to leave).

4. Prescriptive Analytics – What should we do about it? (Example: Increase discounts to retain
customers).

Real-Life Examples:

 Business: Amazon analyzes customer purchase history to recommend products.

 Healthcare: Hospitals analyze patient data to predict disease risks.

 Finance: Banks analyze transactions to detect fraud.

 Sports: Teams use data to track player performance and strategy.

👉 In short: Data Analytics = Turning raw data into smart decisions.

1. Microsoft Power BI

 What it is: A business intelligence (BI) and data visualization tool by Microsoft.

 Use: Helps you take raw data (from Excel, SQL, Google Analytics, etc.) and turn it into
dashboards, reports, and interactive charts.

 Best for: Businesses and analysts who want to make decisions using visual insights and
trends.

 Example: A company can use Power BI to track daily sales, profits, and customer behavior in
a single dashboard.

2. Oracle 10g

 What it is: A version of Oracle’s Relational Database Management System (RDBMS). The “g”
stands for Grid computing.

 Use: Stores and manages structured data (tables, rows, columns).

 Features in 10g: Automatic Storage Management, Data Pump (for fast import/export), and
better performance.

 Example: Banks use Oracle databases to store customer details, transactions, and loan
records securely.

3. MongoDB

 What it is: A NoSQL database that stores data in JSON-like documents instead of tables.

 Use: Best for handling unstructured or semi-structured data (like product catalogs, user
profiles, logs).

 Why special: It’s flexible, scalable, and works well with big data and modern web apps.

 Example: An e-commerce app can use MongoDB to store product details, user reviews, and
shopping carts.

4. MongoSH (Mongo Shell)

 What it is: A command-line interface (CLI) to interact with MongoDB.

 Use: You can run queries, insert data, update records, and manage databases directly from
the terminal.

 Example: You type commands like:

js

use shop

db.products.find()
This switches to a database called “shop” and shows all products stored inside.

5. VS Code (Visual Studio Code)

 What it is: A lightweight but powerful code editor by Microsoft.

 Use: For writing, debugging, and testing code in many languages (Python, JavaScript, C++,
etc.).

 Features: Extensions, GitHub integration, debugging tools, syntax highlighting.

 Example: A web developer writes HTML, CSS, and JavaScript in VS Code with live preview.

6. Notepad++

 What it is: A free, open-source text and code editor for Windows.

 Use: Good for editing text files, source code, and scripts.

 Why special: It’s lighter than VS Code, supports many languages, and is often used for quick
edits.

 Example: A programmer uses Notepad++ to quickly edit a .html file without opening heavy
IDEs.

7. SQL*Plus

 What it is: A command-line tool that comes with Oracle Database.

 Use: To run SQL (Structured Query Language) and PL/SQL commands directly on the Oracle
database.

 Example: You log in to the database using:

bash

sqlplus username/password

Then you can run queries like SELECT * FROM employees;

8. SQL*Loader

 What it is: A utility in Oracle used for bulk data loading.

 Use: Helps import large amounts of data from text/CSV files into Oracle database tables.

 Example: A bank can use SQL*Loader to import millions of customer transaction records
from flat files into Oracle in one go.
9. PL/SQL (Procedural Language/SQL)

 What it is: Oracle’s programming extension to SQL.

 Use: Allows you to write procedures, functions, loops, conditions, and triggers inside SQL.

 Why: SQL alone can only query data, but PL/SQL adds logic and automation.

 Example: A PL/SQL block to give 10% bonus to all employees:

plsql

BEGIN

UPDATE employees SET salary = salary * 1.10;

END;

10. STLC (Software Testing Life Cycle)

 What it is: A process followed in software testing.

 Use: Defines the steps testers follow to ensure software is bug-free and meets requirements.

 Phases include: Requirement analysis → Test planning → Test case design → Test execution
→ Defect reporting → Test closure.

 Example: Before launching a mobile app, testers follow STLC to check login, payment, and
security features.

11. SDLC (Software Development Life Cycle)

 What it is: A step-by-step process for developing software.

 Use: Ensures software is built systematically and delivered successfully.

 Phases include: Requirement gathering → Design → Development → Testing → Deployment

→ Maintenance.

 Example: Building a food delivery app like Swiggy involves following SDLC to plan, design,
code, test, and release.

12. Atlassian JIRA

 What it is: A popular project management and issue-tracking tool.

 Use: Teams use it to manage software development tasks, track bugs, assign work, and
follow Agile/Scrum methods.

 Features: Kanban boards, sprint planning, bug tracking, reports.

 Example: A digital marketing team can use JIRA to track ad campaign tasks, assign deadlines,
and monitor progress.
1. RDBMS (Relational Database Management System)

 What it is: A database system that stores data in tables (rows & columns) and manages the
relationships between them.

 How data is stored: In structured format (tables).

 Query Language: SQL (Structured Query Language).

 Examples: Oracle, MySQL, SQL Server, PostgreSQL.

 Use case: Banking, ERP systems, HR management – where data is structured and
relationships matter.

Example:
You have a database for a school:

 Students table → RollNo, Name, Class.

 Marks table → RollNo, Subject, Score.

The RollNo connects both tables → relational data.

2. NRDBMS (Non-Relational Database Management System)

 What it is: A database system that stores data in formats other than tables (documents, key-
value pairs, graphs, wide-columns).

 How data is stored: Flexible format (JSON, key-value, graph, etc.).

 Query Language: Not always SQL (uses APIs, special query languages).

 Examples: MongoDB, Cassandra, CouchDB, Neo4j.

 Use case: Social media, e-commerce, big data, IoT – where data is unstructured or semi-
structured.

Example:
In MongoDB, a student record can look like this (document-based):

"RollNo": 101,

"Name": "Arun",

"Class": "10",

"Marks": [

{"Subject": "Math", "Score": 90},

{"Subject": "Science", "Score": 85}

}
Here, all details are inside one document (no need for multiple tables).

Key Differences (Quick Table):

Feature RDBMS (Relational) NRDBMS (Non-Relational)

Data Format Tables (rows/cols) Documents, key-value, graph, wide-column

Schema Fixed (structured) Flexible (dynamic)

Query Language SQL NoSQL / API

Scalability Vertical (scale-up) Horizontal (scale-out, big data friendly)

Best For Structured data, transactions Unstructured/semi-structured data, big scale apps

In short:

 RDBMS = Structured, table-based, SQL.

 NRDBMS = Flexible, document/graph-based, NoSQL.

Companies Using RDBMS (Relational Databases)

These companies need structured, highly consistent, transaction-heavy systems.

1. Banks (HDFC, SBI, Citibank, etc.)

o Use Oracle Database / SQL Server

o Why? For customer accounts, transactions, loan records where accuracy is critical.

2. Airlines (IndiGo, Emirates, Delta)

o Use MySQL / Oracle

o Why? To manage ticket booking, passenger info, schedules (structured data with
relations).

3. Telecoms (Airtel, Jio, Vodafone Idea)

o Use PostgreSQL / Oracle

o Why? For customer billing, recharge records, call logs.

 Companies Using NRDBMS (Non-Relational Databases)
These companies need flexible, large-scale, fast systems that handle unstructured/semi-structured
data.

1. Amazon

o Uses DynamoDB (key-value NoSQL) and MongoDB

o Why? For product catalogs, shopping carts, customer reviews (data is huge &
dynamic).

2. Netflix

o Uses Cassandra (wide-column NoSQL)

o Why? To store millions of viewing histories and recommendations globally, in real-

time.

3. Facebook / Instagram

o Use HBase, Cassandra, MongoDB

o Why? For social media posts, likes, comments, friend graphs (massive unstructured
data).

4. Google

o Uses Bigtable (NoSQL) and Spanner (SQL + NoSQL hybrid)

o Why? For search indexing, YouTube videos, Gmail storage.

5. Uber

o Uses MongoDB & Cassandra

o Why? For trip details, driver-rider matching, location data.

Quick Summary:

 RDBMS → Used by banks, airlines, telecoms → where data is structured, needs high
accuracy.

 NRDBMS → Used by tech giants like Amazon, Netflix, Facebook, Uber → where data is
huge, fast, flexible.
Careers Needing RDBMS Skills (Relational Databases like Oracle, MySQL, SQL Server, PostgreSQL)

These roles need structured data, SQL queries, transactions, reporting:

1. Database Administrator (DBA)

o Manages Oracle/MySQL databases.

o Ensures backup, security, and performance.

2. Data Analyst / Business Analyst

o Uses SQL in RDBMS to pull structured data for analysis and reports.

o Example: Pulling monthly sales reports.

3. ERP Specialist (SAP, Oracle Apps)

o Works with databases storing company-wide financials, HR, and operations.

4. Banking/Finance IT Professionals

o Handles transaction-heavy databases like Oracle/SQL Server.

5. Software Developer (Enterprise Apps)

o Builds apps (Java, .NET, PHP) connected to RDBMS.

Careers Needing NRDBMS Skills (Non-Relational Databases like MongoDB, Cassandra, DynamoDB,
Neo4j)

These roles deal with big data, real-time systems, flexible structures:

1. Big Data Engineer

o Works with HBase, Cassandra, MongoDB.

o Handles billions of records (social media, e-commerce logs).

2. Data Scientist / Machine Learning Engineer

o Needs NoSQL for unstructured/semi-structured data like images, reviews, IoT sensor
data.

3. Full Stack Developer (Modern Web Apps)

o Uses MongoDB (MEAN/MERN stack) with Node.js, React, Angular.

o Example: Building scalable apps like e-commerce or chat apps.

4. Cloud Engineer

o Works with AWS DynamoDB, Google Bigtable, Azure Cosmos DB.

o Companies prefer cloud NoSQL for massive scalability.

 5. Product-Based Tech Companies

o At firms like Netflix, Uber, Amazon, Swiggy you’ll need NoSQL for
recommendations, real-time tracking, product catalogs.

Quick Career Map

Skillset Best For Common Tools Example Jobs

RDBMS (SQL, Structured, Oracle, MySQL, SQL DBA, Data Analyst, ERP Specialist,
Oracle, MySQL) transactional systems Server, PostgreSQL Banking IT, Enterprise Developer

NRDBMS (NoSQL, Unstructured, MongoDB, Big Data Engineer, Data Scientist,

MongoDB, scalable, real-time Cassandra, Full Stack Developer, Cloud
Cassandra) systems DynamoDB, Neo4j Engineer, Product Tech Devs

👉 In short:

 If you want to work in Banks, Telecom, ERP, Analytics → RDBMS (SQL, Oracle, MySQL) is
must.

 If you want to work in Big Tech, Startups, AI/ML, Cloud → NRDBMS (MongoDB, Cassandra,
DynamoDB) gives you the edge.
 SQL Concepts Explained

1. Basic SELECT Statement

 SELECT is used to fetch data from a database table.
 Syntax:
SELECT column1, column2 FROM table_name;
Examples:
SELECT * FROM EMPLOYEES;
-- Fetches all data (all columns and rows) from employees.
SELECT FIRST_NAME, SALARY FROM EMPLOYEES;
-- Fetches only the columns "FIRST_NAME" and "SALARY".
SELECT FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90;
-- Fetches data only from employees in Department 90.

2. Concatenation (|| Operator)

 Used to join text and column values.
 Example:
 SELECT FIRST_NAME || ' is getting salary Rs. ' || SALARY AS DATA
 FROM EMPLOYEES;
👉 Output will look like:
Steven is getting salary Rs. 24000
3. Filtering with WHERE
 WHERE lets you filter rows based on a condition.
Relational operators:
 = → Equal
 < → Less than
 > → Greater than
 <= → Less than or equal
 >= → Greater than or equal
 != or <> → Not equal
Examples:
SELECT * FROM EMPLOYEES WHERE SALARY = 5000;
SELECT * FROM EMPLOYEES WHERE SALARY <= 5000;
SELECT * FROM EMPLOYEES WHERE SALARY > 5000;
4. Sorting Results
 ORDER BY is used to sort results.
 ASC = Ascending (default), DESC = Descending
Example:
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
ORDER BY SALARY DESC;
5. Special Operators
a) IN / NOT IN
 Allows checking for multiple values.
Example:
SELECT FIRST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (30, 60, 90);
👉 Returns employees only from departments 30, 60, and 90.
WHERE DEPARTMENT_ID NOT IN (30, 60, 90);
-- Returns employees from all other departments.
b) BETWEEN / NOT BETWEEN
 Used for ranges.
Example:
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY BETWEEN 10000 AND 20000;
👉 Salary must be within the range (including 10000 & 20000).
WHERE SALARY NOT BETWEEN 10000 AND 20000;
-- Salaries outside the range.
c) NULL / NOT NULL
 NULL means no value stored.
Example:
SELECT * FROM EMPLOYEES WHERE COMMISSION_PCT IS NULL;
SELECT * FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL;
6. LIKE Operator (Pattern Matching)
 _ matches one character.
 % matches multiple characters.
Examples:
WHERE FIRST_NAME LIKE 'J_hn'; -- John
WHERE FIRST_NAME LIKE 'J%n'; -- John, Jonathan, etc.
WHERE FIRST_NAME NOT LIKE 'J%n'; -- Excludes names starting with J and
ending with n
7. String Functions
These modify or check text.
 UPPER() → Converts to uppercase.
SELECT UPPER(FIRST_NAME) FROM EMPLOYEES;
 LOWER() → Converts to lowercase.
SELECT LOWER(FIRST_NAME) FROM EMPLOYEES;
 INITCAP() → Capitalizes first letter of each word.
SELECT INITCAP(FIRST_NAME) FROM EMPLOYEES;
 LENGTH() → Returns string length.
SELECT LENGTH(FIRST_NAME) FROM EMPLOYEES;
 CONCAT() → Joins two strings.
SELECT CONCAT(FIRST_NAME, LAST_NAME) FROM EMPLOYEES;
 REVERSE() → Reverses string.
SELECT REVERSE(FIRST_NAME) FROM EMPLOYEES;
 SUBSTR(str, start, length) → Extracts part of a string.
SELECT SUBSTR(FIRST_NAME, 1, 3) FROM EMPLOYEES; -- STE from STEVEN
 INSTR(str, search_char) → Finds position of character.
SELECT INSTR(FIRST_NAME, 'E') FROM EMPLOYEES;
8. Padding Functions
Used to add characters.
 LPAD(value, total_length, char) → Pads left.
SELECT LPAD(SALARY, 8, 'X') FROM EMPLOYEES;
-- e.g., XXX4900
 RPAD(value, total_length, char) → Pads right.
SELECT RPAD(SALARY, 8, 'X') FROM EMPLOYEES;
-- e.g., 24000XX
9. TRIM
 Removes spaces.
SELECT TRIM(' Praveen ') FROM EMPLOYEES;

10. REPLACE & TRANSLATE

 REPLACE(string, search, replace) → Replaces text.
SELECT REPLACE('WELCOME TO COME', 'C', '12') FROM DUAL;
-- WEL12OME TO 12OME
 TRANSLATE(string, from_chars, to_chars) → Character-based
replacement.
SELECT TRANSLATE('WELCOME TO COME', 'C', '12') FROM DUAL;
-- WEL12ME TO 12ME
11. Number Functions
 ROUND(num) → Rounds number.
 CEIL(num) → Next highest integer.
 FLOOR(num) → Next lowest integer.
 POWER(x,y) → x^y.
 MOD(x,y) → Remainder of division.
 SIGN(num) → Returns -1, 0, or 1.
 ABS(num) → Absolute (positive) value.
Examples:
SELECT ROUND(364.751) FROM DUAL; -- 365
SELECT CEIL(465.351) FROM DUAL; -- 466
SELECT FLOOR(465.156) FROM DUAL; -- 465
SELECT POWER(2,3) FROM DUAL; -- 8
SELECT MOD(10,3) FROM DUAL; -- 1
SELECT SIGN(-28) FROM DUAL; -- -1
SELECT ABS(-557) FROM DUAL; -- 557
12. Date Functions
 MONTHS_BETWEEN(date1, date2) → Difference in months.
SELECT MONTHS_BETWEEN(SYSDATE, '07-SEP-2026') FROM DUAL;
 ADD_MONTHS(date, n) → Add n months.
SELECT ADD_MONTHS(SYSDATE, 12) FROM DUAL;
 NEXT_DAY(date, weekday) → Finds next given weekday.
SELECT NEXT_DAY(SYSDATE, 'FRIDAY') FROM DUAL;
13. Keywords
 DISTINCT → Removes duplicates.
SELECT DISTINCT FIRST_NAME FROM EMPLOYEES;

Advanced SQL Concepts

1. Aggregate Functions
Aggregate functions perform calculations on a set of rows and return a single
value.
 COUNT() → Number of rows.
 SUM() → Total sum.
 AVG() → Average.
 MAX() → Maximum value.
 MIN() → Minimum value.
Example:
SELECT COUNT(*) FROM EMPLOYEES;
-- Total number of employees

SELECT AVG(SALARY) FROM EMPLOYEES;

-- Average salary of all employees

SELECT MAX(SALARY), MIN(SALARY) FROM EMPLOYEES;

-- Highest and lowest salary
2. GROUP BY and HAVING
Used to group rows and apply aggregate functions on groups.
 GROUP BY → groups data.
 HAVING → filters groups (similar to WHERE but for groups).
Example:
SELECT DEPARTMENT_ID, AVG(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;
-- Average salary per department
SELECT DEPARTMENT_ID, COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(*) > 5;
-- Only departments with more than 5 employees
3. JOINS
Joins are used to combine rows from multiple tables based on related columns.
a) INNER JOIN
Fetches only matching records from both tables.
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E
INNER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
b) LEFT JOIN (or LEFT OUTER JOIN)
Fetches all rows from left table + matching rows from right.
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E
LEFT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
👉 Employees without departments will still appear, but with NULL for
department.
c) RIGHT JOIN
Opposite of LEFT JOIN.
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E
RIGHT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
👉 All departments are shown, even if they have no employees.
d) FULL JOIN (or FULL OUTER JOIN)
Combines results of LEFT and RIGHT JOIN.
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E
FULL JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
e) SELF JOIN
Table joins with itself.
Example: Find employees and their managers:
SELECT E.FIRST_NAME AS EMPLOYEE, M.FIRST_NAME AS MANAGER
FROM EMPLOYEES E
INNER JOIN EMPLOYEES M
ON E.MANAGER_ID = M.EMPLOYEE_ID;
f) CROSS JOIN
Cartesian product (all rows from both tables).
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E
CROSS JOIN DEPARTMENTS D;
👉 If Employees = 100, Departments = 10 → Output = 1000 rows.
4. Subqueries & Nested Queries
A subquery is a query inside another query.
a) Single-row subquery
Returns only one value.
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES);
-- Employees earning more than average salary
b) Multi-row subquery
Returns multiple values (use IN, ANY, ALL).
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM DEPARTMENTS
WHERE LOCATION_ID = 1700);
c) Correlated Subquery
Subquery depends on outer query.
SELECT E1.FIRST_NAME, E1.SALARY
FROM EMPLOYEES E1
WHERE SALARY > (SELECT AVG(E2.SALARY)
FROM EMPLOYEES E2
WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID);
-- Employees earning above their department’s average
5. Constraints
Constraints enforce rules on table data.
a) Primary Key (PK)
 Uniquely identifies each row.
 Only one PK per table.
CREATE TABLE DEPARTMENTS (
DEPARTMENT_ID INT PRIMARY KEY,
DEPARTMENT_NAME VARCHAR(50)
);
b) Foreign Key (FK)
 Creates a relationship between two tables.
 Ensures value exists in parent table.
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID INT PRIMARY KEY,
FIRST_NAME VARCHAR(50),
DEPARTMENT_ID INT,
CONSTRAINT FK_DEPT FOREIGN KEY (DEPARTMENT_ID)
REFERENCES DEPARTMENTS(DEPARTMENT_ID)
);

c) UNIQUE
 Ensures all values in a column are different.
CREATE TABLE USERS (
USER_ID INT PRIMARY KEY,
EMAIL VARCHAR(100) UNIQUE
);
d) CHECK
 Restricts values to a condition.
CREATE TABLE PRODUCTS (
PRODUCT_ID INT PRIMARY KEY,
PRICE DECIMAL(10,2) CHECK (PRICE > 0)
);
e) NOT NULL
 Column cannot have NULL values.
CREATE TABLE STUDENTS (
ROLL_NO INT PRIMARY KEY,
NAME VARCHAR(50) NOT NULL
);
 SQL Complete Guide
This document contains SQL notes, detailed explanations, and advanced
concepts. It covers everything from basic SELECT statements to joins,
subqueries, and constraints.

1. Basic SELECT Statements

The SELECT statement is used to fetch data from a database table.
SELECT * FROM EMPLOYEES;
-- Fetch all columns and rows

SELECT FIRST_NAME, SALARY FROM EMPLOYEES;

-- Fetch only first name and salary

SELECT FIRST_NAME, SALARY, DEPARTMENT_ID

FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90;
-- Only employees in department 90

2. Concatenation (|| Operator)

The concatenation operator joins strings and column values.
SELECT FIRST_NAME || ' is getting salary Rs. ' || SALARY AS DATA
FROM EMPLOYEES;
3. Filtering with WHERE
WHERE filters rows based on conditions.
Relational operators:
=, >, <, >=, <=, !=
SELECT * FROM EMPLOYEES WHERE SALARY = 5000;
SELECT * FROM EMPLOYEES WHERE SALARY <= 5000;
SELECT * FROM EMPLOYEES WHERE SALARY > 5000;

4. Sorting (ORDER BY)

Sort results in ascending (ASC) or descending (DESC) order.
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
ORDER BY SALARY DESC;

5. Special Operators
IN / NOT IN
SELECT FIRST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (30, 60, 90);

SELECT FIRST_NAME, DEPARTMENT_ID

FROM EMPLOYEES
WHERE DEPARTMENT_ID NOT IN (30, 60, 90);
BETWEEN / NOT BETWEEN
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY BETWEEN 10000 AND 20000;

SELECT FIRST_NAME, SALARY

FROM EMPLOYEES
WHERE SALARY NOT BETWEEN 10000 AND 20000;
NULL / NOT NULL
SELECT * FROM EMPLOYEES WHERE COMMISSION_PCT IS NULL;
SELECT * FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL;

6. LIKE Operator (Pattern Matching)

 _ → one character
 % → multiple characters
SELECT FIRST_NAME FROM EMPLOYEES WHERE FIRST_NAME LIKE 'J_hn'; --
John
SELECT FIRST_NAME FROM EMPLOYEES WHERE FIRST_NAME LIKE 'J%n'; --
John, Jonathan
SELECT FIRST_NAME FROM EMPLOYEES WHERE FIRST_NAME NOT LIKE 'J%n';

7. String Functions
SELECT UPPER(FIRST_NAME), LOWER(FIRST_NAME), INITCAP(FIRST_NAME)
FROM EMPLOYEES;
SELECT LENGTH(FIRST_NAME), CONCAT(FIRST_NAME, LAST_NAME) FROM
EMPLOYEES;
SELECT REVERSE(FIRST_NAME), SUBSTR(FIRST_NAME, 1, 3),
INSTR(FIRST_NAME, 'E') FROM EMPLOYEES;
8. Padding & Replace Functions
SELECT LPAD(SALARY, 8, 'X'), RPAD(SALARY, 8, 'X') FROM EMPLOYEES;
SELECT TRIM(' Praveen ') FROM DUAL;
SELECT REPLACE('WELCOME TO COME','C','12') FROM DUAL;
SELECT TRANSLATE('WELCOME TO COME','C','12') FROM DUAL;

9. Number Functions
SELECT ROUND(364.751), CEIL(465.351), FLOOR(465.156) FROM DUAL;
SELECT POWER(2,3), MOD(10,3), SIGN(-28), ABS(-557) FROM DUAL;

10. Date Functions

SELECT MONTHS_BETWEEN(SYSDATE, '07-SEP-2026') FROM DUAL;
SELECT ADD_MONTHS(SYSDATE, 12) FROM DUAL;
SELECT NEXT_DAY(SYSDATE, 'FRIDAY') FROM DUAL;

11. Aggregate Functions

SELECT COUNT(*), AVG(SALARY), MAX(SALARY), MIN(SALARY)
FROM EMPLOYEES;

12. GROUP BY and HAVING

SELECT DEPARTMENT_ID, AVG(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;

SELECT DEPARTMENT_ID, COUNT(*)

FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(*) > 5;

13. Joins
INNER JOIN
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E
INNER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
LEFT JOIN
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E
LEFT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
RIGHT JOIN
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E
RIGHT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
FULL JOIN
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E
FULL JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
SELF JOIN
SELECT E1.FIRST_NAME AS EMPLOYEE, E2.FIRST_NAME AS MANAGER
FROM EMPLOYEES E1
JOIN EMPLOYEES E2
ON E1.MANAGER_ID = E2.EMPLOYEE_ID;
CROSS JOIN
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E
CROSS JOIN DEPARTMENTS D;

14. Subqueries
Single-row Subquery
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES);
Multi-row Subquery
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (
SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE LOCATION_ID = 1700
);
Correlated Subquery
SELECT E1.FIRST_NAME, E1.SALARY
FROM EMPLOYEES E1
WHERE SALARY > (
SELECT AVG(E2.SALARY)
FROM EMPLOYEES E2
WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID
);

15. Constraints
Primary Key
CREATE TABLE DEPARTMENTS (
DEPARTMENT_ID INT PRIMARY KEY,
DEPARTMENT_NAME VARCHAR(50)
);
Foreign Key
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID INT PRIMARY KEY,
FIRST_NAME VARCHAR(50),
DEPARTMENT_ID INT,
CONSTRAINT FK_DEPT FOREIGN KEY (DEPARTMENT_ID)
REFERENCES DEPARTMENTS(DEPARTMENT_ID)
);
UNIQUE
CREATE TABLE USERS (
USER_ID INT PRIMARY KEY,
EMAIL VARCHAR(100) UNIQUE
);
CHECK
CREATE TABLE PRODUCTS (
PRODUCT_ID INT PRIMARY KEY,
PRICE DECIMAL(10,2) CHECK (PRICE > 0)
);

NOT NULL
CREATE TABLE STUDENTS (
ROLL_NO INT PRIMARY KEY,
NAME VARCHAR(50) NOT NULL
);

SQL Advanced Notes Explanation

 Day 6

1. Pseudo Columns
Pseudo columns are not actual table columns. They are provided by
Oracle (and some other databases) as special read-only values.
Common Pseudo Columns:
 USER → shows the current Oracle username.
 SELECT USER FROM DUAL;
-- Example Output: HR
 UID → numeric ID assigned to the current user session.
 SELECT UID FROM DUAL;
 -- Example Output: 123
 SYSDATE → current system date (without time).
 SELECT SYSDATE FROM DUAL;
 -- Example: 14-SEP-25
 SYSTIMESTAMP → current system date & time with fractional
seconds and timezone.
 SELECT SYSTIMESTAMP FROM DUAL;
 -- Example: 14-SEP-25 14:21:56.123 +05:30
 ROWNUM → assigns a sequential number (starting from 1) to
rows when retrieved.
 SELECT ROWNUM, S.* FROM STUD S;
 ROWID → unique physical address of a row in a table.
 SELECT ROWID, S.* FROM STUD S;
 NEXTVAL → gives the next value of a sequence.
  CURRVAL → gives the current value of a sequence.
👉 Example:
SELECT SEQ_NAME.NEXTVAL FROM DUAL; -- Next number
SELECT SEQ_NAME.CURRVAL FROM DUAL; -- Current number
Filtering using ROWNUM
SELECT ROWNUM AS "RNUM", C.*
FROM CLG C
WHERE ROWNUM <= 5;
-- Returns first 5 rows only

2. Database Schemas
Schemas are the collection of database objects (structures).
Main objects:
 Table – stores data.
 Sequence – generates numbers automatically.
 Synonym – alternate name for a table/view/sequence.
 Index – improves performance of queries.
 View – virtual table (saved query).

3. SEQUENCE
A sequence is used to generate unique numbers automatically (often
for primary keys).
Example:
CREATE TABLE IMP (
ID NUMBER,
 NAME VARCHAR2(30)
);

CREATE SEQUENCE IMP_SEQ

START WITH 1
MINVALUE 1
MAXVALUE 100
INCREMENT BY 1
NOCYCLE
CACHE 20;
👉 Explanation:
 START WITH 1 → first value will be 1.
 INCREMENT BY 1 → increase by 1 every time.
 MAXVALUE 100 → sequence cannot exceed 100.
 NOCYCLE → will not restart after reaching max.
 CACHE 20 → pre-allocates 20 numbers in memory for faster
performance.
Using the sequence
INSERT INTO IMP VALUES (IMP_SEQ.NEXTVAL, 'RAJ');
INSERT INTO IMP VALUES (IMP_SEQ.NEXTVAL, 'RAM KUMAR');
INSERT INTO IMP VALUES (IMP_SEQ.NEXTVAL, 'HARISH');

SELECT * FROM IMP;

SELECT IMP_SEQ.CURRVAL FROM DUAL; -- Last generated number
Dropping a sequence:
DROP SEQUENCE IMP_SEQ;

4. SYNONYM
A synonym provides an alternate name for a table, view, sequence,
or other object.
 Useful for short names or when sharing objects between
schemas.
Example:
CREATE SYNONYM IMPORT FOR IMP;

SELECT * FROM IMP;

SELECT * FROM IMPORT;

DROP SYNONYM IMPORT;

5. INDEX
 Indexes improve performance by making searches faster
(similar to a book index).
 Automatic index → created on PRIMARY KEY and UNIQUE
columns.
 Manual index → you can create it on frequently searched
columns.
Example:
CREATE INDEX NM_IND
ON IMP(NAME);

SELECT NAME FROM IMP;

DROP INDEX NM_IND;

👉 Without index → full table scan.
👉 With index → quick lookup using the index structure (like a B-tree).

6. VIEW
A view is a virtual table that stores only the query, not the data.
 Makes queries simpler.
 Provides data security (restrict access to sensitive columns).
Example:
CREATE OR REPLACE VIEW IMP_VW AS
SELECT NAME FROM IMP;

SELECT * FROM IMP_VW;

DROP VIEW IMP_VW;

👉 Here, IMP_VW shows only NAME column from IMP.

✅ So this section covered:

 Pseudo Columns (USER, UID, SYSDATE, SYSTIMESTAMP,
ROWNUM, ROWID, NEXTVAL, CURRVAL)
 Database Schemas (Table, Sequence, Synonym, Index, View)
  Creating & using Sequences
 Synonyms with examples
 Indexes for performance tuning
 Views (virtual tables)

in a easy way :
1. Pseudo Columns
Think of pseudo columns like special gift boxes that the database
gives you.
They are not real columns in the table, but they behave like one.
 USER → tells you your username.
 SELECT USER FROM DUAL; -- Example: HR
 UID → gives a number ID for your login session.
 SYSDATE → today’s date (from computer).
 SYSTIMESTAMP → date + time + timezone.
 ROWNUM → gives row numbers (1, 2, 3, …) when you ask for
rows.
 SELECT ROWNUM, S.* FROM STUD S;
 ROWID → shows the address of a row inside the database (like
a house address).
 NEXTVAL → next number from a sequence.
 CURRVAL → current number from a sequence.
👉 Example:
SELECT ROWNUM AS "RNUM", C.*
FROM CLG C
WHERE ROWNUM <= 5;
-- Gives first 5 rows

2. Database Schemas
A schema is like a school bag. Inside it, you have different things:
 Table → like a notebook (stores data).
 Sequence → like page numbers (keeps counting).
 Synonym → nickname for a table.
 Index → shortcut to find things faster.
 View → a window to see only part of the table.

3. Sequence
A sequence is like a number machine that automatically gives you
new numbers (1, 2, 3 …).
Example:
CREATE SEQUENCE IMP_SEQ
START WITH 1
INCREMENT BY 1
MAXVALUE 100
NOCYCLE
CACHE 20;
 Starts with 1.
 Goes up by 1 every time.
 Stops at 100.
 Doesn’t start again (NOCYCLE).
  Keeps 20 numbers ready (faster).
👉 Using it:
INSERT INTO IMP VALUES (IMP_SEQ.NEXTVAL, 'RAJ');
INSERT INTO IMP VALUES (IMP_SEQ.NEXTVAL, 'RAM KUMAR');
Here, IDs will be 1 and 2 automatically.

4. Synonym
A synonym is just a nickname for a table.
Example:
CREATE SYNONYM IMPORT FOR IMP;
Now instead of typing IMP, you can type IMPORT.
Like calling Ramesh as Ramu.

5. Index
An index is like the index page in a book 📖.
 Without index → you search every page.
 With index → you jump directly.
Example:
CREATE INDEX NM_IND ON IMP(NAME);
Now searching names in IMP is faster.

6. View
A view is like a window to see only what you want from a big table.
Example:
CREATE OR REPLACE VIEW IMP_VW AS
SELECT NAME FROM IMP;
Now when you open IMP_VW, you see only NAME, not all columns.

✅ That’s the simple explanation of:

 Pseudo columns (special built-in columns)
 Schema parts (table, sequence, synonym, index, view)
 Sequences (number generator)
 Synonyms (nicknames)
 Index (book shortcut)
 Views (windows)