SQL EXERCISE 1 : To generate electricity bill for five consumers.
1. Create a table with the following columns :
Field name Data type Description
RR_NO Varchar(2) Electric meter number
CUS_NAME Varchar(25) Consumer name
BILL_DATE Date Billing date
UNITS Number(4) Units consumed
2. Describe the structure of the table.
3. Insert five records into the table.
4. Add new columns to the table :
BILL_AMT int(10)
DUE_DATE date
5. Compute bill amount as per the following rules:
Minimum amount = Rs. 50
First 100 units = Rs 4.50 / unit
If units > 100 = Rs. 5.50 / unit
6. Compute the due date as billing date + 15 days
7. List all the bills generated.
Solution :
1. MySQL> CREATE TABLE ebill (rr_no varchar(2) , cus_name varchar(25) ,
bill_date date , units number(4));
2. MySQL> DESCRIBE ebill;
3. MySQL> INSERT INTO ebill VALUES(‘101’ , ‘AJAY’ , ’10-MAY-2020’);
Repeat the INSERT command for 5 records with different values
4. MySQL> ALTER TABLE ebill ADD (BILL_AMT number(6,2) , DUE_DATE
date);
5. MySQL> UPDATE ebill SET BILL_AMT = 50 + units*4.50 WHERE units
<= 100;
MySQL> UPDATE ebill SET BILL_AMT = 50 + 100*4.50 +
(units-100)*5.50 WHERE units > 100;
6. MySQL> UPDATE ebill SET DUE_DATE = BILL_DATE + 15;
7. MySQL> SELECT * FROM ebill;
—------------------------- X —----------------------
by