Oracle SQL.pdf

(1)

What is a Database?

A database is a collection of information that is organized so that it can easily be accessed, managed, and updated.

OR

A collection of information organized in such a way that a computer program can quickly select desired pieces of data. You can think of a database as an electronic filing system.

Traditional databases are organized by fields, records, and files/table. A field is a single piece of information; a record is one complete set of fields; and a file is a collection of records/table. For example, a telephone book is analogous to a file. It contains a list of records, each of which consists of three fields: name, address, and telephone number.

OR

The definition of a database is a structured collection of records or data that is stored in a computer system. In order for a database to be truly functional, it must not only store large amounts of records well, but be accessed easily. In addition, new information and changes should also be fairly easy to input. In order to have a highly efficient database system, you need to incorporate a program that manages the queries and information stored on the system. This is usually referred to as DBMS or a Database Management System. Besides these features, all databases that are created should be built with high data integrity.

Database Management System (DBMS)

A collection of programs that enables you to store, modify, and extract information from a database. There are many different types of DBMSs, ranging from small systems that run on personal computers to huge systems that run on mainframes. The following are examples of database applications:

• computerized library systems • automated teller machines • flight reservation systems

• computerized parts inventory systems

(2)

Types of Databases

There are several common types of databases; each type of database has its own data model (how the data is structured). They include; Flat Model, Hierarchical Model, Relational Model and Network Model.

The Flat Model Database

In a flat model database, there is a two dimensional (flat structure) array of data. For instance, there is one column of information and within this column it is assumed that each data item will be related to the other. For instance, a flat model database includes only zip codes. Within the database, there will only be one column and each new row within that one column will be a new zip code.

The Hierarchical Model Database

The hierarchical model database resembles a tree like structure, such as how Microsoft Windows organizes folders and files. In a hierarchical model database, each upward link is nested in order to keep data organized in a particular order on a same level list. For instance, a hierarchal database of sales, may list each days sales as a separate file. Within this nested file are all of the sales (same types of data) for the day.

The Network Model

In a network model, the defining feature is that a record is stored with a link to other records - in effect networked. These networks (or sometimes referred to as pointers) can be a variety of different types of information such as node numbers or even a disk address.

The Relational Model

The relational model is the most popular type of database and an extremely powerful tool, not only to store information, but to access it as well. Relational databases are organized as tables. The beauty of a table is that the information can be accessed or added without reorganizing the tables. A table can have many records and each record can have many fields.

Tables are sometimes called a relation. For instance, a company can have a database called customer orders, within this database will be several different tables or relations all relating to customer orders. Tables can include customer information (name, address, contact, info,

customer number, etc) and other tables (relations) such as orders that the customer previously bought (this can include item number, item description, payment amount, payment method, etc). It should be noted that every record (group of fields) in a relational database has its own primary key. A primary key is a unique field that makes it easy to identify a record.

Relational databases use a program interface called SQL or Standard Query Language. SQL is currently used on practically all relational databases. Relational databases are extremely easy to customize to fit almost any kind of data storage. You can easily create relations for items that you sell, employees that work for your company, etc.

Accessing Information Using a Database

While storing data is a great feature of databases, for many database users the most important feature is quick and simple retrieval of information. In a relational database, it is extremely easy to pull up information regarding an employee, but relational databases also add the power of running queries. Queries are requests to pull specific types of information and either show them in their natural state or create a report using the data. For instance, if you had a database of employees and it included tables such as salary and job description, you can easily run a query of which jobs pay over a certain amount. No matter what kind of information you store on your database, queries can be created using SQL to help answer important questions.

(3)

Storing a Database

Databases can be very small (less than 1 MB) or extremely large and complicated (terabytes as in many government databases), however all databases are usually stored and located on hard disk or other types of storage devices and are accessed via computer. Large databases may require separate servers and locations, however many small databases can fit easily as files located on your computer's hard drive.

Securing a Database

Obviously, many databases store confidential and important information that should not be easily accessed by just anyone. Many databases require passwords and other security features in order to access the information. While some databases can be accessed via the internet through a network, other databases are closed systems and can only be accessed on site.

Field / Column

A space allocated for a particular item of information. Admission form, for example, contains a number of fields: one for your name, one for your Father Name, one for your date of birth, and so on. In database systems, fields are the smallest units of information you can access.

In spreadsheets, fields are called cells.

Record

A collection of fields is called a record.

Table

Collection of related records is called a table. Or

Table is a storage object of a database.

Oracle Tables

Oracle stores records relating to each other in a table. For example, all the records for employees of a company would be stored by Oracle in one table, the employee table.

EMP TABLE

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --- --- --- --- --- --- --- ---

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

EMPNO is a primary key in EMP Table. DEPTNO is a foreign key in EMP Table.

A table consists of a number of records. The field names of each record in the table are the same, although the field values may differ. Every employee record has a salary field, called SAL. The values in the SAL field can be different for each employee.

(4)

Each field occupies one column and each record occupies one row. In each column of the table, you put a specific category of information for the employees, such as their employee number, name, and job. Each row in the table contains the information relating to a specific employee, together as one record. Each record is a unique entry and is independent of any other record in the table. The EMP table, for example, contains records for SMITH and ALLEN. Although both records are part of the EMP table, the data contained within them is independent of each other. There is no relationship between SMITH's andALLEN's salaries.

After the analysis of the business requirements, the database design team defines the necessary tables. Different tables are created for the various groups of information. An EMP table is created for employee information, a DEPT table is created for department information. Related tables are grouped together to form a database. For example, a personnel or human resources application database includes both the EMP and DEPT tables and all other tables involved in the application.

DEPT TABLE

DEPTNO DNAME LOC

--- --- --- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON DEPTNO is a primary key in DEPT Table.

Primary Keys

Every table in Oracle has a field or a combination of fields that uniquely identifies each record in the table. This unique identifier is called the primary key, or simply the key.

* Primary Key is used to identify a record in a table. * No Duplication

* NULL is not allowed (must provide a value) * Only one Primary Key in a table

* May be composite Primary Key

* Table in which Primary Key exists normally called parent table

Foreign Key

Remember that every table in ORACLE has a primary key a field or fields making each record unique. In the employee table, the primary key is the employee ID number, and it is stored in the EMPNO field. In the DEPT table, the department number is the primary key and is stored in the DEPTNO field.

The department number is also stored in a field in the EMP table - the DEPTNO field. The

department number field links the EMP table to the DEPT table. This relationship is based on the department number field.

Employee SMITH works in department number 20, ALLEN works in department 30.

When a field in one table matches the primary key of another table, the field is referred to as a foreign key. A foreign key is a field or a group of fields in one table whose values match those of the primary key of another table. You can think of a foreign key as the primary key of a foreign table. In the personnel database example, the DEPTNO field in the EMP table is a foreign key. The DEPTNO field is still the primary key of the DEPT table.

* Comes from an other table * Duplication allowed

* Null is allowed

* There may be multiple foreign Key in a table

(5)

Relational Databases

Oracle makes it very easy to link the data in multiple tables: matching an employee to the department in which they work is one example. This is a key feature of a relational database management system, or RDBMS. They store data in two or more tables and enable you to define relationships between the tables. The link between the tables is based on one or more field values common to both tables.

For example, the following diagram represents part of the EMP table and the entire DEPT table:

DEPT

DEPTNO DNAME LOC

--- --- --- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

EMP

EMPNO ENAME DEPTNO --- --- --- 7369 SMITH 20 7499 ALLEN 30 7521 WARD 30 7566 JONES 20 7654 MARTIN 30 7698 BLAKE 30 7782 CLARK 10 7788 SCOTT 20 7839 KING 10 7844 TURNER 30 7876 ADAMS 20 7900 JAMES 30 7902 FORD 20 7934 MILLER 10

There is a department number field in both the EMP and DEPT tables. In the EMP table, the department number represents the department in which the employee works. In the DEPT table, the department number represents a valid department within the business. In both tables, they are department numbers; in essence, the contents of the DEPTNO field in the EMP table represents the same thing as the contents of the DEPTNO field in the DEPARTMENT table. It's not necessary that the linking fields have the same field names. What's important is their value and what they represent.

The business is divided into departments. The departments are identified and stored in the DEPT table. Each department is assigned a department number. The relationship between the EMP and DEPT tables is based on the department number. Each employee works in one specific department. The employee's department number is stored in the DEPTNO field of the EMP table. An employee cannot be assigned to a department that is not defined in the DEPT table. A department can be defined in the DEPT table, yet have no employees assigned to it.

(6)

Data Storage on Different Media

Storing Information

Every organization has some information needs. A library keeps a list of members, books, due dates, and fines. A company needs to save information about employees, departments, and salaries. These pieces of information are called data.

Organizations can store data on various media and in different formats, such as a hard-copy document in a filing cabinet or data stored in electronic spreadsheets or in databases. A database is an organized collection of information.

To manage databases, you need database management systems (DBMS). A DBMS is a program that stores, retrieves, and modifies data in the database on request. There are four main types of databases: hierarchical, network, relational, and more recently object relational.

Database

Filling cabinet

Electronic

Spreadsheet

(7)

Chapter 1 Writing Basic SQL SELECT Statements

SQL is an ANSI (American National Standards Institute) standard computer language for

accessing and manipulating databases. SQL statements are used to retrieve and update data in a database. SQL works with database programs like MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, etc.

What is SQL?

SQL stands for Structured Query Language SQL allows you to access a database

SQL is an ANSI standard computer language SQL can execute queries against a database SQL can retrieve data from a database SQL can insert new records in a database SQL can delete records from a database SQL can update records in a database SQL is easy to learn

SQL Queries

With SQL, we can query a database and have a result set returned. A query like this:

SELECT EMPNO, ENAME, JOB, SAL FROM EMP;

SQL Query is also called SQL statement. Result of the above query:

EMPNO ENAME JOB SAL --- --- --- --- 7369 SMITH CLERK 800 7499 ALLEN SALESMAN 1600 7521 WARD SALESMAN 1250 7566 JONES MANAGER 2975 7654 MARTIN SALESMAN 1250 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7788 SCOTT ANALYST 3000 7839 KING PRESIDENT 5000 7844 TURNER SALESMAN 1500 7876 ADAMS CLERK 1100 7900 JAMES CLERK 950 7902 FORD ANALYST 3000 7934 MILLER CLERK 1300

Almost all modern Relational Database Management Systems like MS SQL Server, Microsoft Access, MSDE, Oracle, DB2, Sybase, MySQL, Postgres and Informix use SQL as standard database language.

There are three types of SQL Statements: 1. Data Manipulation Language (DML) 2. Data Manipulation Language (DDL) 3. Data Control Language (DCL)

(8)

What is SQL*Plus?

SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database Server or Client installation. It has a command-line user interface, a Windows Graphical User Interface (GUI) and the iSQL*Plus web-based user interface.

SQL*Plus has its own commands and environment, and it provides access to the Oracle Database. It enables you to enter and execute SQL, PL/SQL, SQL*Plus and operating system commands.

What is iSQL*Plus?

iSQL*Plus is a browser-based interface which uses the SQL*Plus processing engine in a three-tier model comprising:

• Client (Web browser).

• Middle tier (Application Server). • Database (Oracle Database).

(9)

The Oracle Product Family

As an Oracle database administrator, you may be responsible for a database that is actually part of another Oracle product or that complements or is relied upon by that other product. Oracle has long since become more than just a database company— though the Oracle database is at the heart of what Oracle Corporation does best. In addition to the database, Oracle Corporation also offers these products, among others: • Oracle E-Business Suite

Also referred to as Oracle Applications, the E-Business Suite is the premier enterprise resource planning (ERP) suite in the industry. Encompassing many modules, including financials, human resources, distribution, manufacturing, and many others, the E-Business Suite allows

organizations to leverage Oracle technology to better run their businesses and increase profitability and customer satisfaction. Oracle E-Business Suite makes use of the Oracle database, Application Server, and Developer Suite to run and enhance the application. • Oracle Collaboration Suite

One of Oracle’s newest products, Collaboration Suite encompasses e-mail, fax, calendaring, web conferencing, a files repository, voice mail, and other modules to provide a single

integrated platform for an organization’s collaboration and communication infrastructure. Like Oracle E-Business Suite, Collaboration Suite also incorporates the Oracle database

and Application Server as its foundation. • Oracle Application Server

A Java 2 Enterprise Edition (J2EE)–certified server platform, Oracle Application Server integrates everything you need to deploy and develop web-based applications. Oracle Application Server includes a portal server and tools to develop portlets, Web Services capabilities, content management, support for forms-based applications using Oracle Forms, reporting using Oracle Reports, development using PL/SQL or Java, and many other features. It also includes the capability to configure and use a single sign-on server that interfaces with other thirdparty directory services such as Microsoft’s Active Directory, Novell’s NDS, and Sun’s iPlanet.

• Oracle Developer Suite

If you need to develop applications for the Oracle database or Application Server, Oracle

Developer Suite provides tools for the development of Java, PL/SQL, business intelligence, data warehousing, and other applications.

• Oracle Consulting

When you need assistance in your Oracle deployment, Oracle Consulting can provide the expertise and individuals to help your projects succeed.

• Oracle University

The training of customers and partners on Oracle products and technologies is the responsibility of Oracle University and its partners. Instructor-led and online courses are available. More information on the products available from Oracle Corporation can be found on the Oracle web site at www.oracle.com.

(10)

Connecting to database

You can connect to the database by using different tools. I am using SQL*Plus to connect to the database. SQL*Plus is Oracle provided tool for accessing database.

In order to connect to database, you must provide:

SQL prompt is appeared after successful login.

SQL statements are used to access database. There are three types of SQL statements. • Data Manipulation Language (DML)

• Data Definition Language (DDL) • Data Control Language (DCL)

(11)

SQL> SELECT * FROM EMP;

7369 SMITH CLERK 7902 17-DEC-80 800 20

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

1. A single row or tuple representing all data required for a particular employee. Each row in a table should be identified by a primary key, which allows no duplicate rows. The order of rows is insignificant; specify the row order when the data is retrieved.

2. A column or attribute containing the employee number. The employee number identifies a unique employee in the EMP table. In this example, the employee number column is designated as the primary key. A primary key must contain a value, and the value must be unique.

3. A column that is not a key value. A column represents one kind of data in a table; in the example, the name of all the employees. Column order is insignificant when storing data; specify the column order when the data is retrieved.

4. A column containing the department number, which is also a foreign key. A foreign key is a column that defines how tables relate to each other. A foreign key refers to a primary key or a unique key in the same table or in another table. In the example, DEPTNO uniquely identifies a department in the DEPT table.

5. A field may have no value in it. This is called a null value. In the EMP table, only

employees who have a role of salesman have a value in the COMM (commission) field. 6. A field can be found at the intersection of a row and a column. There can be only one

value in it.

SQL> SELECT * FROM DEPT; DEPTNO DNAME LOC

--- --- --- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 1 2 5 5 4 1

(12)

What is Clause? SELECT ... FROM ... WHERE ... ORDER BY ... GROUP BY ... etc. What is Keyword? SELECT FROM WHERE ORDER BY GROUP BY etc. What is SQL Statement? SELECT * FROM EMP;

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP; SELECT * FROM EMP WHERE SAL>2000;

etc.

SELECT & FROM Keywords

Specify number of columns just after SELECT. Specify table name just after FROM keyword.

Solve the following:

Display all columns and all rows of EMP table.

SQL> SELECT * FROM EMP;

7369 SMITH CLERK 7902 17-DEC-80 800 20

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected.

Display all columns and all rows of DEPT table.

SQL> SELECT * FROM DEPT; DEPTNO DNAME LOC

--- --- --- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

(13)

Display employee number, name, and salary of all employees.

SQL> SELECT EMPNO, ENAME, SAL FROM EMP; EMPNO ENAME SAL

--- --- --- 7369 SMITH 800 7499 ALLEN 1600 7521 WARD 1250 7566 JONES 2975 7654 MARTIN 1250 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 7876 ADAMS 1100 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 14 rows selected.

Above query can be written as follows:

SQL> SELECT 2 EMPNO, 3 ENAME, 4 SAL 5 FROM 6 EMP; EMPNO ENAME SAL --- --- --- 7369 SMITH 800 7499 ALLEN 1600 7521 WARD 1250 7566 JONES 2975 7654 MARTIN 1250 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 7876 ADAMS 1100 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 14 rows selected.

It is good habit to write each clause in separate line. i.e.

SQL> SELECT EMPNO, ENAME, SAL 2 FROM EMP;

(14)

Remember about 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 ABOVE SQL STATEMENTS [SELECT , FROM ] ARE KEY WORDS -- IN ABOVE SQL STATEMENTS [SELECT .. , FROM .. ] ARE clauses. * Means All Columns

How to display structure of a table?

Mean how many columns are there in a table, data type, width and either null is allowed or not. User DESCRIBE / DESC command

SQL> DESC EMP

Name Null? Type --- --- ---- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)

There are eight columns in EMP table. Data type and width of each column is also displayed. NOT NULL is written in front of EMPNO.

SQL> DESC DEPT

Name Null? Type --- --- ---- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13)

What is Data Type?

Classification of a particular type of information. It is easy for humans to distinguish between different types of data.

Some data types are as follows:

NUMBER Accepts 0 to 9, decimal point, positive or negative sign CHAR Accepts 0 to 9, alpha bets and special characters VARCHAR2 Accepts 0 to 9, alpha bets and special characters DATE Accepts Date

Example of Numbers Salary, Commission, Fee, Charges, Amount etc Example of VARCHAR2 Name, Father Name, Job, Address etc.

(15)

Selecting specific columns and notice the heading of each column.

SQL> SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP;

7369 SMITH CLERK 7902 17-DEC-80 800 20

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected. SQL> SELECT ENAME, JOB, SAL, DEPTNO FROM EMP; ENAME JOB SAL DEPTNO --- --- --- --- SMITH CLERK 800 20 ALLEN SALESMAN 1600 30 WARD SALESMAN 1250 30 JONES MANAGER 2975 20 MARTIN SALESMAN 1250 30 BLAKE MANAGER 2850 30 CLARK MANAGER 2450 10 SCOTT ANALYST 3000 20 KING PRESIDENT 5000 10 TURNER SALESMAN 1500 30 ADAMS CLERK 1100 20 JAMES CLERK 950 30 FORD ANALYST 3000 20 MILLER CLERK 1300 10 14 rows selected. SQL> SELECT JOB, SAL FROM EMP; JOB SAL --- --- CLERK 800 SALESMAN 1600 SALESMAN 1250 MANAGER 2975 SALESMAN 1250 MANAGER 2850 MANAGER 2450 ANALYST 3000 PRESIDENT 5000 SALESMAN 1500 CLERK 1100 CLERK 950 ANALYST 3000 CLERK 1300

(16)

SQL> SELECT ENAME FROM EMP; ENAME --- SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER

SQL> SELECT DEPTNO, DNAME, LOC FROM DEPT; DEPTNO DNAME LOC

--- --- --- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> SELECT DEPTNO, DNAME FROM DEPT; DEPTNO DNAME --- --- 10 ACCOUNTING 20 RESEARCH 30 SALES 40 OPERATIONS

SQL> SELECT DNAME FROM DEPT; DNAME --- ACCOUNTING RESEARCH SALES OPERATIONS

What is DUAL Table?

The DUAL table is owned by the user SYS and can be accessed by all users. It contains one column, DUMMY, and one row with the value X. The DUAL table is useful when you want to return a value once only, for instance, the value of a constant, pseudocolumn, or expression that is not derived from a table with user data. The DUAL table is generally used for SELECT clause syntax completeness, because both SELECT and FROM clauses are mandatory, and several calculations do not need to select from actual tables.

SQL> DESC DUAL

Name Null? Type --- --- ----

DUMMY VARCHAR2(1) SQL> SELECT * FROM DUAL;

D - X

(17)

Display Current Date using DUAL Table

SQL> SELECT SYSDATE FROM DUAL; SYSDATE

--- 14-JUL-08

Display Current User using DUAL Table

SQL> SELECT USER FROM DUAL; USER

--- SCOTT

SQL>

Arithmetic operators and precedence

+ Addition - Subtraction * Multiplication / Division

PRECEDENCE

1. ( ) Parenthesis has the highest precedence

2. * / Multiplication and Division have the same precedence 3. + - Addition and Subtraction have the same precedence

Performing calculation in using SQL

SQL> SELECT 7+10 FROM DUAL; 7+10

--- 17

SQL> SELECT 7+10-2+5 FROM DUAL; 7+10-2+5

--- 20

SQL> SELECT 7+10*2+5 FROM DUAL; 7+10*2+5

--- 32

SQL> SELECT 7+10-2*5 FROM DUAL; 7+10-2*5

--- 7

(18)

SQL> SELECT 7*10-2+5 FROM DUAL; 7*10-2+5

--- 73

SQL> SELECT 7*10-2*5 FROM DUAL; 7*10-2*5

--- 60

SQL> SELECT 7+10/2+5 FROM DUAL; 7+10/2+5

--- 17

SQL> SELECT 7*10/2+5 FROM DUAL; 7*10/2+5

--- 40

SQL> SELECT 7*10/2*5 FROM DUAL; 7*10/2*5

--- 175

SQL> SELECT 7+10/2*5 FROM DUAL; 7+10/2*5

--- 32

SQL> SELECT 7*10/(2+5) FROM DUAL; 7*10/(2+5)

--- 10

SQL> SELECT 7*10+2+5 FROM DUAL; 7*10+2+5

--- 77

SQL> SELECT 7*(10+2)+5 FROM DUAL; 7*(10+2)+5

--- 89

(19)

Performing calculations with columns, Notice heading of each column

WITH NUMBER COLUMNS

SQL> SELECT EMPNO, ENAME, SAL, SAL+100, SAL-100, SAL*12 FROM EMP; EMPNO ENAME SAL SAL+100 SAL-100 SAL*12 --- --- --- --- --- --- 7369 SMITH 800 900 700 9600 7499 ALLEN 1600 1700 1500 19200 7521 WARD 1250 1350 1150 15000 7566 JONES 2975 3075 2875 35700 7654 MARTIN 1250 1350 1150 15000 7698 BLAKE 2850 2950 2750 34200 7782 CLARK 2450 2550 2350 29400 7788 SCOTT 3000 3100 2900 36000 7839 KING 5000 5100 4900 60000 7844 TURNER 1500 1600 1400 18000 7876 ADAMS 1100 1200 1000 13200 7900 JAMES 950 1050 850 11400 7902 FORD 3000 3100 2900 36000 7934 MILLER 1300 1400 1200 15600

SQL> SELECT EMPNO, ENAME, SAL, SAL*12, SAL+100*12, (SAL+100)*12 FROM EMP; EMPNO ENAME SAL SAL*12 SAL+100*12 (SAL+100)*12

--- --- --- --- --- --- 7369 SMITH 800 9600 2000 10800 7499 ALLEN 1600 19200 2800 20400 7521 WARD 1250 15000 2450 16200 7566 JONES 2975 35700 4175 36900 7654 MARTIN 1250 15000 2450 16200 7698 BLAKE 2850 34200 4050 35400 7782 CLARK 2450 29400 3650 30600 7788 SCOTT 3000 36000 4200 37200 7839 KING 5000 60000 6200 61200 7844 TURNER 1500 18000 2700 19200 7876 ADAMS 1100 13200 2300 14400 7900 JAMES 950 11400 2150 12600 7902 FORD 3000 36000 4200 37200 7934 MILLER 1300 15600 2500 16800 SQL> SELECT EMPNO, ENAME, COMM, COMM+100, COMM-100, COMM*12 FROM EMP; EMPNO ENAME COMM COMM+100 COMM-100 COMM*12

--- --- --- --- --- --- 7369 SMITH 7499 ALLEN 300 400 200 3600 7521 WARD 500 600 400 6000 7566 JONES 7654 MARTIN 1400 1500 1300 16800 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 0 100 -100 0 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER

?

(20)

SQL> SELECT EMPNO, ENAME, SAL, COMM, SAL+COMM FROM EMP; EMPNO ENAME SAL COMM SAL+COMM --- --- --- --- --- 7369 SMITH 800 7499 ALLEN 1600 300 1900 7521 WARD 1250 500 1750 7566 JONES 2975 7654 MARTIN 1250 1400 2650 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 0 1500 7876 ADAMS 1100 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 14 rows selected. SQL> SELECT EMPNO, ENAME, SAL, COMM, SAL+COMM*12, (SAL+COMM)*12 FROM EMP; EMPNO ENAME SAL COMM SAL+COMM*12 (SAL+COMM)*12 --- --- --- --- --- --- 7369 SMITH 800 7499 ALLEN 1600 300 5200 22800 7521 WARD 1250 500 7250 21000 7566 JONES 2975 7654 MARTIN 1250 1400 18050 31800 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 0 1500 18000 7876 ADAMS 1100 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300

In above result notice calculation with NULL values

• A null is a value that is unavailable, unassigned, unknown or inapplicable. • A null is not the same as zero or a blank space.

If you add, subtract, multiply or divided a value with null, the whole result becomes null.

?

(21)

NULL value can be controlled with NVL() function

NVL (column_name, replaced_value)

SELECT EMPNO, ENAME, JOB, SAL, COMM, NVL(COMM,0) FROM EMP; NULL will be replaced with 0

SELECT EMPNO, ENAME, JOB, SAL, COMM, NVL(COMM,100) FROM EMP; NULL will be replaced with 100

Notice the result of column number 3, 4 and 5

SQL> SELECT EMPNO, ENAME, COMM, NVL(COMM,0), NVL(COMM,50) FROM EMP; EMPNO ENAME COMM NVL(COMM,0) NVL(COMM,50)

--- --- --- --- --- 7369 SMITH 0 50 7499 ALLEN 300 300 300 7521 WARD 500 500 500 7566 JONES 0 50 7654 MARTIN 1400 1400 1400 7698 BLAKE 0 50 7782 CLARK 0 50 7788 SCOTT 0 50 7839 KING 0 50 7844 TURNER 0 0 0 7876 ADAMS 0 50 7900 JAMES 0 50 7902 FORD 0 50 7934 MILLER 0 50 14 rows selected.

Notice the result of column number 6 and 7

SQL> SELECT EMPNO, ENAME, JOB, SAL, COMM, SAL+COMM, SAL+NVL(COMM,0) FROM EMP; EMPNO ENAME JOB SAL COMM SAL+COMM SAL+NVL(COMM,0) --- --- --- --- --- --- --- 7369 SMITH CLERK 800 800 7499 ALLEN SALESMAN 1600 300 1900 1900 7521 WARD SALESMAN 1250 500 1750 1750 7566 JONES MANAGER 2975 2975 7654 MARTIN SALESMAN 1250 1400 2650 2650 7698 BLAKE MANAGER 2850 2850 7782 CLARK MANAGER 2450 2450 7788 SCOTT ANALYST 3000 3000 7839 KING PRESIDENT 5000 5000 7844 TURNER SALESMAN 1500 0 1500 1500 7876 ADAMS CLERK 1100 1100 7900 JAMES CLERK 950 950 7902 FORD ANALYST 3000 3000 7934 MILLER CLERK 1300 1300 14 rows selected.

(22)

Performing calculations with columns, Notice heading of each column

WITH DATE COLUMNS

SQL> SELECT SYSDATE, SYSDATE+1, SYSDATE-1 FROM DUAL; SYSDATE SYSDATE+1 SYSDATE-1

--- --- --- 14-JUL-08 15-JUL-08 13-JUL-08

SQL> SELECT SYSDATE, SYSDATE+7, SYSDATE+30, SYSDATE+365 FROM DUAL; SYSDATE SYSDATE+7 SYSDATE+3 SYSDATE+3

--- --- --- --- 14-JUL-08 21-JUL-08 13-AUG-08 14-JUL-09

SQL> SELECT EMPNO, ENAME, HIREDATE, HIREDATE+1, HIREDATE+7 FROM EMP; EMPNO ENAME HIREDATE HIREDATE+ HIREDATE+

--- --- --- --- --- 7369 SMITH 17-DEC-80 18-DEC-80 24-DEC-80 7499 ALLEN 20-FEB-81 21-FEB-81 27-FEB-81 7521 WARD 22-FEB-81 23-FEB-81 01-MAR-81 7566 JONES 02-APR-81 03-APR-81 09-APR-81 7654 MARTIN 28-SEP-81 29-SEP-81 05-OCT-81 7698 BLAKE 01-MAY-81 02-MAY-81 08-MAY-81 7782 CLARK 09-JUN-81 10-JUN-81 16-JUN-81 7788 SCOTT 19-APR-87 20-APR-87 26-APR-87 7839 KING 17-NOV-81 18-NOV-81 24-NOV-81 7844 TURNER 08-SEP-81 09-SEP-81 15-SEP-81 7876 ADAMS 23-MAY-87 24-MAY-87 30-MAY-87 7900 JAMES 03-DEC-81 04-DEC-81 10-DEC-81 7902 FORD 03-DEC-81 04-DEC-81 10-DEC-81 7934 MILLER 23-JAN-82 24-JAN-82 30-JAN-82 14 rows selected.

SQL> SELECT EMPNO, ENAME, HIREDATE, HIREDATE-7 FROM EMP; EMPNO ENAME HIREDATE HIREDATE-

--- --- --- --- 7369 SMITH 17-DEC-80 10-DEC-80 7499 ALLEN 20-FEB-81 13-FEB-81 7521 WARD 22-FEB-81 15-FEB-81 7566 JONES 02-APR-81 26-MAR-81 7654 MARTIN 28-SEP-81 21-SEP-81 7698 BLAKE 01-MAY-81 24-APR-81 7782 CLARK 09-JUN-81 02-JUN-81 7788 SCOTT 19-APR-87 12-APR-87 7839 KING 17-NOV-81 10-NOV-81 7844 TURNER 08-SEP-81 01-SEP-81 7876 ADAMS 23-MAY-87 16-MAY-87 7900 JAMES 03-DEC-81 26-NOV-81 7902 FORD 03-DEC-81 26-NOV-81 7934 MILLER 23-JAN-82 16-JAN-82 14 rows selected.

(23)

Column Alias / Column Heading

When displaying the result of a query, SQL*Plus normally uses the name of the selected column as the column heading. This heading may not be descriptive and hence 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 a space as a separator. By default, alias headings appear in uppercase. If the alias contains spaces or special characters (such as # or $), or is case sensitive, enclose the alias in double quotation marks (" ").

AS is optional

SQL> SELECT EMPNO, ENAME, JOB AS DESIGNATION, SAL AS SALARY, COMM COMMISSION FROM EMP;

EMPNO ENAME DESIGNATI SALARY COMMISSION --- --- --- --- --- 7369 SMITH CLERK 800 7499 ALLEN SALESMAN 1600 300 7521 WARD SALESMAN 1250 500 7566 JONES MANAGER 2975 7654 MARTIN SALESMAN 1250 1400 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7788 SCOTT ANALYST 3000 7839 KING PRESIDENT 5000 7844 TURNER SALESMAN 1500 0 7876 ADAMS CLERK 1100 7900 JAMES CLERK 950 7902 FORD ANALYST 3000 7934 MILLER CLERK 1300 14 rows selected.

SQL> SELECT EMPNO, ENAME, SAL, SAL*12 ANN_SAL FROM EMP; EMPNO ENAME SAL ANN_SAL

--- --- --- --- 7369 SMITH 800 9600 7499 ALLEN 1600 19200 7521 WARD 1250 15000 7566 JONES 2975 35700 7654 MARTIN 1250 15000 7698 BLAKE 2850 34200 7782 CLARK 2450 29400 7788 SCOTT 3000 36000 7839 KING 5000 60000 7844 TURNER 1500 18000 7876 ADAMS 1100 13200 7900 JAMES 950 11400 7902 FORD 3000 36000 7934 MILLER 1300 15600 14 rows selected. SQL>

(24)

SQL> SELECT EMPNO, ENAME, JOB, SAL MONTHLY_SALARY, SAL+NVL(COMM,0) GROSS_SALARY, 2 SAL*12 ANNUAL_SALARY FROM EMP;

EMPNO ENAME JOB MONTHLY_SALARY GROSS_SALARY ANNUAL_SALARY --- --- --- --- --- --- 7369 SMITH CLERK 800 800 9600 7499 ALLEN SALESMAN 1600 1900 19200 7521 WARD SALESMAN 1250 1750 15000 7566 JONES MANAGER 2975 2975 35700 7654 MARTIN SALESMAN 1250 2650 15000 7698 BLAKE MANAGER 2850 2850 34200 7782 CLARK MANAGER 2450 2450 29400 7788 SCOTT ANALYST 3000 3000 36000 7839 KING PRESIDENT 5000 5000 60000 7844 TURNER SALESMAN 1500 1500 18000 7876 ADAMS CLERK 1100 1100 13200 7900 JAMES CLERK 950 950 11400 7902 FORD ANALYST 3000 3000 36000 7934 MILLER CLERK 1300 1300 15600 14 rows selected.

By default, alias headings appear in uppercase. If the alias contains spaces or special characters (such as # or $), or is case sensitive, enclose the alias in double quotation marks (" ").

SQL> SELECT EMPNO, ENAME, JOB "JOB TITLE", SAL "salary", COMM COMMISSION 2 FROM EMP;

EMPNO ENAME JOB TITLE salary COMMISSION --- --- --- --- --- 7369 SMITH CLERK 800 7499 ALLEN SALESMAN 1600 300 7521 WARD SALESMAN 1250 500 7566 JONES MANAGER 2975 7654 MARTIN SALESMAN 1250 1400 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7788 SCOTT ANALYST 3000 7839 KING PRESIDENT 5000 7844 TURNER SALESMAN 1500 0 7876 ADAMS CLERK 1100 7900 JAMES CLERK 950 7902 FORD ANALYST 3000 7934 MILLER CLERK 1300 14 rows selected. SQL>

(25)

Concatenation Operator

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.

SQL> SELECT EMPNO||ENAME FROM EMP; EMPNO||ENAME --- 7369SMITH 7499ALLEN 7521WARD 7566JONES 7654MARTIN 7698BLAKE 7782CLARK 7788SCOTT 7839KING 7844TURNER 7876ADAMS 7900JAMES 7902FORD 7934MILLER 14 rows selected.

SQL> SELECT EMPNO||ENAME||JOB EMPLOYEE_INFO FROM EMP; EMPLOYEE_INFO --- 7369SMITHCLERK 7499ALLENSALESMAN 7521WARDSALESMAN 7566JONESMANAGER 7654MARTINSALESMAN 7698BLAKEMANAGER 7782CLARKMANAGER 7788SCOTTANALYST 7839KINGPRESIDENT 7844TURNERSALESMAN 7876ADAMSCLERK 7900JAMESCLERK 7902FORDANALYST 7934MILLERCLERK 14 rows selected. SQL>

(26)

Literal Character Strings

A literal is a character, a number, or a date that is included in the SELECT list and that is not a column name or a column alias. It is printed for each row returned. Literal strings of free-format text can be included in the query result and are treated the same as a column in the SELECT list.

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

SQL> SELECT EMPNO, ENAME, JOB, 5000, 'KARACHI', '14-AUG-1947' FROM EMP; EMPNO ENAME JOB 5000 'KARACH '14-AUG-194

--- --- --- --- --- --- 7369 SMITH CLERK 5000 KARACHI 14-AUG-1947 7499 ALLEN SALESMAN 5000 KARACHI 14-AUG-1947 7521 WARD SALESMAN 5000 KARACHI 14-AUG-1947 7566 JONES MANAGER 5000 KARACHI 14-AUG-1947 7654 MARTIN SALESMAN 5000 KARACHI 14-AUG-1947 7698 BLAKE MANAGER 5000 KARACHI 14-AUG-1947 7782 CLARK MANAGER 5000 KARACHI 14-AUG-1947 7788 SCOTT ANALYST 5000 KARACHI 14-AUG-1947 7839 KING PRESIDENT 5000 KARACHI 14-AUG-1947 7844 TURNER SALESMAN 5000 KARACHI 14-AUG-1947 7876 ADAMS CLERK 5000 KARACHI 14-AUG-1947 7900 JAMES CLERK 5000 KARACHI 14-AUG-1947 7902 FORD ANALYST 5000 KARACHI 14-AUG-1947 7934 MILLER CLERK 5000 KARACHI 14-AUG-1947 14 rows selected.

SQL> SELECT 'Mr. '||ENAME EMPLOYEE FROM EMP; EMPLOYEE --- Mr. SMITH Mr. ALLEN Mr. WARD Mr. JONES Mr. MARTIN Mr. BLAKE Mr. CLARK Mr. SCOTT Mr. KING Mr. TURNER Mr. ADAMS Mr. JAMES Mr. FORD Mr. MILLER 14 rows selected.

SQL> SELECT 'Rs. '||SAL SALAR FROM EMP;

SQL> SELECT ENAME||' WORKS IN DEPARTMENT NUMBER '||DEPTNO EMPLOYEE FROM EMP; SQL> SELECT ENAME||' IS EARNING '||SAL||' PER MONTH' SALARY FROM EMP;

(27)

SQL> SELECT ENAME||' works as '||JOB||' in Dept No. '||DEPTNO "Employee Information" FROM EMP;

Employee Information

--- SMITH works as CLERK in Dept No. 20

ALLEN works as SALESMAN in Dept No. 30 WARD works as SALESMAN in Dept No. 30 JONES works as MANAGER in Dept No. 20 MARTIN works as SALESMAN in Dept No. 30 BLAKE works as MANAGER in Dept No. 30 CLARK works as MANAGER in Dept No. 10 SCOTT works as ANALYST in Dept No. 20 KING works as PRESIDENT in Dept No. 10 TURNER works as SALESMAN in Dept No. 30 ADAMS works as CLERK in Dept No. 20 JAMES works as CLERK in Dept No. 30 FORD works as ANALYST in Dept No. 20 MILLER works as CLERK in Dept No. 10 14 rows selected.

SQL>

SQL> SELECT ENAME||' Earn Monthly '||SAL||' And His Annual Salary Is '||SAL*12 "Employee Salary Info..." FROM EMP;

Employee Salary Info...

---

SMITH Earn Monthly 800 And His Annual Salary Is 9600 ALLEN Earn Monthly 1600 And His Annual Salary Is 19200 WARD Earn Monthly 1250 And His Annual Salary Is 15000 JONES Earn Monthly 2975 And His Annual Salary Is 35700 MARTIN Earn Monthly 1250 And His Annual Salary Is 15000 BLAKE Earn Monthly 2850 And His Annual Salary Is 34200 CLARK Earn Monthly 2450 And His Annual Salary Is 29400 SCOTT Earn Monthly 3000 And His Annual Salary Is 36000 KING Earn Monthly 5000 And His Annual Salary Is 60000 TURNER Earn Monthly 1500 And His Annual Salary Is 18000 ADAMS Earn Monthly 1100 And His Annual Salary Is 13200 JAMES Earn Monthly 950 And His Annual Salary Is 11400 FORD Earn Monthly 3000 And His Annual Salary Is 36000 MILLER Earn Monthly 1300 And His Annual Salary Is 15600 14 rows selected.

(28)

Duplicate Rows

SQL> SELECT DEPTNO FROM EMP; DEPTNO --- 20 30 30 20 30 30 10 20 10 30 20 30 20 10 14 rows selected.

Eliminating Duplicate Rows

To eliminate duplicate rows in the result, include the DISTINCT keyword in the SELECT clause immediately after the SELECT keyword. In the example on the slide, the EMPLOYEES table actually contains 20 rows but there are only seven unique department numbers in the table. 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.

SQL> SELECT DISTINCT DEPTNO FROM EMP; DEPTNO

--- 30 20 10

SQL> SELECT DISTINCT JOB FROM EMP; JOB --- CLERK SALESMAN PRESIDENT MANAGER ANALYST

SQL> SELECT DISTINCT DEPTNO, JOB FROM EMP; DEPTNO JOB --- --- 20 CLERK 30 SALESMAN 20 MANAGER 30 CLERK 10 PRESIDENT 30 MANAGER 10 CLERK 10 MANAGER 20 ANALYST 9 rows selected. SQL>

(29)

Chapter 2 Restricting Data

There are two types of queries

• Restricted queries (Limiting the rows) • Unrestricted queries

Limiting the Rows Selected

You can restrict the rows 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.

In the following example, the SELECT statement retrieves records of all

employees who belong to department number 10.

SQL> SELECT * FROM EMP WHERE DEPTNO=10;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --- --- --- --- --- --- --- --- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7839 KING PRESIDENT 17-NOV-81 5000 10 7934 MILLER CLERK 7782 23-JAN-82 1300 10 SQL>

Display all employees who are earning more then 2000.

SQL> SELECT * FROM EMP WHERE SAL>3000;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --- --- --- --- --- --- --- --- 7839 KING PRESIDENT 17-NOV-81 5000 10 SQL>

The following query retrieves records of all employees who are working

as CLERK.

SQL> SELECT * FROM EMP WHERE JOB='CLERK';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --- --- --- --- --- --- --- --- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7934 MILLER CLERK 7782 23-JAN-82 1300 10

Note that the job title CLERK has been specified in uppercase to ensure that it matches the job column in the EMP table. Character strings are case sensitive. If data is stored in uppercase and you specify condition in lowercase then the result retrieve no record.

SQL> SELECT * FROM EMP WHERE JOB='clerk'; no rows selected

(30)

Retrieve all employees who were hired on 03-DEC-1981

SQL> SELECT * FROM EMP WHERE HIREDATE='03-DEC-1981';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --- --- --- --- --- --- --- --- 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 SQL>

Character Strings and Dates

Character strings and dates in the WHERE clause must be enclosed in single quotation marks (''). Number constants, however, should not be enclosed in single quotation marks.

All character searches are case sensitive. In the following example, no rows are returned because the EMP table stores all names in upper case:

SELECT * FROM EMP WHERE JOB='clerk';

Oracle databases store dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds. The default date display is DD-MON-RR.

Comparison Conditions

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

SELECT EMPNO, ENAME, JOB FROM EMP WHERE DEPTNO=20; SELECT * FROM EMP WHERE SAL<2000;

SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE SAL>3000; SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE SAL>=3000;

SELECT EMPNO, ENAME, JOB, SAL, SAL*12 FROM EMP WHERE SAL*12>30000; SELECT * FROM EMP WHERE SAL<=COMM;

SELECT * FROM EMP WHERE HIREDATE>'22-FEB-1981';

SELECT ENAME, HIREDATE, HIREDATE+10 FROM EMP WHERE HIREDATE+15>'01-MAR-1981'; SELECT EMPNO, ENAME FROM EMP WHERE ENAME='SMITH';

SELECT EMPNO, ENAME FROM EMP WHERE ENAME>'SMITH'; SELECT EMPNO, ENAME FROM EMP WHERE ENAME>'S'; SELECT EMPNO, ENAME FROM EMP WHERE ENAME<'C'; SELECT EMPNO, ENAME FROM EMP WHERE ENAME<>'SMITH'; SELECT EMPNO, ENAME FROM EMP WHERE SAL<>3000; SELECT EMPNO, ENAME FROM EMP WHERE SAL!=3000;

Operator

=

>

>=

<

<=

<>

Meaning

Equal to

Greater than

Greater than or equal to

Less than

Less than or equal to

Not equal to

(31)

Other Comparison Conditions

BETWEEN ... AND ...

SQL> SELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 3000;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --- --- --- --- --- --- --- --- 7566 JONES MANAGER 7839 02-APR-81 2975 20 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20

Retrieve all employees who were hired in Feb 1981

SQL> SELECT * FROM EMP WHERE HIREDATE BETWEEN '01-FEB-1981' AND '28-FEB-1981'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --- --- --- --- --- --- --- --- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 SQL> SELECT EMPNO, ENAME, JOB FROM EMP WHERE ENAME BETWEEN 'K' AND 'P';

EMPNO ENAME JOB

--- --- --- 7654 MARTIN SALESMAN 7839 KING PRESIDENT 7934 MILLER CLERK

IN ( )

SQL> SELECT * FROM EMP WHERE EMPNO IN(7788,7839,7968,7654) ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --- --- --- --- --- --- --- --- 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 SQL> SELECT * FROM EMP WHERE ENAME IN ('SCOTT','MARTIN','ALLEN','ASLAM', 'ZIA'); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --- --- --- --- --- --- --- --- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7788 SCOTT ANALYST 7566 19-APR-87 3000 20

Operator

BETWEEN ...AND...

IN(set)

LIKE

IS NULL

Meaning

Between two values (inclusive),

Match any of a list of values

Match a character pattern

Is a null value

(32)

You may not always know the exact value to search for. You can select rows that match a character pattern by using the LIKE condition. The character pattern-matching operation is referred to as a wildcard search. Two symbols can be used to construct the search string. % denotes zero or many characters.

_ denotes one character.

Retrieve all employees whose name begins with A

SQL> SELECT * FROM EMP WHERE ENAME LIKE 'A%';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --- --- --- --- --- --- --- --- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20

Retrieve all employees whose name ends with S

SQL> SELECT * FROM EMP WHERE ENAME LIKE '%S';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --- --- --- --- --- --- --- --- 7566 JONES MANAGER 7839 02-APR-81 2975 20 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30

Retrieve all employees who contain M anywhere in their name

SQL> SELECT * FROM EMP WHERE ENAME LIKE '%M%';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --- --- --- --- --- --- --- --- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7934 MILLER CLERK 7782 23-JAN-82 1300 10

Retrieve all employees who contain sequentially A and then M in their name

SQL> SELECT * FROM EMP WHERE ENAME LIKE '%A%M%';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --- --- --- --- --- --- --- --- 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30

Retrieve all employees who contain second character as A in their name

SQL> SELECT * FROM EMP WHERE ENAME LIKE '_A%';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --- --- --- --- --- --- --- --- 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7900 JAMES CLERK 7698 03-DEC-81 950 30

(33)

Retrieve all employees who contain second last character as E in their name

SQL> SELECT * FROM EMP WHERE ENAME LIKE '%E_';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --- --- --- --- --- --- --- --- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7900 JAMES CLERK 7698 03-DEC-81 950 30 7934 MILLER CLERK 7782 23-JAN-82 1300 10

Retrieve all employees who contain first character A and third character L in their name

SQL> SELECT * FROM EMP WHERE ENAME LIKE 'A_L%';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --- --- --- --- --- --- --- --- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

Retrieve all employees who were hired in 87

SQL> SELECT * FROM EMP WHERE HIREDATE LIKE '%87';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --- --- --- --- --- --- --- --- 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7876 ADAMS CLERK 7788 23-MAY-87 1100 20

Retrieve all employees who contains only 4 characters in their name

SQL> SELECT * FROM EMP WHERE ENAME LIKE '____';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --- --- --- --- --- --- --- --- 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7839 KING PRESIDENT 17-NOV-81 5000 10 7902 FORD ANALYST 7566 03-DEC-81 3000 20

Checking null values

SQL> SELECT * FROM EMP WHERE MGR IS NULL;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --- --- --- --- --- --- --- --- 7839 KING PRESIDENT 17-NOV-81 5000 10

SELECT * FROM EMP WHERE HIREDATE BETWEEN '01-JAN-1981' AND '31-DEC-1981'; SELECT * FROM EMP WHERE ENAME LIKE 'KHAN%';

SELECT * FROM EMP WHERE ENAME LIKE '%KHAN'; SELECT * FROM EMP WHERE ENAME LIKE '%KHAN%'; SELECT * FROM EMP WHERE ENAME LIKE '__R%'; SELECT * FROM EMP WHERE COMM IS NULL;

(34)

Logical Conditions

A logical condition combines the result of two component conditions to produce a single result based on them or inverts the result of a single condition. A row is returned only if the overall result of the condition is true. Three logical operators are available in SQL:

• AND • OR • NOT

All the examples so far have specified only one condition in the WHERE clause. You can use several conditions in one WHERE clause using the AND and OR operators.

Using the AND Operator

AND requires both conditions to be true

If either condition is FALSE result will be FALSE in case of AND

CONDITION 1 CONDITION 2 RESULT

TRUE TRUE TRUE

TRUE FALSE FALSE

FALSE TRUE FALSE

FALSE FALSE FALSE

Retrieve all clerks of department number 20

SQL> SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP WHERE JOB='CLERK' AND DEPTNO=20; EMPNO ENAME JOB DEPTNO

--- --- --- --- 7369 SMITH CLERK 20 7876 ADAMS CLERK 20

Retrieve all salesman who are earning more than 1500

SQL> SELECT * FROM EMP WHERE JOB='SALESMAN' AND SAL>1500;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --- --- --- --- --- --- --- --- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

Retrieve all salesman who were hired before mar 1981

SQL> SELECT * FROM EMP WHERE JOB='SALESMAN' AND HIREDATE<'01-MAR-1981';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO --- --- --- --- --- --- --- --- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30