Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

Thursday, February 2, 2023

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


Saturday, August 13, 2022

SQL Cheat sheet - order of execution of SELECT statement in SQL

What is the order of execution of various clauses or parts of SQL SELECT statement? In what order a SELECT query is executed in SQL? Why SELECT clause is executed at the end in most of the SELECT statements? Order of execution of SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY and FETCH clauses


Order of execution in SQL SELECT query

 

SELECT query in SQL is the most used query to access the database. It has the following clauses (or parts) which can be found in most of the DBMSs like Oracle, MySQL, SQL Server etc.

  • SELECT clause
  • FROM clause
  • WHERE clause
  • GROUP BY clause
  • HAVING clause
  • Order By clause

 

SELECT query, when executed, follows certain order to fetch the records as result to the user. The table given below discusses about the order of execution of various clauses of SELECT query.

Order

of

execution

Clause

Description

Presence

 

1

FROM

SELECT query identifies the records in one (or more tables) as per the conditions (if any) and displays the result to the user.

Hence, the first task is to identify the base table from which the record to be fetched.

Must

1a

JOIN

JOIN is a binary operator to combine rows from two tables and it is a part of FROM clause if used in the query. Hence, it is in position 2 in the order of execution.

JOIN can be used in the FROM clause of a query as INNER JOIN, NATURAL JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CARTESIAN JOIN (‘,’ (comma) between table names is used as the symbol for CARTESIAN JOIN).

Optional

2

WHERE

Once you have chosen the base table(s), now it is the time to mention the filtering constraints (conditions). If your query has any, it is mentioned in the WHERE clause.

After choosing records from base tables, we need to filter them based on certain values as per your requirement. Hence, after FROM (or JOIN) clause, WHERE clause will be executed next.

Optional

3

GROUP BY

If you like to group the result based on certain attributes and their values, GROUP BY clause has to be used. It is a way to specify ‘HOW’ I want to see my data in the result.

It executes after FROM (or JOIN, or WHERE) clause.

You typically use a GROUP BY clause in conjunction with an aggregate expression (eg. AVG(), SUM(), etc.).

 

4

HAVING

HAVING clause is used along with GROUP BY clause in case if you like to apply conditions (filters) on grouped data.

For example, assume that you have counted number of employees in each department (using COUNT(*) and ‘GROUP BY did’) and now you want to include that departments that have more than 5 employees. Here, we use HAVING clause.

Optional

5

SELECT

SELECT will take parameters (* OR list of columns OR functions OR arithmetic operations). As per the presence of parameters in the query, SELECT will pick and show necessary data from the results produced by the execution of previous clauses.

For example, the SELECT clause in “SELECT sname, age FROM student WHERE gender = ‘F’;” takes two columns. SELECT will select and show only sname and age columns after the execution of FROM clause and WHERE clause are completed.

Must

6

ORDER BY

ORDER BY clause sorts (ascending or descending) the result data produced by SELECT query.

Optional

7

TOP / LIMIT / FETCH

They are used to limit the number of records to be displayed as a result.

TOP is used by SQL Server, LIMIT is supported by MySQL, and Oracle (versions 12 or later) supports FETCH.

Optional

 

Examples - Order of execution of SQL clauses in SELECT query:

Example 1:

Example 2:
Examples 3:
Example 4:
Example 5:

 

What is the order of execution of various clauses or parts of SQL SELECT statement? 

In what order a SELECT query is executed in SQL? 

Why SELECT clause is executed at the end in most of the SELECT statements?

In which order, the clauses in the following query executed? Why?

List and execute the SELECT query clauses execution order in detail.

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents

data recovery