Mega Code Archive

Performing Cross Joins Using SQL92

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>