1. Create a procedure to get a driver details by d_id.

CREATE OR REPLACE PROCEDURE get_driver_info(

p_d_id IN driver.d_id%TYPE,
p_driver_name OUT VARCHAR2,
p_driver_license_info OUT VARCHAR2
)
IS
BEGIN
SELECT driver_name,license_info
INTO p_driver_name, p_driver_license_info
FROM driver
WHERE d_id = p_d_id;
END;
/

set serveroutput on;

SQL> declare
2 d_name driver.driver_name%type;
3 d_license_info driver.license_info%type;
4 begin
5 get_driver_info(003,d_name,d_license_info);
6 dbms_output.put_line('Driver Name:'||d_name);
7 dbms_output.put_line(' License Info:'||d_license_info);
8 end;
9 /
Driver Name:balaji
License Info:eg2341

PL/SQL procedure successfully completed.

2.Create a procedure to update the city of the driver.

CREATE OR REPLACE PROCEDURE update_driver_city(

p_d_id IN driver.d_id%TYPE,
p_city IN VARCHAR2
)
IS
BEGIN
UPDATE driver
SET city = p_city
WHERE d_id = p_d_id;
DBMS_OUTPUT.PUT_LINE('Driver city updated successfully.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred while updating Driver City.');
END;
/

set serveroutput on;

SQL> begin
2 update_driver_city(2,'madurai');
3 end;
4 /
Driver city updated successfully.

PL/SQL procedure successfully completed.

SQL> select city from driver where d_id = 2;

CITY
--------------------
madurai

3.Create a procedure to delete the paymentdetails.

CREATE OR REPLACE PROCEDURE delete_payment_details (

p_payment_id IN NUMBER
)
AS
BEGIN
DELETE FROM payment
WHERE payment_id = p_payment_id;
END;
/

set serveroutput on;

SQL> begin
2 delete_payment_details(303);
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select * from payment where payment_id =303;

no rows selected

4.Create a function to count the no of payments made by the one customer.

CREATE OR REPLACE FUNCTION get_payment_count(p_customer_id IN NUMBER)

RETURN NUMBER
IS
v_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM payment
WHERE customer_id = p_customer_id;

RETURN v_count;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
/

select payment_id,customer_id,get_payment_count(customer_id) from payment;

PAYMENT_ID CUSTOMER_ID GET_PAYMENT_COUNT(CUSTOMER_ID)

---------- ----------- ------------------------------
302 502 1
304 504 1
316 501 2
310 503 1
311 12 1
 301 501 2

6 rows selected.

5.Create a function to get the start date of the discount by the promotion_id.

CREATE OR REPLACE FUNCTION get_start_date(p_promotion_id IN NUMBER)

RETURN date
IS
v_date date;
BEGIN
SELECT start_date
INTO v_date
FROM promotion_and_discount
WHERE promotion_id = p_promotion_id;

RETURN v_date;
END;
/

select promotion_id,get_start_date(promotion_id) as start_date from

promotion_and_discount;

PROMOTION_ID START_DAT
------------ ---------
701 13-FEB-24
704 28-APR-24
705 05-FEB-24
707 28-APR-24
709 01-MAY-24
710 01-APR-24
711 01-MAY-24

7 rows selected.

6.Create a function to calculate the total amount paid in a particular date.

CREATE OR REPLACE FUNCTION calculate_total_amount_paid(p_date IN date)

RETURN NUMBER
IS
v_total_amount NUMBER := 0;
BEGIN
SELECT SUM(amount)
INTO v_total_amount
FROM payment
WHERE payment_date = p_date;

RETURN v_total_amount;
END;
/

select payment_date,calculate_total_amount_paid(payment_date) from payment;

PAYMENT_D CALCULATE_TOTAL_AMOUNT_PAID(PAYMENT_DATE)
--------- -----------------------------------------
15-FEB-24 32000
19-FEB-24 11000
09-MAY-24 9000
10-FEB-24 14000
03-DEC-24 10000
15-FEB-24 32000

6 rows selected.

7.Create a function to count the no of branch in the particular city.

CREATE OR REPLACE FUNCTION find_no_of_branch(p_branch_name IN varchar)

RETURN NUMBER
IS
v_count NUMBER := 0;
BEGIN
SELECT count(*)
INTO v_count
FROM branch
WHERE branch_name = p_branch_name;

RETURN v_count;
END;
/

BRANCH_NAME FIND_NO_OF_BRANCH(BRANCH_NAME)
-------------------- ------------------------------
sivakasi_branch 1
virudhunagar_branch 1
madurai_branch 4
aruppukottai_branch 1
rajapalayam_branch 1
chennai_branch 1

6 rows selected.

8.Create a cursor to display the payment details.

DECLARE
p_id payment.payment_id%type;
p_amount payment.amount%type;
p_date payment.payment_date%type;
CURSOR p_payment IS
SELECT payment_id,amount,payment_date from payment;
BEGIN
OPEN p_payment;
LOOP
FETCH p_payment into p_id,p_amount,p_date;
EXIT WHEN p_payment%notfound;
dbms_output.put_line(p_id || ' ' || p_amount || ' ' || p_date);
END LOOP;
CLOSE p_payment;
END;
/

302 15000 15-FEB-24

304 11000 19-FEB-24
316 9000 09-MAY-24
310 14000 10-FEB-24
311 10000 03-DEC-24
301 17000 15-FEB-24

PL/SQL procedure successfully completed.

9. Create a cursor to display the driver details who are less than the average
experience.

DECLARE
2 CURSOR c_leastexperience_driver IS
3 SELECT d_id,driver_name,age,license_info FROM driver where experience
< (select avg(experience) from driver);
4 c_d_id driver.d_id%type;
5 c_name driver.driver_name%type;
6 c_age driver.age%type;
7 c_license_info driver.license_info%type;
8 BEGIN
9 OPEN c_leastexperience_driver;
10 LOOP
11 FETCH c_leastexperience_driver INTO c_d_id,c_name,c_age,c_license_info;
12 EXIT WHEN c_leastexperience_driver%NOTFOUND;
13 dbms_output.put_line('Id:'||c_d_id);
14 dbms_output.put_line(' Name:'||c_name);
15 dbms_output.put_line(' Age:'||c_age);
16 dbms_output.put_line(' License Info:'||c_license_info);
17 END LOOP;
18 CLOSE c_leastexperience_driver;
19 END;
20 /
Id:10
Name:ayyanar
Age:30
License Info:bv2468
Id:1
Name:arjun
Age:21
License Info:ad1234
Id:12
Name:abish kumar
Age:30
License Info:ad1234
Id:3
Name:balaji
Age:19
License Info:eg2341
Id:2
Name:murugan
Age:21
License Info:vb1357
Id:17
Name:arun karthick
Age:21
License Info:ad1254
Id:16
Name:ashwin
Age:21
License Info:an1234

PL/SQL procedure successfully completed.

10. Create a cursor to display the active discount.

DECLARE
CURSOR active_discount IS
 SELECT count(*)
FROM promotion_and_discount
WHERE start_date <= sysdate and end_date >= sysdate;
discount_count number;
BEGIN
OPEN active_discount;
FETCH active_discount INTO discount_count;
DBMS_OUTPUT.PUT_LINE('Active Discount:'||discount_count);
CLOSE active_discount;
END;
/

Active Discount:4

PL/SQL procedure successfully completed.

11.Create a procedure to display the active discount details.

CREATE OR REPLACE PROCEDURE get_discountdetails

IS
BEGIN
FOR discount_rec IN (SELECT promotion_id,start_date,end_date from
promotion_and_discount where start_date = '01-MAY-24' and end_date = '31-MAY-24')
LOOP
DBMS_OUTPUT.PUT_LINE('Promotion Id: ' || discount_rec.promotion_id || ',
Start Date: ' || discount_rec.start_date || ', End Date: ' ||
discount_rec.end_date);
END LOOP;
END;
/

set serveroutput on;

SQL> begin
2 get_discountdetails();
3 end;
4 /
Promotion Id: 709, Start Date: 01-MAY-24, End Date: 31-MAY-24
Promotion Id: 711, Start Date: 01-MAY-24, End Date: 31-MAY-24

PL/SQL procedure successfully completed.

12.Create a function to display the one row of payment.

CREATE OR REPLACE FUNCTION get_payment_data(p_payment_id IN NUMBER)

RETURN payment%ROWTYPE
IS
v_payment_data payment%ROWTYPE;
BEGIN
SELECT *
INTO v_payment_data
FROM payment
WHERE payment_id = p_payment_id;

RETURN v_payment_data;
END;
/

DECLARE
v_payment_data payment%rowtype;
BEGIN
v_payment_data := get_payment_data(316);
DBMS_OUTPUT.PUT_LINE('Payment ID: ' || v_payment_data.payment_id);
DBMS_OUTPUT.PUT_LINE('Amount: ' || v_payment_data.amount);
DBMS_OUTPUT.PUT_LINE('Payment Date: ' || TO_CHAR(v_payment_data.payment_date,
'DD-MON-YYYY'));
DBMS_OUTPUT.PUT_LINE('Transaction Detail: ' ||
v_payment_data.transaction_detail);
DBMS_OUTPUT.PUT_LINE('Payment Method: ' || v_payment_data.payment_method);
DBMS_OUTPUT.PUT_LINE('Customer ID: ' || v_payment_data.customer_id);
END;
/

Payment ID: 316

Amount: 9000
Payment Date: 09-MAY-2024
Transaction Detail: payment successfull
Payment Method: upi
Customer ID: 501

PL/SQL procedure successfully completed.

13.Create a function to count the total no of drivers.

CREATE OR REPLACE FUNCTION driver_count

RETURN NUMBER
IS
v_count NUMBER;
begin
SELECT COUNT(*) INTO v_count FROM driver;
dbms_output.put_line('Total Drivers: '||v_count);
return null;
end;
/

SQL> declare
2 v_count number;
3 begin
4 v_count:=driver_count();
5 end;
6 /
Total Drivers: 12

PL/SQL procedure successfully completed.

14.Create a function to check the number is prime or not.

CREATE OR REPLACE FUNCTION is_prime(n IN NUMBER) RETURN BOOLEAN IS

BEGIN
FOR i IN 2..(n/2) LOOP
IF MOD(n, i) = 0 THEN
RETURN FALSE;
END IF;
END LOOP;

RETURN TRUE;
END;
/
DECLARE
num_to_check NUMBER := 12;
is_prime_number BOOLEAN;
BEGIN
is_prime_number := is_prime(num_to_check);
IF is_prime_number THEN
DBMS_OUTPUT.PUT_LINE(num_to_check || ' is a prime number.');
ELSE
DBMS_OUTPUT.PUT_LINE(num_to_check || ' is not a prime number.');
END IF;
END;
/

12 is not a prime number.

PL/SQL procedure successfully completed.

DECLARE
num_to_check NUMBER := 13;
is_prime_number BOOLEAN;
BEGIN
is_prime_number := is_prime(num_to_check);
IF is_prime_number THEN
DBMS_OUTPUT.PUT_LINE(num_to_check || ' is a prime number.');
ELSE
DBMS_OUTPUT.PUT_LINE(num_to_check || ' is not a prime number.');
END IF;
END;
/

13 is a prime number.

PL/SQL procedure successfully completed.