Category: PL/SQL

The ORDER BY clause is used to order rows returned in an select statement. With ORDER BY clause you can use parameters ASC or DESC to specify ascending order and descending order. Order By example SELECT * FROM course ORDER BY price, name; SELECT * FROM course ORDER BY price ASC, name ASC; SELECT *…(Continue Reading)

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)