UNIT III: SQL: Basic SQL querying (select and project) using where clause, arithmetic & logical

operations,
SQL functions(Date and Time, Numeric, String conversion).Creating tables with relationship, implementation
of key and integrity constraints, nested queries, sub queries, grouping, aggregation, ordering, implementation of
different types of joins, view(updatable and non-updatable), relational set operations.
Basic SQL Querying: SELECT and PROJECT using WHERE Clause
SELECT → Retrieves specific columns (projection).

WHERE → Filters rows based on conditions.

Sample Table: employees
id name age department salary
1 Alice 30 IT 60000
2 Bob 35 HR 50000
3 Charlie 28 IT 75000
4 David 40 Sales 45000
5 Eve 32 Marketing 55000

1 Selecting All Columns (*) with a Condition

Query
SELECT * FROM employees WHERE department = 'IT';
Output
id name age department salary
1 Alice 30 IT 60000
3 Charlie 28 IT 75000

2Selecting Specific Columns (Projection)

Query
SELECT name, salary FROM employees WHERE salary > 50000;
Output
name salary
Alice 60000
Charlie 75000
Eve 55000

3Using Multiple Conditions (AND, OR)

Query
SELECT name, age FROM employees WHERE department = 'HR' AND age > 30;

[Link]@[Link]
Output
name age
Bob 35

Query
SELECT name, department FROM employees WHERE department = 'Sales' OR department = 'Marketing';
Output
name department
David Sales
Eve Marketing

4Using Pattern Matching (LIKE)

Query
SELECT name FROM employees WHERE name LIKE 'A%';
Output
name
Alice

5Using Range Conditions (BETWEEN)

Query
SELECT name, salary FROM employees WHERE salary BETWEEN 40000 AND 70000;
Output
name salary
Alice 60000
Bob 50000
David 45000
Eve 55000

6Using Set Membership (IN)

Query
SELECT name, department FROM employees WHERE department IN ('IT', 'HR');
Output
name department
Alice IT

[Link]@[Link]
Charlie IT
Bob HR

Arithmetic & Logical Operations in Oracle

1. Arithmetic Operators in Oracle
Oracle supports standard arithmetic operators for performing mathematical calculations.
Operator Description Example (SELECT statement)
+ Addition SELECT 10 + 5 FROM dual;
- Subtraction SELECT 10 - 5 FROM dual;
* Multiplication SELECT 10 * 5 FROM dual;
/ Division SELECT 10 / 5 FROM dual;
MOD Modulus (Remainder) SELECT MOD(10, 3) FROM dual;
2. Logical Operators in Oracle
Logical operators are used in WHERE clauses to filter data based on conditions.
Operator Description Example (SELECT statement)
AND True if both conditions are true SELECT * FROM employees WHERE salary > 5000 AND
department = 'IT';
OR True if at least one condition is SELECT * FROM employees WHERE salary > 5000 OR
true department = 'IT';
NOT Negates the condition SELECT * FROM employees WHERE NOT department = 'HR';
3. Comparison Operators
Comparison operators are commonly used with logical operators.
Operator Description Example (SELECT statement)
= Equal to SELECT * FROM employees WHERE department = 'HR';
!= or <> Not equal to SELECT * FROM employees WHERE department <> 'HR';
> Greater than SELECT * FROM employees WHERE salary > 5000;
< Less than SELECT * FROM employees WHERE salary < 5000;
>= Greater than or equal to SELECT * FROM employees WHERE salary >= 5000;
<= Less than or equal to SELECT * FROM employees WHERE salary <= 5000;

What is DUAL in Oracle?

DUAL is a special dummy table in Oracle used to perform calculations, function evaluations, and select
statements without requiring an actual table.
Key Features of DUAL Table:

[Link]@[Link]
 1. Single Row, Single Column:
o It has only one row and one column (DUMMY) containing the value 'X'.
o Run:
SELECT * FROM dual;
Output:
DUMMY
-----
X
2. Used for Function Calls & Expressions:
o Since it contains a single row, any function or calculation using DUAL returns just one result.
o Example:
SELECT SYSDATE FROM dual;
Output (Example):
SYSDATE
---------
11-MAR-25
3. Virtual Table (No Storage Needed):
o Oracle automatically optimizes queries on DUAL, making it efficient for quick calculations.
Common Uses of DUAL Table:
Use Case Example Output (Sample)
Get Current Date SELECT SYSDATE FROM dual; 11-MAR-25
Evaluate Expressions SELECT 10 * 5 FROM dual; 50
Get System User SELECT USER FROM dual; HR
Convert Strings SELECT TO_DATE('2025-12-31', 'YYYY-MM-DD') FROM dual; 31-DEC-25
Note: In Oracle 21c and later, DUAL is no longer needed for function calls;
SELECT SYSDATE;
works directly.

SQL Date Functions in Oracle

Oracle provides various date functions to manipulate and format date values.
1. Getting the Current Date & Time
Function Description Example Output (Sample)
SYSDATE Returns the current SELECT SYSDATE FROM 11-MAR-25
system date & time dual;

[Link]@[Link]
SYSTIMESTAMP Returns the current SELECT SYSTIMESTAMP 11-MAR-25
timestamp with time FROM dual; [Link].123456
zone +05:30
CURRENT_DATE Returns the current SELECT CURRENT_DATE 11-MAR-25
date in the session's FROM dual;
time zone
CURRENT_TIMESTAMP Returns the current SELECT 11-MAR-25
timestamp with CURRENT_TIMESTAMP [Link].123456
session's time zone FROM dual; +05:30

2. Extracting Parts of a Date

Function Description Example Output
(Sample)
EXTRACT Extracts components like SELECT EXTRACT(YEAR FROM SYSDATE) 2025
year, month, day FROM dual;
TO_CHAR Converts date to string SELECT TO_CHAR(SYSDATE, 'YYYY-MM- 2025-03-11
format DD HH24:MI:SS') FROM dual; [Link]

3. Date Arithmetic
Function Description Example Output
(Sample)
ADD_MONTHS(date, n) Adds n months SELECT ADD_MONTHS(SYSDATE, 3) 11-JUN-
to a date FROM dual; 25
NEXT_DAY(date, 'DAY') Returns the next SELECT NEXT_DAY(SYSDATE, 17-MAR-
occurrence of a 'Monday') FROM dual; 25
specific day
LAST_DAY(date) Returns the last SELECT LAST_DAY(SYSDATE) FROM 31-MAR-
day of the month dual; 25
MONTHS_BETWEEN(date1, Returns the SELECT 14.33
date2) number of MONTHS_BETWEEN(SYSDATE,
months between TO_DATE('2024-01-01', 'YYYY-MM-DD'))
two dates FROM dual;

[Link]@[Link]
4. Date Formatting with TO_CHAR
Format Description Example Output
(Sample)
'YYYY' Year (4 digits) SELECT TO_CHAR(SYSDATE, 'YYYY') FROM 2025
dual;
'MM' Month (2 digits) SELECT TO_CHAR(SYSDATE, 'MM') FROM dual; 03
'DD' Day (2 digits) SELECT TO_CHAR(SYSDATE, 'DD') FROM dual; 11
'DAY' Full day name SELECT TO_CHAR(SYSDATE, 'DAY') FROM dual; TUESDAY
'DY' Short day name SELECT TO_CHAR(SYSDATE, 'DY') FROM dual; TUE
'HH24:MI:SS' 24-hour time SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') [Link]
format FROM dual;

5. Converting Strings to Dates (TO_DATE)

Function Description Example Output
(Sample)
TO_DATE Converts a string to a date SELECT TO_DATE('2024-03-11', 'YYYY-MM- 11-MAR-24
format DD') FROM dual;

Numeric Functions in Oracle

Oracle provides various numeric functions to perform mathematical calculations.

1. Basic Mathematical Functions

Function Description Example Output
ABS(n) Returns the absolute value of n SELECT ABS(-10) FROM dual; 10
CEIL(n) Returns the smallest integer ≥ n SELECT CEIL(4.3) FROM dual; 5
FLOOR(n) Returns the largest integer ≤ n SELECT FLOOR(4.9) FROM dual; 4
ROUND(n, d) Rounds n to d decimal places SELECT ROUND(3.456, 2) FROM dual; 3.46
TRUNC(n, d) Truncates n to d decimal places SELECT TRUNC(3.456, 2) FROM dual; 3.45

2. Power & Logarithmic Functions

Function Description Example Output
POWER(x, y) Returns x^y SELECT POWER(2, 3) FROM dual; 8
SQRT(n) Returns the square root of n SELECT SQRT(25) FROM dual; 5
LN(n) Returns the natural log (base e) of n SELECT LN(10) FROM dual; 2.3025

[Link]@[Link]
LOG(b, n) Returns log of n to base b SELECT LOG(10, 1000) FROM dual; 3
EXP(n) Returns e^n SELECT EXP(1) FROM dual; 2.7183

3. Random Number Functions

Function Description Example Output
(Sample)
DBMS_RANDOM.VALUE(x, Returns a random SELECT 42.7865
y) number between x DBMS_RANDOM.VALUE(1, 100)
and y FROM dual;

4. Modulus & Sign Functions

Function Description Example Output
MOD(x, y) Returns remainder of x/y SELECT MOD(10, 3) FROM dual; 1
SIGN(n) Returns -1 for negative, 0 for zero, 1 for positive SELECT SIGN(-5) FROM dual; -1
String Conversion Functions in Oracle
Oracle provides various string conversion functions to convert data types between strings, numbers, and dates.

1. Convert Numbers & Dates to Strings (TO_CHAR)

Function Description Example Output
TO_CHAR(n) Converts number SELECT TO_CHAR(1234) FROM dual; '1234'
to string
TO_CHAR(n, 'FM9999.99') Formats number SELECT TO_CHAR(1234.5, '9999.99') '1234.50'
as a string FROM dual;
TO_CHAR(SYSDATE, Converts date to SELECT TO_CHAR(SYSDATE, '2025-03-11'
'YYYY-MM-DD') string 'YYYY-MM-DD') FROM dual;
TO_CHAR(SYSDATE, 'DAY') Returns full day SELECT TO_CHAR(SYSDATE, 'DAY') 'TUESDAY
name FROM dual; '

2. Convert Strings to Numbers (TO_NUMBER)

Function Description Example Output
TO_NUMBER('1234') Converts string to SELECT TO_NUMBER('1234') FROM 1234
number dual;
TO_NUMBER('1,234.56', Converts formatted SELECT TO_NUMBER('1,234.56', 1234.56
'9G999D99') string to number '9G999D99') FROM dual;

[Link]@[Link]
3. Convert Strings to Dates (TO_DATE)
Function Description Example Output
TO_DATE('2025-03-11', Converts string to SELECT TO_DATE('2025-03-11', 'YYYY- 11-MAR-
'YYYY-MM-DD') date MM-DD') FROM dual; 25
TO_DATE('11-MAR-25', 'DD- Converts another SELECT TO_DATE('11-MAR-25', 'DD- 11-MAR-
MON-YY') format MON-YY') FROM dual; 25

4. Convert Data to Raw & Hex (TO_CHAR & RAWTOHEX)

Function Descripti Example Outp
on ut
RAWTOHEX(UTL_RAW.CAST_TO_RA Converts SELECT '4142
W('ABC')) string to RAWTOHEX(UTL_RAW.CAST_TO_RA 43
hex W('ABC')) FROM dual;

Creating Tables with Relationships in Oracle

To define relationships between tables in Oracle, we use Primary Key (PK) and Foreign Key (FK)
constraints.

Example: Employee & Department Relationship

We will create:
 departments table (Primary Table)
 employees table (Child Table, references departments)

Step 1: Create departments Table (Parent Table)

CREATE TABLE departments (
dept_id NUMBER PRIMARY KEY,
dept_name VARCHAR2(100) NOT NULL
);
dept_id is the Primary Key ensuring unique department IDs.

Step 2: Create employees Table (Child Table) with Foreign Key

CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,

[Link]@[Link]
 emp_name VARCHAR2(100) NOT NULL,
salary NUMBER(10,2),
dept_id NUMBER,
CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
Foreign Key (dept_id) links to departments.dept_id, ensuring referential integrity.

Step 3: Insert Sample Data

INSERT INTO departments VALUES (1, 'HR');
INSERT INTO departments VALUES (2, 'IT');

INSERT INTO employees VALUES (101, 'Alice', 60000, 1);

INSERT INTO employees VALUES (102, 'Bob', 75000, 2);

Step 4: Verify Relationship with a Join Query

SELECT e.emp_name, [Link], d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;
Output:
EMP_NAME SALARY DEPT_NAME
-------- ------- ----------
Alice 60000 HR
Bob 75000 IT

Key Relationship Constraints in Oracle

Constraint Description
PRIMARY KEY Ensures unique, non-null values in a column.
FOREIGN KEY Establishes a relationship between two tables.
ON DELETE CASCADE Deletes child records when parent is deleted.
ON DELETE SET NULL Sets child foreign key to NULL if parent is deleted.

Example with ON DELETE CASCADE

CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,

[Link]@[Link]
 emp_name VARCHAR2(100) NOT NULL,
dept_id NUMBER,
CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ON DELETE
CASCADE
);
If a department is deleted, all related employees will also be deleted.
Implementation of Keys and Integrity Constraints in Oracle
Oracle provides keys and integrity constraints to maintain data accuracy and consistency. These constraints
ensure that database rules are enforced.

1. Primary Key Constraint

 Ensures unique, non-null values for a column.
 Used to uniquely identify each record.
Example: Create Table with Primary Key
CREATE TABLE students (
student_id NUMBER PRIMARY KEY, -- Unique student ID
name VARCHAR2(100) NOT NULL
);
student_id must be unique and cannot be NULL.

2. Foreign Key Constraint (Referential Integrity)

 Links two tables by referencing a primary key in another table.
 Prevents inserting invalid data in the foreign key column.
Example: Create a Table with a Foreign Key
CREATE TABLE courses (
course_id NUMBER PRIMARY KEY,
course_name VARCHAR2(100) NOT NULL
);

CREATE TABLE enrollments (

enroll_id NUMBER PRIMARY KEY,
student_id NUMBER,
course_id NUMBER,
CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES students(student_id),

[Link]@[Link]
 CONSTRAINT fk_course FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
student_id and course_id must exist in the referenced tables (students and courses).

3. NOT NULL Constraint

 Prevents NULL values in a column.
Example:
CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(100) NOT NULL, -- Cannot be NULL
salary NUMBER
);
emp_name must always have a value.

4. UNIQUE Constraint
 Ensures all values in a column are unique (but allows NULLs).
Example:
CREATE TABLE customers (
customer_id NUMBER PRIMARY KEY,
email VARCHAR2(150) UNIQUE -- Each email must be unique
);
Duplicate emails are not allowed, but NULL is allowed.

5. CHECK Constraint
 Enforces a condition on column values.
Example:
CREATE TABLE products (
product_id NUMBER PRIMARY KEY,
price NUMBER CHECK (price > 0) -- Price must be greater than 0
);
Prevents negative or zero prices.

6. DEFAULT Constraint
 Assigns a default value when no value is provided.

[Link]@[Link]
Example:
CREATE TABLE orders (
order_id NUMBER PRIMARY KEY,
status VARCHAR2(20) DEFAULT 'Pending' -- Default status is 'Pending'
);
If no status is provided, it defaults to 'Pending'.

7. ON DELETE CASCADE (Automatic Deletion)

 Deletes child records when the parent record is deleted.
Example:
CREATE TABLE orders (
order_id NUMBER PRIMARY KEY
);

CREATE TABLE order_items (

item_id NUMBER PRIMARY KEY,
order_id NUMBER,
CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE
CASCADE
);
Deleting an order will automatically delete its items.

Summary of Key Constraints

Constraint Description
PRIMARY KEY Ensures unique, non-null values
FOREIGN KEY Links tables & maintains referential integrity
NOT NULL Prevents NULL values
UNIQUE Ensures values are unique
CHECK Enforces custom rules
DEFAULT Sets a default value
ON DELETE CASCADE Deletes child records if the parent is deleted

[Link]@[Link]
Nested Queries (Subqueries) in Oracle
A nested query (subquery) is a query inside another SQL query. It is used to fetch data based on the result
of another query.

1. Simple Subquery (Using WHERE)

 Retrieves employees who work in the "IT" department.
SELECT emp_name, salary
FROM employees
WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_name = 'IT');
The inner query finds the dept_id of "IT", and the outer query retrieves employees from that department.

2. Subquery in SELECT (Scalar Subquery)

 Retrieves employee names along with their department name.
SELECT emp_name,
(SELECT dept_name FROM departments WHERE departments.dept_id = employees.dept_id) AS
department
FROM employees;
The subquery returns the department name for each employee.

3. Subquery in FROM (Inline View)

 Finds the highest salary in each department.
SELECT dept_id, MAX(salary) AS highest_salary
FROM (SELECT dept_id, salary FROM employees)
GROUP BY dept_id;
The inner query selects all department salaries, and the outer query finds the highest salary per
department.

4. Subquery with HAVING

 Retrieves departments with an average salary greater than 50,000.
SELECT dept_id
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

[Link]@[Link]
 The subquery calculates the overall average salary, and the outer query filters departments with
higher averages.

5. Correlated Subquery
 Retrieves employees earning more than the average salary of their department.
SELECT emp_name, salary, dept_id
FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.dept_id = e2.dept_id);
The inner query depends on the outer query (correlated subquery).

6. EXISTS with Subquery

 Retrieves departments that have employees.
SELECT dept_name
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE d.dept_id = e.dept_id);
The EXISTS checks if employees exist in each department.

7. IN with Subquery
 Retrieves employees who work in HR or IT.
SELECT emp_name
FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments WHERE dept_name IN ('HR', 'IT'));
The subquery returns department IDs, and the outer query selects employees from those departments.

Summary of Nested Queries Usage

Type Description
WHERE Filters results using a subquery.
SELECT Returns a calculated value in the main query.
FROM Uses a subquery as a temporary table (inline view).
HAVING Filters grouped results based on a subquery.
Correlated Uses the outer query inside the subquery.
EXISTS Checks if a subquery returns results.
IN Filters based on a list of values from a subquery.

[Link]@[Link]
Difference Between Nested Queries and Subqueries in Oracle
Feature Subquery Nested Query
Definition A query inside another query. A subquery inside another subquery.
Depth Single level of nesting. Multi-level nesting (subqueries within
subqueries).
Complexity Simpler, usually used once inside a query. More complex, with multiple layers of
subqueries.
Execution Inner query executes first, then outer Inner-most subquery executes first, then the next,
query. and so on.
Performance Generally faster and easier to optimize. Can be slower due to multiple levels of
execution.
Example WHERE, SELECT, FROM, HAVING, Advanced filtering, multi-step transformations.
Usage EXISTS, IN.

1. Example of a Simple Subquery

 Find employees who work in the "IT" department.
SELECT emp_name
FROM employees
WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_name = 'IT');
The subquery retrieves the department ID, and the outer query fetches employees.

2. Example of a Nested Query

 Find employees in departments where the average salary is higher than the company average.
SELECT emp_name
FROM employees
WHERE dept_id IN (
SELECT dept_id FROM departments
WHERE dept_id IN (
SELECT dept_id FROM employees
GROUP BY dept_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees)
)
);

[Link]@[Link]
 Innermost subquery: Calculates the company-wide average salary.

Middle subquery: Finds departments where the average salary is above the company average.
Outer query: Selects employees from those departments.

Note:
 All nested queries are subqueries, but not all subqueries are nested queries.
 Subqueries are used for single-level filtering.
 Nested queries involve multiple subqueries for complex conditions.

Grouping in Oracle SQL (GROUP BY Clause)

The GROUP BY clause is used to group rows that have the same values in specified columns and apply
aggregate functions such as SUM(), COUNT(), AVG(), MAX(), and MIN() on each group.

Example Table: employees

emp_id emp_name dept_id salary
101 Alice 2 55000
102 Bob 1 60000
103 Charlie 3 48000
104 David 2 72000
105 Emma 1 65000
106 Frank 3 52000

1 Grouping by a Single Column

Total Salary per Department
SELECT dept_id, SUM(salary) AS total_salary
FROM employees
GROUP BY dept_id;
Output
dept_id total_salary
1 125000
2 127000
3 100000

2 Grouping with Multiple Columns

[Link]@[Link]
Total Salary and Employee Count per Department
SELECT dept_id, COUNT(emp_id) AS emp_count, SUM(salary) AS total_salary
FROM employees
GROUP BY dept_id;
Output
dept_id emp_count total_salary
1 2 125000
2 2 127000
3 2 100000

3 Using HAVING to Filter Groups

The HAVING clause filters grouped results after aggregation.
Departments with Total Salary Above 110000
SELECT dept_id, SUM(salary) AS total_salary
FROM employees
GROUP BY dept_id
HAVING SUM(salary) > 110000;
Output
dept_id total_salary
1 125000
2 127000

4 Combining GROUP BY with ORDER BY

Average Salary per Department, Ordered by Average Salary
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
ORDER BY avg_salary DESC;
Output
dept_id avg_salary
2 63500
1 62500
3 50000

[Link]@[Link]
Summary
Feature Query Example
Basic GROUP BY GROUP BY column_name
Multiple Columns GROUP BY col1, col2
Filtering Groups (HAVING) HAVING condition
Ordering Grouped Data ORDER BY aggregate_function()
COUNT(), SUM(), AVG() Aggregates values in groups.
ROLLUP Adds hierarchical totals.
CUBE Creates all possible combinations of grouping.
GROUPING SETS Custom grouping combinations.

Aggregation in Oracle SQL

Aggregation involves performing calculations on groups of rows to return summarized values. Oracle provides
several aggregate functions to process grouped data.

1. Common Aggregate Functions

Function Description
SUM() Returns the total sum of a numeric column.
AVG() Returns the average value.
COUNT() Counts rows or distinct values.
MIN() Returns the smallest value.
MAX() Returns the largest value.

2. SUM() – Total Calculation

 Find total salary for all employees.
SELECT SUM(salary) AS total_salary FROM employees;
Returns the sum of all salaries.

3. AVG() – Average Calculation

 Find the average salary per department.
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id;
Groups data by department and calculates the average salary.

[Link]@[Link]
4. COUNT() – Counting Records
 Find the number of employees per department.
SELECT dept_id, COUNT(*) AS num_employees
FROM employees
GROUP BY dept_id;
COUNT(*) counts all rows, while COUNT(column) ignores NULL values.

5. MIN() and MAX() – Finding Extremes

 Find the highest and lowest salary per job title.
SELECT job_title, MIN(salary) AS lowest_salary, MAX(salary) AS highest_salary
FROM employees
GROUP BY job_title;
Returns min and max salary per job title.

6. Combining Aggregate Functions

 Find total, average, highest, and lowest salary in the company.
SELECT SUM(salary) AS total_salary,
AVG(salary) AS avg_salary,
MAX(salary) AS highest_salary,
MIN(salary) AS lowest_salary
FROM employees;
Aggregates all salaries into multiple summaries.

7. Aggregation with HAVING

 Find departments with an average salary > 50,000.
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 50000;
HAVING filters grouped results (like WHERE, but for aggregates).

8. Aggregation with DISTINCT

 Find unique job titles and their counts.

[Link]@[Link]
SELECT COUNT(DISTINCT job_title) AS unique_jobs FROM employees;
COUNT(DISTINCT column) counts unique values.

Summary of Aggregation Features

Feature Description
SUM() Adds up numeric values.
AVG() Computes the average.
COUNT(*) Counts total rows.
COUNT(column) Counts non-null values.
MIN() / MAX() Finds lowest/highest values.
HAVING Filters aggregated results.

Ordering in Oracle SQL (ORDER BY Clause)

The ORDER BY clause is used to sort query results in ascending (ASC) or descending (DESC) order based
on one or more columns.
Example Table: employees
emp_id emp_name dept_id salary
101 Alice 2 55000
102 Bob 1 60000
103 Charlie 3 48000
104 David 2 72000
105 Emma 1 65000

1⃣ Default Sorting (Ascending)

By default, Oracle sorts in ascending order (ASC).
SELECT emp_id, emp_name, salary FROM employees
ORDER BY salary;
Output (Sorted by Salary in Ascending Order)
emp_id emp_name salary
103 Charlie 48000
101 Alice 55000

[Link]@[Link]
102 Bob 60000
105 Emma 65000
104 David 72000

2 Sorting in Descending Order

Sort employees by highest salary first using DESC.
SELECT emp_id, emp_name, salary FROM employees
ORDER BY salary DESC;
Output (Sorted by Salary in Descending Order)
emp_id emp_name salary
104 David 72000
105 Emma 65000
102 Bob 60000
101 Alice 55000
103 Charlie 48000

3 Sorting by Multiple Columns

Sort by department first (ASC), then by salary (DESC).
SELECT emp_id, emp_name, dept_id, salary FROM employees
ORDER BY dept_id ASC, salary DESC;
Output (Sorted by Dept_ID in Ascending Order, Salary in Descending Order)
emp_id emp_name dept_id salary
105 Emma 1 65000
102 Bob 1 60000
104 David 2 72000
101 Alice 2 55000
103 Charlie 3 48000

4 Ordering with NULL Values

By default:
 NULL values come last in ASC order.
 NULL values come first in DESC order.
Let's assume some employees have NULL salaries:
emp_id emp_name salary

[Link]@[Link]
101 Alice 55000
102 Bob NULL
103 Charlie 48000
104 David NULL
105 Emma 65000
Sorting by salary (ASC):
SELECT emp_id, emp_name, salary FROM employees
ORDER BY salary ASC;
Output (NULLs Last by Default)
emp_id emp_name salary
103 Charlie 48000
101 Alice 55000
105 Emma 65000
102 Bob NULL
104 David NULL
Sorting NULLs first:
SELECT emp_id, emp_name, salary FROM employees
ORDER BY salary ASC NULLS FIRST;
Output (NULLs First)
emp_id emp_name salary
102 Bob NULL
104 David NULL
103 Charlie 48000
101 Alice 55000
105 Emma 65000

Summary of ORDER BY Options

Sorting Type Query Example
Ascending (Default) ORDER BY column_name;
Descending ORDER BY column_name DESC;
Multiple Columns ORDER BY col1 ASC, col2 DESC;
NULLs First ORDER BY column_name NULLS FIRST;
NULLs Last (Default in ASC) ORDER BY column_name NULLS LAST;

[Link]@[Link]
Implementation of Different Types of Joins in Oracle SQL
Joins in Oracle SQL are used to combine data from multiple tables based on a related column. There are
different types of joins:

1. INNER JOIN (Default Join)

Returns matching rows from both tables.
 Example: Get employees with their department names.
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
-> Only employees assigned to a department are included.

2. LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table and matching rows from the right table.
 Example: Get all employees, even if they don't have a department.
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
-> Unmatched employees will have NULL for dept_name.

3. RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the right table and matching rows from the left table.
 Example: Get all departments, even if they have no employees.
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;
-> Departments without employees will show NULL for emp_name.

4. FULL JOIN (FULL OUTER JOIN)

Returns all rows from both tables, with NULLs for missing matches.
 Example: Get all employees and all departments, even if unmatched.
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
FULL JOIN departments d ON e.dept_id = d.dept_id;

[Link]@[Link]
-> Combines LEFT JOIN and RIGHT JOIN.

5. CROSS JOIN (Cartesian Join)

Returns every combination of rows from both tables.
 Example: Match each employee with every department.
SELECT e.emp_name, d.dept_name
FROM employees e
CROSS JOIN departments d;
-> No ON condition, generates all possible combinations.

6. SELF JOIN
Joins a table with itself using aliases.
 Example: Find each employee and their manager.
SELECT e1.emp_name AS employee, e2.emp_name AS manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.emp_id;
-> Employees and managers are both from the employees table.

Summary of Joins in Oracle

Join Type Description
INNER JOIN Returns matching rows from both tables.
LEFT JOIN Returns all left table rows + matching right table rows.
RIGHT JOIN Returns all right table rows + matching left table rows.
FULL JOIN Returns all rows from both tables (with NULLs for no matches).
CROSS JOIN Returns the Cartesian product of both tables.
SELF JOIN Joins a table with itself.

Implementation of Different Types of Joins in Oracle SQL with Sample Tables and Output
Joins are used to combine data from multiple tables based on a related column.

Sample Tables
1. Employees Table

[Link]@[Link]
emp_id emp_name dept_id manager_id
101 Alice 1 103
102 Bob 2 104
103 Charlie NULL NULL
104 David 1 NULL
2. Departments Table
dept_id dept_name
1 HR
2 IT
3 Finance

1⃣ INNER JOIN (Matching Records Only)

Returns employees who are assigned to a department.

SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
-> Output
emp_id emp_name dept_name
101 Alice HR
102 Bob IT
104 David HR

2⃣ LEFT JOIN (All Employees, Even Without Department)

Returns all employees, and NULL for those without a department.

SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
-> Output
emp_id emp_name dept_name
101 Alice HR
102 Bob IT
103 Charlie NULL
104 David HR

[Link]@[Link]
3⃣ RIGHT JOIN (All Departments, Even Without Employees)

Returns all departments, and NULL for those without employees.

SELECT e.emp_name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;
-> Output
emp_name dept_name
Alice HR
Bob IT
David HR
NULL Finance

4⃣ FULL JOIN (All Employees & All Departments)

Returns all employees and all departments, even if unmatched.

SELECT e.emp_name, d.dept_name
FROM employees e
FULL JOIN departments d ON e.dept_id = d.dept_id;
-> Output
emp_name dept_name
Alice HR
Bob IT
Charlie NULL
David HR
NULL Finance

5⃣ CROSS JOIN (Cartesian Product)

Matches every employee with every department.
SELECT e.emp_name, d.dept_name
FROM employees e
CROSS JOIN departments d;
-> Output (All Possible Combinations)
emp_name dept_name

[Link]@[Link]
Alice HR
Alice IT
Alice Finance
Bob HR
Bob IT
Bob Finance
Charlie HR
Charlie IT
Charlie Finance
David HR
David IT
David Finance

6⃣ SELF JOIN (Employees & Their Managers)

Joins employees table with itself to find each employee’s manager.

SELECT e1.emp_name AS employee, e2.emp_name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.emp_id;
-> Output
employee manager
Alice Charlie
Bob David
Charlie NULL
David NULL

Summary of Joins
Join Type Description Example Output
INNER Matches rows in both tables. Employees with departments.
JOIN
LEFT JOIN All left table rows + matched right table rows. All employees, even without
departments.
RIGHT All right table rows + matched left table rows. All departments, even without
JOIN employees.

[Link]@[Link]
FULL JOIN All rows from both tables (NULLs for no matches). All employees & all departments.
CROSS Every row from Table A with every row from Table All employee-department combinations.
JOIN B.
SELF JOIN Joins a table with itself. Employees and their managers.

Views in Oracle (Updatable & Non-Updatable)

A view is a virtual table that presents data from one or more tables. Views can be updatable or non-updatable
based on their structure.
Creating a Simple View
A basic view selects specific columns from a table.
CREATE VIEW emp_view AS
SELECT emp_id, emp_name, dept_id
FROM employees;
Querying the View
SELECT * FROM emp_view;
Output
emp_id emp_name dept_id
101 Alice 1
102 Bob 2
103 Charlie NULL

1. Updatable Views
An updatable view allows INSERT, UPDATE, and DELETE operations.
Conditions for Updatable Views
 Must be based on a single table.
 Must not contain GROUP BY, DISTINCT, JOIN, or aggregate functions.
CREATE VIEW updatable_emp_view AS
SELECT emp_id, emp_name, dept_id
FROM employees
WHERE dept_id IS NOT NULL;
Updating Data Through the View
UPDATE updatable_emp_view
SET emp_name = 'Alice Cooper'
WHERE emp_id = 101;

[Link]@[Link]
This updates the original employees table.
Inserting Data
INSERT INTO updatable_emp_view (emp_id, emp_name, dept_id)
VALUES (105, 'Eve', 3);
A new row is added to employees.
Deleting Data
DELETE FROM updatable_emp_view WHERE emp_id = 102;
Deletes row from employees.

2. Non-Updatable Views
A non-updatable view does not allow INSERT, UPDATE, or DELETE operations.
Reasons for Non-Updatable Views
 Uses multiple tables (JOINs).
 Includes GROUP BY, DISTINCT, or aggregate functions like SUM, AVG.
CREATE VIEW dept_summary AS
SELECT d.dept_name, COUNT(e.emp_id) AS emp_count
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_name;
Querying the View
SELECT * FROM dept_summary;
Output
dept_name emp_count
HR 2
IT 1
Trying to Update Will Fail
UPDATE dept_summary SET emp_count = 10 WHERE dept_name = 'HR';
This will result in an error because non-updatable views cannot be modified.

Summary of View Types

Type Can Update? Example Cases
Updatable View Yes Simple SELECT from one table without JOIN, GROUP BY.
Non-Updatable View No Uses JOIN, GROUP BY, or aggregate functions.

[Link]@[Link]
Difference Between Updatable and Non-Updatable Views in Oracle
Feature Updatable View Non-Updatable View
Modification (INSERT, UPDATE, Allowed Not Allowed
DELETE)
Based on Multiple Tables No Yes (with JOINs)
Contains Aggregations (SUM, AVG, No Yes
COUNT, etc.)
Contains DISTINCT or GROUP BY No Yes
Contains Subqueries No Yes (Common in reporting)
Example Use Case Updating employee Generating department-wise
details employee count
Relational Set Operations in Oracle
Relational set operations combine results from multiple queries. Oracle supports the following set operations:
Operation Description
UNION Combines results from two queries, removing duplicates.
UNION ALL Combines results from two queries, keeping duplicates.
INTERSECT Returns common rows between two queries.
MINUS Returns rows from the first query that are not in the second query.

Example Tables
employees Table
emp_id emp_name dept_id
101 Alice 1
102 Bob 2
103 Charlie 3
104 David 4
managers Table
emp_id emp_name dept_id
102 Bob 2
103 Charlie 3
105 Emma 5

1 UNION (Removes Duplicates)

Combines both tables but removes duplicate records.

[Link]@[Link]
SELECT emp_id, emp_name FROM employees
UNION
SELECT emp_id, emp_name FROM managers;
Output
emp_id emp_name
101 Alice
102 Bob
103 Charlie
104 David
105 Emma

2 UNION ALL (Keeps Duplicates)

Includes all records, even if they appear multiple times.
SELECT emp_id, emp_name FROM employees
UNION ALL
SELECT emp_id, emp_name FROM managers;
Output
emp_id emp_name
101 Alice
102 Bob
103 Charlie
104 David
102 Bob
103 Charlie
105 Emma

3 INTERSECT (Common Rows)

Returns rows that are common in both tables.
SELECT emp_id, emp_name FROM employees
INTERSECT
SELECT emp_id, emp_name FROM managers;
Output
emp_id emp_name
102 Bob

[Link]@[Link]
103 Charlie

4 MINUS (Only in First Table)

Returns rows from employees that are not in managers.
SELECT emp_id, emp_name FROM employees
MINUS
SELECT emp_id, emp_name FROM managers;
Output
emp_id emp_name
101 Alice
104 David

Summary of Set Operations

Operation Removes Duplicates? Returns Common Rows? Returns Unique Rows?
UNION Yes Yes No
UNION ALL No Yes No
INTERSECT Yes Yes No
MINUS Yes No Yes (from the first table only)

Important Points to Remember

1. Basic SQL Querying (SELECT & PROJECT)

 SELECT: Retrieves specific columns from a table.
 PROJECT: Used to display specific attributes (columns).
 WHERE: Filters rows based on conditions.
Example:
SELECT name, age FROM students WHERE age > 18;

2. Arithmetic & Logical Operations in SQL

Arithmetic Operators: +, -, *, /, % (modulus).

Logical Operators: AND, OR, NOT, BETWEEN, IN, LIKE.

Example:
SELECT * FROM employees WHERE salary > 50000 AND age < 40;

3. SQL Functions
a) Date and Time Functions
 NOW(), CURDATE(), CURTIME(), YEAR(), MONTH(), DAY(), DATE_ADD(), DATEDIFF().
 Example:
SELECT NOW(), YEAR(CURDATE()), DATEDIFF('2025-01-01', CURDATE());
b) Numeric Functions
 ABS(), ROUND(), CEIL(), FLOOR(), MOD(), POWER(), SQRT().

[Link]@[Link]
  Example:
SELECT ROUND(25.678, 2), SQRT(16), MOD(10, 3);
c) String Conversion Functions
 UPPER(), LOWER(), CONCAT(), SUBSTRING(), LENGTH(), TRIM().
 Example:
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

4. Creating Tables with Relationships

 Primary Key (PK): Ensures uniqueness in a column.
 Foreign Key (FK): Links two tables.
 Example:
CREATE TABLE Department (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);

CREATE TABLE Employee (

emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);

5. Implementation of Keys and Integrity Constraints

 Primary Key: Uniquely identifies records.
 Foreign Key: Enforces referential integrity.
 Unique Constraint: Ensures unique values in a column.
 Not Null: Prevents null values.
 Check: Restricts column values.
Example:
CREATE TABLE student (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT CHECK (age >= 18)
);

6. Nested Queries & Subqueries

 Nested Query: A query inside another query.
 Subquery Types: Scalar, Single-row, Multi-row, Correlated.
Example:
SELECT name FROM students WHERE age = (SELECT MAX(age) FROM students);

7. Grouping, Aggregation & Ordering

 GROUP BY: Groups data based on a column.
 HAVING: Filters grouped data.
 ORDER BY: Sorts results in ascending (ASC) or descending (DESC) order.
Example:
SELECT dept_id, COUNT(emp_id) FROM Employee GROUP BY dept_id HAVING COUNT(emp_id) > 5;

8. Joins in SQL
 INNER JOIN: Returns matching records.
 LEFT JOIN: Returns all records from the left table + matched ones from the right.
 RIGHT JOIN: Returns all records from the right table + matched ones from the left.
 FULL JOIN: Returns all records if a match is found in either table.
Example:

[Link]@[Link]
SELECT e.emp_name, d.dept_name
FROM Employee e INNER JOIN Department d ON e.dept_id = d.dept_id;

9. Views (Updatable & Non-Updatable)

 View: A virtual table based on a query.
 Updatable View: Allows INSERT, UPDATE, DELETE.
 Non-Updatable View: Based on multiple tables or aggregate functions.
Example:
CREATE VIEW EmployeeView AS
SELECT emp_name, dept_name
FROM Employee INNER JOIN Department ON Employee.dept_id = Department.dept_id;

10. Relational Set Operations

 UNION: Combines results (removes duplicates).
 UNION ALL: Combines results (keeps duplicates).
 INTERSECT: Returns common records.
 EXCEPT/MINUS: Returns records in the first query but not in the second.
Example:
SELECT name FROM students_2023 UNION SELECT name FROM students_2024;

Conclusion:
-> Use SELECT, WHERE, GROUP BY, ORDER BY for basic queries.
-> Functions like ROUND(), CONCAT(), NOW() enhance SQL operations.
-> Joins and subqueries improve data retrieval.
-> Constraints (PK, FK, NOT NULL) maintain data integrity.
-> Views simplify complex queries.
-> Set operations (UNION, INTERSECT) help combine datasets.

[Link]@[Link]