Question

Give the relational algebra, tuple calculus, and SQL expressions for each using the below schema:

SPERSON(EmpID, Name, Dept)

TRIP(TripID, EmpID, ToCity, StartDate, EndDate)

EXPENSE(ExpID, TripID, AccountID, Amount)

a) List the names and employee ID of a salesperson.

b) List employee ID and employee names that took a trip(s) to Charleston city.

c) List the names of all employees who have spent more than $500 on their trip.

d) List employees' names and their IDs that have never taken a trip.

e) List the names of all salespersons that have at least been on one trip.

please type your answers in text!

Answer 1

(Ans. a)select name,empid from sperson,trip,expense where sperson.empid=trip.empid and trip.tripid=expense.tripid

(Ans b)select name,empid from sperson,trip,expense where sperson.empid=trip.empid and trip.tripid=expense.tripid and tocity="Charleston city"

(Ans c)select name from sperson,trip,expense where sperson.empid=trip.empid and trip.tripid=expense.tripid and amount>500

(Ans d) select name,empid from sperson,trip where sperson.empid != trip.empid

(Ans e)select name from sperson,trip where sperson.empid=trip.empid and tripid is not Null