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
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 Which of the following is NOT a Structured Query Language Data type?
Consider the following relational schema:
Students (rollno: integer, name: string, age: integer, cgpa: real)
Courses (courseno: integer, cname: string, credits: integer)
Enrolled (rollno: integer, courseno: integer, grade: string)
Which of the following options is/are correct SQL query/queries to retrieve the names of the students enrolled in course number (i.e., courseno) 1470?Consider a table Employee(EmpID, TeamID), where the column EmpID (ID of an employee) is the primary key. The column TeamID denotes the team ID of the team of which the employee is a member. TeamID is a NOT NULL column.
We want to display the size of the team (denoted as TeamSize) in which each employee is a member by using SQL. As an example, the desired output for the given Employee table is also shown in tabular form.Which of the following is/are correct?
Employee
EmpID TeamID 1 8 2 8 3 8 4 7 5 7 6 9
Output
EmpID TeamSize 1 3 2 3 3 3 4 2 5 2 6 1 Let Account be a relation as shown.
Account
AccNo Balance A1 5000 A2 5000 A3 10000 A4 15000 A5 18000 Consider the given SQL query.
SELECT AccNo FROM Account AS A
WHERE (SELECT COUNT(*) FROM Account AS B
WHERE A.Balance < B.Balance) >= (SELECT COUNT(*)
FROM Account AS C WHERE A.Balance > C.Balance)
The number of rows returned by the SQL query is __________ . (Answer in integer)
