Powered by Blogger.

Saturday 27 April 2024

How To Retrieve Data Using FLASHBACK Command In Oracle.

April 27, 2024 , 1 comment :
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; 

Thursday 7 December 2017

DataBase Keys

December 07, 2017 ,
Type of Dada Base Keys
  •         Primary Key
  •        Foreign key
  •        Composite Key
  •        Natural Key
  •        Surrogate Key
  •        Candidate Key
  •        Compound key
Primary Key:  Primary key must contain UNIQUE Values, and cannot contain Null Values.
Example:
CREATE TABLE TBLNAME (ID INT NOT NULL PRIMARY KEY,
                      FIRSTNAME VARCHAR (225) NOT NULL,
                      LASTNAME VARCHAR (225) NOT NULL);

+-----+---------------------+-------------+
| ID  | LASTNAME             |   LASTNAME   |
+-----+---------------------+-------------+
| 100 | RAJ KUMAR            |  VISHWAKARMA |
 | 101 | RAMESH KUMAR         |  VISHWAKARMA | 
+-----+---------------------+-------------+
Foreign key:  Foreign key is a collection of fields in one table that refers to the primary key in another table
Example:  Create a two Table as a TBL1 AND TBL2
1.       TBL1- ID1 is Primary Key in TBL1
+-----+---------------------+-------------+
| ID1 | LASTNAME1            |   LASTNAME1  |
+-----+---------------------+-------------+
| 100 | RAJ                  |  VISHWAKARMA |
 | 101 | RAJU                 |  VISHWAKARMA | 
+-----+---------------------+-------------+
2.       TBL2- ID1 is foreign key in TBL2 which is reference of column ID1 Primary key in TBL1.
+----+----------+--------+
| ID | NAME     |  ID1   |
+----+----------+--------+
|  1 | RAJU    |   101  |
|  2 | Raj     |   100  |
                                      +-----+----------+--------+

Composite Key:  Composite key is a key which is the combination of more than one field or column of a given table. It may be a candidate key or primary key.
Columns that make up the composite key can be of different data types.
CREATE TABLE TBLNAME (COLUMNNAME1, DATATYPE1,
                      COLUMNNAME2, DATATYPE2,
                      ???
                      PRIMARY KEY (COLUMNNAME1, COLUMNNAME1));

Natural Key: A Natural Key is a type of unique key or also known as Business Key.

Surrogate Key:  A Surrogate key is a made up value with the sole purpose of uniquely identifying a row. This is represented by an auto incrementing ID.
CREATE TABLE TBLNAME (SURROGATE_KEY INT IDENTTY (1, 1),
                      FIRSTNAME VARCHAR (225) NOT NULL,
                      LASTNAME VARCHAR (225) NOT NULL);

Candidate key: A Candidate Key is a column or set of column in a database table.
+----+----------+-----------+
| ID | NAME     |  IDCODE   |
+----+----------+-----------+
|  1 | RAJU    |   101     |
|  2 | Raj     |   100     |
                                      +----+----------+-----------+
A Primary Key is being selected from the group of candidate keys. That means we can either have ID and IDCODE as Primary keys.

Compound Key:  A Compound Key is a key that consists of two or more attributes that uniquely identify an entity Occurrence.

Tuesday 10 October 2017

SQL JOINS

October 10, 2017 , , 1 comment :
Type Of SQL  Join:
  1. (INNER) JOIN: Returns records that have matching values in both tables.
  2. LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table.
  3. RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table.
  4. 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


  1. (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 |
    +----+----------+--------+---------------------+
     
  2. 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                |
    +----+----------+--------+---------------------+
     
  3. 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 |
    +----+----------+--------+---------------------+
     
  4. 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 )