SQLnPLSQLGuru

You will use the below SQL to use to_char function correctly. select first_name, last_name, to_char(hire_date, 'Day, Month DDTH, YYYY') formatted_hire_date from hr.employees; Results from our TO_CHAR formatting appear with the correct day name, month name and other desired formatting in place. FIRST_NAME            LAST_NAME       FORMATTED_ HIRE_DATE -------------------   -----------     -------------------- Donald                OConnell       Monday,June 21ST, 1999 Douglas               Grant          Thursday,January   13TH, 2000 Jennifer              Whalen ...

Using the IN keyword, we can construct a more compact statement,where the set of possible values are enclosed in parentheses and separated by commas. Each row of Entry is investigated, and if TourID is one of the values in the set, then the WHERE condition is true, and that row will be returned. Using IN keyword SELECT e.MemberID FROM Entry e WHERE e.TourID IN (36, 38, 40) Using NOT IN keyword SELECT e.MemberID FROM Entry e WHERE e.TourID NOT IN (36, 38, 40) Conclusion The above examples useful to use IN and NOT IN key words in your SQL.

With the outer join, some table reordering is possible and recommended for efficiency.  Normally, hierarchical structures are built top-down, but when subviews are used, right-sided nesting can cause the structure to be built bottom-up.  Top-down execution is more efficient than bottom-up execution because bottom-up execution can cause throwaways. Throwaways are rows that are retrieved into the working set and then later discarded. SQL Expanded (Bottom-Up): SELECT* FROM Department LEFT JOIN         Employee LEFT JOIN Dependent           ON EmpNo=DpndEmpNo            ON DeptNo=EmpDeptNo   SQL Rewritten (Top-Down): SELECT * FROM Department LEFT JOIN Employee ON DeptNo=EmpDeptNo LEFT JOIN Dependent ON EmpNo=DpndEmpNo

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...

(click for SQL jobs) SQL - QUERIES 1 . DISPLAY ALL THE INFORMATION OF THE EMP TABLE ? A ) SELECT * FROM EMP ; 2 . DISPLAY UNIQUE JOBS FROM EMP TABLE ?   A ) SELECT DISTINCT JOB FROM EMP ; B ) SELECT UNIQUE JOB FROM EMP ; 3 . LIST THE EMPS IN THE ASC ORDER OF THEIR SALARIES ?   A ) SELECT * FROM EMP ORDER BY SAL ASC ; 4 . LIST THE DETAILS OF THE EMPS IN ASC ORDER OF THE DPTNOS AND DESC OF JOBS ?   A ) SELECT * FROM EMP ORDER BY DEPTNO ASC , JOB DESC ; 5 . DISPLAY ALL THE UNIQUE JOB GROUPS IN THE DESCENDING ORDER ? A ) SELECT DISTINCT JOB FROM EMP ORDER BY JOB DESC ; 6 . DISPLAY ALL THE DETAILS OF ALL ‘ MGRS ’ A ) SELECT * FROM EMP WHERE EMPNO IN ( SELECT MGR FROM EMP ) ; 7 . LIST THE EMPS WHO JOINED BEFORE 1981 .   A ) SELECT * FROM EMP WHERE HIREDATE < ( ’ 01 - JAN - 81 ’ ); 8 . LIST TH...