|
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,
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>
3>
4> SELECT
5> empid,
6> empname,
7> salary,
8> E.deptno,
9> deptname,
10> E.jobid,
11> jobdesc
12> FROM
13> Employees AS E
14> JOIN
15> Departments AS D ON E.deptno = D.deptno
16> JOIN
17> Jobs AS J ON E.jobid = J.jobid
18>
19> drop table Employees
20> drop table jobs
21> drop table Departments
22> 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)
|