SQL
SQL is nonprocedural language that
provides database access. It is
nonprocedural in that users describe in
SQL what they want be done, and the
SQL language compiler automatically
generates a procedure to navigate the
database and perform the desired task.
History of SQL
IBM first defined, the language was referred to as
Structured English Query Language (SEQUEL), and later,
the name was changed to SQL
Relational Software, Inc., now the Oracle Corporation,
released their database system before IBM got their product
to the market.
As more vendors released their products, SQL began to
emerge as the standard relational database language.
In 1986 ANSI released the first published standard for the
language (SQL-86).
The standard was updated in 1989 and again in 1992. SQL-
92 represented a major revision of the language, expanding
on and improving features of the earlier versions.
History of SQL
As more vendors released their products, SQL began to emerge as
the standard relational database language.
In 1986 ANSI released the first published standard for the language
(SQL-86).
The standard was updated in 1989 and again in 1992. SQL-92
represented a major revision of the language, expanding on and
improving features of the earlier versions.
Seven years later, in 1999, the latest version of the SQL standard,
SQL:1999, was released, representing yet another large step
forward in bringing SQL up to date with the real-world
implementations of database systems and the needs of those who
use those systems.
SQL*Plus History
SQL*Plus originated from the beginning of the Oracle
RDBMS days as a product called User Friendly Interface
(UFI).
UFI was later renamed to SQL*Plus with the advent of
Oracle Version 5.
There have been some improvements to SQL*Plus from
the UFI days; however, most of the commands and the
ease of formatting results are as easy today as they
were with the UFI product.
SQL*Plus
SQL*Plus is the main, character-mode interface
to the Oracle RDBMS.
• SQL*Plus enables you :
– Enter, edit, store, retrieve, and run SQL
commands and PL/SQL blocks
– Format, perform calculations on, store, and
print query results in the form of reports
– List column definitions for any table
Starting sql*plus
• C:\...>sqlplus
• C:\...>sqlplusw
• Using sql*plus option provided in Start-program file…
Using the SQL*Plus Application Window
• SQL*Plus displays the SQL prompt in the application
window.
• You can enter following kinds of commands at the command
prompt:
– SQL commands, for working with information in the database
– PL/SQL blocks, also for working with information in the database
– SQL*Plus commands, for formatting query results
• To enter SQL and SQL*Plus commands, type them at the SQL
prompt and press Enter.
• To copy a command, highlight it with the left mouse button.
While still holding down the left mouse button, click the right
mouse button
• To get online help for SQL*Plus commands, type HELP at the command prompt
followed by the name of the command. For example:
• SQL>HELP Describe
• To get online help for SQL*Plus all commands, type HELP INDEX
• SQL>HELP INDEX
SQL and Types of SQL Statements
• Data Manipulation Language (DML)
DML statements are used to view, add, modify,or delete data
stored in your database objects. The primary keywords
associated with DML statements are SELECT, INSERT,
UPDATE, and DELETE.
• Data Definition Language (DDL)
DDL statements are used to create, modify, or delete database
objects such as tables, views, schemas, domains, triggers, and
stored procedures.
Statements are CREATE, ALTER, and DROP.
• Data Control Language (DCL)
DCL statements allow you to control who has access to
specific objects in your database. With the DCL statements,
you can grant or restrict access by using the GRANT or
REVOKE statements
Writing SQL Statements
– SQL statements are not case-sensitive.
– SQL statements can be on one or more lines.
– Keywords cannot be abbreviated or split
across lines.
– Clauses are usually placed on separate lines.
– Indents are used to enhance readability.
– In iSQL*Plus, SQL statements can optionally be
terminated by a semicolon (;). Semicolons are required if
you execute multiple SQL statements.
– In SQL*plus, you are required to end each SQL statement
with a semicolon (;).
SELECT STATEMENT
SELECT statement is also called a query
because it quite literally “queries” or asks questions of a database. There are
several uses for the SELECT statement that give you great flexibility in the
database:
Simple query
. A SELECT statement can be used alone to retrieve data from a table or a
group of related tables. You can retrieve all columns or specify some columns.
You can retrieve all rows or specify which rows you want.
Complex query
. A SELECT statement can be embedded within another SELECT statement.
This lets you write a query within a query. The possibilities are endless. Later
chapters cover the details.
Insert, update, or delete data
. A SELECT statement can be used
within the INSERT, UPDATE, or DELETE statements to add
greater flexibility to these commands.
Basic Select Statement
SELECT *|{[DISTINCT] column|expression [alias],...} FROM table;
• Basic SELECT Statement
– 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
– In the syntax:
– SELECT is a list of one or more columns
* selects all columns
DISTINCT suppresses duplicates
column|expression selects the named column or the
expression
alias gives selected columns different headings
FROM table specifies the table containing the columns
• Examples
• SELECT * FROM dep;
• SELECT * FROM EMP;
Arithmetic Expressions
Create expressions with number and date data by using
arithmetic operators
Operator Description
+ Add
- Subtract
* Multiply
/ Divide
• Examples
• SELECT ename, job, sal, sal + 300
FROM emp;
• SELECT ename, sal, 12*sal+100
FROM emp;
• SELECT ename, sal, 12*(sal+100)
FROM emp;
Defining a Null Value
– A null is a value that is unavailable,
unassigned, unknown, or inapplicable.
– A null is not the same as a zero or a blank
space.
• SELECT ename, job, sal, comm
FROM emp;
Arithmetic expressions containing a null value
evaluate to null.
• SELECT ename, 12*sal*comm
FROM emp;
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
• SELECT ename AS name, comm commission
FROM emp;
• SELECT ename "Name" , sal*12 “ Annual Salary"
FROM employees;
Concatenation Operator
• A concatenation operator:
– Links columns or character strings to other columns
– Is represented by two vertical bars (||)
– Creates a resultant column that is a character expression
• SELECT ename||job AS "Employees"
FROM emp;
Literal Character Strings
– A literal is a character, a number, or a date that is included in the
SELECT statement.
– Date and character literal values must be enclosed by single
quotation marks.
– Each character string is output once for each
row returned.
• SELECT ename ||' is a '||job AS "Employee Details"
FROM emp;
Restricting and Sorting Data
– Limit the rows that are retrieved by a query
– Sort the rows that are retrieved by a query
• SELECT empno, ename, job, deptno
FROM emp WHERE deptno0p = 90 ;
• SELECT ename, job, deptno
FROM emp WHERE ename = ‘King' ;
SQL> select ename from emp where hiredate = '17-DEC-80';
ENAME
----------
SMITH
SELECT ENAME, HIREDATE FROM EMP WHERE
HIREDATE=TO_DATE(’01-01-05’, ‘DD-MM-YY’);
Comparison Conditions
Operator Meaning
• = Equal to
• > Greater than
• >= Greater 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 Ename, sal
FROM emp WHERE sal =10000 AND job LIKE '%MAN%‘
• OR requires either condition to be true:
SELECT empno, ename, job, sal
FROM emp
WHERE sal >= 10000 OR job LIKE '%MAN%' ;
• SELECT * FROM EMP WHERE JOB NOT IN
('CLERK', 'SALESMAN')
Using the ORDER BY Clause
– Sort retrieved rows with the ORDER BY clause:
• ASC: ascending order, default
• DESC: descending order
– The ORDER BY clause comes last in the SELECT statement:
• SELECT Ename, job, deptno
FROM emp
ORDER BY deptno;
– Sorting in descending order:
• SELECT ename, job, deptno
• FROM emp ORDER BY deptno DESC ;
» Sorting by column alias:
• SELECT employee_id, last_name, salary*12 annsal
FROM employees ORDER BY annsal ;