Question: Given two relations, student(sid: integer, name: string) and friend(sid1: integer, sid2: integer) Which of the following expressions/queries will find all pairs of students who have

Given two relations, student(sid: integer, name: string) and friend(sid1: integer, sid2: integer) Which of the following expressions/queries will find all pairs of students who have at least two mutual friends? HINT: Join student or friend table with itself (self-joins) and think of next steps(more joins or filtering maybe?) SELECT ALL THAT APPLY BY ticking the text box 1) s1.sid, s2.sid ( (STUDENT AS s1) (STUDENT AS s2) ( ( sid1, sid2 (FRIEND)) AS f1 ( sid1, sid2 (FRIEND)) AS f2 |f1.sid1 = s1.sid AND f2.sid1 = s1.sid AND f1.sid2 = s2.sid AND f2.sid2 = s2.sid |f1.sid1 = s2.sid AND f2.sid1 = s2.sid AND f1.sid2 = s1.sid AND f2.sid2 = s1.sid ) |s1.sid < s2.sid ) 2) s1.sid, s2.sid ( s1.sid < s2.sid AND ( f1.sid1 = s1.sid AND f2.sid1 = s2.sid ( f1.sid2 = f2.sid2 ( FRIEND f1.sid1, f1.sid2 FRIEND f2.sid1, f2.sid2 FRIEND ) ) GROUP BY s1.sid, s2.sid HAVING COUNT(*) >= 2 ) STUDENT AS s1 STUDENT AS s2 ) 3) SELECT s1.name AS student1, s2.name AS student2, COUNT(*) AS num_mutual_friends FROM student s1 JOIN friend f1 ON s1.sid = f1.sid1 JOIN friend f2 ON s2.sid2 = f2.sid1 AND f1.sid1 <> f2.sid2 JOIN friend f3 ON f2.sid2 = f3.sid1 AND f1.sid1 <> f3.sid2 AND f2.sid1 <> f3.sid2 JOIN student s2 ON s2.sid = f3.sid2 AND s2.sid <> s1.sid GROUP BY s1.sid, s2.sid HAVING COUNT(*) >= 2; 4) SELECT s1.name AS student1, s2.name AS student2, COUNT(*) AS num_mutual_friends FROM student s1 JOIN friend f1 ON s1.sid = f1.sid1 JOIN friend f2 ON f1.sid2 = f2.sid1 AND f1.sid1 <> f2.sid2 LEFT JOIN friend f3 ON f2.sid2 = f3.sid1 AND f1.sid1 <> f3.sid2 AND f2.sid1 <> f3.sid2 JOIN student s2 ON s2.sid = f3.sid2 AND s2.sid <> s1.sid GROUP BY s1.sid, s2.sid HAVING COUNT(*) >= 2; 5) None of the above 6) SELECT s1.name AS student1, s2.name AS student2 FROM student s1 JOIN friend f ON s1.sid = f.sid1 JOIN student s2 ON s2.sid = f.sid2 AND s2.sid <> s1.sid GROUP BY s1.sid, s2.sid HAVING COUNT(*) >= 2; 7) f1.sid1, f2.sid1 ( f1.sid1 < f2.sid1 AND f1.sid2 = f2.sid2 AND ( f1.sid1, f1.sid2 (FRIEND) f2.sid1, f2.sid2 (FRIEND) ) GROUP BY f1.sid1, f2.sid1 HAVING COUNT(*) >= 2

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related General Management Questions!

1.) Below are the student, course and enrolled tables used in the examples in the review videos: noctarac=H CRI ECT FROM enrolled; Which of the following options is true for these relational schema?...

Given two relations, student(sid: integer, name: string) and friend(sid1: integer, sid2: integer) Which of the following expressions/queries will find all pairs of students who have at least two...

Solve using Relational Algebra (A similar question was posted before but its not relational algebra) I have posted below solutions to another Relational Algebra question from my textbook which shows...

25. What does the following query compute? 16- Following ER diagram is given to you. There is a 1:1 relationship between Professor and Department entities. Which of the following options are correct...

=== Module Description === This file contains classes that describe a survey as well as classes that described different types of questions that can be asked in a given survey. from __future__ import...

Write the following queries in SQL. No duplicates should be printed for any answer. Consider the following relations: Student(snum: integer, sname: string, major: string, level: string, age: integer)...

Priority reversal can happen when strings of varying needs synchronize on admittance to normal assets - strings of more prominent need might wind up looking out for strings of lesser need, prompting...

Help needed C++ programming. please read the Assignment Overview carefully as it instruct what is allowed to be used for this code in bold. Assignment Overview This assignment focuses on the design,...

C++ Help Please!!!!! Have you ever wondered how social media sites suggest friends to you? Well, most of the sites do indeed use highly sophisticated algorithms, but for this project you will...

Suzuki Corporation operates in a state with no sales tax. Record the following transactions in a general journal: DATE TRANSACTIONS 2016 June 5 Sold merchandise on account to Mahonga Company; issued...

The total revenue function for AutoBright car washing service (Problem) is believed to be incorrect due to a changing demand pattern. Based on new information, the demand function is now estimated to...

Which one of the following is not a private source of retirement income? 0 2 0 7 : 2 8 Multiple Choice Traditional IRAs reock Socian Security Defined contribution plans Defined benefit plans Spousal...

Premise: Using a dictionary and list, create a set of key / value pairs that consists of a musical database. The key / value pairs should consist of Artist Name: Song Names pattern. For example, EACH...