Universal Database (netDB2) Introductory Guide

Using Operators And Predicates For Selective Retrieval Of Data

Combining tables with operator and Predicates: In order to receive data from n tables, the user can use operators and predicates.

OPERATORS: UNION, EXCEPT, INTERSECT
PREDICATES: IN, BETWEEN, LIKES,EXISTS 

syntax: {select statement} union {select statement}

example : 
      select name from student where grade >'B'
      union
      select student_id from student where dept= 'ICS'

This results in all tuples of students who have grades greater than 'B' and also those students who re in the ICS dept.
there could be various tuples which are common to both select statements, these tuples shall be displayed only once. 

syntax:{select statement} except {select statement}

example : 
      select name from student where grades > 'B'
      except
      select student_id from student where dept='ics'

This results in all tuples of students who have grades greater than 'B' and are all departments except the ICS dept. 

syntax:{select statement} intersect {select statement}

example: 
     select name from student where grades > 'B'
     intersect 
     select student_id from student where dept='ics'

This example results in all tuples who have grades greater than 'B' and also are from the ICS dept.

PREDICATES: 

example: 
       select name 
       from student 
       where age in (14, 20)

This results in all names of students who have ages 14 or 20 

example: 
       select name
       from student
       where age between (14, 20)

This results in all names of students who have ages between 14 and 20. 

syntax: select {fields} from {tablename} where [exists / not exists] {select statement 
example:
       select dept_number
       from department z
       where not exists
       (select * from projects where project_deptno=z.deptno)
       order by dept_no

this results in all department numbers who are not involved in projects or are not listed in the project_deptno. 

example:
       select dept_number
       from department z
       where  exists
       (select * from projects where project_deptno=z.deptno)
       order by dept_no

This results in all department numbers who are involved in projects (are not listed in the project_deptno).