These queries I do not have a clue as well?

[Drew37]

I was doing good in this book until I got to subqueries.
I do not have a clue on these problems:


1. Write a query to display the name, department number, and salary of any employees whose department number and salary match the department number and salary of any employee who earns a commission.
select ename, deptno, sal
from emp
where comm > 0;


2. Display the name, department name, and salary of any employee whose salary and commission match the salary and commission of any employee located in Dallas.

select ename, dname, sal
from emp
where emp.deptno=dept.deptno
and (sal, comm) IN
(select sal,comm
from emp
where sal=sal
and comm=comm
and dept.loc = 'DALLas');
3. Create a query to display the name, hire date, and salary for all employees who have both the same salary and commission as Scott

select ename, hiredate, sal
from emp
where (sal, comm) In
(select sal, comm
from emp
where ename = 'Scott';
4. Create a query to display the employees that earn a salary that is higher than the salary of all of the clerks. Sort the results on salary from highest to lowest. Note: Do not display SCOTT in the result set.

select ename, job, sal
from emp
where sal >
(select sal
from emp
where sal

 

[carp]

The trick to these is to figure out the subquery first.
Let's consider the first one:
How would you find employees who earn a commission?
You have it right:
SELECT some_stuff
FROM emp
WHERE comm > 0;
Now we need to be able to join the subquery to the outer query. The key is the conditions set forth in the problem:
...any employees whose department number and salary match the department number and salary of any employee who earns a commission.

This means your connection between the query and the subquery will involve department number and salary:
SELECT the_outer_stuff
FROM emp
WHERE
WHERE (dept_id, salary) IN (SELECT dept_id, salary
FROM emp
WHERE comm > 0);

 

[Guest_imported]

or......ask your professor if your having problems