Sunday 17 June 2012

Sql Server-Nested or Sub Queries practice questions


Written By:- Isha Malhotra (Software Developer)
Email:-malhotra.isha3388@gmail.com.
Note: -if you find this article helpful then kindly leave your comment


For Asp.net Training with C# and Sql Server Click Here


Nested or Sub Queries practice questions
Following is the Employee Table which contains the detail for the employee with department id and head id. Head of the employee is also an another employee
Table:-Employee
EmpId
EmpName
Department
ContactNo
EmailId
EmpHeadId
101
Isha
E-101
1234567890
abc@gmail.com
105
102
Priya
E-104
1234567890
abc@gmail.com
103
103
Neha
E-101
1234567890
abc@gmail.com
101
104
Rahul
E-102
1234567890
abc@gmail.com
105
105
abhishek
E-101
1234567890
abc@gmail.com
102

Click Here to View complete Artical
Posted by at
Labels: , ,

25 comments:

  1. Isha Malhotra14 January 2013 at 11:01

    hi. give me your email id. i will mail you complete detail. and thanks for appreciating my blog.

    ReplyDelete
  2. Navya20 January 2013 at 08:00

    Thanks a lot for the questions.. Where can we find the solutions for these queries??

    ReplyDelete
  3. kunal kumar14 April 2013 at 23:28

    1---> select DeptName from Employee where Empid>103;
    2---> select EmpName from Employee where EmpHeadId=105;
    3---> select EmpName from Employee where Department='E-101';
    4---> select EmpName from Employee where EmpId=(select EmpId from EmpSalary where IsPermanent='Yes');
    5---> select EmpName,ContactNo from Employee where EmpId=(select EmpId from EmpSalary where IsPermanent='No');

    please reply me that these query result is correct or not.


    ReplyDelete
    Replies
    1. Isha Malhotra15 April 2013 at 08:54

      no these are wrong ans..

      Delete
    2. kunal kumar15 April 2013 at 11:31

      Please provide the solution so that i can see were i am wrong...

      Delete
    3. Isha Malhotra15 April 2013 at 11:32

      ok give me your email id.. i will mail you solution

      Delete
    4. kunal kumar19 April 2013 at 11:29

      This is my mail id. Please provide the solution (kunal.kumar786@yahoo.in)
      Thanks

      Delete
    5. kunal kumar22 April 2013 at 12:47

      Please provide the solution ASAP.

      Delete
    6. Unknown26 April 2013 at 06:14

      pls send the answer for 6th question to my mail(jayasimha.jay@gmail.com)

      Delete
    7. Unknown9 July 2013 at 05:38

      This comment has been removed by the author.

      Delete
    8. Unknown9 July 2013 at 05:43

      Can You please send me the answers? mail: misri4u@gmail.com

      Delete
    9. Unknown17 October 2013 at 06:14

      pls mail me the answer to sowmiaramu92@gmail.com if u have received the solutions from Miss.Isha Malhotra

      Delete
  4. Pankaj30 May 2013 at 11:18

    Hello Isha thanks for the questions,
    could you please mail me the ans please...
    Thanks
    pankaj.pahwa3010@gmail.com

    ReplyDelete
  5. Unknown26 June 2013 at 03:51

    would you pls provide solution to all queries
    to sushubh29@gmail.com

    ReplyDelete
  6. Unknown1 July 2013 at 23:14

    Could you Please send me solutions to ranjitk9@gmail.com

    ReplyDelete
  7. nani7 July 2013 at 08:15

    select ename from employee where emphead =(select eid from employee where ename='abhishek')

    ReplyDelete
  8. nani7 July 2013 at 09:19

    SELECT EMPLOYEE SALARY WHO IS CURRENTLY WORKING ON THE PROJECT P-1
    select e.sal from empsal e
    inner join empproject p
    on e.eid =p.eid where p.pid='p-1'

    ReplyDelete
  9. nani7 July 2013 at 09:24

    Select Project id which duration is greater then 30 months

    select pid from project where duration > 30

    ReplyDelete
  10. Unknown31 July 2013 at 22:24

    please send solutions my email id is (arvindsingh10510@gmail.com)

    ReplyDelete
  11. Arun Singh5 August 2013 at 11:50

    Answer of above Queries....

    1. SELECT EmpDept.DeptName
    FROM EmpDept INNER JOIN
    Employee ON EmpDept.Deptid = Employee.Department
    Where Employee.Empid > 103

    2. Select EmpName From Employee Where EmpHEADID = (sELECT EMPID FROM eMPLOYEE
    wHERE empname='Abhishek')

    3. SELECT Employee.EmpName
    FROM Employee INNER JOIN
    EmpDept ON Employee.Department = EmpDept.Deptid
    Where Empdept.DeptName='HR'

    4. SELECT Employee.EmpName
    FROM Employee
    where Empid in (Select DeptHead From Empdept Where DeptName='HR')

    5. Select Distinct empname,contactno from employee
    inner join EmpDept on Empid=Depthead
    where depthead in (select Empid from Empsalary
    where ispermanent='No')

    6. Select empname from employee
    where department in (select deptid from empdept where deptname='HR' ) and
    empid in (select Empid from Empsalary where ispermanent='Yes')

    7. select duration from Project where Projectid not in(select projectid from empproject)

    8. select empname from employee
    inner join empproject on employee.empid=empproject.empid
    where projectid='p-4'

    9. select projectid from project where duration > 30

    10.select salary from empsalary
    inner join empproject on empsalary.empid=empproject.empid
    where projectid='p-1'

    ReplyDelete
  12. Anonymous3 September 2013 at 05:23

    --Select Department name whose EmpId > 3
    Select EmpDept.DeptId,EmpDept.DeptName,Employee.EmpID from EmpDept , Employee where Employee.Department = EmpDept.DeptID and Employee.EmpID > 103

    -- Select the Employee work under Abhishek
    Select Employee.EmpId, Employee.EmpName from Employee
    left join
    EmpDept on Employee.EmpId = EmpDept.DeptHead
    where Employee.department='E-101' and Employee.EmpName <> 'Nishant'

    -- select the Employee Head DepartHead HR
    Select Employee.EmpId, Employee.EmpName,EmpDept.DeptName from Employee inner join EmpDept on Employee.EmpId = EmpDept.DeptHead where EmpDept.DeptName ='HR'

    -- select the Employee Head who is Permanent
    Select Employee.Empid,Employee.EmpName,EmpSalary.Ispermanent from EmpSalary left join Employee on EmpSalary.EmpId = Employee.EmpId
    Where IsPermanent='Yes'

    -- Select the name and contact number of dept head who is not Permanent
    Select Employee.Empid,Employee.EmpName,Employee.ContactNo,EmpSalary.Ispermanent from EmpSalary left join Employee on EmpSalary.EmpId = Employee.EmpId
    Where IsPermanent='No'
    -- Select the name employeewho is working Hr and permanent
    Select Employee.Empid,Employee.EmpName,Employee.ContactNo,EmpSalary.Ispermanent from EmpSalary left join Employee on EmpSalary.EmpId = Employee.EmpId
    Where EmpSalary.IsPermanent='Yes' and Employee.Department='E-101'

    -- Select Duration of Project which is not Assigned
    Select Project.ProjectId,Project.Duration from Project
    left join
    EmpProject
    on Project.ProjectId = EmpProject.ProjectId
    Where EmpProject.ProjectId is null

    -- Select the name of employee who is working on p-4
    Select Employee.EmpName,EmpProject.ProjectId
    from Employee left join
    EmpProject
    on EmpProject.EmpId = Employee.EmpId
    Where EmpProject.ProjectId='p-4'

    -- Select the Project Id which duration is greater then 30 month
    Select ProjectId from Project where duration > 30

    -- select the salary of employee who is currently working in project P1

    Select EmpSalary.Salary,EmpSalary.EmpId,EmpProject.ProjectId from EmpSalary left join
    EmpProject on EmpSalary.EmpId = EmpProject.EmpId where EmpProject.projectId='p-1'

    ReplyDelete
  13. Unknown17 October 2013 at 03:57

    Please send me the answers to sowmiaramu92@gmail.com

    ReplyDelete
  14. Unknown21 January 2014 at 23:23

    send answers!!! yenugasravanthiedu@gmail.com

    ReplyDelete
  15. Unknown10 February 2016 at 23:06

    please send answers to raghsplend@gmail.com

    ReplyDelete
  16. Mayank Jain20 April 2016 at 08:00

    plz send solutions at mayank19j@gmail.com

    ReplyDelete

Subscribe to: Post Comments (Atom)