retrieving data using the sql select statement · • write a select statement that: – returns...
Post on 24-Apr-2020
13 Views
Preview:
TRANSCRIPT
Copyright © 2004, Oracle. All rights reserved.
Retrieving Data Using
the SQL SELECT Statement
1-2 Copyright © 2004, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do
the following:
• List the capabilities of SQL SELECT statements
• Execute a basic SELECT statement
1-3 Copyright © 2004, Oracle. All rights reserved.
Capabilities of SQL SELECT Statements
SelectionProjection
Table 1 Table 2
Table 1Table 1
Join
1-4 Copyright © 2004, Oracle. All rights reserved.
Basic SELECT Statement
• SELECT identifies the columns to be displayed
• FROM identifies the table containing those columns
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table;
1-5 Copyright © 2004, Oracle. All rights reserved.
Selecting All Columns
SELECT *
FROM departments;
1-6 Copyright © 2004, Oracle. All rights reserved.
Selecting Specific Columns
SELECT department_id, location_id
FROM departments;
1-7 Copyright © 2004, Oracle. All rights reserved.
Writing 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.
1-8 Copyright © 2004, Oracle. All rights reserved.
Arithmetic Expressions
Create expressions with number and date data by
using arithmetic operators.
Operator Description
+ Add
- Subtract
* Multiply
/ Divide
1-9 Copyright © 2004, Oracle. All rights reserved.
SELECT last_name, salary, salary + 300
FROM employees;
Using Arithmetic Operators
4
1-10 Copyright © 2004, Oracle. All rights reserved.
SELECT last_name, salary, 12*salary+100
FROM employees;
Operator Precedence
SELECT last_name, salary, 12*(salary+100)
FROM employees;
4
4
1
2
1-11 Copyright © 2004, Oracle. All rights reserved.
Defining 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_pct
FROM employees;
4
4
1-12 Copyright © 2004, Oracle. All rights reserved.
SELECT last_name, 12*salary*commission_pct
FROM employees;
Null Values
in Arithmetic Expressions
Arithmetic expressions containing a null value
evaluate to null.
4
4
1-13 Copyright © 2004, Oracle. All rights reserved.
Defining 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
1-14 Copyright © 2004, Oracle. All rights reserved.
Using Column Aliases
SELECT last_name "Name" , salary*12 "Annual Salary"
FROM employees;
SELECT last_name AS name, commission_pct comm
FROM employees;
4
4
1-15 Copyright © 2004, Oracle. All rights reserved.
Concatenation 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;
4
1-16 Copyright © 2004, Oracle. All rights reserved.
Literal 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 each
row returned.
1-17 Copyright © 2004, Oracle. All rights reserved.
Using Literal Character Strings
4
SELECT last_name ||' is a '||job_id
AS "Employee Details"
FROM employees;
1-18 Copyright © 2004, Oracle. All rights reserved.
Alternative Quote (q) Operator
• Specify your own quotation mark delimiter
• Choose any delimiter
• Increase readability and usability
SELECT department_name ||
q'[, it's assigned Manager Id: ]'
|| manager_id
AS "Department and Manager"
FROM departments;
4
1-19 Copyright © 2004, Oracle. All rights reserved.
Duplicate Rows
The default display of queries is all rows, including
duplicate rows.
SELECT department_id
FROM employees;
4
SELECT DISTINCT department_id
FROM employees;
4
1
2
1-20 Copyright © 2004, Oracle. All rights reserved.
Summary
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;
top related