Jay Krishnan R

1
SQL - Introduction
Jay Krishnan R
2
SQL - Structured Query language

• The language for accessing database
• Processes set of records rather than one at a
  time
• Non procedural statement oriented language
• Need not specify access methods to the data
• Easy to use English like language

3
SQL - The History

• Dr. E. F. Codd published the paper, "A Relational
  Model of Data for Large Shared Data Banks", in
  June 1970 in the Association of Computer
  Machinery (ACM) journal, Communications of the
  ACM. Codd's model is now accepted as the
  definitive model for relational database
  management systems (RDBMS)
• The language, Structured English Query Language
  ("SEQUEL") was developed by IBM Corporation,
  Inc., to use Codd's model. SEQUEL later became
  SQL.
• In 1979, Relational Software, Inc. (now Oracle
  Corporation) introduced the first commercially
  available implementation of SQL,todays industry
  accepted standard.

4
SQL - Datatypes

• VARCHAR2(size) Variable-length character
  string having length size in bytes. Maximum size
  is 4000, and minimum is 1. You must specify size
  for VARCHAR2.
• VARCHAR(size) Same as VARCHAR2. Avoid using
  this to ensure forward compatibility with Oracle
  versions.
• NUMBER(p,s) Number having precision p and
  scale s. The precision p can range from 1 to 38.
• Eg Number(5,2). This can take a maximum
  value of 999.99

5
SQL - Datatypes (Contd)

• LONG Character data of variable length up to 2
  gigabytes, or 231 -1 bytes. Only one Long column
  may be defined per table. Long columns may not be
  used in subqueries, functions, expressions where
  clauses or indexes. Tables containing LONG column
  cannot be partitioned. LONG data cannot be used
  as attributes in any user-defined object type.
• DATE Valid date range from January 1, 4712 BC to
  December 31, 9999 AD.

6
SQL - Datatypes (Contd)

• RAW(size) Raw binary data of length size
  bytes. Maximum size is 2000 bytes. You must
  specify size for a RAW value.
• LONG RAW Raw binary data of variable length up to
  2 gigabytes. Can be used to store graphics,
  sound, documents or arrays of binary data
• Note- RAW and LONG RAW data do not undergo
  any character set conversion when transmitted
  between different systems
• ROWID Hexadecimal string representing the unique
  address of a row in its table. This datatype is
  primarily for values returned by the ROWID
  pseudocolumn.
• CHAR(size) Fixed-length character data of
  length size in bytes. Maximum size is 2000 bytes.
  Default and minimum size is 1 byte.

7
SQL - Datatypes (Contd)

• CLOB A character large object containing
  single-byte characters. Both fixed-width and
  variable-width character sets are supported, both
  using the CHAR database character set. Maximum
  size is 4 GB.
• BLOB A binary large object. Maximum size is 4
  gigabytes
• BFILE A locator value. This locator points to a
  large binary file stored outside the database.
  Enables byte stream I/O access to external LOBs
  residing on the database server. Maximum size is
  4 gigabytes.
• LOB datatypes can be stored inline (within a
  table), out-of-line (within a tablespace, using a
  LOB locator) or in an external file (BFILE
  datatypes)

8
SQL Parsing

• Parsing is the process by which SQL compiler
  converts the SQL statement to an executable
  command
• SQL compiler performs several steps to produce an
  access plan that can be executed.

9
SQL Parsing -- Contd..

• Steps Involved
• 1) Parse the query The SQL compiler analyses
  the SQL query to validate its syntax. If any
  error is there SQL compiler returns the
  appropraite SQL error to the application. After
  this step is complete, an internal representation
  of the query is created.
• 2) Checks for inconsistencies among parts of the
  statement.
• eg Compiler verifies that the datatype of the
  column specified for the YEAR function is date .
• 3) Rewrites the query Transforms the query into
  a form that can be optimised more easily

10
SQL Parsing -- Contd..

• 4) Optimizes the Access Plan Chooses the plan
  to retrieve data with the smallest estimated
  execution cost
• 5) Generate Executable code The compiler uses
  the access plan and to create an executable
  access plan, or section, for the query. Using
  this executable code, the database manager will
  determine how to access the data and provide
  results for the query.

11
SQL-Operators

• , - , , / - Arithmetic Operators.
• - Concatenation Operators.
• NOT, And, Or - Logical Operators.
• Union, Union All, Intersect, Minus - Set Op.
• Between, Not Between, Any, Some, All, Like, Not
  Like, Is, Is Not, In, Not In, Exists, Not Exists,
  , !, ltgt, lt, gt, lt, gt
• - Comparison Operators.
• (Outer Join operator), Prior - Others.

12
SQL-Operators, Precedence

• ( ) surrounds sub-queries or list of columns
• surrounds a literal such as character or
  string or date constant
• surrounds a table or column alias that
  contains special
• character or a space
• ( ) overrides normal operator precedence
• - addition subtraction. Also used as prefix
  sign for numbers or
• number expression
• char value concatenation

13
SQL-Operators, Precedence

• NOT reserves result of an expression
• AND True if both conditions are true
• OR True if either condition is true
• UNION returns all distinct rows from both
  of two queries
• INTERSECT returns all matching distinct rows
  from two queries
• MINUS returns all distinct rows in first
  query that are not in
• second
• UNION ALL returns all rows from both of two
  queries with the
• intersecting rows
  repeating
• Note - Operators with equal precedence are
  evaluated from left to right. All ANDs are
  evaluated before any OR

14
Different Types of SQL Commands

• DDL - Data Definition Language -
  Create, Alter, Drop, Truncate, Rename,
  Comment.
• DML - Data Manipulation Language - Select,
  Insert, Update, Delete, Lock Table.
• DCL - Data Control Language - Grant, Revoke.
• TCL - Transaction Control Language - Commit,
  Rollback, Savepoint, Set Transaction.
• SCL - Session Control language - Alter session.

15
References

• Oracle9i The Complete Reference Oracle Press