14. SQL (group by, multilevel query) - Agnivo102/Database_Architect GitHub Wiki

select department_id, count(*) from employees group by department_id order by department_id;

select department_id, count(*) from employees where department_id != 50 group by department_id order by department_id;

The record with the null also didn't come because the database engine couldn't compare null value with 50. The others are being compared with 50 to find that whether they satisfy the condition of the where clause but since null value couldn't be compared so it didn't show. To show it:

select department_id, count(*) from employees where department_id != 50 or department_id is null group by department_id order by department_id;

select department_id, count() from employees group by department_id having count() > 6 order by department_id;

select department_id, count() from employees group by department_id having count() < 6 order by department_id;

select max(salary) sal, department_id deptno from employees group by department_id order by max(salary);

select first_name, count(first_name) from employees group by first_name having count(first_name) > 1 order by first_name;

The first query is called outer query and the query in the bracket is called inner query or sub query. Inner query always executes first. The inner query will give the max salary value and the outer query will become this :

Select employee_id, first_name || ' ' || last_name "Full name" from employees where salary = 24000;

This will give the name of the employee who earns maximum salary

We can write n number inner queries in a sql query.

select employee_id, first_name || ' ' || last_name "Full Name", department_id from employees where salary = (select max(salary) from employees);

select employee_id, first_name || ' ' || last_name "Full Name", salary from employees where salary > (select avg(salary) from employees) order by salary;

select employee_id, first_name || ' ' || last_name "Full Name", department_id, salary from employees where (salary, department_id) in (select max(salary), department_id from employees group by department_id) order by salary;

select employee_id, first_name || ' ' || last_name "Full Name", department_id, salary from employees, (select max(salary) sal, department_id deptno from employees group by department_id) where salary = sal and department_id = deptno order by salary;