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

27 comments:

  1. Hello, i would like to ask that what is the benefits of sql training, what all topics should be covered and it is kinda bothering me … and has anyone studies from this course http://www.wiziq.com/course/125-comprehensive-introduction-to-sql of SQL tutorial online?? or tell me any other guidance...
    would really appreciate help… and Also i would like to thank for all the information you are providing on sql training.

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

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

    ReplyDelete
  3. I am not a programmer but I have this SQL subject this session and have to prepare for it. What all topics should be covered in it?
    And has anyone studied from this course www.wiziq.com/course/125-comprehensive-introduction-to-sql of SQL tutorial online?? or tell me any other guidance...
    would really appreciate help

    ReplyDelete
  4. 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. Please provide the solution so that i can see were i am wrong...

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

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

      Delete
    4. Please provide the solution ASAP.

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

      Delete
    6. This comment has been removed by the author.

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

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

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

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

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

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

    ReplyDelete
  9. 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
  10. Select Project id which duration is greater then 30 months

    select pid from project where duration > 30

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

    ReplyDelete
  12. 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
  13. --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
  14. Please send me the answers to sowmiaramu92@gmail.com

    ReplyDelete
  15. send answers!!! yenugasravanthiedu@gmail.com

    ReplyDelete
  16. please send answers to raghsplend@gmail.com

    ReplyDelete
  17. plz send solutions at mayank19j@gmail.com

    ReplyDelete