Understanding Intersections and Differences

   
SQL>
SQL> CREATE TABLE empExam (
  2     empID  INT NOT NULL,
  3     ExamID     INT NOT NULL,
  4     Mark       INT,
  5     Taken   SMALLINT,
  6     Comments   VARCHAR(255),
  7     CONSTRAINT PK_empExam PRIMARY KEY (empID, ExamID));

Table created.

SQL>
SQL>
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (1,1,55,1,'Satisfactory');

1 row created.

SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (1,2,73,1,'Good result');

1 row created.

SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (2,3,44,1,'Hard');

1 row created.

SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (2,5,39,0,'Simple');

1 row created.

SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken) VALUES (2,6,63,1);

1 row created.

SQL>
SQL>
SQL>
SQL> CREATE TABLE dept (
  2     deptID INT NOT NULL PRIMARY KEY,
  3     empID    INT NOT NULL,
  4     ClassID      INT NOT NULL,
  5     EnrolledOn   DATE,
  6     Grade        INT);

Table created.

SQL>
SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (1,1,1,DATE '2002-09-23',62);

1 row created.

SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (2,1,2,DATE '2002-09-30',70);

1 row created.

SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (3,2,3,DATE '2003-09-23',51);

1 row created.

SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (4,2,5,DATE '2003-09-23',41);

1 row created.

SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (5,2,6,DATE '2003-09-23',68);

1 row created.

SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (6,3,4,DATE '2002-09-30',78);

1 row created.

SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (7,3,7,DATE '2002-09-30',80);

1 row created.

SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (8,4,8,DATE '2002-09-20',70);

1 row created.

SQL>
SQL>
SQL> SELECT empID
  2     FROM empExam
  3     WHERE Mark < 40
  4  MINUS
  5  SELECT empID
  6     FROM dept
  7     WHERE GRADE > 69
  8  ORDER BY empID;

     EMPID
----------
         2

1 row selected.

SQL>
SQL>
SQL> drop table empExam;

Table dropped.

SQL> drop table dept;

Table dropped.

Related examples in the same category

1.	MINUS: returns all those records from the first query, but not the second
2.	minus with like operator
3.	One result set 'minus' another result set
4.	An MINUS query that uses an ORDER BY clause
5.	Use COUNT to count the rows from a subquery with set operator
6.	Minus id column
7.	All locations, where courses are offering, have no departments

Understanding Intersections and Differences : Minus « Result Set « Oracle PL / SQL

Related examples in the same category