关系代数和SQL求解 [英] Relational algebra and SQL solving

问题描述

使用以下关系:

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)

我正在尝试使用SQL和关系代数解决以下情况

Im trying to work out the following scenario using SQL and Relational algebra

查找顾问的姓名和顾问为客户工作的客户的姓名，并且客户收到的发票在10万英镑至20万英镑之间

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

使用SQL我有:

从客户公司C中选择I.amount，C.name，发票I在哪里 I.Customer = C.id和> all(从CustomerCompany，Con Consultant中选择C.name，Con.Name，i.amount 在100到200之间)；

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);

关系代数:

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?

感谢您的帮助！

推荐答案

这将完成技巧

select 
con.Name
from CustomerCompany CustComp
inner join project PRO on PRO.CustomerId=CustComp.Id
inner join consultant con on con.id=PRO.ConsultantID
inner join Invoice Inv on Inv.Customer=CustComp.name
where Inv.Amount between 100 and 200

JOIN

JOIN

这篇关于关系代数和SQL求解的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！