Table of contents:

  1. Using AVG exampele I
  2. Using AVG exampele II
  3. Table aliases example I
  4. Column aliases example II
  5. Inner join
  6. Left outer join

This post is the continuation of the previous post. Below is the database schema:

Database Schema
Database Schema
  • Using AVG exampele I

select avg(salary)
from employee;
  • Using AVG exampele II

select department_id, avg(salary)
from employee
group by department_id
order by avg(salary);
  • Table aliases example I

select employee_id, first_name, last_name
from employee e;

select employee_id, first_name, last_name, e.salary
from employee e
where e.salary < 30000;
  • Column aliases example II

select e.employee_id AS "Empployee", e.last_name AS "Last name", e.first_name AS "First name" , e.salary AS "Salary" 
from employee e;

select e.employee_id as "Employee", e.first_name as "First name", e.last_name as "Last name", e.salary as "Salary", e.salary/12 as "1 month salary",
e.salary/56 as "56/salary", e.salary * 12 as "Salary multiply per 12"
from employee e;
  • Inner join

select e.employee_id,  e.first_name, e.last_name, 
d.department_name, d.department_id
from employee e
join department d on e.department_id = d.department_id
where e.salary > 6000; 
  • Left outer join

select c.customer_id, c.first_name, c.last_name,
co.order_date
from customer c
inner join customer_order co ON c.customer_id = co.customer_id;

Reference:

  1. SQL queries, part I
  2. Oracle SQL Developer
  3. Microsoft SQL documentation