SQLnPLSQLGuru

Most databases live and work in one time zone. If you have a database that covers more than one time zone, you might consider storing time in UTC and adding a numeric column to hold the local time-zone offset.The time zones start at UTC, which has an offset of zero. This is how the system-level time-zone table in Standard SQL is defined. Theory Behind Time There are also ISO-standard three-letter codes for the time zones of the world, such as EST, for Eastern Standard Time, in the United States. The offset is usually a positive or negative number of hours, but there were some odd zones that differed by 15 minutes from the expected pattern; these were removed in 1998. Now you have to factor in daylight saving time on top of that to get what is called “lawful time,” which is the basis for legal agreements. The U.S. government uses DST on federal lands inside states that do not use DST. If the hardware clock in the computer in which the database resides is the source of the ...

The below are some of the key points about nulls, and how to compare them. Just to make sure that you should know about what is NULL value. Interpretation is not exactly missing value. There could be many reasons why no value is present, e.g., value inappropriate. How to compare NULL to values In the below example the where condition just checks for TRUE condition. If not matches you will get UNKNOWN. Example: Table Name is: Sells bar beer price ======================= Joe's bar Bud NULL SELECT bar FROM Sells WHERE price < 2.00 OR price >= 2.00; ------------ ------------- UNKNOWN Conclusion Joe's Bar is not produced, even though the WHERE condition is correctly given. The reason is the price value is NULL. So, we should not compare NULL to values.

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.

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

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

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

Bill Gates is a very rich man today…and do you want to know why? The answer is one word: versions. - Dave Barry, Pulitzer prize-winning American author and columnist It seems IT industry glamour is reducing. Increasing cost, lot of competition, technology advancements. To survive in the market there is only one solution  is to update our IT skills. Daily need to spend 1 or 2 hours to upgrade our IT skills.  Maybe they didn't finish college, but I'll be damned if our famous (and infamous) tech captains of industry, and most of the minions who helped them get there, did one thing quite well—and that's this: they constantly strove to be bigger, better, and badder. Badder to the bone, baby. Meaning that they acquired new skills and upgraded themselves, and they ensured that their teams did the same. These days are no exception, because those who do these things are the ones who will survive the greatest recession since the Depression.  It's not going to be easy...