SQL queries, part II
Table of contents:
- Using AVG exampele I
- Using AVG exampele II
- Table aliases example I
- Column aliases example II
- Inner join
- Left outer join
This post is the continuation of the previous post. Below is the 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
— SQL Daily (@sqldaily) September 16, 2021
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
Reference:
My site is free of ads and trackers. Was this post helpful to you? Why not
Disqus is great for comments/feedback but I had no idea it came with these gaudy ads.