Tag Archives: DBMS

DDL , DML, DCL and TCL

Posted on by

What is the difference between DDL, DML, DCL and TCL? Well look below…

DDL

Data Definition Language (DDL) statements are 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 (DML) statements are 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 data
LOCK TABLE – control concurrency

DCL

Data Control Language (DCL) statements. Some examples:

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

TCL

Transaction Control (TCL) statements are 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

Triggers(dbms)

Posted on by

sample1:

sql>ed trg1.sql

…………………………………………………..

create trigger trig1 after insert on sailors

begin

insert into boats values(’21’,’dustin’,’red’);  //see hint 2 ,hint 3

dbms_output.put_line(‘Value inserted into boat table’); //see hint 1

end;

…………………………………………

sql>@trg1.sql

………………………………………….

hint 1::be careful in put_line use only single quotes to print text.

hint 2 :: see that every insert or update command ends with a semi colon( ; )

hint 3 :: Be careful with inserting value, some values need single quotes.

…………………………………………………..

Sample 2

|-)

Connection string for odbc in C sharp

Posted on by

First create a odbc driver as specified below.

goto control panel->system and security->Administrative tools->data sources(odbc)

step1)click on add

step2)select sql server

step3)specify ur sql server name and create the driver

IN C sharp write the following commands to get connect:

Odbcconnection con=new Odbcconnection(“Driver=<drivername>;Server=<servername>;UID=<username>;PWD=<passwd>;“);

if your sql server is authorized then no need of specifing user name and password

eg:    Odbcconnection con=new Odbcconnection(“Driver={SQL server};Server=shivaji-pc;“);

if not authorized then.

eg:    Odbcconnection con=new Odbcconnection(“Driver={SQL server};Server=shivaji-pc;UID=shivaji;PWD=1234;“);

sample program to execute a query:

public viod exequery( )

{

Odbcconnection con=new Odbcconnection(“Driver={SQL server};Server=shivaji-pc;“);

Odbccommand comd=new Odbccommand(“insert into student values(’21’,’shivaji’);“,con);

con.Open( );

comd.ExecuteNonQuery( );

con.Close( );

}

note:

you can also create odbc drivers for oracle and other db server in similar manner.

you can also connect directly to db server without using odbc.

<<there are many other ways to get connect>>