36>
37> CREATE TABLE works_on (emp_no INTEGER NOT NULL,
38> project_no CHAR(4) NOT NULL,
39> job CHAR (15) NULL,
40> enter_date DATETIME NULL)
41>
42> insert into works_on values (1, "p1", "analyst", "1997.10.1")
43> insert into works_on values (1, "p3", "manager", "1999.1.1")
44> insert into works_on values (2, "p2", "clerk", "1998.2.15")
45> insert into works_on values (2, "p2", NULL, "1998.6.1")
46> insert into works_on values (3, "p2", NULL, "1997.12.15")
47> insert into works_on values (4, "p3", "analyst", "1998.10.15")
48> insert into works_on values (5, "p1", "manager", "1998.4.15")
49> insert into works_on values (6, "p1", NULL, "1998.8.1")
50> insert into works_on values (7, "p2", "clerk", "1999.2.1")
51> insert into works_on values (8, "p3", "clerk", "1997.11.15")
52> insert into works_on values (7, "p1", "clerk", "1998.1.4")
53>
54>
55> SELECT project_no, COUNT(*) emp_quantity
56> FROM works_on
57> GROUP BY project_no
58> ORDER BY 2 DESC
59> 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)
project_no emp_quantity
---------- ------------
p1 4
p2 4
p3 3
(3 rows affected)
1>
2> drop table works_on
3> GO
1>
