Register

GO Classes DPP | GATE CS, DA | DBMS | SQL

ago
21 views
0 0 votes

The $\mathrm{Employee}$ relation contains:

$$\begin{array}{|c|c|c|c|} \hline \mathrm{EmployeeNo} & \mathrm{Name} & \mathrm{DateOfBirth} & \mathrm{Department} \\ \hline 0001 & \mathrm{Arai\ Kenji} & 1970\text{-}02\text{-}04 & \mathrm{Sales} \\ 0002 & \mathrm{Suzuki\ Taro} & 1975\text{-}03\text{-}13 & \mathrm{General\ Affairs} \\ 0003 & \mathrm{Sato\ Hiroshi} & 1981\text{-}07\text{-}11 & \mathrm{Engineering} \\ 0004 & \mathrm{Tanaka\ Hiroshi} & 1978\text{-}01\text{-}24 & \mathrm{Planning} \\ 0005 & \mathrm{Suzuki\ Taro} & 1968\text{-}11\text{-}09 & \mathrm{Sales} \\ \hline \end{array}$$

Which query extracts the names for which more than one employee has the same name?

  1. $\mathrm{SELECT\ Name}$
    $\mathrm{FROM\ Employee}$
    $\mathrm{GROUP\ BY\ Name}$
    $\mathrm{HAVING\ COUNT(*)}>1$;
     
  2. $\mathrm{SELECT\ Name}$
    $\mathrm{FROM\ Employee}$
    $\mathrm{WHERE\ Name=Name}$;
     
  3. $\mathrm{SELECT\ Name}$
    $\mathrm{FROM\ Employee}$
    $\mathrm{WHERE\ Name=Name}$
    $\mathrm{ORDER\ BY\ Name}$;
     
  4. $\mathrm{SELECT\ Name,\ COUNT(*)}$
    $\mathrm{FROM\ Employee}$
    $\mathrm{GROUP\ BY\ Name}$;

1 Answer

0 0 votes

Grouping by $\mathrm{Name}$ creates one group for each different name.

For eg,

$\mathrm{Suzuki\ Taro}$

$\mathrm{employee\ 0002}$

$\mathrm{employee\ 0005}$

This group contains two tuples.

$\mathrm{COUNT(*)}$ tells us the number of tuples in each group, and:

$\mathrm{HAVING\ COUNT(*)}>1$

keeps only names occurring more than once.

Option D calculates counts for all names, including those appearing exactly once. 

The requirement is specifically to return names with duplicates.

Hence,

Answer : $\boxed{\mathrm{A}}$

ago
Voting & Accepting an answer helps improve the community
Answer:
← Previous · GO Classes DPP | GAT...Next → · GO Classes DPP | GAT...
← Previous in category · GO Classes DPP | GAT...Next in category → · GO Classes DPP | GAT...
Position:
Show:

Related questions

0 0 votes
1 1 answer
26
26 views
GO Classes asked 23 hours ago
26 views
GO Classes DPP | GATE CS, DA | DBMS | SQL Queries
The database contains relations $\mathrm{animals}$ and $\mathrm{visits}$, with $\mathrm{aid}$ identifying an animal.Which SQL query returns the names of animals that visi...
0 0 votes
1 1 answer
20
20 views
GO Classes asked 23 hours ago
20 views
GO Classes DPP | GATE CS, DA | DBMS | Evaluation Order
Conceptually, in which order does SQL process the following three operations?$\mathrm{WHERE}\rightarrow\mathrm{HAVING}\rightarrow\mathrm{GROUP\ BY}$ $\mathrm{WHERE}\right...
0 0 votes
1 1 answer
24
24 views
GO Classes asked 23 hours ago
24 views
GO Classes DPP | GATE CS, DA | DBMS | GROUP BY & ORDER BY
The relation is:$\mathrm{Midterm}(\mathrm{ClassName},\mathrm{SubjectName},\mathrm{StudentNo},\mathrm{Name},\mathrm{Score})$.The objective is to calculate the average scor...
0 0 votes
1 1 answer
33
33 views
GO Classes asked 1 day ago
33 views
GO Classes DPP | GATE CS, DA | DBMS | WHERE vs HAVING
The relation $\mathrm{ExamResult}$ stores examination records for the previous three years:$\mathrm{ExamResult}(\mathrm{StudentNo},\mathrm{ExamDate},\mathrm{Score},\mathr...