02 – Retrieving Data

using the SQL SELECT

Statement
Lesson Objectives
After completing this lesson, you should be able to:
• Execute the basic of SELECT statement
• Using Column aliases
• Using concatenation operator, literal character strings, alternative quote
operator, and the DISTINCT keyword
• Using Arithmetic expressions and NULL values in the SELECT statement
Displaying the Table Structure
Using Describe
• Provide a description of a table
• Returns column names, nullable or not and the data types

DESCRIBE employees

DESC employees
Using Information
• Provide more information on a table
INFO employees

INFORMATION employees

• Provide information on:

• Table
• Column
• Indexes
• References
Using Info+
• INFO+ shows more info with more stats

INFO+ employees
Basic Information About A Table
• How about click on the table icon in SQL Developer?
Writing Comments in SQL
• For single line comments, use --
• While multiple line comments, use /* … */
SQL Select Statement Capabilities
Capabilities of SQL Select Statements
Projection Selection

Table 1 Table 1

• Selected column(s) • Selected rows or every single row

Capabilities of SQL Select Statements

Join

Table 1 Table 2
• Brings together the date that is stored in different tables by specifying the link
between them
Basic SELECT Statement

SELECT *|{[DISTINCT] column|expression [alias],...}

FROM table;

• Keyword refers to an individual SQL Statement, eg: SELECT and FROM are keywords
• A clause is a part of a SQL statement, eg: Select employee_id, last_name
• Statement is a combination of two or more clauses, eg: SELECT * FROM employees
• Square brackets [ ] means the keyword(s) are optional
• A pipe | symbol means OR
Selecting All Columns
SELECT *
FROM departments;

• Columns in the Select list will appear in the order that are stored in that table
Selecting Specific Columns
SELECT department_id, location_id
FROM departments;

• List one or more columns or expression from table or view

Writing SQL Statements
• SQL statements are not case-sensitive.
• SQL statements can be entered 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
• Semicolons are required when you execute multiple SQL statements.
• In SQL Developer, SQL statements can optionally be terminated by a semicolon (;), but
semicolons are required when execute multiple SQL statements
• In SQL*Plus, you are required to end each SQL statement with a semicolon (;)
Arithmetic Expressions
• Perform calculation on numeric and data fields
• Valid operators are:

Operator Function Precedence

+ Addition 2
- Subtraction 2
* Multiplication 2
/ Division 1
% Get the reminder 1
Arithmetic Expressions
• Use brackets to override precedence
• For DATE and TIMESTAMP data types, only additional and subtraction
operations are allowed
Arithmetic Expressions
SELECT last_name, salary, salary + 300
FROM employees;
Arithmetic Expressions

SELECT last_name, salary, 12*salary+100

FROM employees;

SELECT last_name, salary, 12*(salary+100)

FROM employees;
Arithmetic Expressions
• Arithmetic operations with date values return new date values

SELECT sysdate + 2
FROM dual;
Null Values
• Null is a value that is undefine, unknown
• Null is not the same as zero or a blank value
• Zero is a number
• Blank value is character

• Arithmetic operations with the null values return NULL

Null Values

SELECT last_name, salary, commission_pct

FROM employees;
Null Values in Arithmetic Expressions
SELECT last_name, salary,
commission_pct,
salary+commission_pct
FROM employees;
Column Aliases
• Renames a column heading due to the heading may not be descriptive
• Is useful with calculations
• Immediately follows the column name
• Can also be the optional AS keyword between the column name and alias
• Requires double quotation marks (“ “)if it contains spaces or special characters
(eg: # or $), or if it is case-sensitive
Column Aliases

SELECT last_name AS name, commission_pct comm

FROM employees;

SELECT last_name "Name" , salary*12 "Annual Salary"

FROM employees;
Concatenation Operator
• Is represented by two vertical bars (||)
• 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 within single quotation
marks.
• Each character string is output once for each row returned.
Concatenation Operator

SELECT last_name||job_id AS "Employees"

FROM hr.employees;
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 within single quotation
marks.
• Each character string is output once for each row returned
Literal Character Strings
SELECT last_name ||' is a '||job_id
AS "Employee Details"
FROM employees;

• Use two single quote literal

SELECT last_name || '''s job is ' || job_id

AS "Employee Details”
FROM employees;
Literal Character Strings
SELECT last_name || '''is a ' || job_id
AS "Employee Details", '$' || salary as SALARY
FROM employees;
Alternative Quote (q) Operator
• The quotation mark is used to increase readability and usability
• Used in escaping special characters within strings
• Syntax: q’ [your string here]’
• Can use any convenient delimiter, single-byte or multibyte, or any of the
following character pairs
• [ ], { }, ( ), or < >, or even any character like ‘A’, ‘*’
• Usually, [ ] is used as quotation mark delimiter
Alternative Quote (q) Operator

SELECT ’I’’m using quote operator Statement’ as “Quote Operator”

FROM dual;

SELECT q’[I’m using quote operator Statement]’ as “Quote Operator”

FROM dual;

SELECT q’AI’m using quote operator StatementA’ as “Quote Operator”

FROM dual;
Alternative Quote (q) Operator
SELECT last_name || q'['s job is ]’ || job_id
AS "Employee Details”
FROM employees;
Alternative Quote (q) Operator
SELECT department_name || ' Department' ||
q'['s Manager Id: ]'
|| manager_id
AS "Department and Manager"
FROM departments;
Distinct Operator
• There are used to eliminate the duplicate rows

SELECT department_id SELECT DISTINCT department_id

FROM employees; FROM employees;
Exercise
1. You need to produce a report for mailing labels for all customers. The mailing label must have only the customer
name and address. The CUSTOMERS table has these columns:

CUST_ID. NUMBER(4). NOT NULL

CUST_NAME. VARCHAR2(100). NOT NULL
CUST_ADDRESS. VARCHAR2(150)
CUST_PHONE. VARCHAR2(20)
Which SELECT statement accomplishes this task?

A. SELECT * FROM customers

B. SELECT name, address FROM customers;
C. SELECT id, name, address, phone FROM customers;
D. SELECT cust_name, cust_address FROM customers;
E. SELECT cust_id, cust_name, cust_address, cust_phone FROM customers;
Exercise
2. The CUSTOMERS table has these columns:

CUSTOMER_ID NUMBER(4) NOT NULL

CUSTOMER_NAME VARCHAR2(100) NOT NULL
CUSTOMER_ADDRESS VARCHAR2(150)
CUSTOMER_PHONE VARCHAR2(20)

You need to produce output that states "Dear Customer customer_name, ". The customer_name data values
come from the CUSTOMER_NAME column in the CUSTOMERS table. Which statement produces this output?

A. SELECT dear customer, customer_name, FROM customers;

B. SELECT "Dear Customer", customer_name || ', ' FROM customers;
C. SELECT 'Dear Customer ' || customer_name ', ' FROM customers;
D. SELECT 'Dear Customer ' || customer_name || ', ' FROM customers;
E. SELECT "Dear Customer " || customer_name || ", " FROM customers;
F. SELECT 'Dear Customer ' || customer_name || ', ' || FROM customers;
Exercise
3. Evaluate this SQL statement:

SELECT ename, sal, 12*sal+100 FROM emp;

The SAL column stores the monthly salary of the employee. Which change must be
made to the above syntax to calculate the annual compensation as "monthly salary plus
a monthly bonus of $100, multiplied by 12"?

A. No change is required to achieve the desired results

B. SELECT ename, sal, 12*(sal+100) FROM emp;
C. SELECT ename, sal, (12*sal)+100 FROM emp;
D. SELECT ename, sal+100, *12 FROM emp;
Exercise
4. Examine the structure of the PROMOTIONS table:
The management wants to see a report of unique promotion costs in each promotion category.

PROMO_ID NOT NULL NUMBER(6)

PROMO_NAME NOT NULL VARCHAR2(30)
PROMO_CATEGORY NOT NULL VARCHAR2(30)
PROMO_COST NOT NULL NUMBER (10,2)
Which query would achieve the required result?

A. SELECT DISTINCT promo_cost, promo_category FROM promotions;

B. SELECT promo_category, DISTINCT promo_cost FROM promotions;
C. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;
D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
Summary
• You should have learned
• Use Describe function
• Write comments in SQL
• Describe SELECT statement capabilities
• Write a basic SQL statement
• Use arithmetic expressions in SQL
• Use column alias
• Use concatenation operator
• Eliminate duplicates in the retrieved data
Assignment – SCOTT Schema
1. Display all the records held in the Dept table
2. Display a list of department names and the locations of the departments
3. Display a list of employees showing their name, salary, commission and the
value of their salary and commission added together
4. Display the employee’s name, department number, salary, 3% of their salary
and use the column headings as: Name, Department, Salary, Bonus
5. Display a list of all the different job types. (The job should only be listed once)