SQLnPLSQLGuru

The below are the steps in writing sample procedure in SQL. CREATE PROCEDURE update_sal (IN empnum    CHAR(6),                               INOUT rating SMALLINT)    LANGUAGE SQL    BEGIN      IF rating = 1 THEN        UPDATE employee        SET    salary = salary * 1.10,               bonus = 1000        WHERE  empno = empnum;      ELSEIF rating = 2 THEN        UPDATE employee        SET    salary = salary * 1.05,               bonus = 500        WHERE  empno = empnum;      ELSE        UPDATE employee        SET    ...

Initial PL/SQL versions were not sequenced with the version of the database. For example, PL/SQL 1.0 shipped with the Oracle 6 Database. PL/SQL 2.x shipped with the Oracle 7.x Databases, beginning with Oracle 8, PL/SQL versions correspond to the database release numbers, like PL/SQL 11.1 in the Oracle 11g Release 1 Database. In PL/SQL, C, C++, or Java. Java programs can be directly stored inside the Oracle 11g Database in all releases except the Oracle Express Edition. PL/SQL is a structured programming. PL/SQL supports dynamic datatypes by mapping them at run time against types defined in the Oracle 11g Database catalog. Matching operators and string delimiters means simplified parsing because SQL statements are natively embedded in PL/SQL programming units. The PL/SQL run-time engine exists as a resource inside the SQL*Plus environment. The SQL*Plus environment is both interactive and callable. Every time you connect to the Oracle 11g Database, the database creates a...

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.