copyright © 2004, oracle. all rights reserved. lecture 4: 1-retrieving data using the sql select...
TRANSCRIPT
![Page 1: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/1.jpg)
Copyright © 2004, Oracle. All rights reserved.
Lecture 4: Lecture 4: 1-Retrieving Data Using 1-Retrieving Data Using the SQL the SQL SELECTSELECT Statement Statement 2-Restricting and Sorting 2-Restricting and Sorting
DataData
ORACLEORACLE
![Page 2: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/2.jpg)
ObjectivesObjectives
After completing this lesson, you should be able to do the following:◦List the capabilities of SQL SELECT
statements◦Execute a basic SELECT statement
![Page 3: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/3.jpg)
Capabilities of SQL Capabilities of SQL SELECTSELECT StatementsStatements
SelectionProjection
Table 1 Table 2
Table 1Table 1
Join
![Page 4: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/4.jpg)
Basic Basic SELECTSELECT Statement Statement
◦ SELECT identifies the columns to be displayed◦ FROM identifies the table containing those columns
SELECT *|{[DISTINCT] column|expression [alias],...}FROM table;
![Page 5: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/5.jpg)
Selecting All ColumnsSelecting All Columns
SELECT *FROM departments;
![Page 6: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/6.jpg)
Selecting Specific Selecting Specific ColumnsColumns
SELECT department_id, location_idFROM departments;
![Page 7: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/7.jpg)
Writing SQL StatementsWriting SQL Statements
◦SQL statements are not case-sensitive. ◦SQL statements can be on one or more
lines.◦Keywords cannot be abbreviated or split
across lines.◦Clauses are usually placed on separate
lines.◦Indents are used to enhance readability.◦In SQL*plus, you are required to end
each SQL statement with a semicolon (;).
![Page 8: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/8.jpg)
Arithmetic ExpressionsArithmetic Expressions
Create expressions with number and date data by using arithmetic operators.
OperatorDescription
+Add
-Subtract
*Multiply
/Divide
![Page 9: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/9.jpg)
SELECT last_name, salary, salary + 300FROM employees;
Using Arithmetic Using Arithmetic OperatorsOperators
…
![Page 10: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/10.jpg)
SELECT last_name, salary, 12*salary+100FROM employees;
Operator PrecedenceOperator Precedence
SELECT last_name, salary, 12*(salary+100)FROM employees;
…
…
1
2
![Page 11: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/11.jpg)
Defining a Null ValueDefining a Null Value
◦ A null is a value that is unavailable, unassigned, unknown, or inapplicable.
◦ A null is not the same as a zero or a blank space.
SELECT last_name, job_id, salary, commission_pctFROM employees;
…
…
![Page 12: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/12.jpg)
SELECT last_name, 12*salary*commission_pctFROM employees;
Null Values Null Values in Arithmetic Expressionsin Arithmetic Expressions
Arithmetic expressions containing a null value evaluate to null.
…
…
![Page 13: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/13.jpg)
Defining a Column AliasDefining a Column Alias
A column alias:◦Renames a column heading◦ Is useful with calculations◦ Immediately follows the column name
(There can also be the optional AS keyword between the column name and alias.)
◦Requires double quotation marks if it contains spaces or special characters or if it is case-sensitive
![Page 14: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/14.jpg)
Using Column AliasesUsing Column Aliases
SELECT last_name "Name" , salary*12 "Annual Salary"FROM employees;
SELECT last_name AS name, commission_pct commFROM employees;
…
…
![Page 15: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/15.jpg)
Concatenation OperatorConcatenation Operator
A concatenation operator:◦ Links columns or character strings to
other columns ◦ Is represented by two vertical bars (||)◦ Creates a resultant column that is a
character expression
SELECT last_name||job_id AS "Employees"FROM employees;
…
![Page 16: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/16.jpg)
Literal Character StringsLiteral Character Strings
◦A literal is a character, a number, or a date that is included in the SELECT statement.
◦Date and character literal values must be enclosed by single quotation marks.
◦Each character string is output once for eachrow returned.
![Page 17: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/17.jpg)
Using Literal Character Using Literal Character StringsStrings
…
SELECT last_name ||' is a '||job_id AS "Employee Details"FROM employees;
![Page 18: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/18.jpg)
Alternative Quote (Alternative Quote (qq) ) OperatorOperator◦Specify your own quotation mark
delimiter◦Choose any delimiter◦Increase readability and usabilitySELECT department_name ||
q'[, it's assigned Manager Id: ]' || manager_id AS "Department and Manager" FROM departments;
…
![Page 19: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/19.jpg)
Duplicate RowsDuplicate Rows
The default display of queries is all rows, including duplicate rows.
SELECT department_idFROM employees;
…
SELECT DISTINCT department_idFROM employees;
…
1
2
![Page 20: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/20.jpg)
SummarySummary
In this lesson, you should have learned how to: ◦Write a SELECT statement that:
Returns all rows and columns from a table
Returns specified columns from a table Uses column aliases to display more
descriptive column headings
SELECT *|{[DISTINCT] column|expression [alias],...}FROM table;
![Page 21: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/21.jpg)
Practice 1Practice 1::1- The following SELECT statement executes successfully:
SELECT last_name, job_id, salary AS Sal
FROM employees;
True/False
2-The following SELECT statement executes successfully:
SELECT *
FROM job_grades;
True/False
3-There are four coding errors in the following statement. Can you identify them?
SELECT employee_id, last_name
sal x 12 ANNUAL SALARY
FROM employees;
![Page 22: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/22.jpg)
Practice 1Practice 1: :
4-The HR department needs a query to display all unique job codes from the EMPLOYEES table.
5-The HR department has requested a report of all employees and their job IDs. Display the last name concatenated with the job ID (separated by a comma and space) and name the column Employee and Title.
![Page 23: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/23.jpg)
Copyright © 2004, Oracle. All rights reserved.
22--Restricting and Sorting Restricting and Sorting
DataData
ORACLEORACLE
![Page 24: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/24.jpg)
ObjectivesObjectives
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
![Page 25: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/25.jpg)
Limiting Rows Using a Limiting Rows Using a SelectionSelection
“retrieve allemployees in department 90”
EMPLOYEES
…
![Page 26: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/26.jpg)
Limiting the Rows That Are Limiting the Rows That Are SelectedSelected
◦ Restrict the rows that are returned by using the WHERE clause:
◦ The WHERE clause follows the FROM clause.
SELECT *|{[DISTINCT] column | expression [alias],...}FROM table[WHERE condition(s)];
![Page 27: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/27.jpg)
SELECT employee_id, last_name, job_id, department_idFROM employeesWHERE department_id = 90 ;
Using the Using the WHEREWHERE Clause Clause
![Page 28: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/28.jpg)
SELECT last_name, job_id, department_idFROM employeesWHERE last_name = 'Whalen' ;
Character Strings and Character Strings and DatesDates
◦Character strings and date values are enclosed by single quotation marks.
◦Character values are case-sensitive, and date values are format-sensitive.
◦The default date format is DD-MON-RR.
![Page 29: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/29.jpg)
Comparison ConditionsComparison Conditions
OperatorMeaning
=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
LIKEMatch a character pattern
IS NULLIs a null value
![Page 30: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/30.jpg)
SELECT last_name, salaryFROM employeesWHERE salary <= 3000 ;
Using Comparison Using Comparison ConditionsConditions
![Page 31: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/31.jpg)
SELECT last_name, salaryFROM employeesWHERE salary BETWEEN 2500 AND 3500 ;
Using the Using the BETWEENBETWEEN ConditionCondition
Use the BETWEEN condition to display rows based on a range of values:
Lower limit Upper limit
![Page 32: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/32.jpg)
SELECT employee_id, last_name, salary, manager_idFROM employeesWHERE manager_id IN (100, 101, 201) ;
Using the Using the ININ Condition Condition
Use the IN membership condition to test for values in a list:
![Page 33: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/33.jpg)
SELECT first_nameFROM employeesWHERE first_name LIKE 'S%' ;
Using the Using the LIKELIKE Condition 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.
![Page 34: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/34.jpg)
Using the Using the LIKELIKE Condition Condition◦You can combine pattern-matching
characters:SELECT last_nameFROM employeesWHERE last_name LIKE '_o%' ;
![Page 35: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/35.jpg)
SELECT last_name, manager_idFROM employeesWHERE manager_id IS NULL ;
Using the Using the NULLNULL Conditions Conditions
Test for nulls with the IS NULL operator.
![Page 36: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/36.jpg)
Logical ConditionsLogical Conditions
OperatorMeaning
ANDReturns TRUE if both component conditions are true
ORReturns TRUE if either component condition is true
NOTReturns TRUE if the following condition is false
![Page 37: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/37.jpg)
SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary >=10000AND job_id LIKE '%MAN%' ;
Using the Using the ANDAND Operator Operator
AND requires both conditions to be true:
![Page 38: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/38.jpg)
SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary >= 10000OR job_id LIKE '%MAN%' ;
Using the Using the OROR Operator Operator
OR requires either condition to be true:
![Page 39: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/39.jpg)
SELECT last_name, job_idFROM employeesWHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP') ;
Using the Using the NOTNOT Operator Operator
![Page 40: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/40.jpg)
Rules of PrecedenceRules of Precedence
You can use parentheses to override rules of precedence.
OperatorMeaning
1Arithmetic operators
2Concatenation operator
3Comparison conditions
4IS [NOT] NULL, LIKE, [NOT] IN
5[NOT] BETWEEN
6Not equal to
7NOT logical condition
8AND logical condition
9OR logical condition
![Page 41: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/41.jpg)
SELECT last_name, job_id, salaryFROM employeesWHERE job_id = 'SA_REP'OR job_id = 'AD_PRES'AND salary > 15000;
Rules of PrecedenceRules of Precedence
SELECT last_name, job_id, salaryFROM employeesWHERE (job_id = 'SA_REP'OR job_id = 'AD_PRES')AND salary > 15000;
1
2
![Page 42: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/42.jpg)
Using the Using the ORDER BYORDER BY ClauseClause◦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_dateFROM employeesORDER BY hire_date ;
…
![Page 43: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/43.jpg)
SortingSorting
◦ Sorting in descending order:
◦ Sorting by column alias:
◦ Sorting by multiple columns:
SELECT last_name, job_id, department_id, hire_dateFROM employeesORDER BY hire_date DESC ;
1
SELECT employee_id, last_name, salary*12 annsalFROM employeesORDER BY annsal ;
2
SELECT last_name, department_id, salaryFROM employeesORDER BY department_id, salary DESC;
3
![Page 44: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/44.jpg)
SELECT *|{[DISTINCT] column|expression [alias],...}FROM table[WHERE condition(s)][ORDER BY {column, expr, alias} [ASC|DESC]] ;
SummarySummary
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
![Page 45: Copyright © 2004, Oracle. All rights reserved. Lecture 4: 1-Retrieving Data Using the SQL SELECT Statement 2-Restricting and Sorting Data Lecture 4: 1-Retrieving](https://reader037.vdocuments.us/reader037/viewer/2022103122/56649f485503460f94c6a0a1/html5/thumbnails/45.jpg)
Practice 2Practice 2::
1-The HR departments needs to find high-salary and low-salary employees. display the last name and salary of employees who earn between $5,000 and $12,000 and are in department 20 or 50. Label the columns Employee and Monthly Salary, respectively.
2- Create a report to display the last name, salary, and commission of all employees who earn commissions. Sort data in descending order of salary and commissions.
3- Display the last name of all employees who have both an a and an e in their last name.
4- Display the last name, job, and salary for all employees whose job is SA_REP or ST_CLERKand whose salary is not equal to $2,500, $3,500, or $7,000.