Dataset INTERSECT
27>
28>
29> CREATE TABLE works_on (emp_no INTEGER NOT NULL,
30> project_no CHAR(4) NOT NULL,
31> job CHAR (15) NULL,
32> enter_date DATETIME NULL)
33>
34> insert into works_on values (1, 'p1', 'analyst', '1997.10.1')
35> insert into works_on values (1, 'p3', 'manager', '1999.1.1')
36> insert into works_on values (2, 'p2', 'clerk', '1998.2.15')
37> insert into works_on values (2, 'p2', NULL, '1998.6.1')
38> insert into works_on values (3, 'p2', NULL, '1997.12.15')
39> insert into works_on values (4, 'p3', 'analyst', '1998.10.15')
40> insert into works_on values (5, 'p1', 'manager', '1998.4.15')
41> insert into works_on values (6, 'p1', NULL, '1998.8.1')
42> insert into works_on values (7, 'p2', 'clerk', '1999.2.1')
43> insert into works_on values (8, 'p3', 'clerk', '1997.11.15')
44> insert into works_on values (7, 'p1', 'clerk', '1998.1.4')
45>
46> select * from works_on
47>
48> CREATE TABLE employee (emp_no INTEGER NOT NULL,
49> emp_fname CHAR(20) NOT NULL,
50> emp_lname CHAR(20) NOT NULL,
51> dept_no CHAR(4) NULL)
52>
53> insert into employee values(1, 'Matthew', 'Smith', 'd3')
54> insert into employee values(2, 'Ann', 'Jones', 'd3')
55> insert into employee values(3, 'John', 'Barrimore','d1')
56> insert into employee values(4, 'James', 'James', 'd2')
57> insert into employee values(5, 'Elsa', 'Bertoni', 'd2')
58> insert into employee values(6, 'Elke', 'Hansel', 'd2')
59> insert into employee values(7, 'Sybill', 'Moser', 'd1')
60>
61> select * from employee
62> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
emp_no project_no job enter_date
----------- ---------- --------------- -----------------------
1 p1 analyst 1997-10-01 00:00:00.000
1 p3 manager 1999-01-01 00:00:00.000
2 p2 clerk 1998-02-15 00:00:00.000
2 p2 NULL 1998-06-01 00:00:00.000
3 p2 NULL 1997-12-15 00:00:00.000
4 p3 analyst 1998-10-15 00:00:00.000
5 p1 manager 1998-04-15 00:00:00.000
6 p1 NULL 1998-08-01 00:00:00.000
7 p2 clerk 1999-02-01 00:00:00.000
8 p3 clerk 1997-11-15 00:00:00.000
7 p1 clerk 1998-01-04 00:00:00.000
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
emp_no emp_fname emp_lname dept_no
----------- -------------------- -------------------- -------
1 Matthew Smith d3
2 Ann Jones d3
3 John Barrimore d1
4 James James d2
5 Elsa Bertoni d2
6 Elke Hansel d2
7 Sybill Moser d1
(7 rows affected)
1>
2> SELECT emp_no FROM employee WHERE dept_no = 'd1'
3> INTERSECT
4> SELECT emp_no FROM works_on WHERE enter_date < '01.01.1999'
5> GO
emp_no
-----------
3
7
(2 rows affected)
1>
2> drop table employee
3> drop table works_on
4> GO
1>