SQLnPLSQLGuru

Oracle 11g features Oracle Database 11g offers terrific ETL capabilities that enable a newer way to load data into a database: the transform-while-loading method. By using the Oracle database to perform all the ETL steps, you can efficiently perform the typically laborious ETL processes. Oracle provides you with a whole set of complementary tools and techniques aimed at reducing the time needed to load data into the database while simplifying the work involved. Oracle's ETL solution includes the following components: External tables: External tables provide a way to merge the loading and transformation processes. Using external tables will enable you to eliminate cumbersome and time-consuming intermediate staging tables during data loading. Multitable inserts: Using the multitable insert feature, you can insert data into more than one table at the same time, using different criteria for the various tables. This capability eliminates the additional step of first div

What is SELECT in SQL query? When executed, a SELECT statement always returns a table. The table may have only one column and no rows, but every SELECT statement returns its query results as a table. Moreover, when a SELECT statement appears within another SELECT statement as a subquery, the inner SELECT statement returns a results table that serves as the input table for the outer (or main) SELECT statement. If you are using interactive SQL (such as the window-oriented MS-SQL Server Query Analyzer or the command-line MS-SQL Server ISQL application), the SELECT statement displays its query results in tabular form on your computer screen. If you are using a program (such as Visual Basic, C, or C++) to send a query (SELECT statement) to the DBMS, the DBMS will use a cursor to hold the tabular query results while it passes the rows of column values to your application program's (host) variables. SELECT with WHERE clause A SELECT statement with a WHERE clause will sti

The default order of rows returned for a result table varies depending upon the specific nature of the query. However, generally you can assume that the order of result table rows is random. If a specific order is required then one must include the optional ORDER BY clause.                              SELECT [column(s)]                FROM [table(s)]                WHERE [row(s) to select]                GROUP BY [column(s) for summaries]                HAVING [groups to select]                ORDER BY (Cols to sort) The ORDER BY clause is almost always placed at the end of the SELECT statement (although some advanced statement structures beyond the scope of this course could alter this general rule). The default sort order is ascending if the ORDER BY clause is omitted.

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         Thursday,September 17TH, 1987 Michael               Hartstein      Saturday,February  17TH, 1996 Pat                   Fay            Sunday,August    17TH, 1997

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.

SQL query to retrieve all junior girls SELECT * FROM Member m WHERE m.MemberType = 'Junior'       AND m.Gender = 'F'; Conclusion The above query useful to retrieve all junior girls.

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

SELECT * FROM Member m ORDER BY m . LastName

LIST THE EMPS IN THE ASC ORDER OF DESIGNATIONS OF THOSE JOINED AFTER THE SECOND HALF OF 1981. SELECT * FROM EMP WHERE HIREDATE > ( ’ 30 - JUN - 81 ’ ) AND TO_CHAR ( HIREDATE , ’ YYYY ’ ) = 1981 ORDER BY JOB ASC ;

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

SQL query to list the employees who joined before 1981 SELECT * FROM EMP WHERE HIREDATE < ( ’ 01 - JAN - 81 ’ );

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 , department_id FROM

The following are new enhancements in PL/SQL of Oracle 11g: Automatic subprogram in-lining CREATE OR REPLACE PROCEDURE inline_demo  ( a NUMBER , b NUMBER ) IS   PRAGMA INLINE(add_numbers,'YES'); BEGIN   FOR i IN 1..10000 LOOP     dbms_output.put_line(add_function(8,3));   END LOOP; END; / Inlining a subprogram replaces the call to the external subprogram with a copy of the subprogram. This almost always improves program performance. You could instruct the compiler to inline subprograms by using the PRAGMA INLINE compiler directive in PL/SQL starting with the Oracle 11g Database. You must set the PRAGMA when you have the PLSQL_OPTIMIZE_LEVEL parameter set to 2. A continue statement BEGIN    FOR i IN 1..5 LOOP      dbms_output.put_line('First statement, index is ['||i||'].');      IF MOD(i,2) = 0 THEN        CONTINUE;     END IF;      dbms_output.put_line('Second statement, index is ['||i||'].');   END LOOP;  END;  /