Project 1
1)1. Order table -- Table structure for table `order` -- CREATE TABLE IF NOT EXISTS `order` ( `order_id` int(11) NOT NULL AUTO_INCREMENT, `order_date` date NOT NULL, `order_status` varchar(255) NOT NULL, `order_delivery_date` date NOT NULL, `order_delivery_expectation` varchar(255) NOT NULL, `order_customer_id` int(11) NOT NULL, `order_seller_id` int(11) NOT NULL, PRIMARY KEY (`order_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
2. Order_item table -- Table structure for table `order_item` -- CREATE TABLE IF NOT EXISTS `order_item` ( `order_item_id` int(11) NOT NULL AUTO_INCREMENT, `order_item_order_id` int(11) NOT NULL, `order_item_product_id` int(11) NOT NULL, `order_item_quantity` int(11) NOT NULL, `order_item_unit_price` decimal(10,0) NOT NULL, `order_item_total_price` decimal(10,0) NOT NULL, PRIMARY KEY (`order_item_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
3. Product table -- Table structure for table `product` -- CREATE TABLE IF NOT EXISTS `product` ( `product_id` int(11) NOT NULL AUTO_INCREMENT, `product_name` varchar(255) NOT NULL, `product_description` text NOT NULL, `product_unit_price` decimal(10,0) NOT NULL, `product_is_featured` tinyint(1) NOT NULL, `product_image` varchar(255) NOT NULL, `product_quantity` int(11) NOT NULL, `product_seller_id` int(11) NOT NULL, `product_category_id` int(11) NOT NULL, `product_brand_id` int(11) NOT NULL, `product_popularity_index` int(11) NOT NULL, `product_average_ratings` decimal(10,0) NOT NULL, `product_total_ratings` int(11) NOT NULL, `product_is_on_sale` tinyint(1) NOT NULL, PRIMARY KEY (`product_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
4. Product_category table -- Table structure for table `product_category` -- CREATE TABLE IF NOT EXISTS `product_category` ( `product_category_id` int(11) NOT NULL AUTO_INCREMENT, `product_category_name` varchar(255) NOT NULL, `product_category_description` text NOT NULL, PRIMARY KEY (`product_category_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
5. Product_subcategory table -- Table structure for table `product_subcategory` -- CREATE TABLE IF NOT EXISTS `product_subcategory` ( `product_subcategory_id` int(11) NOT NULL AUTO_INCREMENT, `product_subcategory_name` varchar(255) NOT NULL, `product_subcategory_description` text NOT NULL, `product_subcategory_category_id` int(11) NOT NULL, PRIMARY KEY (`product_subcategory_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
6. Review table -- Table structure for table `review` -- CREATE TABLE IF NOT EXISTS `review` ( `review_id` int(11) NOT NULL AUTO_INCREMENT, `review_date` date NOT NULL, `review_rating` int(11) NOT NULL, `review_description` text NOT NULL, `review_product_id` int(11) NOT NULL, `review_customer_id` int(11) NOT NULL, PRIMARY KEY (`review_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
7. Seller table -- Table structure for table `seller` -- CREATE TABLE IF NOT EXISTS `seller` ( `seller_id` int(11) NOT NULL AUTO_INCREMENT, `seller_name` varchar(255) NOT NULL, `seller_company_name` varchar(255) NOT NULL, `seller_company_address` text NOT NULL, `seller_gstin` varchar(255) NOT NULL, `seller_email` varchar(255) NOT NULL, `seller_mobile` varchar(255) NOT NULL, `seller_password` varchar(255) NOT NULL, PRIMARY KEY (`seller_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
8. User table -- Table structure for table `user` -- CREATE TABLE IF NOT EXISTS `user` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `user_name` varchar(255) NOT NULL, `user_email` varchar(255) NOT NULL, `user_password` varchar(255) NOT NULL, `user_gender` varchar(255) NOT NULL, `user_date_of_birth` date NOT NULL, `user_contact` varchar(255) NOT NULL, `user_city` varchar(255) NOT NULL, PRIMARY KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
9. User_address table -- Table structure for table `user_address` -- CREATE TABLE IF NOT EXISTS `user_address` ( `user_address_id` int(11) NOT NULL AUTO_INCREMENT, `user_address_user_id` int(11) NOT NULL, `user_address_address_line_1` varchar(255) NOT NULL, `user_address_address_line_2` varchar(255) NOT NULL, `user_address_city` varchar(255) NOT NULL, `user_address_state` varchar(255) NOT NULL, `user_address_country` varchar(255) NOT NULL, `user_address_zipcode` varchar(255) NOT NULL, `user_address_is_primary` tinyint(1) NOT NULL, PRIMARY KEY (`user_address_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
10. User_order table -- Table structure for table `user_order` -- CREATE TABLE IF NOT EXISTS `user_order` ( `user_order_id` int(11) NOT NULL AUTO_INCREMENT, `user_order_user_id` int(11) NOT NULL, `user_order_order_id` int(11) NOT NULL, PRIMARY KEY (`user_order_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
11. User_payment_info table -- Table structure for table `user_payment_info` -- CREATE TABLE IF NOT EXISTS `user_payment_info` ( `user_payment_info_id` int(11) NOT NULL AUTO_INCREMENT, `user_payment_info_user_id` int(11) NOT NULL, `user_payment_info_card_type` varchar(255) NOT NULL, `user_payment_info_name_on_card` varchar(255) NOT NULL, `user_payment_info_card_number` varchar(255) NOT NULL, `user_payment_info_expiry_month` int(11) NOT NULL, `user_payment_info_expiry_year` int(11) NOT NULL, `user_payment_info_cvv` varchar(255) NOT NULL, PRIMARY KEY (`user_payment_info_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
12. User_wishlist table -- Table structure for table `user_wishlist` -- CREATE TABLE IF NOT EXISTS `user_wishlist` ( `user_wishlist_id` int(11) NOT NULL AUTO_INCREMENT, `user_wishlist_user_id` int(11) NOT NULL, `user_wishlist_product_id` int(11) NOT NULL, PRIMARY KEY (`user_wishlist_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
13.Name, mobile no, password, categories of products provided, Product-Name, price, category, seller id, description Customer- Name, mobile no, password, email, address Order- ID, customer id, seller id, product id, quantity, price
3)1.SELECT * FROM Customers ORDER BY City
2.ALTER TABLE product RENAME COLUMN name TO prod_name, RENAME COLUMN description TO prod_description, RENAME COLUMN price TO prod_price, RENAME COLUMN category TO prod_category;
3.SELECT c.name, c.address, c.phone FROM customers c, orders o WHERE c.id = o.customer_id GROUP BY c.id HAVING count(o.id) >= 1
4.SELECT customer_name, product_name FROM customers JOIN orders ON customers.customer_id = orders.customer_id JOIN products ON orders.product_id = products.product_id WHERE customer_name LIKE 'A%' AND customer_name LIKE '%A' GROUP BY customer_name HAVING COUNT(product_name) > 1
5.SELECT * FROM orders WHERE delivered = 1 AND cost > 10000 AND num_of_customers > 1;
6.1. Open the SQLite shell and connect to the database
. 2. Enter the following command to run all the table creation statements, insert statements and alter statements at once:
.read create_tables.sql .
read insert_data.sql .
read alter_tables.sql