SQL
SQL(Structured Query Language)
INTRODUCTION
Sql stands for Structured Query Language.
Sql helps you to perform activities related to querying & manipulating databases.
Sql helps you to perform activities related to querying & manipulating databases.
SQL COMMAND ARE DIVIDED INTO TWO CATAGORIES
1. DDL (Data Definition Language)
2. DML (Data Manipulation Language)
2. DML (Data Manipulation Language)
DATA DEFINITION LANGUAGE
The DDL commands are used to define a data & objects in a database.
The DDL commands are
The DDL commands are
Syntax
Description
Create Table
Is used to create a table
Alter Table
Is used to alter or modify the table
Drop Table
Is used to Delete Table
Create Index
Is used to create an index
Drop Index
Is used to delete an index
Grant
Is used to grant access privileges to a user
DATA MANIPULATION LANGUAGE
The DML is used to manipulate data through queries it also changes the content of a Table.
The DML commands are
The DML commands are
Syntax
Description
Select
Is used to display the data from a table
Insert
Is used to insert a row in a table
Delete
Is used to Delete a row from the Table
Update
Is used to change the content of the table
Example
|
XSL-FO DOCUMENT
XSL-FO documents are stored in files with a .fo or a .fob file extension.
: The root element is the root element of the XSL-FO document.
element that declares the “fo” namespace prefix.
: The masters set to create one or more
templates for pages,
page sequences and regions.
: The page sequence represents a sequence
of pages formatted the
way you want.
: The page masters are templates for
individual pages
The master page layout holds the all master used in the
Document.
templates for pages,
page sequences and regions.
of pages formatted the
way you want.
individual pages
The master page layout holds the all master used in the
Document.
DATA TYPES
DATA TYPES
The Data types are used to declare variable type which is stored in a specified format.
Char :
The value of this data type is of fixed length character strings of maximum
length of 255 characters.
Char :
The value of this data type is of fixed length character strings of maximum
length of 255 characters.
Example:
Emp_city Char (20);
This declares the emp_city variable of character type of fixe size is 20.
Emp_city Char (20);
This declares the emp_city variable of character type of fixe size is 20.
Varchar2:
The value of this data type is variable length character strings of maximum length 2000.
The value of this data type is variable length character strings of maximum length 2000.
Example:
Emp_city Varchar (20);
This declares Emp_city variable of character type having maximum variable
length as 20.The length is not fixed to 20 depending on the name of the city
the size will get allocated.
Emp_city Varchar (20);
This declares Emp_city variable of character type having maximum variable
length as 20.The length is not fixed to 20 depending on the name of the city
the size will get allocated.
Example:
If the name of the city is Bangalore, size allocated is 8 characters.
If the name of the city is Bangalore, size allocated is 8 characters.
Number:
The value of this data type is either fixed or floating point.
This datatype can store numbers up to 38 digits.
The value of this data type is either fixed or floating point.
This datatype can store numbers up to 38 digits.
Example:
Emp_id number (10);
Dept_id number (10);
Emp_id number (10);
Dept_id number (10);
Date:
The Standard Date format is DD-MM-YY in SQL.
The Standard Date format is DD-MM-YY in SQL.
Example:
18-Mar-11
18-Mar-11
Long:
This datatype is used to store variable length character
strings containing up to 65,535 characters (up to 2 GB).
.Long data are generally used to store arrays of binary data in ASCII format.
This datatype is used to store variable length character
strings containing up to 65,535 characters (up to 2 GB).
.Long data are generally used to store arrays of binary data in ASCII format.
Long Raw:
This data type is similar to Varchar .Long Raw is used to store
binary data/files. It can store binary data up to 4000 bytes.
This data type is similar to Varchar .Long Raw is used to store
binary data/files. It can store binary data up to 4000 bytes.
LOB data types:
To store large object LOB data type is used.
To store large object LOB data type is used.
CREATE TABLE
CREATE TABLE
To create a Table:
To create a Table Create Table command is used
Syntax:
Create table tablename
(Columnname1 datatype, Columnname2 datatype);
To create a Table Create Table command is used
Syntax:
Create table tablename
(Columnname1 datatype, Columnname2 datatype);
Example:
create table student
(stud_id number (10),
stud_name varchar (20),
stud_dob date);
create table student
(stud_id number (10),
stud_name varchar (20),
stud_dob date);
Modifying the structure of Table:
Table can be modified in three ways:
a. Adding a column to an existing table:
Syntax:
alter table tablename
Add(newcolumnname datatype);
Table can be modified in three ways:
a. Adding a column to an existing table:
Syntax:
alter table tablename
Add(newcolumnname datatype);
Example:
Alter table student
Add (stud_add varchar2 (30),
stud_email varchar (20));
Alter table student
Add (stud_add varchar2 (30),
stud_email varchar (20));
b.Manipulating a columns definition :
Syntax:
Alter table tablename
modify (columnname datatype);
Syntax:
Alter table tablename
modify (columnname datatype);
Example:
alter table student
modify (stud_add varchar2 (40));
alter table student
modify (stud_add varchar2 (40));
c. Dropping an existing column:
Syntax:
There are two methods to drop a column:
• set unused clause:
e.g. alter table student set unused column stud_add;
If the column is marked as unused you cannot access that column.
Making a column as ‘unused ‘does not release the space previously used by the column until you drop the unused columns as:
Alter table stud drop unused columns; to the page
Syntax:
There are two methods to drop a column:
• set unused clause:
e.g. alter table student set unused column stud_add;
If the column is marked as unused you cannot access that column.
Making a column as ‘unused ‘does not release the space previously used by the column until you drop the unused columns as:
Alter table stud drop unused columns; to the page
• drop clause:
E.g. alter table student drop column stud_add;
Multiple columns can be dropped as:
E.g. alter table student drop (stud_email, stud_dob);
If the dropped columns are part of primary key or unique constraints then you have to cascade constraints clause as part of alter table command.
E.g. alter table student drop column stud_add;
Multiple columns can be dropped as:
E.g. alter table student drop (stud_email, stud_dob);
If the dropped columns are part of primary key or unique constraints then you have to cascade constraints clause as part of alter table command.
Dropping Tables:
Table can be deleted using Drop Table command.
Syntax:
Drop table tablename;
E.g. Drop table student;
Table can be deleted using Drop Table command.
Syntax:
Drop table tablename;
E.g. Drop table student;
Truncate Command:
Truncate command is used to remove all rows in the table & it reclaims the space for other uses without removing table definition from database. Truncating can’t be rolled back.
Syntax:
Truncate Table tablename;
E.g. truncate table student;
Truncate command is used to remove all rows in the table & it reclaims the space for other uses without removing table definition from database. Truncating can’t be rolled back.
Syntax:
Truncate Table tablename;
E.g. truncate table student;
CONSTRAINTAS IN CREATE TABLE
a. Uniqueness of column.
b. Matching of column values (primary & foreign key).
c. Values of certain column within specified range.
Column Level Constraints:
If the constraints are defined along with the column definition, it is called column level constraint. This constraint can be applied to only one column at a time. If the constraint spans across multiple columns, the user will have to use table level constraints.
b. Matching of column values (primary & foreign key).
c. Values of certain column within specified range.
Column Level Constraints:
If the constraints are defined along with the column definition, it is called column level constraint. This constraint can be applied to only one column at a time. If the constraint spans across multiple columns, the user will have to use table level constraints.
Table Level Constraints:
Table level constraints are used if the data constraints attached to a specific column in a table reference the contents of another column in the table. Table Level constraints are stored as a part of the global table definition.
Table level constraints are used if the data constraints attached to a specific column in a table reference the contents of another column in the table. Table Level constraints are stored as a part of the global table definition.
The Candidate Key:
A Candidate Key is a combination of one or more columns, the values of which uniquely identify each row of a table.
A Candidate Key is a combination of one or more columns, the values of which uniquely identify each row of a table.
Unique Key:
Example:
Create table employee
(Emp_id number not null,
City varchar (20) not null,
Date date not null constraint trouble-UQ UNIQUE (Emp_id, city));
The unique key is combination of Emp_id & city. Not Null can be defined to ensure that value has to be input (By default all column in table allow nulls).
Unique key constraint is used to check there are no duplicated values in any of the rows.
Example:
Create table employee
(Emp_id number not null,
City varchar (20) not null,
Date date not null constraint trouble-UQ UNIQUE (Emp_id, city));
The unique key is combination of Emp_id & city. Not Null can be defined to ensure that value has to be input (By default all column in table allow nulls).
Unique key constraint is used to check there are no duplicated values in any of the rows.
Primary Key Constraint:
In the primary key of table you can give some special characteristics.
There is only one primary key in the table & primary key doesn’t contain nulls.
In the primary key of table you can give some special characteristics.
There is only one primary key in the table & primary key doesn’t contain nulls.
Example:
create table doctor (doc_id number primary key, doc_name varchar2 (20), doc_city varchar2 (20));
create table doctor (doc_id number primary key, doc_name varchar2 (20), doc_city varchar2 (20));
You can create a primary key with multiple columns:
Example:
create table doctor (doc_id number, doc_name varchar2 (20), doc_city varchar2 (20) Constraint Trouble-PK primary key (doc_id, doc_city));
Example:
create table doctor (doc_id number, doc_name varchar2 (20), doc_city varchar2 (20) Constraint Trouble-PK primary key (doc_id, doc_city));
Foreign Key Constraint:
A Foreign Key Constraint is a combination of columns with values based on the primary key values from another table.
A Foreign Key Constraint is also known as referential integrity constraints.
The REFERENCE clause is used to define the Foreign Key Constraint as it specifies the values of the column correspond to actual values of the primary key in the other table.
A Foreign Key Constraint is a combination of columns with values based on the primary key values from another table.
A Foreign Key Constraint is also known as referential integrity constraints.
The REFERENCE clause is used to define the Foreign Key Constraint as it specifies the values of the column correspond to actual values of the primary key in the other table.
Example:
create table doctor (doc_id number, doc_name varchar2, ward_no number (20), doc_city varchar2 (20) Constraint doctor-PK primary key (doc_id) foreign key (ward no) References room_ward (ward_no));
create table doctor (doc_id number, doc_name varchar2, ward_no number (20), doc_city varchar2 (20) Constraint doctor-PK primary key (doc_id) foreign key (ward no) References room_ward (ward_no));
Check Constraint:
The Check Constraint is used to specify the values for a column that are within a certain range or that satisfy certain conditions.
The Check Constraint is used to specify the values for a column that are within a certain range or that satisfy certain conditions.
Example:
create table student (
stud_id number (20),
stud_name varchar2 (20),
stud_age number check (age between 6 AND 20));
create table student (
stud_id number (20),
stud_name varchar2 (20),
stud_age number check (age between 6 AND 20));
Altering the Constraints:
The Constraints can be changed using alter Table command.
The Constraints can be changed using alter Table command.
Example:
alter table emp
add Primary Key (emp_id);
alter table emp
add Primary Key (emp_id);
Example:
alter table emp
Add constraint dept-key foreign key (deptno) reference dept, modify (ename varchar2 (10) not null);
alter table emp
Add constraint dept-key foreign key (deptno) reference dept, modify (ename varchar2 (10) not null);
INSERT TABLE
CREATE TABLE FROM A TABLE
You can create a table from an existing table
Syntax:
Create table tablename
(columnname1, columnname2)
as select columnname1, columnname2
from tablename;
Syntax:
Create table tablename
(columnname1, columnname2)
as select columnname1, columnname2
from tablename;
Example:
Create table stud_details
(stud_id, stud_name)
as select stud_id, stud_name
from student;
Create table stud_details
(stud_id, stud_name)
as select stud_id, stud_name
from student;
Insertion of Data in to Table:
To insert data into a table INSERT command is used. Only a single row can get inserted into a table.
Syntax:
Insert into tablename
(columnname1, columnname2)
Values (expression1, expression2);
To insert data into a table INSERT command is used. Only a single row can get inserted into a table.
Syntax:
Insert into tablename
(columnname1, columnname2)
Values (expression1, expression2);
Example:
insert into student
Values (1, ABC);
insert into student
Values (1, ABC);
Example:
insert into stud_details
(stud_id, stud_name, stud_add)
Values (1, Ram, Pune);
insert into stud_details
(stud_id, stud_name, stud_add)
Values (1, Ram, Pune);
Inserting Data into a table from another table:
Using insert command with select option, you can insert data from one table to another existing table.
Syntax:
insert into tablename
select columnname1, columnname2
from tablename;
Using insert command with select option, you can insert data from one table to another existing table.
Syntax:
insert into tablename
select columnname1, columnname2
from tablename;
Example:
Insert into emp
Select empno, ename
From emp1;
Insert into emp
Select empno, ename
From emp1;
Example:
insert intoemp3
select * from emp1;
insert intoemp3
select * from emp1;
Insertion of data from one table to another existing table using where option:
Using insert with select command with where option, you can insert data from one table to another existing table.
Syntax:
Insert into tablename
select columnname1, columnname2
from tablename
Where column=expression;
Using insert with select command with where option, you can insert data from one table to another existing table.
Syntax:
Insert into tablename
select columnname1, columnname2
from tablename
Where column=expression;
Example:
insert into emp2
Select empno, ename
from emp1
where empno=12;
insert into emp2
Select empno, ename
from emp1
where empno=12;
Updating the contents of a table:
You can change the contents of the existing columns, by using update command.
Syntax:
Update tablename
Set columnname1 = expression1,
columnname2 = expression2
where condition;
You can change the contents of the existing columns, by using update command.
Syntax:
Update tablename
Set columnname1 = expression1,
columnname2 = expression2
where condition;
Example:
update stud
set stud_name = ‘Sam’,
stud_city=’London’,
where stud_id=10;
update stud
set stud_name = ‘Sam’,
stud_city=’London’,
where stud_id=10;
Deletion operations:
The delete command removes all he rows using following:
Syntax:
Delete from tablename;
Delete from student;
The delete command removes all he rows using following:
Syntax:
Delete from tablename;
Delete from student;
Deleting specified number of rows:
When using delete command with where clause, it deletes the specified
rows matching the condition from the table.
Syntax:
delete from tablename
where condition;
When using delete command with where clause, it deletes the specified
rows matching the condition from the table.
Syntax:
delete from tablename
where condition;
Example:
delete from student
where stud_id =12;
delete from student
where stud_id =12;
SELECT COMMAND
SELECT COMMAND
Selection of the Table:
To display the data from a table “select” command is used.
Syntax:
Select * from tablename;
To display the data from a table “select” command is used.
Syntax:
Select * from tablename;
Example:
SQL> select * from student;
Here the data of the table is displayed.
SQL> select * from student;
Here the data of the table is displayed.
For retrieval of specific columns from a table:
Select command is used with column names.
Syntax:
Select columnname1, columnname2
from tablename;
Select command is used with column names.
Syntax:
Select columnname1, columnname2
from tablename;
Example:
SQL> select stud_id, stud_name
from student;
Here the data of the stud_id & stud_name will be displayed
SQL> select stud_id, stud_name
from student;
Here the data of the stud_id & stud_name will be displayed
Selecting data set from table data:
For retrieval of selected rows Select command is used with where clause.
Syntax:
select columnname1, columnname2
from tablename
where condition;
For retrieval of selected rows Select command is used with where clause.
Syntax:
select columnname1, columnname2
from tablename
where condition;
Example:
select stud_id, stud_name
from student
where stud_id = 2;
In this example only the whole row data of the stud_id =2 will be displayed.
select stud_id, stud_name
from student
where stud_id = 2;
In this example only the whole row data of the stud_id =2 will be displayed.
Selecting Distinct (Unique) records:
To eliminate duplicate records from the table the select command with Distinct clause is used.
Syntax:
Select distinct columnname1, columnname2 from tablename;
To eliminate duplicate records from the table the select command with Distinct clause is used.
Syntax:
Select distinct columnname1, columnname2 from tablename;
Example:
SQL> select distinct stud_name from student;
SQL> select distinct stud_name from student;
Using Order By clause:
To display the data of the table with specific sorted order, select command with order by clause is used.
Syntax:
Select columnname1, columnname2
From tablename
Order by columnname;
To display the data of the table with specific sorted order, select command with order by clause is used.
Syntax:
Select columnname1, columnname2
From tablename
Order by columnname;
Example:
SQL> select empno, ename, deptno
from emp
Order by deptno;
SQL> select empno, ename, deptno
from emp
Order by deptno;
OPERATOR
OPERATOR
IN Operator:
The” IN” operator is used to check values of number or character is equal to one of the value, specified after” in” operator.
The” IN” operator is used to check values of number or character is equal to one of the value, specified after” in” operator.
Example:
select empno, empname, sal, deptno from emp where deptno IN (10, 20, 30);
select empno, empname, sal, deptno from emp where deptno IN (10, 20, 30);
NOT IN Operator:
The” IN” operator is used to check values of number or character is equal to one of the value, specified after” in” operator.
The” IN” operator is used to check values of number or character is equal to one of the value, specified after” in” operator.
Example:
select empno, empname, sal, deptno from emp where deptno NOT IN (10, 20, 30);
select empno, empname, sal, deptno from emp where deptno NOT IN (10, 20, 30);
BETWEEN Operator:
To check the values between the ranges, BETWEEN operator is used
To check the values between the ranges, BETWEEN operator is used
Example:
SQL> select stud_id, stud_name, stud_add
From student
Where stud_id between 10 and 30;
SQL> select stud_id, stud_name, stud_add
From student
Where stud_id between 10 and 30;
NOT BETWEEN Operator:
Example:
SQL>select stud_id, stud_name from student where stud_id NOT between 20 and 30
Example:
SQL>select stud_id, stud_name from student where stud_id NOT between 20 and 30
AND & OR Operator:
The AND operator is used to combine two logical expressions & the rows which satisfy condition will be displayed.
The AND operator is used to combine two logical expressions & the rows which satisfy condition will be displayed.
Example:
SQL> select empno, ename, sal, deptno from emp where sal>2000 and deptno=10;
The OR operator is also used to combine two logical expressions but in this case the rows which satisfy one of the condition will be displayed.
SQL> select empno, ename, sal, deptno from emp where sal>2000 and deptno=10;
The OR operator is also used to combine two logical expressions but in this case the rows which satisfy one of the condition will be displayed.
Example:
SQL>select empno, ename, sal, deptno
From emp
Where sal>3000 or deptno =10;
SQL>select empno, ename, sal, deptno
From emp
Where sal>3000 or deptno =10;
Combining Logic:
The AND & OR logic can be combined in a virtually unlimited number of ways .The AND is always acted on first since AND is having higher precedence than OR.
The AND & OR logic can be combined in a virtually unlimited number of ways .The AND is always acted on first since AND is having higher precedence than OR.
Example
SQL> select empno, ename, sal, deptno
from emp
where sal > 2000 and (deptno =10 or deptno = 20);
SQL> select empno, ename, sal, deptno
from emp
where sal > 2000 and (deptno =10 or deptno = 20);
OTHER OPERATOR
Operator
Example
=
sal=1000
>
sal>1000
>=
sal>=1000
<
sal<5000 span="">
<=
sal<=5000
!= or ^=
sal!=4000/ sal^=4000
Not equal<>
Not equal
LIKE OPERATOR
Like is a pattern matching operator, which is able to search through
the rows of a database column for values that look like a pattern you describe.
It uses
1. (% ) a percent sign called a wild card (It represents any number of spaces or characters)&
2. An underline (-) called a position marker (It represents one space or character).
the rows of a database column for values that look like a pattern you describe.
It uses
1. (% ) a percent sign called a wild card (It represents any number of spaces or characters)&
2. An underline (-) called a position marker (It represents one space or character).
Example:
SQL> select stud_id, stud_name, stud_city
From student
Where stud_name like”J%”;
Here the example shows that, it will display names of the student starting with “J”.
SQL> select stud_id, stud_name, stud_city
From student
Where stud_name like”J%”;
Here the example shows that, it will display names of the student starting with “J”.
Example:
SQL>select stud_id, stud_name
From student
Where stud_name like “-I”;
This will display the names having “I” as the second letter like “Sita, Nita”.
SQL>select stud_id, stud_name
From student
Where stud_name like “-I”;
This will display the names having “I” as the second letter like “Sita, Nita”.
Example:
SQL>select stud_id, stud_name
From student
Where stud_name like “%A%A”;
Here it will display the name of the student having 2 A’s.
SQL>select stud_id, stud_name
From student
Where stud_name like “%A%A”;
Here it will display the name of the student having 2 A’s.
IS Null, Is Not Null:
Null or not null is used to check the values of the particular column.
The word Null and not null is must to check equality.
Null or not null is used to check the values of the particular column.
The word Null and not null is must to check equality.
Example:
SQL> select stud_id, stud_name
From student
Where add is null;
SQL> select stud_id, stud_name
From student
Where add is null;
JOINS
JOINS
If you want to select data from two tables you should use “Join” command.
There are three types of joins:
• Equijoin
• Selfjoin
• Outerjoin
• Equijoin
• Selfjoin
• Outerjoin
Equijoin:
In Equijoin two or more tables are joined together with “=” operator, using the where clause.
If two tables are joined then only one “=” operator is used.
In Equijoin two or more tables are joined together with “=” operator, using the where clause.
If two tables are joined then only one “=” operator is used.
Example:
select emp_name, dep_name
Where emp.deptno=dept.deptno;
select emp_name, dep_name
Where emp.deptno=dept.deptno;
Selfjoin:
In Selfjoin, logically one table is treated as two tables &recursive connection (pointer to it) is used.
In Selfjoin, logically one table is treated as two tables &recursive connection (pointer to it) is used.
Example:
select v1.vender_name, v2.vname coll_name
from vender v1,vender v2
where v1. coll_name=v2.vender_no;
select v1.vender_name, v2.vname coll_name
from vender v1,vender v2
where v1. coll_name=v2.vender_no;
Outerjoin:
When there is no child & we want to list parent in that case Outerjoin is used.
When there is no child & we want to list parent in that case Outerjoin is used.
Example:
select vname, d.deptno from vender v, dept d where v.deptno=d.deptno;
select vname, d.deptno from vender v, dept d where v.deptno=d.deptno;
Example:
select vname, d.deptno from vender v, dept d where v.deptno (+) =d.deptno;
select vname, d.deptno from vender v, dept d where v.deptno (+) =d.deptno;
NESTED QUERY/SUB QUERY/INNER QUERY
• A subquery is a form of an SQL statement that appears inside another SQL statement.
• The statement containing a subquery is called a parent statement.
• It can be used for creating tables & inserting the records in the target table.
• Updating records in the target table, To create view.
• To provide values for the conditions where, having, in select, update & delete statement
• The statement containing a subquery is called a parent statement.
• It can be used for creating tables & inserting the records in the target table.
• Updating records in the target table, To create view.
• To provide values for the conditions where, having, in select, update & delete statement
Example:
Find out the employee having the highest salary in the organization
select * from employee where gross-sal= (select max (gross_sal) from employee);
Find out the employee having the highest salary in the organization
select * from employee where gross-sal= (select max (gross_sal) from employee);
Example:
Print the names of student who stay in city=”Pune”
select stud_name form student where stud_id= (select stud_id from student where
stud_city =”Pune”);
Print the names of student who stay in city=”Pune”
select stud_name form student where stud_id= (select stud_id from student where
stud_city =”Pune”);
FUNCTION
FUNCTION
CHARACTER FUNCTION
Use of Concatenation (||):
Example:
select stud_id ||’ ‘|| stud_name
from student;
Example:
select stud_id ||’ ‘|| stud_name
from student;
Concat:
Concatenates two strings together same as ||
Concatenates two strings together same as ||
Example:
SQL>select concat (stud_id, stud_city) from student;
SQL>select concat (stud_id, stud_city) from student;
Initcap Initial Capital:
Capitalizes first letter of a word.
Syntax:
INITCAP (string)
Capitalizes first letter of a word.
Syntax:
INITCAP (string)
Example:
SQL>select stud_id, initcap (stud_name)
from student;
SQL>select stud_id, initcap (stud_name)
from student;
Lower:
Converts every letter in a string to lower case.
Syntax:
lower (string)
Converts every letter in a string to lower case.
Syntax:
lower (string)
Example:
SQL> select stud_id, lower (stud_name)
from student;
SQL> select stud_id, lower (stud_name)
from student;
Upper:
Converts every letter in a string to upper case.
Syntax:
Upper(string)
Converts every letter in a string to upper case.
Syntax:
Upper(string)
Example:
SQL>select stud_id,upper(stud_name)
from student;
SQL>select stud_id,upper(stud_name)
from student;
Length:
The length of string –how many characters if has in it including letters, spaces and
anything else.
Syntax:
Length(string)
The length of string –how many characters if has in it including letters, spaces and
anything else.
Syntax:
Length(string)
Example:
SQL> select stud_name length(stud_name)
from student;
SQL> select stud_name length(stud_name)
from student;
Substr:
Substring –clips out of piece of a string
Syntax:
Substr (string, start [, count])
Substring –clips out of piece of a string
Syntax:
Substr (string, start [, count])
Example:
SQL> select subbstr(stud_name 3)
from student;
SQL> select subbstr(stud_name 3)
from student;
When the string out of sub section of string stud_name, starting
from 3rd position till the
the end of the string;
from 3rd position till the
the end of the string;
Example:
SQL> select substr (stud_name, 3,2)
From student;
SQL> select substr (stud_name, 3,2)
From student;
Instr:
Find the location of a character in a string. It has two position, one within the other.
The
First option is start, the default, it will look for the set starting from 1st position.
Find the location of a character in a string. It has two position, one within the other.
The
First option is start, the default, it will look for the set starting from 1st position.
Syntax:
Instr (string, set [, start,[,occurance]])
Instr (string, set [, start,[,occurance]])
Example:
SQL> select stud_name,instr (stud_name,’A’) from student;
SQL> select stud_name,instr (stud_name,’A’) from student;
In this example if the character ‘A’ is present in stud_name then
instr return at what
Position if occurs otherwise returns 0.
instr return at what
Position if occurs otherwise returns 0.
Rpad and Lpad:
Allows you to concatenate spaces or other characters on the right or left side of string
Syntax:
Rpad (string, length [‘set’])
Lpad (string, length [,’set’])
Allows you to concatenate spaces or other characters on the right or left side of string
Syntax:
Rpad (string, length [‘set’])
Lpad (string, length [,’set’])
String is the name of char or varchar2 column from the database, length is the
total
Number of characters long that the result should be (width) set is the set of
characters
Do the padding the set must be enclosed in single quotation.
By default space is used if set
Of characters in not specified.
total
Number of characters long that the result should be (width) set is the set of
characters
Do the padding the set must be enclosed in single quotation.
By default space is used if set
Of characters in not specified.
Example:
SQl>
select rpad (stud_name, 10,’-’) from student;
select lpad (stud_name, 10,’-’) from student;
SQl>
select rpad (stud_name, 10,’-’) from student;
select lpad (stud_name, 10,’-’) from student;
Ltrim and Rtrim:
Trim off unwanted characters from the left and right ends of string.
Syntax:
Rtrim (string, [,’set’])
Ltrim (string, [,’set’])
Trim off unwanted characters from the left and right ends of string.
Syntax:
Rtrim (string, [,’set’])
Ltrim (string, [,’set’])
String is the name of column in database. Set is the collection of characters you want to Trim off.
If no set of characters is specified it trim off spaces.
If no set of characters is specified it trim off spaces.
Example:
SQL>
Select stud_name, ltrim (stud_name) from student;
SQL>
Select stud_name, ltrim (stud_name) from student;
You can trim off more than one char at a time, to do so, simply make a list of the
Characters you want to remove.
Characters you want to remove.
Example:
SQL>
Select Rtrim (stud_name, ‘.’’’) from student;
SQL>
Select Rtrim (stud_name, ‘.’’’) from student;
Soundex:
It finds the words that sound like other words, virtually regardless or how either is
spelled.
Syntax:
Soundex (string );
It finds the words that sound like other words, virtually regardless or how either is
spelled.
Syntax:
Soundex (string );
Example:
SQL>
Select stud_name from student where soundex (stud_name) =soundex (‘preeti’);
SQL>
Select stud_name from student where soundex (stud_name) =soundex (‘preeti’);
DATE FUNCTION
FUNCTION
DATE FUNCTION
Sysdate:
Current date
Current date
Example:
SQl>
Select sysdate from dual;
SQl>
Select sysdate from dual;
Adding Month:
Add count months to date.
Syntax:
add_months (date, count)
Add count months to date.
Syntax:
add_months (date, count)
Example:
SQL>
Select add_months (sysdate, 5) from dual;
SQL>
Select add_months (sysdate, 5) from dual;
Subtracting Months:
Less count months to date;
Less count months to date;
Example:
SQL>
Select add_months (sysdate,-2) from dual;
SQL>
Select add_months (sysdate,-2) from dual;
Greatest and Least:
Syntax:
Greatest (date1, date2, date3…….) // Picks the latest date from list of dates
Syntax:
Greatest (date1, date2, date3…….) // Picks the latest date from list of dates
Example:
SQL>
Select Greatest (sysdate,to_date’25-JAN-02’) from dual;
SQL>
Select Greatest (sysdate,to_date’25-JAN-02’) from dual;
Syntax:
Least (date1, date2, date3……) // Picks the earliest date from list of dates
Least (date1, date2, date3……) // Picks the earliest date from list of dates
Example:
SQL>
Select least (sysdate, ’25-JAN-02’) from dual;
SQL>
Select least (sysdate, ’25-JAN-02’) from dual;
Next_day:
Computers the date of the next named day of the week after the given date.
Computers the date of the next named day of the week after the given date.
Example:
SQL>
Select Next-day (sysdate, Friday) from dual;
If sysdate ->(13/02/2009) Wednesday
o/p -> 15-february-2009 Friday
SQL>
Select Next-day (sysdate, Friday) from dual;
If sysdate ->(13/02/2009) Wednesday
o/p -> 15-february-2009 Friday
Last_day:
Gives date of last day of month that date s in.
Gives date of last day of month that date s in.
Example:
SQL>
Select last_day (sysdate) from dual;
SQL>
Select last_day (sysdate) from dual;
Months_Between Two Months:
Gives difference between two dates in months.
Syntax:
Months_between (date1, date2)
Gives difference between two dates in months.
Syntax:
Months_between (date1, date2)
Example:
SQL>
Select stud_name months-between (sysdate, birthdate)as age from student;
SQL>
Select stud_name months-between (sysdate, birthdate)as age from student;
TO_CHAR (date[format]):
Reformats date according to format
Reformats date according to format
Example:
SQL>
Select stud_name to_char (birthdate, mm/dd/yy) as Birthdate from student;
SQL>
Select stud_name to_char (birthdate, mm/dd/yy) as Birthdate from student;
VIEW
VIEW
INTRODUCTION
A view is a virtual or logical table that allows viewing or manipulating of the contents of one or more table. View is created by a query that uses origin or base tables from which data is extracted. The view is stored the data dictionary as a text and contains the SQL command used in creation of the view. View is provided the data security. View prevents the updating a record. View avoids data redundancy.
CREATING A VIEW
It is a programming convention that a view name begins with vw to allows oneto distinguish a view from a table when name is used in SQL query.
Syntax:
Create [or replace ] view viewname as
Select columns from tablename Where condition
Create [or replace ] view viewname as
Select columns from tablename Where condition
Example:
SQL>
Create view vw_stud as
Select stud_id, stud_name from student Where Stud_id<=20;
SQL>
Create view vw_stud as
Select stud_id, stud_name from student Where Stud_id<=20;
To display the content of vw_stud view use select command
Select * from vw_stud;
Select * from vw_stud;
A View On Two Tables:
We are creating a view fron two tables, the name should b specify before the name of each
Column.
We are creating a view fron two tables, the name should b specify before the name of each
Column.
Example:SQL>
Create view vw_name as
Select emp.emp_no, emp.emp_name, dept.dept_no, dept.dept_name from emp, dept
Where emp.dept_no=dept.dept_no;
Create view vw_name as
Select emp.emp_no, emp.emp_name, dept.dept_no, dept.dept_name from emp, dept
Where emp.dept_no=dept.dept_no;
UPDATABLE VIEW
Updatable view is used for the manipulating the data. Updatable view can perform insert, update Or delete operation on the view. If the view containing the columns from a single table then you can changed values without any restriction and changes are reflected into base table immediately.
• The primary key column of the table should be included in the view.
• The select statement used for creating a view should not include.
o Aggregate function
o Distinct Operator
o Group by or having clause
o The Union, Union All, Interset and Minus operation
o Constants, string or value expressions.
• For insert if should include all the Not Null fields.
• The primary key column of the table should be included in the view.
• The select statement used for creating a view should not include.
o Aggregate function
o Distinct Operator
o Group by or having clause
o The Union, Union All, Interset and Minus operation
o Constants, string or value expressions.
• For insert if should include all the Not Null fields.
DICTIONARY VIEW
All_ Updatables_ View:
display all the columns of the tables can be changed
User_Updatable_Columns:
Display all the columns of the users tables and views that can be changed
DBA_Updatables_Columns:
Display all the columns of the DBAs tables and views that can be changed
display all the columns of the tables can be changed
User_Updatable_Columns:
Display all the columns of the users tables and views that can be changed
DBA_Updatables_Columns:
Display all the columns of the DBAs tables and views that can be changed
Creating read only View:
Views that do not allows data manipulation are called read only views. Views used the
“with read only” clause of the create view command to prevent users from manipulating records.
Views that do not allows data manipulation are called read only views. Views used the
“with read only” clause of the create view command to prevent users from manipulating records.
Example:
SQL>
Create view vw_stud as
Select * from student
With read only;
SQL>
Create view vw_stud as
Select * from student
With read only;
REPLACING A VIEW
The replacement of view means changing the definition of view in the data dictionary.
Example:
SQL>
Create or replace view vw_stud as
Select stud_id, stud_name from student
With read only;
SQL>
Create or replace view vw_stud as
Select stud_id, stud_name from student
With read only;
Destroying A View
View can be deleted using the drop view command
Syntax
Drop view viewname;
View can be deleted using the drop view command
Syntax
Drop view viewname;
Example:
SQL>
Drop view vw_name;
SQL>
Drop view vw_name;
The data dictionary is deleted , When the view is dropped, this command does not affect the base table.
User-View:
The definition of a view are stored n a view called User-view in the data dictionary.
The definition of a view are stored n a view called User-view in the data dictionary.
Example:SQL>
Describe user_views;
Describe user_views;
SEQUENCES
SEQUENCES
A sequence is an automatic counter which create unique identifiers for a record. The number is generated by sequences can be used to update a column. A sequence is an automatic counter which create unique identifiers for a record. The number is generated by sequences can be used to update a column.
To access sequence value the contents of two pseudo columns are used.
Currval:
Return current value of a sequence
Creating A sequence:
The create sequence command is used to create a sequence
Syntax:
Create sequence sequence_name
Start with integer
The create sequence command is used to create a sequence
Syntax:
Create sequence sequence_name
Start with integer
Increment by integer
Example:
SQL>
Create sequence seq
Start with 100
Increment by 10
Example:
SQL>
Create sequence seq
Start with 100
Increment by 10
You can specify minimum and maximum value of a sequence using MINVALUE and MAXVALUE.
Syntax:
Create sequence seq
MINVALUE integer
MAXVALUE integer
Syntax:
Create sequence seq
MINVALUE integer
MAXVALUE integer
NOMINVALUE and NOMAXVALUE:
This is used to indicate that a sequence doesn’t have any predefined minimum and maximum value.
This is used to indicate that a sequence doesn’t have any predefined minimum and maximum value.
CYCLE/NOCYCLE:
When cycle clause is used at the time of creating a sequence then that sequence reinitialized to the starting value
If nocycle is used then sequence is not reinitialized.
When cycle clause is used at the time of creating a sequence then that sequence reinitialized to the starting value
If nocycle is used then sequence is not reinitialized.
Syntax:
CREATE SEQUENCE sequence_name
Start with integer
Increament by integer
Minvalue integer/Nominvalue
Maxvalue integer /Nomaxvalue
Cycle/Nocycle
CREATE SEQUENCE sequence_name
Start with integer
Increament by integer
Minvalue integer/Nominvalue
Maxvalue integer /Nomaxvalue
Cycle/Nocycle
Example:
CREATE SEQUENCE seq_nm
START WITH 5
MAXVALUE 200
CYCLE;
CREATE SEQUENCE seq_nm
START WITH 5
MAXVALUE 200
CYCLE;
CYCLE/NOCYCLE:
When cycle clause is used at the time of creating a sequence then that sequence reinitialized to the starting value
If nocycle is used then sequence is not reinitialized.
When cycle clause is used at the time of creating a sequence then that sequence reinitialized to the starting value
If nocycle is used then sequence is not reinitialized.
Syntax:
CREATE SEQUENCE sequence_name
Start with integer
Increament by integer
Minvalue integer/Nominvalue
Maxvalue integer /Nomaxvalue
Cycle/Nocycle
CREATE SEQUENCE sequence_name
Start with integer
Increament by integer
Minvalue integer/Nominvalue
Maxvalue integer /Nomaxvalue
Cycle/Nocycle
Example:
CREATE SEQUENCE seq_nm
START WITH 5
MAXVALUE 200
CYCLE;
CREATE SEQUENCE seq_nm
START WITH 5
MAXVALUE 200
CYCLE;
CACHE integer:
The cache indicates the number sequences specified by integer.
This is created and stored in the cache memory in advanced.
By default 20 sequence value will be cached.
It accessed frequently case higher value of integer so there will less access to disk
and processing become faster.
The cache indicates the number sequences specified by integer.
This is created and stored in the cache memory in advanced.
By default 20 sequence value will be cached.
It accessed frequently case higher value of integer so there will less access to disk
and processing become faster.
Syntax:
CREATE SEQUENCE sequence_name
START WITH integer
INCREAMENT BY integer
MINVALUE integer/NOMINVALUE
MAXVALUE integer/NOMAXVALUE
CYCLE/NOCYCLE
CACHE 30;
CREATE SEQUENCE sequence_name
START WITH integer
INCREAMENT BY integer
MINVALUE integer/NOMINVALUE
MAXVALUE integer/NOMAXVALUE
CYCLE/NOCYCLE
CACHE 30;
CURRVAL & NEXTVAL
CURRVAL AND NEXTVAL
To access sequence value the contents of two pseudo columns are used.
Currval:
Return current value of a sequence
Nextval:
Return the increment value i.e. next value
This pseudo column are used with sequence like
Sequence_name.Currval
Sequence_name.Nextval
Return the increment value i.e. next value
This pseudo column are used with sequence like
Sequence_name.Currval
Sequence_name.Nextval
Example:
Select seq1.nextval from dual;
Select seq1.nextval from dual;
Example:
Select seq2.currval from dual;
Select seq2.currval from dual;
In Insert command:
Insert into student values (seq1.nextval, ‘Priti’,’pune’, 45);
In Update Command:
Update student set stud_id=sed1.currval
Where stud_id=20;
Insert into student values (seq1.nextval, ‘Priti’,’pune’, 45);
In Update Command:
Update student set stud_id=sed1.currval
Where stud_id=20;
In Update Command:
Update student set stud_id=sed1.currval
Where stud_id=20;
Update student set stud_id=sed1.currval
Where stud_id=20;
In select Command:
Syntax:
Select seq1.currval from dual;
The pseudo columns cannot be used if select command has the DISTINCT, GROUP BY, ORDER BY or SUBQUERY clauses.
Syntax:
Select seq1.currval from dual;
The pseudo columns cannot be used if select command has the DISTINCT, GROUP BY, ORDER BY or SUBQUERY clauses.
CHANGING THE SEQUENCE
ALTER SEQUENCE Command is used to the sequence parameter can be changed.
Syntax:
ALTER SEQUENCE seq1 parameters;
ALTER SEQUENCE seq1 parameters;
The changed minimum value for the table con not is greater than the current value.
The increment value of a sequence can changed without any problem.
The increment value of a sequence can changed without any problem.
Deleting A Sequence:
The DROP command is used to remove the sequence
Syntax:
DROP SEQUENCE sequence_name;
The DROP command is used to remove the sequence
Syntax:
DROP SEQUENCE sequence_name;
Example:
DROP SEQUENCE sqe1;
DROP SEQUENCE sqe1;
User_Sequences:
The table user_sequences check users list of sequences.
stores the list of user created sequences with the value of their parameters
Syntax:
Select * from USER_SEQUENCES;
The table user_sequences check users list of sequences.
stores the list of user created sequences with the value of their parameters
Syntax:
Select * from USER_SEQUENCES;
INDEXES
INDEXES
The index is an ordered list of contents of a column or group of columns in a table. Index is created simple index for single column and composite index for multiple column.
Local Indexes
Global Indexes
Local Indexes
Global Indexes
Local Indexes having two types:
Local prefixed index:
local prefixed indexes whereby the partition keys are on the leading edge of the index definition. it can support uniqueness.
Local nonprefixed index:
These indexes do not have the partition key on the leading
edge of their column list.
It can support uniqueness.
local prefixed indexes whereby the partition keys are on the leading edge of the index definition. it can support uniqueness.
Local nonprefixed index:
These indexes do not have the partition key on the leading
edge of their column list.
It can support uniqueness.
Global Idexes:
global indexes are partitioned using a scheme that is different from that used in the underlying table.
global indexes are partitioned using a scheme that is different from that used in the underlying table.
Simple Index:
Create [Unique] INDEX index_filename
On tablename (columnname)
Example:
Create index ind_fn on vender (vender_name);
Create [Unique] INDEX index_filename
On tablename (columnname)
Example:
Create index ind_fn on vender (vender_name);
Composite Index:
Create INDEX indexfilename
On tablename (column1, column2);
Create INDEX indexfilename
On tablename (column1, column2);
Example:
Create Index ind_fname
On vender (vender_no, vender_name);
Create Index ind_fname
On vender (vender_no, vender_name);
CREATING UNIQUE INDEX
Unique clause is used to the create unique indexes.
Example:
Create UNIQUE index ind_fname on student (stud_id);
Example:
Create UNIQUE index ind_fname on student (stud_id);
Creating Function based Index:
Function is used creating an index
Example:
Create INDEX ind_fn on student (UPPER (stud_name));
Function is used creating an index
Example:
Create INDEX ind_fn on student (UPPER (stud_name));
USER_INDEXES:
User_Indexes table is used display the information about the user defined index
Example:
Select * from USER_INDEXES;
User_Indexes table is used display the information about the user defined index
Example:
Select * from USER_INDEXES;
Display the field of USER_INDEXES use command:
Describe USER_INDEXES;
Describe USER_INDEXES;
Deleting An Index:
Drop index command is used delete an index
Drop index command is used delete an index
Syntax:
DROP INDEX index_name;
DROP INDEX index_name;
Example:
DROP INDEX ind_nm;
DROP INDEX ind_nm;
HOSTING
HOSTING
SQL hosting is alternate Oracle hosting, which is also platform independent.
Hosting having three types
• Free Hosting
• Shared Hosting
• Dedicated Hosting
Hosting having three types
• Free Hosting
• Shared Hosting
• Dedicated Hosting
Free Hosting:
Free hosting suited for small sites with low traffic, and low cost like personal sites. Technical support is often limited, and technical options are few.
Free hosting suited for small sites with low traffic, and low cost like personal sites. Technical support is often limited, and technical options are few.
Shared SQL Hosting:
Shared SQL Hosting is a web hosting that offers some kind of database software, which is shared between multiple users. A shared SQL hosting will offer SQL servers at a cheaper price, but they will be used together with other users.
Shared SQL Hosting is a web hosting that offers some kind of database software, which is shared between multiple users. A shared SQL hosting will offer SQL servers at a cheaper price, but they will be used together with other users.
Dedicated SQL Hosting:
Dedicated SQL Hosting is a web hosting that offers access to particular database server software, and the access to it is not shared between multiple users. Dedicated hosting provides a secure environment with a large capacity for data storage. This combined with performance speed ensures the perfect product to meet the needs of any business.
Dedicated SQL Hosting is a web hosting that offers access to particular database server software, and the access to it is not shared between multiple users. Dedicated hosting provides a secure environment with a large capacity for data storage. This combined with performance speed ensures the perfect product to meet the needs of any business.
MYSQL SERVER HOSTING
MySQL is that it runs on both Unix and Windows. MySQL is fast and reliable database server. High quality web hosts usually limit the number of MySQL databases. The user can have in a single hosting account, because database driven websites are resource intensive. MySQL for low-cost database-access.
ASP.Net Web Hosting:
The ASP.NET software, developers and users can access the ASP.NET web hosting control panel. ASP web hosting tools include administration of site security, administration of applications, administration of provider information and administration of internal tools specific to the website being developed. ASP.NET web hosting allows users to create web administration system. The security system is intended to simplify the authentication and authorization of users with optimum efficiency.
The ASP.NET software, developers and users can access the ASP.NET web hosting control panel. ASP web hosting tools include administration of site security, administration of applications, administration of provider information and administration of internal tools specific to the website being developed. ASP.NET web hosting allows users to create web administration system. The security system is intended to simplify the authentication and authorization of users with optimum efficiency.
PHP Web Hosting:
PHP is can be used by individuals and businesses to create dynamic web pages to make website visitor experiences and flexible. PHP can be used on most web servers. PHP can be used nearly all operating systems. PHP can be used in conjunction with database management systems. PHP makes cheap web hosting possible. PHP is extremely versatile web development script.
PHP is can be used by individuals and businesses to create dynamic web pages to make website visitor experiences and flexible. PHP can be used on most web servers. PHP can be used nearly all operating systems. PHP can be used in conjunction with database management systems. PHP makes cheap web hosting possible. PHP is extremely versatile web development script.
Asp Web Hosting:
Asp web hosting allows the creation of dynamic web services and web applications.
ASP.net web hosting updated version of Microsoft’s Active Server Pages technology.
ASP.NET web hosting works through the use of web forms as building blocks to develop dynamic websites and pages
Asp web hosting allows the creation of dynamic web services and web applications.
ASP.net web hosting updated version of Microsoft’s Active Server Pages technology.
ASP.NET web hosting works through the use of web forms as building blocks to develop dynamic websites and pages
Windows Web Hosting:
The Windows hosting control panel system allows access to web pages or file transfer protocol, for uploading and maintaining web pages. Windows forms of operating software also allow for database server hosting. Windows only handling ASP hosting and ASP.NET hosting. Windows operating platforms, however, will handle ColdFusion server-side scripts.
The Windows hosting control panel system allows access to web pages or file transfer protocol, for uploading and maintaining web pages. Windows forms of operating software also allow for database server hosting. Windows only handling ASP hosting and ASP.NET hosting. Windows operating platforms, however, will handle ColdFusion server-side scripts.
Linux Web Hosting:
Linux form of operating software also allow for database server hosting. The Linux alternative for ASP hosting is PHP hosting for website maintenance and scripting. Linux operating system will handle ColdFusion server-side scripts.
Linux form of operating software also allow for database server hosting. The Linux alternative for ASP hosting is PHP hosting for website maintenance and scripting. Linux operating system will handle ColdFusion server-side scripts.
Domain Name:
DNS stands for Domain Name System. The domain name is reserve a website name and can end in any number of internet protocol variations. The domain name is added, information about the web site, including the IP address, is stored on a DNS server. The most popular ending variations for websites are .com, .net, .org and .edu Domains can be registered from domain name registration companies.
Email Hosting:
An e-mail hosting service is an Internet hosting service that runs e-mail servers. Email account is appears like priti@ mygmail.com , mycompany@yahoo.com. POP stands for Post Office Protocol. POP is a standard client/server protocol for sending and receiving e-mail. Email hosting to provide a company must necessarily agree to maintain the security of its clients’ email accounts.
An e-mail hosting service is an Internet hosting service that runs e-mail servers. Email account is appears like priti@ mygmail.com , mycompany@yahoo.com. POP stands for Post Office Protocol. POP is a standard client/server protocol for sending and receiving e-mail. Email hosting to provide a company must necessarily agree to maintain the security of its clients’ email accounts.
No comments:
Post a Comment