Oracle SQL Learnings

• NLS settings
  
  NLS settings supports the globalization of the db instance. It has variety of parameters like NLS_LANGUAGE, NLS_TERRITORY etc. The settings can be done at three level database, instance and session level. Session level takes precedance over the other two. The NLS settings are stored in
  NLS_DATABASE_PARAMETERS,
  NLS_INSTANCE_PARAMETERS,
  NLS_SESSION_PARAMETERS.
  
  The session can be set as
  ALTER SESSION SET parameter_name = 'parameter_value'
  Example:
  ALTER SESSION SET NLS_TERRITORU = 'AMERICA'.
  
  NLS settings governs lots of db settings like date, time format, calendar, start of week, etc

• LOCK TABLE
  
  An update of any table can be prevented by putting lock on it using below command.
  
  LOCK TABLE hr.per_medical_assessments IN EXCLUSIVE MODE;
  
  The lock is removed by either a coomit or a rollback

• CALUCLATING AGE
  
  Age can be calculated as
  FLOOR ( MONTHS_BETWEEN(trunc(sysdate) , ppx.DATE_OF_BIRTH)/12) age