Range Conditions Using the BETWEEN Operator

Using the BETWEEN Operator

Use the BETWEEN operator to display rows based on a range of values.You can display rows based on a range of values using the BETWEEN operator. The range that you specify contains a lower limit and an upper limit.

             SELECT last_name, salary
             FROM employees
             WHERE salary BETWEEN 2500 AND 3500 ;

The SELECT statement in the example returns rows from the EMPLOYEES table for any employee

whose salary is between 2500 and 3500.

Values that are specified with the BETWEEN operator are inclusive. However, you must specify the

lower limit first.

You can also use the BETWEEN operator on character values. 

               SELECT last_name

               FROM employees

               WHERE last_name between 'King' and 'Smith';

Character Strings and Dates

Character strings and date values are enclosed with single quotation marks.
Character values are case-sensitive and date values are format-sensitive.
The default date display format is DD-MON-RR.

    SELECT last_name, job_id, department_id 

    FROM employees 

    WHERE last_name = 'Whalen' ;

    SELECT last_name

    FROM employees

    WHERE hire_date = '17-FEB-96' ;

Character strings and dates in the WHERE clause must be enclosed with single quotation marks ('').

Number constants, however, should not be enclosed with single quotation marks.

All character searches are case-sensitive.

Oracle databases store dates in an internal numeric format, representing the century, year, month,

day, hours, minutes,and seconds.

Comparison Operators

Comparison operators are used in conditions that compare one expression to another value or

expression. They are used in the WHERE clause in the following format:

Syntax

... WHERE expr operator value

Example
... WHERE hire_date = '01-JAN-95'
... WHERE salary >= 6000
... WHERE last_name = 'Smith'
An alias cannot be used in the WHERE clause.

Note: The symbols != and ^= can also represent the not equal to condition.

Using Comparison Operators

                                Operator                                                    Meaning

                                  =                                                             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

               SELECT last_name, salary

               FROM employees

               WHERE salary <= 3000 ;

In the example, the SELECT statement retrieves the last name and salary from the EMPLOYEES

table for any employee whose salary is less than or equal to 3,000. Note that there is an explicit

value supplied to the WHERE clause. The explicit value of 3000 is compared to the salary value in

the SALARY column of the EMPLOYEES table.

Restricting and Sorting Data

When retrieving data from the database, you may need to do the following:

• Restrict the rows of data that are displayed
• Specify the order in which the rows are displayed

Limiting rows with:

The WHERE clause

The comparison conditions using =, <=, BETWEEN, IN, LIKE, and NULL conditions

Logical conditions using AND, OR, and NOT operators

             SELECT * column_name  FROM table_name WHERE condition;

The WHERE clause follows the FROM clause.

You can restrict the rows that are returned from the query by using the WHERE clause. A WHERE

clause contains a condition that must be met and it directly follows the FROM clause. If the condition

is true, the row meeting the condition is returned.

The WHERE clause can compare values in columns, literal, arithmetic expressions, or functions. It

consists of three elements:

1.  Column name
2.  Comparison condition
3.  Column name, constant, or list of values

Using the WHERE Clause

                SELECT employee_id, last_name, job_id, department_id 

                               FROM employees WHERE department_id = 90 ;

Note: You cannot use column alias in the WHERE clause.



To filter the data a query retrieves, you add a WHERE clause—also called a predicate list or a set of conditions—to your SQL statement. In a nutshell, the WHERE clause specifies criteria that must be met before records are included in your query result set. The WHERE clause must specify a WHERE clause condition (or conditions) that the database software evaluates to be true or false—alternatively, the software can determine the absence of a value. A WHERE clause consists of conditional expressions. A conditional expression takes the form

<left_expression> <as compared with> <right_expression>

some examples of common types of conditional expressions: 

WHERE <column_name> = 

<literal_character_value> 

WHERE <column_name> IN (3, 7, 9) 

WHERE <column_name> >= 100 

WHERE <column_name> LIKE 'E%'; 

WHERE <column_name> BETWEEN 100 AND 500; 

A literal character value, or string, is any list of alphanumeric characters enclosed in single quotation marks, such as ‘King’, ‘73xyz’, or ’16-MAR-1965’.

Use of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword

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

You can link columns to other columns, arithmetic expressions, or constant values to create a

character expression by using the concatenation operator (||). Columns on either side of the operator

are combined to make a single output column.

                   SELECT FROM last_name||job_id AS "Employees" employees;

In the example, LAST_NAME and JOB_ID are concatenated, and given the alias Employees. Note

that the last name of the employee and the job code are combined to make a single output column.

The AS keyword before the alias name makes the SELECT clause easier to read.

Null Values with the concatenation operator

If you concatenate null value with a character string,the result is a character string. 

LAST_NAME || NULL results in LAST_NAME.

Note: You can also concatenate Date expression with other expressions or columns.

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.

Date and character literals must be enclosed within single quotation marks (' '); number literals

need not be enclosed in a similar manner.

     SELECT last_name ||' is a '||job_id AS "Employee Details" FROM employees;

The example in the slide displays the last names and job codes of all employees. The column has the

heading Employee Details. Note the spaces between the single quotation marks in the SELECT

statement. The spaces improve the readability of the output.

Alternative Quote (q) Operator

• Specify your own quotation mark delimiter.
• Select any delimiter.
• Increase readability and usability.

             SELECT department_name || q'[ Department's Manager Id: ]'

                          || manager_id AS "Department and Manager" FROM departments;

Many SQL statements use character literals in expressions or conditions. If the literal itself contains a

single quotation mark, you can use the quote (q) operator and select your own quotation mark

delimiter.

You can choose any convenient delimiter, single-byte or multibyte, or any of the following character

pairs: [ ], { }, ( ), or < >.

In the example shown, the string contains a single quotation mark, which is normally interpreted as a

delimiter of a character string. By using the q operator, however, brackets [] are used as the quotation

mark delimiters.The string between the brackets delimiters is interpreted as a literal character string.

Duplicate Rows

Unless you indicate otherwise, SQL displays the results of a query without eliminating the duplicate

rows. The first example in the slide displays all the department numbers from the EMPLOYEES table.

Note that the department numbers are repeated.

To eliminate duplicate rows in the result, include the DISTINCT keyword in the SELECT clause

immediately after the SELECT keyword. In the second example in the slide, the EMPLOYEES table

actually contains 20 rows, but there are only seven unique department numbers in the table.

                       SELECT department_id FROM employees;

               

                        SELECT DISTINCT department_id FROM employees;

You can specify multiple columns after the DISTINCT qualifier. The DISTINCT qualifier affects

all the selected columns, and the result is every distinct combination of the columns.

               SELECT  DISTINCT department_id, job_id FROM employees;

DESCRIBE command

Displaying the Table Structure

Use the DESCRIBE command to display the structure of a table.

Or, select the table in the Connections tree and use the Columns tab to view the table structure.

In SQL Developer, you can display the structure of a table by using the DESCRIBE command. The

command displays the column names and the data types, and it shows you whether a column must

contain data (that is, whether the column has a NOT NULL constraint).

In the syntax, table name is the name of any existing table, view, or synonym that is accessible to

the user.

                                                 DESCRIBE departments

Using the SQL Developer GUI interface, you can select the table in the Connections tree and use the

Columns tab to view the table structure.

Note: The DESCRIBE command is supported by both SQL*Plus and SQL Developer.

SQL Column Alias

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

When displaying the result of a query, SQL Developer normally uses the name of the selected

column as the column heading. This heading may not be descriptive and, therefore, may be difficult

to understand. You can change a column heading by using a column alias.

Specify the alias after the column in the SELECT list using blank space as a separator. By default,

alias headings appear in uppercase. If the alias contains spaces or special characters (such as # or $),

or if it is case-sensitive, enclose the alias in double quotation marks (" ").

Using Column Aliases

                                        

                       SELECT last_name AS name, commission_pct comm FROM employees;

The first example displays the names and the commission percentages of all the employees. Note that

the optional AS keyword has been used before the column alias name. The result of the query is the

same whether the AS keyword is used or not. Also, note that the SQL statement has the column

aliases, name and comm, in lowercase, whereas the result of the query displays the column headings

in uppercase. As mentioned earlier column headings appear in uppercase by default.

                     SELECT last_name "Name" , salary*12 "Annual Salary" FROM employees;

The second example displays the last names and annual salaries of all the employees. Because

Annual Salary contains a space, it has been enclosed in double quotation marks. Note that the

column heading in the output is exactly the same as the column alias.

SQL Defining a Null Value

Defining a Null Value

Null is a value that is unavailable, unassigned, unknown,or inapplicable.

Null is not the same as zero or a blank space.

If a row lacks a data value for a particular column, that value is said to be null or to contain a null.

Null is a value that is unavailable, unassigned, unknown, or inapplicable. Null is not the same as zero

or a blank space. Zero is a number and blank space is a character.

Columns of any data type can contain nulls. However, some constraints (NOT NULL and PRIMARY

KEY) prevent nulls from being used in the column.

            SELECT last_name, job_id, salary, commission_pct FROM employees;

               

In the COMMISSION_PCT column in the EMPLOYEES table, notice that only a sales manager or

sales representative can earn a commission. Other employees are not entitled to earn commissions. 

A null represents that fact.

Null Values in Arithmetic Expressions

Arithmetic expressions containing a null value evaluate to null.

If any column value in an arithmetic expression is null, the result is null. For example, if you attempt

to perform division by zero, you get an error. However, if you divide a number by null, the result is a

null or unknown.

            SELECT last_name, 12*salary*commission_pct FROM employees;

In the example employee King does not get any Commission.Because the COMMISSION_PCT column in the arithmetic expression is null so the result is null. 

SQL Arithmetic Expressions

Arithmetic Expressions

Create expressions with number and date data by using arithmetic operators.You may need to modify the way in which data is displayed, or you may want to perform calculations, or look at what-if scenarios. All these are possible using arithmetic expressions. An arithmetic expression can contain column names, constant numeric values, and the arithmetic operators.

Arithmetic Operators

The slide lists the arithmetic operators that are available in SQL. You can use arithmetic operators in
any clause of a SQL statement (except the FROM clause).
Note: With the DATE and TIMESTAMP data types, you can use the addition and subtraction operators only.

                                                   Operator                  Description

                                                       

                                                        +                             Add

                                                        -                              Subtract

                                                        *                             Multiply

                                                        /                              Divide

    

  Using Arithmetic Operators

                      SELECT last_name, salary, salary + 300 FROM employees;

The example uses the addition operator to calculate a salary increase of 300 for all employees. It also displays a SALARY+300 column in the output.

Note that the resultant calculated column, SALARY+300, is not a new column in the EMPLOYEES

table; it is for display only. By default, the name of a new column comes from the calculation that

generated it—in this case, salary+300.

Note: The oracle server ignores blank spaces before and after arithmetic operators.

Operator Precedence

If an arithmetic expression contains more than one operator, multiplication and division are evaluated

first. If operators in an expression are of the same priority, then evaluation is done from left to right.

You can use parentheses to force the expression that is enclosed by the parentheses to be evaluated

first.

Rules of Precedence:

• Multiplication and division occur before addition and subtraction.

• Operators of the same priority are evaluated from left to right.

• Parentheses are used to override the default precedence or to clarify the statement.

                SELECT last_name, salary, 12*salary+100 FROM employees;

               

The first example displays the last name, salary, and annual compensation of employees.

It calculates the annual compensation by multiplying the monthly salary with 12, plus a one-time

bonus of 100. Note that multiplication is performed before addition.

Using Parentheses

You can override the rules of precedence by using parentheses to specify the desired order in which

the operators are to be executed.

The second example displays the last name, salary, and annual compensation of employees. It calculates the annual compensation as follows: adding a monthly bonus of 100 to the monthly salary, and then multiplying that subtotal with 12. Because of the parentheses, addition takes priority over multiplication.

                 SELECT last_name, salary, 12*(salary+100) FROM employees;

                     

                             

Selecting Specific Columns

Selecting Specific Columns

You can use the SELECT statement to display specific columns of the table by specifying the column
names, separated by commas. The example in the slide displays all the department numbers and
location numbers from the DEPARTMENTS table.

                     SELECT department_id, location_id FROM departments;

In the SELECT clause, specify the columns that you want in the order in which you want them to

appear in the output. For example, to display location before department number (from left to right),

you use the following statement:

                    SELECT location_id,department_id FROM departments;

Column Heading Defaults

SQL Developer:

                               Default heading alignment: Left-aligned

                               Default heading display: Uppercase

SQL*Plus:

                              Character and Date column headings are left-aligned.

                              Number column headings are right-aligned.

                              Default heading display: Uppercase

                     SELECT last_name, hire_date, salary FROM employees;

Basic SQL SELECT statement

SELECT statement

Capabilities of SQL SELECT Statements

A SELECT statement retrieves information from the database. With a SELECT statement, you can
use the following capabilities:

• Projection: Select the columns in a table that are returned by a query. Select as few or as many
                    of the columns as required.
• Selection:  Select the rows in a table that are returned by a query. Various criteria can be used to
                    restrict the rows that are retrieved.
• Joining:     Bring together data that is stored in different tables by specifying the link between
                    them.                                                    

  

In its simplest form, a SELECT statement must include the following:
• A SELECT clause, which specifies the columns to be displayed
• A FROM clause, which identifies the table containing the columns that are listed in the SELECT

clause

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

In the syntax:

                            SELECT                            is a list of one or more columns

                            *                                         selects all columns

                            DISTINCT                         suppresses duplicates

                            alias                                    gives the selected columns different headings

                            column|expression              selects the named column or the expression

                            FROM table                       specifies the table containing the columns

Note: In this tutorial I have used oracle HR sample schema and sql developer tool for querying the sql commands.

You need to install oracle 11g express edition which you can download  Oracle express edition 

And already I have posted  How to unlock hr schema in oracle express edition which guides you to unlock the sample HR schema from the oracle database.

Selecting All Columns

  

                                                       SELECT * FROM departments;

You can display all columns of data in a table by following the SELECT keyword with an asterisk

(*). In the example in the slide, the department table contains four columns: DEPARTMENT_ID,

DEPARTMENT_NAME, MANAGER_ID, and LOCATION_ID. The table contains eight rows, one for each department.

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.

In SQL Developer, SQL statements can optionally be terminated by a semicolon (;). 

Semicolons are required when you execute multiple SQL statements.

In SQL*Plus, you are required to end each SQL statement with a semicolon (;).

Executing SQL Statement

In SQL Developer, click the Run Script icon or press [F5] to run the command or commands in the

SQL Worksheet. You can also click the Execute Statement icon or press [F9] to run a SQL statement

in the SQL Worksheet. The Execute Statement icon executes the statement at the mouse pointer in

the Enter SQL Statement box while the Run Script icon executes all the statements in the Enter SQL

Statement box. The Execute Statement icon displays the output of the query on the Results tabbed

page while the Run Script icon emulates the SQL*Plus display and shows the output on the Script

Output tabbed page.

In SQL*Plus, terminate the SQL statement with a semicolon, and then press [Enter] to run the

command.