Samples from SQL Cookbook¶
In this article will be used two tables, EMP and DEPT.
The EMP table (14-row table, numeric, string, and date fields).
The DEPT table is a simple four-row table with only numeric and string fields.
Besides we are facing the T1, T10, T100, and T500 pivot tables. The pivot tables are a useful shortcut when we need to create a series of rows to facilitate a query.
Subqueries FROM FROM¶
Many solutions make use of inline views, or subqueries in the FROM clause with mandatory table aliases for such view.
select job, sal from (select job, max( sal) sal from emp group by job) x;
Retrieve records¶
several conditions satisfied¶
Recipy:
Multiple conditions after WHERE separated by the OR and AND clauses are true for any row.
FROM executed before WHERE¶
The results from the query below are generated before the outermost WHERE clause, and your outermost WHERE clause "sees" the alias names.
select *
from (
select sal as salary, comm as commission
from emp
) x
where salary < 5000
IF-ELSE logic in a SELECT¶
produce a result set such that if an employee is paid 2,000 or less, a message of 'UNDERPAID' is returned; if an employee is paid 4,000 or more, a message of 'OVERPAID' is returned; and if they make somewhere in between, then 'OK' is returned. The result set should look like this:
CASE expression is used to perform conditional logic.
select ename, sal,
case when sal < = 2000 then 'UNDERPAID'
when sal > = 4000 then
'OVERPAID'
else
'OK'
end as status
from emp
Query limited number of rows¶
Postgresql
SELECT *
FROM my_table limit 5
SQLServer
SELECT ename, job
FROM my_table
ORDER BY random() limit 5
Query n random records¶
Return n random records from a table.
In postgresql you chain ORDER BY RANDOM LIMIT
Postgresql
SELECT *
FROM my_table limit 5
SQLServer uses combination of TOP ORDER BY NEWID.
select top 5 ename, job
from emp
order by newid()
Managing NULL¶
You can filter records whose particular column is NULL and even return non-null values in place of NULL.
select * 2 from emp 3 where comm is null
select coalesce( comm, 0) 2 from emp
There the value of COMM is returned whenever COMM is not null.
Pattern Matching¶
A particular substring or pattern. Consider the following need: get record with int value within a given range, or why not find substring in a string.
select ename, job from emp where deptno in (10,20)
Of the employees in departments 10 and 20, you want to return only those that have either an "I" somewhere in their name or a job title ending with "ER":
select ename, job 2 from emp 3 where deptno in (10,20) 4 and (ename like '% I%' or job like '%ER')
Specify Order¶
You want to display the names, jobs etc of n random records in order based on their registered date (from oldest to newest).
select * from (
select top 5 ename, job, edited
from emp
order by newid())
order by edited
ename, job, sal from emp where deptno = 10 order by sal desc
Return Ordered by Multiple Columns¶
You want to sort the rows from SAMPLE_TABLE first by non unique DEPT_ID ascending, then by EDITED descending.
This may be achieved in one query by including different sort columns in the ORDER BY clause, separated by commas:
``` select empno, dept_id, sal, ename, job
from emp
order by deptno, edited desc
### SQL Joins with Countries
I want to describe an example of using SQL JOIN clauses to retrieve data from two tables: the countries table and the nobel_prizes table.
The countries table contains information about different countries, including their names and populations. It has the following structure:
countries
+------------+-------------+
| country_id | country |
+------------+-------------+
| 1 | USA |
| 2 | Canada |
| 3 | France |
| 4 | Germany |
| 5 | UK |
+------------+-------------+
The nobel_prizes table contains information about Nobel Prize winners, including their names, the year they won the prize, and the country they are from. It has the following structure:
nobel_prizes
+------------+-----------------+------+------------+
| prize_id | winner | year | country_id |
+------------+-----------------+------+------------+
| 1 | Albert Einstein | 1921 | 1 |
| 2 | Marie Curie | 1903 | 3 |
| 3 | Albert Einstein | 1922 | 1 |
| 4 | Marie Curie | 1904 | 3 |
| 5 | Niels Bohr | 1922 | 4 |
+------------+-----------------+------+------------+
To retrieve the names of the Nobel Prize winners and the countries they are from, we can use a JOIN clause like this:
SELECT winner, countries.country
FROM nobel_prizes
JOIN countries ON nobel_prizes.country_id = countries.country_id
This would return the following result:
+-----------------+---------+
| winner | country |
+-----------------+---------+
| Albert Einstein | USA |
| Marie Curie | France |
| Albert Einstein | USA |
| Marie Curie | France |
| Niels Bohr | Germany |
+-----------------+---------+
There are several different types of JOIN clauses that you can use, including INNER JOIN, OUTER JOIN, LEFT JOIN, and RIGHT JOIN. The type of JOIN you use will depend on the specific requirements of your query.