Performing Cross Joins Using SQL/92
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 empno Number(3) NOT NULL, -- Employee ID
3 ename VARCHAR2(10 BYTE), -- Employee Name
4 hireDate DATE, -- Date Employee Hired
5 orig_salary Number(8,2), -- Orignal Salary
6 curr_salary Number(8,2), -- Current Salary
7 region VARCHAR2(1 BYTE) -- Region where employeed
8 )
9 /
Table created.
SQL>
SQL> create table job(
2 empno Number(3) NOT NULL, -- Employee ID
3 jobtitle VARCHAR2(10 BYTE) -- Employee job title
4 )
5 /
Table created.
SQL> -- prepare data for employee table
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(122,'Alison',to_date('19960321','YYYYMMDD'), 45000, 48000, 'E')
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(123, 'James',to_date('19781212','YYYYMMDD'), 23000, 32000, 'W')
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(104,'Celia',to_date('19821024','YYYYMMDD'), 53000, 58000, 'E')
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(105,'Robert',to_date('19840115','YYYYMMDD'), 31000, 36000, 'W')
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(116,'Linda', to_date('19870730','YYYYMMDD'), 43000, 53000, 'E')
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(117,'David', to_date('19901231','YYYYMMDD'), 78000, 85000, 'W')
3 /
1 row created.
SQL> insert into Employee(empno, ename, hireDate, orig_salary, curr_salary, region)
2 values(108,'Jode', to_date('19960917','YYYYMMDD'), 21000, 29000, 'E')
3 /
1 row created.
SQL>
SQL> -- prepare data for job table
SQL>
SQL> insert into job(empno, jobtitle)
2 values(101, 'Painter');
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(122, 'Tester');
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(123, 'Dediator');
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(104, 'Chemist');
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(105, 'Accountant');
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(116, 'Manager');
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(117, 'Programmer');
1 row created.
SQL>
SQL> insert into job(empno, jobtitle)
2 values(108, 'Developer');
1 row created.
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R
---------- ---------- --------- ----------- ----------- -
122 Alison 21-MAR-96 45000 48000 E
123 James 12-DEC-78 23000 32000 W
104 Celia 24-OCT-82 53000 58000 E
105 Robert 15-JAN-84 31000 36000 W
116 Linda 30-JUL-87 43000 53000 E
117 David 31-DEC-90 78000 85000 W
108 Jode 17-SEP-96 21000 29000 E
7 rows selected.
SQL>
SQL> select * from job;
EMPNO JOBTITLE
---------- ----------
101 Painter
122 Tester
123 Dediator
104 Chemist
105 Accountant
116 Manager
117 Programmer
108 Developer
8 rows selected.
SQL>
SQL>
SQL>
SQL> --Performing Cross Joins Using SQL/92
SQL>
SQL> SELECT *
2 FROM employee CROSS JOIN job;
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R EMPNO JOBTITLE
---------- ---------- --------- ----------- ----------- - ---------- ----------
122 Alison 21-MAR-96 45000 48000 E 101 Painter
122 Alison 21-MAR-96 45000 48000 E 122 Tester
122 Alison 21-MAR-96 45000 48000 E 123 Dediator
122 Alison 21-MAR-96 45000 48000 E 104 Chemist
122 Alison 21-MAR-96 45000 48000 E 105 Accountant
122 Alison 21-MAR-96 45000 48000 E 116 Manager
122 Alison 21-MAR-96 45000 48000 E 117 Programmer
122 Alison 21-MAR-96 45000 48000 E 108 Developer
123 James 12-DEC-78 23000 32000 W 101 Painter
123 James 12-DEC-78 23000 32000 W 122 Tester
123 James 12-DEC-78 23000 32000 W 123 Dediator
123 James 12-DEC-78 23000 32000 W 104 Chemist
123 James 12-DEC-78 23000 32000 W 105 Accountant
123 James 12-DEC-78 23000 32000 W 116 Manager
123 James 12-DEC-78 23000 32000 W 117 Programmer
123 James 12-DEC-78 23000 32000 W 108 Developer
104 Celia 24-OCT-82 53000 58000 E 101 Painter
104 Celia 24-OCT-82 53000 58000 E 122 Tester
104 Celia 24-OCT-82 53000 58000 E 123 Dediator
104 Celia 24-OCT-82 53000 58000 E 104 Chemist
104 Celia 24-OCT-82 53000 58000 E 105 Accountant
104 Celia 24-OCT-82 53000 58000 E 116 Manager
104 Celia 24-OCT-82 53000 58000 E 117 Programmer
104 Celia 24-OCT-82 53000 58000 E 108 Developer
105 Robert 15-JAN-84 31000 36000 W 101 Painter
105 Robert 15-JAN-84 31000 36000 W 122 Tester
105 Robert 15-JAN-84 31000 36000 W 123 Dediator
105 Robert 15-JAN-84 31000 36000 W 104 Chemist
105 Robert 15-JAN-84 31000 36000 W 105 Accountant
105 Robert 15-JAN-84 31000 36000 W 116 Manager
105 Robert 15-JAN-84 31000 36000 W 117 Programmer
105 Robert 15-JAN-84 31000 36000 W 108 Developer
116 Linda 30-JUL-87 43000 53000 E 101 Painter
116 Linda 30-JUL-87 43000 53000 E 122 Tester
116 Linda 30-JUL-87 43000 53000 E 123 Dediator
116 Linda 30-JUL-87 43000 53000 E 104 Chemist
116 Linda 30-JUL-87 43000 53000 E 105 Accountant
116 Linda 30-JUL-87 43000 53000 E 116 Manager
116 Linda 30-JUL-87 43000 53000 E 117 Programmer
116 Linda 30-JUL-87 43000 53000 E 108 Developer
117 David 31-DEC-90 78000 85000 W 101 Painter
117 David 31-DEC-90 78000 85000 W 122 Tester
117 David 31-DEC-90 78000 85000 W 123 Dediator
117 David 31-DEC-90 78000 85000 W 104 Chemist
117 David 31-DEC-90 78000 85000 W 105 Accountant
117 David 31-DEC-90 78000 85000 W 116 Manager
117 David 31-DEC-90 78000 85000 W 117 Programmer
117 David 31-DEC-90 78000 85000 W 108 Developer
108 Jode 17-SEP-96 21000 29000 E 101 Painter
108 Jode 17-SEP-96 21000 29000 E 122 Tester
108 Jode 17-SEP-96 21000 29000 E 123 Dediator
108 Jode 17-SEP-96 21000 29000 E 104 Chemist
108 Jode 17-SEP-96 21000 29000 E 105 Accountant
108 Jode 17-SEP-96 21000 29000 E 116 Manager
108 Jode 17-SEP-96 21000 29000 E 117 Programmer
108 Jode 17-SEP-96 21000 29000 E 108 Developer
56 rows selected.
SQL>
SQL>
SQL> drop table job;
Table dropped.
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
Related examples in the same category