SQLnPLSQLGuru

SELECT EMPNO , ENAME , JOB , HIREDATE , MONTHS_BETWEEN ( SYSDATE , HIREDATE ) EXP FROM EMP WHERE EMPNO IN ( SELECT MGR FROM EMP );

The MERGE statement is a single command that combines the ability to update or insert rows into a table by conditionally deriving the rows to be updated or inserted from one or more sources.  It is most frequently used in data warehouses to move large amounts of data but its use is not limited to only data warehouse environments.  The big value-add this statement provides is that you have a convenient way to combine multiple operations into one. This allows you to avoid issuing multiple INSERT, UPDATE, and DELETE statements.  Syntax of Merge statement: MERGE INTO <table_name> USING <table_view_or_query> ON (<condition>) WHEN MATCHED THEN <update_clause> DELETE <where_clause> WHEN NOT MATCHED THEN <insert_clause> [LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>]; Best example: MERGE INTO dept60_bonuses b USING ( SELECT employee_id , salary , departme...

Natural Keys: -A natural key is a subset of attributes that occur in a table and act as a unique identifier. The user sees them. You can go to the external reality and verify them. Example: bar codes on consumer goods (read the package), geographic coordinates (get a GPS), or the VIN on an automobile (read the tag riveted to the dashboard, etched into the windows, and engraved on the engine block). Artificial Keys:- An artificial key is an extra attribute added to the table that is seen by the user. It does not exist in the external reality but can be verified for syntax or check digits inside itself. Example: the open codes in the UPC/EAN scheme that a user can assign to his own stuff. For example, a grocery store not only sells packaged good with preprinted bar codes but also bakes bread in the store and labels them with an open code. The check digits still work, but you have to verify them inside your own enterprise. Exposed Physical LOactors: An exposed physical loca...