oracle sql query

I need urgent help with a simple query.
I have two tables   Teacher and student table.
I want a list of all teachers who teach  Junior and  sophomore and senior students.
I am going crazy.
any idea?
Thank you.
ALad2005Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
can you show your table columns and some data so that we can write the query for you.
0
sdstuberCommented:
what have you tried?

Show what you have, some sample data and expected results
0
ALad2005Author Commented:
I just wanted a basic pull.
I have this query --   Policy and coverage table.
Same idea.  I want to pull all policy records that have forms,
  ='555.5'
 ='353.1'
 '350.0'
I have attached the file.
F--Main-Support-Scans-Reports-Forms-.sql
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ALad2005Author Commented:
Thank you so much experts!!
0
ALad2005Author Commented:
I wait for your analysis. thx
0
Pawan KumarDatabase ExpertCommented:
I have two tables   Teacher and student table. ???

You have attached something else.
0
ALad2005Author Commented:
sorry same idea.  I wanted to simplify it so it is easily understood.

Idea is I have two tables Policy (Teacher)   coverage(student)
0
ALad2005Author Commented:
does my question / script make sense?
thanks for your help in advance.
Thanks
0
Pawan KumarDatabase ExpertCommented:
We need table structure, sample rows and the expected output you need.
0
ALad2005Author Commented:
Ok, here it is:
CREATE TABLE SIMDATA.TEACHER
(  T_NAME                  VARCHAR2(80 BYTE),
  CLASS                   VARCHAR2(80 BYTE) )
;  


CREATE TABLE SIMDATA.STUDENT
(  S_NAME                  VARCHAR2(80 BYTE),
   CLASS                   VARCHAR2(80 BYTE),
   S_TYPE                  VARCHAR2(10 BYTE)
    )
;  
SELECT * FROM SIMDATA.STUDENT;
SELECT * FROM SIMDATA.TEACHER;

INSERT INTO SIMDATA.TEACHER(T_NAME,CLASS) VALUES ('SMITH','MATH');
INSERT INTO SIMDATA.TEACHER(T_NAME,CLASS) VALUES ('JOHN','GEOGRAPHY');
INSERT INTO SIMDATA.TEACHER(T_NAME,CLASS) VALUES ('SAM','SCIENCE');

INSERT INTO SIMDATA.STUDENT(S_NAME,CLASS,S_TYPE) VALUES ('JOE',  'MATH','YOUTH');
INSERT INTO SIMDATA.STUDENT(S_NAME,CLASS,S_TYPE) VALUES ('HARRY','MATH','ADULT');
INSERT INTO SIMDATA.STUDENT(S_NAME,CLASS,S_TYPE) VALUES ('JERRY','MATH','JUNIOR');

INSERT INTO SIMDATA.STUDENT(S_NAME,CLASS,S_TYPE) VALUES ('MARY',  'SCIENCE','YOUTH');
INSERT INTO SIMDATA.STUDENT(S_NAME,CLASS,S_TYPE) VALUES ('MARGI' ,'SCIENCE','ADULT');
INSERT INTO SIMDATA.STUDENT(S_NAME,CLASS,S_TYPE) VALUES ('SANDRA','SCIENCE','JUNIOR');

INSERT INTO SIMDATA.STUDENT(S_NAME,CLASS,S_TYPE) VALUES ('MEGH','GEOGRAPHY','YOUTH');
INSERT INTO SIMDATA.STUDENT(S_NAME,CLASS,S_TYPE) VALUES ('HENRY','GEOGRAPHY','JUNIOR');


Q1. Which teacher teaches all types of students (youth,adult and junior)
Q2. Which teacher teaches Youth and junior?
0
Pawan KumarDatabase ExpertCommented:
Please use the solutions below -

Q1. Which teacher teaches all types of students (youth,adult and junior)

SOLUTION
SELECT T_NAME,S_NAME,S_TYPE,T.CLASS FROM STUDENT S INNER JOIN Teacher T ON s.CLASS = T.CLASS
WHERE S_Type = 'YOUTH' OR S_Type = 'ADULT' OR S_Type = 'JUNIOR';

Open in new window


OUTPUT
| T_NAME | S_NAME | S_TYPE |     CLASS |
|--------|--------|--------|-----------|
|  SMITH |    JOE |  YOUTH |      MATH |
|  SMITH |  HARRY |  ADULT |      MATH |
|  SMITH |  JERRY | JUNIOR |      MATH |
|    SAM |   MARY |  YOUTH |   SCIENCE |
|    SAM |  MARGI |  ADULT |   SCIENCE |
|    SAM | SANDRA | JUNIOR |   SCIENCE |
|   JOHN |   MEGH |  YOUTH | GEOGRAPHY |
|   JOHN |  HENRY | JUNIOR | GEOGRAPHY |

Open in new window


Q2. Which teacher teaches Youth and junior?
SOLUTION
SELECT T_NAME,S_NAME,S_TYPE,T.CLASS FROM STUDENT S INNER JOIN Teacher T ON s.CLASS = T.CLASS
WHERE S_Type = 'YOUTH' OR S_Type = 'JUNIOR';

Open in new window


OUTPUT
| T_NAME | S_NAME | S_TYPE |     CLASS |
|--------|--------|--------|-----------|
|  SMITH |    JOE |  YOUTH |      MATH |
|  SMITH |  JERRY | JUNIOR |      MATH |
|    SAM |   MARY |  YOUTH |   SCIENCE |
|    SAM | SANDRA | JUNIOR |   SCIENCE |
|   JOHN |   MEGH |  YOUTH | GEOGRAPHY |
|   JOHN |  HENRY | JUNIOR | GEOGRAPHY |

Open in new window

0
Pawan KumarDatabase ExpertCommented:
We can also use IN clause instead of OR.

Q1. Which teacher teaches all types of students (youth,adult and junior)

SOLUTION
SELECT T_NAME,S_NAME,S_TYPE,T.CLASS FROM STUDENT S INNER JOIN Teacher T ON s.CLASS = T.CLASS
WHERE S_Type IN ( 'YOUTH' , 'ADULT' , 'JUNIOR' );

Open in new window


OR

SELECT T_NAME,S_NAME,S_TYPE,T.CLASS FROM STUDENT S INNER JOIN Teacher T ON s.CLASS = T.CLASS
AND S_Type IN ( 'YOUTH' , 'ADULT' , 'JUNIOR' );

Open in new window


OUTPUT
| T_NAME | S_NAME | S_TYPE |     CLASS |
|--------|--------|--------|-----------|
|  SMITH |    JOE |  YOUTH |      MATH |
|  SMITH |  HARRY |  ADULT |      MATH |
|  SMITH |  JERRY | JUNIOR |      MATH |
|    SAM |   MARY |  YOUTH |   SCIENCE |
|    SAM |  MARGI |  ADULT |   SCIENCE |
|    SAM | SANDRA | JUNIOR |   SCIENCE |
|   JOHN |   MEGH |  YOUTH | GEOGRAPHY |
|   JOHN |  HENRY | JUNIOR | GEOGRAPHY |

Open in new window


Q2. Which teacher teaches Youth and junior?

SOLUTION
SELECT T_NAME,S_NAME,S_TYPE,T.CLASS FROM STUDENT S INNER JOIN Teacher T ON s.CLASS = T.CLASS
WHERE S_Type IN ( 'YOUTH' , 'JUNIOR' );

Open in new window


OR
SELECT T_NAME,S_NAME,S_TYPE,T.CLASS FROM STUDENT S INNER JOIN Teacher T ON s.CLASS = T.CLASS
AND S_Type IN ( 'YOUTH', 'JUNIOR' );

Open in new window



OUTPUT
| T_NAME | S_NAME | S_TYPE |     CLASS |
|--------|--------|--------|-----------|
|  SMITH |    JOE |  YOUTH |      MATH |
|  SMITH |  JERRY | JUNIOR |      MATH |
|    SAM |   MARY |  YOUTH |   SCIENCE |
|    SAM | SANDRA | JUNIOR |   SCIENCE |
|   JOHN |   MEGH |  YOUTH | GEOGRAPHY |
|   JOHN |  HENRY | JUNIOR | GEOGRAPHY |

Open in new window

0
ALad2005Author Commented:
thanks for answer Pawan.
for the first question............
 But what I am looking at is bit deeper.
I am not looking for OR  clause.
what I want to see is list of teacher which teaches youth AND adult AND junior  
so if a teacher teaches just adult AND youth, but not junior  I don't want to see that.
Only the teacher that teach all the types of students.
Thanks
Let me know
0
Pawan KumarDatabase ExpertCommented:
Ok,Please try this -

Q1. Which teacher teaches all types of students (youth,adult and junior)
SOLUTION
SELECT a.T_NAME FROM 
(
  SELECT T_NAME,S_NAME,S_TYPE,T.CLASS FROM STUDENT S INNER JOIN Teacher T ON s.CLASS = T.CLASS
  WHERE S_Type = 'YOUTH'
)a  
INNER JOIN 
(
  SELECT T_NAME,S_NAME,S_TYPE,T.CLASS FROM STUDENT S INNER JOIN Teacher T ON s.CLASS = T.CLASS
  WHERE S_Type = 'ADULT'
)b
ON a.T_NAME = b.T_NAME
INNER JOIN
(
  SELECT T_NAME,S_NAME,S_TYPE,T.CLASS FROM STUDENT S INNER JOIN Teacher T ON s.CLASS = T.CLASS
  WHERE S_Type = 'JUNIOR'
)c
ON c.T_NAME = b.T_NAME;

Open in new window

OUTPUT
| T_NAME |
|--------|
|  SMITH |
|    SAM |

Open in new window


Q2. Which teacher teaches Youth and junior?
SOLUTION
SELECT a.T_NAME FROM 
(
  SELECT T_NAME,S_NAME,S_TYPE,T.CLASS FROM STUDENT S INNER JOIN Teacher T ON s.CLASS = T.CLASS
  WHERE S_Type = 'YOUTH'
)a  
INNER JOIN
(
  SELECT T_NAME,S_NAME,S_TYPE,T.CLASS FROM STUDENT S INNER JOIN Teacher T ON s.CLASS = T.CLASS
  WHERE S_Type = 'JUNIOR'
)c
ON c.T_NAME = a.T_NAME;

Open in new window

OUTPUT
| T_NAME |
|--------|
|  SMITH |
|    SAM |
|   JOHN |        

Open in new window

0
sdstuberCommented:
It's inefficient to requery the same tables repeatedly.  Simply query once and aggregate.
By using a simple aggregate and count it doesn't matter how many s_type value you have, the query structure remains the same. (you simply change the IN list and the count)

If you go with the repeat query and join method, just imagine if you had 20 or 100 s_type values.
While that might be unrealistic for this particular sample problem, as a learning exercise it's important to think about data scaling.



Q1

  SELECT t.t_name
    FROM student s INNER JOIN teacher t ON s.class = t.class
   WHERE s.s_type IN ('YOUTH', 'ADULT', 'JUNIOR')
GROUP BY t.t_name
  HAVING COUNT(DISTINCT s.s_type) = 3;

Q2

  SELECT t.t_name
    FROM student s INNER JOIN teacher t ON s.class = t.class
   WHERE s.s_type IN ('YOUTH', 'JUNIOR')
GROUP BY t.t_name
  HAVING COUNT(DISTINCT s.s_type) = 2;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pawan KumarDatabase ExpertCommented:
@Author -

If you dont want to use multiple selects then below are some nultiple options for you-

Q1. Which teacher teaches all types of students (youth,adult and junior)

SOLUTION 1
SELECT t.t_name
FROM STUDENT s INNER JOIN TEACHER t ON s.class = t.class
GROUP BY t.t_name
HAVING SUM(CASE WHEN s.s_type IN ('YOUTH', 'ADULT', 'JUNIOR') THEN 1 ELSE 0 END) >= 3

Open in new window


SOLUTION 2
SELECT t_name
FROM 
(
	SELECT t.t_name,SUM(CASE WHEN s.s_type IN ('YOUTH', 'ADULT', 'JUNIOR') THEN 1 ELSE 0 END) rnk
	FROM STUDENT s INNER JOIN TEACHER t ON s.class = t.class
	GROUP BY t.t_name
)r WHERE rnk >= 3

Open in new window


OUTPUT
| T_NAME |
|--------|
|  SMITH |
|    SAM |

Open in new window


Q2. Which teacher teaches Youth and junior?

SOLUTION 1
SELECT t_name
FROM 
(
	SELECT t.t_name,SUM(CASE WHEN s.s_type IN ('YOUTH', 'JUNIOR') THEN 1 ELSE 0 END) rnk
	FROM STUDENT s INNER JOIN TEACHER t ON s.class = t.class
	GROUP BY t.t_name
)r WHERE rnk >= 2

Open in new window


SOLUTION 2
SELECT t.t_name
FROM STUDENT s INNER JOIN TEACHER t ON s.class = t.class
GROUP BY t.t_name
HAVING SUM(CASE WHEN s.s_type IN ('YOUTH', 'JUNIOR') THEN 1 ELSE 0 END) >= 2

Open in new window


OUTPUT
| T_NAME |
|--------|
|  SMITH |
|   JOHN |
|    SAM |        

Open in new window

0
ALad2005Author Commented:
Thank you Pawan Kuman and sdstuber.
both of you guys made me think outside of the box.
I had kind of thought of the first solution by Pawan
but then sdstuber  showed faster and slicker solution.  It took me a while to understand it.
then Pawan also gave another good solution!!!
now I have 3 to choose from
Thank you so much guys.
You are the best!!
I am in quandry about splitting points,  Hope you don't mind the split.
Al
(I may reach out to you guys in future !!! :)  thx
https://www.experts-exchange.com/questions/29070241/oracle-sql-query.html#
0
sdstuberCommented:
The accepted answer isn't actually correct.

While Pawan was obviously attempting to extend on my answer, he apparently didn't understood how it worked.

For the limited data set, his queries do produce the desired results but are not actually a reliable way to solve the problem because they don't work in general.

As an example - simply inserting this teacher with 3 YOUTH students is sufficient to break any of the examples given in Pawan's accepted answer.

INSERT INTO TEACHER(T_NAME,CLASS) VALUES ('TIM','HISTORY');

INSERT INTO STUDENT(S_NAME,CLASS,S_TYPE) VALUES ('BILL','HISTORY','YOUTH');
INSERT INTO STUDENT(S_NAME,CLASS,S_TYPE) VALUES ('BOB','HISTORY','YOUTH');
INSERT INTO STUDENT(S_NAME,CLASS,S_TYPE) VALUES ('BORIS','HISTORY','YOUTH');


I suggest having the question reopened and then accepting only the answer that actually works (ID: 42382721)

While it would be possible to fix Pawan's answers so they could work, doing so would simply create a more complicated syntax (and less efficient because it involves extra work) over what was already provided.
0
Pawan KumarDatabase ExpertCommented:
Not much changes are required if we have this kind of data. We can do like below. Just tweaked having clause a bit.

Q1. Which teacher teaches all types of students (youth,adult and junior)
--
SELECT t.T_NAME
FROM STUDENT s INNER JOIN TEACHER t ON s.class = t.class
GROUP BY t.t_name
HAVING 
SUM(CASE WHEN s.s_type = 'YOUTH' THEN 1 ELSE 0 END) +
SUM(CASE WHEN s.s_type = 'Adult' THEN 1 ELSE 0 END) +
SUM(CASE WHEN s.s_type = 'JUNIOR' THEN 1 ELSE 0 END) >= 3
--

Open in new window


Q2. Which teacher teaches Youth and junior?
--
SELECT t.T_NAME
FROM STUDENT s INNER JOIN TEACHER t ON s.class = t.class
GROUP BY t.t_name
HAVING 
SUM(CASE WHEN s.s_type = 'YOUTH' THEN 1 ELSE 0 END) +
SUM(CASE WHEN s.s_type = 'JUNIOR' THEN 1 ELSE 0 END) >= 2
--

Open in new window

0
sdstuberCommented:
Nope - those don't work either  they have the same problem  the TIM teacher will still be returned even though he only teaches YOUTH classes

Worse, they also cause the teachers that should be included to be removed.
0
ALad2005Author Commented:
This is great discussion.  Thank you for your expert analysis on this subject Sdstuber!!  You really know your material.
I jumped the gun when I saw different solutions and quickly closed the question.  
 Let me reassess.   thanks.
0
Pawan KumarDatabase ExpertCommented:
Checking. Thanks
0
Pawan KumarDatabase ExpertCommented:
Sorry last time forget insert the new rows that Sdstuber added. :)

TESTED SQLfiddle - http://sqlfiddle.com/#!4/0d9d1/19

Q1.

SELECT T_NAME FROM 
(
  SELECT t.T_NAME,
  SUM(CASE WHEN s.S_TYPE = 'YOUTH' THEN 1 ELSE 0 END)+
  SUM(CASE WHEN s.S_TYPE = 'ADULT' THEN 1 ELSE 0 END)+
  SUM(CASE WHEN s.S_TYPE = 'JUNIOR' THEN 1 ELSE 0 END) cnt
  FROM 
  ( 
    SELECT DISTINCT class,S_TYPE FROM STUDENT 
  )s 
  INNER JOIN TEACHER t ON s.class = t.class
  WHERE s.S_TYPE IN ('YOUTH','ADULT','JUNIOR')
  GROUP BY t.T_NAME
) WHERE cnt >= 3  

Open in new window


Q2.

SELECT T_NAME FROM 
(
  SELECT t.T_NAME,
  SUM(CASE WHEN s.S_TYPE = 'YOUTH' THEN 1 ELSE 0 END)+
  SUM(CASE WHEN s.S_TYPE = 'JUNIOR' THEN 1 ELSE 0 END) cnt
  FROM 
  ( 
    SELECT DISTINCT class,S_TYPE FROM STUDENT 
  )s 
  INNER JOIN TEACHER t ON s.class = t.class
  WHERE s.S_TYPE IN ('YOUTH','JUNIOR')
  GROUP BY t.T_NAME
) WHERE cnt >= 2;

Open in new window

0
sdstuberCommented:
yes, the options in ID: 42385781  do work for this data set, but they add the extra, unnecessary complexity and inefficient step of an extra 3 CASE evaluations that I mentioned previously.

So, you "could" use them, but why would you want to?

Sometimes complexity is necessary, but in this case it isn't a helpful option.
0
Pawan KumarDatabase ExpertCommented:
One more update.. we can remove the cases also.....

SELECT T_NAME FROM 
(
  SELECT t.T_NAME,COUNT(*) cnt
  FROM 
  ( 
    SELECT DISTINCT class,S_TYPE FROM STUDENT 
  )s 
  INNER JOIN TEACHER t ON s.class = t.class
  WHERE s.S_TYPE IN ('YOUTH','ADULT','JUNIOR')
  GROUP BY t.T_NAME
) WHERE cnt >= 3 ;

Open in new window


SELECT T_NAME FROM 
(
  SELECT t.T_NAME, COUNT(*) cnt
  FROM 
  ( 
    SELECT DISTINCT class,S_TYPE FROM STUDENT 
  )s 
  INNER JOIN TEACHER t ON s.class = t.class
  WHERE s.S_TYPE IN ('YOUTH','JUNIOR')
  GROUP BY t.T_NAME
) WHERE cnt >= 2;

Open in new window

0
sdstuberCommented:
no, those are wrong too.

Pawan - please don't confuse the thread chasing points with all these variations that don't actually work.
It's noble to try to help but a flurry of untested (or insufficiently tested) queries just muddies the waters.

Here's another simple example illustrating the problem with the most recent queries.
I've expanded teacher TIM's curriculum from just "history" to more specific classes of "US History", "European History" and "World History"  all available to Youth students.

He shouldn't show up in either query because he only teaches one type of student.

CREATE TABLE teacher
(
    t_name    VARCHAR2(80 BYTE),
    class     VARCHAR2(80 BYTE)
);


CREATE TABLE student
(
    s_name    VARCHAR2(80 BYTE),
    class     VARCHAR2(80 BYTE),
    s_type    VARCHAR2(10 BYTE)
);

SELECT * FROM student;

SELECT * FROM teacher;

INSERT INTO teacher(t_name, class)
     VALUES ('SMITH', 'MATH');

INSERT INTO teacher(t_name, class)
     VALUES ('JOHN', 'GEOGRAPHY');

INSERT INTO teacher(t_name, class)
     VALUES ('SAM', 'SCIENCE');

INSERT INTO student(s_name, class, s_type)
     VALUES ('JOE', 'MATH', 'YOUTH');

INSERT INTO student(s_name, class, s_type)
     VALUES ('HARRY', 'MATH', 'ADULT');

INSERT INTO student(s_name, class, s_type)
     VALUES ('JERRY', 'MATH', 'JUNIOR');

INSERT INTO student(s_name, class, s_type)
     VALUES ('MARY', 'SCIENCE', 'YOUTH');

INSERT INTO student(s_name, class, s_type)
     VALUES ('MARGI', 'SCIENCE', 'ADULT');

INSERT INTO student(s_name, class, s_type)
     VALUES ('SANDRA', 'SCIENCE', 'JUNIOR');

INSERT INTO student(s_name, class, s_type)
     VALUES ('MEGH', 'GEOGRAPHY', 'YOUTH');

INSERT INTO student(s_name, class, s_type)
     VALUES ('HENRY', 'GEOGRAPHY', 'JUNIOR');


INSERT INTO teacher(t_name, class)
     VALUES ('TIM', 'US HISTORY');

INSERT INTO teacher(t_name, class)
     VALUES ('TIM', 'EUROPEAN HISTORY');

INSERT INTO teacher(t_name, class)
     VALUES ('TIM', 'WORLD HISTORY');


INSERT INTO student(s_name, class, s_type)
     VALUES ('MINNIE', 'US HISTORY', 'YOUTH');

INSERT INTO student(s_name, class, s_type)
     VALUES ('MOLLY', 'US HISTORY', 'YOUTH');

INSERT INTO student(s_name, class, s_type)
     VALUES ('MICHELLE', 'US HISTORY', 'YOUTH');


INSERT INTO student(s_name, class, s_type)
     VALUES ('BILL', 'WORLD HISTORY', 'YOUTH');

INSERT INTO student(s_name, class, s_type)
     VALUES ('BOB', 'WORLD HISTORY', 'YOUTH');

INSERT INTO student(s_name, class, s_type)
     VALUES ('BORIS', 'WORLD HISTORY', 'YOUTH');

INSERT INTO student(s_name, class, s_type)
     VALUES ('TAMMY', 'EUROPEAN HISTORY', 'YOUTH');

INSERT INTO student(s_name, class, s_type)
     VALUES ('TOMMY', 'EUROPEAN HISTORY', 'YOUTH');

INSERT INTO student(s_name, class, s_type)
     VALUES ('TONY', 'EUROPEAN HISTORY', 'YOUTH');


COMMIT;

Open in new window

0
Pawan KumarDatabase ExpertCommented:
Things can be be handled. But if we requirements in small small batches we shall face issues. We can only work on the data set we receive.

SELECT T_Name
FROM 
(
  SELECT S_TYPE,T_Name
  FROM 
  ( 
    SELECT class,S_TYPE FROM STUDENT 
    WHERE S_TYPE IN ('YOUTH','ADULT','JUNIOR')
  )s 
  INNER JOIN TEACHER t ON s.class = t.class
  GROUP BY S_TYPE,T_Name
) GROUP BY T_Name
HAVING COUNT(*) >= 3;

Open in new window

0
sdstuberCommented:
That one is technically correct, but if the asker chooses to go this route, then I suggest a couple of changes.

First this works
    "GROUP BY S_TYPE,T_Name"

but it doesn't make sense to use a GROUP BY when you don't have an aggregate.  Functionally, it will create distinct pairs, but if you want distinct, it makes more sense to make be explicit and simply use DISTINCT

Also,  
     "HAVING COUNT(*) >= 3"

will also work, but again it doesn't really make sense to use ">=" for a condition we already know can't possibly be greater than 3.
It's not technically wrong, but it implies functionality that doesn't exist. We've already constrained the number of possible types at 3, so no count could possibly be greater than 3.

With these changes the query might look something like this.  Still more verbose than what I originally posted but this one should also work and work efficiently.

  SELECT t_name
    FROM (SELECT DISTINCT s_type, t_name
            FROM (SELECT class, s_type
                    FROM student
                   WHERE s_type IN ('YOUTH', 'ADULT', 'JUNIOR')) s
                 INNER JOIN teacher t ON s.class = t.class)
GROUP BY t_name
  HAVING COUNT(*) = 3;


>>>> We can only work on the data set we receive.

I don't think I can agree with that statement.  When testing a query, if the input data is obviously insufficient then it behooves us to either point that out, or accommodate.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.