-- Exer19 SQL: Generate the Electricity Bill for one consumer.
-- Create a DATABASE with the name eb
create database eb;
-- open / use the database that you just created
use eb;
-- create a table in the database with the name eb_bill
CREATE TABLE `eb`.`eb_bill` (
`custno` VARCHAR(10) NOT NULL,
`custname` VARCHAR(45) NULL,
`billingdate` DATETIME NULL,
`units` INT NULL,
PRIMARY KEY (`custno`));
-- Check the Structure of the table just created
describe eb_bill;
-- Add two more fields to the table eb_bill
alter table eb_bill add (
bill_amount decimal(5,2),
duedate date
);
-- insert / add 10 records into the table eb_bill using the insert SQL command
INSERT INTO eb_bill(custno,custname,billingdate,units) VALUES ('EH 1003', 'ARUN KUMAR' ,'2022-03-16',98);
INSERT INTO eb_bill(custno,custname,billingdate,units) VALUES ('EH 2005', 'NAVEEN' ,'2022-04-16',108);
INSERT INTO eb_bill(custno,custname,billingdate,units) VALUES ('EH 2007','VARUN' ,'2022-02-16',157);
INSERT INTO eb_bill(custno,custname,billingdate,units)VALUES ('EH 3009', 'DAVID' ,'2022-04-16',77);
INSERT INTO eb_bill(custno,custname,billingdate,units) VALUES ('EH 3010', 'JHON' ,'2022-03-16',89);
INSERT INTO eb_bill(custno,custname,billingdate,units) VALUES ('EH 3013', 'AKSHAY' ,'2022-02-16',68);
INSERT INTO eb_bill(custno,custname,billingdate,units)VALUES ('EH 1010', 'CHANDRU' ,'2022-03-16',108);
INSERT INTO eb_bill(custno,custname,billingdate,units)VALUES ('EH 1008', 'GHANAVI' ,'2022-03-16',132);
INSERT INTO eb_bill(custno,custname,billingdate,units) VALUES ('EH 2105', 'DRUVA' ,'2022-03-16',87);
INSERT INTO eb_bill(custno,custname,billingdate,units) VALUES ('EH 3041', 'SHREYA' ,'2022-03-16',127);
-- See what data is in the table eb_bill now.
select * from eb_bill;
/*
Compute the bill amount for each customer as per the following rules.
a. MIN_AMT Rs. 50
b. First 100 units Rs 4.50/Unit
c. >100 units Rs. 5.50/Unit
*/
update eb_bill set bill_amount = 50 + units * 4.5 where units <=100;
update eb_bill set bill_amount = 50 + (100 * 4.5) + (units - 100) * 5.50 where units >100;
-- Compute due date as BILLING_DATE + 15 Days
update eb_bill set duedate = date_add(billingdate, interval 15 day);
-- List all the bills generated.
select * from eb_bill;
-- End of the exercise.
Comments
Post a Comment