Author: Dev

The HAVING clause is used like search condition for a group or an aggregate function used in an select statement. SQL Query SELECT c.course_id, c.name, c.description, c.price, SUM(c.price) AS amount, COUNT(c.course_id) AS quantity FROM course c, orders o WHERE o.course_id = c.course_id GROUP BY c.course_id, c.name, c.description, c.price; Output COURSE_ID NAME DESCRIPTION PRICE AMOUNT QUANTITY…(Continue Reading)

The Group By clause is used when an aggregate function (count, max, min, sum, avg) exists in the pl/sql query. SQL Query SELECT c.course_id, c.name, c.description, o.order_id, c.price FROM course c, orders o WHERE o.course_id = c.course_id; Output COURSE_ID NAME DESCRIPTION ORDER_ID PRICE 1 SQL 1 SQL course for beginners 22 10 1 SQL 1…(Continue Reading)

The COALESCE function returns the first non null expression in the expression list. COALESCE syntax COALESCE( expr_1, expr_2, expr_n ) COALESCE example select COALESCE(null,’1′) from dual; Result: 1 select COALESCE(null, 2) from dual; Result: 2 select COALESCE(null, null, 3) from dual; Result: 3 select COALESCE(null, null, null, 4) from dual; Result: 4 select COALESCE(null, null,…(Continue Reading)

The NVL2 function returns the value returned by a query based, if the expression is null or not null. If expression1 is not null, then NVL2 returns expression2. If expression1 is null, then NVL2 returns expression3. NVL2 syntax NVL2( expression1, expression2, expression3 ) NVL2 example select NVL2( null, 1, 2 ) from dual; Result: 2…(Continue Reading)

The Mod function returns the remainder of n2 divided by n1. Mod syntax MOD ( n2, n1 ) Mod example select mod(19,8) from dual; Result: 3 select mod(-19,8) from dual; Result: -3 select mod(19,0) from dual; Result: 19 select mod(19.7,2) from dual; Result: 1.7 select mod(19.7,2.1) from dual; Result: 0.8