gfg dbms quiz

Question

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 ?

Comments

@Shaik Mastan  @abhishekmehta4u

---

@vijju532

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

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

Answer 1

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