In SQL, running on an Oracle database, write a query for each problem that solve

Question

In SQL, running on an Oracle database, write a query for each problem that solves the task.

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.

Table 1: Person Driver id Name Jenson Button Rubens Barrichello1st Ave Sebastin Vettel Mark Webber Lewis Hamilton Felipe Massa Unirersity St 8 Unirersity St Tabe 3: Owns Driver id License Report nAccident dateLocation 04-13-2008 Highland Heights Highland Heights Report nr License Driver id Damage amt

Explanation / Answer

1. Select Name, count(License) from Person left outer join Owns on Person.Driver_id=Owns.Driver_id left outer join Car on Owns.License=Car.License group by License Model having Car.Model not in ("BMW");

2. Select name,count(License) from ( Select Driver_id,Name from Person Union all Select License,Model from Car where Model not in("BMW") union all Select Driver_id,License from Owns) group by License;

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

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