Project 1
project 1
Table Name: tbl_Customer
| Field | Description | Type | Length |
| customer_ID (PK) | Customer ID | Int | 11 |
| name | Customer Name | Varchar | 255 |
| contact_add | Contact Address | Int | 11 |
| address | Address | Text |
Table Name: tbl_Products
| Field | Description | Type | Length |
| product_ID (PK) | Product ID | Int | 11 |
| category_ID (FK) | Category ID | Int | 11 |
| product_name | Product Name | Varchar | 225 |
Table Name: tbl_Categories
| Field | Description | Type | Length |
| category_ID (PK) | Category ID | Int | 11 |
| category_name | Category Name | Varchar | 30 |
| category_type | Category Type | Varchar | 30 |
Table Name:tbl_ Seller
| Field | Description | Type | Length |
| seller_ID (PK) | Seller ID | Int | 11 |
| product_ID (FK) | Product ID | Int | 11 |
| name | Name | Varchar | 30 |
Table Name: tbl_Shopping Order
| Field | Description | Type | Length |
| order_ID (PK) | Order ID | Int | 11 |
| customer_ID (FK) | Customer ID | Int | 11 |
| date | Date of Order | Date |
Table Name: tbl_Payment
| Field | Description | Type | Length |
| payment_ID (PK) | Payment ID | Int | 11 |
| category_ID (FK) | Category ID | Int | 11 |
| date | Date of Payment | Date |
Table Name: tbl_Deliveries
| Field | Description | Type | Length |
| delivery_ID (PK) | Delivery ID | Int | 11 |
| customer_ID (FK) | Customer ID | Int | 11 |
| date | Date odf Delivery | Date |
Table Name:tbl_ Transaction Reports
| Field | Description | Type | Length |
| report_ID | Report ID | Int | 11 |
| customer_ID (FK) | Customer ID | Int | 11 |
| order_ID (FK) | Order ID | Int | 11 |
| Product_ID (FK) | Product ID | Int | 11 |
| payment_ID (FK) | Payment ID | Int | 11 |
1.
SELECT cus→meridcus→merid,
cus→mer≥ndercus→mer≥nder,
cus→merdobcus→merdob,
cus→merpwcus→merpw,
cus→mernamecus→mername,
cus→meremailcus→meremail,
cus→mer∂iveryaddressescus→mer∂iveryaddresses,
cus→mercontactscus→mercontacts,
cus→mercurrentorderscus→mercurrentorders,
cus→merpreviousorderscus→merpreviousorders,
cus→merpayment∈fosavedcus→merpayment∈fosaved
FROM
websitewebsite.cus→mercus→mer
ORDER BY cus→mer∂iveryaddressescus→mer∂iveryaddresses ASC;
2.
ALTER TABLE product
CHANGE COLUMN product_id prod_id VARCHAR(100),
CHANGE COLUMN product_name prod_name VARCHAR(100),
CHANGE COLUMN product_colour prod_colour VARCHAR(100),
CHANGE COLUMN product_seller prod_seller VARCHAR(100),
CHANGE COLUMN product_sizes prod_sizes VARCHAR(100),
CHANGE COLUMN product_quantity_available prod_quantity_available VARCHAR(100),
CHANGE COLUMN product_cost prod_cost VARCHAR(100),
CHANGE COLUMN product_current_discount prod_current_discount VARCHAR(100),
CHANGE COLUMN product_place_of_origin prod_place_of_origin VARCHAR(100),
CHANGE COLUMN product_for_delivery prod_for_delivery VARCHAR(100),
CHANGE COLUMN product_calculations prod_calculations VARCHAR(100),
CHANGE COLUMN product_isFeatured prod_isFeatured VARCHAR(100),
CHANGE COLUMN product_popuiarityIndex prod_popuiarityIndex VARCHAR(100),
CHANGE COLUMN product_avgratings prod_avgratings VARCHAR(100),
CHANGE COLUMN product_totalratings prod_totalratings VARCHAR(100),
CHANGE COLUMN product_reviews_for_ratings prod_reviews_for_ratings VARCHAR(100),
CHANGE COLUMN product_review_country prod_review_country VARCHAR(100),
CHANGE COLUMN product_isOnSale prod_isOnSale VARCHAR(100),
CHANGE COLUMN product_categories prod_categories VARCHAR(100),
CHANGE COLUMN product_brands prod_brands VARCHAR(100),
CHANGE COLUMN product_images prod_images VARCHAR(100),
CHANGE COLUMN product_questions_with_answers_about_the_products prod_questions_with_answers_about_the_products VARCHAR(100),
CHANGE COLUMN product_description prod_description VARCHAR(100),
CHANGE COLUMN product_tags prod_tags VARCHAR(100),
CHANGE COLUMN product_related_products_ids prod_related_products_ids VARCHAR(100);
3.
SELECT customer_name FROM customer WHERE customer_previous_orders IS NOT NULL
4.
SELECT customer_name FROM customer WHERE customer_name LIKE 'A%A' AND customer_previous_orders IS NOT NULL
SELECT status_id,received_status
FROM order_status
INNER JOIN product
ON order_status.status_id = product.prod_id WHERE prod_cost>10000;
6.
DELIMITER &&
CREATE PROCEDURE create_insert_alter_table()
BEGIN
CREATE TABLE Coupouns (
Coupoun_id INT,
Coupoun_name VARCHAR(255),
Coupoun_discount VARCHAR(255),
discount_rule VARCHAR(255)
);
INSERT INTO coupouns
(Coupoun_id,
Coupoun_name,
Coupoun_discount,
discount_rule
)
VALUES
('1',
'BACK2SCHOOL',
'500',
'Ankle Boots'
);
INSERT INTO coupouns
(Coupoun_id,
Coupoun_name,
Coupoun_discount,
discount_rule
)
VALUES
('2',
'LITTLELEARNERS',
'1000',
'Boat shoes'
);
CREATE TABLE customer (
customer_id INT NOT NULL,
customer_gender VARCHAR(100),
customer_dob VARCHAR(100),
customer_pw VARCHAR(100),
customer_name VARCHAR(100),
customer_email VARCHAR(100),
customer_delivery_addresses VARCHAR(100),
customer_contacts VARCHAR(100),
customer_current_orders VARCHAR(100),
customer_previous_orders VARCHAR(100),
customer_payment_info_saved VARCHAR(100),
PRIMARY KEY (customer_id)
);
INSERT INTO cus→mercus→mer
(cus→meridcus→merid,
cus→mer≥ndercus→mer≥nder,
cus→merdobcus→merdob,
cus→merpwcus→merpw,
cus→mernamecus→mername,
cus→meremailcus→meremail,
cus→mer∂iveryaddressescus→mer∂iveryaddresses,
cus→mercontactscus→mercontacts,
cus→mercurrentorderscus→mercurrentorders,
cus→merpreviousorderscus→merpreviousorders,
cus→merpayment∈fosavedcus→merpayment∈fosaved
)
VALUES
('1',
'm',
'1 jan',
'12345',
'abc',
'hyderabad',
'998547563',
'mobile',
'laptop',
'yes'
);
INSERT INTO cus→mercus→mer
(cus→meridcus→merid,
cus→mer≥ndercus→mer≥nder,
cus→merdobcus→merdob,
cus→merpwcus→merpw,
cus→mernamecus→mername,
cus→meremailcus→meremail,
cus→mer∂iveryaddressescus→mer∂iveryaddresses,
cus→mercontactscus→mercontacts,
cus→mercurrentorderscus→mercurrentorders,
cus→merpreviousorderscus→merpreviousorders,
cus→merpayment∈fosavedcus→merpayment∈fosaved
)
VALUES
('2',
'm',
'1 jan',
'12345',
'abc',
'banglore',
'998547563',
'mobile',
'laptop',
'yes'
);
CREATE TABLE invoice (
invoice_id INT,
invoice_name VARCHAR(255),
invoice_product VARCHAR(255),
PRIMARY KEY(invoice_id)
);
INSERT INTO invoice
(invoice_id,
invoice_name,
invoice_product
)
VALUES
('1',
'Final Payment',
'Ankle Boots'
);
INSERT INTO invoice
(invoice_id,
invoice_name,
invoice_product
)
VALUES
('2',
'Invoice #3',
'Boat shoes'
);
CREATE TABLE order_status (
order_id INT NOT NULL,
received_status VARCHAR(255),
returning_status VARCHAR(255),
PRIMARY KEY(order_id)
);
INSERT INTO order_status
(order_id,
received_status,
returning_status
)
VALUES
('1',
'yes',
'no'
);
INSERT INTO order_status
(order_id,
received_status,
returning_status
)
VALUES
('2',
'yes',
'no'
);
CREATE TABLE product (
product_id INT NOT NULL,
product_name VARCHAR(100),
product_colour VARCHAR(100),
product_seller VARCHAR(100),
product_sizes VARCHAR(100),
product_quantity_available VARCHAR(100),
product_cost VARCHAR(100),
product_current_discount VARCHAR(100),
product_place_of_origin VARCHAR(100),
product_for_delivery VARCHAR(100),
product_calculations VARCHAR(100),
product_isFeatured VARCHAR(100),
product_popuiarityIndex VARCHAR(100),
product_avgratings VARCHAR(100),
product_totalratings VARCHAR(100),
product_reviews_for_ratings VARCHAR(100),
product_review_country VARCHAR(100),
product_isOnSale VARCHAR(100),
product_categories VARCHAR(100),
product_brands VARCHAR(100),
product_images VARCHAR(100),
product_questions_with_answers_about_the_products VARCHAR(100),
product_description VARCHAR(100),
product_tags VARCHAR(100),
product_related_products_ids VARCHAR(100),
PRIMARY KEY (product_id)
);
INSERT INTO ∏uct∏uct
(∏uctid∏uctid,
∏uctname∏uctname,
∏uctcolour∏uctcolour,
∏uctsel≤r∏uctsel≤r,
∏uctsizes∏uctsizes,
∏uctquantityavailab≤∏uctquantityavailab≤,
∏uctcost∏uctcost,
∏uctcurrentdiscount∏uctcurrentdiscount,
∏uctplaceoforig∈∏uctplaceoforig∈,
∏uctfor∂ivery∏uctfor∂ivery,
∏uctcalca––tions∏uctcalca̲tions,
∏uctisFeatured∏uctisFeatured,
∏uctpopuiarityIndex∏uctpopuiarityIndex,
∏uctavgrat∈gs∏uctavgrat∈gs,
∏uct→talrat∈gs∏uct→talrat∈gs,
∏uctreviewsforrat∈gs∏uctreviewsforrat∈gs,
∏uctreviewcountry∏uctreviewcountry,
∏uctisOnSa≤∏uctisOnSa≤,
∏uctcategories∏uctcategories,
∏uctbrands∏uctbrands,
∏uctima≥s∏uctima≥s,
∏uctquestionswithanswersaboutthe∏ucts∏uctquestionswithanswersaboutthe∏ucts,
∏uctdescription∏uctdescription,
∏ucttags∏ucttags,
∏uctrelated∏uctsids∏uctrelated∏uctsids
)
VALUES
('1',
'samsung mobile',
'black',
'samsung',
'16 gb',
'10',
'23000',
'2000',
'india',
'yes',
'1200',
'yes',
'1',
'9',
'45',
'good',
'india',
'yes',
'mobiles',
'samsung',
'yes',
'does phone have 5g',
'samsung',
'smartphone',
'12345'
);
INSERT INTO ∏uct∏uct
(∏uctid∏uctid,
∏uctname∏uctname,
∏uctcolour∏uctcolour,
∏uctsel≤r∏uctsel≤r,
∏uctsizes∏uctsizes,
∏uctquantityavailab≤∏uctquantityavailab≤,
∏uctcost∏uctcost,
∏uctcurrentdiscount∏uctcurrentdiscount,
∏uctplaceoforig∈∏uctplaceoforig∈,
∏uctfor∂ivery∏uctfor∂ivery,
∏uctcalca––tions∏uctcalca̲tions,
∏uctisFeatured∏uctisFeatured,
∏uctpopuiarityIndex∏uctpopuiarityIndex,
∏uctavgrat∈gs∏uctavgrat∈gs,
∏uct→talrat∈gs∏uct→talrat∈gs,
∏uctreviewsforrat∈gs∏uctreviewsforrat∈gs,
∏uctreviewcountry∏uctreviewcountry,
∏uctisOnSa≤∏uctisOnSa≤,
∏uctcategories∏uctcategories,
∏uctbrands∏uctbrands,
∏uctima≥s∏uctima≥s,
∏uctquestionswithanswersaboutthe∏ucts∏uctquestionswithanswersaboutthe∏ucts,
∏uctdescription∏uctdescription,
∏ucttags∏ucttags,
∏uctrelated∏uctsids∏uctrelated∏uctsids
)
VALUES
('2',
'samsung z',
'black',
'samsung',
'16 gb',
'10',
'23000',
'2000',
'india',
'yes',
'1200',
'yes',
'1',
'9',
'45',
'good',
'india',
'yes',
'mobiles',
'samsung',
'yes',
'does phone have 5g',
'samsung',
'smartphone',
'12345'
);
ALTER TABLE product
CHANGE COLUMN product_seller prod_seller VARCHAR(100),
CHANGE COLUMN product_sizes prod_sizes VARCHAR(100),
CHANGE COLUMN product_quantity_available prod_quantity_available VARCHAR(100),
CHANGE COLUMN product_cost prod_cost VARCHAR(100),
CHANGE COLUMN product_current_discount prod_current_discount VARCHAR(100),
CHANGE COLUMN product_place_of_origin prod_place_of_origin VARCHAR(100),
CHANGE COLUMN product_for_delivery prod_for_delivery VARCHAR(100),
CHANGE COLUMN product_calculations prod_calculations VARCHAR(100),
CHANGE COLUMN product_isFeatured prod_isFeatured VARCHAR(100),
CHANGE COLUMN product_popuiarityIndex prod_popuiarityIndex VARCHAR(100),
CHANGE COLUMN product_avgratings prod_avgratings VARCHAR(100),
CHANGE COLUMN product_totalratings prod_totalratings VARCHAR(100),
CHANGE COLUMN product_reviews_for_ratings prod_reviews_for_ratings VARCHAR(100),
CHANGE COLUMN product_review_country prod_review_country VARCHAR(100),
CHANGE COLUMN product_isOnSale prod_isOnSale VARCHAR(100),
CHANGE COLUMN product_categories prod_categories VARCHAR(100),
CHANGE COLUMN product_brands prod_brands VARCHAR(100),
CHANGE COLUMN product_images prod_images VARCHAR(100),
CHANGE COLUMN product_questions_with_answers_about_the_products prod_questions_with_answers_about_the_products VARCHAR(100),
CHANGE COLUMN product_description prod_description VARCHAR(100),
CHANGE COLUMN product_tags prod_tags VARCHAR(100),
CHANGE COLUMN product_related_products_ids prod_related_products_ids VARCHAR(100);
CREATE TABLE reviews (
review_id INT NOT NULL,
review_name VARCHAR(100),
review_description VARCHAR(100),
PRIMARY KEY (review_id)
);
INSERT INTO reviews
(review_id,
review_name,
review_description
)
VALUES
('1',
'casanova',
'good'
);
INSERT INTO reviews
(review_id,
review_name,
review_description
)
VALUES
('2',
'Joe',
'good'
);
CREATE TABLE seller (
seller_id INT NOT NULL,
seller_mobile_number VARCHAR(100),
seller_password VARCHAR(100),
seller_categories VARCHAR(100),
PRIMARY KEY (seller_id)
);
INSERT INTO seller
(seller_id,
seller_mobile_number,
seller_password,
seller_categories
)
VALUES
('1',
'9586235489',
'123456',
'mobile'
);
INSERT INTO seller
(seller_id,
seller_mobile_number,
seller_password,
seller_categories
)
VALUES
('2',
'9586235434',
'1234ww',
'mobile'
);
CREATE TABLE Shipment (
Shipment_id INT,
Shipment_name VARCHAR(255),
Shipment_location VARCHAR(255),
PRIMARY KEY(Shipment_id)
);
INSERT INTO Shipment
(Shipment_id,
Shipment_name,
Shipment_location
)
VALUES
('1',
'Shipment1',
'Aspen'
);
INSERT INTO Shipment
(Shipment_id,
Shipment_name,
Shipment_location
)
VALUES
('2',
'Shipment2',
'Aspen'
);
CREATE TABLE wishlist (
wishlist_id INT,
wishlist_name VARCHAR(255),
wishlist_product VARCHAR(255),
PRIMARY KEY(wishlist_id)
);
INSERT INTO wishlist
(wishlist_id,
wishlist_name,
wishlist_product
)
VALUES
('1',
'casanova',
'shoes'
);
INSERT INTO wishlist
(wishlist_id,
wishlist_name,
wishlist_product
)
VALUES
('2',
'Joe',
'clothes'
);
CREATE TABLE users (
users_id INT NOT NULL,
users_name VARCHAR(100),
wishlist_id INT,
review_id INT,
PRIMARY KEY (users_id),
FOREIGN KEY (wishlist_id) REFERENCES wishlist(wishlist_id),
FOREIGN KEY (review_id) REFERENCES reviews(review_id)
);
INSERT INTO users
(users_id,
users_name,
wishlist_id,
review_id
)
VALUES
('1',
'casanova',
'1',
'1'
);
INSERT INTO users
(users_id,
users_name,
wishlist_id,
review_id
)
VALUES
('2',
'Joe',
'2',
'2'