Project 1

All the tables created and quires screen shots are updated in the word file and uploded word file and also entered quiries as below,

1)

CREATE VIEW Coupon_codes AS SELECT quantity,current_discount FROM products WHERE (quantity > 50 OR current_discount < 10);

2)
CREATE TABLE `orders` (
`Oder_id` int(4) NOT NULL,
`Shipment_details` varchar(20) NOT NULL,
`Invoices` varchar(225) NOT NULL
)

3) CREATE TABLE `user_wishlist` (
`product_id` int(4) NOT NULL,
`Name` varchar(20) NOT NULL,
`Amount` int(20) NOT NULL
)

INSERT INTO `user_wishlist` (`product_id`, `Name`, `Amount`) VALUES ('1001', 'Mobile phone', '10500'), ('1002', 'Mobile Phone', '9500');

4) SELECT status,order_id FROM Orders WHERE status ='cancelled';

5) SELECT reviews_fo_ratings,review_country FROM products;

6)

CREATE TABLE `products` (
`name` varchar(20) DEFAULT NULL,
`color` varchar(10) DEFAULT NULL,
`seller` varchar(20) DEFAULT NULL,
`sizes` int(5) DEFAULT NULL,
`quantity` int(10) DEFAULT NULL,
`cost` int(10) DEFAULT NULL,
`current_discount` int(4) DEFAULT NULL,
`plac_of_origin` varchar(20) DEFAULT NULL,
`isFeatured` varchar(5) DEFAULT NULL,
`popularityIndex` int(2) DEFAULT NULL,
`avgratings` decimal(2,2) DEFAULT NULL,
`totalratings` decimal(2,2) DEFAULT NULL,
`reviews_fo_ratings` varchar(255) DEFAULT NULL,
`review_country` varchar(15) DEFAULT NULL,
`isOnSale` varchar(5) DEFAULT NULL,
`categories` varchar(10) DEFAULT NULL,
`brands` varchar(10) DEFAULT NULL,
`images` blob DEFAULT NULL,
`FAQ` varchar(225) DEFAULT NULL,
`description` varchar(225) DEFAULT NULL,
`tags` int(2) DEFAULT NULL,
`related_products_ids` int(5) DEFAULT NULL
)

ALTER TABLE `products` ADD `product_id` INT(4) NULL DEFAULT NULL FIRST, ADD PRIMARY KEY (`product_id`);

Table inserted vales are uploaded in the word file

7)

CREATE TABLE `customer` (
`gender` varchar(6) DEFAULT NULL,
`dob` date DEFAULT NULL,
`city` varchar(10) DEFAULT NULL,
`pw` date DEFAULT NULL,
`name` varchar(22) DEFAULT NULL,
`email` varchar(22) DEFAULT NULL,
`delivery_addresses` varchar(225) DEFAULT NULL,
`contacts` varchar(50) DEFAULT NULL,
`current_and_prev_orders` varchar(50) DEFAULT NULL,
`payment_info_saved` varchar(50) DEFAULT NULL
)

ALTER TABLE `customer` ADD `customer_id` INT(4) NULL DEFAULT NULL FIRST, ADD PRIMARY KEY (`customer_id`);

8) ALTER TABLE `orders` ADD `Status` VARCHAR(10) NOT NULL AFTER `Invoices`;

ALTER TABLE orders ADD CONSTRAINT check_status CHECK (Status IN( 'cancelled','completed','ordered','packed','shipped','delivered'));

ALTER TABLE orders ADD CONSTRAINT chk_delivery CHECK (delivery_status IN('deliveryexpectation','deliverydate'));

ALTER TABLE orders ADD orderdate DATE;

9)

CREATE TABLE `seller` (
`Name` varchar(20) DEFAULT NULL,
`mobile_no` varchar(20) DEFAULT NULL,
`password` varchar(20) DEFAULT NULL,
`categories_of_products_provided` varchar(20) DEFAULT NULL
)

ALTER TABLE `seller` ADD `seller_id` INT(4) NULL DEFAULT NULL FIRST, ADD PRIMARY KEY (`seller_id`);