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 subqueries interview questions and answers

Please read the below given table for solving questions from 1 to 20
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 a subquery?
  1. subquery is an inner query which is nested in an outer query.
  2. subquery is an inner select statement present inside an outer query.
  3. Both First and second options are valid definitions for subquery.
  4. None of the options are correct.
Show Answer
Share Facebook gmail Twitter
2
Which of the following is the syntax for a subquery?
  1. outer_query comparison [ANY | ALL | SOME] (sql_select_statement)
  2. outer_query expression [NOT] IN (sql_select_statement)
  3. outer_query expression [NOT] EXISTS (sql_statement)
  4. All the syntaxes are correct
Show Answer
Share Facebook gmail Twitter
Show Answer
Share Facebook gmail Twitter
4
Which of the following query returns the employees fullnames whose salaries are greater than salary of gourav?
  1. select * from employees where salary>20000 ;
  2. select fname+lname as 'fullname' from employees where salary>20000 ;
  3. select fname+lname as 'fullname' from employees where salary>(select salary from employees where fname='gourav') ;
  4. None of the options are correct.
Show Answer
Share Facebook gmail Twitter
5
Which of the following query returns the full name of the db department employee/s whose salary is greater than their average department salary?
  1. select fname+lname as 'fullname' from employees where salary>(select avg(salary) from employees where dept='db') and dept='db'
  2. select fname+lname as 'fullname' from employees where salary>(select avg(salary) from employees where dept='db') and  where dept='db'
  3. select fname+lname as 'fullname' from employees where salary>(select avg(salary) from employees group by dept ) and dept='db'
  4. None of the options are correct.
Show Answer
Share Facebook gmail Twitter
6
Which of the following queries return the full name of the db department employee/s whose salary is greater than their minimum department salary?
  1. select fname+lname as 'fullname' from employees where salary>(select min(salary) from employees group by dept ) and dept='db'
  2. select fname+lname as 'fullname' from employees where salary>(select min(salary) from employees where dept='db') and  where dept='db'
  3. select fname+lname as 'fullname' from employees where salary>(select min(salary) from employees where dept='db' ) and dept='db'
  4. None of the options are correct.
Show Answer
Share Facebook gmail Twitter
7
Which of the following queries return the employee details whose salary is between rajeev and gourav's salary?
Note: assume rajeev salary is less than gourav's salary.
Note:assume only one employee is existing with the fname rajeev and only one with fname gourav.
  1. select * from employees where salary between ( select distinct salary from employees where fname='rajeev' ) and ( select distinct salary from employees where fname='gourav' )
  2. select * from employees where salary between ( select salary from employees where fname='rajeev' ) and ( select salary from employees where fname='gourav' )
  3. select * from employees where salary >= ( select distinct salary from employees where fname='rajeev' ) and salary <= ( select salary from employees where fname='gourav' )
  4. Both second and third queries are correct.
Show Answer
Share Facebook gmail Twitter
8
Which of the following queries return the dept names, whose average salaries are greater than the average salary of db department?
  1. select dept from employees group by dept , salary having AVG(salary)>(select AVG(salary) from employees where dept='db')
  2. select dept from employees group by dept having AVG(salary)>(select AVG(salary) from employees where dept='db')
  3. select dept from employees group by dept having AVG(salary)>(select salary from employees where dept='db')
  4. select dept from employees group by dept having AVG(salary)>(select AVG(salary) from employees  group by dept )
Show Answer
Share Facebook gmail Twitter
9
Which of the following queries return the employees details whose salary is not equal to the salary of rajeev sukla , anu _singh and gourav chourasia?
  1. select * from employees where salary not in ( select salary from employees where fname+lname in ('rajeevsukla','anu_singh','gouravchourasia'))
  2. select * from employees where salary in ( select salary from employees where fname+lname in ('rajeevsukla','anu_singh','gouravchourasia'))
  3. select * from employees where fname+lname not in ( select salary from employees where fname+lname in ('rajeevsukla','anu_singh','gouravchourasia'))
  4. None of the options are correct .
Show Answer
Share Facebook gmail Twitter
10
Which of the following queries update the employees salary for whom the salary is undefined or null with the minimum employees salary?
  1. update from employees set salary=(select MIN(salary) from employees ) where salary is null
  2. update employees set salary=(select MIN(salary) from employees ) where salary = null
  3. update employees set salary=(select MIN(salary) from employees ) where salary is null
  4. None of the options are correct .
Show Answer
Share Facebook gmail Twitter
11
Which of the following queries delete the employee whose salary is same as salary of employees fname anu (Query must not delete the employee whose fname is anu )?
  1. delete employees where salary=(select salary from employees where fname='anu') and fname!='anu'
  2. delete employees where salary=(select salary from employees where fname='anu')
  3. delete employees where salary=(select salary from employees where fname!='anu') and fname=='anu'
  4. None of the options are correct.
Show Answer
Share Facebook gmail Twitter
12
Which of the following queries insert new employee with the following details into employees table ?
New employee details :
Eid: current max eid +1 ; Fname:sudheer ; Lname:Reddy ; Age: 25 ; Salary : Same as current highest paid employee salary ; dept : the department in which employee anu is working ; doj : 23rd may 2012
  1. insert into employees (eid,fname,lname,age,salary,dept,doj) values (select max(eid)+1 from employees ,'sudheer','reddy',25,select MAX(salary) from employees ,select dept from employees where fname='anu','05-23-2012')
  2. insert into employees (eid,fname,lname,age,salary,dept,doj) values ((select max(eid)+1 from employees) ,'sudheer','reddy',25,(select MAX(salary) from employees) ,(select dept from employees where fname='anu'),'05-23-2012')
  3. It is not possible to use subqueries if the outer query is of type insert.
  4. None of the options are correct.
Show Answer
Share Facebook gmail Twitter
13
What is the use of any keyword in SQL?
  1. any keyword is used for comparing a scalar value with single columns set of values.
  2. any keyword is used for comparing a scalar value with the minimum value in a set of values.
  3. First and second statements together will give full meaning for any keyword.
  4. None of the options are correct.
Show Answer
Share Facebook gmail Twitter
14
What is the output for the following query?
query:
select fname+lname as 'fullname',salary from employees where salary>any(select salary from employees where dept='.net')
  1. The given query returns all the employees full names and salaries whose salaries are greater than the max salary of .net department employees.
  2. The given query returns all the employees full names and salaries whose salaries are greater than the min salary of .net department employees.
  3. The query will fail since the inner query will return multiple values.
Show Answer
Share Facebook gmail Twitter
15
What is the use of all key word in SQL?
  1. all key word is used for comparing a scalar value with single columns set of values.
  2. all keyword is used for comparing a scalar value with the maximum value in a set of values.
  3. First and second statements together will give full meaning for all keyword.
Show Answer
Share Facebook gmail Twitter
16
What is the output for the following query?
query:
select fname+lname as 'fullname',salary from employees where salary>all(select salary from employees where dept='.net')
  1. The given query returns all the employees full names and salaries whose salaries are greater than the max salary of .net department employees.
  2. The given query returns all the employees full names and salaries whose salaries are greater than the min salary of .net department employees.
  3. The query will fail since the inner query will return multiple values.
Show Answer
Share Facebook gmail Twitter
17
What is a correlated subquery?
  1. Correlated subquery is a sub query or inner query which depends on outer query for its input.
  2. Correlated subquery takes input from outer query , after execution of inner query the result will be passed back to outer query.
  3. First and second statements together will give full meaning for correlated sub query.
  4. None of the options are correct.
Show Answer
Share Facebook gmail Twitter
18
Identify the right query which returns all the employees full names whose salaries are more than their corresponding department average salaries?
  1. select fname+lname as 'fullname' from employees e1 where e1.salary >(select avg(e2.salary) from employees e2 where e1.dept=e2.dept )
  2. select fname+lname as 'fullname' from employees group by dept having AVG(salary)<salary
  3. None of the queries will return the correct result set.
Show Answer
Share Facebook gmail Twitter
19
Identify the query that returns First highest salary?
  1. select e1.salary from Employees e1 where 1>= ( select count(*) from Employees e2 where e2.salary>e1.salary ) and salary is not null
  2. select e1.salary from Employees e1 where 1= ( select count(*) from Employees e2 where e2.salary>e1.salary ) and salary is not null
  3. select e1.salary from Employees e1 where 0= ( select count(*) from Employees e2 where e2.salary>e1.salary ) and salary is not null
  4. None of the options are correct.
Show Answer
Share Facebook gmail Twitter
20
Identify the query that returns second highest salary?
  1. select e1.salary from Employees e1 where 1>= ( select count(*) from Employees e2 where e2.salary>e1.salary ) and salary is not null
  2. select e1.salary from Employees e1 where 1= ( select count(*) from Employees e2 where e2.salary>e1.salary ) and salary is not null
  3. select e1.salary from Employees e1 where 0= ( select count(*) from Employees e2 where e2.salary>e1.salary ) and salary is not null
  4. None of the options are correct .
Show Answer
Share Facebook gmail Twitter
Read more papers :