Project 1
PROJECT 1 - WORKSHOP 2 (MYSQL)
TASK 1:- Task 1 in which we have to draw io diagram...And solution of this question is given in pdf format file whis is attached below in file box.
TASK 2:- In this task 2 it contain all SQL queries for creatin and inserting in our Ecommerce database tables and it is given below
USE dbmsworkshop;
CREATE TABLE customers(
Customer_Id INT,
CustomerName VARCHAR(20),
phone INT,
addressLine VARCHAR(100),
city VARCHAR(20),
state VARCHAR(20),
postalCode INT(10),
country VARCHAR(20),
creditLimit INT,
PRIMARY KEY(Customer_id)
);
DROP TABLE customers;
INSERT INTO customers(Customer_id,CustomerName,phone,addressLine,city,state,postalCode,country,creditLimit)
VALUES(101,'Ram',90001,'house_no_50 ram naghar','delhi','delhi',110001,'India',50000),
(102,'Shyam',90002,'house_no_51 shyam naghar','delhi','delhi',110001,'India',50000),
(103,'Mohan',90003,'house_no_52 mohan naghar','delhi','delhi',110001,'India',50000),
(104,'Shoan',90004,'house_no_53 shoan naghar','delhi','delhi',110001,'India',50000);
SELECT*FROM customers;
DESCRIBE customers;
CREATE TABLE products(
product_id INT,
productName VARCHAR(50),
productScale VARCHAR(50),
quantityInStock INT,
buyPrice INT,
MSRP INT,
PRIMARY KEY(products_id)
);
DROP TABLE products;
INSERT INTO products(product_Id,productName,productScale,quantityInStock,buyPrice,MSRP)
VALUES(301,'fan','4kg',500,1700,2500),
(302,'toy','light_weight',1000,500,800),
(303,'shirt','XL',200,400,700),
(304,'pen','smooth writing',2000,10,15);
SELECT*FROM products;
DESCRIBE products;
CREATE TABLE orders(
order_id INT,
customer_id INT,
order_Number INT,
Payment_id INT,
order_date DATE,
ship_date DATE,
shipper_id INT,
TransactStatus VARCHAR(10),
Paid INT,
Payment_date DATE,
PRIMARY KEY(orders_id)
);
DESCRIBE orders;
DROP TABLE orders;
INSERT INTO orders(order_id,customer_id,order_Number,Payment_id,order_date,ship_date,shipper_id,
TransactStatus,Paid,Payment_date)
VALUES(201,101,1001,501,'2022-08-23','2022-08-24',601,'sucessful',500,'2022-08-26'),
(202,102,1002,502,'2022-08-25','2022-08-26',602,'sucessful',1000,'2022-08-28'),
(203,103,1003,503,'2022-06-23','2022-06-24',603,'sucessful',200,'2022-06-26'),
(204,104,1004,504,'2022-08-23','2022-08-24',604,'sucessful',5000,'2022-08-26');
SELECT*FROM orders;
CREATE TABLE payment(
payment_id VARCHAR(20),
Payment_type VARCHAR(10),
Allowed VARCHAR(10),
PRIMARY KEY(payment_id)
);
DROP TABLE payment;
INSERT INTO payment(Payment_id,Payment_type,allowed)
VALUES(501,'online','debit_card'),
(502,'online','debit_card'),
(503,'online','credit_card'),
(504,'online','debit_card');
SELECT*FROM payment;
DESCRIBE payment;
CREATE TABLE shipper(
shipper_id INT,
companyName VARCHAR(50),
phone INT,
PRIMARY KEY(shipper_id)
);
INSERT INTO shipper(shipper_id,companyName,phone)
VALUES(601,'abc','222324'),
(601,'bcd','222325'),
(601,'cde','222326'),
(601,'def','222327');
SELECT*FROM shipper;
DESCRIBE shipper;
CREATE TABLE supplier(
supplier_id INT,
companyName VARCHAR(50),
itemName VARCHAR(50),
Address VARCHAR(100),
city VARCHAR(20),
state VARCHAR(20),
postalCode INT,
country VARCHAR(20),
phone INT,
Email VARCHAR(20),
PaymentMethod VARCHAR(20),
customer_id INT,
PRIMARY KEY(supplier_id)
);
DROP TABLE supplier;
DESCRIBE supplier;
INSERT INTO supplier(supplier_id,companyName,itemName,address,city,state,postalCode,
country,phone,Email,PaymentMethod,customer_id)
VALUES(901,'abc','fan','50_ram_naghar','delhi','delhi','110001','India','800001','[email protected]','online','201'),
(902,'bcd','toy','52_shyam_naghar','delhi','delhi','110001','India','800001','[email protected]','online','202'),
(903,'cde','shirt','53_mohan_naghar','delhi','delhi','110001','India','800001','[email protected]','online','203'),
(904,'abc','pen','50_ram_naghar','delhi','delhi','110001','India','800001','[email protected]','online','204');
SELECT*FROM supplier;
CREATE TABLE OrderDetails(
order_id INT,
product_id INT,
orderNumber INT,
price INT,
quantity INT,
total INT,
size VARCHAR(20),
colour VARCHAR(20),
shipDate DATE,
billDate DATE,
FOREIGN KEY(order_id) REFERENCES orders(order_id),
FOREIGN KEY(product_id) REFERENCES products(product_id)
);
DROP TABLE OrderDetails;
INSERT INTO OrderDetails(order_id,product_id,orderNumber,price,quantity,total,size,colour,shipDate,billDate)
VALUES(201,301,401,200,5,1000,'M','red','2022-05-23','2022-05-23'),
(202,302,402,100,2,200,'M','red','2022-05-23','2022-05-23'),
(203,303,403,300,10,3000,'M','red','2022-05-23','2022-05-23'),
(204,304,404,500,3,1500,'M','red','2022-05-23','2022-05-23');
SELECT*FROM OrderDetails;
DESCRIBE OrderDetails;
TASK 3 :-Write the SQL queries for the given scenarios in Task 3 .
QUE 1.
SOLUTION :-
SELECT customers.city FROM customers
INNER JOIN orders
ON customers.customer_id=orders.order_id;
QUE 2.
SOLUTION :-
ALTER TABLE products
CHANGE product_id prod_id INT;
ALTER TABLE products
CHANGE productName prod_Name VARCHAR(50);
ALTER TABLE products
CHANGE productScale prod_Scale VARCHAR(50);
QUE 3.
SOLUTION :-
SELECT customers.customer_id
FROM customers
RIGHT OUTER JOIN orders
ON customers.customer_id=orders.order_id
WHERE orders.order_id>=1;
QUE 4.
SOLUTION :-
SELECT customers.customer_id,customers.customerName FROM customers
INNER JOIN orders ON customers.customer_id=orders.order_id
WHERE customers.customerName LIKE'a%a'
AND orders.order_id>1;
QUE 5.
SOLUTION :- SELECT orders.order_id,orders.customer_id,orders.order_Number,orders.Payment_id,orders.
order_date,orders.ship_date,orders.ship_date,orders.shipper_id,orders.TransactStatus,
orders.Paid,orders.Payment_date
FROM orders
LEFT OUTER JOIN customers
ON customers.customer_id=orders.order_id
WHERE orders.Paid > 10000
AND customers.customer_id>1;
QUE 6.
SOLUTION :-
DELIMITER $$
PROCEDURE GetCustomersNew()
BEGIN
CREATE TABLE customers(
Customer_Id INT,
CustomerName VARCHAR(20),
phone INT,
addressLine VARCHAR(100),
city VARCHAR(20),
state VARCHAR(20),
postalCode INT(10),
country VARCHAR(20),
creditLimit INT
);
INSERT INTO customers VALUES(105,'Ram',90005,'house_no_50 ram
naghar','delhi','delhi',110001,'India',50000);
ALTER TABLE customers
CHANGE customerName Cust_Name VARCHAR(50);
END$$
DELIMITER;
CALL GetCustomersNew();
SELECT*FROM customers;
DROP PROCEDURE GetCustomersNew;
DELIMITER $$
CREATE PROCEDURE GetProductsNew()
BEGIN
CREATE TABLE products(
product_id INT,
productName VARCHAR(50),
productScale VARCHAR(50),
quantityInStock INT,
buyPrice INT,
MSRP INT
);
INSERT INTO products VALUES(306,'fan','4kg',600,1700,2400);
ALTER TABLE products
CHANGE productScale prod_Scale VARCHAR(50);
END$$
DELIMITER;
CALL GetProductsNew();
SELECT*FROM Products;
DROP PROCEDURE GetProductsNew;