Let Account be a relation as shown. Account 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)AccNo Balance A1 5000 A2 5000 A3 10000 A4 15000 A5 18000
Query Analysis: Median Balance Identification
The SQL query aims to find accounts where the number of accounts with a higher balance is greater than or equal to the number of accounts with a lower balance. This condition effectively identifies accounts whose balance is at or near the median balance.
Account Relation Data
The provided $Account$ relation has the following records:
| AccNo | Balance |
| A1 | 15000 |
| A2 | 25000 |
| A3 | 10000 |
| A4 | 15000 |
| A5 | 18000 |
Step-by-Step Evaluation
We evaluate the query's $WHERE$ clause for each row in the $Account$ table:
- For A1 (Balance = 15000):
- Higher balances: A2 (25000), A5 (18000) - Count = 2
- Lower balances: A3 (10000) - Count = 1
- Condition: $2 \ge 1$ (True) - A1 is selected.
- For A2 (Balance = 25000):
- Higher balances: None - Count = 0
- Lower balances: A1 (15000), A3 (10000), A4 (15000), A5 (18000) - Count = 4
- Condition: $0 \ge 4$ (False) - A2 is not selected.
- For A3 (Balance = 10000):
- Higher balances: A1 (15000), A2 (25000), A4 (15000), A5 (18000) - Count = 4
- Lower balances: None - Count = 0
- Condition: $4 \ge 0$ (True) - A3 is selected.
- For A4 (Balance = 15000):
- Higher balances: A2 (25000), A5 (18000) - Count = 2
- Lower balances: A3 (10000) - Count = 1
- Condition: $2 \ge 1$ (True) - A4 is selected.
- For A5 (Balance = 18000):
- Higher balances: A2 (25000) - Count = 1
- Lower balances: A1 (15000), A3 (10000), A4 (15000) - Count = 3
- Condition: $1 \ge 3$ (False) - A5 is not selected.
Resulting Rows
The accounts that satisfy the condition are A1, A3, and A4.
Final Count
The total number of rows returned by the SQL 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 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.
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 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 ________.
