3>
4> CREATE TABLE Departments(
5> Deptno int NOT NULL CONSTRAINT PK_dept_deptno PRIMARY KEY,
6> deptname varchar(15) NOT NULL
7> )
8> GO
1>
2> CREATE TABLE Jobs(
3> jobid int NOT NULL CONSTRAINT PK_jobs_jobid PRIMARY KEY,
4> jobdesc varchar(15) NOT NULL
5> )
6> GO
1>
2> CREATE TABLE Employees(
3> empid int NOT NULL CONSTRAINT PK_emps_empid PRIMARY KEY,
4> empname varchar(10) NOT NULL,
5> deptno int NULL CONSTRAINT FK_emps_depts REFERENCES Departments(deptno),
6> jobid int NOT NULL CONSTRAINT FK_emps_jobs REFERENCES Jobs(jobid),
7> salary decimal(7,2) NOT NULL
8> )
9> GO
1>
2> INSERT INTO Departments VALUES(100, 'Java2sing')
3> INSERT INTO Departments VALUES(200, 'Production')
4> INSERT INTO Departments VALUES(300, 'Marketing')
5> INSERT INTO Departments VALUES(400, 'Management')
6> INSERT INTO Jobs VALUES(10, 'Java2s')
7> INSERT INTO Jobs VALUES(20, 'Oracle')
8> INSERT INTO Jobs VALUES(30, 'MySQL')
9> INSERT INTO Jobs VALUES(40, 'SqlServer')
10> INSERT INTO Employees VALUES(1, 'Joe', 400, 30, 3456.00)
11> INSERT INTO Employees VALUES(2, 'James', 200, 20, 4325.00)
12> INSERT INTO Employees VALUES(3, 'Chris', 100, 10, 8952.00)
13> INSERT INTO Employees VALUES(4, 'Rob', 400, 30, 1234.00)
14> INSERT INTO Employees VALUES(5, 'Linda', 400, 30, 4567.00)
15> INSERT INTO Employees VALUES(6, 'Lisa', NULL, 30, 8765.00)
16> 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 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT
3> empid,
4> empname,
5> salary,
6> E.deptno,
7> deptname,
8> E.jobid,
9> jobdesc
10> FROM
11> Employees AS E
12> JOIN
13> Departments AS D ON E.deptno = D.deptno
14> JOIN
15> Jobs AS J ON E.jobid = J.jobid
16> OPTION(FORCE ORDER)
17>
18> drop table Employees
19> drop table jobs
20> drop table Departments
21> GO
empid empname salary deptno deptname jobid jobdesc
----------- ---------- --------- ----------- --------------- ----------- ---------------
1 Joe 3456.00 400 Management 30 MySQL
2 James 4325.00 200 Production 20 Oracle
3 Chris 8952.00 100 Java2sing 10 Java2s
4 Rob 1234.00 400 Management 30 MySQL
5 Linda 4567.00 400 Management 30 MySQL
(5 rows affected)
1>