“Who is the borrower whose id is 12345?”σ borrower borrower_id = 12345select *from borrowerwhere borrower_id = '12345';“List the names of all borrowers”π borrower last_name first_nameselect last_name, first_namefrom borrower;The rows happen to come out in alphabetic order of last name in our example database, because that happens to be the way they were inserted into the database. In SQL, we could get a different order by requesting it. Relational Algebra incorporates no such provision, since relations are sets.select last_name, first_namefrom borrowerorder by borrower_id;“What is the title of the book whose call number is QA76.093?”π σ book title call_number = QA76.093select titlefrom bookwhere call_number = 'QA76.093';Because Relational Algebra relations are sets, the project operation can result in eliminating rows if two or more rows happen to agree on the attributes being projected. In SQL, this behavior is not the default, but can be requested explicitly using "distinct". Compare the results of :select authorfrom book;andselect distinct authorfrom book;“List the titles of all books that are currently checked out”π (checked_out |X| book) title-which is equivalent to:π (checked_out ϑ book) title checked_out.call_number = book.call_numberORπ σ (checked_out X book) title checked_out.call_number = book.call_number(Some SQL implementations do not provide natural join. The second and third Relational Algebra syntaxes above correspond to the way one would do natural join on such systems)All of the following are SQL equivalents - but not all implementations support all syntaxes: select titlefrom checked_out natural join bookor select titlefrom checked_out join book on checked_out.call_number = book.call_numberor select titlefrom checked_out, bookwhere checked_out.call_number =book.call_number“List the names of all borrowers having one or more books overdue”π σ (checked_out |X| borrower) last_name, date_due < today first_name select last_name, first_namefrom checked_out natural join borrowerwhere date_due < current date;or select last_name, first_namefrom checked_out join borrower on checked_out.borrower_id = borrower.borrower_idwhere date_due < current date;“List the names of all employees who earn more than their supervisor”π σ (ρ employee ϑ ρ employee) e.last_name e.salary > s.salary e e.supervisor_ssn = s e.first_name s.ssn (Natural join cannot be used here, because we are joining based on columns with different names in the two versions of the table)select e.last_name, e.first_namefrom employee as e join employee as son e.supervisor_ssn = s.ssnwhere e.salary > s.salary;“List the names of all people connected with the library - whether borrowers, employees, or both.” (π borrower) ∪ (π employee) last_name, last_name, first_name first_name(select last_name, first_name from borrower)union(select last_name, first_namefrom employee);Contrast results of the above with(select last_name, first_name from borrower)union all(select last_name, first_namefrom employee);“List the names of all borrowers who are not employees.” (π borrower) − (π employee) last_name, last_name, first_name first_name(select last_name, first_name from borrower)except(select last_name, first_namefrom employee);“List all books needed as course reserves that are currently checked out to someone”(π reserve_book) ∩ (π checked_out) call_number call_number(select call_number from reserve_book) intersect(select call_number from checked_out);“List the names of all employees together with their supervisor’s name.” π σ (ρ employee X ρ employee) e.last_name e.supervisor_ssn = e s e.first_name s.ssn s.last_name s.first_nameselect e.last_name, e.first_name, s.last_name, s.first_namefrom employee as e join employee as son e.supervisor_ssn = s.ssn;“List the call numbers of all overdue books, together with the number of days they are overdue”π σ checked_outcall_number date_due < todaytoday - date_dueselect call_number, current date - date_duefrom checked_outwhere date_due < current date;“What is the average salary of all employees?”G employee average(salary)select avg(salary)from employee;“Print a list of borrower ids and the number of books each has out”G checked_outborrower_id count(call_number)select borrower_id, count(*)from checked_outgroup by borrower_id;The following variant of the above is easily expressed in SQL, though awkward in Relational Algebra:“Print a list of borrower ids and the number of books each has out, but only for borrowers who have at least two books out”select borrower_id, count(*)from checked_outgroup by borrower_idhaving count(*) >= 2;“List the titles of all books, together with the borrower id of the person (if any) who has the book out.”π book |X| checked_out title borrower_idselect title, borrower_id from book natural left outer join checked_outORselect title, borrower_id from book left outer join checked_out on book.call_number=checked_out.call_number;Revisiting “List the names of all employees together with their supervisor’s name.” to include Aardvark in the result. (He was not included in the original query because he has no supervisor)This is something that is easy to do in SQL, but somewhat awkward in Relational Algebraselect e.last_name, e.first_name, s.last_name, s.first_namefrom employee as e left outer join employee as son e.supervisor_ssn = s.ssnThe following operations are not supported by many versions of SQL•"Natural join• DivisionBoth can by synthesized from other operations where needed• Assume tables A and Z have schemes (a, b, c) and (c, d, e) - with attribute c in commonthen A |X| B isselect a, b, A.c, d, efrom A join Z on A.c = Z.c • Division gets really messy! (but it can be