Understanding SQL queries - Multiple Choice Questions

 Understanding SQL queries / what is the result of this sql query / difference between distinct query, normal query and subquery / check the following statements and justify whether true or false

Question:

Consider the relation schemas of a database X given below. Primary keys of all the tables are underlined.

        COUNTRY (cid, cname)

        LOCATION (locid, countryid, lname, population)

        DEPARTMENT (did, dname, noOfEmp, location, country, manager)

Consider the following queries;

A. SELECT sum(population) FROM Department D, Location L WHERE D.location=L.locid AND D.country=L.countryid;

B. SELECT sum(DISTINCT population) FROM Department D, Location L WHERE D.location=L.locid AND D.country=L.countryid;

C. SELECT sum(population) FROM Location WHERE (locid,countryid) IN (SELECT location, country FROM Department);

Use the queries A, B, and C and the database X given above, answer the following questions;

 

Check for each of the following statements if it is true or false. Justify your answers.

1. A and B always return the same result.

2. B and C always return the same result.

3. One of A, B, and C has a correlated sub-query.

4. One of the queries computes the total population in all countries.

 

Answer:

 

1. False. Query A counts every occurrence of the population value for each department, whereas query B eliminates duplicates and counts unique values alone (because of DISTINCT).

 

2. False. They may differ if two locations have the same population. In such cases, query B counts the population only once whereas query C counts each population value.

 

3. False. The only query that has a sub-query is query C which is not correlated. Correlated sub-queries are sub-queries that are dependent on the outer query and are executed for each row of the main query.

Query C is considered as regular sub-query (non-correlated) that is independent of the outer query and is evaluated only once before the main query runs.

 

5. False. None of them counts the population in locations with NO departments.

That is, for example, let us suppose we have location X. X has a location id, country id and population. If this location id and country id are not part of department table (ie., there is no department in X), then X’s population is not counted. This is TRUE for all queries.

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

Keywords:

Find the difference between two SQL queries
Why two similar queries behave differently
What difference a sub-query and a distinct keyword make in execution of an SQL query?

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

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.