Perl - DBI Programming:
Handles:
Driver Handles:
The actual instantiation of driver handles happens ''under the hood'' of DBI, typically when DBI->connect() is called.
Statement Handles:
Database Handles:
Database handles are the first step towards actually doing work with the database, in that they encapsulate a single connection to a particular database.
Connecting to DB:
$dbh = DBI->connect( $data_source, $username, $password, \%attr );
The final argument, \%attr, is optional and may be omitted
Ex:
use DBI; # Load the DBI module
### Perform the connection using the Oracle driver
my $dbh = DBI->connect( "dbi:Oracle:archaeo", "username", "password" )
or die "Can't connect to Oracle database: $DBI::errstr\n";
exit;
Note:
We can have any number of Database Handles for same Database.
Disconnecting:
Since DBI handles are references to Perl objects,
Perl's own garbage collector will move in and sweep up any object trash you leave lying around.
It does that by calling the object's DESTROY method when there are no longer any references to the object held by your script, or when Perl is exiting.
Piece of Code :
### Prepare a SQL statement for execution
my $sth = $dbh->prepare( "SELECT * FROM megaliths" )
or die "Can't prepare SQL statement: ", $dbh->errstr(), "\n";
### Execute the statement in the database
$sth->execute
or die "Can't execute SQL statement: ", $sth->errstr(), "\n";
### Retrieve the returned rows of data
while ( my @row = $sth->fetchrow_array() ) {
print "Row: @row\n";
}
warn "Problem in fetchrow_array(): ", $sth->errstr(), "\n"
if $sth->err();
### Disconnect from the database
$dbh->disconnect
Fetching Columns:
while ( @row = $sth->fetchrow_array ) {
### Print out a wee message
print "Megalith site $row[0] is a $row[1]\n";
}
Quick way to Ftech & Print:
$sth = $dbh->prepare( "
SELECT name, mapref, location
FROM megaliths
" );
$sth->execute( );
$rows = $sth->dump_results( );
which would display the following results:
'Balbirnie', 'NO 285 029', 'Balbirnie Park, Markinch, Fife'
'Castlerigg', 'NY 291 236', 'Near Keswick, Cumbria, England'
'Sunhoney', 'NJ 716 058', 'Near Insch, Aberdeenshire'
'Avebury', 'SU 103 700', 'Avebury, Wiltshire, England'
Dump the formatted results to the file:
### Dump the formatted results to the file
$rows = $sth->dump_results( 80, '\n', ':', \*FILE );
do vs Prepare:
do method used with database handle..
$dhb->do(insert into ....); internally uses prepare,execute ...
### Setup the statement for repeated execution
$sth = $dbh->prepare( "INSERT INTO megaliths ( name ) VALUES ( ? )" );
### Iterate through the various bits of data...
foreach $name ( qw( Stonehenge Avebury Castlerigg Sunhoney ) ) {
### ... and insert them into the table
$sth->execute( $name );
Fetching Only 1 Row:
selectrow_array( ) and selectrow_arrayref( ) .
>> Like fetchrow_array,fetchrow_arrayref..
* Used with database Handle..
$dbh->selectrow_array( "SELECT name, mapref
FROM megaliths" ); // No need of prepare,execute..
Monday, June 22, 2009
Thursday, June 4, 2009
SQL -Quick Tutorial
Points to Remember in SQL:
**** SQL TUTORIAL ****
1. delete from employee;
Note: if you leave off the where clause, all records will be deleted!
2. Dropping the table removes the table definition as well as all of its rows.
drop table "tablename"
Example:
drop table myemployees_ts0211;
3. ALL and DISTINCT are keywords used to select either ALL (default) or the "distinct" or
unique records in your query.
4. Aggregate Functions:
Aggregate functions are used to compute against a "returned column of numeric data" from your SELECT statement.
They basically summarize the results of a particular column of selected data.
* MIN,MAX,SUM,COUNT,AVG.
Used with GROUP BY.
5. Group By Clause:
The GROUP BY clause will gather all of the rows together that contain data in the specified column(s) and
will allow aggregate functions to be performed on the one or more columns.
This can best be explained by an example:
GROUP BY clause syntax:
SELECT column1, SUM(column2) FROM "list-of-tables" GROUP BY "column-list";
select max(salary),last_name,dept from employee group by dept,last_name;
6.HAVING clause [ grabbing particular data from result of group by ]
The HAVING clause allows you to specify conditions on the rows for each group -
in other words,
which rows should be selected will be based on the conditions you specify..
Ex: SELECT dept, avg(salary)
FROM employee
GROUP BY dept
HAVING avg(salary) > 20000;
7.IN and BETWEEN Conditional Operators:[ NoT IN/NOT Between ]
* SELECT employeeid, lastname, salary
FROM employee_info
WHERE lastname IN ('Hernandez', 'Jones', 'Roberts', 'Ruiz');
* SELECT employeeid, age, lastname, salary
FROM employee_info
WHERE age BETWEEN 30 AND 40;
8.JOIN: (NORMAL JOIN==INNER JOIN)
* SELECT customer_info.firstname, customer_info.lastname, purchases.item
FROM customer_info, purchases
WHERE customer_info.customer_number = purchases.customer_number;
* SELECT customer_info.firstname, customer_info.lastname, purchases.item
FROM customer_info INNER JOIN purchases
ON customer_info.customer_number = purchases.customer_number;
9. OUTER JOIN:[ LEFT/RIGHT ]
The second type of SQL JOIN is called SQL OUTER JOIN and it has 2 sub-types called LEFT OUTER JOIN and RIGHT OUTER JOIN.
The LEFT OUTER JOIN or simply LEFT JOIN (you can omit the OUTER keyword in most databases), selects all the rows from the first table listed after the FROM clause,
no matter if they have matches in the second table.
FULL OUTER JOIN: Return rows in LEFT and right even there is no Matches.
Use FULL JOIN in Query.
10. INSERT QUERY:
INSERT INTO Customers (FirstName, LastName, Email, DOB, Phone)
VALUES ('Peter', 'Hunt', 'peter.hunt@tgmail.net', '1/1/1974', '626 888-8888')
11.TABLE ALIAS:
SELECT Emp.Employee FROM EmployeeHours AS Emp
12. NOTE on this Query :
When using SQL on text data, "alfred" is greater than "a" (like in a dictionary).
SELECT CompanyName, ContactName
FROM customers
WHERE CompanyName > 'g'
AND ContactName > 'g'
13. SQL Wildcards:
SQL wildcards must be used with the SQL LIKE operator.
With SQL, the following wildcards can be used:
Wildcard Description
% A substitute for zero or more characters
_ A substitute for exactly one character
[charlist] Any single character in charlist
[^charlist] or [!charlist] Any single character not in char
14. SQL UNION: [ 1 Result set Followed by Second Result Set]
The SQL UNION operator combines two or more SELECT statements.
The UNION operator is used to combine the result-set of two or more SELECT statements.
Notice that each SELECT statement within the UNION must have the same number of columns.
The columns must also have similar data types.
Also, the columns in each SELECT statement must be in the same order.
15.UNION & UNION ALL:
The UNION operator selects only distinct values by default.
To allow duplicate values, use UNION ALL.
16.SQL SELECT INTO Example
Make a Backup Copy - Now we want to make an exact copy of the data in our "Persons" table.
SELECT *
INTO Persons_Backup
FROM Persons
17.IN CLAUSE Example:
We can also use the IN clause to copy the table into another database:
SELECT *
INTO Persons_Backup IN 'Backup.mdb'
FROM Persons
18.SELECT INTO - Joined Tables
Selecting data from more than one table is also possible.
The following example creates a "Persons_Order_Backup" table contains data from the two tables "Persons" and "Orders":
SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
19.CREATE DATABASE Syntax
CREATE DATABASE database_name;
20.SQL Constraints
Constraints are used to limit the type of data that can go into a table.
Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).
* NOT NULL
* UNIQUE
* PRIMARY KEY
* FOREIGN KEY
* CHECK
* DEFAULT
21.Unique Constraint:
CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
**** For More than a Column:
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
* allows dropping constraint easily than updating each column..
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID
22. PRIMARY KEY Constraint:
SQL PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only one primary key.
Example:
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
* For More than One column: CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
We can alter a table by
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
23. FOREIGN KEY:
The FOREIGN KEY constraint is used to prevent actions that would destroy link between tables.
The FOREIGN KEY constraint also prevents that invalid data is inserted into the foreign key column,
because it has to be one of the values contained in the table it points to.
Example:
CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)
24.SQL CHECK Constraint
The CHECK constraint is used to limit the value range that can be placed in a column.
Age varchar2(10) check(age>0)
On 2/More Columns,
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
25.SQL DEFAULT Constraint on CREATE TABLE
The following SQL creates a DEFAULT constraint on the "City" column when the "Persons" table is created:
My SQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes
26. CREATE INDEX Statement
The CREATE INDEX statement is used to create indexes in tables.
Indexes allow the database application to find data fast; without reading the whole table.
Indexes
An index can be created in a table to find data more quickly and efficiently.
The users cannot see the indexes, they are just used to speed up searches/queries.
Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.
SQL CREATE INDEX Syntax
Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name
ON table_name (column_name)
SQL CREATE UNIQUE INDEX Syntax
Creates a unique index on a table. Duplicate values are not allowed:
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
27.DROPPING INDEX:
DROP INDEX index_name ON table_name
28.The DROP DATABASE Statement
The DROP DATABASE statement is used to delete a database.
DROP DATABASE database_name
The TRUNCATE TABLE Statement
What if we only want to delete the data inside the table, and not the table itself?
Then, use the TRUNCATE TABLE statement:
TRUNCATE TABLE table_name
29. AUTO INCREMENT:
In Oracle the code is a little bit more tricky.
You will have to create an auto-increment field with the sequence object (this object generates a number sequence).
Use the following CREATE SEQUENCE syntax:
CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10
The code above creates a sequence object called seq_person, that starts with 1 and will increment by 1. It will also cache up to 10 values for performance. The cache option specifies how many sequence values will be stored in memory for faster access.
To insert a new record into the "Persons" table, we will have to use the nextval function (this function retrieves the next value from seq_person sequence):
INSERT INTO Persons (P_Id,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen')
30.VIEW :
In SQL,a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.
SQL CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
31.SQL IS NULL
How do we select only the records with NULL values in the "Address" column?
We will have to use the IS NULL operator:
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL
[ cant be compared with ' ' and >,<,etc ]
32. ORACLE DATATYPES:
INTEGER
This ANSI datatype will be accepted by Oracle - it is actually a synonym for NUMBER(38)
The FLOAT datatype
This ANSI datatype will be accepted by Oracle - Very similar to NUMBER it stores zero, positive, and negative floating-point numbers
The NUMBER datatype
Stores zero, positive, and negative numbers, fixed or floating-point numbers
Fixed-point NUMBER
NUMBER(p,s)
precision p = length of the number in digits
scale s = places after the decimal point, or (for negative scale values) significant places before the decimal point.
Integer NUMBER
NUMBER(p)
This is a fixed-point number with precision p and scale 0. Equivalent to NUMBER(p,0)
Floating-Point NUMBER
NUMBER
floating-point number with decimal precision 38
Confusingly the Units of measure for PRECISION vary according to the datatype.
For NUMBER data types: precision p = Number of Digits
For FLOAT data types: precision p = Binary Precision (multiply by 0.30103 to convert)
{So FLOAT = FLOAT (126) = 126 x 0.30103 = approx 37.9 digits of precision.}
Example
The value 7,456,123.89 will display as follows
NUMBER(9) 7456124
NUMBER(9,1) 7456123.9
NUMBER(*,1) 7456123.9
NUMBER(9,2) 7456123.89
NUMBER(6) [not accepted exceeds precision]
NUMBER(7,-2) 7456100
NUMBER 7456123.89
FLOAT 7456123.89
FLOAT(12) 7456000.0
Oracle stores all numeric data in variable length format.
33.SQL Scalar Functions:
SQL Scalar functions
SQL scalar functions return a single value, based on the input value.
Useful scalar functions:
* UCASE() - Converts a field to upper case
* LCASE() - Converts a field to lower case
* MID() - Extract characters from a text field
* LEN() - Returns the length of a text field
* ROUND() - Rounds a numeric field to the number of decimals specified
* NOW() - Returns the current system date and time
* FORMAT() - Formats how a field is to be displayed
34. MID:
The MID() Function
The MID() function is used to extract characters from a text field.
SQL MID() Syntax
SELECT MID(column_name,start[,length]) FROM table_name
35.The ROUND() Function
The ROUND() function is used to round a numeric field to the number of decimals specified.
SQL ROUND() Syntax
SELECT ROUND(column_name,decimals) FROM table_name
36.NOW:
SELECT ProductName, UnitPrice, Now() as PerDate FROM Products
The result-set will look like this:
ProductName UnitPrice PerDate
Jarlsberg 10.45 10/7/2008 11:25:02 AM
Mascarpone 32.56 10/7/2008 11:25:02 AM
Gorgonzola 15.67 10/7/2008 11:25:02 AM
E.g SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate
FROM Products
**** SQL TUTORIAL ****
1. delete from employee;
Note: if you leave off the where clause, all records will be deleted!
2. Dropping the table removes the table definition as well as all of its rows.
drop table "tablename"
Example:
drop table myemployees_ts0211;
3. ALL and DISTINCT are keywords used to select either ALL (default) or the "distinct" or
unique records in your query.
4. Aggregate Functions:
Aggregate functions are used to compute against a "returned column of numeric data" from your SELECT statement.
They basically summarize the results of a particular column of selected data.
* MIN,MAX,SUM,COUNT,AVG.
Used with GROUP BY.
5. Group By Clause:
The GROUP BY clause will gather all of the rows together that contain data in the specified column(s) and
will allow aggregate functions to be performed on the one or more columns.
This can best be explained by an example:
GROUP BY clause syntax:
SELECT column1, SUM(column2) FROM "list-of-tables" GROUP BY "column-list";
select max(salary),last_name,dept from employee group by dept,last_name;
6.HAVING clause [ grabbing particular data from result of group by ]
The HAVING clause allows you to specify conditions on the rows for each group -
in other words,
which rows should be selected will be based on the conditions you specify..
Ex: SELECT dept, avg(salary)
FROM employee
GROUP BY dept
HAVING avg(salary) > 20000;
7.IN and BETWEEN Conditional Operators:[ NoT IN/NOT Between ]
* SELECT employeeid, lastname, salary
FROM employee_info
WHERE lastname IN ('Hernandez', 'Jones', 'Roberts', 'Ruiz');
* SELECT employeeid, age, lastname, salary
FROM employee_info
WHERE age BETWEEN 30 AND 40;
8.JOIN: (NORMAL JOIN==INNER JOIN)
* SELECT customer_info.firstname, customer_info.lastname, purchases.item
FROM customer_info, purchases
WHERE customer_info.customer_number = purchases.customer_number;
* SELECT customer_info.firstname, customer_info.lastname, purchases.item
FROM customer_info INNER JOIN purchases
ON customer_info.customer_number = purchases.customer_number;
9. OUTER JOIN:[ LEFT/RIGHT ]
The second type of SQL JOIN is called SQL OUTER JOIN and it has 2 sub-types called LEFT OUTER JOIN and RIGHT OUTER JOIN.
The LEFT OUTER JOIN or simply LEFT JOIN (you can omit the OUTER keyword in most databases), selects all the rows from the first table listed after the FROM clause,
no matter if they have matches in the second table.
FULL OUTER JOIN: Return rows in LEFT and right even there is no Matches.
Use FULL JOIN in Query.
10. INSERT QUERY:
INSERT INTO Customers (FirstName, LastName, Email, DOB, Phone)
VALUES ('Peter', 'Hunt', 'peter.hunt@tgmail.net', '1/1/1974', '626 888-8888')
11.TABLE ALIAS:
SELECT Emp.Employee FROM EmployeeHours AS Emp
12. NOTE on this Query :
When using SQL on text data, "alfred" is greater than "a" (like in a dictionary).
SELECT CompanyName, ContactName
FROM customers
WHERE CompanyName > 'g'
AND ContactName > 'g'
13. SQL Wildcards:
SQL wildcards must be used with the SQL LIKE operator.
With SQL, the following wildcards can be used:
Wildcard Description
% A substitute for zero or more characters
_ A substitute for exactly one character
[charlist] Any single character in charlist
[^charlist] or [!charlist] Any single character not in char
14. SQL UNION: [ 1 Result set Followed by Second Result Set]
The SQL UNION operator combines two or more SELECT statements.
The UNION operator is used to combine the result-set of two or more SELECT statements.
Notice that each SELECT statement within the UNION must have the same number of columns.
The columns must also have similar data types.
Also, the columns in each SELECT statement must be in the same order.
15.UNION & UNION ALL:
The UNION operator selects only distinct values by default.
To allow duplicate values, use UNION ALL.
16.SQL SELECT INTO Example
Make a Backup Copy - Now we want to make an exact copy of the data in our "Persons" table.
SELECT *
INTO Persons_Backup
FROM Persons
17.IN CLAUSE Example:
We can also use the IN clause to copy the table into another database:
SELECT *
INTO Persons_Backup IN 'Backup.mdb'
FROM Persons
18.SELECT INTO - Joined Tables
Selecting data from more than one table is also possible.
The following example creates a "Persons_Order_Backup" table contains data from the two tables "Persons" and "Orders":
SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
19.CREATE DATABASE Syntax
CREATE DATABASE database_name;
20.SQL Constraints
Constraints are used to limit the type of data that can go into a table.
Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).
* NOT NULL
* UNIQUE
* PRIMARY KEY
* FOREIGN KEY
* CHECK
* DEFAULT
21.Unique Constraint:
CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
**** For More than a Column:
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
* allows dropping constraint easily than updating each column..
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID
22. PRIMARY KEY Constraint:
SQL PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only one primary key.
Example:
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
* For More than One column: CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
We can alter a table by
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
23. FOREIGN KEY:
The FOREIGN KEY constraint is used to prevent actions that would destroy link between tables.
The FOREIGN KEY constraint also prevents that invalid data is inserted into the foreign key column,
because it has to be one of the values contained in the table it points to.
Example:
CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)
24.SQL CHECK Constraint
The CHECK constraint is used to limit the value range that can be placed in a column.
Age varchar2(10) check(age>0)
On 2/More Columns,
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
25.SQL DEFAULT Constraint on CREATE TABLE
The following SQL creates a DEFAULT constraint on the "City" column when the "Persons" table is created:
My SQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes
26. CREATE INDEX Statement
The CREATE INDEX statement is used to create indexes in tables.
Indexes allow the database application to find data fast; without reading the whole table.
Indexes
An index can be created in a table to find data more quickly and efficiently.
The users cannot see the indexes, they are just used to speed up searches/queries.
Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.
SQL CREATE INDEX Syntax
Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name
ON table_name (column_name)
SQL CREATE UNIQUE INDEX Syntax
Creates a unique index on a table. Duplicate values are not allowed:
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
27.DROPPING INDEX:
DROP INDEX index_name ON table_name
28.The DROP DATABASE Statement
The DROP DATABASE statement is used to delete a database.
DROP DATABASE database_name
The TRUNCATE TABLE Statement
What if we only want to delete the data inside the table, and not the table itself?
Then, use the TRUNCATE TABLE statement:
TRUNCATE TABLE table_name
29. AUTO INCREMENT:
In Oracle the code is a little bit more tricky.
You will have to create an auto-increment field with the sequence object (this object generates a number sequence).
Use the following CREATE SEQUENCE syntax:
CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10
The code above creates a sequence object called seq_person, that starts with 1 and will increment by 1. It will also cache up to 10 values for performance. The cache option specifies how many sequence values will be stored in memory for faster access.
To insert a new record into the "Persons" table, we will have to use the nextval function (this function retrieves the next value from seq_person sequence):
INSERT INTO Persons (P_Id,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen')
30.VIEW :
In SQL,a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.
SQL CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
31.SQL IS NULL
How do we select only the records with NULL values in the "Address" column?
We will have to use the IS NULL operator:
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL
[ cant be compared with ' ' and >,<,etc ]
32. ORACLE DATATYPES:
INTEGER
This ANSI datatype will be accepted by Oracle - it is actually a synonym for NUMBER(38)
The FLOAT datatype
This ANSI datatype will be accepted by Oracle - Very similar to NUMBER it stores zero, positive, and negative floating-point numbers
The NUMBER datatype
Stores zero, positive, and negative numbers, fixed or floating-point numbers
Fixed-point NUMBER
NUMBER(p,s)
precision p = length of the number in digits
scale s = places after the decimal point, or (for negative scale values) significant places before the decimal point.
Integer NUMBER
NUMBER(p)
This is a fixed-point number with precision p and scale 0. Equivalent to NUMBER(p,0)
Floating-Point NUMBER
NUMBER
floating-point number with decimal precision 38
Confusingly the Units of measure for PRECISION vary according to the datatype.
For NUMBER data types: precision p = Number of Digits
For FLOAT data types: precision p = Binary Precision (multiply by 0.30103 to convert)
{So FLOAT = FLOAT (126) = 126 x 0.30103 = approx 37.9 digits of precision.}
Example
The value 7,456,123.89 will display as follows
NUMBER(9) 7456124
NUMBER(9,1) 7456123.9
NUMBER(*,1) 7456123.9
NUMBER(9,2) 7456123.89
NUMBER(6) [not accepted exceeds precision]
NUMBER(7,-2) 7456100
NUMBER 7456123.89
FLOAT 7456123.89
FLOAT(12) 7456000.0
Oracle stores all numeric data in variable length format.
33.SQL Scalar Functions:
SQL Scalar functions
SQL scalar functions return a single value, based on the input value.
Useful scalar functions:
* UCASE() - Converts a field to upper case
* LCASE() - Converts a field to lower case
* MID() - Extract characters from a text field
* LEN() - Returns the length of a text field
* ROUND() - Rounds a numeric field to the number of decimals specified
* NOW() - Returns the current system date and time
* FORMAT() - Formats how a field is to be displayed
34. MID:
The MID() Function
The MID() function is used to extract characters from a text field.
SQL MID() Syntax
SELECT MID(column_name,start[,length]) FROM table_name
35.The ROUND() Function
The ROUND() function is used to round a numeric field to the number of decimals specified.
SQL ROUND() Syntax
SELECT ROUND(column_name,decimals) FROM table_name
36.NOW:
SELECT ProductName, UnitPrice, Now() as PerDate FROM Products
The result-set will look like this:
ProductName UnitPrice PerDate
Jarlsberg 10.45 10/7/2008 11:25:02 AM
Mascarpone 32.56 10/7/2008 11:25:02 AM
Gorgonzola 15.67 10/7/2008 11:25:02 AM
E.g SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate
FROM Products
Subscribe to:
Posts (Atom)