Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 26-01-2009
Member
 
Join Date: Jan 2009
Posts: 41
How to Extract Particular Records from the Database?

Hello to all, if I have the following databases:

Quote:
IMP (Nimp, Name, Ndipart, Jobs, Director, Stip)
Departments (Ndipart, NomeDipart, City)
USO (Ndipart, Part)
Supply (Supplier, Part)
And I have the following request:

Quote:
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?

Quote:
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.
Reply With Quote
  #2  
Old 26-01-2009
Member
 
Join Date: Apr 2008
Posts: 193
Re: How to Extract Particular Records from the Database?

Quote:
What do you think the following solution is correct?

Quote:
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)

Quote:
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

Quote:
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,
Quote:
... 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,

Quote:
... WHERE (IDNazione * 100000 + IDDipart) IN (SELECT IDNazione * 100000 + IDDipart FROM ...)
Reply With Quote
  #3  
Old 26-01-2009
Member
 
Join Date: Jan 2009
Posts: 41
Re: How to Extract Particular Records from the Database?

Thanks for answers, All clear.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to Extract Particular Records from the Database?"
Thread Thread Starter Forum Replies Last Post
How to fetch records from multiple tables in a database ? kALAMATHI Software Development 4 29-12-2010 07:56 AM
Creating Database link in database Lachlann Software Development 3 28-01-2010 01:17 PM
Possibility to filter the records in a Database Elbanco Software Development 3 06-11-2009 09:21 PM
How to Extract records from Mysql database using JTable StudyBoy Software Development 3 08-04-2009 06:22 PM
convert filemaker pro database to access database Czack MS Office Support 3 15-04-2007 01:06 AM


All times are GMT +5.5. The time now is 04:22 AM.