Consider the following tables (relations): Students: Roll-No Name 18CS101 Ramesh 18CS102 Mukesh 18CS103 Ramesh Performance: Roll-No Course Marks 18CS101 DBMS 60 18CS101 Compiler Design 65 18CS102 DBMS 80 18CS103 DBMS 85 18CS102 Compiler Design 75 18CS103 Operating System 70 Primary keys in the tables are shown using Underline. Now, Consider the following query: SELECT S.Name, Sum (P.Marks) FROM Students S, Performance P WHERE S.Roll-No = P.Roll-No GROUP BY S.Name The number of rows returned by the above query is
2
Understanding the SQL Query and Database Tables
The question asks us to determine the number of rows returned by a given SQL query executed on two tables: Students and Performance. Let's first examine the structure and data in these tables.
| Roll-No | Name |
|---|---|
| 18CS101 | Ramesh |
| 18CS102 | Mukesh |
| 18CS103 | Ramesh |
| Roll-No | Course | Marks |
|---|---|---|
| 18CS101 | DBMS | 60 |
| 18CS101 | Compiler Design | 65 |
| 18CS102 | DBMS | 80 |
| 18CS103 | DBMS | 85 |
| 18CS102 | Compiler Design | 75 |
| 18CS103 | Operating System | 70 |
The primary keys are Roll-No in both tables. Note that the Students table has duplicate names ('Ramesh') associated with different Roll-Nos.
The SQL query is:
SELECT S.Name, Sum (P.Marks) FROM Students S, Performance P WHERE S.Roll-No = P.Roll-No GROUP BY S.Name
Analyzing the SQL Query Components
- FROM Students S, Performance P: This specifies that we are querying from the Students table (aliased as S) and the Performance table (aliased as P).
- WHERE S.Roll-No = P.Roll-No: This is the join condition. It combines rows from the Students table with rows from the Performance table where the Roll-No values match. This is an inner join based on the `Roll-No` column.
- SELECT S.Name, Sum (P.Marks): This specifies the output columns. We want to see the student's name (from the Students table) and the sum of marks (from the Performance table). The Sum() is an aggregate function.
- GROUP BY S.Name: This clause groups the rows from the joined result based on the values in the S.Name column. The aggregate function Sum(P.Marks) will be applied to each group.
Executing the Query Step-by-Step
Step 1: Perform the JOIN operation
The WHERE S.Roll-No = P.Roll-No clause joins rows from Students and Performance where the Roll-No matches. The intermediate result looks like this:
| S.Roll-No | S.Name | P.Roll-No | P.Course | P.Marks |
|---|---|---|---|---|
| 18CS101 | Ramesh | 18CS101 | DBMS | 60 |
| 18CS101 | Ramesh | 18CS101 | Compiler Design | 65 |
| 18CS102 | Mukesh | 18CS102 | DBMS | 80 |
| 18CS102 | Mukesh | 18CS102 | Compiler Design | 75 |
| 18CS103 | Ramesh | 18CS103 | DBMS | 85 |
| 18CS103 | Ramesh | 18CS103 | Operating System | 70 |
Step 2: Apply GROUP BY S.Name
The query now groups the rows from the joined result based on the unique values in the S.Name column. The unique names are 'Ramesh' and 'Mukesh'.
- Group 'Ramesh': This group includes all rows from the joined result where S.Name is 'Ramesh'. These are the rows corresponding to Roll-Nos 18CS101 and 18CS103.
- (18CS101, Ramesh, 18CS101, DBMS, 60)
- (18CS101, Ramesh, 18CS101, Compiler Design, 65)
- (18CS103, Ramesh, 18CS103, DBMS, 85)
- (18CS103, Ramesh, 18CS103, Operating System, 70)
- Group 'Mukesh': This group includes all rows from the joined result where S.Name is 'Mukesh'. These are the rows corresponding to Roll-No 18CS102.
- (18CS102, Mukesh, 18CS102, DBMS, 80)
- (18CS102, Mukesh, 18CS102, Compiler Design, 75)
Step 3: Calculate Sum(P.Marks) for each group
The Sum(P.Marks) aggregate function is applied to the P.Marks column within each group.
- For Group 'Ramesh': Sum of Marks = $60 + 65 + 85 + 70 = 280$
- For Group 'Mukesh': Sum of Marks = $80 + 75 = 155$
Step 4: SELECT S.Name, Sum(P.Marks)
The final result set contains one row for each group, showing the group name (S.Name) and the calculated sum of marks.
| S.Name | Sum(P.Marks) |
|---|---|
| Ramesh | 280 |
| Mukesh | 155 |
Counting the Rows Returned
The final result table shows 2 rows.
Therefore, the number of rows returned by the query is 2.
Revision Table: Key Concepts
| Concept | Explanation | Relevance to Query |
|---|---|---|
| JOIN | Combines rows from two or more tables based on a related column. The query uses an inner join on Roll-No. | Merges data from Students and Performance tables to link student names with their marks. |
| GROUP BY | Groups rows that have the same values in specified columns into summary rows. Aggregate functions operate on these groups. | Groups all marks belonging to the same student Name, regardless of their specific Roll-No if different Roll-Nos share the same name. |
| Aggregate Function (Sum) | Performs a calculation on a set of values (a group) and returns a single value. Sum() adds up numerical values. | Calculates the total marks obtained by each student identified by their Name within their respective group. |
| Alias (S, P) | Temporary names given to tables or columns for easier reference. | Used for brevity (S for Students, P for Performance) and to specify which table a column comes from (e.g., S.Name). |
Additional Information on SQL Queries
SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. Queries are used to retrieve data from a database. Key clauses include:
- SELECT: Specifies the columns to retrieve.
- FROM: Specifies the table(s) to retrieve data from.
- WHERE: Filters rows based on a condition before grouping.
- GROUP BY: Groups rows with identical values in specified columns.
- HAVING: Filters groups based on a condition (applied after GROUP BY).
- ORDER BY: Sorts the final result set.
In this specific query, the GROUP BY S.Name clause is the most critical part for determining the number of output rows. Because 'Ramesh' appears with two different Roll Numbers (18CS101 and 18CS103) in the Students table, and both of these Roll Numbers have associated performance records, all these performance records for 18CS101 and 18CS103 are grouped under the single name 'Ramesh' by the GROUP BY S.Name clause. Mukesh (18CS102) forms a separate group.
The final result set will contain one row for each unique value produced by the GROUP BY clause.
Important Questions from SQL
Match the following -
List I List II (a) DDL (i) LOCK TABLE (b) DML (ii) COMMIT (c) TCL (iii) Natural Difference (d) Binary operation (iv) REVOKE Given the following STUDENT‐COURSE scheme : STUDENT (Rollno, Name, courseno) COURSE (courseno, coursename, capacity), where Rollno is the primary key of relation STUDENT and courseno is the primary key of relation COURSE. Attribute coursename of COURSE takes unique values only. Which of the following query(ies) will find total number of students enrolled in each course, along with its coursename.
A. SELECT coursename, count(*) 'total' from STUDENT natural join COURSE group by coursename;
B. SELECT C.coursename, count(*) 'total' from STUDENT S, COURSE C where S.courseno = C.courseno group by coursename;
C. SELECT coursename, count(*) 'total' from COURSE C where courseno in (SELECT courseno from STUDENT);
Which of the following is used to create a database schema?
Which of the component module of DBMS does rearrangement and possible ordering of operations, eliminate redundancy in query and use efficient algorithms and indexes during the execution of a query?
Which of the following statements are DML statements?
(a) Update [tablename] Set [columnname] = VALUE
(b) Delete [tablename]
(c) Select * from [tablename]
