SQL solved exercise using JOIN, GROUP BY, subqueries

Structured Query Language exercise, SQL lab exercise solved, SQL join queries explained, Easy join, group by clauses and subqueries

SQL solved exercise

Consider a social network database, about users and their relationships. The database has two relations:

Users (uid, name)

Relationship (uid1, rel, uid2)

Here uid is the key for Users relation; uid1 and uid2 are foreign keys and both are referencing uid of Users; rel is a string representing the relation type, and the value can be friend or enemy. Note that the relationship is not necessarily symmetric: if Alice is friend with Bob, this does not imply that Bob is friend with Alice.

 

Query 1:

Find the names of all friends of Alice.

 

Solution 1:

SELECT name FROM Users WHERE uid IN (SELECT uid2

FROM Users x, Relationship y

WHERE x.uid = y.uid1 AND x.name = ‘Alice’ AND y.rel = ‘friend’);

 

Explanation:

We need the names of Alice’s friends. The query has two parts;

Inner query: selects all the user ids of friends of Alice by joining the tables Users with Relationship. Mentioning the list of tables separated by comma in FROM clause represents basic Cartesian product join.

Outer query: to identify and display the names of the uids from the result of inner query.

IN in outer query: the result of inner query may consist of zero or more records. To include the names of all the uids that are in the result of inner query, the IN operator is used.

 

Query 2:

Write a SQL query that computes, for each user, the total number of their friends. Your query should return results containing the uid, the name, and the count. Note that your query must return exactly one answer for every user in Users.

 

Solution 2:

SELECT x.uid, x.name, count(*)

FROM Users x LEFT OUTER JOIN Relationship y

ON x.uid = y.uid1 AND y.rel='friend'

GROUP BY x.uid, x.name;

 

Explanation:

SELECT clause: We need the uid, name and the count of their friends. FROM clause: uid and name is in Users table. To find the number of friends, we need to join Users with Relationship.

JOIN...ON: To make a valid join, every record of both tables should be compared using common attributes (x.uid = y.uid1).

Condition: y.rel=’friend’ is included because we are interested only in the value ‘friend’.

GROUP BY clause: As per the question, we need to display uid and name along with the count of friends. To include attributes along with an aggregate function (count) in SELECT clause, we need to group the results on the attributes needed. 

 

Note: the names x and y in the above queries are called as tuple variables or aliases or rename variables. They are useful in disambiguating the attribute names in case if attribute names of two different tables are same. In these queries, they are not needed.

 

*************

 

SQL solved exercise on social network database

How to use group by clause in a social network database

Use of left outer join to join tables in SQL

Use of IN operator in SQL 

Use of aliases/tuple variables/rename variables in SQL