I have exercise for SQL using Library database, on picture below You can see how some of tables are connected.
I need to return addressess of those adult members of library who has kids(juvenile) born before 1996. I have prepared SQL statement.
Is this statement right? Probably not, there is option that addresses could be the same for various of members. I don't know how to aproach to this statement statement.
SELECT DISTINCT a.street,a.city,a.state,a.zip FROM adult AS a
JOIN juvenile AS j
ON a.member_no = j.adult_member_no
WHERE YEAR(j.birth_date) < 1996
This is your query:
SELECT DISTINCT a.street, a.city, a.state, a.zip FROM adult a JOIN juvenile j ON a.member_no = j.adult_member_no WHERE YEAR(j.birth_date) < 1996;
Assuming the column and table names are correct, there is nothing a priori wrong with it. It should do what you want.
Is it the best possible statement? No. The issue is that the query needs to do the
join and then an aggregation (for the distinct). Another way to write the query is closer to the statement of the question:
SELECT a.* FROM adult a WHERE EXISTS (SELECT 1 FROM juvenile j WHERE a.member_no = j.adult_member_no AND YEAR(j.birth_date) < 1996 );
You can describe this query as: "Select adults who have a juvenile born before 1996."
Note that this version does not require
select distinct (unless there are duplicates in the
adult table, which is unlikely). Technically, this form of join is called a "semi-join". The "semi" is because it can stop at the first matching row. Once an appropriate juvenile is found for a given adult, no more need to be found.