#Returning Query Result in a Specified Order Display the names, job, and salaries of employees in department 10 in order based on their salary.
select name, job, sal
from emp
where depno = 10
order by sal asc
The order by allows you to order the rows of your result and then in ascending order, or you can use DESC to sort in descending order
#Sorting by Multiple Fields You want to sort the rows from EMP first by DEPTNO ascending and then by salary descending.
select empno, deptno, sal, ename, job
from emp
order by deptno, sal desc
#Sorting by Substrings Sort by the last two characters in the job field
select ename,job
from emp
order by substr(job,length(job)-1)
#Dealing with Nulls when Sorting when you sort something by a column and there are nulls in there. Make sure nulls sort last.
select ename, sal, comm
from emp
order by 3
#Use extra column to do the sorting(case)
This extra column would allow you to identify NULL value and sort them altogether.
select ename,sal,comm,
case when comm is null then 0 else 1 end as is_null
from emp
#Sort based on some conditional logic, if job is salesman sort on comm, otherwise, sort by sal
select ename, sal, job, comm
from emyp
order by case when job = 'SALESMAN' then comm else sal end
#Working with Multiple Tables
Joins are the foundation of SQL. Set operations count too!!!
Use UNION ALL to combine rows from multiple tables. If you want to get rid of duplicates, you can just use UNION. But keep in mind, they must match in number and data type.
#Combine related Rows join two table sharing the same column
select e.name, d.loc
from emp e, dep d
where e.depno = d.depno
and e.depno = 10
#Retriving Values from One table that does not exsit in Another Do you want to the specfic coloumn in table one that does not exsit in table two?
select depno
from dept
where depno not in (select depno in emp)