A relational database contains two tables, student and department. The student…
2004
A relational database contains two tables, student and department. The student table has columns roll_no, name, and dept_id; the department table has columns dept_id and dept_name. The following INSERT statements were executed successfully to populate the empty tables:
INSERT INTO department VALUES (1, 'Mathematics');
INSERT INTO department VALUES (2, 'Physics');
INSERT INTO student VALUES (1, 'Navin', 1);
INSERT INTO student VALUES (2, 'Mukesh', 2);
INSERT INTO student VALUES (3, 'Gita', 1);How many rows and columns will be retrieved by the following SQL statement?
SELECT * FROM student, department;Answer: D. 6 rows and 5 columns — ConceptWhen a FROM clause lists multiple tables without a join condition, the intermediate relation is their Cartesian product: every row of one table is…
- A.
0 row and 4 columns
- B.
3 rows and 4 columns
- C.
3 rows and 5 columns
- D.
6 rows and 5 columns
Attempted by 968 students.
Show answer & explanation
Correct answer: D
Concept
When a FROM clause lists multiple tables without a join condition, the intermediate relation is their Cartesian product: every row of one table is paired with every row of the other.
For SELECT *, the output keeps all columns contributed by that intermediate relation. Identically named columns are not merged unless the query uses a construct such as NATURAL JOIN or JOIN ... USING.
Application
The student table contains 3 rows, and the department table contains 2 rows.
The Cartesian product therefore contains 3 × 2 = 6 rows.
The student table contributes 3 columns, while the department table contributes 2 columns.
SELECT * therefore projects 3 + 2 = 5 columns; the two dept_id columns remain separate output columns.
Cross-check
Enumerating the row pairs gives two department pairings for each of the three students, for six tuples. Each tuple contains the three student values followed by the two department values.
Result
The query retrieves 6 rows and 5 columns.