Bissoy.com
Doctors Medicines MCQs Q&A

Consider the following relation schema Sailors(sid, sname, rating, age) Reserve(sid, bid, day) Boats(bid, bname, color) What is the equivalent of following relational algebra query in SQL query. πsname((σcolor= ‘red’Boats) ⨝ Reserves ⨝ Sailors)  

Consider the following relation schema Sailors(sid, sname, rating, age) Reserve(sid, bid, day) Boats(bid, bname, color) What is the equivalent of following relational algebra query in SQL query. πsname((σcolor= ‘red’Boats) ⨝ Reserves ⨝ Sailors)   Correct Answer <p>SELECT S.sname</p> <p>FROM Sailors S, Reserves R, Boats B</p> WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ‘red’

Sailors:

Sid

Sname

Rating

Age

S1

SA

1

30

S2

SB

2

40

S3

SC

3

50

S4

SD

3

20

S5

SA

4

30

 

Reserves:

Sid

Bid

Day

S1

B1

Tue

S2

B2

Wed

S3

B3

Thu

S4

B4

Fri

S5

B5

Mon

 

Boats:

Bid

Bname

Color

B1

BA

Red

B2

BB

Blue

B3

BC

Red

B4

BD

Green

B5

BA

Red

 

 Given query: πsname((σcolor= ‘red’Boats) ⨝ Reserves ⨝ Sailors) 

This query first find the boats having red color than matches B.Bid with R.Sid where it finds equal than matches R.Sid with S.Sid  , at that point finds the name of sailors with same sid.

Here it results in sailor SA, SC, and SD.

This query matches with:

SELECT S.sname

FROM Sailors S, Reserves R, Boats B

WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ‘red’

Related Questions

Consider the schema Sailors(sid, sname, rating, age) with the following data Sid Sname Rating age 22 Dustin 7 45 29 Borg 1 33 31 Pathy 8 55 32 Robert 8 25 58 Raghu 10 17 64 Herald 7 35 71 Vishnu 10 16 74 King 9 35 85 Archer 3 26 84 Bob 3 64 96 Flinch 3 17 For the query SELECT S.rating, AVG(S.age) AS avgage FROM Sailors S Where S.age >= 18 GROUP BY S.rating HAVING 1 < (SELECT COUNT(*) FROM Sailors S2 where S.rating = S2.rating) The number of rows returned is
Consider the following relational schema. Students(rollno: integer, sname: string) Courses(courseno: integer, cname: string) Registration(rollno: integer, courseno: integer, percent: real) Which of the following queries are equivalent to this query in English? “Find the distinct names of all students who score more than 90% in the course numbered 107” (I) SELECT DISTINCT S.sname FROM Students as S, Registration as R WHERE R.rollno=S.rollno AND R.courseno=107 AND R.percent >90 (II) Πsname (σcourseno = 107∧percent>90 (Registration ⋈ Students)) (III) {T | ∃S ∈ Students, ∃R ∈ Registration (S.rollno = R.rollno ∧R. courseno = 107 ∧ R.percent > 90 ∧ T.sname = S. sname)} (IV) {〈SN〉 | ∃SR∃RP(〈SR, SN〉 ∈ Students ∧ 〈SR, 107, RP〉 ∈ Registration ∧ RP > 90)}
What does the following query find?
(SELECT DISTINCT r.sid
FROM boats b, reserves r
WHERE b.bid = r.bid
AND b.color = 'red')
MINUS
(SELECT DISTINCT r.sid
FROM boats b, reserves r
WHERE b.bid = r.bid
AND b.color = 'green')
Suppliers(Sid: integer, sname:string, city:string, street:string) Parts(pid:integer, pname:string, color:string) Catalog(sid:integer, pid:integer, cost:real) Consider the following relational query on the above database: SELECT S.sname FROM Suppliers S  WHERE S.sid NOT IN (SELECT C.sid FROM Catalog C WHERE C.pid NOT IN (SELECT P.pid FROM Parts P WHERE P.color < > ‘blue’)) Assume that relations corresponding to the above schema are not empty. Which of the following is the correct interpretation of the above query?
Girls in a shopping mall like dresses of three different colors; Red, Blue, and Black. 45% of total girls like red color dress, out of which 60% like only red color. If 36 girls like red color dress along with at least one more color, 10% like both red and blue color while 60% of total girls like black color dress, then how many girls like black color or blue or both color dress but not red color?
Consider the following three relations in a relational database. Employee(eId, Name), Brand(bId, bName), Own(eId, bId) Which of the following relational algebra expressions return the set of eIds who own all the brands? 
Consider the relational schema given below, where eId of the relation dependent is a foreign key referring to empId of the relation employee. Assume that every employee has at least one associated dependent in the dependent relation. Employee (empId, empName, empAge) Dependent(depId, eId, depName, depAge) Consider the following relational algebra query: ΠempId(employee)-ΠempId (employee⋈(empId = eID)∧(empAge ≤ depAge)dependent) The above query evaluates to the set of empIds of employees whose age is greater than that of
In a group, 22 women like Red color, 20 women like the blue color and 19 women like Yellow color. Among them, 5 women like both Red and Blue color, 5 women like both Yellow and Blue color and 9 women like both Red and Yellow color. 14 women like only Blue color and 4 women all the three colors. Find the total number of women who like only Red color.
Consider the following four relational schemas. For each schema, all non-trivial functional dependencies are listed. The underlined attributes are the respective primary keys. Schema I: Field ‘courses’ is a set-valued attribute containing the set of courses a student has registered for. Non-trivial functional dependency: rollno → courses Schema II: Non-trivial functional dependencies: rollno, courseid → email email → rollno Schema III: Non-trivial functional dependencies: rollno, courseid → marks, grade marks → grade Schema IV: Non-trivial functional dependencies: rollno, courseid → credit courseid → credit Which one of the relational schemas above is in 3NF but not in BCNF?
Given below are quantities named A,B and C. Based on the given information, you have to determine the relation between the quantities. You should use the given data and your knowledge of mathematics to choose among the possible answer. Akash’s present age is six years less than Badshah’s age after nine years. The ratio of present age of Badshahand RAFTAAR is 5:9. Raftaar is 21 years younger than Denny and Denny is three years older than Honey. Square of the Honey’s present age is 1350 more than 15 times of Honey’s present age? Quantity A: What is the difference between AKASH’s present age and HONEY’s age after 16 years? Quantity B: After 6 years Mankirat’s age is twice that of Akash’s present age. Diljeet’s present age is 3.5 times of 25% of sum of present age of Mankirat and A. What is the present age of Diljeet? Quantity C: Twice of A’s present age is eight years more than that of Babbu’spresent age, then find the Babbu’s present age?