Project 1
please find my answer in the attatched word
..........................
Task1:
-----------------------------------------------------------------------------------------
Task 2:
CREATE DATABASE project1;
USE project1;
CREATE TABLE `discount` (
`discount` FLOAT DEFAULT NULL,
`code` VARCHAR(20) NOT NULL,
`status` VARCHAR(100) DEFAULT NULL,
`email` VARCHAR(50) DEFAULT NULL,
`id` INT(11) DEFAULT NULL
);
INSERT INTO `discount`
(`discount`, `code`, `status`, `email`, `id`)
VALUES
(30, 'AAA123', 'Done ', [email protected]', 267),
(20, '34DBB', 'pending ', '[email protected]', 123);
SELECT * FROM discount;
CREATE TABLE `orderl` (
`Shipment` VARCHAR(255) NOT NULL,
`Invoices` VARCHAR(255) NOT NULL
);
INSERT INTO `orderl`
(`Shipment`, `Invoices`)
VALUES
('Bike', 'online mode'),
('Tansport', 'Cash on Delivery');
SELECT * FROM orderl;
CREATE TABLE `product` (
`NAME` VARCHAR(255) NOT NULL,
`Color` VARCHAR(255) NOT NULL,
`Seller` VARCHAR(255) NOT NULL,
`Sizeavailable` INT(20) NOT NULL,
`Cost` INT(20) NOT NULL,
`Currentdiscount` INT(20) NOT NULL,
`Orginofdelivery` VARCHAR(255) NOT NULL,
`avgrating` INT(20) NOT NULL,
`totalrating` INT(20) NOT NULL,
`reviewrating` VARCHAR(20) NOT NULL,
`reviewcountry` VARCHAR(20) NOT NULL,
`Category` VARCHAR(255) NOT NULL,
`Brand` VARCHAR(255) NOT NULL
);
INSERT INTO `product`
(`NAME`, `Color`, `Seller`, `Sizeavailable`, `Cost`, `Currentdiscount`, `Orginofdelivery`, `avgrating`, `totalrating`, `reviewrating`, `reviewcountry`, `Category`, `Brand`)
VALUES
(' Iman ', ' Red ', ' Hoby ', 256, 90000, 2000, ' KSA ', 10, 10, ' GOOD ', ' canada', ' Electronic r nHome applicance ', ' preethi'),
(Ahmed, 'Black', Tarig, 255, 50000, 1500, UAE, 10, 10, 'GOOD', USA, ' HometheatrenAC ', ' SAMSUNG ');
SELECT * FROM product;
CREATE TABLE `customer` (
`gender` VARCHAR(10) DEFAULT NULL,
`dob` INT(11) DEFAULT NULL,
`city` VARCHAR(30) DEFAULT NULL,
`pw` VARCHAR(30) DEFAULT NULL,
`name` VARCHAR(50) NOT NULL,
`email` VARCHAR(20) DEFAULT NULL,
`delivey_address` VARCHAR(50) DEFAULT NULL,
`contacts` INT(11) DEFAULT NULL,
`current` VARCHAR(30) DEFAULT NULL,
`orders` VARCHAR(40) DEFAULT NULL,
`payments` VARCHAR(20) DEFAULT NULL
);
INSERT INTO `customer`
(`gender`, `dob`, `city`, `pw`, `name`, `email`, `delivey_address`, `contacts`, `current`, `orders`, `payments`)
VALUES
('female', 24011999, 'Dubai', '423203', 'manal', '[email protected]', 'abc', 983776578, 'Dubai', 'zebronic', 'online mode'),
('male', 3082005, 'beed', '425463', 'sam', '[email protected]', 'xyz', 953778548, 'ajman', 'washing machine', 'cash on delivery'),
('female', 2201199, 'sharja', '428673', 'sara', '[email protected]', 'pqr', 923778528, 'abudhabi', 'AC', 'online mode'),
('male', 6071999, 'riyadh', '435063', 'zamzami', '[email protected]', 'lmn', 913770548, 'mumbai', 'adidas', 'online mode');
SELECT * FROM customer;
CREATE TABLE `orders` (
`orderstatus` VARCHAR(100) DEFAULT NULL,
`deliveryexpectation` VARCHAR(30) DEFAULT NULL,
`deliverydate` VARCHAR(30) DEFAULT NULL,
`orderdate` VARCHAR(30) NOT NULL
);
INSERT INTO `orders`
(`orderstatus`, `deliveryexpectation`, `deliverydate`, `orderdate`)
VALUES
('Completed', 'Packed', '22.08.2022', '19.08.2022'),
('Completed', 'Shipped', '03.09.2022', '28.08.2022'),
('Cancelled', 'Packed', '10.09.2022', '31.08.2022');
SELECT * FROM orders;
CREATE TABLE `seller` (
`sellername` VARCHAR(255) NOT NULL,
`mobile_no` INT(11) DEFAULT NULL,
`password` VARCHAR(20) DEFAULT NULL,
`categories` VARCHAR(255) DEFAULT NULL
);
INSERT INTO `seller`
(`sellername`, `mobile_no`, `password`, `categories`)
VALUES
('hoby', 788590887, 'hoby123', 'groceries'),
('tarig', 888590882, 'tarig123', 'hometheatre elecronics'),
('jamal', 988590082, 'jamal123', 'home appliances');
SELECT * FROM seller;
ALTER TABLE `customer`
ADD PRIMARY KEY (`name`);
ALTER TABLE `discount`
ADD PRIMARY KEY (`code`);
ALTER TABLE `orderl`
ADD PRIMARY KEY (`Shipment`);
ALTER TABLE `orders`
ADD PRIMARY KEY (`orderdate`);
ALTER TABLE `product`
ADD PRIMARY KEY (`Seller`);
ALTER TABLE `seller`
ADD PRIMARY KEY (`sellername`),
ADD UNIQUE KEY `password` (`password`);
COMMIT;
----------------------------------------------------------------------
Task 3:
1.
SELECT * FROM customer ORDER BY city;
2.
ALTER TABLE `product` CHANGE `NAME` `prod_NAME` VARCHAR(255);
ALTER TABLE `product` CHANGE `Color` `prod_Color` VARCHAR(255);
ALTER TABLE `product` CHANGE `Seller` `prod_Seller` VARCHAR(255);
ALTER TABLE `product` CHANGE `Sizeavailable` `prod_Sizeavailable` INT(20);
ALTER TABLE `product` CHANGE `Cost` `prod_Cost` INT(20);
ALTER TABLE `product` CHANGE `Currentdiscount` `prod_Currentdiscount` INT(20);
ALTER TABLE `product` CHANGE `Orginofdelivery` `prod_Orginofdelivery` VARCHAR(255);
ALTER TABLE `product` CHANGE `avgrating` `prod_avgrating` INT(20);
ALTER TABLE `product` CHANGE `totalrating` `prod_totalrating` INT(20);
ALTER TABLE `product` CHANGE `reviewrating` `prod_reviewrating` VARCHAR(20);
ALTER TABLE `product` CHANGE `reviewcountry` `prod_reviewcountry` VARCHAR(20);
ALTER TABLE `product` CHANGE `Category` `prod_Category` VARCHAR(255);
ALTER TABLE `product` CHANGE `Brand` `prod_Brand` VARCHAR(255);
3.
SELECT * FROM customer WHERE COUNT (orders) > 0;
4.
SELECT * FROM customer
WHERE 'name' LIKE 'A%A'
HAVING COUNT(orders >0);
5.
SELECT orders.* FROM orders WHERE
INNER JOIN product ON orders.orderId= product.orderId
WHERE Cost >10000 AND prod_orderstatus="completed";
6.
DELIMITER $$
CREATE PROCEDURE Getcustomer()
BEGIN
SELECT * FROM customer;
END$$
DELIMITER ;
CALL Getcustomer();
DELIMITER $$
CREATE PROCEDURE Getproduct()
BEGIN
SELECT * FROM product;
END$$
DELIMITER ;
CALL Getproduct()
DELIMITER $$
CREATE PROCEDURE Getorder()
BEGIN
SELECT * FROM orders;
END$$
DELIMITER ;
CALL Getorder();
DELIMITER $$
CREATE PROCEDURE Getseller()
BEGIN
SELECT * FROM seller;
END$$
DELIMITER ;
CALL Getseller();
DELIMITER $$
CREATE PROCEDURE Getorderl()
BEGIN
SELECT * FROM orderl;
END$$
DELIMITER ;
CALL Getorderl();
DELIMITER $$
CREATE PROCEDURE Getdiscount()
BEGIN
SELECT * FROM discount;
END$$
DELIMITER ;
CALL Getdiscount();