Project 2

Answer is attached below,

Â

SELECT *
FROM orders a
WHERE purch_amt >
(SELECT AVG(purch_amt) FROM orders b
WHERE b.customer_id = a.customer_id);

Â

SELECT SUM (month)
FROM orders;

Â

SELECT COUNT(cities,group,name of cities)
FROM customer;

CREATE TABLE `customer` (
`gender` varchar(20) NOT NULL,
`dob` int(20) NOT NULL,
`city` varchar(20) NOT NULL,
`pw` varchar(20) NOT NULL,
`name` varchar(20) NOT NULL,
`email` varchar(20) NOT NULL,
`delivery addresses` varchar(20) NOT NULL,
`contacts` int(20) NOT NULL,
`current` varchar(20) NOT NULL,
`orders` varchar(20) NOT NULL,
`payment` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `customer` (`gender`, `dob`, `city`, `pw`, `name`, `email`, `delivery addresses`, `contacts`, `current`, `orders`, `payment`) VALUES
('female', 24011999, 'Chennai', '600047', 'Swathy', '[email protected]', 'xyz', 893973033, 'Chennai', 'Zebronic', 'ONLINE MODE'),
('Male', 3082005, 'Bangalore', '600032', 'Venkat', '[email protected]', 'xrt', 734567973, 'Karnataka', 'Washing machine', 'Cash on delivery'),
('female', 22011999, 'Chennai', '600038', 'Vijayalakshmi', '[email protected]', 'Pudur', 954366408, 'Chennai', 'AC', 'ONLINE MODE'),
('Male', 6071999, 'Madurai', '600023', 'Devika', '[email protected]', 'YZX', 978675478, 'Madurai', 'Adidas', 'Online mode');

CREATE TABLE `discount` (
`discount` float NOT NULL,
`code` varchar(20) NOT NULL,
`status` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`id` int(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `discount` (`discount`, `code`, `status`, `email`, `id`) VALUES
(20, 'LM34D', 'Pending', '[email protected]', 123),
(50, 'AMS123', 'Done', '[email protected]', 267);

CREATE TABLE `order` (
`order status` varchar(255) NOT NULL,
`deliveryexpectation` varchar(255) NOT NULL,
`deliverydate` varchar(255) NOT NULL,
`orderdate` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `order` (`order status`, `deliveryexpectation`, `deliverydate`, `orderdate`) VALUES
('Completed', 'Packed', '22.08.2022', '19.08.2022'),
('Cancelled', 'Packed', '10.09.2022', '31.08.2022'),
('Completed', 'shipped', '03.09.2022', '28.08.2022');

CREATE TABLE `order1` (
`Shipment` varchar(255) NOT NULL,
`Invoices` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `order1` (`Shipment`, `Invoices`) VALUES
('Bike', 'Online mode'),
('Transport', 'Cash on Delivery');

CREATE TABLE `product` (
`Name` varchar(255) NOT NULL,
`Color` varchar(255) NOT NULL,
`Seller` varchar(255) NOT NULL,
`Size available` int(20) NOT NULL,
`Cost` int(20) NOT NULL,
`Current discount` int(20) NOT NULL,
`Orgin of delivery` varchar(255) NOT NULL,
`avgrating` int(20) NOT NULL,
`totalrating` int(20) NOT NULL,
`review rating` varchar(20) NOT NULL,
`review country` varchar(20) NOT NULL,
`Category` varchar(255) NOT NULL,
`Brand` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `product` (`Name`, `Color`, `Seller`, `Size available`, `Cost`, `Current discount`, `Orgin of delivery`, `avgrating`, `totalrating`, `review rating`, `review country`, `Category`, `Brand`) VALUES
('Zebronic', 'Black', 'Praveen', 255, 50000, 1500, 'Chennai\r\nBangalore', 10, 10, 'GOOD', 'Good', 'Hometheatre\r\nAC', 'SAMSUNG'),
('Grinder', 'Red', 'Venkat', 256, 90000, 2000, 'Chennai', 10, 10, 'GOOD', 'Good', 'Electronic\r\nHome applicance', 'Preethi');

CREATE TABLE `review` (
`Product` varchar(255) NOT NULL,
`Status` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `review` (`Product`, `Status`) VALUES
('Zebronic', 'Pending'),
('Air Conditioner', 'Approved');

CREATE TABLE `review1` (
`review_customer` varchar(255) NOT NULL,
`review_product` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `review1` (`review_customer`, `review_product`) VALUES
('Good', 'Zebronic'),
('Average', 'Air conditioner');

CREATE TABLE `seller` (
`seller name` varchar(255) NOT NULL,
`Mobile.no` int(20) NOT NULL,
`password` int(255) NOT NULL,
`Categories` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `seller` (`seller name`, `Mobile.no`, `password`, `Categories`) VALUES
(' Praveen', 954366089, 1234, 'Zebronic\r\nAC Conditioner'),
('Venkat', 735802089, 2345, 'Adidas\r\nGadgets');

CREATE TABLE `whislist` (
`id` int(11) NOT NULL,
`pid` int(11) NOT NULL,
`uid` int(11) NOT NULL,
`timestamp` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `whislist` (`id`, `pid`, `uid`, `timestamp`) VALUES
(1, 7, 41, '2022-08-15 21:39:04'),
(2, 8, 43, '2022-09-02 21:39:04');

ALTER TABLE `discount`
ADD PRIMARY KEY (`id`);

ALTER TABLE `whislist`
ADD PRIMARY KEY (`id`);
COMMIT;