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