Consider the following two tables named Raider and Team in a relational database maintained by a Kabaddi league. The attribute ID in table Team references the primary key of the Raider table, ID. The SQL query described below is executed on this database: SELECT * FROM Raider, Team WHERE Raider.ID=Team.ID AND City="Jaipur" AND RaidPoints > 200; The number of rows returned by this query is ________.Raider ID Name Raids RaidPoints 1 Arjun 200 250 2 Ankush 190 219 3 Sunil 150 200 4 Reza 150 190 5 Pratham 175 220 6 Gopal 193 215 Team City ID BidPoints Jaipur 2 200 Patna 3 195 Hyderabad 5 175 Jaipur 1 250 Patna 4 200 Jaipur 6 200
SQL Query Analysis
The SQL query retrieves all columns ($*$) by joining the $Raider$ and $Team$ tables. The join condition is $Raider.ID = Team.ID$. It applies two filters: $Team.City = \text{"Jaipur"}$ and $Raider.RaidPoints > 200$.
Raider and Team Data
The query operates on the following two tables:
| ID | Name | Raids | RaidPoints |
|---|---|---|---|
| 1 | Arjun | 200 | 250 |
| 2 | Ankush | 190 | 219 |
| 3 | Sunil | 150 | 200 |
| 4 | Reza | 150 | 190 |
| 5 | Pratham | 175 | 220 |
| 6 | Gopal | 193 | 215 |
| City | ID | BidPoints |
|---|---|---|
| Jaipur | 2 | 200 |
| Patna | 3 | 195 |
| Hyderabad | 5 | 175 |
| Jaipur | 1 | 250 |
| Patna | 4 | 200 |
| Jaipur | 6 | 200 |
Query Conditions Evaluation
We evaluate the query conditions to determine the number of matching rows:
1. Filter by City: Select rows from the $Team$ table where $Team.City = \text{"Jaipur"}$. The IDs of these teams are 1, 2, and 6.
2. Filter by RaidPoints: Select rows from the $Raider$ table where $Raider.RaidPoints > 200$. The IDs of raiders meeting this condition are 1 (RaidPoints=250), 2 (RaidPoints=219), 5 (RaidPoints=220), and 6 (RaidPoints=215).
3. Apply Join and Combined Filters: We need rows where the ID matches between the filtered $Team$ (IDs 1, 2, 6) and the filtered $Raider$ (IDs 1, 2, 5, 6). The common IDs that satisfy both city and raid points criteria are 1, 2, and 6.
- ID 1: Team (City="Jaipur", ID=1) and Raider (ID=1, RaidPoints=250). Meets all conditions.
- ID 2: Team (City="Jaipur", ID=2) and Raider (ID=2, RaidPoints=219). Meets all conditions.
- ID 6: Team (City="Jaipur", ID=6) and Raider (ID=6, RaidPoints=215). Meets all conditions.
4. Count Matching Rows: There are 3 rows that satisfy all conditions.
The number of rows returned by the query is 3.
Important Questions from SQL
Consider a relation book (title, price) which contains the titles and prices of different books.
Assuming that no two books have the same price, what does the following SQL query list ?
Select title
from book as B
where (select count (*)
from book as T
where T.price > B.price) < 7Match 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?
