Querying A Single Table
Overview
WHERE conditions
ORDER-BY: Sorting Data
Single Row Functions
- Selection without conditions. The syntax is as follows:
SELECT [DISTINCT] <column name, ...> FROM
<table name,...>;
SELECT identifies what columns to retrieve; FROM identified which table to
retrieve.
For example,
SQL> SELECT id FROM employee;
- Selection with conditions. This retrieves data
from a table with a condition. The syntax is:
SELECT [DISTINCT] <column name, ...> FROM <table
name> WHERE <conditions>;
WHERE identifies
condition(s). Multiple conditions are joined by operators such as AND and
OR.
For
example,
SQL> SELECT id FROM employee WHERE name='scott'
AND title='CFO';
- Select ALL operator *. This retrieves all columns from a
table.
For example,
SQL> SELECT * FROM employee WHERE name='scott';
- Arithmetic operations. The +, -, *, / operators can be applied on
columns of NUMBER type and the operator precedence follows common rules.
Parentheses can be used as well.
For example,
SQL> SELECT id, name, salary * 12 + 100 FROM
employee;
- Column Alias. Column alias allows you to rename a column heading.
For example,
SQL> SELECT
id, name AS "Name", salary * 12 AS "Annual salary" FROM employee;
- Concatenation operator || and literal character strings. The
concatenation operator || allows you to merge the concatenated column values
into a single column value. Literal character strings can be included in
the SELECT clause and must be enclosed in single quotation marks.
For example,
SQL> SELECT name
|| ' is a ' || jobtitle AS 'Employee Detaisl' FROM employee;
The output looks like:
Employee Details
----------------------
KING is a PRESIDENT
SCOTT is a CFO
JANE is a CTO
....
12 rows selected.
- Display non-duplicated values. The default display of query results
are all rows including duplicated rows. Use the DISTINCT keyword to eliminate
the duplicates.
For example,
SQL> SELECT
DISTINCT dept_id FROM employee;
- Characters strings and date values are enclosed in single quotation marks.
Strings are case-sensitive and dates are format-sensitive. The default date
format is DD-MON-YY.
In WHERE conditions, the comparison operators are >, >=, <,
<=, =, <>, BETWEEN ... AND... , IN(list), LIKE, and IS
NULL. The logical operators are NOT, AND, and OR.
- BETWEEN ... AND ... operators displays rows based on a range of
values.
For example,
SQL> SELECT ename, sal FROM employee WHERE sal
BETWEEN 1000 and 1500;
- IN(list) operator tests values in a list.
For example,
SQL> SELECT ename, sal, dept_no
FROM employee WHERE dept_no IN (10,20,30);
- LIKE operator performs wild char searches of strings. Search patterns can
contain literals or numbers. % denotes zero or many characters and - denotes
only one character.
For example,
SQL> SELECT ename FROM employee
WHERE ename LIKE 'W_N%H';
- IS NULL operator tests if a value is NULL.
For example,
SQL> SELECT ename, sal FROM
employee WHERE comment IS NOT NULL;
The rule of precedence is: all comparison operators are followed by NOT, then
by AND, then followed by OR.
Rows are sorted with the ORDER BY clause with two ordering options: ASC
(ascending order, the default) and DESC (descending order).
The ORDER-BY clause comes last in the SELECT statements.
For example,
SQL> SELECT ename, sal FROM
employee ORDER BY sal DESC;
Single row functions take zero or more number of arguments and return
only one value. They act on per data row. There are 4 types of SQL single-row
functions:
1) string functions. Some are:
- LOWER, UPPER, Convert the case of the whole string.
- SUBSTR(string,m,n), Extract the substring from offset m to n.
- CONCAT(s1,s2), Concatenate string s2 to s1.
- LENGTH(s), Return length of the string.
For example,
SQL> SELECT name, LENGTH(name) FROM EMPLOYEE
WHERE UPPER(name) = 'SCOTT';
2) number functions
- ROUND, rounds values to specified decimals. e.g., ROUND(2.35, 1)
returns 2.36.
- TRUNC, truncates values to specified decimals. e.g., TRUNC(2.35, 1)
returns 2.3.
- MOD, returns remainder of division. e.g. MOD(500,200) returns 100.
3) date functions
- SYSDATE is a function that returns the current server's date and time.
- Arithmetic operators can be applied with dates to find the time elapsed.
Substract two dates to find the number of days in between.
For example,
SQL> SELECT name, (SYSDATE - hireDate) AS Days FROM
EMPLOYEE;
WHERE UPPER(name) = 'SCOTT';
4) conversion functions
- TO_CHAR function: converts numbers and dates into string format. For
example, TO_CHAR(SYSDATE , 'DD-MM-YYYY DAY") returns '19-07-2002
FRIDAY', TO_CHAR(67553, '$99,999') returns $67,553.
- TO_NUMBER function: converts a string into a numeric value.
- TO_DATE function: converts a string into a date.