Please help. (5) Use the SQL already built to demonstrate use of the following SQL statements: (a) Updating one record from each table. (b) Performing a calculation in at least one table and store the results of the calculation as an alias. (c) Linking a set of tables together to demonstrate the use of a JOIN. (d) Creating at least one two level SELECT structure. (e) Creating one multiple table query useful to your specific project. (6) Conclusion CREATE TABLE consumer ( ConsumerID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Age INT, Gender VARCHAR(10), OnlineInStore VARCHAR(20) ); INSERT INTO consumer (ConsumerID, FirstName, LastName, Age, Gender, OnlineInStore) VALUES (1, 'John', 'Doe', 25, 'Male', 'Online'); INSERT INTO consumer (ConsumerID, FirstName, LastName, Age, Gender, OnlineInStore) VALUES (2, 'Jane', 'Smith', 30, 'Female', 'In-store'); INSERT INTO consumer (ConsumerID, FirstName, LastName, Age, Gender, OnlineInStore) VALUES (3, 'Mike', 'Johnson', 20, 'Male', 'Online'); INSERT INTO consumer (ConsumerID, FirstName, LastName, Age, Gender, OnlineInStore) VALUES (4, 'Sarah', 'Williams', 35, 'Female', 'In-store'); INSERT INTO consumer (ConsumerID, FirstName, LastName, Age, Gender, OnlineInStore) VALUES (5, 'Chris', 'Davis', 28, 'Male', 'Online'); CREATE TABLE InStoreRetailer ( RetailerID INT PRIMARY KEY, RetailerName VARCHAR(50), StoreLayout VARCHAR(50), CustomerService VARCHAR(100) ); INSERT INTO InStoreRetailer (RetailerID, RetailerName, StoreLayout, CustomerService) VALUES (1, 'ElectroStore', 'Spacious', 'Helpful staff'); INSERT INTO InStoreRetailer (RetailerID, RetailerName, StoreLayout, CustomerService) VALUES (2, 'GadgetHaven', 'Organized', 'Quick assistance'); INSERT INTO InStoreRetailer (RetailerID, RetailerName, StoreLayout, CustomerService) VALUES (3, 'TechZone', 'Well-organized', 'Knowledgeable staff'); INSERT INTO InStoreRetailer (RetailerID, RetailerName, StoreLayout, CustomerService) VALUES (4, 'HomeEssentials', 'Cozy', 'Friendly and attentive service'); INSERT INTO InStoreRetailer (RetailerID, RetailerName, StoreLayout, CustomerService) VALUES (5, 'FashionTrends', 'Modern and trendy', 'Personalized styling assistance'); CREATE TABLE OnlineRetailer ( RetailerID INT PRIMARY KEY, RetailerName VARCHAR(50), UserExperience VARCHAR(50), OnlinePromotions VARCHAR(100), CustomerReviews VARCHAR(255) ); INSERT INTO OnlineRetailer (RetailerID, RetailerName, UserExperience, OnlinePromotions, CustomerReviews) VALUES (1, 'OnlineMart', 'Good', 'Discounts on electronics', 'Positive reviews'); INSERT INTO OnlineRetailer (RetailerID, RetailerName, UserExperience, OnlinePromotions, CustomerReviews) VALUES (2, 'WebShop', 'Excellent', 'Flash sales every week', 'Highly rated'); INSERT INTO OnlineRetailer (RetailerID, RetailerName, UserExperience, OnlinePromotions, CustomerReviews) VALUES (3, 'E-Store', 'Average', 'Free shipping on orders over $50', 'Mixed reviews'); INSERT INTO OnlineRetailer (RetailerID, RetailerName, UserExperience, OnlinePromotions, CustomerReviews) VALUES (4, 'DiscountDeals', 'Good', 'Daily deals on a variety of products', 'Positive feedback'); INSERT INTO OnlineRetailer (RetailerID, RetailerName, UserExperience, OnlinePromotions, CustomerReviews) VALUES (5, 'eCommerceShop', 'Excellent', '10% off for new customers', 'Highly recommended'); CREATE TABLE Product ( ProductID INT PRIMARY KEY, ProductName VARCHAR(50), Brand VARCHAR(50), Price DECIMAL(10, 2) ); INSERT INTO Product (ProductID, ProductName, Brand, Price) VALUES (1, 'Laptop', 'ABC', 1200.00); INSERT INTO Product (ProductID, ProductName, Brand, Price) VALUES (2, 'Smartphone', 'XYZ', 800.00); INSERT INTO Product (ProductID, ProductName, Brand, Price) VALUES (3, 'Tablet', 'DEF', 500.00); INSERT INTO Product (ProductID, ProductName, Brand, Price) VALUES (4, 'Headphones', 'GHI', 100.00); INSERT INTO Product (ProductID, ProductName, Brand, Price) VALUES (5, 'Smartwatch', 'JKL', 250.00);
Please help.
(5) Use the SQL already built to demonstrate use of the following SQL
statements:
(a) Updating one record from each table.
(b) Performing a calculation in at least one table and store the results
of the calculation as an alias.
(c) Linking a set of tables together to demonstrate the use of a JOIN.
(d) Creating at least one two level SELECT structure.
(e) Creating one multiple table query useful to your specific project.
(6) Conclusion
CREATE TABLE consumer (
ConsumerID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT,
Gender VARCHAR(10),
OnlineInStore VARCHAR(20)
);
INSERT INTO consumer (ConsumerID, FirstName, LastName, Age, Gender, OnlineInStore) VALUES (1, 'John', 'Doe', 25, 'Male', 'Online');
INSERT INTO consumer (ConsumerID, FirstName, LastName, Age, Gender, OnlineInStore) VALUES (2, 'Jane', 'Smith', 30, 'Female', 'In-store');
INSERT INTO consumer (ConsumerID, FirstName, LastName, Age, Gender, OnlineInStore) VALUES (3, 'Mike', 'Johnson', 20, 'Male', 'Online');
INSERT INTO consumer (ConsumerID, FirstName, LastName, Age, Gender, OnlineInStore) VALUES (4, 'Sarah', 'Williams', 35, 'Female', 'In-store');
INSERT INTO consumer (ConsumerID, FirstName, LastName, Age, Gender, OnlineInStore) VALUES (5, 'Chris', 'Davis', 28, 'Male', 'Online');
CREATE TABLE InStoreRetailer (
RetailerID INT PRIMARY KEY,
RetailerName VARCHAR(50),
StoreLayout VARCHAR(50),
CustomerService VARCHAR(100)
);
INSERT INTO InStoreRetailer (RetailerID, RetailerName, StoreLayout, CustomerService) VALUES
(1, 'ElectroStore', 'Spacious', 'Helpful staff');
INSERT INTO InStoreRetailer (RetailerID, RetailerName, StoreLayout, CustomerService) VALUES
(2, 'GadgetHaven', 'Organized', 'Quick assistance');
INSERT INTO InStoreRetailer (RetailerID, RetailerName, StoreLayout, CustomerService) VALUES
(3, 'TechZone', 'Well-organized', 'Knowledgeable staff');
INSERT INTO InStoreRetailer (RetailerID, RetailerName, StoreLayout, CustomerService) VALUES
(4, 'HomeEssentials', 'Cozy', 'Friendly and attentive service');
INSERT INTO InStoreRetailer (RetailerID, RetailerName, StoreLayout, CustomerService) VALUES
(5, 'FashionTrends', 'Modern and trendy', 'Personalized styling assistance');
CREATE TABLE OnlineRetailer (
RetailerID INT PRIMARY KEY,
RetailerName VARCHAR(50),
UserExperience VARCHAR(50),
OnlinePromotions VARCHAR(100),
CustomerReviews VARCHAR(255)
);
INSERT INTO OnlineRetailer (RetailerID, RetailerName, UserExperience, OnlinePromotions, CustomerReviews) VALUES
(1, 'OnlineMart', 'Good', 'Discounts on electronics', 'Positive reviews');
INSERT INTO OnlineRetailer (RetailerID, RetailerName, UserExperience, OnlinePromotions, CustomerReviews) VALUES
(2, 'WebShop', 'Excellent', 'Flash sales every week', 'Highly rated');
INSERT INTO OnlineRetailer (RetailerID, RetailerName, UserExperience, OnlinePromotions, CustomerReviews) VALUES
(3, 'E-Store', 'Average', 'Free shipping on orders over $50', 'Mixed reviews');
INSERT INTO OnlineRetailer (RetailerID, RetailerName, UserExperience, OnlinePromotions, CustomerReviews) VALUES
(4, 'DiscountDeals', 'Good', 'Daily deals on a variety of products', 'Positive feedback');
INSERT INTO OnlineRetailer (RetailerID, RetailerName, UserExperience, OnlinePromotions, CustomerReviews) VALUES
(5, 'eCommerceShop', 'Excellent', '10% off for new customers', 'Highly recommended');
CREATE TABLE Product (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(50),
Brand VARCHAR(50),
Price DECIMAL(10, 2)
);
INSERT INTO Product (ProductID, ProductName, Brand, Price) VALUES (1, 'Laptop', 'ABC', 1200.00);
INSERT INTO Product (ProductID, ProductName, Brand, Price) VALUES (2, 'Smartphone', 'XYZ', 800.00);
INSERT INTO Product (ProductID, ProductName, Brand, Price) VALUES (3, 'Tablet', 'DEF', 500.00);
INSERT INTO Product (ProductID, ProductName, Brand, Price) VALUES (4, 'Headphones', 'GHI', 100.00);
INSERT INTO Product (ProductID, ProductName, Brand, Price) VALUES (5, 'Smartwatch', 'JKL', 250.00);
Trending now
This is a popular solution!
Step by step
Solved in 4 steps with 4 images
