How to Extract Particular Records from the Database?

[Sukhvinder]

Hello to all, if I have the following databases:

IMP (Nimp, Name, Ndipart, Jobs, Director, Stip)
Departments (Ndipart, NomeDipart, City)
USO (Ndipart, Part)
Supply (Supplier, Part)

And I have the following request:

List the names and codes in the departments where the average salary of employees is <1000

What do you think the following solution is correct?

Select distinct Ndipart, NomeDipart
from Dept.
where in Ndipart
(Select distinct Ndipart
from imp
group by Ndipart
having avg (Stip)> 1000)

In particular, I want to know:

1) If you can put in the Select the only attribute of the group by leaving out the one used in having.
2) When in use where the Strategy "attribute in (new query)" the result of internal query should give only a single attribute comparison outer query? That is, in the Select internal must be only and only the attribute of comparison?

As always, Thanks.

[Mecurtis]

What do you think the following solution is correct?

Select distinct Ndipart, NomeDipart
from Dept.
where in Ndipart
(Select distinct Ndipart
from imp
group by Ndipart
having avg (Stip)> 1000)

correct, but both are unnecessary DISTINCT.
The first because not necessarily that you can have separate departments, being the key of the table contained in clause Dept. SELECT The second, because the DISTINCT and implicit in the clause IN (subselects)

In particular, I want to know:

1) If you can put in the Select the only attribute of the group by leaving out the one used in having.

If I understood the question, generally you can make even the GROUP BY clause (and most appropriate frequent), but this situation does not resolve your problem

2) When in use where the Strategy "attribute in (new query)" the result of internal query should give only a single attribute comparison outer query? That is, in the Select internal must be only and only the attribute of comparison?

As always, thanks

Yes, even because it would make no sense and does not serve SELECTION other fields, because the query INNER serve only to search for values to be met for membership clause IN.

SQL-from Standard and can have only one parameter of comparison group. Some extensions of SQL (Oracle) also include the use of T-uple for this condition,

... WHERE (IDDipart, IDNazione) IN (SELECT IDDipart, IDNazione FROM ...)

useful in this case, for example if the codes of the department are unique only within a nation.

In SQL-Standard would have had to write a similar flaw,

... WHERE (IDNazione * 100000 + IDDipart) IN (SELECT IDNazione * 100000 + IDDipart FROM ...)

[Sukhvinder]

Thanks for answers, All clear.