#1. Display the manager number and the salary of the lowest paid employee for that manager. Exclude anyone whose manager is not known. Exclude any groups where the minimum salary is less than $1000. Sort the output in descending order of salary.SELECT mgr, MIN(sal)
FROM emp GROUP BY mgr
HAVING mgr is not null
AND MIN(sal) ＞= 1000
ORDER BY MIN(sal)DESC;
#2. Display the employee number, name, salary, and salary increase by 15% expressed as a whole number. Label the column New Salary.
SELECT ename,empno, sal, ROUND(sal*1.15) "New Salary"
FROM emp;
#3. Write a query to display the department name, location name, number of employees, and the average salary for all employees in that department. Label the columns dname, loc, Number of People, and Salary, respectively.
COLUMN dname HEADING "Department Name" FORMAT A15;
COLUMN loc HEADING "Location Name";
COLUMN COUNT(ename) HEADING "Number of Employees";
COLUMN AVG(sal) HEADING "Average Salary";
SELECT dname,loc, COUNT(ename), AVG(sal)
FROM emp, dept
WHERE emp.deptno=dept.deptno
GROUP BY dname, loc;
#4. What is difference between COUNT(*), COUNT(col_name), COUNT(DISTINCT(col_name)), COUNT(ALL(col_name))? Explain with examples.
/* COUNT(*): counts the number of rows in a table, for example, COUNT(*) from emp returns 14 rows;
COUNT(col_name): counts the number of nonnull rows for col_name, for example,COUNT(mgr) from emp returns 13 rows becasue there is one row with null value;
COUNT(DISTINCT(col_name)), eliminates duplicates and counts the number of different nonnull rows for col_name, for example,COUNT(DISTINCT(mgr)) from emp returns 6 rows with different values ;
COUNT(ALL(col_name)), retains duplicates, counts the number of nonnull rows for col_name, the same as COUNT(col_name), ALL is assumed if neither ALL nor DISTINCT is specified. For example, COUNT(ALL(mgr)) from emp returns 13 rows with nonnull values.
*/
# one example, see output test
SELECT COUNT(*), COUNT(mgr),COUNT(DISTINCT(mgr)),COUNT(ALL(mgr))
FROM emp;
#5. Write a query to display department names with salary grade, minimum salary and average commission. For departments with null commission, you should display 0. (salgrade table can be used for getting salary grade).
SELECT dname, grade, b.commavg, b.salmin
FROM salgrade, (SELECT dname, MIN(sal) salmin, AVG(NVL(comm,0)) commavg
FROM emp, dept
WHERE emp.deptno = dept.deptno
GROUP BY dname) b
WHERE (b.salmin BETWEEN salgrade.losal AND salgrade.hisal);