From the course: Advanced SQL: Logical Query Processing, Part 2

Unlock the full course today

Join today to access over 22,600 courses taught by industry experts or purchase this course individually.

Solution

Solution - SQL Tutorial

From the course: Advanced SQL: Logical Query Processing, Part 2

Start my 1-month free trial

Solution

“

- [Instructor] As always, I'll follow execution order and begin with a FROM clause. Since I need to match two animals, I'm going to need two instances of the Animals table. As a starting point, I'll execute SELECT * FROM Animals AS A1 CROSS JOIN Animals AS A2; and this query returns each animal matched with every other animal. I need to match only animals of the same species and breed, and only purebreds. So, I'll change the CROSS JOIN to an INNER JOIN and add a qualification predicate ON A1.Species = A2.Species AND A1.Breed = A2.Breed; the visual clutter of all these redundant columns from the * is kind of a distraction so let's change it and include only the columns that we need. A1.Species, A1.Breed, A1.Name, AS Male, and A2.Name AS Female. I chose to get species, breed, and male from A1, but it's completely arbitrary. You're welcome to do it the other way around. I'll add an ORDER BY for convenience and…

Contents