Venky DevMastery

 /*Group by Functions GROUP or AGGREGATE Functions Group or Aggregate functions process similary group of rows and returns one value   1.MAX()  2.MIN()  3.SUM()  4.AVG()  5.COUNT()  6.COUNT(*)  7.count(1) 1.MAX() It returns maximum value.  Syntax: MAX(colname) Eg: */   SELECT MAX(SAL) FROM EMP;      SELECT MAX(HIREDATE) FROM EMP; --It will give us the most recent record which has the recent date.   select hiredate from emp order by hiredate desc;   SELECT MAX(ENAME) FROM EMP; --WARD   select ename from emp order by ename desc; select max(sal) from emp;--5000 select max(hiredate) from emp;--12-JAN-83 select max(ename) from emp;--WARD select max(empno) from emp;--7934 select max(job) from emp;--SALESMAN select max(comm) from emp;--1540 select max(deptno) from emp;--30 select comm from emp; /* MI...

 WITH Clause  ====================================== What? With Clause is a Sub-Query factoring or (Common Table Expressions-CTEs) This features is introduced in Oracle 9i.Using this feature: We assign query result set to a temporary tables. We can access data from temporary tables in main Select clause as many times we require with out repeating the sub-queries. When? Usage of (2,3 level-Nested Queries) can  be minimized or avoided when using With Clause. If we use sub-queries up to 1 level then performance may be good but when we use 2 level or 3 level sub-queries then there may chance of performance issues when we try to fetch large large volume of data. How to implement CTEs ====================== SELECT deptno,        Sum(sal) total_sal FROM   emp GROUP  BY deptno; --fetch sum of sal for each dept. SELECT Avg(total_sal) FROM  (SELECT deptno,               Sum(sal) total_sal     ...

Single Row_SubQuery -------------------- Single-Row Sub-Queries Returns one value SELECT <columns> FROM <table_name> WHERE <col_name> <OP> (SELECT STMNT); > , >=, <, <=, =. <> A single-row subquery in Oracle SQL is a subquery (a query nested inside another query) that returns only one row. The main/outer query uses the inner query result as its conditions If single row sub-query returns more than one row then you will get an error ORA-01422: exact fetch returns more than requested number of rows) It can be used in Select, Where, Having clauses Single Row_SubQuery -------------------- --1. Find who is getting highest salary in the employee table? select max(sal) from emp; select *from emp where sal=(select max(sal) from emp); select max(salary) from employees; select *from employees where salary=(select max(salary) from employees); --2.Find second maximum salary emp? select *from emp where sal=( select max(sal) from emp where sal!=(select max(sa...