Software Programming: SQL TUTORIAL

Saturday 27 April 2024

How To Retrieve Data Using FLASHBACK Command In Oracle.

This command undoes changes made by Oracle Database to the data files that exist when you run the command. FLASHBACK can fix logical failures, but not physical failures
FLASHBACK DATABASE is usually much faster than a RESTORE operation followed by point-in-time recovery because no data files are restored. The time needed to perform FLASHBACK DATABASE depends on the number of changes made to the database since the desired flashback time. On the other hand, the time needed to do a traditional point-in-time recovery from restored backups depends on the size of the database.
For Example :

FLASHBACK TABLE TABLENAME to TIMESTAMP(SYSDATE-1);

Note: If Flashback is not working on SQL Tools/Oracle DataTable you can First run the command given below and then run the flashback command.

ALTER TABLE TABLENAME ENABLE ROW MOVEMENT;

Tuesday 10 October 2017

SQL JOINS

October 10, 2017 SQL , SQL JOIN , SQL TUTORIAL 1 comment :

Type Of SQL Join:

(INNER) JOIN: Returns records that have matching values in both tables.
LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table.
RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table.
FULL (OUTER) JOIN: Return all records when there is a match in either left or right table.

The General syntax is

SELECT column - name FROM table-name1 JOIN table-name2 ON common_name1 = common_name2 where condition;

Example of SQL Join's

(INNER) JOIN: The most important and frequently used of the joins is the INNER JOIN. They are also referred to as an EQUIJOIN.

SELECT table1.column1, table2.column2...FROM table1 INNER JOIN table2 ON table1.common_field = table2.common_field;

Consider the following two tables.

Table 1 − CUSTOMERS Table is as follows.

+---+---------+-----+--------+----------+

|ID | NAME   | AGE | ADDRESS| SALARY |

+---+---------+-----+--------+----------+

| 1 | Manoj   | 27 | Delhi | 2000.00 |

| 2 | Kumar   | 25 | Bhopal | 1500.00 |

| 3 | Rajesh | 23 | Kota   | 2000.00 |

+---+---------+-----+--------+----------+

Table 2 − ORDERS Table is as follows.

+-----+---------------------+-------------+--------+

| OID | DATE                | CUSTOMER_ID | AMOUNT |

+-----+---------------------+-------------+--------+

| 102 | 2017-10-08 00:00:00 |           1 |   3000 |

| 100 | 2017-10-08 00:00:00 |           1 |   1500 |

| 101 | 2017-11-20 00:00:00 |           2 |   1560 |

+-----+---------------------+-------------+--------+

These two tables using the INNER JOIN as follows

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Output:

+----+----------+--------+---------------------+

| ID | NAME     | AMOUNT | DATE                |

+----+----------+--------+---------------------+

| 1 | Manoj   |   3000 | 2017-10-08 00:00:00 |

| 1 | Manoj   |   1500 | 2017-10-08 00:00:00 |

| 2 | Kumar   |   1560 | 2017-11-20 00:00:00 |

+----+----------+--------+---------------------+
LEFT (OUTER) JOIN: The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

SELECT table1.column1, table2.column2...FROM table1 LEFT JOIN table2 ON table1.common_field = table2.common_field;

Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.

Consider the following two tables.

Table 1 − CUSTOMERS Table is as follows.

+---+---------+-----+--------+----------+

|ID | NAME   | AGE | ADDRESS| SALARY |

+---+---------+-----+--------+----------+

| 1 | Manoj   | 27 | Delhi | 2000.00 |

| 2 | Kumar   | 25 | Bhopal | 1500.00 |

| 3 | Rajesh | 23 | Kota   | 2000.00 |

+---+---------+-----+--------+----------+

Table 2 − ORDERS Table is as follows.

+-----+---------------------+-------------+--------+

| OID | DATE                | CUSTOMER_ID | AMOUNT |

+-----+---------------------+-------------+--------+

| 102 | 2017-10-08 00:00:00 |           1 |   3000 |

| 100 | 2017-10-08 00:00:00 |           1 |   1500 |

| 101 | 2017-11-20 00:00:00 |           2 |   1560 |

+-----+---------------------+-------------+--------+

These two tables using the LEFT JOIN as follows

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Output:

Note: The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).

+----+----------+--------+---------------------+

| ID | NAME     | AMOUNT | DATE                |

+----+----------+--------+---------------------+

| 1 | Manoj   |   3000 | 2017-10-08 00:00:00 |

| 1 | Manoj   |   1500 | 2017-10-08 00:00:00 |

| 2 | Kumar   |   1560 | 2017-11-20 00:00:00 |

| 3 | Rajesh |   Null | Null                |

+----+----------+--------+---------------------+
RIGHT (OUTER) JOIN: The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.

SELECT table1.column1, table2.column2...FROM table1 RIGHT JOIN table2 ON table1.common_field = table2.common_field;

Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

Consider the following two tables.

Table 1 − CUSTOMERS Table is as follows.

+---+---------+-----+--------+----------+

|ID | NAME   | AGE | ADDRESS| SALARY |

+---+---------+-----+--------+----------+

| 1 | Manoj   | 27 | Delhi | 2000.00 |

| 2 | Kumar   | 25 | Bhopal | 1500.00 |

| 3 | Rajesh | 23 | Kota   | 2000.00 |

+---+---------+-----+--------+----------+

Table 2 − ORDERS Table is as follows.

+-----+---------------------+-------------+--------+

| OID | DATE                | CUSTOMER_ID | AMOUNT |

+-----+---------------------+-------------+--------+

| 102 | 2017-10-08 00:00:00 |           1 |   3000 |

| 100 | 2017-10-08 00:00:00 |           1 |   1500 |

| 101 | 2017-11-20 00:00:00 |           2 |   1560 |

+-----+---------------------+-------------+--------+

These two tables using the RIGHT JOIN as follows

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Output:

Note: The RIGHT JOIN keyword returns all records from the right table (Customers), even if there are no matches in the left table (Orders).

+----+----------+--------+---------------------+

| ID | NAME     | AMOUNT | DATE                |

+----+----------+--------+---------------------+

| 1 | Manoj   |   3000 | 2017-10-08 00:00:00 |

| 1 | Manoj   |   1500 | 2017-10-08 00:00:00 |

| 2 | Kumar   |   1560 | 2017-11-20 00:00:00 |

+----+----------+--------+---------------------+
FULL (OUTER) JOIN: The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records.

SELECT table1.column1, table2.column2...FROM table1 FULL OUTER JOIN table2 ON table1.common_field = table2.common_field;

Note: FULL OUTER JOIN can potentially return very large result-sets!

Consider the following two tables.

Table 1 − CUSTOMERS Table is as follows.

+----+----------+-----+-----------+----------+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+----+----------+-----+-----------+----------+

| 1 | Ramesh   | 32 | Ahmedabad | 2000.00 |

| 2 | Khilan   | 25 | Delhi     | 1500.00 |

| 3 | kaushik | 23 | Kota      | 2000.00 |

+----+----------+-----+-----------+----------+

Table 2 − ORDERS Table is as follows.

+-----+---------------------+-------------+--------+

|OID | DATE                | CUSTOMER_ID | AMOUNT |

+-----+---------------------+-------------+--------+

| 102 | 2017-10-08 00:00:00 |           3 |   3000 |

| 100 | 2017-10-08 00:00:00 |           3 |   1500 |

| 101 | 2017-11-20 00:00:00 |           2 |   1560 |

+-----+---------------------+-------------+--------+

These two tables using the FULL OUTER JOIN as follows

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS FULL OUTER JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Output:

Note: The FULL OUTER JOIN keyword returns all the rows from the left table (Customers), and all the rows from the right table (Orders). If there are rows in "Customers" that do not have matches in "Orders” or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well.

+------+----------+--------+---------------------+

| ID   | NAME     | AMOUNT | DATE                |

+------+----------+--------+---------------------+

|    1 | Ramesh   |   NULL | NULL                |

|    2 | Khilan   |   1560 | 2017-11-20 00:00:00 |

|    3 | kaushik |   3000 | 2017-10-08 00:00:00 |

|    3 | kaushik |   1500 | 2017-10-08 00:00:00 |

|    3 | kaushik |   3000 | 2017-10-08 00:00:00 |

|    3 | kaushik |   1500 | 2017-10-08 00:00:00 |

|    2 | Khilan   |   1560 | 2017-11-20 00:00:00 |

+------+----------+--------+---------------------+

Subscribe to: Posts ( Atom )

Saturday 27 April 2024

How To Retrieve Data Using FLASHBACK Command In Oracle.

Tuesday 10 October 2017

SQL JOINS

SELECT column - name FROM table-name1 JOIN table-name2 ON common_name1 = common_name2 where condition;

Example of SQL Join's

Consider the following two tables.

Table 1 − CUSTOMERS Table is as follows.

Table 2 − ORDERS Table is as follows.

These two tables using the INNER JOIN as follows

Output:

Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.

Consider the following two tables.

Table 1 − CUSTOMERS Table is as follows.

Table 2 − ORDERS Table is as follows.

These two tables using the LEFT JOIN as follows

Output:

Note: The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).

Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

Consider the following two tables.

Table 1 − CUSTOMERS Table is as follows.

Table 2 − ORDERS Table is as follows.

These two tables using the RIGHT JOIN as follows

Output:

Note: The RIGHT JOIN keyword returns all records from the right table (Customers), even if there are no matches in the left table (Orders).

Note: FULL OUTER JOIN can potentially return very large result-sets!

Consider the following two tables.

Table 1 − CUSTOMERS Table is as follows.

Table 2 − ORDERS Table is as follows.

These two tables using the FULL OUTER JOIN as follows

Output:

OUR TECH-PROJECTS

DOWNLOAD ANY ONLINE VIDEOS

LABELS

Development Accessories

POPULAR POSTS

Saturday 27 April 2024

How To Retrieve Data Using FLASHBACK Command In Oracle.

Tuesday 10 October 2017

SQL JOINS

SELECT column - name FROM table-name1 JOIN table-name2 ON common_name1 = common_name2 where condition; Example of SQL Join's

Consider the following two tables.

Table 1 − CUSTOMERS Table is as follows.

Table 2 − ORDERS Table is as follows.

These two tables using the INNER JOIN as follows

Output:

Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.

Consider the following two tables.

Table 1 − CUSTOMERS Table is as follows.

Table 2 − ORDERS Table is as follows.

These two tables using the LEFT JOIN as follows

Output:

Note: The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).

Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

Consider the following two tables.

Table 1 − CUSTOMERS Table is as follows.

Table 2 − ORDERS Table is as follows.

These two tables using the RIGHT JOIN as follows

Output:

Note: The RIGHT JOIN keyword returns all records from the right table (Customers), even if there are no matches in the left table (Orders).

Note: FULL OUTER JOIN can potentially return very large result-sets!

Consider the following two tables.

Table 1 − CUSTOMERS Table is as follows.

Table 2 − ORDERS Table is as follows.

These two tables using the FULL OUTER JOIN as follows

Output:

OUR TECH-PROJECTS

DOWNLOAD ANY ONLINE VIDEOS

LABELS

Development Accessories

POPULAR POSTS

SELECT column - name FROM table-name1 JOIN table-name2 ON common_name1 = common_name2 where condition;

Example of SQL Join's