Latest Event Updates
Self-Joins Using the ON Clause
Self-Joins Using the ON Clause
Employee table may have a SupervisorID column that points to the employee that is the boss of the current employee. It’s basically used where there is any relationship between rows stored in the same table. SQL self–join simply is a normal join which is used to join a table to itself.
Example: Read the rest of this entry »
Types of Joins
Types of Joins
- INNER-JOIN: It merges(or combiens) matched rows from two tables. …
- **OUTER-JOIN:**It merges(or combines) matched rows from two tables and unmatched rows with NULL values.
Restricting Group
Restricting Group
Results with the HAVING Clause
The GROUP BY Clause is used together with the SQL SELECT statement. The SELECT statement used in the GROUP BY clause can only be used contain column names, aggregate functions, constants and expressions. The HAVING clause is used to restrict the results returned by the GROUP BY clause. Read the rest of this entry »
Group Functions and Null Values
Group Functions and Null Values
According to the SQL Reference Manual section on Aggregate Functions: All aggregate functions except COUNT(*) and GROUPING ignore nulls. You can use the NVL function in the argument to an aggregate function to substitute a value for a null. COUNT never returns null, but returns either a number or zero.
Group functions ignore null values in the column.
You can divide rows in a table into smaller groups by using the GROUP BY clause. Read the rest of this entry »
SQL AVG()
SQL AVG()
- The AVG() Function. The AVG() function returns the average value of a numeric column. SQL AVG() Syntax. …
- Demo Database. In this tutorial we will use the well-known Northwind sample database. …
- SQL AVG() Example. The following SQL statement gets the average value of the “Price” column from the “Products” table.
Group Functions?
What Are Group Functions?
Group functions are built-in SQL functions that operate on groups of rows and return one value for the entire group. These functions are: COUNT, MAX, MIN, AVG, SUM, DISTINCT. SQL COUNT (): This function returns the number of rows in the table that satisfies the condition specified in the WHERE condition.
Group functions operate on sets of rows to give one result per group. Read the rest of this entry »
DECODE Function
DECODE Function
In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then Oracle returns the result of the first search that is also null.
Facilitates conditional inquiries by doing the work of a CASE expression or an IF-THEN-ELSE statement.
COALESCE Function
Using the COALESCE Function
In the example above, if any of the “UnitsOnOrder” values are NULL, the result is NULL. Microsoft’s ISNULL() function is used to specify how we want to treat NULL values. The NVL(), IFNULL(), and COALESCE() functions can also be used to achieve the same result.
• The advantage of the COALESCE function over theNVL function is that the COALESCE function cantake multiple alternate values.
• If the first expression is not null, the COALESCE function returns that expression; otherwise, it does a COALESCE of the remaining expressions. Read the rest of this entry »
oracletechnocampus 