Using the following relations:
consultant(id,Name,Skill)
CustomerCompany(Id,name Address, Phone, Email, WebAddr,Market)
project(id,StartDate,EndDate,ConsultantID,CustomerId,Days)
Invoice(id,Date,Customer,Amount,Status)
Im trying to work out the following scenario using SQL and Relational algebra
Find the names of the consultants and the names of customers, where the consultant has worked for the customer, and the customer received an invoice in the range of GBP 100k to 200k
Using SQL i have:
Select I.amount, C.name
from CustomerCompany C, Invoice I
where I.Customer= C.id and > all
(
select C.name, Con.Name
from CustomerCompany, Con Consultant
where i.amount between 100 and 200
);
Relational algebra:
amount = σ(Invoice, amount>=100 and amount<=200)
joininv= Ⓧ(amount, Customer, CustomerCompany, id)
joincon Ⓧ(joiniv, Consultant id, Project, ConsultantID)
π =(joincon, name, Name)
I was wondering if write or wrong?
Thanks for any help!