|
19>
20> CREATE TABLE works_on (emp_no INTEGER NOT NULL,
21> project_no CHAR(4) NOT NULL,
22> job CHAR (15) NULL,
23> enter_date DATETIME NULL)
24>
25> insert into works_on values (1, 'p1', 'analyst', '1997.10.1')
26> insert into works_on values (1, 'p3', 'manager', '1999.1.1')
27> insert into works_on values (2, 'p2', 'clerk', '1998.2.15')
28> insert into works_on values (2, 'p2', NULL, '1998.6.1')
29> insert into works_on values (3, 'p2', NULL, '1997.12.15')
30> insert into works_on values (4, 'p3', 'analyst', '1998.10.15')
31> insert into works_on values (5, 'p1', 'manager', '1998.4.15')
32> insert into works_on values (6, 'p1', NULL, '1998.8.1')
33> insert into works_on values (7, 'p2', 'clerk', '1999.2.1')
34> insert into works_on values (8, 'p3', 'clerk', '1997.11.15')
35> insert into works_on values (7, 'p1', 'clerk', '1998.1.4')
36>
37> select * from works_on
38> GO
Msg 2714, Level 16, State 6, Server JAVA2S\SQLEXPRESS, Line 20
There is already an object named 'works_on' in the database.
1> CREATE TABLE employee (emp_no INTEGER NOT NULL,
2> emp_fname CHAR(20) NOT NULL,
3> emp_lname CHAR(20) NOT NULL,
4> dept_no CHAR(4) NULL)
5>
6> insert into employee values(1, 'Matthew', 'Smith', 'd3')
7> insert into employee values(2, 'Ann', 'Jones', 'd3')
8> insert into employee values(3, 'John', 'Barrimore','d1')
9> insert into employee values(4, 'James', 'James', 'd2')
10> insert into employee values(5, 'Elsa', 'Bertoni', 'd2')
11> insert into employee values(6, 'Elke', 'Hansel', 'd2')
12> insert into employee values(7, 'Sybill', 'Moser', 'd1')
13>
14> select * from employee
15> GO
Msg 2714, Level 16, State 6, Server JAVA2S\SQLEXPRESS, Line 1
There is already an object named 'employee' in the database.
1>
2> --The FORCE ORDER hint forces the optimizer to join tables in the order in which
3> --they are specified in a query
4>
5> SET SHOWPLAN_TEXT ON
6> GO
1> select emp_lname, emp_fname
2> from works_on JOIN employee
3> ON employee.emp_no = works_on.emp_no
4> WHERE works_on.job = 'Analyst'
5> option (force order)
6> GO
StmtText
------------------------------------------------------------------------------------------------------------------------
---------------------------
select emp_lname, emp_fname
from works_on JOIN employee
ON employee.emp_no = works_on.emp_no
WHERE works_on.job = 'Analyst'
option (force order)
(1 rows affected)
StmtText
------------------------------------------------------------------------------------------------------------
|--Hash Match(Inner Join, HASH:([master].[dbo].[works_on].[emp_no])=([master].[dbo].[employee].[emp_no]))
|--Table Scan(OBJECT:([master].[dbo].[works_on]), WHERE:([master].[dbo].[works_on].[job]='Analyst'))
|--Table Scan(OBJECT:([master].[dbo].[employee]))
(3 rows affected)
1> SET SHOWPLAN_TEXT OFF
2> GO
1> drop table employee
2> drop table works_on
3> GO
1>
2>
|