Project 1

1

CREATE TABLE customers (
cust_id INT PRIMARY KEY,
cust_name VARCHAR(50) ,
cust_gender ENUM('M', 'F', 'O'),
phone VARCHAR(20),
email VARCHAR
);

CREATE TABLE products (
p_id INT PRIMARY KEY,
p_name VARCHAR(50),
p_price DECIMAL(10,2),
p_quantity INT NOT NULL,
p_color VARCHAR(20)
);

CREATE TABLE cart (
cart_id INT PRIMARY KEY,
no_of_products INT NOT NULL,
p_id INT NOT NULL,
FOREIGN KEY (p_id) REFERENCES products(p_id)
);

CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_name VARCHAR(50) NOT NULL
);

CREATE TABLE payment (
pay_id INT PRIMARY KEY,
pay_amount DECIMAL(10,2) NOT NULL,
cust_id INT NOT NULL,
FOREIGN KEY (cust_id) REFERENCES customer(cust_id)
);

2.

INSERT INTO customer (cust_id, cust_name, cust_gender, phone, email)
VALUES
(1, 'Raju', 'Male', '98049844', '[email protected]'),
(2, 'Jessy', 'Female', '456677', '[email protected]'),
(3, 'Boby', 'Male', '2255', '[email protected]');

INSERT INTO products (p_id, p_name, p_price, p_quantity, p_color)
VALUES
(1, 'T-Shirt', 20, 100, 'Red'),
(2, 'Jeans', 50, 50, 'Blue'),
(3, 'Sneakers', 100, 25, 'Black');

INSERT INTO cart (cart_id, no_of_products, p_id)
VALUES
(1, 2, 1),
(2, 1, 2),
(3, 3, 3);

INSERT INTO orders (order_id, order_name)
VALUES
(1, 'Order 1'),
(2, 'Order 2'),
(3, 'Order 3');

INSERT INTO payment (pay_id, pay_amount, cust_id)
VALUES
(1, 50.00, 1),
(2, 100.00, 2),
(3, 25.00, 3);

3.

1.SELECT *
FROM customer
ORDER BY city;

2.ALTER TABLE product
RENAME COLUMN p_id TO prod_p_id,
RENAME COLUMN p_name TO prod_p_name,
RENAME COLUMN p_price TO prod_p_price,
RENAME COLUMN p_quantity TO prod_p_quantity,
RENAME COLUMN p_color TO prod_p_color;

3.SELECT DISTINCT c.*
FROM customer c
JOIN cart ca ON c.cust_id = ca.cust_id;

4.SELECT c.*
FROM customer c
JOIN (
SELECT cust_id, COUNT(*) AS num_items
FROM cart
GROUP BY cust_id
HAVING num_items > 1
) ca ON c.cust_id = ca.cust_id
WHERE c.cust_name LIKE 'a%' AND c.cust_name LIKE '%a';

6

DELIMITER $$

CREATE PROCEDURE `run_all_statements`()
BEGIN
-- table creation statements
CREATE TABLE IF NOT EXISTS customer (
cust_id INT PRIMARY KEY,
cust_name VARCHAR(50),
cust_gender VARCHAR(10),
phone VARCHAR(20),
email VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS product (
p_id INT PRIMARY KEY,
p_name VARCHAR(50),
p_price DECIMAL(10,2),
p_quantity INT,
p_color VARCHAR(20)
);

CREATE TABLE IF NOT EXISTS cart (
cart_id INT PRIMARY KEY,
no_of_products INT,
p_id INT,
FOREIGN KEY (p_id) REFERENCES product(p_id)
);

CREATE TABLE IF NOT EXISTS order_table (
order_id INT PRIMARY KEY,
order_name VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS payment (
pay_id INT PRIMARY KEY,
pay_amount DECIMAL(10,2),
cust_id INT,
FOREIGN KEY (cust_id) REFERENCES customer(cust_id)
);

INSERT INTO customer (cust_id, cust_name, cust_gender, phone, email) VALUES
(1, 'John ', 'Male', '1234567890', '[email protected]'),
(2, 'Jessy', 'Female', '2345678901', '[email protected]');

INSERT INTO product (p_id, p_name, p_price, p_quantity, p_color) VALUES
(1, 'Product A', 10.00, 100, 'Red'),
(2, 'Product B', 15.00, 200, 'Green');

INSERT INTO cart (cart_id, no_of_products, p_id) VALUES
(1, 2, 1),
(2, 1, 2);

INSERT INTO order_table (order_id, order_name) VALUES
(1, 'Order 1'),
(2, 'Order 2');

INSERT INTO payment (pay_id, pay_amount, cust_id) VALUES
(1, 50.00, 1),
(2, 25.00, 2);

ALTER TABLE product RENAME COLUMN p_id TO prod_id;
ALTER TABLE product RENAME COLUMN p_name TO prod_name;
ALTER TABLE product RENAME COLUMN p_price TO prod_price;
ALTER TABLE product RENAME COLUMN p_quantity TO prod_quantity;
ALTER TABLE product RENAME COLUMN p_color TO prod_color;

END$$

DELIMITER ;

CALL run_all_statements();