Nested or Sub-queries Practice Questions with Answers

Hi Guys,
Today we discuss mysql sub-queries questions with answers. Sql queries are useful and used in web application development connected with database tables. We can fetch the data from database, insert the data to database, update the existing data in a database table and also delete the data or table in a database based on conditions or other functionality.




In below there are five tables are available with datas. Based on these five table we perform some sql queries like sub-queries or nested queryy operations.
There are five table are available. They are:

  1. employee
  2. empdept
  3. empsalary
  4. empproject
  5. project

Table 1: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




table 2: empdept

DeptId DeptName DeptHead
E-101 HR 105
E-102 Development 101
E-103 House Keeping
E-104 Sales 104
E-105 Purchase 104

Table 3: empproject

EmpId ProjectId
101 p-1
102 p-2
103 p-1
104 p-4
105 p-4




Table 4: empsalary

EmpId Salary Ispermanent
101 2000 Yes
102 10000 Yes
103 5000 No
104 1900 Yes
105 2300 Yes

Table 5: project

ProjectId Duration
p-1 23
p-2 15
p-3 45
p-4 2
p-5 30

Question 1: Select departmentid and department name of the company which is assigned to the employee whose employee id is greater than 103

Answer:


Select DeptId,DeptName from empdept INNER JOIN employee ON empdept.DeptId=employee.Department WHERE employee.EmpId>103




Output as follows:

DeptId DeptName
E-101 HR
E-102 Development

Question 2: Select salary of the employee who is currently working on the project p-2

Answer:


Select Salary from empsalary INNER JOIN empproject ON empsalary.EmpId=empproject.EmpId WHERE empproject.ProjectId='p-2'

Output as follows:

Salary
10000

Question 3: Select name of the employee who is department head is HR

Answer:


Select Empname from employee INNER JOIN empdept ON employee.Department=empdept.DeptId WHERE empdept.Deptname='HR'

Output as follows:

Empname
Isha
Neha
abhishek




Question 4: Select name of the employee who is department head is Sales

Answer:


Select Empname from employee INNER JOIN empdept ON employee.Department=empdept.DeptId WHERE empdept.Deptname='Sales';

Output as follows:

Empname
Isha
Neha
abhishek
Priya




Question 5: Select name of the employee who is department head is Sales or department head is HR

Answer:


Select Empname from employee INNER JOIN empdept ON employee.Department=empdept.DeptId WHERE empdept.Deptname='Sales' OR empdept.DeptName='HR';

Output as follows:

Empname
Priya

Question 6: Select all the details from employee

Answer:


select * from employee;




Output as follows:

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




Question 7: Select the project id which duration is greater than 30 months.

Answer:


select ProjectId from project where Duration >30

Output as follows:

ProjectId
p-3

Question 8: Duration of the project which didn’t assigned to any candidates

Answer:


select Duration from project where ProjectId not in (select ProjectId from empproject)

Output as follows:

Duration
30
45

Question 9: Select the name of the candidate who is working in HR department and they are working as permanent.

Answer:


select EmpName from employee where Department in (select deptId from empdept where Deptname='HR') and EmpId in (select EmpId from empsalary where Ispermanent='Yes');

Output as follows:

EmpName
Isha abhishek

If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

2 thoughts on “Nested or Sub-queries Practice Questions with Answers

  1. gaurav

    really nice, new way to write sub queries by joining the two tables from which we want the required information.

    Reply
    1. Pritty Post author

      Hi Gaurav,
      Example:Select DeptId,DeptName from empdept INNER JOIN employee ON empdept.DeptId=employee.Department WHERE employee.EmpId>103
      I just convert the inner join query to subqueries in simple method:
      select d.DeptId,d.DepatName,e.Department,e.EmpId from empdept d, employee e where e.Department=d.DeptId and e.empId>103

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *