1. Using Aggregate functions: Count,Min,Max,Sum,Avg.
    1. Find the number of rows in emp ?
      SELECT count(*) FROM emp;
      
    2. Find the how many different Job profile are there in Employee table ?
      SELECT COUNT(DISTINCT job) FROM emp;
      
    3. Find out how many people were given commision ? ( Count function does n't include null as it counts)
      SELECT COUNT(comm)FROM emp;
      
    4.  
    5.  

    6. 
      

Q5:Select records  from two tables;
5.1

SELECT e.ename, d.deptno,e.sal FROM emp e,dept d;

5.2 Select records from two tables depending condition suppose where deptno of from both emp and dept mathes

Select e.ename, d.deptno,e.sal from emp e,dept d  where e.deptno=d.deptno;


Q6:  Select records using where clause;
6.1

SELECT ename,sal FROM emp  WHERE sal>2000

6.2 Select records from emp and dept where both deptno matches

SELECT e.ename,e.sal,d.deptno FROM emp e,dept d WHERE e.deptno=d.deptno

6.3 Select secords where empno=1003

SELECT * FROM emp WHERE empno=1003

6.4 Select records where ename is KING

SELECT * FROM emp WHERE ename='KING';


Q7:Select records using or;
7.1

SELECT ename,sal FROM emp WHERE deptno=10 OR deptno= 20

7.2

SELECT * FROM emp WHERE city='LONDON' OR city='PARIS'

7.3

SELECT * FROM emp WHERE sal>2000 OR comm IS NOT NULL


Q8: Select records using between and;
8.1

SELECT * FROM emp WHERE sal BETWEEN 2000 AND 3000

8.2

SELECT * FROM dept WHERE deptno BETWEEN 20 AND 40;

8.3

SELECT * FROM emp WHERE empno BETWEEN 1001 TO 1010;


Q9: Select records using in;
9.1

SELECT * FROM emp WHERE ename IN('SCOTT','WARD','ALLEN')

9.2

SELECT ename FROM emp WHERE sal IN(1000,2000.3000.3200,3300);


Q10: Select records using not in;

SELECT * FROM emp WHERE ename NOT IN('SCOTT','WARD','ALLEN')


Q11: Select records using null;

SELECT * FROM emp WHERE comm IS  NULL;


Q12:Select records using not null;

SELECT * FROM emp WHERE comm IS NOT NULL;


Q13:Select records using like;
13.1

SELECT * FROM emp WHERE ename LIKE'S%';

13.2

SELECT * FROM emp WHERE ename LIKE 'sC___';

13.3

SELECT * FROM emp WHERE ename LIKE 'AL%';

13.4

SELECT * FROM emp WHERE ename LIKE 'A_L_N';

13.5

SELECT * FROM emp WHERE ename LIKE '___L%';

 


Q14:Select records using not like;

SELECT * FROM emp WHERE ename NOT LIKE 'A%';


Q15:Select records using multiple conditions.
15.1

SELECT * FROM emp WHERE comm IS NOT NULL AND sal >2000 AND deptno>20;

15.2

SELECT * FROM emp WHERE sal>2000 AND deptno>20 AND ename NOT LIKE(A%) 

15.3

SELECT * FROM emp WHERE deptno<20 AND comm IS NOT NULL OR sal IN{1000,2000,3000,4000}; 


Q16:Select records using function;
16.1

SELECT ename,sum(sal+comm) AS total FROM emp

16.2

SELECT count(*) FROM emp;
16.3
SELECT deptno,avg(sal) AS avgsal FROM emp GROUP BY deptno;

 


Q17:Select records using order by;
17.1

SELECT * FROM emp ORDER BY sal

17.2

SELECT ename,sal FROM emp ORDER BY sal,ename

17.3

SELECT empno,ename,sal FROM emp ORDER BY sal DESC,empno,empname