SQL usually pronounced as “Sequel” stands for Structured Query
Language.
SQL is the native language of the Oracle Server. It is the language
used to communicate with the database. Again SQL consists of SQL
statements and SQL*Plus commands. SQL statements are used to talk to the
database. When you enter a SQL statement, it is stored in a part of the
memory called the SQL Buffer and remains there until a new statement is
entered. SQL*Plus is an Oracle tool that recognizes and submits
SQL statements to the Oracle server for execution and contains its own
command language.
Features of SQL:-
It is very easy to write, it can be used by all kinds of users with
little or not programming experience.
It is a non-procedural language.
It reduces the amount of time required for creating and maintaining
systems.
It is based on American National Standards Iinstitute (ANSI) standard
SQL.
It manipulates data and tables definition in the database.
It is entered into SQL buffer on one or more lines.
It does not have a continuation character.
It cannot be abbreviated.
It uses a termination character to execute command immediately.
Uses functions to perform some formatting.
Features of SQL*Plus :-
Recognizes SQL statements and sends them to the server.
It accepts SQL input from files.
It provides a line editor for modifying SQL statements.
It controls environmental settings.
Accesses local and remote databases.
It doesn’t allow manipulation of values in the database.
It is entered one line at a time and is not stored in the SQL buffer.
Has a dash(-) as a continuation character if command exceed one line.
Doesn’t require any termination character, commands are executed
immediately.
It uses commands to format data.
Advantages of SQL :-
SQL is a high level language that provides a greater degree of
abstraction than procedural languages. It is designed so that the
programmer can specify what data is needed but need not specify how to
retrieves it.
Applications written in SQL can be easily ported across systems.
SQL as a language is independent of the way it is implemented
internally.
The language even being simple and easy to learn can handle complex
situations.
It is not just a query language, it can be used to define data
structures, control access to the data and delete, insert, modify occurrences
of the data.
The results to be expected are well defined i.e, there is no ambiguity
about the way a query will interpret the data and produce the result.
Types of SQL :-
SQL can be classified on the basis of its
various functionality, listed below.
Querying data.
Updating, inserting and deleting database objects.
Controlling access to the database.
Providing data integrity and consistency.
SQL statements are divided into following
types.
Data Definition Language (DDL)
Data Manipulation Language (DML)
Transaction Control Lauguage (TCL)
Data Control Language (DCL)
1)Data Definition Language DDL) :-
The DDL commands enables you to perform :--
CREATE statement.
ALTER statement.
DROP statement.
RENAME statement.
TRUNCATE statement.
The objects that can be created, altered, dropped are :-
A DML command is used when we want to add,
update or delete data in the database. A collection of DM statements that form
a logical unit of work is called a transaction.
The DML commands are :-
INSERT statement.
UPDATE statement.
DELETE statement.
3)Transaction Control Language
(TCL) :-
As we know transaction is nothing but a set
of inserts, updated and deletes (performed on the database) that form a logical
unit of work. TCL is used to control these transactions i.e , whether or not a
transaction should take place is controlled by TCL.
The TCL commands are :-
COMMIT statement.
ROLLBACK statement.
SAVEPOINT statement.
4)Data Control Language (DCL) :-
The DCL commands enable you to grant or
revoke user privileges and roles.
An Object is a reusable
application component that developers need to be aware of, rather than how it
works. Object are basic entities in a system. They could represent a person,
place, bank account, or any item that is handled by program. Every object consists
of an attribute and one or more methods. An attribute could be any property of
the object.
Class:
It is a collection of attributes and functions (method) to
plan the object.
Object
Table :
· Object
table are created by using the user defined data types.
·In an
object table each row or record is treated as an object.
·Each row in
an object table has an object Identified (OID), which is unique through out the database.
·The rows or
objects of an object table can be referenced by other objects with in the database.
·An object
table is created using the CREATE TABLE command.
·All object
types are associated with default method applied upon the relational tables ,
i.e INSERT, DELETE, UPDATE and SELECT.
·The
relational DML operation style is accepted only when the user defined data type
is a collection of Build-in data types, and the object table doesn't contain any
REF Constraints.
Creating an
user defined Object type :-
Syntax
SQL> CREATE OR REPLACE TYPE
Object (Element1 (size),
(Element2 (size),
(ElementN (size) );
All user
defined data types are schema objects of the database.
The user
defined object data type can be used as Reference in other tables.
All user
defined data types and object are stored permanently in the Data dictionaries.
USER_TYPES
USER_OBJECTS
We can
query for the uesr defined data types and objects using the relational SELECT.
SQL> Select Type_Name, TypeCode,
Attributes,Methods
FROM USER_TYPES;
SQL> Select Object_Name,
Object_Type
FROM USER_OBJECTS;
Creating
User Defined Address Type :-
SQL> Create or Replace TYPE Addr_type AS OBJECT (
Hno VARCHAR2 (10),
Street VARCHAR2 (20), City VARCHAR2
(20), Pin NUMBER(6));
SQL> Create or Replace TYPE PF_TYPE AS OBJECT (
PFNO NUMBER(6), AMT NUMBER(12,2));
The above
statement create the user defined object data type called as Addr_type and PF_TYPE in the data dictionary called USER_TYPES.
This data
type is also called as collection in oracle, and this collection is reusable
where ever the same data type collection is expected in project development.
Note :-
Alias is must to retrieve/manipulate Object elements.
Update data
from Object Table:-
SQL> UPDATE EMPLOYEE E SET BASIC =BASIC+100 WHERE
E.ADDRESS.CITY=’HYD’;
Deleting
data from Object Table:-
SQL> DELETE FROM
EMPLOYEE;
SQL> DELETE FROM
EMPLOYEE E WHERE E.PF.PFNO=1200;
Creating
User Defined Emp Det Type:
SQL> Create type
info As Object (code Number(4), Name Varchar2(20), Hno Varchar2(10), Street
Varchar2(20), City Varchar2(20), Pin Number(6));
Creating an
Object Table:-
Is a table
which is entirely build from the abstract type.
Syntax
Create Table (Table_Name) of (Object
Name);
Inserting Rows into Object Table:-
To insert a
record into an Object Table we may use the CONSTRUCTOR METHOD of the actual
data type or directly implement the RELATIONAL INSERT statement.
The Normal;
INSERT or RELATION INSERT is possible only when the table does not contain any
nested data types.
Relation Insert:-
SQL> Insert
into Emp_info Values(1001, ‘ram’, ‘12-123’, ‘AMEERPET’,’MGS’,’BLORE’,516217);
Inserting Using Construct Method:-
SQL> Insert into Emp_info values(info(1002,’suman’,’12-124’,’vijiinag’,’blore’,516217);
This clause in a select statement arranges the data
on a specified column in ascending or in descending order.
This clause will work after the execution of select
statement.
By default it arranges the data in ascending order.
In order to arrange the data in descending order ,
an option called DESC is used.
Data is arranged in order over a required column
for temporary.
Order by clause can be specified with one or more
columns.
Examples
:-
Display empno, ename and salary of all the
employees arranging salaries in ascending order.
Ans : Select empno, ename, sal from emp order by
sal;
Display ename, job, salary, deptno, commission, and
hiredate of all those employees who are working as CLERK, SALESMAN and they
have been hired in the year 81 and they don’t earn any commission. Arrange the
data in ascending order of salary.
Ans : Select ename, job, sal, deptno , comm,
hiredate from emp
Where
job IN(‘CLERK’ , ‘SALESMAN’) AND
Hiredate
LIKE ‘%81’ AND
Comm
IS NULL
Order
by sal;
Display ename, job, sal of all the employees arranging
job in ascending order and salary in descending order.
Ans : Select ename, job, sal from emp
Order
by job, sal desc;
Display ename, job, sal of all those employees
arranging job and sal in ascending order.
These characters are
used to provide pattern matching which means comparision to data is provided
with partial value.
ORACLE provides the
following wild characters.
i)_(Underscore) -> Represents single
character.
ii)%(Percentage) -> Represents group of
character.
ØInorder
to have wild characters for the comparision to partial data, LIKE operator is
used.
ØIf
these wild characters are formed in data, then the wild characters can be
converted to data using ‘\’ (backslash). It is represented through an option
called ‘ESCAPE’ , which is written after like operator.
Examples
:-
Display employee
number and employee name of all those employees whose names contain 4
characters.
Ans : select empno,
ename from emp
Where ename LIKE ‘----‘;
Display ename, job of
all those employees whose name starts with ‘A’.
Ans : select empno,
ename from emp
Where ename LIKE ‘A%’;
Display ename, job of
all those employees whose name ends with ‘S’.
Ans : select empno,
ename from emp
Where ename LIKE ‘%S’;
Display ename, job,
sal of all those employees whose name contain ‘A’.
Ans : Select ename,
job, sal from emp
Where ename LIKE ‘%A%’;
Display ename, sal of
all those employees whose salary is ending with ’00’.
Ans : Select ename,sal
from emp
Where sale LIKE ‘’;
Display ename, hiredate
of all those employees who have been hired in the year 81.
Ans : Select ename,
hiredate from emp
Where hiredate LIKE ‘%81’;
Display empno, ename,
hiredate of all those employees who have been hired in those months whose name
doesn’t starts with ‘A’.
Ans : Select empno,
ename, hiredate from emp
Where hiredate NOT LIKE ‘_ _ _ _A%’;
Display all those
employees whose designation contains ‘_’.
