Considering the following four tables: Farmer(SIN, name, age, sex, vname) Villag

Question

Considering the following four tables:
Farmer(SIN, name, age, sex, vname)
Village(name, area, population, province)
Kids(SIN, f_sin, m_sin, s_name)
School(sname, vname, no_classes)

Where: sname corresponds to school name, vname corresponds to village name, and f_sin and m_sin stand for the father’s and mother’s SIN, respectively.

Code the following queries in SQL:

1) Find each school attended only by kids from the same village where the school is located?
2) Find farmers who have at least one kid in every school registered in the database?

Explanation / Answer

1) Find each school attended only by kids from the same village where the school is located? for this one we need clear links between the village, the school and the kids. We'll use the Farmer parents as a link between the kids' homes and the school location: School vname = Village name Village name = Farmer vname Kids f_sin OR m_sin = Farmer SIN Kids s_name = School sname These are all the possible links we can use here, so we need to select schools such that: the kids attend the school, the kids live in the same village with their farmer parent(s), the school is located in that same village This translates to: SELECT S.name from School S, Village V, Farmer F, Kids K WHERE S.vname = V.name AND V.name = F.vname AND K.s_name = S.sname AND (K.f_sin = F.SIN OR K.m_sin = F.SIN); You'd still be safer creating the tables, filling them with dummy data, and trying this out in realtime. ========================================= 2) Find farmers who have at least one kid in every school registered in the database? This second one made me think a little because the formulation suggests A LOT of correlated subqueries or JOINS, which are to be avoided unless absolutely necessary. While I'm not able to provide a 100% sure answer since my SQL is a bit rusty, I can at least point you in the direction of a good alternative using aggregate functions + GROUP BY: We need to find farmers with 1 or more kids in every school from the database. So when we get a list of all the kids for each farmer, the total of DISTINCT occurrences for each school's name in all the kinds rows should be equal to the total number of schools in the system. We use distinct to get rid of redundant school rows in case a parent has 8 kids and all of them go to 1 school, then they don't attend the other schools and shouldn't be on this list. I hope you're still following. So counting occurrences of an item is SQL is done with COUNT(column_name), we compare the count() for distinct schools attended for each set of kids and check if it's equal to count(sname) from the original Schools table; if they're equal, then we select that Farmer parent's name and whatnot: SELECT F.SIN, F.name from Farmer F, Kids K WHERE K.f_sin = F.SIN OR K.m_sin = F.SIN GROUP BY F.SIN HAVING COUNT(DISTINCT K.sname) > (SELECT COUNT(sname) FROM School) this is basically creating a table where you have the parent (farmer) column and a column indicating the total distinct schools attended by their kids the HAVING clause filters out the totals that are less than # all schools in DB Again, please run this in an actual DB to see if the logic/syntax sticks, haven't done this in ages. Hope it helps either way.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.