0% found this document useful (0 votes)
20 views

SQL Queries For Part1 2

The document contains SQL queries and commands to create tables, views, insert data, and join tables related to an e-commerce platform including shoppers, orders, products, sellers, reviews, and questions.

Uploaded by

Pratiksha Patil
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
20 views

SQL Queries For Part1 2

The document contains SQL queries and commands to create tables, views, insert data, and join tables related to an e-commerce platform including shoppers, orders, products, sellers, reviews, and questions.

Uploaded by

Pratiksha Patil
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 4

/*1a.

select shopper_first_name as Name, shopper_surname as Surname, shopper_id,


shopper_email_address,
(strftime('%Y','now')- strftime('%Y', date_of_birth)) as AGE, ifnull(gender, 'Not
Known') as GENDER, substr(date_joined,9,2) || '-' || substr(date_joined,6,2) || '-'
|| substr(date_joined,1,4) as Joining_date
from shoppers WHERE gender='F' OR date_joined>'2020-01-01' order by gender, AGE
DeSC;

/*1b.
select shopper_orders.order_id, substr(shopper_orders.order_date,9,2) || '-' ||
substr(shopper_orders.order_date,6,2) || '-' ||
substr(shopper_orders.order_date,1,4) as ORDER_DATE, ordered_products.quantity,
sellers.seller_name, shoppers.shopper_first_name, shoppers.shopper_surname,
products.product_description, '£'|| printf('%.2f', ordered_products.price) as
Price, ordered_products.ordered_product_status
from shoppers inner join shopper_orders on
shopper_orders.shopper_id=shoppers.shopper_id
inner join ordered_products on shopper_orders.order_id = ordered_products.order_id
inner join products on ordered_products.product_id=products.product_id
inner join sellers on ordered_products.seller_id = sellers.seller_id where
shoppers.shopper_id = 10000 order by shopper_orders.order_date DESC;

select shopper_orders.order_id, substr(shopper_orders.order_date,9,2) || '-' ||


substr(shopper_orders.order_date,6,2) || '-' ||
substr(shopper_orders.order_date,1,4) as ORDER_DATE, ordered_products.quantity,
sellers.seller_name, shoppers.shopper_first_name, shoppers.shopper_surname,
products.product_description, '£'|| printf('%.2f', ordered_products.price) as
Price, ordered_products.ordered_product_status
from shoppers inner join shopper_orders on
shopper_orders.shopper_id=shoppers.shopper_id
inner join ordered_products on shopper_orders.order_id = ordered_products.order_id
inner join products on ordered_products.product_id=products.product_id
inner join sellers on ordered_products.seller_id = sellers.seller_id where
shoppers.shopper_id = 10019 order by shopper_orders.order_date DESC;

/*1c.
SELECT '£'|| printf('%.2f', ordered_products.price) as
Price,Seller_name,Product_description,
substr(shopper_orders.order_date,9,2) || '-' ||
substr(shopper_orders.order_date,6,2) || '-' ||
substr(shopper_orders.order_date,1,4) as order_date,
product_code,quantity,products.product_id,
count(ordered_products.order_id)as number_of_order,
Sum(ordered_products.quantity*ordered_products.Price) AS Total_Price FROM
ordered_products
LEFT JOIN sellers on sellers.seller_id = ordered_products.seller_id
LEFT JOIN products on products.product_id = ordered_products.product_id
LEFT JOIN shopper_orders on shopper_orders.order_id = ordered_products.order_id
GROUP by ordered_products.order_id ORDER BY
sellers.seller_name,products.product_description

/*1d.
Create table temp1 AS select * from
(SELECT product_id,ifnull(avg(quantity) ,0) as avg_product_quantity_sold from
ordered_products
where ordered_product_status != 'Cancelled' group by product_id);
select * from temp1;
Create table temp2 AS select * from
(SELECT category_id,avg(quantity) as avg_category_quantity_sold from
ordered_products,products where
products.product_id =ordered_products.product_id and
ordered_product_status != 'Cancelled' group by category_id );
select * from temp2;

SELECT
category_description,product_code,product_description, temp1.
avg_product_quantity_sold,temp2. avg_category_quantity_sold
from temp1,temp2,products,categories where categories.category_id=temp2.category_id
and temp1.product_id=products.product_id

SELECT
category_description,product_code,product_description, temp1.
avg_product_quantity_sold,temp2. avg_category_quantity_sold
from temp1,temp2,products,categories where categories.category_id=temp2.category_id
and temp1.product_id=products.product_id
and avg_product_quantity_sold<avg_category_quantity_sold;

/*part2
Create table Seller_review
(seller_id INTEGER,

shopper_id INTEGER,

star_rating varchar(5),
comment varchar(30),

Review_timestamp timestamp,
Constraint pk_seller_review primary key (seller_id, shopper_id),
Constraint fk_seller_seller_review foreign key (seller_id)
references seller(seller_id)
,
Constraint fk_shopper_seller_review foreign key (shopper_id)
references shopper (shopper_id));

Insert into Seller_review values(200000,10000,'**', 'Not satisfied',strftime


('now'));
Insert into Seller_review values(200001,10001,'*****', 'Nice',strftime
('now'));
Insert into Seller_review values(200002,10002,'**', 'Not satisfied',strftime
('now'));
Insert into Seller_review values(200003,10003,'***', 'Satisfied',strftime
('now'));

Create table product_review


(product_id INTEGER,

shopper_id INTEGER,

star_rating varchar(5),
comment varchar(30),
Review_timestamp timestamp,
Constraint pk_product_review primary key (product_id, shopper_id),
Constraint fk_product_product_review foreign key (product_id)
references product(product_id)
,
Constraint fk_shopper_product_review foreign key (shopper_id)
references shopper (shopper_id));

Insert into product_review values(3000000,10000,'**', 'Not satisfied',strftime


('now'));
Insert into product_review values(3000021,10001,'*****', 'Nice',strftime
('now'));
Insert into product_review values(3000055,10002,'**', 'Not satisfied',strftime
('now'));
Insert into product_review values(3000033,10003,'***', 'Satisfied',strftime
('now'));

Create table Questions


(question_id integer primary key,

shopper_id INTEGER,

question varchar(50),

Constraint fk_shopper_Questions
foreign key (shopper_id)
references shopper (shopper_id));
Insert into Questions values(1,10000,'Can we buy this product on EMI?');
Insert into Questions values(2,10001, 'When it will be available');
Insert into Questions values(3,10002,'is this a good product?');
Insert into Questions values(4,10003,'is this available in bulk?');

Create table Answer_of_questions


(answer_id integer primary key,
question_id integer,
answer varchar(100),
Constraint fk_Answer_Questions
foreign key (question_id)
references questions (question_id));

Insert into Answer_of_questions


values(1,1,10000,'Yes');
Insert into Answer_of_questions
values(2,1,10001, 'No');
Insert into Answer_of_questions
values(3,2,10002,'Next month');
Insert into Answer_of_questions
values(4,3,10003,'Yes its an excellent product');

/*Views

Create view Best_seller AS


select sellers.seller_id,seller_name,no_of_best_ratings from sellers,
(select seller_id,count(seller_id) as no_of_best_ratings from Seller_review
group by seller_id having star_rating='*****')TEMP
where sellers.seller_id=TEMP.seller_id;
SELECT * FROM shoppers,Questions,Answer_of_questions
where
shoppers.shopper_id=Questions.shopper_id
and
Questions.question_id=Answer_of_questions.question_id;

You might also like