ACTIVITY 2 FINALS
CPC223(A) – DATABASE PROGRAMMING
2nd Semester A.Y. 2024-2025
Name Naguit, Hurjay Ali D.
Score
Year & Section BSIT 2-I Date 2025/ 05 / 07
Directions for Lab Exercise: Converting Tables to Queries on your paper. (Individual work)(50 points)a
Laboratory Exercise: MySQL Workbench - Stored Procedures
Title: Create Stored Procedure for Insert, Update, Delete and Join Operations
Objective:
You are managing a database for an e-commerce company that sells products online. The database has the following two tables:
Exercise Questions
Scenario:
Scenario:
You are managing a database for an e-commerce company that sells products online. The database has the following two tables:
1. Customers
● customer_id (Primary Key)
● first_name
● last_name
● email
● phone_number
● address
2. Orders
● order_id (Primary Key)
● customer_id (Foreign Key)
● order_date
● order_amount
● status (Pending, Completed, Canceled)
Tasks:
Task 1: Create a Stored Procedure to Select All Customers
Question: Create a stored procedure to select all customer records from the Customers table. Ensure that you can execute the procedure and display the
results.
Task 2: Create a Stored Procedure to Insert a New Order
Question: Create a stored procedure to insert a new order into the Orders table. The procedure should accept the customer_id, order_date, order_amount,
and status as parameters.
Task 3: Create a Stored Procedure to Update Order Status
Question: Create a stored procedure to update the status of an order. The procedure should accept order_id and new_status as parameters.
Task 4: Create a Stored Procedure to Delete an Order
Question: Create a stored procedure to delete an order based on order_id. The procedure should remove an order from the Orders table.
Task 5: Create a Stored Procedure with a JOIN Query
Question: Create a stored procedure that joins the Customers and Orders tables to display customer information along with their order details (order date,
amount, and status)
Summary of Tasks:
● Task 1: Create a stored procedure to select all customers.
● Task 2: Create a stored procedure to insert a new order.
● Task 3: Create a stored procedure to update an order status.
● Task 4: Create a stored procedure to delete an order.
● Task 5: Create a stored procedure that joins customer and order details.
CREATE DATABASE myDb;
USE myDb;
CREATE TABLE Customers )(
customer_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
phone_number VARCHAR(20),
address TEXT);
CREATE TABLE Orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
order_date DATE,
order_amount DECIMAL(10, 2),
status ENUM('Pending', 'Completed', 'Canceled'),
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id));
SHOW TABLES;
INSERT INTO Customers (first_name, last_name, email, phone_number, address)
VALUES
('John', 'Doe', '[email protected]', '555-1234', '123 Main St, Springfield'),
('Jane', 'Smith', '[email protected]', '555-5678', '456 Oak St, Springfield');
INSERT INTO Orders (customer_id, order_date, order_amount, status)
VALUES
(1, '2025-05-01', 200.50, 'Pending'),
(2, '2025-05-02', 350.00, 'Completed');
DELIMITER //
CREATE PROCEDURE SelectAllCustomers()
BEGIN
SELECT * FROM Customers;
END //
DELIMITER ;
CALL SelectAllCustomers();
DELIMITER //
CREATE PROCEDURE InsertNewOrder(
IN p_customer_id INT,
IN p_order_date DATE,
IN p_order_amount DECIMAL(10, 2),
IN p_status ENUM('Pending', 'Completed', 'Canceled'))
BEGIN
INSERT INTO Orders (customer_id, order_date, order_amount, status)
VALUES (p_customer_id, p_order_date, p_order_amount, p_status);
END //
DELIMITER ;
CALL InsertNewOrder(1, '2025-05-07', 250.75, 'Pending');
DELIMITER //
CREATE PROCEDURE UpdateOrderStatus(
IN p_order_id INT,
IN p_new_status ENUM('Pending', 'Completed', 'Canceled'))
BEGIN
UPDATE Orders
SET status = p_new_status
HERE order_id = p_order_id;
END //
DELIMITER ;
CALL UpdateOrderStatus(1, 'Completed');
DELIMITER //
CREATE PROCEDURE DeleteOrder(
IN p_order_id INT)
BEGIN
DELETE FROM Orders
WHERE order_id = p_order_id;
END //
DELIMITER ;
CALL DeleteOrder(1);
DELIMITER //
CREATE PROCEDURE GetCustomerOrderDetails()
BEGIN
SELECT
c.customer_id, c.first_name, c.last_name,c.email,c.phone_number,c.address,o.order_id,o.order_date,o.order_amount,o.status FROM
Customers c
JOIN Orders o ON c.customer_id = o.customer_id;
END //
DELIMITER ;
