What is PL/SQL?⌗

1. PL/SQL stands for Procedural Language Extension to SQL
2. It is a Extension of SQL developed by Oracle.
3. It adds SQL capabilities of procedural programming like variables, loops and conditional statements in SQL.

PL/SQL Architecture⌗

Physical Architecture⌗

• PL/SQL engine processes the entire PL/SQL block of code.
• It Separate SQL statements and Procedural statements.

Simple Architecture Flow⌗

PL/SQL Logical Architecture⌗

• Corporates with SQL Engine.
• Enables Subprograms -> Allows re-usability of code/script like variables and functions.
• Dynamic Queries-> We can modify or create new Queries.
• Case Insensitivity
• Optimizer-> It optimizes our code for better performance.
• Enables Object-Oriented Programming
• Web Development #Extras Context Switching: When you write SQL Query in PL/SQL the PL/SQL send the query to SQL Engine and the result will be returned this operation is called Context Switching.

---

Pluggable Database⌗

What is PDB?⌗

Pluggable Database (PDB) is a portable collection of schemas, tables, and objects that can be plugged into a Container Database (CDB) in Oracle.

Why PDB?⌗

• Better resource management
• Easier database consolidation
• Faster provisioning and cloning

Structure⌗

1. A Container Database (CDB) holds multiple Pluggable Databases (PDBs).
2. A PDB is independent and can be moved or cloned between CDBs.
3. Each PDB has its own:
   • Data files
   • Schemas
   • Tables
   • Users
4. A single CDB can manage multiple PDBs, improving scalability and consolidation.

---

What are Blocks?⌗

Block is a logical unit of code that groups related declarations, statements.

Structure⌗

1. DECLARE Block.

Here we declare Variables, const, exceptions.

2. EXECUTION BLOCK.

Contains Executable Statements like SQL, procedural statements. Starts with BEGIN

3. EXCEPTION HANDLING BLOCK.

Starts with EXCEPTION Handles exceptions and errors during execution.

Syntax of PL/SQL Block⌗

DECLARE 
   -- Declaration statements (optional)
BEGIN 
   -- Executable statements (mandatory)
EXCEPTION 
   -- Exception handling statements (optional)
END;

Types of Blocks⌗

1. Anonymous Block
2. Procedures
3. Functions

Coding in PL/SQL⌗

How to Print Text as Output in PL/SQL⌗

SET SERVEROUTPUT ON;
begin
dbms_output.put_line('Hello World');
end;

Nested Blocks⌗

*We can add begin end inside begin end block that way we can do nested blocks.

SET SERVEROUTPUT ON;
begin
dbms_output.put_line('Hello World');
    BEGIN
        dbms_output.put_line('Its Me Rawat');    
    END;
end;

Variables⌗

`Same Use Cases as in normal Programming language.'

PL/SQL Variable Types⌗

1. SCALAR Holds single value of same type.
2. REFERENCE Pointers.
3. LARGE OBJECTS Holds pointers of large data.
4. COMPOSITE Hold more then one data type.

SCALAR DATA TYPES⌗

1. CHAR()
2. VARCHAR()
3. NUMBER[,]
4. BINARY_INTEGER = PLSINTEGER
5. BINARY_FLOAT
6. BINARY_DOUBLE
7. BOOLEAN
8. DATE
9. TIMESTAMP
10. TIMESTAMP(P) WITH TIME ZONE
11. TIMESTAMP(P) WITH LOCAL TIME ZONE
12. INTERVAL(P) YEAR TO MONTH
13. INTERVAL(P) DAY TO SECOND(P)

Variable Naming Conventions⌗

• Must Start with letter.
• Can contain some special character.(like _ ,#,$)
• Can be maximum 30 char
• can not have oracle reserved words.

Operations in PL-SQL⌗

Setting default variables⌗

SET SERVEROUTPUT ON;
DECLARE
    V_TEXT STRING(50) NOT NULL DEFAULT 'ITS_RAWAT';
BEGIN
DBMS_OUTPUT.PUT_LINE(V_TEXT);
END;

Assigning variables⌗

SET SERVEROUTPUT ON;
DECLARE
    V_TEXT STRING(50) NOT NULL DEFAULT 'ITS_RAWAT';
BEGIN
    V_TEXT:= 'PL/SQL';
    DBMS_OUTPUT.PUT_LINE(V_TEXT);
END;

Concatenation of Strings⌗

SET SERVEROUTPUT ON;
DECLARE
    V_TEXT STRING(50) NOT NULL DEFAULT 'ITS_RAWAT';
BEGIN
    V_TEXT:= 'PL/SQL' || ' IS DIFFERENT';
    DBMS_OUTPUT.PUT_LINE(V_TEXT || ' BUT MANAGEABLE');
END;
    

OR

SET SERVEROUTPUT ON;
DECLARE
    V_TEXT STRING(50) NOT NULL := 'WELCOME';
BEGIN
    DBMS_OUTPUT.PUT_LINE(V_TEXT || ' ITS_RAWAT');
END;
    

---

🟢 Level 1 — Easy⌗

Focus: SELECT, WHERE, ORDER BY, LIMIT, basic functions.

1. Display all columns from the products table.

2. Display only productName, productLine, and buyPrice from products.

3. Find all products whose productLine is Classic Cars.

4. Find all products where buyPrice is greater than 50.

5. Display all customers from USA.

6. Find all employees whose jobTitle is Sales Rep.

7. Display products sorted by buyPrice from lowest to highest.

8. Display the 10 most expensive products based on buyPrice.

9. Find products where quantityInStock is less than 1000.

10. Display customers whose creditLimit is greater than 100000.

---

🟡 Level 2 — Medium⌗

Focus: AND, OR, LIKE, IN, BETWEEN, aggregate functions, GROUP BY.

11. Find products whose buyPrice is between 50 and 100.

12. Find customers who are from USA, France, or Germany.

13. Find products whose name contains the word Ford.

14. Find products where buyPrice > 50 and quantityInStock > 5000.

15. Find the minimum, maximum, and average buyPrice of all products.

16. Find the total number of products in the products table.

17. Find the number of customers in each country.

18. Find the average credit limit for each country.

19. Find the number of products in each productLine.

20. Find product lines having more than 10 products.

---

🟠 Level 3 — Hard⌗

Focus: JOIN, GROUP BY, HAVING, subqueries.

21. Display each product along with its product line name.

22. Display each customer along with the first name and last name of their sales representative.

23. Display each employee along with their office city and country.

24. Display each order along with the customer’s name.

25. Find the total number of orders for each customer.

26. Find the total payment amount for each customer.

27. Find the total quantity ordered for each product.

28. Find the total sales amount for each order.

Hint:
quantityOrdered × priceEach

29. Find customers who have placed more than 5 orders.

30. Find products whose buyPrice is greater than the average buy price of all products.

---

🔴 Level 4 — Very Hard⌗

Now start thinking like a SQL developer / data analyst. 💪

31. Find the top 10 customers by total payment amount.

32. Find the top 10 products by total sales revenue.

33. Find the total revenue generated by each product line.

34. Find the total revenue generated by each customer using orders + orderdetails.

35. Find the sales representative who is responsible for the highest number of customers.

36. Find the sales representative who generated the highest total sales revenue.

37. Find customers who have never placed an order.

38. Find customers who have never made a payment.

39. Find products that have never been ordered.

40. Find the customer who has the highest total order value.

---

🔥 Level 5 — Advanced SQL⌗

These are closer to interview-level questions.

41. Find the second-highest buy price among all products.

42. Find the third-highest buy price without using LIMIT.

43. Find the most expensive product in each product line.

44. Find the cheapest product in each product line.

45. Find the customer with the highest credit limit in each country.

46. Find the employee who has the most customers assigned to them.

47. Find each customer’s:

    • customer name
    • total orders
    • total payments
    • total order value

48. Find customers whose total payments are greater than their total order value.

49. Find orders where the total order value is greater than the average order value of all orders.

50. Find the best-selling product in each product line based on total quantity ordered.