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:
- employee
- empdept
- empsalary
- empproject
- 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.
really nice, new way to write sub queries by joining the two tables from which we want the required information.
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