In SQL, running on an Oracle database, write a query for each problem that solve
ID: 3587453 • Letter: I
Question
In SQL, running on an Oracle database, write a query for each problem that solves the task. Please ensure that it
1) Find for each person how many cars manufactured by Honda or Toyota they own. If a person does not own any such cars, she should still appear in the result with the number of cars reported as 0. Write your query using outer join. Be very careful not to count also the BMW cars a person owns.
2) Write the same query as above, but using a scalar query for counting the Hondas and Toyotas a person owns, without outer join.
Note: The queries listed above should yield the following result shown below. If possible, try to show the output for your queries.
NAME Jenson Button1 Rubens Barrichello 1 Sebastian Vettel 0 Mark Webber Lewis Hamilton 2 Felipe Massa 0 NUM_OF_CARS CDExplanation / Answer
1)
select P.Name, CASE WHEN count(C.Model) is null THEN 0 ELSE count(C.model) END as NUM_OF_CARS
from Person as P left join Owns as O
on P.Driver_id = O.Driver_id
left join Car as C
on O.Licence = C.Licence and C.Model in ("Honda", "Toyota")
group by P.Name;
2)
select P.Name, count(C.model) as NUM_OF_CARS
from Person as P join Owns as O
on P.Driver_id = O.Driver_id
left Car as C
on O.Licence = C.Licence and C.Model in ("Honda", "Toyota")
group by P.Name;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.