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); a. Use Oracle to implement the database and its’ tables. b. The project must be implemented using scripts so that they can be executed in the classroom. c. Use meaningful names for the database, table names, field names, primary keys, foreign keys, attributes, aliases, script names, etc. d. There should be at least 5 records in each table.
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
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);
a. Use Oracle to implement the
b. The project must be implemented using scripts so that they can be executed in the
classroom.
c. Use meaningful names for the database, table names, field names, primary keys,
foreign keys, attributes, aliases, script names, etc.
d. There should be at least 5 records in each table.
e. At most, use ten records per table to test the database but more can be used if
wanted.
f. Use valid data for field contents
h. Each table in the database should be linked to at least one other table.
i. Using SQL, design and print all the data attributes needed to demonstrate use of
the following SQL statements:
Update one record from each table.
After updating each table, display the updated table immediately after
each update.
Perform a calculation in at least one table and store the results of the
calculations as an alias.
Display the alias containing the results of the calculations.
Link a set of tables together to demonstrate the use of a JOIN.
Display the contents of the results of performing the JOIN.
Create at least one two level SELECT structure.
Display the results of the SELECT structure.
Create one multiple table query useful to your specific project.
Display the results of the multiple table query.
j. Using a comment block, show the actual SQL used, followed by the output
generated for the above items.
k. Also print out and submit the most updated version of the ERD from the last lab
assignment.
Show me step by step on how to complete in.
Step by step
Solved in 4 steps with 15 images
