Select chapter



Basics (2.papers)
Data Types (2.papers)
Table-designing (1.papers)
Queries-Commands (3.papers)
String functions (2.papers)
Subqueries (1.papers)
Joins (1.papers)
Constraints (1.papers)
Triggers (1.papers)
Hide

Sponsored Links


  

sql groupby having clause interview questions and answers

Please read the below given table for solving questions from 1 to 13
eidfnamelnameagesalarydeptdoj
1rajeevsukla2312000.net23-Oct-11 12:00:00 AM
2sowmyakumari2319000db13-Nov-10 12:00:00 AM
3kishorekumar2736000android16-Oct-11 12:00:00 AM
4abimanyubiswal22nullandroid25-May-10 12:00:00 AM
5sonikumar2421800.net21-Jun-09 12:00:00 AM
6anu_singh2212000db23-Oct-10 12:00:00 AM
7_dineshmoh%anty2315000.net26-Aug-09 12:00:00 AM
8nischala_kumari2218000db19-Jul-08 12:00:00 AM
9gouravchourasia2420000android06-Oct-13 12:00:00 AM
Note: Assume table is present in a database named skillgun , the Schema name is dbo and the table name as Employees ;
1
What is the use of group by clause?
  1. group by clause is used for grouping the selected set of rows into a set of summary rows.
  2. group by clause is used for grouping the selected set of columns into a set of summary rows.
  3. A group by clause returns one row for each group of column or columns specified in the group by clause.
  4. First and third options are correct.
Show Answer
Share Facebook gmail Twitter
2
What is the output for the following query?
Query: select age from employees group by age ;
  1. Query returns all rows present in age column.
  2. Query gives parser error stating the column used in group by must not be allowed in select list.
  3. age
    22
    23
    24
    27
  4. None of the options are correct.
Show Answer
Share Facebook gmail Twitter
3
What is the output for the following query?
Query: select age,fname from employees group by age ;
  1. Query returns all rows present in age column and fname column.
  2. Query gives parser error stating the column used in the select list must be present in group by clause or must be associated with aggregate function.
  3. query returns distinct age and fname values combination as shown in the below table 
    .
    agefname
    22abimanyu
    22anu
    22nischala_
    23_dinesh
    23rajeev
    23sowmya
    24gourav
    24soni
    27kishore
  4. None of the options are correct.
Show Answer
Share Facebook gmail Twitter
4
Which of the following query returns dept name and total salaries offered to the individual department employees?
Expected result set :
totalsaldept
60800.net
56000android
49000db
  1. select SUM(salary) as 'totalsal',dept from employees group by dept
  2. select SUM(salary) as 'totalsal',dept from employees.
  3. select SUM(salary) as 'totalsal',dept from employees group by dept,sum(salary).
  4. None of the options are correct .
Show Answer
Share Facebook gmail Twitter
5
Which of the following query is used for displaying all department names and least salaries in each department?
Expected Result set :
minsaldept
12000.net
20000android
12000db
  1. select MIN(salary) as 'minsal',dept from employees group by dept ;
  2. select MIN(salary) as 'minsal',dept from employees group by min(salary);
  3. select MIN(salary) as 'minsal',dept from employees ;
  4. None of the options are correct.
Show Answer
Share Facebook gmail Twitter
6
Which of the following query returns average year wise salaries?
Expected result set:
avgsalyear
180002008
184002009
155002010
200002011
200002013
  1. select avg(salary) as 'avgsal',year(doj) 'year' from employees group by YEAR(doj),salary
  2. select avg(salary) as 'avgsal',year(doj) 'year' from employees group by salary
  3. select avg(salary) as 'avgsal',year(doj) 'year' from employees group by avg(salary)
  4. select avg(salary) as 'avgsal',year(doj) 'year' from employees group by YEAR(doj)
Show Answer
Share Facebook gmail Twitter
7
What is the output for the following query?
Query :
select SUM(salary)as 'totalsal',YEAR(doj) 'yoj'  from employees group by YEAR(doj),dept
  1. The query will produce parser error since group by clause must not contain more than one expression or column .
  2. totalsalyoj
    180002008
    368002009
    310002010
    600002011
    200002013
  3. totalsalyoj
    368002009
    240002011
    null2010
    360002011
    200002013
    180002008
    310002010
  4. None of the options are correct .
Show Answer
Share Facebook gmail Twitter
8
What is the use of having clause?
  1. having clause is used for filtering the intermediate result sets data after execution of group by clause.
  2. having clause is used along with group by for filtering data by using aggregate functions.
  3. having clause is same as where clause and there is no difference between having and where clause.
  4. First and second options are correct.
Show Answer
Share Facebook gmail Twitter
9
Which of the following query returns dept name/s for which average salary is greater than 20000?
  1. select dept  from employees group by dept having AVG(salary)>20000
  2. select dept  from employees group by dept,salary having AVG(salary)>20000
  3. select dept  from employees group by salary having AVG(salary)>20000
  4. None of the options are correct .
Show Answer
Share Facebook gmail Twitter
10
Which of the following query returns all dept names in the descending order of the avg department salaries?
  1. select dept  from employees group by avg(salary) order by avg(salary) desc
  2. select dept  from employees group by dept order by avg(salary) desc
  3. select dept  from employees group by dept order by salary desc
  4. select dept  from employees group by salary order by avg(salary) desc
Show Answer
Share Facebook gmail Twitter
11
Which of the following queries will return dept name which is having highest avg salary?
  1. select top 1 dept  from employees group by dept order by avg(salary) desc
  2. select dept  from employees group by dept having avg(salary)=max(avg(salary))
  3. select dept from employees where avg(slary)=max(salary) group by dept 
  4. None of the options are correct.
Show Answer
Share Facebook gmail Twitter
Show Answer
Share Facebook gmail Twitter
13
Which of the following queries return dept names along with the departments average salaries for .net and db departments, if the average department salary is greater than 15000?
  1. select dept,avg(salary) 'avgsal' from employees where dept = ('.net','db') group by dept having AVG(salary)>15000
  2. select dept,avg(salary) 'avgsal' from employees where dept in ('.net','db') group by dept ,salary having AVG(salary)>15000
  3. select dept,avg(salary) 'avgsal' from employees where dept in ('.net','db') group by dept having AVG(salary)>15000
  4. None of the options are correct.
Show Answer
Share Facebook gmail Twitter
Read more papers :