1>
2> CREATE TABLE works_on (emp_no INTEGER NOT NULL,
3> project_no CHAR(4) NOT NULL,
4> job CHAR (15) NULL,
5> enter_date DATETIME NULL)
6>
7> insert into works_on values (1, 'p1', 'analyst', '1997.10.1')
8> insert into works_on values (1, 'p3', 'manager', '1999.1.1')
9> insert into works_on values (2, 'p2', 'clerk', '1998.2.15')
10> insert into works_on values (2, 'p2', NULL, '1998.6.1')
11> insert into works_on values (3, 'p2', NULL, '1997.12.15')
12> insert into works_on values (4, 'p3', 'analyst', '1998.10.15')
13> insert into works_on values (5, 'p1', 'manager', '1998.4.15')
14> insert into works_on values (6, 'p1', NULL, '1998.8.1')
15> insert into works_on values (7, 'p2', 'clerk', '1999.2.1')
16> insert into works_on values (8, 'p3', 'clerk', '1997.11.15')
17> insert into works_on values (7, 'p1', 'clerk', '1998.1.4')
18> 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)
(1 rows affected)
1>
2> -- GROUP BY builds different groups for all possible values (NULL, too!)
3>
4> SELECT project_no, job FROM works_on GROUP BY project_no, job
5> GO
project_no job
---------- ---------------
p1 NULL
p1 analyst
p1 clerk
p1 manager
p2 NULL
p2 clerk
p3 analyst
p3 clerk
p3 manager
(9 rows affected)
1>
2> drop table works_on
3> GO
1>