Name: ___________________________ USC ID: ______________________
INF 551 – Spring 2019
Quiz 7: SQL – 2 (10 points), 10 minutes
Consider three tables W, H, and D, storing the weight (in pounds), height (in centimeters), and
demographic information (gender and age) of people. Example data are shown below.

1. [5 points] Write an SQL query to compute natural full outer join of table W and H, using the left
and right outer joins. What is the result of the query on the example data?

select W.Name, W.Weight, H.Height from W left outer join H on W.Name = H.Name where
H.Height is Null
union all
select H.Name, W.Weight, H.Height from W right outer join H on W.Name = H.Name;

Result :
Name Weight Height
David 180 NULL
John 170 175
Mary 110 160
Bill NULL 170


2. [5 points] Write an SQL query to compute, for each gender, the average weight of people of the
gender with the age of 20 or above. What is the result of the query on the example data?

select D.Gender, avg(W.Weight) as Avg_weight from D, W
where D.Name = W.Name and D.Age >= 20
group by D.Gender;

Result :
Gender Avg_weight
F 110
M 175


欢迎咨询51作业君