Oracle SQL - DDL

DATA DEFINITION LANGUAGE

1. CREATE
2. ALTER
3. DROP
4. MODIFY
5. TRUNCATE

CREATE
A create command is create any database any database object such as table, view, synonym etc.
Syntax for Create Command
Create Table (column1_name datatype(fieldsize), column2_name datatype(fieldsize),.........);

Create a table EMPL with the following fields
SQL> CREATE TABLE EMPL(EMPNO NUMBER, ENAME VARCHAR2(20), JOB VARCHAR2(10), HIRE_DATE DATE);
Table created.
Creating a table from an existing table
SQL> CREATE TABLE EMP2 AS SELECT * FROM EMP;
Table created.
SQL> DESC EMPL
Name Null? Type
------------------------------- -------- ----
EMPNO NUMBER
ENAME VARCHAR2(20)
JOB VARCHAR2(10)
HIRE_DATE DATE

Make a copy of an existing table.
SQL> CREATE TABLE EMP12 AS SELECT * FROM EMP;
Table created.

Make a copy of an existing table with only a few columns and not all
SQL> CREATE TABLE EMP13 AS SELECT EMPNO, ENAME, JOB, SAL FROM EMP;
Table created.
ALTER
An Alter table is used to Add a new column to an exisiting table or to increase the size of an existing column
Syntax
Alter Table add(column_name datatype(fieldsize));
How to Add a column
SQL> ALTER TABLE EMPL ADD(SAL NUMBER(5));
Table altered.

SQL> DESC EMPL
Name Null? Type
------------------------------- -------- ----
EMPNO NUMBER
ENAME VARCHAR2(20)
JOB VARCHAR2(10)
HIRE_DATE DATE
SAL NUMBER(5)
To Increase the field size of Job Column using Alter with Modify
SQL> ALTER TABLE EMPL MODIFY(JOB VARCHAR2(15));
Table altered.

DROP
A drop command is used to drop a database object such as table, view, synonym etc.
How to Drop a table
Syntax
SQL> Drop table
Drop a table
SQL> DROP TABLE EMP13;
Table dropped.

To Drop a column
Oracle 8.0 does not permit to drop a column with a single command.
We need to follow a three step procedure to drop a column.
1. Create a new table temp on the existing table EMP12 from which you
want to drop the column, but do not select the columns which you want to drop.
SQL> CREATE TABLE TEMP AS SELECT EMPNO, ENAME, JOB, SAL FROM EMP12;
Table created.
2. Drop the old table EMP12
SQL> DROP TABLE EMP12;
Table dropped.

3. Rename the table TEMP to EMP12.
SQL> RENAME TEMP TO EMP12;
Table renamed.
This way you will be able to rename a table.
TRUNCATE
A truncate command is used to remove all the values from the table. Also it will free-up the space occupied by the table data.

SQL> TRUNCATE TABLE EMP12;
Table truncated.
SQL> SELECT * FROM EMP12;
no rows selected

DML (Data Manipulation Language)
The DML statements are used to manipulate the records of a table. The DML commands are of three types.

1. INSERT
2. UPDATE
3. DELETE
INSERT
This is used to Insert new records into an existing table.
Syntax
INSERT INTO VALUES ('VALUE1', 'VALUE2', VALUE3);
Please note that the values have to be specified in single quotes excepting for numerical values.
To insert new row into the EMP table
INSERT INTO EMP VALUES(1001, 'JOHN', 'MANAGER', 7369, '12-JAN-87',4500, 500, 20);

SELECTIVE INSERTS
To insert only the empno,ename and salary values into the emp table.
SQL>INSERT INTO EMP3(EMPNO, ENAME, SAL) SELECT EMPNO, ENAME, SAL FROM EMP
SELECT STATEMENT TO INSERT A RECORD
( To copy all the records from one table to another)
SQL> INSERT INTO EMP2 SELECT EMPNO, ENAME, SAL FROM EMP;
NOTE: You should have exactly those many columns in both the tables,else specify the columns as follows

SQL>INSERT INTO EMP3(EMPNO, ENAME, SAL) SELECT EMPNO, ENAME, SAL FROM EMP
SIMPLE INSERT
INSERT WITH USER PROMPTS
SQL>INSERT INTO EMP3(EMPNO, ENAME, SAL) VALUES(&EMPNO, '&ENAME', &SAL)
FROM EMP


UPDATE
SQL> UPDATE EMP SET SAL=3500 WHERE ENAME='ALLEN';
Updating multiple columns of a record using an update command
SQL> UPDATE EMP1 SET ENAME='JOHN', DEPTNO=40 WHERE EMPNO=7369;
1 row updated.
DELETE
SQL> DELETE FROM EMP WHERE ENAME='ALLEN';

DATA CONTROL LANGUAGE

GRANT
REVOKE
THE DCL defines the privileges which a user can grant to other users to accessing his tables.

Steps for working with DCL
SQL> Connect as SYSTEM/MANAGER AND

CREATE A USER BY NAME TAJ IDENTIFIED BY TAJ
SQL> Create user Taj identified by Taj;

GRANT DBA TO TAJ;
SQL> Grant DBA to taj;
Grant succeeded.

Then connect to Oracle with the new user
SQL> conn taj/taj@oracle_lab1
Connected.

CREATE A TABLE IN THE TAJ'S SCHEMA BY NAME DEPT1 AND ENTER THREE RECORDS.

SQL> CREATE TABLE DEPT_TAJ(DEPTNO NUMBER, DNAME VARCHAR2(10), LOC VARCHAR2(10));
Table created.


Insert three values into the new table
SQL> INSERT INTO DEPT_TAJ VALUES(&DEPARTMENT_NUMBER, '&DEPARTMENT_NAME', '&LOCATION');
Enter value for department_number: 10
Enter value for department_name: SALES
Enter value for location: UK
old 1: INSERT INTO DEPT_TAJ VALUES(&DEPARTMENT_NUMBER, '&DEPARTMENT_NAME', '&LOCATION')
new 1: INSERT INTO DEPT_TAJ VALUES(10, 'SALES', 'UK')
1 row created.


SQL> SELECT * FROM DEPT_TAJ;
DEPTNO DNAME LOC
--------- ---------- ----------
10 SALES UK
20 PURCHASE SAN DEIGO
30 MKTG US

Grant privelege of select on the newly created table to scott
TAJ>GRANT SELECT ON DEPT_TAJ TO SCOTT;


OPEN ANOTHER SQL WINDOW AND CONNECT AS SCOTT/TIGER
USE SET SQLP SCOTT> TO CHANGE THE PROMPT TO SCOTT>
SQL> SET SQLP SCOTT>
SCOTT>
SCOTT>

Also on Taj user
SQL> SET SQLP TAJ>

Note : Use the Show user command on the SQL prompt to know as which user you are presently connected as
SCOTT>SHO USER
USER is "SYSTEM"



SCOTT>SELECT * FROM TAJ.DEPT1;
You will see the whole dept1 table belonging to Taj under Scott's schema

Now give the following command, and an error will be encountered as follows
SCOTT>UPDATE TAJ.DEPT_TAJ SET LOC = 'US';
UPDATE TAJ.DEPT_TAJ SET LOC = 'US'
*
ERROR at line 1:
ORA-01031: insufficient privileges

Then apply priveleges to Scott as follows
TAJ>GRANT ALL ON DEPT1 TO SCOTT;


Insert a new record on the DEPT1_TAJ table from the scotts schema
1* INSERT INTO TAJ.DEPT_TAJ VALUES(40,'INVENTORY','UK')
SCOTT>/
1 row created.


Then switch to Taj schema whether the new row got added to the dept_taj table, and you will not find the new record. Then go to Scott schema and apply a commit as follows
SCOTT>COMMIT
Now you will find the new record in the taj schema

The Data Dictionary to verify the priveliges being Made and Received
SCOTT>SELECT * FROM USER_TAB_PRIVS_RECD;
OWNER TABLE_NAME GRANT PRIVILEGE GRA
-------- ------------------------------ ----- ---------------------------------------- ---
TAJ DEPT1 TAJ ALTER NO
TAJ DEPT1 TAJ DELETE NO
TAJ DEPT1 TAJ INDEX NO
TAJ DEPT1 TAJ INSERT NO
TAJ DEPT1 TAJ SELECT NO
TAJ DEPT1 TAJ UPDATE NO
TAJ DEPT1 TAJ REFERENCES NO


TAJ>REVOKE ALL ON EMPG FROM SCOTT;



TAJ> SELECT * FROM USER_TAB_PRIVS_MADE;
GRANTE TABLE_ GRANTOR PRIVILEGE GRA
------ ------ ------------------------------ ---------------------------------------- ---
SCOTT DEPT1 TAJ ALTER NO
SCOTT DEPT1 TAJ DELETE NO
SCOTT DEPT1 TAJ INDEX NO
SCOTT DEPT1 TAJ INSERT NO
SCOTT DEPT1 TAJ SELECT NO
SCOTT DEPT1 TAJ UPDATE NO
SCOTT DEPT1 TAJ REFERENCES NO

7 rows selected.


IF YOU WANT TO GRANT THE PRIVELEGE TO OTHER USERS GIVE WITH GRANT OPTION.

TAJ> GRANT ALL ON DEPT1 TO SCOTT WITH GRANT OPTION;

SCOTT>SELECT * FROM USER_TAB_PRIVS_RECD;

OWNER TABLE_NAME GRANT PRIVILEGE GRA
-------- ------------------------------ ----- ---------------------------------------- ---
TAJ DEPT1 TAJ ALTER YES
TAJ DEPT1 TAJ DELETE YES
TAJ DEPT1 TAJ INDEX YES
TAJ DEPT1 TAJ INSERT YES
TAJ DEPT1 TAJ SELECT YES
TAJ DEPT1 TAJ UPDATE YES
TAJ DEPT1 TAJ REFERENCES YES

After recieving the 'WITH GRANT OPTION' scott can now grant this table to another user i.e system
SCOTT>GRANT SELECT ON TAJ.DEPT_TAJ TO SYSTEM




Then connect to System/manager
SCOTT>CONN SYSTEM/MANAGER@ORACLE_LAB1
Connected.

SQL>SELECT * FROM TAJ.DEPT_TAJ;

DEPTNO DNAME LOC
--------- ---------- ----------
10 SALES UK
20 PURCHASE SAN DEIGO
30 MKTG US
40 INVENTORY UK


Revoking priveleges

TAJ>REVOKE ALL ON DEPT1 FROM SCOTT;

Switch to the scott schema and give the following command
SCOTT>SELECT * FROM TAJ.DEPT_TAJ;
SELECT * FROM TAJ.DEPT_TAJ
*
ERROR at line 1:
ORA-01031: insufficient privileges



QUESTIONS ON DCL
IF YOU WANT TO GRANT THE PRIVELEGE TO OTHER USERS GIVE WITH GRANT OPTION.