SQL exercise to turn parent table statistics Query

Create TABLE Father (
F_ID Number (2) primary key,
F_name VARCHAR2 (10)
);
Create Table Son (
S_ID Number (2) primary key,
S_name VARCHAR2 (10),
S_height number (3,2),
S_money number,
F_ID Number (2),
Foreign KEY (f_id) references father (f_id)
);
--Inserting Father's information
Insert into Father values (1, ' He Shenda ');
Insert into Father values (2, ' He Zhongda ');
Insert into Father values (3, ' He Guo Da ');
Insert into Father values (4, ' strangers ');
--Inserting son information
Create sequence S1;
Insert into son values (S1.nextval, ' He Liang ', 1.70,6000,1);
Insert into son values (S1.nextval, ' He Shin ', 1.68,4000,1);
Insert into son values (S1.nextval, ' He Zhengyan ', 1.73,7000,2);
Insert into son values (S1.nextval, ' he zhengming ', 1.72,4000,2);
Insert into son values (S1.nextval, ' Ho Zhengyuan ', 1.68,8500,2);
Insert into son values (S1.nextval, ' Ho Zhenglu ', 1.66,5000,3);
Insert into son (s_id,f_id) values (s1.nextval,4);
--Query s_id, S_name, f_id
Select s_id,s_name,f_id from Son;
--inquire about the new tuition fee after 20%, pay attention to the price of 8000 blocks below.
Select s_name,s_money*1.2 from son where s_money>8000;
--Query s_id, S_name, f_id, F_name
Select F.*,s.s_id,s.s_name
From Father F
Join son S
On f.f_id=s.f_id;
--Query f_id, f_name, son number (no son's not shown)
Select F.f_id,f.f_name,count (S.s_name) as has several sons
From Father F
Join son S
On f.f_id=s.f_id
GROUP BY F.f_id,f.f_name
Having count (S.s_name) >0;
--Query the number of f_id, F_name and Sons (number of no sons shown as 0)
Select F.f_id,f.f_name,count (S.s_name) as has several sons
From Father F
Join son S
On f.f_id=s.f_id
GROUP BY F.f_id,f.f_name
Have count (s.s_name) is not null;
--Find out more than one son's father message: f_id, f_name, son number
Select F.f_id,f.f_name,count (s.s_name) as more than one son
From Father F
Join son S
On f.f_id=s.f_id
GROUP BY F.f_id,f.f_name
Having count (S.s_name) >1;
--Find out the most father information about your son: FID, fname, number of sons
Select F.f_id,f.f_name,count (s.s_id) as number
From Father F
Join son S
On f.f_id=s.f_id
Group BY F.f_id,f.f_name;
--、 find the father of the FID for (7,9,11), the height of each son
Select S_name,s_height from son, where f_id in (n/a);
--Find the tallest son in all father.
Select S_name from son where S_height= (
Select Max (s_height) from son
);
--Find the tallest son in each father.
Select son.* from Son,
(select F_id,max (s_height) highest son from son Group by f_id) x
where son.f_id=x.f_id and son.s_height=x. the tallest son;
--Find out all the sons and fathers from 1.8 to 1.65. Information: fid,fname,sid,sname,height;
--where is used because the aggregate function is not used in the previous
Select F.*,s.s_id,s.s_name,s.s_height
From Father F
Join son S
On f.f_id=s.f_id
where s.s_height between 1.65 and 1.8;
select * from Father;
select * from Son;

<! DOCTYPE Root [
<! ELEMENT Root (father +, son *) >
<! ELEMENT Father Empty>
<! ELEMENT son empty>
<! Attlist father
FID CDATA #REQUIRED
Name CDATA #REQUIRED
>
<! Attlist Son
Sid CDATA #REQUIRED
Name CDATA #REQUIRED
Sex (male | female) #REQUIRED
Age CDATA #REQUIRED
Tuition CDATA #REQUIRED
Height CDATA #IMPLIED
FID IDREFS #REQUIRED
>
]>
<root>
< father Fid= "p_1" name = "He Shenda"/>
< father Fid= "p_2" name = "ho tat"/>
< father Fid= "p_3" name = "ho Guo da"/>
< father Fid= "P_4" name = "Stranger"/>
< son sid= "1" name = "he liang" gender = "male" age = "24" tuition = "5000" height = "1.72" fid= "P_1"/>
< son sid= "1" name = "He Shin" gender = "male" age = "24" tuition = "5000" height = "1.72" fid= "P_1"/>
< son sid= "1" name = "He Zhengyan" gender = "male" age = "24" tuition = "1000" height = "1.72" fid= "p_2"/>
< son sid= "1" name = "Ming he" gender = "male" age = "24" tuition = "5000" height = "1.72" fid= "p_2"/>
< son sid= "1" name = "He Yuan" gender = "male" age = "24" tuition = "5000" height = "1.72" fid= "p_2"/>
< son sid= "1" name = "he lu" sex = "male" age = "24" tuition = "5000" height = "1.72" fid= "P_3"/>
</root>

SQL exercise to turn parent table statistics Query