Posts

Featured post

Time in SQL query top ready to use examples for your project

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
Post a Comment
Read more

Top myths for NULL value in SQL Queries

Image
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.
Post a Comment
Read more

The DB2 Stored Procedure these are Steps to follow

Image
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    salary = salary * 1.03,               bonus = 0        WHERE  empno = empnum;      END IF;    END; 
Post a Comment
Read more

The PLSQL Beginning and Key Concepts

Image
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
Post a Comment
Read more

Top features of ORACLE 11g ETL capabilities

Image
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
Post a Comment
Read more

Top features of SELECT statement in SQL query

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
Post a Comment
Read more

SQL Query to use ORDER BY clause

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.
Post a Comment
Read more