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

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.

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