Feeds:
Posts
Comments

Archive for the ‘Oracle’ Category

Oracle

Read Full Post »

Diff Betwn – DDL, DML and DCL commands

DDL – Data Definition Language: statements used to define the database structure or schema. Some examples:

* CREATE – to create objects in the database
* ALTER – alters the structure of the database
* DROP – delete objects from the database
* TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
* COMMENT – add comments to the data dictionary
* RENAME – rename an object

DML – Data Manipulation Language: statements used for managing data within schema objects. Some examples:

* SELECT – retrieve data from the a database
* INSERT – insert data into a table
* UPDATE – updates existing data within a table
* DELETE – deletes all records from a table, the space for the records remain
* MERGE – UPSERT operation (insert or update)
* CALL – call a PL/SQL or Java subprogram
* EXPLAIN PLAN – explain access path to the data
* LOCK TABLE – controls concurrency

DCL – Data Control Language. Some examples:

* GRANT – gives user’s access privileges to database
* REVOKE – withdraw access privileges given with the GRANT command

TCL – Transaction Control: statements used to manage the changes made by DML statements.
It allows statements to be grouped together into logical transactions.

* COMMIT – save work done
* SAVEPOINT – identify a point in a transaction to which you can later roll back
* ROLLBACK – restore database to original since the last COMMIT
* SET TRANSACTION – Change transaction options like isolation level and what rollback segment to use

Note: DML are not auto-commit. i.e. you can roll-back the operations, but DDL are auto-commit

Thanks & Regards,
Rajeshkumar. E | Tel : +(0) 91 -996-246-1212 |
Email :rajeshkumar.e | Website :www.sella.it

Read Full Post »

Natural and Cartesian Joins

This is the example which I tried for Natural joins and Cartesian joins

Natural Joins: Natural join combines two tables based on their common columns i.e. columns with the same name.
If any columns are not matched then it acts as a Cartesian Joins( means simply joins all columns of both tables)

A Cartesian join is when you join every row of one table to every row of another table.

CREATE TABLE countries (
cntr_id INTEGER NOT NULL PRIMARY KEY,
country VARCHAR(15) NOT NULL);
CREATE TABLE cities (
cty_id INTEGER NOT NULL PRIMARY KEY,
cntr_id INTEGER,
city VARCHAR(15) NOT NULL);
CREATE TABLE places (
plc_id INTEGER NOT NULL PRIMARY KEY,
cty_id INTEGER,
text VARCHAR(15) NOT NULL);

INSERT INTO countries VALUES (1, ‘LATVIA’);
INSERT INTO countries VALUES (2, ‘ESTONIA’);
INSERT INTO countries VALUES (3, ‘XXXXXX’);
INSERT INTO cities VALUES (1, 1, ‘RIGA’);
INSERT INTO cities VALUES (2, 1, ‘LIEPAJA’);
INSERT INTO cities VALUES (3, 2, ‘TALLINN’);
INSERT INTO places VALUES (1, 1, ‘OLD RIGA’);
INSERT INTO places VALUES (2, 2, ‘KARA OSTA’);
COMMIT;

Sample Query

  • In case tables haven’t common column name then natural join degrades back to Cartesian join.
  • Common query

SELECT country, text
FROM countries
NATURAL JOIN places;

Natural join Query and output

SELECT cntr_id ( it display only, both the tables have same column and data, also it have 1 table extra, but in natural joins obmit that data )
FROM countries
NATURAL JOIN cities;

CNTR_ID

———–

1

1

2

Cartesian Join  ( Same query if you used country which is not matched colulmn for both the tables

SELECT country  ( here only one table have tat column,  remaining one not have so act as Cartesian Products )

FROM countries

NATURAL JOIN places;

Country

——-

LATVIA

ESTONIA

XXXXXX

LATVIA

ESTONIA

XXXXXX

Note : Simply we can say   Matrix   – 3 X  1    and  4 X  1    =  12  X  1  , so 12 rows and 1 columns

Read Full Post »

Oracle

Introduction to SQL

Tunning…

Read Full Post »