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 Cheatsheet - SELECT FROM WHERE clauses with examples

SQL Cheat sheet for beginners, SELECT clause, FROM clause, WHERE clause explained with examples.

 

 SELECT ... FROM ... WHERE clauses in SQL - Cheat sheet

Querying data using SELECT…FROM...WHERE clauses in SQL
Commands/Clauses	Examples
SELECT 👈 To view required data from one or more tables.   Syntax: SELECT parameters FROM tablename;   Parameters for SELECT: *, list of attributes, functions, arithmetic operations are the parameters for the command (clause) SELECT.	Parameters examples for SELECT clause Simple: SELECT * FROM student; Result: Displays all columns of all records of student table. List of attributes: SELECT name, phone FROM student; Result: Displays only name and phone of all students; Function: SELECT MAX(salary) FROM employee; Result: Displays the maximum of all salary values stored in employee table. Operations: SELECT Basic + HRA FROM employee; Result: Displays the sum of the values stored in Basic and HRA columns (attributes) of all employee records.
FROM 👈 To specify the name of the table/view from which we want to view the data.   Syntax: SELECT parameters FROM tablename; Parameters for FROM: Table name or table names separated with comma.	Parameters examples for FROM clause Single table: SELECT * FROM employee; Result: Displays all records from employee table. Multiple tables: SELECT * FROM employee, dept; Result: It is a special case. It performs join. Displays columns of tables, employee and dept with every record of table employee combined with every record of dept.
WHERE 👈 To specify one or more conditions to filter the data to view/get required data only.   Syntax: SELECT parameters FROM tablename WHERE condition;   Parameters for WHERE: WHERE clause accepts the conditions (predicates) of the following format; Attribute_name OP value; Attribute_name OP attribute_name; Here, OP refers to OPERATOR that can be one of =, <>, >, <, >=, and <=   More conditions can be mentioned in WHERE clause. In that case, the conditions should be connected using logical connectives AND, OR, NOT.	Parameter examples for WHERE clause attribute_name OP value: SELECT * FROM employee WHERE gender = ‘male’; Result: Displays all records of employee who are MALE.   SELECT * FROM employee WHERE salary > 10000; Result: Displays all records of employees who earn more than 10000.   SELECT name, age FROM employee WHERE salary = 25000; Result: Displays only name and ages of all employees who earn 25000. attribute_name OP attribute_name: SELECT * FROM employee, dept WHERE emp_dno = dept_dno;

 

***********

 

What are the parameters for SELECT clause?

Can we perform arithmetic calculations using SELECT clause in SQL?

How to filter the data in a table using WHERE clause?

Can we use more than one condition in WHERE clause?

How do you find cars that are sold for fifty thousand dollars or more?

Where to specify the condition like the watches that are costlier than Omega watches?