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

• 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;

A LEFT (OUTER) JOIN returns

All the rows from the table on the left side of the join
Any rows matching the join criteria from the table on the right

If there are no matching rows in the right table, the result show NULL for these columns

A RIGHT JOIN does the opposite#SQL pic.twitter.com/hQRMCvza1v

— SQL Daily (@sqldaily) September 16, 2021

---

Reference:

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

My site is free of ads and trackers. Was this post helpful to you? Why not