Task 1. All SQL files are attached below.
Task 2. Data are inserting as per structure.
Customer Table:
CREATE TABLE `customer` (
`Customer_id` varchar(10) NOT NULL Primary key,
`Name` varchar(200) NOT NULL,
`email` varchar(100) NOT NULL,
`gender` varchar(50) NOT NULL,
`dob` datetime NOT NULL,
`city` varchar(100) NOT NULL,
`diliveryAddress` varchar(500) NOT NULL,
`contact` int(11) NOT NULL,
`Currentorder` varchar(100) NOT NULL,
`PrevOrder` varchar(100) NOT NULL,
`Cartid` varchar(100) DEFAULT NULL
);
INSERT INTO `customer` (`Customer_id`, `Name`, `email`, `gender`, `dob`, `city`, `diliveryAddress`, `contact`, ` Currentorder`, `PrevOrder`, `Cartid`) VALUES
('0023pkr', 'Aishwarya', '
[email protected]', 'female', '2003-11-14 15:23:33', 'mumbai', 'dadar', 888859944, 'Dress', 'lehanga', 'c_105'),
('B12kr', 'Abhi', '
[email protected]', 'male', '1995-11-02 15:19:36', 'Bangalore', 'bangalore', 988849445, 'Headphone', 'Shirts', 'c_103'),
('G_235', 'Ganesh', '
[email protected]', 'male', '2022-11-05 08:11:53', 'Pune', 'Hadapsar Pune', 456777653, 'T_Shirt', 'Cap', 'c_102'),
('rtlel', 'vikas', '
[email protected]', 'm', '2022-11-20 18:38:08', 'latur', 'latur', 987987, 'pen', 'book', '234'),
('Tj402', 'Vankatesh', 'vanki@
[email protected]', 'male', '2002-11-21 15:19:36', 'Chennai', 'chennai', 78906483, 'Homethreater', 'sounds', 'c_104'),
('V_Patil', 'Vaibhav', '
[email protected]', 'Male', '2000-11-12 12:41:53', 'Nanded', 'Latur', 845905668, 'Macbook', 'HP Laptop', 'c_101');
Seller Table :
CREATE TABLE `seller` (
`sellerid` varchar(100) NOT NUL Primary key,
`Name` varchar(200) NOT NULL,
`Mobileno` int(11) NOT NULL,
`password` varchar(200) NOT NULL,
`ProductCategory` varchar(100) NOT NULL
);
INSERT INTO `seller` (`sellerid`, `Name`, `Mobileno`, `password`, `ProductCategory`) VALUES
('er455k', 'Jully', 59545554, 'li9345n', 'Accessories'),
('kjhkj54', 'Danni', 345665656, 'ggj54t02', 'Laptop'),
('S-66jd', 'Javed', 483595847, 'ghd3435j', 'fashion'),
('S111_MH', 'Abhi', 8549505, 'V4590nd', 'Accessories'),
('S123_KR', 'Vinay', 84564865, '90hf3e', 'Phones');
Cart Table :
CREATE TABLE `cart` (
`Cartid` varchar(100) NOT NULL Primary key,
`Itemquantity` int(11) NOT NULL,
`Total_cost` int(11) NOT NULL
);
INSERT INTO `cart` (`Cartid`, `Itemquantity`, `Total_cost`) VALUES
('c_101', 20, 3000),
('c_102', 10, 1200),
('c_103', 2, 200),
('c_104', 5, 2000),
('c_105', 10, 70000);
Cart items Table:
CREATE TABLE `cartitem` (
`Cartid` varchar(100) NOT NULL Primary key,
`Productid` varchar(100) NOT NULL,
`DateAdded` date NOT NULL,
`purchased` varchar(3) DEFAULT 'NO',
`itemquantity` int(11) DEFAULT NULL,
`customerid` varchar(100) DEFAULT NULL
);
ALTER TABLE `cartitem`
ADD CONSTRAINT `cartitem_ibfk_1` FOREIGN KEY (`Cartid`) REFERENCES `cart` (`Cartid`),
ADD CONSTRAINT `cartitem_ibfk_2` FOREIGN KEY (`Productid`) REFERENCES `product` (`Productid`);
INSERT INTO `cartitem` (`Cartid`, `Productid`, `DateAdded`, `purchased`, `itemquantity`, `customerid`) VALUES
('c_101', 'P_3', '2022-11-07', 'NO', 7, '023pkr'),
('c_102', 'P_1', '2022-11-22', 'y', 3, 'B12kr'),
('c_103', 'P_1', '2022-10-19', 'Y', 5, 'V_patil'),
('c_104', 'P_3', '2022-11-03', 'Y', 6, 'G235'),
('c_105', 'P_1', '2022-11-01', 'NO', 3, 'Tj402');
Product Table :
CREATE TABLE `product` (
`Productid` varchar(50) NOT NULL Primary key,
`Name` varchar(200) NOT NULL,
`color` varchar(50) DEFAULT NULL,
`sellerid` varchar(100) NOT NULL,
`size` int(11) NOT NULL,
`quantity` int(11) NOT NULL,
`cost` int(11) NOT NULL,
`discount` int(11) NOT NULL,
`placefordelivery` varchar(200) NOT NULL,
`isFeatured` varchar(10) NOT NULL,
`rating` int(11) NOT NULL,
`reviews` int(11) NOT NULL,
`isOnScale` varchar(100) NOT NULL,
` Category_id` varchar(100) NOT NULL,
`brand` varchar(100) NOT NULL,
` description` varchar(300) NOT NULL,
` tags` varchar(100) NOT NULL,
` relatedproduct` varchar(300) NOT NULL
);
ALTER TABLE `product`
ADD CONSTRAINT `product_ibfk_1` FOREIGN KEY (`sellerid`) REFERENCES `seller` (`sellerid`);
INSERT INTO `product` (`Productid`, `Name`, `color`, `sellerid`, `size`, `quantity`, `cost`, `discount`, `placefordelivery`, `isFeatured`, `rating`, `reviews`, `isOnScale`, ` Category_id`, `brand`, ` description`, ` tags`, ` relatedproduct`) VALUES
('Hag3943r', 'Rebion Goggals', 'red-black', 'S123_KR', 0, 1, 2000, 300, 'Latur', 'yes', 5, 4, 'yes', 'cat_291', 'Rebion', 'sunlight glasses', 'goggles', 'yellow glasses'),
('krt455', 'T_Shirt', 'Yellow', 'S111_MH', 42, 2, 3000, 200, 'Nanded', '', 4, 8, 'yes', 'Fashion', 'being human', 'T_shirt with cap', '', 'being humans jeans'),
('P_1', 'Macbook', 'black', 'S123_KR', 14, 1, 120000, 7000, 'pune', 'yes', 5, 40, 'very good product', 'C_301', 'Apple', 'Product is very branded and good', 'Laptop', 'IPad'),
('P_3', 'Vivo_30', 'silver', 'S111_MH', 20, 2, 40000, 2000, 'Latur', 'Welll feat', 7, 20, 'well developed', 'Phones', 'Vivo', 'Very good phone camera 64 ram 8 gb', 'vivo', 'vivo-28'),
('P_490g', 'headphone', 'white', 'S111_MH', 0, 3, 700, 110, 'pune', 'yes', 5, 5, 'yes', 'cat_285', 'realme', 'white headphones', 'realme', 'realme ipods');
Category Table :
CREATE TABLE `category` (
`category_id` varchar(100) NOT NULL Primary key,
`categoryname` varchar(100) NOT NULL,
`Productid` varchar(100) NOT NULL,
`subCategory` varchar(100) NOT NULL
);
ALTER TABLE `category`
ADD CONSTRAINT `category_ibfk_1` FOREIGN KEY (`Productid`) REFERENCES `product` (`Productid`);
INSERT INTO `category` (`category_id`, `categoryname`, `Productid`, `subCategory`) VALUES
('cat_285', 'Accessories', 'P_1', 'Headphones'),
('cat_288', 'Laptops', 'P_1', 'Apple'),
('cat_291', 'Accessories', 'P_3', 'Phones'),
('cat_299', 'Phones', 'P_3', 'Vivo'),
('cat_301', 'Fashion', 'P_3', 'Mens');
Orders Table :
CREATE TABLE `orders` (
`orderid` varchar(100) NOT NULL Primary key,
`status` varchar(100) NOT NULL,
`orderdate` date NOT NULL,
`deliverydate` date NOT NULL,
`customerid` varchar(100) DEFAULT NULL,
`productid` varchar(100) DEFAULT NULL
);
ALTER TABLE `orders`
ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customerid`) REFERENCES `customer` (` Customer_id`);
INSERT INTO `orders` (`orderid`, `status`, `orderdate`, `deliverydate`, `customerid`, `productid`) VALUES
('02341mrk', 'shipping', '2022-11-05', '2022-11-08', 'V_Patil', 'krt455'),
('09hjwe3', 'delivered', '2022-11-01', '2022-11-05', '0023pkr', 'P_1'),
('1567o3d', 'delivered', '2022-11-02', '2022-11-04', 'G_235', ''),
('klr892', 'dileverd soon', '2022-11-04', '2022-11-06', 'B12kr', 'P_3'),
('ormh26', 'shiiping', '2022-11-02', '2022-11-07', 'Tj402', '');
Payments Table :
CREATE TABLE `payment` (
`paymentid` varchar(100) NOT NULL Primary key,
`paymentdate` date NOT NULL,
`Paymenttype` varchar(200) NOT NULL,
`Customerid` varchar(100) NOT NULL,
`Cartid` varchar(100) DEFAULT NULL
);
ALTER TABLE `payment`
ADD CONSTRAINT `payment_ibfk_1` FOREIGN KEY (`Customerid`) REFERENCES `customer` (` Customer_id`),
ADD CONSTRAINT `payment_ibfk_2` FOREIGN KEY (`Cartid`) REFERENCES `cart` (`Cartid`);
INSERT INTO `payment` (`paymentid`, `paymentdate`, `Paymenttype`, `Customerid`, `Cartid`) VALUES
('0023449', '2022-11-03', 'reference-0394j34', 'B12kr', 'c_104'),
('0294738484', '2022-11-01', 'UPI', '0023pkr', 'c_102'),
('488443565', '2022-11-02', 'phonepay', 'G_235', 'c_103'),
('4930980', '2022-11-04', 'bharatPay', 'V_Patil', 'c_101'),
('9849344', '2022-11-04', 'upi', 'Tj402', 'c_104');
Task 3. Write a sql queries for below.
1. WAQ to fetch the details of the customers in the order of their city.
==> SELECT * FROM `customer` ORDER BY `customer`.`city` ASC
2. WAQ to alter the table product by renaming all the columns by precending them with 'prod_'.
==> ALTER TABLE `product` RENAME COLUMN `productid` `prod_productid`;
ALTER TABLE `product` RENAME COLUMN `Name` `prod_Name`;
ALTER TABLE `product` RENAME COLUMN `color` `prod_color`;
ALTER TABLE `product` RENAME COLUMN `sellerid` `prod_sellerid`;
ALTER TABLE `product` RENAME COLUMN `size` `prod_size`;
ALTER TABLE `product` RENAME COLUMN `cost` `prod_cost`;
ALTER TABLE `product` RENAME COLUMN `discount` `prod_discount`;
ALTER TABLE `product` RENAME COLUMN `placefordelivery` `prod_placefordelivery`;
ALTER TABLE `product` RENAME COLUMN `isfeatured` `prod_isfeatured`;
ALTER TABLE `product` RENAME COLUMN `rating` `prod_rating`;
ALTER TABLE `product` RENAME COLUMN `reviews` `prod_reviews`;
ALTER TABLE `product` RENAME COLUMN `isOnScale` `prod_isOnScale`;
ALTER TABLE `product` RENAME COLUMN `category_id` `prod_category_id`;
ALTER TABLE `product` RENAME COLUMN `brand` `prod_brand`;
ALTER TABLE `product` RENAME COLUMN `description` `prod_description`;
ALTER TABLE `product` RENAME COLUMN `tags` `prod_tags`;
ALTER TABLE `product` RENAME COLUMN `relatedproducts` `prod_relatedproducts`;
3. WAQ to fetch the details of customer who have purchased at least one product.
==> SELECT * FROM `customer`AS c INNER JOIN cartitem AS ct ON c.Customer_id=ct.customerid WHERE ct.purchased="y" AND ct.itemquantity>1;
4. WAQ to fetch the details of the customer who have name starting from A and ending from A and has purchased more than one item.
==> SELECT * FROM `customer`AS c INNER JOIN cartitem AS ct ON c.Customer_id=ct.customerid WHERE ct.purchased="y" AND ct.itemquantity>1 AND c.name=(SELECT Name FROM customer WHERE Name LIKE "A%A");
5.WAQ to all the information about the orders which are already delivered and has cost more than 10000 and also has been purchased by more than 1 customer.
==> SELECT o.orderid, o.orderdate,o.status,o.deliverydate,p.Name,p.cost, COUNT(customerid) as customers_purchased FROM `orders` o INNER JOIN product p ON o.productid=p.prod_Productid WHERE o.status="delivered" AND p.cost>10000 GROUP BY customerid;
6. Write a simple procedure to run all the tables creation statements,insrt statements and alter statements at once.
==>
ALTER PROCEDURE sqloperations (
@Tablename varchar(10)='',
@`Customer_id` varchar(10) NOT NULL Primary key,
@`Name` varchar(200) NOT NULL,
@`email` varchar(100) NOT NULL,
@`gender` varchar(50) NOT NULL,
@`dob` datetime NOT NULL,
@`city` varchar(100) NOT NULL,
@`diliveryAddress` varchar(500) NOT NULL,
@`contact` int(11) NOT NULL,
@`Currentorder` varchar(100) NOT NULL,
@`PrevOrder` varchar(100) NOT NULL,
@`Cartid` varchar(100) DEFAULT NULL
@`sellerid` varchar(100) NOT NUL Primary key,
@`Name` varchar(200) NOT NULL,
@`Mobileno` int(11) NOT NULL,
@`password` varchar(200) NOT NULL,
@`ProductCategory` varchar(100) NOT NULL
@`Cartid` varchar(100) NOT NULL Primary key,
@`Itemquantity` int(11) NOT NULL,
@`Total_cost` int(11) NOT NULL
) AS BEGIN IF @Tablename='customer' BEGIN INSERT INTO customer (`Customer_id`, `Name`, `email`, `gender`, `dob`, `city`, `diliveryAddress`, `contact`, ` Currentorder`, `PrevOrder`, `Cartid`) VALUES
( @`Customer_id`, @`Name`, @`email`, @`gender`, @`dob`, @`city`, @`diliveryAddress`, @`contact`, @`Currentorder`, @`PrevOrder`, @`Cartid`) END BEGIN SELECT * FROM
customer END BEGIN UPDATE customer SET first_name = @first_name,
last_name = @last_name, salary = @salary, city = @city WHERE customerid = @customerid END ELSE BEGIN DELETE FROM customer WHERE customerid = @customerid END
IF @Tablename='customer' BEGIN INSERT INTO seller (`sellerid`, `Name`, `Mobileno`, `password`, `ProductCategory`) VALUES
( @`sellerid`, @`Name`, @`Mobileno`, @`password`, @`ProductCategory`) END BEGIN SELECT * FROM
seller END BEGIN UPDATE seller SET first_name = sellerid= @`sellerid`, name=@`Name`, mobileno= @`Mobileno`, password=@`password`, productcategory=@`ProductCategory` WHERE sellerid = @sellerid END ELSE BEGIN DELETE FROM seller WHERE id = @id END
IF @Tablename='cartid' BEGIN INSERT INTO cartid (`Cartid`, `Itemquantity`, `Total_cost`) VALUES
( @`Cartid`, @`Itemquantity`, @`Total_cost`) END BEGIN SELECT * FROM
cartid END BEGIN UPDATE cartid SET first_name = cartid=@`Cartid`, itemquantity=@`Itemquantity`, totalcost=@`Total_cost` WHERE id = @id END ELSE BEGIN DELETE FROM cartid WHERE id = @id END
END