SQL LAB5(5.1-5.13)

                                               SQL  QUREIES

List the minimum, maximum and average salaries and rename the column as min_sal, max_sal, avg_sal, total salary of the employees.

SELECT MAX(SAL) AS MAX_SAL,MIN(SAL) AS MIN_SAL,AVG(SAL) AS AVG_SAL ,SUM(SAL) AS TOTAL FROM EMP;

List the Project names undertaken by Marketing Department.

SELECT PRJ_NAME FROM PROJ P, DEPART D WHERE D.DNO=P.DNO AND DANAME='MARKETING';

Display the employees name in capital, lower, 1st character only capital, number of characters and 3 characters from 2nd position.

SELECT upper(ename)AS ENAME,lower(ENAME) AS ename , SUBSTR(ENAME,1,1),LENgth(ENAME),LOWER(SUBSTR(ename,2,3)) FROM emp;

List the name of employees who are working under the manager ‘Raghu’.

SELECT ENAME FROM EMP WHERE MGRID = (SELECT EMPNO FROM EMP WHERE EMPNO='RAGHU'); 

Display department name, Max salary and Min salary in each department.

SELECT DANAME, MAX(SAL) ,MIN(SAL) FROM DEPART D,EMP E WHERE D.DNO = E.DEPTNO GROUP BY DANAME;

Display number of employees working in each department and their department name.

SELECT DANAME,COUNT(ENAME) FROM EMP E,DEPART D WHERE D.DNO= E.DEPTNO GROUP BY DANAME;