PL/SQL Query Practice #1

Write a PL/SQL function to return the current month meter reading for the given customer telephone number (write a separate code to call the function)

Output

Separate Function

Output

Write a PL/SQL procedure that performs an update of a zip code value.

Output

Write PL/SQL to List of customers whose meter reading is above 100 and below 200.

Output

Write PL/SQL to List of customers whose total billing amount is more than Rs.300.

Output

Write a PL/SQL procedure to find sum of meter readings of current month using for and while loop.

Output

Challenge

The following information is available in customer data file:

Customer Details:

Customer Telephone No: 7 numbers
Customer name: characters Customer
address: Alphanumeric (House no./house name, street, etc, city, pincode)
Meter readings :
1. Previous month : Numeric
2. Current month : Numeric

Create Tables

Consider the following tables:

CREATE TABLE customer_info(

    customer_id int primary key,

    customer_name varchar(20),

    customer_address varchar(30),

    phone number(7) NOT NULL

);

CREATE TABLE meter_reading(

    meter_id int primary key,

    previous_month number(6),

    current_month number(6),

    month_id number NOT NULL CHECK (month_id>0 and month_id<13),

    customer_id int,

    foreign key(customer_id) references customer_info(customer_id)

);

Write a PL/SQL procedure for telephone billing using the above input file.

For calculating the call charges use the following

No. of calls Rate
- 1 to 50 free
- 51 to 300 Rs.1.00
- above 301 Rs.1.20

The amount payable should include the rental charges and service tax.

The output should be in a proper format containing all the details.

Write a PL/SQL function to return the current month meter reading for the given customer telephone number (write a separate code to call the function)
Write a PL/SQL procedure that performs an update of a zip code value.
Write PL/SQL to do the following.
1. List of customers whose meter reading is above 100 and below 200.
2. List of customers whose total billing amount is more than Rs.300.
Write a PL/SQL procedure to find sum of meter readings of current month using for and while loop.

Insert Data

INSERT INTO customer_info VALUES(1,'Deepanshu Bhatti', 'Gol Market, Delhi 110012', 6170583);

INSERT INTO customer_info VALUES(2,'John Cena', 'West Berry, New York', 9265478);

INSERT INTO customer_info VALUES(3,'Hrithik Roshan', 'Juhu, Mumbai', 5841253);

INSERT INTO customer_info VALUES(4,'Arvind Kejriwal', 'Kaushambi, Ghaziabad', 9892686);

INSERT INTO customer_info VALUES(5,'Narendra Modi', '7 Race Course Road, Delhi', 7777777);

INSERT INTO customer_info VALUES(6,'Wayne Rooney', 'Manchester, England', 4548132);

INSERT INTO meter_reading VALUES(1,45,51,5,3);

INSERT INTO meter_reading VALUES(2,445,435,6,1);

INSERT INTO meter_reading VALUES(3,316,151,9,4);

INSERT INTO meter_reading VALUES(4,256,745,1,5);

INSERT INTO meter_reading VALUES(5,900,851,10,2);

INSERT INTO meter_reading VALUES(6,123,124,5,6);

-- To Enable Print Statements

set serveroutput on

Solutions

Write a PL/SQL function to return the current month meter reading for the given customer telephone number (write a separate code to call the function)

DECLARE

customerId customer_info.customer_id%type;

customerName customer_info.customer_name%type;

customerAddress customer_info.customer_address%type;

Phone customer_info.phone%type;

Calls meter_reading.current_month%type;

temp meter_reading.current_month%type;

monthId meter_reading.month_id%type;

serviceTax decimal(5,2);

rentCharges decimal(5,2);

totalAmount decimal(10,2);

numberOfCustomers number;

numberOfMeters number;

BEGIN

      customerId := &customerId;

      monthId := &monthId;

      SELECT count(*) INTO totalAmount FROM customer_info WHERE customer_id = customerId;

      IF totalAmount > 0 THEN

      BEGIN

            SELECT count(*) INTO numberOfMeters FROM meter_reading WHERE customer_id = customerId and month_id = monthId;

            IF numberOfMeters > 0 THEN

            BEGIN

                  SELECT customer_name,customer_address,phone INTO customerName,customerAddress,Phone FROM customer_info WHERE customer_id = customerId;

                  SELECT current_month INTO Calls FROM meter_reading WHERE customer_id = customerId;

            END;

            ELSE

            BEGIN

                  dbms_output.put_line('No Bill for this month exists');

                  RETURN;

            END;

            END IF;

      END;

      ELSE

      BEGIN

            dbms_output.put_line('Incorrect ID' || chr(10) || 'No Customer with this ID exists');

            RETURN;

      END;

      END IF;

      dbms_output.put_line('********************' || chr(10) ||'   Telephone Bill' || chr(10) || '********************' || chr(10) || 'Month Number :' || monthId);

      totalAmount := 0;

      IF Calls > 300 THEN

      BEGIN

            temp := Calls - 300;

            totalAmount := totalAmount + temp*(1.2) + 250;

      END;

      elsif Calls > 50 THEN

      BEGIN

            totalAmount := totalAmount + Calls;

      END;

      END IF;

      rentCharges := 300;

      serviceTax := totalAmount*(0.12);

      totalAmount :=  totalAmount + serviceTax + rentCharges;

      dbms_output.put_line('customerName : ' || customerName);

      dbms_output.put_line('customerAddress : ' || customerAddress);

      dbms_output.put_line('Phone Number : ' || Phone);

      dbms_output.put_line('Calls : ' || Calls);

      dbms_output.put_line('Rent : ' || rentCharges);

      dbms_output.put_line('Service Tax : ' || serviceTax);

      dbms_output.put_line('Total : ' || totalAmount);

END;

Output

Enter value for id: 3

old  15:        customerId := &customerId;

new  15:        customerId := 3;

Enter value for monthId: 5

old  16:        monthId := &monthId;

new  16:        monthId := 5;

********************

   Telephone Bill

********************

Month Number :5

customerName : Hrithik Roshan

customerAddress : Juhu, Mumbai

Phone Number : 5841253

Calls : 51

Rent : 300

Service Tax : 6.12

Total : 357.12

Separate Function

CREATE or REPLACE function currentReading(phone number) RETURN number

is

numberOfCustomers number;

reading number;

BEGIN

      SELECT count(*) INTO numberOfCustomers FROM customer_info WHERE phone = phone;

      IF numberOfCustomers > 0 THEN

      BEGIN

            SELECT current_month INTO reading FROM meter_reading WHERE customer_id in (SELECT customer_id FROM customer_info WHERE phone = phone);

            RETURN reading;

      END;

      ELSE

      BEGIN

            dbms_output.put_line('Phone number not exists');

            RETURN -1;

      END;

      END IF;

END;

DECLARE

calls number;

ph number;

BEGIN

      ph := &ph;

      calls := currentReading(ph);

      IF calls > -1 THEN

      BEGIN

            dbms_output.put_line('Total Calls : ' || calls);

      END;

      END IF;

END;

Output

Enter value for ph: 7777777

old   5:        ph := &ph;

new   5:        ph := 7777777;

Total Calls : 745

Write a PL/SQL procedure that performs an update of a zip code value.

CREATE or REPLACE procedure nameUpdate(id in int, name in varchar)

is

c number;

BEGIN

      SELECT count(*) INTO c FROM customer_info WHERE customer_id = id;

      IF c>0 THEN

            update customer_info set customer_name = name WHERE customer_id = id;

      ELSE

            dbms_output.put_line('customerId Dosent exists');

      END IF;

END;

DECLARE

id int;

name varchar(20);

BEGIN

      id := &id;

      name := &name;

      nameUpdate(id,name);

END;

Output

Enter value for id: 1

old   5:        id := &id;

new   5:        id := 1;

Enter value for name: 'Deepanshu'

old   6:        name := &name;

new   6:        name := 'Deepanshu';

PL/SQL procedure successfully completed.

SQL> SELECT * FROM customer_info;

       CID CNAME                CADDRESS                           CPHONE

---------- -------------------- ------------------------------ ----------

         1 Deepanshu            Gol Market, Delhi 110012          6170583

         2 John Cena            West Berry, New York              9265478

         3 Hrithik Roshan       Juhu, Mumbai                      5841253

         4 Arvind Kejriwal      Kaushambi, Ghaziabad              9892686

         5 Narendra Modi        7 Race Course Road, Delhi         7777777

Write PL/SQL to List of customers whose meter reading is above 100 and below 200.

DECLARE

cursor c is SELECT customer_id FROM meter_reading WHERE current_month> 100 and current_month < 200;

cursor d is SELECT customer_id,customer_name FROM customer_info;

BEGIN

      for a in c loop

            for b in d loop

                  IF a.customer_id = b.customer_id THEN

                        dbms_output.put_line(b.customer_name);

                  END IF;

            END loop;

      END loop;

END;

Output

Arvind Kejriwal

Wayne Rooney

Write PL/SQL to List of customers whose total billing amount is more than Rs.300.

CREATE or REPLACE function checkBill(customerId int) RETURN decimal

is

Calls meter_reading.current_month%type;

temp meter_reading.current_month%type;

serviceTax decimal(5,2);

rentCharges decimal(5,2);

totalAmount decimal(10,2);

d number;

BEGIN

      totalAmount := 0;

      SELECT count(*) INTO d FROM meter_reading WHERE customer_id = customerId;

      IF d>0 THEN

            BEGIN

                  SELECT current_month INTO Calls FROM meter_reading WHERE customer_id = customerId;

            END;

      ELSE

            BEGIN

                  dbms_output.put_line('No Bill for this month exists');

                  RETURN totalAmount;

            END;

      END IF;

      IF Calls > 300 THEN

      BEGIN

            temp := Calls - 300;

            totalAmount := totalAmount + temp*(1.2) + 250;

      END;

      elsif Calls > 50 THEN

      BEGIN

            totalAmount := totalAmount + Calls;

      END;

      END IF;

      rentCharges := 300;

      serviceTax := totalAmount*(0.12);

      totalAmount :=  totalAmount + serviceTax + rentCharges;

      RETURN totalAmount;

END;

CREATE or REPLACE procedure custName

is

cursor c is SELECT customer_id,customer_name FROM customer_info;

total decimal(10,2);

BEGIN

      for customer in c loop

            total := checkBill(customer.customer_id);

            IF total>500 THEN

                  dbms_output.put_line(customer.customer_name);

            END IF;

      END loop;

END;

Output

SQL> exec custName;

Deepanshu

John Cena

Narendra Modi

Write a PL/SQL procedure to find sum of meter readings of current month using for and while loop.

CREATE or REPLACE procedure sumRed

is

cursor c is SELECT current_month FROM meter_reading;

total number;

BEGIN

      total := 0;

      for totalsum in c loop

            total := total + totalsum.current_month;

      END loop;

      dbms_output.put_line('Sum is ' || total);

END;

Output

SQL> exec sumRed;

sum is 2357

Page updated

Google Sites

Report abuse