Retrieving and manipulating Data in Oracle & Sql server 2008
Restricting and Sorting Data
After completing this lesson, you should be able to do the following:
¡ Limit the rows that are retrieved by a query
¡ Sort the rows that are retrieved by a query
Using the WHERE Clause
SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [WHERE condition(s)];
Selecting All Columns
SELECT employee_id, last_name, job_id, department_id FROM employees
WHERE department_id = 90 ;
Character Strings and Dates
• Character strings and date values are enclosed by single quotation marks.
• Character values are case-sensitive, and date values are format-sensitive.
• To know the default date format
• SELECT Current_Date FROM DUAL; à Oracle
• SELECT GETDATE() AS CurrentDateTime; à Sql Server
Comparison Conditions
Operator
|
Description
|
=
|
Equal to
|
>
|
Greater than
|
>=
|
Greater than or equal to
|
<
|
Less than
|
<=
|
Less than or equal to
|
<>
|
Not equal to
|
BETWEEN...AND...
|
Between two values (inclusive)
|
IN(set)
|
Match any of a list of values
|
LIKE
|
Match a character pattern
|
IS NULL
|
Is a null value
|
Using Comparison Conditions
§ SELECT last_name, salary FROM employees WHERE salary <= 3000 ;
§ SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500 ;
§ SELECT employee_id, last_name, salary, manager_id FROM employees
WHERE manager_id IN (100, 101, 201) ;
Using the LIKE Condition
- Use the LIKE condition 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.
SELECT first_name FROM employees WHERE first_name LIKE 'S%' ;
- You can use the ESCAPE identifier to search for the actual % and _ symbols.
- You can combine pattern-matching characters.
Using the NULL Conditions
§ SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL ;
Logical Conditions
Operator
|
Description
|
AND
|
Returns TRUE if both component conditions are true
|
OR
|
Returns TRUE if either component condition is true
|
NOT
|
Returns TRUE if the following condition is false
|
§ SELECT employee_id, last_name, job_id, salary
FROM employees WHERE salary >=10000 AND job_id LIKE '%MAN%' ;
§ SELECT employee_id, last_name, job_id, salary
FROM employees WHERE salary >= 10000 OR job_id LIKE '%MAN%' ;
§ SELECT last_name, job_id
FROM employees WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP') ;
Rules of Precedence
Order
|
Description
|
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
|
Using the ORDER BY Clause
- Sort retrieved rows with the ORDER BY clause:
- ASC: ascending order, default
- DESC: descending order
- The ORDER BY clause comes last in the SELECT statement:
SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date ;
Post a Comment