I have a table like this (two columns)
std_id skill 1 Java 1 J2EE 1 Struts 2 Java 2 J2EE 3 Java 3 J2EE 3 Struts
Now I want to retrieve student IDs (std_id's) for those students that have three skills: Java, J2EE, Struts. Each student must posses all skills, which means my answer should be as follows:
std_id 1 3
How do I write this query?
The students you're looking for are those that have all three rows in the skills table. This requires analysis of a group of rows, namely, the group of skill rows for each student. I would suggest to you that GROUP BY will be involved in the solution.
SELECT std_id FROM skills WHERE skill IN ('Java','J2EE','Struts') GROUP BY std_id HAVING COUNT(*) = 3
The WHERE clause ensures that only the three rows of skills which we're interested in are retrieved from the table for each student. The GROUP BY and HAVING clauses ensure that all three were found.
How would you modify this solution to return, say, all students who had at least two of those three skills?
Dig Deeper on Oracle development languages
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading