Oracle SQLPLUS: Please answer the following question. Thank you for your help. S

Question

Oracle SQLPLUS: Please answer the following question. Thank you for your help.

Suppliers(sid: integer, sname: varchar(20), address: varchar(50))
Parts(pid: integer, pname: varchar(20), color: varchar(15))
Catalog(sid: integer, pid: integer, cost: real)
• The key fields are underlined
• Domains of each field are listed after field name
• The Catalog relation lists prices charged for parts by Suppliers

Write the following queries in relational algebra.
(i) Find the names of suppliers who supply some blue part
(II) Find the sids of suppliers who supply some blue or red part
(III) Find the sids of suppliers who supply some blue part and some red part
(iv) Find the sids of suppliers who supply every blue part
(v) Find the pids of parts supplied by every supplier at less than $50. (If a supplier either
does not supply the part or charges more than $50, the part is not selected.)
(vi) Find the sids of suppliers who do not supply a red part

Explanation / Answer

Hi,
(i) Find the names of suppliers who supply some blue part
For this we need suppliers ,parts and catalog tables to be joined like below
select s.sname from Suppliers s,Parts p,Catalog c where p.pid=c.pid and c.sid=s.sid and p.color='blue';--adding where condition after join and selcting only sname
(II) Find the sids of suppliers who supply some blue or red part
in this since we only need sids. we can just join parts and catalog tables like below
select c.sid from Parts p,Catalog c where p.pid=c.pid and (color='red' or p.color='blue');--added where condition to include both red and blue color
(III) Find the sids of suppliers who supply some blue part and some red part
This is exactly same as above query but with and in where
select c.sid from Parts p,Catalog c where p.pid=c.pid and (color='red' AND p.color='blue');
(iv) Find the sids of suppliers who supply every blue part
select c.sid from Parts p,Catalog c where p.pid=c.pid and color='blue'

(v) Find the pids of parts supplied by every supplier at less than $50.
select p.pid from Parts p,Catalog c where p.pid=c.pid and c.cost<50;

(vi) Find the sids of suppliers who do not supply a red part

This is again same as ii but with a negation.
select c.sid from Parts p,Catalog c where p.pid=c.pid and color!='red'

Thumbs up if this was helpful, otherwise let me know in comments

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.