Program 4

Write SQL queries using character, number, date, and group functions.

👉 We will be using ‘tbl_teachers’ from Prgram 3 to demonstrate this program :

+------+-----------------+-----+--------+-----------------+----------+
| id | name | age | sub_id | date_of_joining | salary |
+------+-----------------+-----+--------+-----------------+----------+
| 1001 | Sharda Tiwari | 27 | 1 | 2020-03-20 | 70000.00 |
| 1002 | Shashwat Shukla | 36 | 2 | 2024-05-11 | 35000.00 |
| 1003 | Ritu Dwivedi | 29 | 3 | 2023-02-03 | 68000.00 |
| 1004 | Chetna Singh | 39 | 4 | 2025-02-10 | 65000.00 |
| 1005 | Rakesh Sharma | 40 | 5 | 2023-10-15 | 40000.00 |
| 1006 | Pooja Verma | 28 | NULL | 2022-04-01 | 62000.00 |
| 1007 | Suman Sharma | 33 | 1 | 2021-05-05 | 68000.00 |
| 1008 | Avinash Pandey | 27 | NULL | 2023-09-06 | 60000.00 |
| 1009 | Sonam Singh | 24 | NULL | 2020-08-09 | 65000.00 |
| 1010 | Faraz Khan | 35 | NULL | 2022-03-19 | 55000.00 |
| 1011 | Arunima | 24 | NULL | 2021-11-27 | 63000.00 |
+------+-----------------+-----+--------+-----------------+----------+

(1) SQL Queries Using Character (2) SQL Queries Using Number
Functions Functions

mysql> SELECT name, UPPER(name) AS mysql> SELECT ROUND(12.6856, 2);

uppercase_name FROM tbl_teachers LIMIT 5; +-------------------+
+-----------------+-----------------+ | ROUND(12.6856, 2) |
| name | uppercase_name | +-------------------+
+-----------------+-----------------+ | 12.69 |
| Sharda Tiwari | SHARDA TIWARI | +-------------------+
| Shashwat Shukla | SHASHWAT SHUKLA | 1 row in set (0.00 sec)
| Ritu Dwivedi | RITU DWIVEDI |
| Chetna Singh | CHETNA SINGH | mysql> SELECT CEIL(12.6856),
| Rakesh Sharma | RAKESH SHARMA | FLOOR(12.6856);
+-----------------+-----------------+ +---------------+----------------+
5 rows in set (0.00 sec) | CEIL(12.6856) | FLOOR(12.6856) |
+---------------+----------------+
mysql> SELECT name, LOWER(name) AS | 13 | 12 |
lowercase_name FROM tbl_teachers LIMIT 6 +---------------+----------------+
OFFSET 5; 1 row in set (0.00 sec)
+----------------+----------------+
| name | lowercase_name | mysql> SELECT MOD(143, 69),
+----------------+----------------+ POWER(2, 10);
| Pooja Verma | pooja verma | +--------------+--------------+
| Suman Sharma | suman sharma | | MOD(143, 69) | POWER(2, 10) |
| Avinash Pandey | avinash pandey | +--------------+--------------+
| Sonam Singh | sonam singh | | 5 | 1024 |
| Faraz Khan | faraz khan | +--------------+--------------+
| Arunima | arunima | 1 row in set (0.00 sec)
+----------------+----------------+
6 rows in set (0.00 sec)

15
mysql> SELECT name, LENGTH(name) AS (3) SQL Queries Using Date
name_length FROM tbl_teachers LIMIT 5;
+-----------------+-------------+ Functions
| name | name_length |
+-----------------+-------------+ mysql> SELECT NOW() AS curr_timestamp;
| Sharda Tiwari | 13 | +---------------------+
| Shashwat Shukla | 15 | | curr_timestamp |
| Ritu Dwivedi | 12 | +---------------------+
| Chetna Singh | 12 | | 2025-03-06 11:13:39 |
| Rakesh Sharma | 13 | +---------------------+
+-----------------+-------------+ 1 row in set (0.00 sec)
5 rows in set (0.00 sec)
mysql> SELECT CURDATE() AS today_date;
mysql> SELECT name AS before_concat, +------------+
CONCAT(‘Prof. ‘, name) AS after_concat | today_date |
FROM tbl_teachers LIMIT 6 OFFSET 5; +------------+
+----------------+----------------------+ | 2025-03-06 |
| before_concat | after_concat | +------------+
+----------------+----------------------+ 1 row in set (0.00 sec)
| Pooja Verma | Prof. Pooja Verma |
| Suman Sharma | Prof. Suman Sharma | mysql> SELECT DAY(CURDATE()) AS day,
| Avinash Pandey | Prof. Avinash Pandey | MONTH(CURDATE()) AS month,
| Sonam Singh | Prof. Sonam Singh | YEAR(CURDATE()) as year;
| Faraz Khan | Prof. Faraz Khan | +-----+-------+------+
| Arunima | Prof. Arunima | | day | month | year |
+----------------+----------------------+ +-----+-------+------+
6 rows in set (0.00 sec) | 6 | 3 | 2025 |
+-----+-------+------+
mysql> SELECT name AS full_name, 1 row in set (0.00 sec)
SUBSTRING(name, 1, LOCATE(' ', name) - 1)
AS first_name FROM tbl_teachers LIMIT 5; mysql> SELECT HOUR(NOW()) AS hour,
+-----------------+------------+ MINUTE(NOW()) AS minute,
| full_name | first_name | SECOND(NOW()) AS second;
+-----------------+------------+ +------+--------+--------+
| Sharda Tiwari | Sharda | | hour | minute | second |
| Shashwat Shukla | Shashwat | +------+--------+--------+
| Ritu Dwivedi | Ritu | | 11 | 16 | 50 |
| Chetna Singh | Chetna | +------+--------+--------+
| Rakesh Sharma | Rakesh | 1 row in set (0.00 sec)
+-----------------+------------+
5 rows in set (0.00 sec)

mysql> SELECT id, name, YEAR(date_of_joining) FROM tbl_teachers LIMIT 3;

+------+-----------------+-----------------------+
| id | name | YEAR(date_of_joining) |
+------+-----------------+-----------------------+
| 1001 | Sharda Tiwari | 2020 |
| 1002 | Shashwat Shukla | 2024 |
| 1003 | Ritu Dwivedi | 2023 |
+------+-----------------+-----------------------+
3 rows in set (0.00 sec)

(4) SQL Queries Using Group Functions

mysql> SELECT
-> COUNT(*) AS total_teachers,
-> MAX(salary) AS max_salary_amt,
-> MIN(salary) AS min_salary_amt,
-> AVG(salary) AS average_of_salary
-> FROM tbl_teachers;

16
+----------------+----------------+----------------+-------------------+
| total_teachers | max_salary_amt | min_salary_amt | average_of_salary |
+----------------+----------------+----------------+-------------------+
| 11 | 70000.00 | 35000.00 | 59181.818182 |
+----------------+----------------+----------------+-------------------+
1 row in set (0.00 sec)

Program 5

Write SQL queries for extracting data from more than one table.

👉 Data from more than one table can be extracted using the JOIN functions. The
JOIN function are of 4 types – INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

👉 INNER JOIN – extracts common data only.

👉 LEFT JOIN – extracts all data of left table matching with the right table.
👉 RIGHT JOIN – extracts all data of right table matching with the left table.

👉 FULL JOIN – extracts all data ( left + right ), but not available in MySQL.

👉 We will be using both the tables, ‘tbl_teachers’ & ‘tbl_subjects’ from

Program 3 to demonstrate this program :

+------+-----------------+-----+--------+-----------------+----------+
| id | name | age | sub_id | date_of_joining | salary |
+------+-----------------+-----+--------+-----------------+----------+
| 1001 | Sharda Tiwari | 27 | 1 | 2020-03-20 | 70000.00 |
| 1002 | Shashwat Shukla | 36 | 2 | 2024-05-11 | 35000.00 |
| 1003 | Ritu Dwivedi | 29 | 3 | 2023-02-03 | 68000.00 |
| 1004 | Chetna Singh | 39 | 4 | 2025-02-10 | 65000.00 |
| 1005 | Rakesh Sharma | 40 | 5 | 2023-10-15 | 40000.00 |
| 1006 | Pooja Verma | 28 | NULL | 2022-04-01 | 62000.00 |
| 1007 | Suman Sharma | 33 | 1 | 2021-05-05 | 68000.00 |
| 1008 | Avinash Pandey | 27 | NULL | 2023-09-06 | 60000.00 |
| 1009 | Sonam Singh | 24 | NULL | 2020-08-09 | 65000.00 |
| 1010 | Faraz Khan | 35 | NULL | 2022-03-19 | 55000.00 |
| 1011 | Arunima | 24 | NULL | 2021-11-27 | 63000.00 |
+------+-----------------+-----+--------+-----------------+----------+

+--------+------+
| sub_id | sub |
+--------+------+
| 1 | DBMS |
| 2 | OS |
| 3 | SE |
| 4 | SNT |
| 5 | COA |
+--------+------+

17
(1) Extracting Data Using INNER JOIN

mysql> SELECT id, name, age, date_of_joining, tbl_subjects.sub, salary

-> FROM tbl_teachers
-> INNER JOIN tbl_subjects ON tbl_teachers.sub_id = tbl_subjects.sub_id;
+------+-----------------+-----+-----------------+------+----------+
| id | name | age | date_of_joining | sub | salary |
+------+-----------------+-----+-----------------+------+----------+
| 1001 | Sharda Tiwari | 27 | 2020-03-20 | DBMS | 70000.00 |
| 1002 | Shashwat Shukla | 36 | 2024-05-11 | OS | 35000.00 |
| 1003 | Ritu Dwivedi | 29 | 2023-02-03 | SE | 68000.00 |
| 1004 | Chetna Singh | 39 | 2025-02-10 | SNT | 65000.00 |
| 1005 | Rakesh Sharma | 40 | 2023-10-15 | COA | 40000.00 |
| 1007 | Suman Sharma | 33 | 2021-05-05 | DBMS | 68000.00 |
+------+-----------------+-----+-----------------+------+----------+
6 rows in set (0.00 sec)

(2) Extracting Data Using LEFT JOIN

mysql> SELECT id, name, age, date_of_joining, tbl_subjects.sub, salary

-> FROM tbl_teachers
-> LEFT JOIN tbl_subjects ON tbl_teachers.sub_id = tbl_subjects.sub_id;
+------+-----------------+-----+-----------------+------+----------+
| id | name | age | date_of_joining | sub | salary |
+------+-----------------+-----+-----------------+------+----------+
| 1001 | Sharda Tiwari | 27 | 2020-03-20 | DBMS | 70000.00 |
| 1002 | Shashwat Shukla | 36 | 2024-05-11 | OS | 35000.00 |
| 1003 | Ritu Dwivedi | 29 | 2023-02-03 | SE | 68000.00 |
| 1004 | Chetna Singh | 39 | 2025-02-10 | SNT | 65000.00 |
| 1005 | Rakesh Sharma | 40 | 2023-10-15 | COA | 40000.00 |
| 1006 | Pooja Verma | 28 | 2022-04-01 | NULL | 62000.00 |
| 1007 | Suman Sharma | 33 | 2021-05-05 | DBMS | 68000.00 |
| 1008 | Avinash Pandey | 27 | 2023-09-06 | NULL | 60000.00 |
| 1009 | Sonam Singh | 24 | 2020-08-09 | NULL | 65000.00 |
| 1010 | Faraz Khan | 35 | 2022-13-19 | NULL | 55000.00 |
| 1011 | Arunima | 24 | 2021-11-27 | NULL | 63000.00 |
+------+-----------------+-----+-----------------+------+----------+
11 rows in set (0.00 sec)

(3) Extracting Data Using RIGHT JOIN

mysql> SELECT id, name, age, date_of_joining, tbl_subjects.sub, salary

-> FROM tbl_teachers
-> RIGHT JOIN tbl_subjects ON tbl_teachers.sub_id = tbl_subjects.sub_id;
+------+-----------------+-----+-----------------+------+----------+
| id | name | age | date_of_joining | sub | salary |
+------+-----------------+-----+-----------------+------+----------+
| 1007 | Suman Sharma | 33 | 2021-05-05 | DBMS | 68000.00 |
| 1001 | Sharda Tiwari | 27 | 2020-03-20 | DBMS | 70000.00 |
| 1002 | Shashwat Shukla | 36 | 2024-05-11 | OS | 35000.00 |
| 1003 | Ritu Dwivedi | 29 | 2023-02-03 | SE | 68000.00 |
| 1004 | Chetna Singh | 39 | 2025-02-10 | SNT | 65000.00 |
| 1005 | Rakesh Sharma | 40 | 2023-10-15 | COA | 40000.00 |
+------+-----------------+-----+-----------------+------+----------+
6 rows in set (0.00 sec)

18
Program 6

Write SQL queries for sub-queries and nested queries.

👉 Sub-queries are the queries nested inside another (big) query. They are used to
fetch complex data demands by performing operations like filtering, aggregations,
and comparisons before the main query is executed. Sub-queries are of 3 types –
Single Row Sub-query, Multiple Row Sub-query, and Correlated Sub-query.

👉 Single Row Sub-query – returns only one result, row or value

👉 Multiple Row Sub-query – returns multiple results, rows or values

👉 Correlated Sub-query – it is a row-wise dynamic query

👉 We will be using both the tables, ‘tbl_teachers’ & ‘tbl_subjects’ from

Program 3 to demonstrate this program :

+------+-----------------+-----+--------+-----------------+----------+
| id | name | age | sub_id | date_of_joining | salary |
+------+-----------------+-----+--------+-----------------+----------+
| 1001 | Sharda Tiwari | 27 | 1 | 2020-03-20 | 70000.00 |
| 1002 | Shashwat Shukla | 36 | 2 | 2024-05-11 | 35000.00 |
| 1003 | Ritu Dwivedi | 29 | 3 | 2023-02-03 | 68000.00 |
| 1004 | Chetna Singh | 39 | 4 | 2025-02-10 | 65000.00 |
| 1005 | Rakesh Sharma | 40 | 5 | 2023-10-15 | 40000.00 |
| 1006 | Pooja Verma | 28 | NULL | 2022-04-01 | 62000.00 |
| 1007 | Suman Sharma | 33 | 1 | 2021-05-05 | 68000.00 |
| 1008 | Avinash Pandey | 27 | NULL | 2023-09-06 | 60000.00 |
| 1009 | Sonam Singh | 24 | NULL | 2020-08-09 | 65000.00 |
| 1010 | Faraz Khan | 35 | NULL | 2022-03-19 | 55000.00 |
| 1011 | Arunima | 24 | NULL | 2021-11-27 | 63000.00 |
+------+-----------------+-----+--------+-----------------+----------+

+--------+------+
| sub_id | sub |
+--------+------+
| 1 | DBMS |
| 2 | OS |
| 3 | SE |
| 4 | SNT |
| 5 | COA |
+--------+------+

19
(1) Single Row Sub-query – fetching teacher(s) whose salary is maximum

mysql> SELECT id, name, age, date_of_joining, salary FROM tbl_teachers

-> WHERE salary = (
-> SELECT MAX(salary) FROM tbl_teachers
-> );
+------+---------------+-----+-----------------+----------+
| id | name | age | date_of_joining | salary |
+------+---------------+-----+-----------------+----------+
| 1001 | Sharda Tiwari | 27 | 2020-03-20 | 70000.00 |
+------+---------------+-----+-----------------+----------+
1 row in set (0.00 sec)

(2) Multiple Row Sub-query – fetching teachers who are alloted the subjects

mysql> SELECT id, name, age, date_of_joining, salary FROM tbl_teachers

-> WHERE sub_id IN (
-> SELECT sub_id FROM tbl_subjects
-> )
-> ORDER BY name ASC;
+------+-----------------+-----+-----------------+----------+
| id | name | age | date_of_joining | salary |
+------+-----------------+-----+-----------------+----------+
| 1004 | Chetna Singh | 39 | 2025-02-10 | 65000.00 |
| 1005 | Rakesh Sharma | 40 | 2023-10-15 | 40000.00 |
| 1003 | Ritu Dwivedi | 29 | 2023-02-03 | 68000.00 |
| 1001 | Sharda Tiwari | 27 | 2020-03-20 | 70000.00 |
| 1002 | Shahswat Shukla | 36 | 2024-05-11 | 35000.00 |
| 1007 | Suman Sharma | 33 | 2021-05-05 | 68000.00 |
+------+-----------------+-----+-----------------+----------+
6 rows in set (0.00 sec)

(3) Correlated Sub-query – fetching teachers whose salary is above average

mysql> SELECT id, name, age, date_of_joining, salary FROM tbl_teachers

-> WHERE salary > (
-> SELECT AVG(salary) FROM tbl_teachers
-> )
-> ORDER BY salary ASC;
+------+----------------+-----+-----------------+----------+
| id | name | age | date_of_joining | salary |
+------+----------------+-----+-----------------+----------+
| 1008 | Avinash Pandey | 27 | 2023-09-06 | 60000.00 |
| 1006 | Pooja Verma | 28 | 2022-04-01 | 62000.00 |
| 1011 | Arunima | 24 | 2021-11-27 | 63000.00 |
| 1004 | Chetna Singh | 39 | 2025-02-10 | 65000.00 |
| 1009 | Sonam Singh | 24 | 2020-08-09 | 65000.00 |
| 1003 | Ritu Dwivedi | 29 | 2023-02-03 | 68000.00 |
| 1007 | Suman Sharma | 33 | 2021-05-05 | 68000.00 |
| 1001 | Sharda Tiwari | 27 | 2020-03-20 | 70000.00 |
+------+----------------+-----+-----------------+----------+
8 rows in set (0.00 sec)

20
Program 7

Write program by the use of PL / SQL.

Since on a local system, PL / SQL, i.e., Procedural Language / Structured Query

Language needs Oracle to be installed. So instead, it can be demonstrated on an
online compiler https://onecompiler.com/plsql. The code and the output
demonstrated below is also available on https://onecompiler.com/plsql/43fvw6unn.

🔴 Code 👇

SET SERVEROUTPUT ON;

DECLARE
v_username VARCHAR2(20) := ‘r-tripathi’; -- could be promted by user
v_password VARCHAR2(20) := ‘raj@090806’; -- could be promted by user

db_username VARCHAR2(20) := ‘r-tripathi’; -- could be fetched from db

db_password VARCHAR2(20) := ‘raj@090806’; -- could be fetched from db

BEGIN
IF v_username = db_username AND v_password = db_password THEN
DBMS_OUTPUT.PUT_LINE(‘Login Successful ! Welcome ‘ || v_username);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Login Failed ! Invalid Credentials‘);
END IF;

END;
/

🔴 Output 👇

Login Successful ! Welcome r-tripathi

👉 VARCHAR2 is Oracle’s officially recommended data type. VARCHAR is reserved in

PL / SQL and is unsafe for production use.

21
Program 8

Concepts for ROLLBACK and COMMIT.

Design the following schemas 👇

Database 👉 db_bank
Table 👉 tbl_accounts (ENGINE=InnoDB)
Fields
+---------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| cust_id | int | NO | PRI | NULL | |
| cust_name | varchar(50) | NO | | NULL | |
| ac_number | varchar(5) | NO | | NULL | |
| balance | decimal(10,2) | NO | | NULL | |
| last_txn_type | varchar(10) | YES | | NULL | |
| last_txn_date | date | YES | | NULL | |
+---------------+---------------+------+-----+---------+-------+
Data
+---------+-----------+-----------+----------+---------------+---------------+
| cust_id | cust_name | ac_number | balance | last_txn_type | last_txn_date |
+---------+-----------+-----------+----------+---------------+---------------+
| 1001 | A | 98765 | 60000.00 | NULL | NULL |
| 1002 | B | 54321 | 10000.00 | NULL | NULL |
+---------+-----------+-----------+----------+---------------+---------------+
Let us supose that customer ‘A’ is sending Rs. 20000 to customer ‘B’ .

(1) With TRANSACTION and ROLLBACK

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE tbl_accounts

-> SET balance = balance – 20000,
-> last_txn_type = ‘debit’,
-> last_txn_date = CURDATE()
-> WHERE ac_number = ‘98765’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE tbl_accounts

-> SET balance = balance + 20000,
-> last_txn_type = ‘credit’,
-> last_txn_date = CURDATE()
-> WHERE ac_number = ‘12345’;
Query OK, 0 row affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

22
mysql> SELECT * FROM tbl_accounts;
+---------+-----------+-----------+----------+---------------+---------------+
| cust_id | cust_name | ac_number | balance | last_txn_type | last_txn_date |
+---------+-----------+-----------+----------+---------------+---------------+
| 1001 | A | 98765 | 40000.00 | debit | 2025-04-06 |
| 1002 | B | 54321 | 10000.00 | NULL | NULL |
+---------+-----------+-----------+----------+---------------+---------------+
2 rows in set (0.00 sec)

👉 The transaction between both the customers is NOT completed due to the invalid
‘ac_number’ of customer ‘B’. So any changes made during the transaction process could be
reverted back using the ROLLBACK statement as :

mysql> ROLLBACK;
Query OK, 0 row affected (0.00 sec)

mysql> SELECT * FROM tbl_accounts;

+---------+-----------+-----------+----------+---------------+---------------+
| cust_id | cust_name | ac_number | balance | last_txn_type | last_txn_date |
+---------+-----------+-----------+----------+---------------+---------------+
| 1001 | A | 98765 | 60000.00 | NULL | NULL |
| 1002 | B | 54321 | 10000.00 | NULL | NULL |
+---------+-----------+-----------+----------+---------------+---------------+
2 rows in set (0.00 sec)

(2) With TRANSACTION and COMMIT

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE tbl_accounts

-> SET balance = balance – 20000,
-> last_txn_type = ‘debit’,
-> last_txn_date = CURDATE()
-> WHERE ac_number = ‘98765’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE tbl_accounts

-> SET balance = balance + 20000,
-> last_txn_type = ‘credit’,
-> last_txn_date = CURDATE()
-> WHERE ac_number = ‘54321’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

23
mysql> SELECT * FROM tbl_accounts;
+---------+-----------+-----------+----------+---------------+---------------+
| cust_id | cust_name | ac_number | balance | last_txn_type | last_txn_date |
+---------+-----------+-----------+----------+---------------+---------------+
| 1001 | A | 98765 | 40000.00 | debit | 2025-04-06 |
| 1002 | B | 54321 | 30000.00 | credit | 2025-04-06 |
+---------+-----------+-----------+----------+---------------+---------------+
2 rows in set (0.00 sec)

👉 The transaction between both the customers is completed. So we could make those
changes permanent by using the COMMIT statement as :

mysql> COMMIT;
Query OK, 0 row affected (0.00 sec)

mysql> SELECT * FROM tbl_accounts;

+---------+-----------+-----------+----------+---------------+---------------+
| cust_id | cust_name | ac_number | balance | last_txn_type | last_txn_date |
+---------+-----------+-----------+----------+---------------+---------------+
| 1001 | A | 98765 | 40000.00 | debit | 2025-04-06 |
| 1002 | B | 54321 | 30000.00 | credit | 2025-04-06 |
+---------+-----------+-----------+----------+---------------+---------------+
2 rows in set (0.00 sec)

24
Program 9

Create VIEWS and understand its concepts.

👉 VIEWS are the virtual table created using SELECT query. It allows simplified access to
specific rows & columns of one or more tables without altering the original data.

👉 VIEWS does not store the data itself. The VIEW will be failed if the base table is
dropped, i.e., the table from which the VIEW is created. Some VIEWS are non-updatable.

👉 We can use the concept of VIEWS to hide out the sensitive columns / information. This
increases the security and provides a level of abstraction.

👉 We will be using ‘tbl_teachers’ from Prgram 3 to create a VIEW of it :

+------+-----------------+-----+--------+-----------------+----------+
| id | name | age | sub_id | date_of_joining | salary |
+------+-----------------+-----+--------+-----------------+----------+
| 1001 | Sharda Tiwari | 27 | 1 | 2020-03-20 | 70000.00 |
| 1002 | Shashwat Shukla | 36 | 2 | 2024-05-11 | 35000.00 |
| 1003 | Ritu Dwivedi | 29 | 3 | 2023-02-03 | 68000.00 |
| 1004 | Chetna Singh | 39 | 4 | 2025-02-10 | 65000.00 |
| 1005 | Rakesh Sharma | 40 | 5 | 2023-10-15 | 40000.00 |
| 1006 | Pooja Verma | 28 | NULL | 2022-04-01 | 62000.00 |
| 1007 | Suman Sharma | 33 | 1 | 2021-05-05 | 68000.00 |
| 1008 | Avinash Pandey | 27 | NULL | 2023-09-06 | 60000.00 |
| 1009 | Sonam Singh | 24 | NULL | 2020-08-09 | 65000.00 |
| 1010 | Faraz Khan | 35 | NULL | 2022-03-19 | 55000.00 |
| 1011 | Arunima | 24 | NULL | 2021-11-27 | 63000.00 |
+------+-----------------+-----+--------+-----------------+----------+

mysql> CREATE VIEW high_salary_teachers AS

-> SELECT id, name, age, date_of_joining, salary FROM tbl_teachers
-> WHERE salary > 65000;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM high_salary_teachers ORDER BY age DESC;

+------+-----------------+-----+----------+-----------------+
| id | name | age | date_of_joining | salary |
+------+-----------------+-----+----------+-----------------+
| 1007 | Suman Sharma | 33 | 2021-05-05 | 68000.00 |
| 1003 | Ritu Dwivedi | 29 | 2023-02-03 | 68000.00 |
| 1001 | Sharda Tiwari | 27 | 2020-03-20 | 70000.00 |
+------+-----------------+-----+----------+-----------------+
3 rows in set (0.00 sec)

25
Program 10

Create CURSORS and understand its concepts.

👉 CURSORS are the mechanism to fetch multiple rows from a query and process them
one-by-one in PL / SQL. Since queries like SELECT returns multiple rows, then CURSORS
works as a pointer to handle the result set in row-by-row manner.

👉 CURSORS are of 2 types – An Implicit Cursor (managed by the Oracle itself, for e.g. :
normal SELECT or UPDATE query), and An Explicit Cursor (created and managed manually
by the users). This program demonstrates using the Explicit Cursor.

👉 Again it’s a concept of PL / SQL, so it could be demonstrated on an online compiler

https://onecompiler.com/plsql. The code and the output demonstrated below is also
available on https://onecompiler.com/plsql/43g3p97q3.

🔴 Code 👇

SET SERVEROUTPUT ON;

DECLARE
CURSOR stu_cursor IS -- declaring CURSOR
SELECT ‘Raj’ AS name, 95 AS marks FROM dual
UNION ALL
SELECT ‘Priya’ AS name, 80 AS marks FROM dual
UNION ALL
SELECT ‘Pranav’ AS name, 85 AS marks FROM dual;
-- variables to hold CURSOR data 👇
name VARCHAR2(50);
marks NUMBER;
BEGIN
OPEN stu_cursor; -- opening CURSOR
LOOP
-- fetching data into CURSOR variables 👇
FETCH stu_cursor INTO v_name, v_marks;
-- exiting when no more rows / records found 👇
EXIT WHEN stu_cursor%NOTFOUND;
-- processing fetched data 👇
DBMS_OUTPUT.PUT_LINE(‘Name: ‘ || name || CHR(9) || ‘Marks:’ || marks);
END LOOP;
CLOSE stu_cursor; -- closing CURSOR
END;

26
🔴 Output 👇

Student: Raj Marks: 95

Student: Priya Marks: 80
Student: Pranav Marks: 85

👉 ‘dual’ is a dummy table in Oracle used to select a value without access any real table.

👉 The ‘UNION’ or ‘UNION ALL’ statement is used to combine the results of two ‘SELECT’
queries. On executing the ‘UNION’ statement, the duplicate rows are removed from the
result, whereas on executing the ‘UNION ALL’ statement, it combines all the rows as-it-is
without removing the duplicates.

27