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 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)
On a relation named Loan of a bank:
LOAN loan_number branch_name amount L11 Banjara Hills 90000 L14 Kondapur 50000 L15 SR Nagar 40000 L22 SR Nagar 25000 L23 Balanagar 80000 L25 Kondapur 70000 L19 SR Nagar 65000
the following SQL query is executed.
SELECT L1.loan_number
FROM Loan L1
WHERE L1.amount > (SELECT MAX (L2.amount)
FROM Loan L2
WHERE L2.branch_name = 'SR Nagar');
The number of rows returned by the query is ____________ (Answer in integer)Which of the following statements are correct?
A. The HAVING CLAUSE is an optional clause which tells Oracle to group rows based on distinct values that exist for specified columns.
B. Unions can be used in subqueries.
C. The intersect clause outputs only rows produced by both the queries intersected.
D. The statement containing a subquery is called a parent statement.
E. If a select statement is defined as a subquery, the innermost select statement gets executed first.
Choose the correct answer from the options given below:
- Consider the following two commands C1 and C2 on the relation R from an SQL database :
C1 : drop table R;
C2 : delete from R;
Which of the following statements is TRUE ?
I. Both C1 and C2 delete the schema for R.
II. C2 retains relation R, but deletes all tuples in R.
III. C1 deletes not only all tuples of R, but also the schema for R.
