by Dinesh Thakur Category: SQL Queries

Use the LIKE condition to perform wildcard searches of valid search string values. Search conditions can contain either literal characters or numbers. You can combine pattern-matching characters. You can use the ESCAPE identifier to search for the actual % and _ symbols.

 
by Dinesh Thakur Category: SQL Queries

The MINUS query will compare each record in statementl to a record in statement2. The result returned will be records in Select Statement I that are not in Select statement2.

 
by Dinesh Thakur Category: SQL Queries

By default all columns in a table can contain null values. If you want to ensure that a column must always have a value, i.e. it should not be left blank, then define a NOT NULL constraint on it.

 
by Dinesh Thakur Category: SQL Queries

The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain unique values. A primary key column cannot contain NULL values. Each table should have a primary key, and each table can have only one primary key.



 
by Dinesh Thakur Category: SQL Queries

In SQL the UNION Clause combines the results of two SQL queries into a single table of all matching rows. The two queries must have the same number of columns and compatible data types to unite. Any duplicate records are automatically removed unless UNION ALL is used.



 
by Dinesh Thakur Category: SQL Queries

A UNIQUE key integrity constraint requires that every value in a column or set of columns (key) be unique-that is, no two rows of a table have duplicate values in a specified column or set of columns.

 
by Dinesh Thakur Category: SQL Commands

Thus far we have only been getting data from one table at a time. This is fine for simple tasks, but in most real world SQL usage you will often need to get data from multiple tables in a single query. Oracle provides the facility to retrieve the data from multiple tables with the help of joins. It is perhaps a wonderful feature of SQL that permit to retrieve the data from multiple users.

 
by Dinesh Thakur Category: SQL Commands

In SQL inner joins are also called simple joins or equijoin. We are now ready to present a SELECT statement with what is called an inner join:

 
by Dinesh Thakur Category: SQL Commands

A self-join is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the same column in the same table. The SQL self-join can be done by using table aliases to cheat one table like a different table and then join them together.

 
by Dinesh Thakur Category: SQL Commands

A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the Joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in A.

 
by Dinesh Thakur Category: SQL Commands

It is important to note that the INNER JOIN only returns data where a match is found. While using the inner join we have seen that if there exists certain records in one table. Which doesn't have corresponding values in the second table then those rows will not be selected.

 
by Dinesh Thakur Category: SQL Commands

The result of a left outer join (or simply left join) for table A and B always contains records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result-but with NULL in each column from B.

 
by Dinesh Thakur Category: SQL Commands

To retrieve the Employee name, job, salary, deptno, dname from EMP, DEPT table. It will return all information regarding the employees and the department information. This is Full Outer Join's Example.



 
by Dinesh Thakur Category: SQL Commands

A cross join, or product provides the foundation upon which all types of inner joins operate. A cross join returns the Cartesian product of the sets of records from the two joined tables. Thus, it equates to an inner join where the join-condition always evaluates to true or join-condition is absent in statement. This command is introduced for Oracle9i. If A and B are two sets, then the cross join is written as A x B.

 
by Dinesh Thakur Category: SQL Commands

When the user fires a SELECT statement to search for a particular record, the oracle engine must first locate the table on the hard disk. The Oracle engine reads the information and locates the starting location of a table's record on the current storage media.



 
by Dinesh Thakur Category: SQL Commands

The SQL DROP INDEX statement is the SQL command that removes an entire SQL index. You may drop an index permanently when it is no longer useful or temporarily. If the index is harming or not helping performance it could be dropped.

 
by Dinesh Thakur Category: SQL Functions

SQL COUNT is the aggregate arithmetic function. COUNT allows us to COUNT number of row that matches specified criteria. This function returns the number of rows in the query. The COUNT function will only count those records in which the field in the brackets is NOT NULL. The SQL COUNT function is easy to use.

 
by Dinesh Thakur Category: SQL Functions

All of the aforementioned functions relate to specific ways to manipulate character, date/time, or numeric datatypes. But you may need to convert data from one datatype to another or convert NULL values to something meaningful.

 
by Dinesh Thakur Category: SQL Functions

 The ROUND function allows you to round any numeric value. The general format is:

ROUND (NumericValue, DecimalPlaces)
 
by Dinesh Thakur Category: SQL Functions

The simplest of the date/time functions is one that returns the current date and time. In Microsoft SQL Server, the function is named GETDATE. This function has no arguments. It merely returns the current date and time. For example:

 

Page 2 of 3



About Dinesh Thakur

Dinesh ThakurDinesh Thakur holds an B.SC (Computer Science), MCSE, MCDBA, CCNA, CCNP, A+, SCJP certifications. Dinesh authors the hugely popular blog. Where he writes how-to guides around Computer fundamental , computer software, Computer programming, and web apps. For any type of query or something that you think is missing, please feel free to Contact us.