oracle sql query

can you show your table columns and some data so that we can write the query for you.

what have you tried?

Show what you have, some sample data and expected results

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

Thank you so much experts!!

I wait for your analysis. thx

I have two tables   Teacher and student table. ???

You have attached something else.

sorry same idea.  I wanted to simplify it so it is easily understood.

Idea is I have two tables Policy (Teacher)   coverage(student)

does my question / script make sense?
thanks for your help in advance.
Thanks

We need table structure, sample rows and the expected output you need.

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?

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';

Select allOpen 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 |

Select allOpen 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';

Select allOpen 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 |

Select allOpen in new window

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' );

Select allOpen 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' );

Select allOpen 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 |

Select allOpen 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' );

Select allOpen 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' );

Select allOpen 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 |

Select allOpen in new window

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

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;

Select allOpen in new window

OUTPUT

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

Select allOpen 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;

Select allOpen in new window

OUTPUT

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

Select allOpen in new window

@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

Select allOpen 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

Select allOpen in new window

OUTPUT

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

Select allOpen 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

Select allOpen 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

Select allOpen in new window

OUTPUT

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

Select allOpen in new window

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#

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.

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
--

Select allOpen 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
--

Select allOpen in new window

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.

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.

Checking. Thanks

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  

Select allOpen 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;

Select allOpen in new window

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.

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 ;

Select allOpen 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;

Select allOpen in new window

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;

Select allOpen in new window

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;

Select allOpen in new window

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.