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
|
hi. give me your email id. i will mail you complete detail. and thanks for appreciating my blog.
ReplyDeleteThanks a lot for the questions.. Where can we find the solutions for these queries??
ReplyDelete1---> select DeptName from Employee where Empid>103;
ReplyDelete2---> 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.
no these are wrong ans..
DeletePlease provide the solution so that i can see were i am wrong...
Deleteok give me your email id.. i will mail you solution
DeleteThis is my mail id. Please provide the solution (kunal.kumar786@yahoo.in)
DeleteThanks
Please provide the solution ASAP.
Deletepls send the answer for 6th question to my mail(jayasimha.jay@gmail.com)
DeleteThis comment has been removed by the author.
DeleteCan You please send me the answers? mail: misri4u@gmail.com
Deletepls mail me the answer to sowmiaramu92@gmail.com if u have received the solutions from Miss.Isha Malhotra
DeleteHello Isha thanks for the questions,
ReplyDeletecould you please mail me the ans please...
Thanks
pankaj.pahwa3010@gmail.com
would you pls provide solution to all queries
ReplyDeleteto sushubh29@gmail.com
Could you Please send me solutions to ranjitk9@gmail.com
ReplyDeleteselect ename from employee where emphead =(select eid from employee where ename='abhishek')
ReplyDeleteSELECT EMPLOYEE SALARY WHO IS CURRENTLY WORKING ON THE PROJECT P-1
ReplyDeleteselect e.sal from empsal e
inner join empproject p
on e.eid =p.eid where p.pid='p-1'
Select Project id which duration is greater then 30 months
ReplyDeleteselect pid from project where duration > 30
please send solutions my email id is (arvindsingh10510@gmail.com)
ReplyDeleteAnswer of above Queries....
ReplyDelete1. 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'
--Select Department name whose EmpId > 3
ReplyDeleteSelect 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'
Please send me the answers to sowmiaramu92@gmail.com
ReplyDeletesend answers!!! yenugasravanthiedu@gmail.com
ReplyDeleteplease send answers to raghsplend@gmail.com
ReplyDeleteplz send solutions at mayank19j@gmail.com
ReplyDelete