Database
SQL
Restricting and Sorting Data
Pascal TUFENKJI 1 Database SQL
Objectives
After completing this lesson, you should
be able to do the following:
• Limit the rows retrieved by a query
• Sort the rows retrieved by a query
Pascal TUFENKJI 2 Database SQL
Limiting Rows Using a Selection
“… retrieve all
employees in
department 10”
Pascal TUFENKJI 3 Database SQL
Limiting Rows Selected
• Restrict the rows returned by using the
WHERE clause
SELECT [DISTINCT] {*| column [alias],…}
FROM table
WHERE condition(s)];
• The WHERE clause follows the FROM
clause.
Pascal TUFENKJI 4 Database SQL
Using the WHERE Clause
SELECT ename, job, deptno FROM emp
WHERE job = 'CLERK';
Pascal TUFENKJI 5 Database SQL
Character Strings and Dates
• Character strings and date values are enclosed in
single quotation marks.
• Character values are case sensitive and date values are
format sensitive.
• The default date format is different from one
database to another
Database Date format
PostgreSQL & MySQL & SQLite YYYY-MM-DD
Oracle DD-MON-YY
SQL Server & MS Access MM/DD/YYYY
(depending on the Regional Options)
Pascal TUFENKJI 6 Database SQL
Character Strings and Dates
SELECT ename, job, deptno FROM emp
WHERE deptno = 10;
SELECT ename, job, deptno FROM emp
WHERE job = 'CLERK';
SELECT ename, job, deptno FROM emp
WHERE hiredate = '1981-11-17';
Pascal TUFENKJI 7 Database SQL
Comparison Operators
Operator Meaning
= Equal to
> Greater than
>= Greater than or equal to
< Less than
<= Less than or equal to
<>
Not equal to
!=
Pascal TUFENKJI 8 Database SQL
Using the Comparison Operators
SELECT ename, sal, comm FROM emp
WHERE sal < comm;
Pascal TUFENKJI 9 Database SQL
Using the Comparison Operators
SELECT empno, ename, job, mgr FROM emp
WHERE mgr <> 7839;
Null values cannot be compared to any other values
Pascal TUFENKJI 10 Database SQL
Other Comparison Operators
Operator Meaning
BETWEEN
Between two values (inclusive)
…AND…
IN (list) Match any of a list values
LIKE Match a character pattern
IS NULL Is a null value
Pascal TUFENKJI 11 Database SQL
Using the BETWEEN Operator
• Use the BETWEEN operator to display
rows based on a range of values.
SELECT ename, sal FROM emp
WHERE sal BETWEEN 1000 AND 5000;
Lower limit Higher limit
Pascal TUFENKJI 12 Database SQL
Using the BETWEEN Operator
• The BETWEEN operator can also be used with date
and character datatypes.
SELECT ename, hiredate FROM emp WHERE hiredate
BETWEEN '1981-01-01' AND '1981-12-31';
SELECT ename FROM emp
WHERE ename BETWEEN 'A' AND 'D';
Pascal TUFENKJI 13 Database SQL
Using the IN Operator
• Use the IN operator to test for values
in a list.
SELECT empno, ename, sal, mgr FROM emp
WHERE mgr IN (7902, 7566, 7788);
Pascal TUFENKJI 14 Database SQL
Using the IN Operator
• The IN operator can be used with any
datatype.
• If characters or dates are used in the list,
they must be enclosed in single quotation
marks ('').
SELECT empno, ename, mgr, deptno FROM emp
WHERE ename IN ('FORD', 'ALLEN');
Pascal TUFENKJI 15 Database SQL
Using the LIKE Operator
• Use the LIKE operator to perform
wildcard searches of valid search string
values.
• Search conditions can contain either
literal characters or numbers.
– % denotes zero or many characters.
– _ denotes one character.
Note: In MS Access, the special character
❑ % is replaced by *
❑ _ is replaced by ?
Pascal TUFENKJI 16 Database SQL
Using the LIKE Operator
• LIKE is case sensitive in some databases
SELECT ename FROM emp
WHERE ename LIKE 'S%';
• LIKE can be used as a shortcut for
some BETWEEN comparisons
SELECT ename, hiredate FROM emp
WHERE hiredate LIKE '1981%';
Pascal TUFENKJI 17 Database SQL
Using the LIKE Operator
• You can combine pattern-matching
characters.
SELECT ename FROM emp
WHERE ename LIKE '_A%';
Pascal TUFENKJI 18 Database SQL
Using the IS NULL Operator
• Test for null values with the IS NULL
operator.
SELECT ename, mgr FROM emp
WHERE mgr IS NULL;
Pascal TUFENKJI 19 Database SQL
Logical Operators
Operator Meaning
Returns TRUE if both
AND
component conditions are TRUE
Returns TRUE if either
OR
component condition are TRUE
Returns TRUE if the following
NOT
condition is FALSE
Pascal TUFENKJI 20 Database SQL
Using the AND Operator
AND requires both conditions to be TRUE
SELECT empno, ename, job, sal FROM emp
WHERE sal >= 1100 AND job = 'CLERK';
Pascal TUFENKJI 21 Database SQL
AND Truth Table
• The following table shows the results of
combining two expressions with AND.
Expression 1 Expression 2 Result
TRUE TRUE TRUE
TRUE FALSE FALSE
FALSE TRUE FALSE
FALSE FALSE FALSE
Pascal TUFENKJI 22 Database SQL
Using the OR Operator
OR requires either conditions to be TRUE
SELECT empno, ename, job, sal FROM emp
WHERE sal >= 1100 OR job = 'CLERK';
Pascal TUFENKJI 23 Database SQL
OR Truth Table
• The following table shows the results of
combining two expressions with OR.
Expression 1 Expression 2 Result
TRUE TRUE TRUE
TRUE FALSE TRUE
FALSE TRUE TRUE
FALSE FALSE FALSE
Pascal TUFENKJI 24 Database SQL
Using the NOT Operator
SELECT ename, job FROM emp
WHERE job NOT IN ('CLERK','MANAGER','ANALYST');
Pascal TUFENKJI 25 Database SQL
Using the NOT Operator
• The NOT operator can also be used with
other SQL operators, such as
BETWEEN, LIKE, and NULL.
… WHERE NOT job IN ('CLERK','MANAGER','ANALYST');
… WHERE sal NOT BETWEEN 1000 AND 1500;
… WHERE ename NOT LIKE '%A%';
… WHERE comm is NOT NULL;
Pascal TUFENKJI 26 Database SQL
Rules of Precedence
Order Evaluated Operator
1 Arithmetic operators
2 Concatenation operator
3 Comparison conditions
4 IS [NOT] NULL, LIKE, [NOT] IN
5 [NOT] BETWEEN
6 Not equal to
7 NOT logical condition
8 AND logical condition
9 OR logical condition
Override rules of precedence by using
parentheses.
Pascal TUFENKJI 27 Database SQL
Rules of Precedence
SELECT ename, job, sal FROM emp
WHERE job = 'SALESMAN'
OR job = 'PRESIDENT'
AND sal > 1500;
Pascal TUFENKJI 28 Database SQL
Rules of Precedence
• Use parentheses to force priority
SELECT ename, job, sal FROM emp
WHERE (job = 'SALESMAN'
OR job = 'PRESIDENT')
AND sal > 1500;
Pascal TUFENKJI 29 Database SQL
ORDER BY Clause
• Sort rows with the ORDER BY clause
– ASC: ascending order (default)
– DESC: descending order
• The ORDER BY clause comes last in the
SELECT statement.
Pascal TUFENKJI 30 Database SQL
ORDER BY Clause
SELECT ename, job, deptno, hiredate
FROM emp
ORDER BY hiredate;
Pascal TUFENKJI 31 Database SQL
Sorting in Descending Order
SELECT ename, job, deptno, hiredate
FROM emp
ORDER BY hiredate DESC;
Pascal TUFENKJI 32 Database SQL
Sorting by Column Alias
SELECT empno, ename, job, sal*12 as annsal
FROM emp
ORDER BY annsal;
Pascal TUFENKJI 33 Database SQL
Sorting by Multiple Columns
• The order of ORDER BY list is the
order of sort.
SELECT ename, deptno, sal
FROM emp
ORDER BY deptno, sal DESC;
Pascal TUFENKJI 34 Database SQL
Sorting by Multiple Columns
SELECT empno, ename, job, deptno
FROM emp
ORDER BY job DESC, ename;
You can sort by a column that is not in the SELECT list.
Pascal TUFENKJI 35 Database SQL
DISTINCT & ORDER BY Clause
• The DISTINCT keyword eliminates the
duplicates rows and sets the columns in an
ascending order automatically.
• When using the SELECT DISTINCT, the
sorting of the query is the order of the
columns, unless it’s specified differently in
the ORDER BY clause.
• ORDER BY items must appear in the select
list if SELECT DISTINCT is specified.
Pascal TUFENKJI 36 Database SQL
DISTINCT & ORDER BY Clause
SELECT distinct job, ename FROM emp;
SELECT distinct job, ename FROM emp
ORDER BY ename, job;
Pascal TUFENKJI 37 Database SQL
DISTINCT & ORDER BY Clause
SELECT distinct job, ename FROM emp
ORDER BY deptno;
SELECT distinct job, ename, deptno FROM emp
ORDER BY deptno;
Pascal TUFENKJI 38 Database SQL
Limiting the results
• To limit the number of rows returned by a query, you
often use the LIMIT clause.
• However, the LIMIT clause is not a SQL-standard.
• To conform with the SQL standard, PostgreSQL
provides the FETCH clause to retrieve a portion of
rows returned by a query.
• Note that the FETCH clause was introduced in
SQL:2008.
Pascal TUFENKJI 39 Database SQL
FETCH & OFFSET
• The following illustrates the syntax of the
row limiting clause:
• In this syntax:
– ROW and FIRST are synonymous with ROWS and
NEXT respectively.
– The start is an integer that must be zero or positive.
By default, it is zero if the OFFSET clause is not
specified.
– The row_count is one or higher. By default, the value of
row_count is one if it is not specified.
Pascal TUFENKJI 40 Database SQL
FETCH & OFFSET
• The following query returns the first row of the film
sorted by title
• The following statement returns the next five films
after the first five films sorted by the title:
Pascal TUFENKJI 41 Database SQL
Limiting the results
• Limiting results in a query differ from one
database to another
Database Keyword
PostgreSQL Limit x Offset y
MySQL Limit x, y
SQL Server Top x
Oracle Rownum
Pascal TUFENKJI 42 Database SQL
LIMIT & OFFSET Clause in Postgres
• If a LIMIT count is given, no more than
that many rows will be returned.
• OFFSET says to skip that many rows
before beginning to return rows.
Pascal TUFENKJI 43 Database SQL
LIMIT & OFFSET Clause in Postgres
[LIMIT { number | ALL } ] [ OFFSET number ];
• LIMIT ALL is the same as omitting the
LIMIT clause.
• OFFSET 0 is the same as omitting the
OFFSET clause
Pascal TUFENKJI 44 Database SQL
LIMIT Clause
SELECT empno, ename FROM emp
ORDER BY empno;
SELECT empno, ename FROM emp
ORDER BY empno limit 5;
Pascal TUFENKJI 45 Database SQL
OFFSET Clause
SELECT empno, ename FROM emp
ORDER BY empno;
SELECT empno, ename FROM emp
ORDER BY empno offset 5;
Pascal TUFENKJI 46 Database SQL
Both OFFSET and LIMIT Clause
SELECT empno, ename FROM emp
ORDER BY empno;
SELECT empno, ename FROM emp
ORDER BY empno limit 5 offset 5;
Pascal TUFENKJI 47 Database SQL
TOP and Offset rows in SQL Server
• Unfortunately, TOP can not be used in the
same query or sub-query as a OFFSET.
SELECT TOP 10 * FROM `your_table`;
This will display the first 10 results from the database.
SELECT TOP 50 percent * FROM `your_table`;
This will display half the results from the database.
SELECT * FROM `your_table` OFFSET 5 ROWS;
This will omit 5 results from the database.
Pascal TUFENKJI 48 Database SQL
TOP
SELECT empno, ename FROM emp
ORDER BY empno;
SELECT TOP 5 empno, ename FROM emp
ORDER BY empno;
Pascal TUFENKJI 49 Database SQL
OFFSET ROWS
SELECT empno, ename FROM emp
ORDER BY empno;
SELECT empno, ename FROM emp
ORDER BY empno OFFSET 5 ROWS;
Pascal TUFENKJI 50 Database SQL
LIMIT in MySQL
• You can use it to show the first X number of results, or to
show a range from X - Y results.
• It is phrased as Limit X, Y and included at the end of your
query.
• X is the starting point (remember the first record is 0)
and Y is the duration (how many records to display).
SELECT * FROM `your_table` LIMIT 0, 10;
This will display the first 10 results from the database.
SELECT * FROM `your_table` LIMIT 5, 5;
This will show records 6, 7, 8, 9, and 10
Pascal TUFENKJI 51 Database SQL
FETCH in Oracle
• The following illustrates the syntax of the
row limiting clause:
Pascal TUFENKJI 52 Database SQL
FETCH in Oracle
• OFFSET clause
– If the offset is negative, then it is treated
as 0.
– If the offset is NULL or greater than the
number of rows returned by the query,
then no row is returned.
– If the offset includes a fraction, then the
fractional portion is truncated.
Pascal TUFENKJI 53 Database SQL
FETCH in Oracle
• FETCH clause
– The FETCH clause specifies the number of rows or
percentage of rows to return.
– you can use the keyword ROW instead of ROWS,
FIRST instead of NEXT
– The ONLY returns exactly the number of rows or
percentage of rows after FETCH NEXT (or
FIRST).
– The WITH TIES returns additional rows with the
same sort key as the last row fetched. Note that
if you use WITH TIES, you must specify an
ORDER BY clause in the query. If you don’t, the
query will not return the additional rows.
Pascal TUFENKJI 54 Database SQL
FETCH in Oracle
SELECT val
FROM rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;
Pascal TUFENKJI 55 Database SQL
FETCH in Oracle
SELECT val
FROM rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS WITH TIES;
Pascal TUFENKJI 56 Database SQL
Summary
In this lesson, you should have learned how to:
• Use the WHERE clause to restrict rows of
output:
– Use the comparison conditions
– Use the BETWEEN, IN, LIKE, and NULL conditions
– Apply the logical AND, OR, and NOT operators
• Use the ORDER BY clause to sort rows of output:
SELECT [DISTINCT] {*| column [alias],…}
FROM table
[WHERE condition(s)]
[ORDER BY {column, expr, alias} {ASC|DESC]]
[LIMIT { number | ALL } ] [ OFFSET number ];
Pascal TUFENKJI 57 Database SQL
