Considering the following four tables: Farmer (SIN, name, age, sex, vname) Villa
ID: 3777191 • Letter: C
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 :
a) Find SIN of farmers who have some kids going to school?
b) Find each school attended only by kids from the same village where the school is located?
c) Find the name(s) of the most crowded village(s) (most population)?
d) Find farmers who have at least one kid in every school registered in the database?
e) Find name(s) of village(s) with no schools?
Explanation / Answer
a)SELECT DISTINCT s.SIN FROM Farmer s,Kids k WHERE s.sin=k.f_sin ;
b)SELECT sname FROM Schools WHERE sname NOT IN(SELECT DISTINCT s.sname FROM Schools s, Kids k ,Farmer f WHERE k.f_sin=f.SIN AND f.vname!=s.vname);
c)SELECT name FROM Village WHERE population=MAX(population);
d)SELECT DISTINCT f.name FROM Farmer f, KIds k count(k.f_sin) FROM (SELECT DISTINCT s_name, f_sin FROM Kids ORDER BY f_sin)
e)SELECT name From Village WHERE name NOT IN(SELECT DISTINCT vname FROM School);