Embedded Files
Overview of SQL DDL, DML and DCL Commands With Examples.
DDL is Data Definition Language statements. 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
GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT command
DML is Data Manipulation Language statements. 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
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency
DCL is Data Control Language statements. Some examples:
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 what rollback segment to use
Basic SQL DDL Commands.
To practice basic SQL DDL Commands such as CREATE, DROP, etc.
1. SQL -CREATE TABLE
Syntax: CREATE TABLE tablename (column_name data_ type constraints, …)
Example:
INPUT:
SQL> CREATE TABLE Emp ( Empid short CONSTRAINT PKey PRIMARY KEY,
EName VarChar(15), Job Char(10) CONSTRAINT Uname1 UNIQUE,
Mgr short CONSTRAINT FKey1 REFERENCES EMP (Empid),
Hiredate Date, DeptNo short CONSTRAINT FKey2 REFERENCES DEPT(DeptNo));
RESULT: Table created.
SQL>Create table prog20 (pname varchar2(20) not null), doj date not null,dob date not null,
sex varchar(1) not null, prof1 varchar(20),prof2 varchar(20),salary number(7,2) not null);
RESULT:
Table created.
SQL>desc prog20;
Name Null? Type
--------------------------------- -------- ----------------------------
PNAME NOT NULL VARCHAR2(20)
DOJ NOT NULL DATE
DOB NOT NULL DATE
SEX NOT NULL VARCHAR2(1)
PROF1 VARCHAR2(20)
PROF2 VARCHAR2(20)
SALARY NOT NULL NUMBER(7,2)
2. SQL -ALTER TABLE
INPUT:
SQL>ALTER TABLE EMP ADD CONSTRAINT Pkey1 PRIMARY KEY (EmpNo);
RESULT: Table Altered.
Similarly, ALTER TABLE EMP DROP CONSTRAINT Pkey1;
3. SQL - DROP TABLE
– Deletes table structure – Cannot be recovered – Use with caution
INPUT:
SQL> DROP TABLE EMP; Here EMP is table name
RESULT: Table Dropped.
4. TRUNCATE TRUNCATE TABLE <TABLE NAME>;
Basic SQL DML Commands.
To practice basic SQL DML Commands such as INSERT, DELETE, etc.
1. SQL -INSERT INTO
Syntax: INSERT INTO tablename VALUES (value list)
Single-row insert
INSERT INTO S VALUES(‘S3’,’SUP3’,’BLORE’,10)
Inserting one row, many columns at a time
INSERT INTO S (SNO, SNAME) VALUES (‘S1’, ‘Smith’); S1’ Smith’
Inserting many rows, all/some columns at a time.
INSERT INTO NEW_SUPPLIER (SNO, SNAME)
SELECT SNO, SNAME FROM S
WHERE CITY IN (‘BLORE’,’MADRAS’)
Other Examples:
INPUT:
SQL>Insert into prog values (‘kkk’,’05-may-56’);
RESULT: 1 row created.
INPUT:
SQL>Insert into prog20 values(‘Hema’,’25-sept-01’28-jan-85’,’f’,’c’,’c++’,’25000’);
RESULT: 1 row created.
INPUT:
SQL>Insert into prog values(‘&pname’,’&doj’);
SQL> Insert into prog values('&pname','&doj');
Enter value for pname: ravi
Enter value for doj: 15-june-81
RESULT:
old 1: Insert into prog values('&pname','&doj')
new 1: Insert into prog values('ravi','15-june-81')
1 row created.
2. SQL -UPDATE
Syntax: UPDATE tablename SET column_name =value [ WHERE condition]
Examples:
UPDATE SET CITY = ‘KANPUR’ WHERE SNO=‘S1’
UPDATE EMP SET SAL = 1.10 * SAL
SQL> update emp set sal=20000 where empno=7369;
1 row updated.
3. SQL -DELETE FROM
Syntax: DELETE FROM tablename WHERE condition
Examples:
DELETE FROM SP WHERE PNO= ‘P1’
DELETE FROM SP
INPUT:
SQL>Delete from emp where empno=7369;
RESULT: 1 row deleted.
Basic SQL DCL Commands.
To practice basic SQL DCL Commands such as COMMIT, ROLLBACK etc.
1. COMMIT
Save changes (transactional).
Syntax:
COMMIT [WORK] [COMMENT 'comment_text']
COMMIT [WORK] [FORCE 'force_text' [,int] ]
FORCE - will manually commit an in-doubt distributed transaction
force_text - transaction identifier (see the DBA_2PC_PENDING view)
int - sets a specific SCN.
If a network or machine failure prevents a distributed transaction from committing
properly, Oracle will store any commit comment in the data dictionary along with the
transaction ID.
INPUT:
SQL>commit;
RESULT: Commit complete.
2. ROLLBACK
Undo work done (transactional).
Syntax:
ROLLBACK [WORK] [TO [SAVEPOINT]'savepoint_text_identifier'];
ROLLBACK [WORK] [FORCE 'force_text'];
FORCE - will manually rollback an in-doubt distributed transaction
INPUT:
SQL>rollback;
RESULT:Rollback complete.
3. SAVEPOINT
Save changes to a point (transactional).
Syntax:
SAVEPOINT text_identifier
Example:
UPDATE employees
SET salary = 95000
WHERE last_name = 'Smith';
SAVEPOINT justsmith;
UPDATE employees
SET salary = 1000000;
SAVEPOINT everyone;
SELECT SUM(salary) FROM employees;
ROLLBACK TO SAVEPOINT justsmith;
COMMIT;
Page updated
Report abuse