D2_SaiRajesh_Rapelli_script
txt
keyboard_arrow_up
School
Arizona State University *
*We aren’t endorsed by this school
Course
620
Subject
Industrial Engineering
Date
Jan 9, 2024
Type
txt
Pages
6
Uploaded by ChancellorAtom12902
set serveroutput on;
--drop tables
drop table invoices;
drop table credit_cards;
drop table orders;
drop table products;
drop table product_categories;
drop table customers;
--create customers table
CREATE table customers (
cust_ID INT,
cust_name VARCHAR(100),
cust_city VARCHAR(100),
cust_email VARCHAR(100),
cust_state VARCHAR(100),
cust_zip NUMBER(5),
PRIMARY KEY (cust_ID)
);
--create product_categories table
CREATE table product_categories (
category_ID INT,
category_name VARCHAR(100),
category_description VARCHAR(100),
PRIMARY KEY (category_ID)
);
--create products table
CREATE table products (
prod_ID INT NOT NULL,
prod_name VARCHAR(100),
prod_quantity INT CONSTRAINT Valid_Quantity_Check CHECK (prod_quantity > 0),
prod_price FLOAT,
category_ID INT NOT NULL,
PRIMARY KEY (prod_ID),
FOREIGN KEY (category_ID) REFERENCES product_categories (category_ID)
);
--create orders table
CREATE table orders (
order_ID INT,
cust_ID INT,
prod_ID INT,
order_quantity INT,
order_date DATE,
PRIMARY KEY (order_ID),
FOREIGN KEY (cust_ID) REFERENCES customers(cust_ID),
FOREIGN KEY (prod_ID) REFERENCES products(prod_ID)
);
--create credit_cards table
CREATE table credit_cards (
creditcard_number INT NOT NULL,
cust_ID INT,
creditcard_type VARCHAR(100),
creditcard_expyear INT,
creditcard_expmonth INT,
PRIMARY KEY (creditcard_number),
FOREIGN KEY (cust_ID) REFERENCES customers(cust_ID)
);
--create invoices table
CREATE table invoices (
invoice_ID INT,
order_ID INT,
cust_ID INT,
creditcard_number INT,
invoice_amount INT,
PRIMARY KEY (invoice_ID),
FOREIGN KEY (order_ID) REFERENCES orders(order_ID),
FOREIGN KEY (cust_ID) REFERENCES customers(cust_ID),
FOREIGN KEY (creditcard_number) REFERENCES credit_cards(creditcard_number)
);
--Insert into customers tables
insert into customers values(1,'Preetham', 'Charloette','preetham@gmail.com', 'NC',
'21338');
insert into customers values(2,'Pranahith', 'Baltimore','pranahithbabu@gmail.com', 'MD', '21227');
insert into customers values(3,'Jaya Raj', 'New York','jayaraj@gmail.com', 'NY', '20904');
insert into customers values(4,'Sai Vikas', 'Phoenix', 'saivikas@gmail.com','AZ', '20850');
insert into customers values(5,'Sai Rajesh', 'Ellicott City','sairajesh@gmail.com',
'MD', '21043');
insert into customers values(6,'Ameenur Khan', 'Columbia', 'ameenurkhan@gmail.com','SC', '21044');
--Insert into product_categories tables
insert into product_categories values(1,'Clothing', 'Wide range of appareal suitable for daily wear');
insert into product_categories values(2,'Electronics','Smartphone,tablets,Computers
and laptops');
insert into product_categories values(3,'Footwear', 'Step into fashion and comfort with our diverse range of footwear');
insert into product_categories values(4,'Grocery', 'One step destination for essential food supplies');
insert into product_categories values(5,'Furniture', 'Transform your living space with our furniture collection');
--Insert into products tables
insert into products values(1, '40inchTV', 100, 350, 2);
insert into products values(2, 'T-shirt', 100, 30, 1);
insert into products values(3, 'Nike shoes', 100, 45, 3);
insert into products values(4, 'Sofa', 100, 1000, 5);
insert into products values(5, 'Milk', 100, 4, 4);
insert into products values(6, 'Chocolates', 100, 20, 4);
insert into products values(7, 'Laptop', 100, 700, 2);
insert into products values(8, 'Table', 100, 60, 5);
--Insert into credit_cards tables
insert into credit_cards values(44678, 1, 'VISA', 2028, 10);
insert into credit_cards values(44679, 2, 'VISA', 2029, 08);
insert into credit_cards values(44660, 3, 'VISA', 2030, 12);
insert into credit_cards values(44681, 4, 'VISA', 2028, 10);
insert into credit_cards values(47778, 5, 'AMEX', 2031, 10);
insert into credit_cards values(47781, 6, 'AMEX', 2028, 10);
insert into credit_cards values(58978, 1, 'DISCOVER', 2028, 10);
insert into credit_cards values(67085, 4, 'MASTERCARD', 2028, 10);
--################# HELPER FUNCTIONS ############
-- This PL/SQL function is designed to find a customer ID based on their email address.
create or replace function find_customer_id(v_email in customers.cust_email%type) return
customers.cust_id%type as
v_id customers.cust_id%type;
begin
--Implicit cursor to retirve value and store it in a variable
select cust_id into v_id from customers where cust_email=v_email;
--It returns the customer ID
return v_id;
exception
--When no rows returned, then this exception is executed
when no_data_found then
dbms_output.put_line('No customer found');
return -1;
end;
-- This PL/SQL function is designed to find a product ID based on the product name.
create or replace function find_product_id(v_pname in products.prod_name%type) return
products.prod_id%type as
v_id products.prod_id%type;
begin
--Implicit cursor to retirve value and store it in a variable
select prod_id into v_id from products where prod_name=v_pname;
--It returns the product ID
return v_id;
exception
--When no rows returned, then this exception is executed
when no_data_found then
dbms_output.put_line('No product found');
return -1;
end;
--############## MEMBER 4 #################
--############## DEPENDENT
--The Place Order process, owned by Member 3, initiates a call to the Update Inventory procedure, controlled by Member 2.
create or replace procedure update_inventory(v_pid in products.prod_id%type, v_pquant in INT)
is
var_availableQuantity int;
begin
--Implicit cursor to retirve value and store it in a variable
select prod_quantity into var_availableQuantity
from products where prod_ID=v_pid;
--Products table is updated with available stock
update products
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
set prod_quantity = var_availableQuantity-v_pquant
where prod_ID=v_pid;
--To save the database stage
commit;
dbms_output.put_line('Inventory Updated');
exception
--When no rows returned, then this exception is executed
when no_data_found then
dbms_output.put_line('Product not found');
when others then
dbms_output.put_line ('SQLCODE: ' || SQLCODE);
dbms_output.put_line ('SQLERRM: ' || SQLERRM);
end;
--Invoice_Customer (Belongs to Member 4, this prodcedure is invoked in Place_Order procedure)
--Sequence is used to increase the Order ID value automatically.
--The sequence is dropped first, then created. It starts with 1 and increases by 1.
drop sequence inv_seq_id;
create sequence inv_seq_id
start with 1
increment by 1;
--The PL/SQL procedure will generate a invoice when the customer place a order.
create or replace procedure invoice_customer(v_oId in invoices.order_ID%type,
v_cEmail in customers.cust_email%type,v_ccNum in invoices.creditcard_number%type,
v_invAmount in invoices.invoice_amount%type)
as
var_custId customers.cust_ID%type;
begin
--To check if customer exists or not
var_custId:=find_customer_id(v_cEmail);
if var_custId != -1 then
insert into invoices values(inv_seq_id.nextval, v_oId, var_custId, v_ccNum, v_invAmount);
dbms_output.put_line('Invoices created');
--To save the database stage
commit;
else
dbms_output.put_line('Customer not found');
end if;
exception
when others then
dbms_output.put_line ('SQLCODE: ' || SQLCODE);
dbms_output.put_line ('SQLERRM: ' || SQLERRM);
end;
--9) Place_Order
--Sequence is used for automatically incrementing the Order ID value.
--Sequence is dropped initially and then created. It starts with value 1 and increments by 1.
drop sequence ord_seq_id;
create sequence ord_seq_id
start with 1
increment by 1;
--This procedure places the order as directed by the customer.
create or replace procedure place_order(v_cemail in customers.cust_email%type,
v_pname in products.prod_name%type,v_pquant in products.prod_quantity%type,
v_ccnum in credit_cards.creditcard_number%type,v_odate orders.order_date%type)
as
var_cId customers.cust_ID%type;
var_pId products.prod_ID%type;
var_invAmount invoices.invoice_amount%type;
begin
--Calling respective helper functions to retrieve associated values
var_cId := find_customer_id(v_cemail);
var_pId := find_product_id(v_pname);
--Condition to check if both customer and products exists to place the order
if var_cId!=-1 and var_pId!=-1 then
--Inventory is updated with new quantity of associated product based on product
ID.
update_inventory(var_pId,v_pquant);
select prod_price*v_pquant into var_invAmount from products where prod_ID=var_pId;
insert into orders values(ord_seq_id.nextval,var_cId,var_pId,v_pquant,v_odate);
--Invoice generated under customer who is associated with the purchase
invoice_customer(ord_seq_id.currval,v_cemail,v_ccnum,var_invAmount);
--To save the database stage
commit;
dbms_output.put_line('Order placed');
else
dbms_output.put_line('Customer or Product doesnot exist');
end if;
exception
when others then
dbms_output.put_line ('SQLCODE: ' || SQLCODE);
dbms_output.put_line ('SQLERRM: ' || SQLERRM);
end;
--##########
--14) Report_Best_Customers
create or replace procedure report_best_customers(v_minAmount in number)
as
var_customer boolean;
begin
var_customer := false;
for i in (
select c.cust_name, sum(i.invoice_amount) as Total_Amount
from customers c, invoices i
where c.cust_id=i.cust_id
group by c.cust_name
having sum(i.invoice_amount)>v_minAmount
) loop
dbms_output.put_line('Customer Name: '||i.cust_name||', Total Amount Spent: '||
i.Total_Amount);
var_customer := true;
end loop;
if not var_customer then
dbms_output.put_line('There is no best customer');
end if;
end;
-- This PL/SQL statemts are before running final anonymous block
select * from products;
select * from orders;
select * from invoices;
--This is anonymous code to call procedure from member #3 begin
place_order('sairajesh@gmail.com','Nike shoes',2,47778,sysdate);
report_best_customers(50);
end;
--This PL/SQL tells after running the final anonymous block
select * from products;
select * from orders;
select * from invoices;
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help