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.
***************