33>
34>
35> -- INTERSECT: find all employee numbers that belong to the result set of the first query as well as of the second query.
36> CREATE TABLE employee (emp_no INTEGER NOT NULL,
37> emp_fname CHAR(20) NOT NULL,
38> emp_lname CHAR(20) NOT NULL,
39> dept_no CHAR(4) NULL)
40>
41> insert into employee values(1, "Matthew", "Smith", "d3")
42> insert into employee values(2, "Ann", "Jones", "d3")
43> insert into employee values(3, "John", "Barrimore","d1")
44> insert into employee values(4, "James", "James", "d2")
45> insert into employee values(5, "Elsa", "Bertoni", "d2")
46> insert into employee values(6, "Elke", "Hansel", "d2")
47> insert into employee values(7, "Sybill", "Moser", "d1")
48>
49> select * from employee
50> GO
(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>
3> CREATE TABLE department(dept_no CHAR(4) NOT NULL,
4> dept_name CHAR(25) NOT NULL,
5> location CHAR(30) NULL)
6>
7> insert into department values ("d1", "developer", "Dallas")
8> insert into department values ("d2", "tester", "Seattle")
9> insert into department values ("d3", "marketing", "Dallas")
10>
11> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT DISTINCT emp_fname FROM employee
3> EXCEPT
4> SELECT location FROM department
5> GO
emp_fname
------------------------------
Ann
Elke
Elsa
James
John
Matthew
Sybill
(7 rows affected)
1>
2> drop table employee
3> drop table department
4> GO
1>