Relational Algebra 1. Identify products available for [customer demographics]. D
ID: 642406 • Letter: R
Question
Relational Algebra
1. Identify products available for [customer demographics]. Display the product name, store and price.
2. Identify customers with [customer demographics]. Display the customer name and email.
3. Identify [product name] with low customer ratings in the last 6 months at [store location] store. Display the product, price, rating, and the customer description.
4. Identify employees who are not customers. Display the employee name, employee ID and address.
5. Identify products sold [day] at the [store location]. Display the customer name, product purchased, price and address.
6. Identify if customers increase the purchase of hot chocolate in cold weather.
7. Identify customers who live near stores. Display the customer name and store address.
Customers(CID, CCity, CStreet, CZip, CFirst, CLast)
Payment Method(CID, Card#, Code#, CardName, CardFrom, CardTo, PMID)
Store(StoreID, ProID,EpyID, SCity, SStreet, SZip)
Online Store(PID, ShippingCity, ShippingStreet, ShippingZip, ShippingDate, OrderID, DayofOrder)
Products(ProID, ProName, ProInformation, Proquantity, StoreID, ProPrice)
Employee(EpyID, StoreID, EpyCity, EpyStreet, EpyZip, EpyFirst, EpyLast, Salary, EpyDoB)
Customers Comment (Rate, CID, EpyID, Comment)
Puchase(ProID, CID, OrderID, PurID, CPMID, totalprice, PurchaseDay)
Explanation / Answer
Q1
select storeID ,ProName ,ProPrice from Stores,Products where Store.ProID = Products.ProID;
Q2
select CFirst, CLast, CID from Coustomers;
Q3
select Rate , Comment , ProPrice , ProName from Products , (select Rate , ProID , Comment from Store , (select Rate , StoreID , Comment from Employee , (select Rate , EpyID ,Comment from Coustemer Comment where Rate < 2) as x1 where Employee.EpyID = X1.EpyID ) as x2 where Store.StoreID = x2.StoreID ) as x3 where Products.ProdID = x3.ProdID
Q4
select EpyFirst,EpyLast,EpyID, EpyStreet ,EpyCity , EpyZip, from Employee , Coustomers where Coustomers.CID != Employee.EpyID
Q5
Slect ProName , ProPrice , StoreID , CFirst , CLast from Coustmers , Store ,(select ProName , ProPrice, CID , ProID from Purchase , Products where Purchase . ProID = Product.ProID) as x1 where X1.ProID = Store.ProID AND x1.CID = Coustmers.CID;
Q7
select CFirst, CLast , SCity, SStreet, SZip from Store , Coustmers where Coustomers.CCity = Store.SCity AND Coustomer.CZip = Store.SZip;
if they are very near then we have to add the street also
select CFirst, CLast , SCity, SStreet, SZip from Store , Coustmers where Coustomers.CCity = Store.SCity AND Coustomers.CZip = Store.SZip AND Coustmers.CStreet = Store.SStreet;
I am still doing this be patient