DROP vs. TRUNCATE vs. DELETE

DROP TABLE

• Deletes Table structure  + Data
• Invalidates the dependent objects
• Drops the indexes

TRUNCATE and DELETE

• Deletes only data

TRUNCATE vs. DELETE

• TRUNCATE performs an automatic commit and cannot be rolled back (It 's a DDL) but after DELETE, you can commit / rollback changes (It' s DML) 
• Trigger is fired when DELETE is used, but not in case of TRUNCATE
• Condition (WHERE clause) can be used with DELETE but not with TRUNCATE
• TRUNCATE is faster than DELETE

Before deleting using DELETE, all data is copied to Rollback Tablespace but in case of TRUNCATE there is no extra copy operation, so TRUNCATE is faster than DELETE

How to use SELECT...CASE to get customized column names in result ?

SELECT...CASE can be used to display different strings based on different values for a table column.

Syntax

SELECT

   CASE field

       WHEN value1 THEN newValue1

       WHEN value2 THEN newValue2

       ELSE defaultValue

   END

   as fieldAlias

   ...

FROM ...

WHERE ...

ExampleSELECT

qry.date as Date_Time,

CASE qry.id_service

  WHEN 0 THEN 'Internet'

  WHEN 1 THEN 'TV'

  WHEN 2 THEN 'telephone'

  ELSE qry.id_service

END as Service_Name,

CASE qry.returnStatus

  WHEN 0 THEN 'Success'

  ELSE 'Failed'

END as Status,

qry.name as 'Name',

FROM 

(

  SELECT lp.date, lp.id_service, lp.returnStatus, u.name

  FROM logs lp, userinfos u

  WHERE u.id = lp.id_user

) AS qry

SQL vs. HQL

HQL vs. SQL

HQL is similar in appearance to SQL, but with some properties : 

• HQL is fully object-oriented and understands various OOPs features
• HQL is RDBMS independent