Relational division using set operators - SQL Tutorial

- [Instructor] Our first task is the same as the previous chapters. Find candidates who possess all skills. You know who. And like before, let's first figure out whether Praveena has all skills and then generalize the solution. Here are the two queries that return all skills and Praveena's skills. This time we won't count on aggregations, pun intended, but instead, we will use set operators as you probably figured out from the chapter's title by now. An accept operator carefully placed between these two queries translates to English as find all skills except for Praveena's. If this query returns any skill, it means that Praveena doesn't have that skill and if it returns an empty set, we'll know she's a superhuman. Execute it. Bam. Praveena, like most of us, is not perfect. She doesn't know database design and SQL but that's only because she hasn't watched my courses yet. So also doesn't have Office skills and does not consider herself to be a leader, but unfortunately, I can't help her with that. Now, we can generalize it for all candidates and this time, we'll use a third query that returns all candidates. Supposedly, we could use candidate skills to get our candidates, like we did with the aggregation technique. A candidate with no skills will not show up anyway. That said, we're going to do it the right way this time and get the data from the candidates table. This is what this table represents in the real world and soon you will see why this is crucial. You can consider this a not so subtle hint. Select candidate from candidates returns all candidates. No surprise there. The only thing we care about is whether the accept query returns an empty set, which will indicate a superhero candidate. We need to eliminate candidates who don't have all skills. So a where clause is in order. Now we can move the except query into a not exists predicate to be evaluated for all candidates. Of course, Praveena's literal string filter must go because we want the subquery evaluated for each candidate from the outer query and for that, we must use a correlation, CS.Candidate equals C.Candidate, the candidate from the outer query. If this sub query returns even one row, the predicate will evaluate to a false. If it returns an empty set, the predicate will evaluate to true and the candidate will make it to the select and return. Execute, bam. Correctomundo. This query literally translates to English. Find all candidates for whom the set of all skills except theirs, is empty. And this is the same as those who have all skills. Direct, elegant, concise and clear. Amazing SQL, isn't it? Don't answer, it's a rhetorical question. Task two, find all candidates who fit the DB architect role. The only difference from the previous query is the divisor. Now, instead of evaluating candidates against the very high bar of possessing all skills, we need to evaluate them only for a subset of their skills, those required for the DB architect role. These skills are stored in the RoleSkills table, so let's replace the select from skills with a select from RoleSkills where role equals DB Architect. And that's it. Execute and bam, Chen and Darrin are a good fit for this role. You might have noticed that Darrin is becoming kind of a nuisance, so you know what you've got to do next, right?