Consider the following database tables of a sports league. player (\( pid \), \( pname \), \( age \)) coach (\( cid \), \( cname \)) team (\( tid \), \( tname \), \( city \), \( cid \)) members (\( pid \), \( tid \)) An instance of the table and an SQL query are given. Player table
coach table:
team table:
members table:
SQL query: \[ {SELECT MIN(P.age)} \] \[ {FROM player P} \] \[ {WHERE P.pid IN (} \] \[ { SELECT M.pid} \] \[ { FROM team T, coach C, members M} \] \[ { WHERE C.cname = 'Mark'} \] \[ { AND T.cid = C.cid} \] \[ { AND M.tid = T.tid)} \] The value returned by the given SQL query is __________. (Answer in integer)
Consider the following database tables of a sports league. player (\( pid \), \( pname \), \( age \)) coach (\( cid \), \( cname \)) team (\( tid \), \( tname \), \( city \), \( cid \)) members (\( pid \), \( tid \)) An instance of the table and an SQL query are given. Player table
coach table:
team table:
members table:
SQL query: \[ {SELECT MIN(P.age)} \] \[ {FROM player P} \] \[ {WHERE P.pid IN (} \] \[ { SELECT M.pid} \] \[ { FROM team T, coach C, members M} \] \[ { WHERE C.cname = 'Mark'} \] \[ { AND T.cid = C.cid} \] \[ { AND M.tid = T.tid)} \] The value returned by the given SQL query is __________. (Answer in integer)
Show Hint
Correct Answer: 26
Solution and Explanation
2. Next, it finds the teams coached by "Mark" in the team table. The team with \( cid = 102 \) is "MI" (with \( tid = 10 \)).
3. Then, the query finds the players who are part of team "MI". From the members table, players with \( tid = 10 \) are:
- Player 1 (Jasprit)
- Player 3 (Ishan)
4. The ages of these players are:
- Player 1 (Jasprit): 31
- Player 3 (Ishan): 26
5. The minimum age among these players is 26. Thus, the value returned by the given SQL query is 26.
Top Questions on Database Management Systems
A schedule of three database transactions \(T_1\), \(T_2\), and \(T_3\) is shown. \(R_i(A)\) and \(W_i(A)\) denote read and write of data item A by transaction \(T_i\), \(i = 1, 2, 3\). The transaction \(T_1\) aborts at the end. Which other transaction(s) will be required to be rolled back?
- GATE CS - 2025
- Computer Science and IT Engineering
- Database Management Systems
- Consider a relational schema team(name, city, owner), with functional dependencies \( \{ {name} \rightarrow {city}, {name} \rightarrow {owner} \} \). The relation team is decomposed into two relations, \( t_1({name}, {city}) \) and \( t_2({name}, {owner}) \). Which of the following statement(s) is/are TRUE?
- GATE CS - 2025
- Computer Science and IT Engineering
- Database Management Systems
- Consider the following two relations, named Customer and Person, in a database:
\[ \text{Person} \left( \begin{array}{l} \text{aadhaar CHAR(12) PRIMARY KEY,} \\ \text{name VARCHAR (32);} \end{array} \right) \]
\[ \text{Customer} \left( \begin{array}{l} \text{name VARCHAR (32),} \\ \text{email VARCHAR(32) PRIMARY KEY,} \\ \text{phone CHAR(10),} \\ \text{aadhaar CHAR(12),} \\ \text{FOREIGN KEY (aadhaar) REFERENCES Person (aadhaar);} \end{array} \right) \]
Which of the following statements is/are correct?- GATE DA - 2025
- Computer Science and IT Engineering
- Database Management Systems
- Consider a database relation \( R \) with attributes \( A, B, C, D, E, F, G \), and having the following functional dependencies: \[ A \rightarrow BCEF \quad E \rightarrow DG \quad BC \rightarrow A \] Which of the following statements is/are correct?
- GATE DA - 2025
- Computer Science and IT Engineering
- Database Management Systems
- If a relational decomposition is not dependency-preserving, which one of the following relational operators will be executed more frequently in order to maintain the dependencies?
- GATE DA - 2025
- Computer Science and IT Engineering
- Database Management Systems
Questions Asked in GATE CS exam
In a 4-bit ripple counter, if the period of the waveform at the last flip-flop is 64 microseconds, then the frequency of the ripple counter in kHz is ______________. {(Answer in integer)}
- GATE CS - 2025
- Flip-Flop
Consider the following C code segment:
int x = 126, y = 105; do { if (x > y) x = x - y; else y = y - x; } while (x != y); printf("%d", x);The output of the given C code segment is ____________. (Answer in integer)
- GATE CS - 2025
- Programming in C
The following two signed 2’s complement numbers (multiplicand \( M \) and multiplier \( Q \)) are being multiplied using Booth’s algorithm:
Multiplicand (\( M \)) Multiplier (\( Q \)) 1100 1101 1110 1101 1010 0100 1010 1010 The total number of addition and subtraction operations to be performed is __________. (Answer in integer)
- GATE CS - 2025
- Complementation
The maximum value of \(x\) such that the edge between the nodes B and C is included in every minimum spanning tree of the given graph is __________ (answer in integer).
- GATE CS - 2025
- Programming and Data Structures
Consider the following C program
The value printed by the given C program is __________ (Answer in integer).- GATE CS - 2025
- Programming in C