Unidirectional – bidirectional relationship provides navigational access in one direction

  Parent -----> Child

i.e you can go from parent to child, but you cannot go back from children to parent.

However, if there were no pointer to Parent in Child:

class Child { }

Bidirectional – bidirectional relationship provides navigational access in both directions

  Parent <-----> Child

i.e you can go from a Parent to its child, and vice-versa: the parent knows about its child, the child knows about its parent

class Parent {
  Child* children;
}

class Child {
  Parent* parent;
}

Idle Scenarios
One to One unidirectional Mapping
employee knows the employer

One to One Bidirectional Mapping
employer knows the employee and employee knows the employer

One to Many unidirectional Mapping
employee has a skill in his skill set which is not used by other employees

One to Many bidirectional Mapping
employee has a skill in his skill set which is not used by other employees and
employer knows that employee has this skill in skill set

Many to One unidirectional Mapping
employees knows which employer he is going to work but employer has no idea about employee

employees will have employerId in their entity class but employer has no details of employee

Many to One bidirectional Mapping
Many employees work for one Employer. The employer knows about employee and employee knows about employer

employees will have employerId in their entity class but employer will have empid of employee

Many to Many unidirectional Mapping
Employee knows the employers he has worked for but employers does not know details of employee who worked for them

Many to Many bidirectional Mapping
Employee knows about the employers he has worked for and employers knows about employee who has worked for them.

  1. JDBC-ODBC Bridge Driver
  2. JDBC-Native API
  3. Network Protocol driver(Pure Java Driver)
  4. Thin driver(Pure Java Driver)

What is Difference between Thin client driver and Thick Client driver?
Thick driver – type 1 and type 2 JDBC drivers are called thick drivers.It provides JDBC access via ODBC drivers. ODBC binary code, and in many cases, database client code, must be loaded on each client machine that uses such a driver. and as the program is connected to database by an extra layer, hence thick drivers.

Thin drivers: Drivers converts JDBC calls into the network protocol, used directly by DBMS, allowing a direct call from the client machine to the DBMS server and providing a practical solution for intranet access.

What is the simplest SQL Query to find the second largest value?

SELECT MAX(col)
  FROM table
 WHERE col < (SELECT MAX(col)
                FROM table)
SELECT MAX(col) 
  FROM table 
 WHERE col NOT IN (SELECT MAX(col) FROM table);
Posted in SQL.

FOR cur IN (SELECT  process_Id 
              FROM  tbl_process)
LOOP
  SELECT (MAX(TO_NUMBER(substr(p.old_id, instr(p.old_id, '_', -1) + 1))))
    INTO new_id
    FROM tbl_process p
   WHERE process_Id = cur.process_Id;

  IF new_id IS NULL THEN
    new_id := cur.process_Id || '_1';
  ELSE
    new_id := new_id + 1;
    new_id := cur.process_Id || '_' || new_id;
  END IF;
END LOOP;

If you want a query which returns rows in column containing NULL or empty space then the using NULL alone in where clause will not return empty space rows. So the query should be altered as below

Actual Query

 SELECT COUNT (*) 
   FROM TABLE 
  WHERE COL_NAME IS NULL;

Altered Query

  SELECT COUNT (*) 
    FROM TABLE 
   WHERE trim(COL_NAME) IS NULL

(or)

Altered Query

  SELECT COUNT (*) 
    FROM TABLE  
    WHERE COL_NAME IS NULL OR 
          LENGTH(TRIM (COL_NAME)) = 0

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.

There is a performance hit when using UNION vs UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).

UNION Example:

 SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar

Result

+-----+
| bar |
+-----+
| foo |
+-----+
1 row in set (0.00 sec)

UNION ALL example:

 SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar

Result

+-----+
| bar |
+-----+
| foo |
| foo |
+-----+
2 rows in set (0.00 sec)
Posted in SQL.

During Release the Scripts are executed in the Following Order

  1. Schema – DDL Scripts – (Table Alter Scripts)
  2. Content Script – Rows addition and Deletion
  3. Packages (Stored Procedures and Function)- DML Scripts – (CRUD)