Project 1

Ecommerce Database

Use ecommerce;

CREATE TABLE customer ( customer_id INT AUTO_INCREMENT, customer_name VARCHAR(255) NOT NULL, customer_email VARCHAR(255) NOT NULL, customer_password VARCHAR(255) NOT NULL, customer_address VARCHAR(255) NOT NULL, customer_city VARCHAR(255) NOT NULL, customer_state VARCHAR(255) NOT NULL, customer_zipcode VARCHAR(255) NOT NULL, customer_country VARCHAR(255) NOT NULL, customer_phone VARCHAR(255) NOT NULL, customer_creditcardtype VARCHAR(255) NOT NULL, customer_creditcardnumber VARCHAR(255) NOT NULL, customer_creditcardexpiration VARCHAR(255) NOT NULL, customer_datejoined DATE NOT NULL, customer_status VARCHAR(255) NOT NULL, customer_login_status VARCHAR(255) NOT NULL, customer_purchases INT NOT NULL, PRIMARY KEY (customer_id);

INSERT INTO customer (customer_name, customer_email, customer_password, customer_address, customer_city, customer_state, customer_zipcode, customer_country, customer_phone, customer_creditcardtype, customer_creditcardnumber, customer_creditcardexpiration, customer_datejoined, customer_status, customer_login_status, customer_purchases) VALUES ('John Doe', '[email protected]', 'password', '123 Main St', 'New York', 'NY', '10001', 'USA', '212-555-1212', 'Visa', '4111111111111111', '10/2022', '2020-01-01', 'active', 'logged-in', 0), ('Jane Doe', '[email protected]', 'password', '456 Elm St', 'Boston', 'MA', '02118', 'USA', '617-555-1212', 'MasterCard', '5555555555554444', '09/2021', '2020-01-02', 'active', 'logged-in', 0);

CREATE TABLE seller ( seller_id INT NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, store_name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL, phone VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (seller_id) );

INSERT INTO seller (name, email, password, store_name, address, phone) VALUES ('John Doe', '[email protected]', '123456', 'John Doe Store', '123 Main Street', '555-1212'), ('Jane Doe', '[email protected]', '123456', 'Jane Doe Store', '456 Main Street', '555-1212'), ('Mike Smith', '[email protected]', '123456', 'Mike Smith Store', '789 Main Street', '555-1212'), ('Sandy Smith', '[email protected]', '123456', 'Sandy Smith Store', '012 Main Street', '555-1212'), ('Bill Jones', '[email protected]', '123456', 'Bill Jones Store', '345 Main Street', '555-1212');

CREATE TABLE products ( id int NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, color varchar(255) NOT NULL, seller varchar(255) NOT NULL, sizesAvailable varchar(255) NOT NULL, quantityAvailable int NOT NULL, cost decimal(10,2) NOT NULL, currentDiscount decimal(10,2) NOT NULL, placeOfOriginForDelivery varchar(255) NOT NULL, calculations varchar(255) NOT NULL, isFeatured tinyint(1) NOT NULL, popularityIndex int NOT NULL, avgratings decimal(10,2) NOT NULL, totalratings int NOT NULL, reviewsForRatings varchar(255) NOT NULL, reviewCountry varchar(255) NOT NULL, isOnSale tinyint(1) NOT NULL, categories varchar(255) NOT NULL, brands varchar(255) NOT NULL, images varchar(255) NOT NULL ;

INSERT INTO products (name, color, seller, sizesAvailable, quantityAvailable, cost, currentDiscount, placeOfOriginForDelivery, calculations, isFeatured, popularityIndex, avgratings, totalratings, reviewsForRatings, reviewCountry, isOnSale, categories, brands, images) VALUES

('Product 1', 'Red', 'Seller 1', 'S, M, L', 10, 100.00, 10.00, 'USA', 'calc1', 1, 1, 4.5, 10, 'reviews1', 'USA', 1, 'cat1, cat2', 'brand1, brand2', 'image1.jpg, image2.jpg'),

('Product 2', 'Blue', 'Seller 2', 'S, M, L', 20, 200.00, 20.00, 'Canada', 'calc2', 0, 2, 4.0, 20, 'reviews2', 'Canada', 0, 'cat3, cat4', 'brand3, brand4', 'image3.jpg, image4.jpg'),

('Product 3', 'Green', 'Seller 3', 'S, M, L', 30, 300.00, 30.00, 'Mexico', 'calc3', 1, 3, 3.5, 30, 'reviews3', 'Mexico', 1, 'cat5, cat6', 'brand5, brand6', 'image5.jpg, image6.jpg');

CREATE TABLE orders ( order_id int Auto increment, status varchar(255), packed varchar(255), shipment varchar(255), delivery_date varchar(255), delivery_expectation varchar(255), order_date varchar(255), customer_id INT References customer(customer_id), PRIMARY KEY (order_id) );

insert into orders values (1, 'delivered', 'yes', 'yes', '2019/01/01', '2019/01/05', '2019/01/01', 1), insert into orders values (2, 'delivered', 'yes', 'yes', '2019/01/03', '2019/01/05', '2019/01/01', 2), insert into orders values (3, 'delivered', 'yes', 'yes', '2019/01/05', '2019/01/05', '2019/01/01', 3);

Ans 3(1)-

SELECT * FROM customers ORDER BY city;

Ans 3(2)-

ALTER TABLE product RENAME COLUMN name TO prod_name; ALTER TABLE product RENAME COLUMN description TO prod_description; ALTER TABLE product RENAME COLUMN price TO prod_price; ALTER TABLE product RENAME COLUMN category TO prod_category;

Ans 3(3)-

SELECT c.id, c.name, c.email, c.created_at, c.updated_at FROM customers c JOIN orders o ON c.id = o.customer_id JOIN order_items oi ON o.id = oi.order_id GROUP BY c.id HAVING count(oi.product_id) > 0;

Ans 3(4) -

SELECT customer_name, count(*) as num_items FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE customer_name LIKE 'A%' AND customer_name LIKE '%A' GROUP BY customer_name HAVING num_items > 1;

Ans 3(5) -

SELECT * FROM orders WHERE delivered = 1 AND cost > 10000 AND num_customers > 1;

Ans 3(6) -

1.Open the SQL script file in a text editor.

2. Select all the text in the file.
3. Copy the selected text.
4. Open the MySQL console.
5. Paste the copied text into the console.
6. Press Enter.