The Gateway to Computer Science Excellence
0 votes
88 views

Consider the following relational schema :

    Employee ( Empid, Deptid, Salary )

    Department ( Deptid, DeptName )

 

What does the following query return ?

Query_1 :-

     Select D.DeptName

     From Department D

     Where ( Select COUNT(*)

                  From Employee E

                  where E.Deptid = D.Deptid and E.Salary > 1000 )   >  2

Query_2 :-

    Select min( Depatment.DeptName ) as deptname

    From Department inner join Employee on Department.Deptid = Employee.DeptId

    Where Salary > 1000

    Group By ( Empid ) 

    Having count( EmpId )  ≥ 2

 

are  both the query  return same output ??

in second query what is mean by min(dept.name ) is it count having minimum dept name ?

in Databases by Active (1.1k points)
edited by | 88 views
0
@Shaik Mastan  @abhishekmehta4u
0

@vijju532

Don't add screenshots unnecessarily... instead of that type the question...

Moreover that is not the way of tagging the user...

1 Answer

+1 vote

are  both the query  return same output ??

No...

i) Aggregate operators produce single output, but your Query_1 may result more than one

ii) Actually Query_2 is wrong due to Aggregate operators use column, then that column should be used in GROUP BY clause or that column should be key.

 

in second query what is mean by min(dept.name )

when you use min on column, then output should be select minimum value from that column..

if it is used on the VarChar datastructure, then arrange them in Alphabetical order, pick up the least one

i mean APPLE, BALL ==> APPLE is the MINIMUM

 

Now, My Question is what are the changes necessary to do for Query_2 to match it with Query_1 ?

 

Query_1 :-

     Select D.DeptName

     From Department D

     Where ( Select COUNT(*)

                  From Employee E

                  where E.Deptid = D.Deptid and E.Salary > 1000 )   >  2

Query_2 :-

    Select Depatment.DeptName

    From Department inner join Employee on Department.Deptid = Employee.DeptId

    Where Salary > 1000

    Group By ( Deptid

    Having count( Deptid > 2

 

Note that After grouping by Deptid, Deptid is the key for resultant table

==> No problem by using select Department.DeptName statement

by Veteran (65.7k points)
Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true
50,737 questions
57,370 answers
198,506 comments
105,272 users