|
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
Msg 2714, Level 16, State 6, Server JAVA2S\SQLEXPRESS, Line 2
There is already an object named 'works_on' in the database.
1>
2>
3>
4> -- ANY evaluates to true if the result of an inner query contains at least one row that satisfies the comparison
5>
6> SELECT DISTINCT emp_no, project_no, job FROM works_on WHERE enter_date > ANY
7> (SELECT enter_date FROM works_on)
8> GO
emp_no project_no job
----------- ---------- ---------------
1 p3 manager
2 p2 NULL
2 p2 clerk
3 p2 NULL
4 p3 analyst
5 p1 manager
6 p1 NULL
7 p1 clerk
7 p2 clerk
8 p3 clerk
(10 rows affected)
1>
2> drop table works_on
3> GO
1>
2>
|