21.What is the difference between cross joins and natural joins?
The cross join produces the cross product or Cartesian product of two tables. The natural join is based on all the columns having same name and data types in both the tables.
22.What is the purpose of the group functions in SQL? Give some examples of group functions.
Group functions in SQL work on sets of rows and returns one result per group. Examples of group functions are AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE.
23.Say True or False. Give explanation if False.
By default the group functions consider only distinct values in the set.
By default, group functions consider all values including the duplicate values.
24.Say True or False. Give explanation if False.
The DISTINCT keyword allows a function consider only non-duplicate values.
True.
25.Say True or False. Give explanation if False.
All group functions ignore null values.
True.
26.Say True or False. Give explanation if False.
COUNT(*) returns the number of columns in a table.
False. COUNT(*) returns the number of rows in a table.
27.Say True or False. Give explanation if False.
COUNT(*) returns the number of columns in a table.
False. COUNT(*) returns the number of rows in a table.
28.What’s wrong in the following query?
SELECT subject_code, count(name)
FROM students;
It doesn’t have a GROUP BY clause. The subject_code should be in the GROUP BY clause.
SELECT subject_code, count(name)
FROM students
GROUP BY subject_code;
29.What’s wrong in the following query?
SELECT subject_code, AVG (marks)
FROM students
WHERE AVG(marks) > 75
GROUP BY subject_code;
The WHERE clause cannot be used to restrict groups. The HAVING clause should be used.
SELECT subject_code, AVG (marks)
FROM students
HAVING AVG(marks) > 75
GROUP BY subject_code;
30.Say True or False. Give explanation if False.
Group functions cannot be nested.
False. Group functions can be nested to a depth of two.
