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.

How to unlock hr schema in oracle express edition

Unlocking HR Schema in Oracle 10g

For working with oracle database tables you need to install oracle database and unlock

the sample HR schema from your installed version of oracle database.

Here I am going to describe steps and screenshots to unlock the HR schema.

step 1 : After installing oracle database go to the database homepage and login with system  and password.

                            

step 2: After login you can see screenshot page in this page you have to go Administrator link.

step 3: Click on Administrator select Database Users.

step 4: After that go to Manage Users and select manage users.

step 5: Here you can see inside the Manage Users there is HR which is locked click on HR.

step 6:  By default HR user Account status is locked just type the password and select unlocked in account status.

step 7: After that click on Alter User  and you can also set user privileges by selecting Roles.

And you have unlocked the HR schema and now you can connect HR with SQL*Plus or from sql developer.

Introduction to SQL

SQL stands for Structured Query Language which is used to access the database,manipulate the databases.

Structured query language(sql) is:

• The ANSI(American National Standard Institute)standard for operating relational databases.
• Efficient,easy to learn and use
• SQL is set of statements with which all programs and users access data in Microsoft sql database or in oracle database
• Querying data
• Inserting,updating,and deleting rows in a table
• Creating replacing,altering,and dropping objects
• Controlling access to the database and its objects
• Guaranteeing database consistency and integrity 

SQL unifies all of the preceding tasks in one consistent language and enables you to work with data at a logical level.

SQL commands:

SQL commands to interact with Relational Databases are CREATE,SELECT,INSERT,UPDATE,DELETE and DROP,commands can be classified into groups based on their nature.

DML - Data Manipulation Language:

Command	Description
SELECT	Retrieves certain records from one or more tables
INSERT	Creates a record
UPDATE	Modifies records
DELETE	Deletes records

DDL - Data Definition Language:

Command	Description
CREATE	Creates a new table, a view of a table, or other object in database
ALTER	Modifies an existing database object, such as a table.
DROP	Deletes an entire table, a view of a table or other object in the database.

DCL - Data Control Language:

Command	Description
GRANT	Gives a privilege to user
REVOKE	Takes back privileges granted from user

Oracle 11g step-by-step Installation

This are the steps of Oracle 11g installation guide that covers a typical installation scenario with screenshots.

You can purchase and download oracle Database 11g from the official website of Oracle from clicking  Download

1. Select installation method

Following two installation methods are available:

• Basic Installation – Select this to perform the full Oracle Database 11g installation with standard configuration options. You can use only file system for storage in this option.

• Advanced Installation – This gives your full control over the installation, including the ability to choose Automatic Storage Management. Select this option as shown below.

2. Specify Inventory directory and credentials

Enter the following information:

• Enter the inventory directory: /u01/app/oraInventory
• Specify operating system group name: oinstall

3. Select Installation Type

Following three installation types are available:

• Enterprise Edition – Select this option.
• Standard Edition
• Custom

   

4. Specify Install Location

Enter the following information:

• Oracle Base: /u01/app/oracle
• Name: Oracle_Home
• Path: /u01/app/oarcle/product/11.1.0

5. Prerequisite Checks

In this screen, the Installer verifies that your environment meets all of the minimum requirements for installing and configuring the products that you have chosen to install. You must manually verify and confirm the items that are flagged with warnings and items that require manual checks.

6. Select Configuration Option

Following three configuration options are available:

• Create a Database – Select this option.
• Configure Automatic Storage Management (ASM)
• Install Software Only

7. Select a Database Configuration

Following three database configuration options are available.

• General Purpose/Transaction Processing
• Data Warehouse
• Advanced

8. Specify Database Configuration

Enter the following information:

• Global Database Name: devdb.example.com . An Oracle database is uniquely identified by a Global Database Name, typically of the form “name.domain”
• SID: devdb

9. Specify Database Configuration Details

There are four different tabs in this screen as shown below:

• Memory Tab
• Select the check-box to Enable Automatic Memory Management
• Drag the bar to allocate the memory. This will automatically allocate memory size for SGA and PGA depending on the total database memory allocated.
• Character Sets Tab. Following three character set options are given in this tab.
• Use the default
• Use Unicode (AL32UTF8)
• Choose from the list of character sets.  Select Database Character Set as Unicode standard UTF-8AL32UTF8 – Select this option.
• Security Tab – Just leave the defaults in this tab.
• Sample Schema Tab – Just leave the defaults in this tab.

10. Select Database Management Option

Following two management options are available:

• Use Grid Control for Database Management
• Management Service: Choose an agent.
• Use Database Control for Database Management- Select this option.
• Select the check-box to Enable Email Notifications
• Outgoing mail (SMTP) Server:
• Email Address:

11. Specify Database Storage Option

Following two storage options are available:

• File System – Select this option.
• Specify Database File location: /u01/app/oracle/oradata/
• Automatic Storage Management. ASM simplifies database storage administration and optimizes database layout for I/O performance.

12. Specify Backup and Recovery Option

Enable automated backup as shown below. If you’ve configured ASM, you can choose to store the automated backups on ASM. If not, select File System option.

13. Specify database schema password

Enter the password for sys, system, sysman, dbsnmp schemas. You have the option of either using the same passwords or different password for all the accounts in this screen.

14. Privileged Operating System Groups

Enter the following information in this screen:

• Database Administrator (OSDBA) Group: dba
• Database Operator (OSOPER) Group: dba
• ASM administrator (OSASM) Group: dba

15. Oracle Configuration Manager Registration

You can associate your configration information with your metalink account as shown below.

16. Summary Screen

This will display a summary of all the installation option you’ve selected so far. Click on Install to complete the installation.