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
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?
