It is possible to make an outer join with partitions in Oracle. Suppose that you have a table that contains workers' work days. If a worker did not work, there is no record in that table, working_time table. If your boss want you to prepare a sheet that contains all workers' working times. You have to put all day entries. If a worker did not work, you simple add "0" working hours for worker. How can you achieve this situation?
Follow the example....
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> DROP TABLE working_time;
DROP TABLE working_time
ORA-00942: table or view does not exist
SQL> DROP TABLE worker;
DROP TABLE worker
ORA-00942: table or view does not exist
SQL> CREATE TABLE worker( id number primary key, name varchar2(32), salary number);
Table created
SQL> INSERT INTO worker VALUES (1, 'Mennan', 1200);
1 row inserted
SQL> INSERT INTO worker VALUES (2, 'Ali', 1500);
1 row inserted
SQL> INSERT INTO worker VALUES (3, 'Selim', 900);
1 row inserted
SQL> INSERT INTO worker VALUES (4, 'Ayse', 1450);
1 row inserted
SQL> INSERT INTO worker VALUES (5, 'Seyyah', 2900);
1 row inserted
SQL> commit;
Commit complete
SQL> SELECT * FROM worker;
ID NAME SALARY
---------- -------------------------------- ----------
1 Mennan 1200
2 Ali 1500
3 Selim 900
4 Ayse 1450
5 Seyyah 2900
SQL> CREATE TABLE working_time( worker_id number references worker, working_date date, working_hour number );
Table created
SQL> INSERT INTO working_time VALUES(1, to_date('01.11.2006','DD.MM.YYYY'), 4);
1 row inserted
SQL> INSERT INTO working_time VALUES(1, to_date('02.11.2006','DD.MM.YYYY'), 8);
1 row inserted
SQL> INSERT INTO working_time VALUES(1, to_date('05.11.2006','DD.MM.YYYY'), 8);
1 row inserted
SQL> INSERT INTO working_time VALUES(5, to_date('01.11.2006','DD.MM.YYYY'), 8);
1 row inserted
SQL> INSERT INTO working_time VALUES(5, to_date('02.11.2006','DD.MM.YYYY'), 8);
1 row inserted
SQL> INSERT INTO working_time VALUES(5, to_date('03.11.2006','DD.MM.YYYY'), 8);
1 row inserted
SQL> INSERT INTO working_time VALUES(5, to_date('04.11.2006','DD.MM.YYYY'), 6);
1 row inserted
SQL> INSERT INTO working_time VALUES(3, to_date('02.11.2006','DD.MM.YYYY'), 6);
1 row inserted
SQL> INSERT INTO working_time VALUES(3, to_date('05.11.2006','DD.MM.YYYY'), 8);
1 row inserted
SQL> commit;
Commit complete
SQL> SELECT * FROM working_time;
WORKER_ID WORKING_DATE WORKING_HOUR
---------- ------------ ------------
1 01.11.2006 4
1 02.11.2006 8
1 05.11.2006 8
5 01.11.2006 8
5 02.11.2006 8
5 03.11.2006 8
5 04.11.2006 6
3 02.11.2006 6
3 05.11.2006 8
9 rows selected
SQL> SELECT * FROM worker;
ID NAME SALARY
---------- -------------------------------- ----------
1 Mennan 1200
2 Ali 1500
3 Selim 900
4 Ayse 1450
5 Seyyah 2900
SQL> SELECT wr.name, wt.working_date, wt.working_hour FROM working_time wt, worker wr WHERE wt.worker_id = wr.id;
NAME WORKING_DATE WORKING_HOUR
-------------------------------- ------------ ------------
Mennan 01.11.2006 4
Mennan 02.11.2006 8
Mennan 05.11.2006 8
Seyyah 01.11.2006 8
Seyyah 02.11.2006 8
Seyyah 03.11.2006 8
Seyyah 04.11.2006 6
Selim 02.11.2006 6
Selim 05.11.2006 8
9 rows selected
SQL> SELECT inn.NAME, tim.time_day, nvl(inn.working_hour, 0) working_hour
2 FROM (SELECT wr.NAME, wt.working_date, wt.working_hour
3 FROM working_time wt, worker wr
4 WHERE wt.worker_id = wr.id) inn PARTITION BY(inn.NAME)
5 RIGHT OUTER JOIN (SELECT to_date('31.10.2006', 'DD.MM.YYYY') + rownum time_day
6 FROM all_objects
7 WHERE rownum <=
8 (SYSDATE - to_date('31.10.2006', 'DD.MM.YYYY'))) tim ON (inn.working_date =
9 tim.time_day)
10 ORDER BY inn.NAME, tim.time_day;
NAME TIME_DAY WORKING_HOUR
-------------------------------- ----------- ------------
Mennan 01.11.2006 4
Mennan 02.11.2006 8
Mennan 03.11.2006 0
Mennan 04.11.2006 0
Mennan 05.11.2006 8
Mennan 06.11.2006 0
Mennan 07.11.2006 0
Mennan 08.11.2006 0
Mennan 09.11.2006 0
Mennan 10.11.2006 0
Mennan 11.11.2006 0
Mennan 12.11.2006 0
Mennan 13.11.2006 0
Mennan 14.11.2006 0
Selim 01.11.2006 0
Selim 02.11.2006 6
Selim 03.11.2006 0
Selim 04.11.2006 0
Selim 05.11.2006 8
Selim 06.11.2006 0
Selim 07.11.2006 0
Selim 08.11.2006 0
Selim 09.11.2006 0
Selim 10.11.2006 0
Selim 11.11.2006 0
Selim 12.11.2006 0
Selim 13.11.2006 0
Selim 14.11.2006 0
Seyyah 01.11.2006 8
Seyyah 02.11.2006 8
Seyyah 03.11.2006 8
Seyyah 04.11.2006 6
Seyyah 05.11.2006 0
Seyyah 06.11.2006 0
Seyyah 07.11.2006 0
Seyyah 08.11.2006 0
Seyyah 09.11.2006 0
Seyyah 10.11.2006 0
Seyyah 11.11.2006 0
Seyyah 12.11.2006 0
Seyyah 13.11.2006 0
NAME TIME_DAY WORKING_HOUR
-------------------------------- ----------- ------------
Seyyah 14.11.2006 0
42 rows selected
SQL> DROP TABLE working_time;
Table dropped
SQL> DROP TABLE worker;
Table dropped
SQL>
Showing posts with label join. Show all posts
Showing posts with label join. Show all posts
14 November 2006
19 October 2006
LEFT OUTER JOIN and NULL Values
I have mentioned some join operations on my previous post. LEFT OUTER JOIN takes all records of "left" table; so some of records of "right" table can stay NULL. Suppose you need to make some calculations on that NULL fields. Equality operations can not work on NULL values. NULL values are special values. If you add a condition on that field like = or <= it will skip all NULL values which are taken by OUTER JOIN. For clarity follow the example:
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> drop table eq_h_test;
Table dropped
SQL> create table eq_h_test( i number, z number );
Table created
SQL> INSERT INTO eq_h_test VALUES(1, 100 );
1 row inserted
SQL> INSERT INTO eq_h_test VALUES(1, 103 );
1 row inserted
SQL> INSERT INTO eq_h_test VALUES(1, 109 );
1 row inserted
SQL> INSERT INTO eq_h_test VALUES(2, 10 );
1 row inserted
SQL> INSERT INTO eq_h_test VALUES(2, 1020 );
1 row inserted
SQL> INSERT INTO eq_h_test VALUES(3, 1700 );
1 row inserted
SQL> drop table eq_test;
Table dropped
SQL> create table eq_test( i number, k varchar2(3) );
Table created
SQL> INSERT INTO eq_test VALUES( 1, 'aaa' );
1 row inserted
SQL> INSERT INTO eq_test VALUES( 2, 'fff' );
1 row inserted
SQL> INSERT INTO eq_test VALUES( 3, 'rrr' );
1 row inserted
SQL> INSERT INTO eq_test VALUES( 4, 'ttt' );
1 row inserted
SQL> INSERT INTO eq_test VALUES( 5, 'qqq' );
1 row inserted
SQL> commit;
Commit complete
Let's selecting and operating. Aggreagating functions return NULL values even if they have no record.(For more please read Aggregation Functions and NO_DATA_FOUND Exception In Oracle) I use the join field in where clause for equality. I can not get NULL values and can not get power of OUTER JOIN. So i use an extra clause that contains special equality for NULL values.
SQL> SELECT * FROM eq_test t LEFT OUTER JOIN eq_h_test h ON t.i = h.i;
I K I Z
---------- --- ---------- ----------
1 aaa 1 100
1 aaa 1 103
1 aaa 1 109
2 fff 2 10
2 fff 2 1020
3 rrr 3 1700
5 qqq
4 ttt
8 rows selected
SQL> SELECT *
2 FROM eq_test t
3 LEFT OUTER JOIN eq_h_test h ON t.i = h.i
4 WHERE h.z = (SELECT MAX(h2.z) FROM eq_h_test h2 WHERE h2.i = h.i);
I K I Z
---------- --- ---------- ----------
1 aaa 1 109
2 fff 2 1020
3 rrr 3 1700
SQL> SELECT *
2 FROM eq_test t
3 LEFT OUTER JOIN eq_h_test h ON t.i = h.i
4 WHERE decode(h.z,
5 (SELECT MAX(h2.z) FROM eq_h_test h2 WHERE h2.i = h.i),
6 1,
7 0) = 1;
I K I Z
---------- --- ---------- ----------
1 aaa 1 109
2 fff 2 1020
3 rrr 3 1700
5 qqq
4 ttt
SQL> SELECT *
2 FROM eq_test t
3 LEFT OUTER JOIN eq_h_test h ON t.i = h.i
4 WHERE h.z = (SELECT MAX(h2.z) FROM eq_h_test h2 WHERE h2.i = h.i)
5 OR h.z IS NULL;
I K I Z
---------- --- ---------- ----------
1 aaa 1 109
2 fff 2 1020
3 rrr 3 1700
5 qqq
4 ttt
SQL> ---
You must keep in mind NULL values are special. You must handle them. An alternative way, you can use DECODE for equality of NULLs. I wrote a simple example to show this. You can also get more information in my On NULL Values post.
SQL>
SQL> SELECT decode(NULL, NULL, 'null is null', 'null is not null') FROM dual;
DECODE(NULL,NULL,'NULLISNULL',
------------------------------
null is null
SQL> --
SQL> SELECT dummy FROM dual WHERE 1 = 1;
DUMMY
-----
X
SQL> SELECT dummy FROM dual WHERE NULL = NULL;
DUMMY
-----
SQL> SELECT dummy FROM dual WHERE NULL IS NULL;
DUMMY
-----
X
SQL>
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> drop table eq_h_test;
Table dropped
SQL> create table eq_h_test( i number, z number );
Table created
SQL> INSERT INTO eq_h_test VALUES(1, 100 );
1 row inserted
SQL> INSERT INTO eq_h_test VALUES(1, 103 );
1 row inserted
SQL> INSERT INTO eq_h_test VALUES(1, 109 );
1 row inserted
SQL> INSERT INTO eq_h_test VALUES(2, 10 );
1 row inserted
SQL> INSERT INTO eq_h_test VALUES(2, 1020 );
1 row inserted
SQL> INSERT INTO eq_h_test VALUES(3, 1700 );
1 row inserted
SQL> drop table eq_test;
Table dropped
SQL> create table eq_test( i number, k varchar2(3) );
Table created
SQL> INSERT INTO eq_test VALUES( 1, 'aaa' );
1 row inserted
SQL> INSERT INTO eq_test VALUES( 2, 'fff' );
1 row inserted
SQL> INSERT INTO eq_test VALUES( 3, 'rrr' );
1 row inserted
SQL> INSERT INTO eq_test VALUES( 4, 'ttt' );
1 row inserted
SQL> INSERT INTO eq_test VALUES( 5, 'qqq' );
1 row inserted
SQL> commit;
Commit complete
Let's selecting and operating. Aggreagating functions return NULL values even if they have no record.(For more please read Aggregation Functions and NO_DATA_FOUND Exception In Oracle) I use the join field in where clause for equality. I can not get NULL values and can not get power of OUTER JOIN. So i use an extra clause that contains special equality for NULL values.
SQL> SELECT * FROM eq_test t LEFT OUTER JOIN eq_h_test h ON t.i = h.i;
I K I Z
---------- --- ---------- ----------
1 aaa 1 100
1 aaa 1 103
1 aaa 1 109
2 fff 2 10
2 fff 2 1020
3 rrr 3 1700
5 qqq
4 ttt
8 rows selected
SQL> SELECT *
2 FROM eq_test t
3 LEFT OUTER JOIN eq_h_test h ON t.i = h.i
4 WHERE h.z = (SELECT MAX(h2.z) FROM eq_h_test h2 WHERE h2.i = h.i);
I K I Z
---------- --- ---------- ----------
1 aaa 1 109
2 fff 2 1020
3 rrr 3 1700
SQL> SELECT *
2 FROM eq_test t
3 LEFT OUTER JOIN eq_h_test h ON t.i = h.i
4 WHERE decode(h.z,
5 (SELECT MAX(h2.z) FROM eq_h_test h2 WHERE h2.i = h.i),
6 1,
7 0) = 1;
I K I Z
---------- --- ---------- ----------
1 aaa 1 109
2 fff 2 1020
3 rrr 3 1700
5 qqq
4 ttt
SQL> SELECT *
2 FROM eq_test t
3 LEFT OUTER JOIN eq_h_test h ON t.i = h.i
4 WHERE h.z = (SELECT MAX(h2.z) FROM eq_h_test h2 WHERE h2.i = h.i)
5 OR h.z IS NULL;
I K I Z
---------- --- ---------- ----------
1 aaa 1 109
2 fff 2 1020
3 rrr 3 1700
5 qqq
4 ttt
SQL> ---
You must keep in mind NULL values are special. You must handle them. An alternative way, you can use DECODE for equality of NULLs. I wrote a simple example to show this. You can also get more information in my On NULL Values post.
SQL>
SQL> SELECT decode(NULL, NULL, 'null is null', 'null is not null') FROM dual;
DECODE(NULL,NULL,'NULLISNULL',
------------------------------
null is null
SQL> --
SQL> SELECT dummy FROM dual WHERE 1 = 1;
DUMMY
-----
X
SQL> SELECT dummy FROM dual WHERE NULL = NULL;
DUMMY
-----
SQL> SELECT dummy FROM dual WHERE NULL IS NULL;
DUMMY
-----
X
SQL>
How To Join Tables In Oracle With LEFT RIGHT OUTER INNER FULL Keywords
In relational database systems, information is stored in multiple tables. You joins multiple tables to get information. Standart SQL, has defined join to combine multiple set based on desired attributes.
I simply demonstrate a complete example to show how to joins take place in Oracle.
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> drop table student;
Table dropped
SQL> drop table project;
Table dropped
SQL> drop table course;
Table dropped
SQL> create table student(student_id number, taken_project_id number, given_course_id number, last_name varchar2(32) );
Table created
SQL> create table project(project_id number, project_description varchar2(32));
Table created
SQL> create table course(course_id number, course_description varchar2(32));
Table created
SQL> INSERT INTO course values(1, 'Database Management Sys');
1 row inserted
SQL> INSERT INTO course values(2, 'Logic');
1 row inserted
SQL> INSERT INTO course values(3, 'Circuit Theory');
1 row inserted
SQL> INSERT INTO project values(100, 'Reservation System');
1 row inserted
SQL> INSERT INTO project values(101, 'Online Booking');
1 row inserted
SQL> INSERT INTO project values(102, 'Face Recog. with brute force');
1 row inserted
SQL> INSERT INTO project values(103, 'On Fly Form Generator');
1 row inserted
SQL> INSERT INTO student values(60001, null, null, 'Alonso');
1 row inserted
SQL> INSERT INTO student values(60002, 102, 1, 'Richar');
1 row inserted
SQL> INSERT INTO student values(60001, 100, 2, 'Tekbir');
1 row inserted
SQL> INSERT INTO student values(60001, null, 1, 'Sergio');
1 row inserted
SQL> INSERT INTO student values(60001, 101, null, 'Alexov');
1 row inserted
SQL> commit;
Commit complete
SQL> SELECT * FROM student;
STUDENT_ID TAKEN_PROJECT_ID GIVEN_COURSE_ID LAST_NAME
---------- ---------------- --------------- --------------------------------
60001 Alonso
60002 102 1 Richar
60001 100 2 Tekbir
60001 1 Sergio
60001 101 Alexov
SQL> SELECT * FROM project;
PROJECT_ID PROJECT_DESCRIPTION
---------- --------------------------------
100 Reservation System
101 Online Booking
102 Face Recog. with brute force
103 On Fly Form Generator
SQL> SELECT * FROM course;
COURSE_ID COURSE_DESCRIPTION
---------- --------------------------------
1 Database Management Sys
2 Logic
3 Circuit Theory
SQL> SELECT st.last_name, pr.project_description FROM student st, project pr WHERE st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Richar Face Recog. with brute force
Tekbir Reservation System
Alexov Online Booking
SQL> SELECT st.last_name, pr.project_description FROM student st INNER JOIN project pr ON st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Richar Face Recog. with brute force
Tekbir Reservation System
Alexov Online Booking
SQL> SELECT st.last_name, pr.project_description FROM student st, project pr WHERE st.taken_project_id = pr.project_id(+);
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Tekbir Reservation System
Alexov Online Booking
Richar Face Recog. with brute force
Sergio
Alonso
SQL> SELECT st.last_name, pr.project_description FROM student st LEFT OUTER JOIN project pr ON st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Tekbir Reservation System
Alexov Online Booking
Richar Face Recog. with brute force
Sergio
Alonso
SQL> SELECT st.last_name, pr.project_description FROM student st, project pr WHERE st.taken_project_id(+) = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Richar Face Recog. with brute force
Tekbir Reservation System
Alexov Online Booking
On Fly Form Generator
SQL> SELECT st.last_name, pr.project_description FROM student st RIGHT OUTER JOIN project pr ON st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Richar Face Recog. with brute force
Tekbir Reservation System
Alexov Online Booking
On Fly Form Generator
SQL> --SELECT st.last_name, pr.project_description FROM student st, project pr WHERE st.taken_project_id(+) = pr.project_id(+);--ERROR
SQL> SELECT st.last_name, pr.project_description FROM student st FULL OUTER JOIN project pr ON st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Tekbir Reservation System
Alexov Online Booking
Richar Face Recog. with brute force
Sergio
Alonso
On Fly Form Generator
6 rows selected
SQL> SELECT st.last_name, pr.project_description, co.course_description FROM student st, project pr, course co WHERE st.taken_project_id = pr.project_id AND st.given_course_id = co.course_id;
LAST_NAME PROJECT_DESCRIPTION COURSE_DESCRIPTION
-------------------------------- -------------------------------- --------------------------------
Richar Face Recog. with brute force Database Management Sys
Tekbir Reservation System Logic
SQL> SELECT st.last_name, pr.project_description, co.course_description FROM student st INNER JOIN project pr ON st.taken_project_id = pr.project_id INNER JOIN course co ON st.given_course_id = co.course_id;
LAST_NAME PROJECT_DESCRIPTION COURSE_DESCRIPTION
-------------------------------- -------------------------------- --------------------------------
Tekbir Reservation System Logic
Richar Face Recog. with brute force Database Management Sys
SQL> SELECT st.last_name, pr.project_description, co.course_description FROM student st FULL OUTER JOIN project pr ON st.taken_project_id = pr.project_id FULL OUTER JOIN course co ON st.given_course_id = co.course_id;
LAST_NAME PROJECT_DESCRIPTION COURSE_DESCRIPTION
-------------------------------- -------------------------------- --------------------------------
Sergio Database Management Sys
Richar Face Recog. with brute force Database Management Sys
Tekbir Reservation System Logic
On Fly Form Generator
Alonso
Alexov Online Booking
Circuit Theory
7 rows selected
SQL>
I simply demonstrate a complete example to show how to joins take place in Oracle.
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> drop table student;
Table dropped
SQL> drop table project;
Table dropped
SQL> drop table course;
Table dropped
SQL> create table student(student_id number, taken_project_id number, given_course_id number, last_name varchar2(32) );
Table created
SQL> create table project(project_id number, project_description varchar2(32));
Table created
SQL> create table course(course_id number, course_description varchar2(32));
Table created
SQL> INSERT INTO course values(1, 'Database Management Sys');
1 row inserted
SQL> INSERT INTO course values(2, 'Logic');
1 row inserted
SQL> INSERT INTO course values(3, 'Circuit Theory');
1 row inserted
SQL> INSERT INTO project values(100, 'Reservation System');
1 row inserted
SQL> INSERT INTO project values(101, 'Online Booking');
1 row inserted
SQL> INSERT INTO project values(102, 'Face Recog. with brute force');
1 row inserted
SQL> INSERT INTO project values(103, 'On Fly Form Generator');
1 row inserted
SQL> INSERT INTO student values(60001, null, null, 'Alonso');
1 row inserted
SQL> INSERT INTO student values(60002, 102, 1, 'Richar');
1 row inserted
SQL> INSERT INTO student values(60001, 100, 2, 'Tekbir');
1 row inserted
SQL> INSERT INTO student values(60001, null, 1, 'Sergio');
1 row inserted
SQL> INSERT INTO student values(60001, 101, null, 'Alexov');
1 row inserted
SQL> commit;
Commit complete
SQL> SELECT * FROM student;
STUDENT_ID TAKEN_PROJECT_ID GIVEN_COURSE_ID LAST_NAME
---------- ---------------- --------------- --------------------------------
60001 Alonso
60002 102 1 Richar
60001 100 2 Tekbir
60001 1 Sergio
60001 101 Alexov
SQL> SELECT * FROM project;
PROJECT_ID PROJECT_DESCRIPTION
---------- --------------------------------
100 Reservation System
101 Online Booking
102 Face Recog. with brute force
103 On Fly Form Generator
SQL> SELECT * FROM course;
COURSE_ID COURSE_DESCRIPTION
---------- --------------------------------
1 Database Management Sys
2 Logic
3 Circuit Theory
SQL> SELECT st.last_name, pr.project_description FROM student st, project pr WHERE st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Richar Face Recog. with brute force
Tekbir Reservation System
Alexov Online Booking
SQL> SELECT st.last_name, pr.project_description FROM student st INNER JOIN project pr ON st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Richar Face Recog. with brute force
Tekbir Reservation System
Alexov Online Booking
SQL> SELECT st.last_name, pr.project_description FROM student st, project pr WHERE st.taken_project_id = pr.project_id(+);
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Tekbir Reservation System
Alexov Online Booking
Richar Face Recog. with brute force
Sergio
Alonso
SQL> SELECT st.last_name, pr.project_description FROM student st LEFT OUTER JOIN project pr ON st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Tekbir Reservation System
Alexov Online Booking
Richar Face Recog. with brute force
Sergio
Alonso
SQL> SELECT st.last_name, pr.project_description FROM student st, project pr WHERE st.taken_project_id(+) = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Richar Face Recog. with brute force
Tekbir Reservation System
Alexov Online Booking
On Fly Form Generator
SQL> SELECT st.last_name, pr.project_description FROM student st RIGHT OUTER JOIN project pr ON st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Richar Face Recog. with brute force
Tekbir Reservation System
Alexov Online Booking
On Fly Form Generator
SQL> --SELECT st.last_name, pr.project_description FROM student st, project pr WHERE st.taken_project_id(+) = pr.project_id(+);--ERROR
SQL> SELECT st.last_name, pr.project_description FROM student st FULL OUTER JOIN project pr ON st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Tekbir Reservation System
Alexov Online Booking
Richar Face Recog. with brute force
Sergio
Alonso
On Fly Form Generator
6 rows selected
SQL> SELECT st.last_name, pr.project_description, co.course_description FROM student st, project pr, course co WHERE st.taken_project_id = pr.project_id AND st.given_course_id = co.course_id;
LAST_NAME PROJECT_DESCRIPTION COURSE_DESCRIPTION
-------------------------------- -------------------------------- --------------------------------
Richar Face Recog. with brute force Database Management Sys
Tekbir Reservation System Logic
SQL> SELECT st.last_name, pr.project_description, co.course_description FROM student st INNER JOIN project pr ON st.taken_project_id = pr.project_id INNER JOIN course co ON st.given_course_id = co.course_id;
LAST_NAME PROJECT_DESCRIPTION COURSE_DESCRIPTION
-------------------------------- -------------------------------- --------------------------------
Tekbir Reservation System Logic
Richar Face Recog. with brute force Database Management Sys
SQL> SELECT st.last_name, pr.project_description, co.course_description FROM student st FULL OUTER JOIN project pr ON st.taken_project_id = pr.project_id FULL OUTER JOIN course co ON st.given_course_id = co.course_id;
LAST_NAME PROJECT_DESCRIPTION COURSE_DESCRIPTION
-------------------------------- -------------------------------- --------------------------------
Sergio Database Management Sys
Richar Face Recog. with brute force Database Management Sys
Tekbir Reservation System Logic
On Fly Form Generator
Alonso
Alexov Online Booking
Circuit Theory
7 rows selected
SQL>
Subscribe to:
Posts (Atom)