505 views

4 Answers

1 votes
1 votes
That query gives minimum salary of department in which that particular employee is working.
0 votes
0 votes

The inner query, which you have asked in the end of the question does the below:

For each tuple in employee database, the query outputs minimum salary of a department in which the employee is working.

Now coming to the main query, it will output those employee's name and department Id whose salary is greater than minimum salary in their department.

Here minimum salary of department 1 is 5000, so we get Sowmya and Srinu.

In department 2,3 and 4 minimum salary is 30000, 50000 and 60000 respectively and for these departments there are no employees whose salary is greater than the minimum salary, so they are not included.

0 votes
0 votes
Note: We do not run the inner query independent of the outer query because it is a correlated subquery(3rd level query is dependent on 1st level table instance E).

So, the dry run would be like pick a employee from E and then note dept_id, then we read the 2nd level query which says to find the minimum from any dept whose dept_id is returned by 3rd query and so we run the 3rd query where we use the value of the dept_id noted from 1st level query. Now 3rd query would return that dept_id and the 2nd query would return minimum salary for that department and then we can check in the 1st query if the employees salary is greater than the minimum and select accordingly.
To summarize I would say the 2nd level query would always return the minimum salary of the dept that the employee under consideration at a point belongs to and this would be repeated for each employee in table instance E.

Related questions

0 votes
0 votes
0 answers
1
Asutosh asked Jul 7, 2018
210 views
For the relations:A(pid, cid)B(pid, pname, powner)C(cid, cname, cdesciption)How will the query be executed :SELECT pname, cname FROM A, B, CWHERE B.powner = $somevalueAND...
0 votes
0 votes
0 answers
2
iarnav asked Dec 5, 2017
191 views
Kindly explain Check Constraint in SQL with a small example!