DDL AND DML

SQL - DATA DEFINITION LANGUAGE AND DATA
MANIPULATION LANGUAGE STATEMENTS

Syntax for various QUERIES:

1. CREATE TABLE Statement:

To create a new table with the set of columns specified with respective types.

CREATE TABLE [schema.] table (column data type [DEFAULT expr];

Table – Name of new table to be created.
Schema – same as the owner’s name.
DEFAULT expr – specifies a default value if a value is omitted in the INSERT statement.
Column – Name of the column.
Datatype – The column’s datatype and length.

2. INSERT Statement:

Add new rows to a table by using the INSERT statement.

INSERT INTO table [(column [, column…])]
VALUES (value [, value…]);

Table – Name of the table.
Column x – The ‘x’- th column name of the table.
Value x – The value for the ‘x’- th column.

3. UPDATE Statement:

Modify existing rows with the UPDATE statement.

UPDATE table
SET column = value [, column = value]
[WHERE condition];

Table – Name of the table.
Column – Name of the column in the table to populate.
Value – The corresponding value or sub-query for the column.
Condition – identifies the rows to be updated and is composed of column names, expressions, constants, sub-queries and comparison operators.

4. DELETE Statement:

Remove existing rows using the delete statement.

DELETE [FROM] table
[WHERE condition];

Table – Name of the table.
Condition – identifies the rows to be updated and is composed of column names, expressions, constants, sub-queries and comparison operators.


5. Querying the Data Dictionary:

The data dictionary tables can be queried to view various database objects owned by the user. Frequently used dictionary tables are:

USER_TABLES, USER_OBJECTS, USER_CATALOG, USER_CONSTRAINTS.

6. ALTER TABLE Statement:

ALTER TABLE table
ADD (column data type [DEFAULT expr] [, column data type]…);

Table – Name of the table.
Column – Name of the new column.
Data type – The data type and length of the new column.
DEFAULT expr - specifies the default value by using the ALTER TABLE statement with the MODIFY clause.

7. RENAME Statement:

RENAME old_name TO new_name;

Old_name – Old name of the table, view, sequence, or synonym.
New_name – New name of the table, view, sequence, or synonym.

8. TRUNCATE Statement:

TRUNCATE TABLE table;

Table – Name of the table.

9. COMMENT Statement:

COMMENT ON TABLE table is ‘name’;

Comments stored in the data dictionary can be viewed in one of the following data dictionary views in the COMMENTS column:

ALL_COL_COMMENTS, USER_COL_COMMENTS, ALL_TA_COMMENTS, USER_TAB_COMMENTS.

Table – Name of the table.
Column – Name of the new column.
Text – The text of the comment.

10. DEFINING CONSTRAINTS:

CREATE TABLE [schema.]Table (column data type [DEFAULT expr] [column_constraint] … [table_constraint]);

Table – Name of the table.
Column – Name of the new column.
Data type – The data type and length of the new column.
Schema – same as the owner’s name.
DEFAULT expr – specifies a default value if a value is omitted in the INSERT statement.
Column_constraint – An integrity constraint as part of the column definition.
Table_constraint - An integrity constraint as part of the table definition.



Data Manipulation Language - Statements:

Q: 1 – Inserting new rows into the department ‘DEPT‘ table.

SQL> insert into dept(deptno,dname,loc) values(50,'DEVELOPMENT','DETROIT');

1 row created.

SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 DEVELOPMENT DETROIT

Q: 2 – Inserting rows with NULL values.

Implicit method: Omit the column from the column list.

SQL> insert into dept (deptno,dname) values(60,'MIS');

1 row created.

SQL>select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 DEVELOPMENT DETROIT
60 MIS

6 rows selected.

Explicit method: Specify the NULL keyword.

SQL> insert into dept values (70, 'FINANCE' ,NULL);

SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------------- --------------------
10 SALES ARIZONA
20 PRODUCTION CALIFORNIA
30 MANUFACTURE TEXAS
40 SUPPLY CHENNAI
50 DEVELOPMENT DETROIT
70 FINANCE
30 MANUFACTURE GEORGIA
80 EDUCATION ATLANTA

8 rows selected.





Q: 3 – Inserting special values.

Adding new employee record to the Employee ‘EMP’ table using SYSDATE and USER.
SYSDATE and USER function records the current date and time.

SQL> insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(7196,USER,'SALESMAN',7782,SYSDATE,2000,NULL,10);

1 row created.

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- --------------- ---------- ------------------------------------------------------------
7198 SCOTT SALESMAN 7782 21-APR-07 2000 20
2296 AROMANO CLERK 7782 03-FEB-97 1300 30
7196 SCOTT SALESMAN 7782 22-APR-07 2000 10

Q: 4 – Inserting Specific Date Values.

Adding a new employee record to the Employee ‘EMP’ table.

SQL> insert into emp values(2296,'AROMAMO','SALESMAN',7782,TO_DATE('FEB 03
97','MON DD YY'),1300,NULL,10);

1 row created.

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- --------------- ---------- -----------------------------------------------
7196 Smith WORKER 7799 02-FEB-93 1000 10
7198 SCOTT SALESMAN 7782 21-APR-07 2000 20

2 rows selected.

Q: 5 – Substitution variables (&) and (&&).

Create an interactive script by using SQL *Plus substituting parameters.

SQL> insert into dept(deptno,dname,loc)
values('&department_id','&department_name','&location');

Enter value for department_id: 80
Enter value for department_name: EDUCATION
Enter value for location: ATLANTA
old 1: INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES ('&DEPARTMENT_ID','&DEPARTMENT_NAME','&LOCATION')
new 1: INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES ('80','EDUCATION','ATLANTA')

1 row created.






SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------------- ------------
10 SALES ARIZONA
20 PRODUCTION CALIFORNIA
30 MANUFACTURE TEXAS
40 SUPPLY CHENNAI
50 DEVELOPMENT DETROIT

5 rows selected.

SQL> select empno,ename,job,&&column_name from emp order by &column_name;

Enter value for column_name: DEPTNO
old 1: SELECT EMPNO, ENAME, JOB, &&COLUMN_NAME FROM EMP ORDER BY &COLUMN_NAME
new 1: SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP ORDER BY DEPTNO

EMPNO ENAME JOB DEPTNO
---------- ---------- -------- --------------------
2296 AROMANO SALESMAN 10
7196 SCOTT SALESMAN 10
7198 Ada mech 20

3 rows selected.

Q: 6 – Copying from one table to another.

Using the UPDATE statement.

SQL> insert into managers(id,name,salary,hiredate) select empno,ename,sal,hiredate from
emp where job='MANAGER';

3 rows created.

SQL> SELECT * FROM MANAGERS;

ID NAME SALARY HIREDATE
---------- ----------- ---------- ---------
7799 James 8100 11-JAN-99
7783 Steve 8100 16-NOV-91
7787 Tommy 8100 12-FEB-80

3 rows selected.

Q: 7 – Updating rows in a table.

SQL> update emp2 set deptno=20 ;

4 rows updated.

SQL> select * from emp2;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- --------------- ---------- --------
7196 Smith salesman 7799 02-FEB-93 1000 20
7799 James manager 11-JAN-99 8100 100 20
7184 Brooks worker 7787 19-AUG-97 200 20
7196 SCOTT SALESMAN 7782 21-APR-07 2000 20

4 rows selected.


SQL> update emp2 set deptno=55 where deptno=20;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------------- --------------- ---------- -------------- ------- --------- -----------
7196 Smith salesman 7799 02-FEB-93 1000 55
7799 James manager 11-JAN-99 8100 100 55
7184 Brooks worker 7787 19-AUG-97 3200 55
7196 SCOTT SALESMAN 7782 21-APR-07 2000 55

4 rows selected.

Q: 8 – Deleting a set of rows from a table.

Specific row or rows are deleted when you specify the WHERE clause.

SQL> delete from dept2 where dname='DEVELOPMENT';

1 row deleted.

SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------------- --------------------- ---------------
10 SALES ARIZONA
20 PRODUCTION CALIFORNIA
30 MANUFACTURE TEXAS
40 SUPPLY CHENNAI

4 rows selected.
`
SQL> DELETE FROM DEPT2;

4 rows deleted.

DATABASE TRANSACTIONS:

A transaction begins when the first executable SQL statement is encountered and terminates when one of the following occurs:

A COMMIT or ROLLBACK statement is issued.
A DDL statement, such as CREATE is issued.
A DCL statement is issued.
The user exits SQL Plus.
The machine fails or the system crashes.

After the end of a transaction, the next executable SQL statement will automatically start the next transaction. A DDL statement or a DCL statement is automatically committed and therefore implicitly ends a transaction.

Q: 9 - Committing data:

Make changes to the EMP table as follows:

SQL> update emp set deptno=20 where empno=7782;

1 row updated.




Commit the changes made to the EMP table so that all changes are made permanent.

SQL> commit;

Commit complete.

Q: 10 - Rolling back the State of data:

1. Delete all records from the EMP2 table.

SQL> delete from emp2;

4 rows deleted.

SQL> select * from emp2;

No rows selected.

2. After using ‘rollback’…

SQL> rollback;

Rollback complete.

SQL> select * from emp2;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------- ------ -------------- --------------- ------- --------- ------------
7196 Smith salesman 7799 02-FEB-93 1000 20
7799 James manager 11-JAN-99 8100 100 20
7184 Brooks worker 7787 19-AUG-97 3200 20
7196 SCOTT SALESMAN 7782 21-APR-07 2000 20


Q: 11 - Rolling back to a marker:

1. List all records of the DEPT table.

SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------------- ----------------
10 SALES ARIZONA
20 PRODUCTION CALIFORNIA
30 MANUFACTURE TEXAS
40 SUPPLY CHENNAI

4 rows selected.

2. Make a save-point so that the state of data in the database can be restored to this point, later.

SQL> savepoint update_done;

Savepoint created.






3. Make changes to the DEPT table as follows:

SQL> insert into dept(deptno,dname) values(60,'MIS');

1 row created.

4. List all records of the DEPT table.

SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------------- -----------
10 SALES ARIZONA
20 PRODUCTION CALIFORNIA
30 MANUFACTURE TEXAS
40 SUPPLY CHENNAI
60 MIS

5 rows selected.

5. Use ROLLBACK to restore the database to state UPDATE_DONE..

SQL> rollback to update_done;

Rollback complete.


Data Definition Language - Statements:

Q: 12 – Creating tables.

SQL> create table dep(deptno number(2),dname varchar2(14),loc varchar2(13));

Table created.

SQL>desc dep;

Name Null? Type
------------------------------------------------------
DEPTNO NUMBER (2)
DNAME VARCHAR2 (14)
LOC VARCHAR2 (13)

Q: 13 – Querying the Data dictionary.

SQL> select distinct object_type from user_objects;

OBJECT_TYPE
------------
INDEX
TABLE











SQL> select * from user_catalog;

TABLE_NAME TABLE_TYPE
--------------------- -----------
DEP TABLE
DEPT2 TABLE
DEPT3 TABLE
DEPT5 TABLE
EMP TABLE
EMP2 TABLE
EMP3 TABLE
EMP4 TABLE
MANAGERS TABLE

9 rows selected.

Q: 14 – Create table using sub-query.

SQL> create table deptno30 as select empno,ename,sal*12 annsal,hiredate from emp where
deptno=30;

Table created.

SQL> desc dept30;

Name Null? Type
-----------------------------------------
EMPNO NUMBER (4)
ENAME VARCHAR2 (20)
ANNSAL NUMBER
HIREDATE DATE

ALTERING A TABLE:

Q: 15 – Adding a column.

SQL> alter table deptno30 add(job varchar2(9));

Table altered.

SQL> desc dept30;

Name Null? Type
-----------------------------------------
EMPNO NUMBER (4)
ENAME VARCHAR2 (20)
ANNSAL NUMBER
HIREDATE DATE
JOB VARCHAR2 (9)

Q: 16 – Modifying a column.

SQL> alter table deptno30 modify(ename varchar2(15));

Table altered.







SQL> desc dept30;

Name Null? Type
-----------------------------------------
EMPNO NUMBER (4)
ENAME VARCHAR2 (15)
ANNSAL NUMBER
HIREDATE DATE
JOB VARCHAR2 (9)

Q: 17 – Dropping a column.

SQL> alter table deptno30 drop column ename;

Table altered.

SQL> desc dept30;

Name Null? Type
----------------------------- -------- --
EMPNO NUMBER (4)
ANNSAL NUMBER
HIREDATE DATE
JOB VARCHAR2 (9)

SQL> alter table deptno30 set unused column ename;

SQL> alter table deptno30 drop unused columns;

SQL> desc dept30;

Name Null? Type
----------------------------- -------- --
EMPNO NUMBER (4)
ANNSAL NUMBER
HIREDATE DATE
JOB VARCHAR2 (9)

Q: 18 – Dropping a Table.

SQL> drop table deptno30;

Table dropped.

Q: 19 – Renaming an object.

SQL> rename dept2 to department2;

Table renamed.

Q: 20 – Truncating a Table.

SQL> truncate table department2;

Table truncated.

SQL> Select * from department;

No rows selected.




Q: 21 – Adding comments to a table.

SQL> comment on table emp is 'EMPLOYEE INFORMATION';

Comment created.

CONSTRAINTS:

Q: 22 – Using the NOT NULL constraint.

SQL> create table emp3(empno number(4),ename varchar2(10) not null,job varchar2(9),mgr
number(4) ,hiredate date, sal number(7,2), comm number(7,2), deptno number(7,2)not null);
Table created.

SQL> desc emp3;

Name Null? Type
--------------- --------------- -----------------
EMPNO NUMBER (4)
ENAME NOT NULL VARCHAR2 (10)
JOB VARCHAR2 (9)
MGR NUMBER (4)
HIREDATE DATE
SAL NUMBER (7, 2)
COMM NUMBER (7, 2)
DEPTNO NOT NULL NUMBER (7, 2)

Q: 23 – The UNIQUE key constraint.

SQL> create table dept3(deptno number(2),dname varchar2(14),loc varchar2(13),constraint
dept_dname_uk unique(dname));

Table created.

SQL> desc dept30;

Name Null? Type
------------------------------------------------
EMPNO NUMBER(4)
ANNSAL NUMBER
HIREDATE DATE


Q: 24 – Using the PRIMARY KEY constraint.

SQL> create table dept6(deptno number(2),dname varchar2(14),loc varchar2(13),constraint
dept_dname_ke unique(dname),constraint dept_deptno_pke primary key(deptno));

Table created.

SQL> desc dept5;

Name Null? Type
------------------------------------------------
DEPT NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)





Q: 25 – Using the FOREIGN KEY constraint.

SQL> create table emp4(empno number(4),ename varchar2(10) not null,job varchar2(9),mgr
number(4),hiredate date,sal number(7,2),comm number(7,2),deptno number(7,2) not null,
constraint emp_deptno_fk foreign key(deptno) references dept5(deptno));

Table created.

SQL> desc emp4;

Name Null? Type
----------------- ----------------- --------------
EMPNO NUMBER(4)
ENAME NOT NULL VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SA NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NOT NULL NUMBER(7,2)


Q: 26 – Adding a constraint.

SQL> alter table emp add constraint emp_empno_pk primary key(empno);

Table altered.

SQL> select constraint_name,constraint_type,search_condition from user_constraints where
table_name='emp';

CONSTRAINT_NAME C SEARCH_CONDITION
------------------------------ - -----------------------------------------------------------------------
EMP_EMPNO_PK P

SQL> alter table emp3 add constraint emp_mgr_fk foreign key(mgr) references emp(empno);

Table altered.

SQL> desc emp6;

Name Null? Type
--------------------------------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(20)
JOB VARCHAR2(15)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(5)
COMM NUMBER(4)
DEPTNO NUMBER(2)

Q: 27 – Dropping a constraint.

SQL> alter table emp3 drop constraint emp_mgr_fk;

Table altered.






SQL> desc emp6;

Name Null? Type
---------------- ---------------------- ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(20)
JOB VARCHAR2(15)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(5)
COMM NUMBER(4)
DEPTNO NUMBER(2)

SQL> alter table dept6 drop primary key cascade;

Table altered.

SQL> desc dept6;

Name Null? Type
-------------------------------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

Q: 28 – Disabling a constraint.

SQL> alter table emp6 disable constraint emp_empno_pke cascade;

Constraint disabled.

Q: 29 – Enabling a constraint.

SQL> alter table emp enable constraint emp_empno_pk;

Constraint enabled.

Q: 30 – Viewing constraints.

SQL>select constraint_name,constraint_type,search_condition from user_constraints where
table_name= 'emp4';

CONSTRAINT_NAME C SEARCH_CONDITION
------------------------------ ------------------------------------
SYS_C003010 C "ENAME" IS NOT NULL
SYS_C003011 C "DEPTNO" IS NOT NULL
EMP_DEPTNO_FK R


SQL>select constraint_name,column_name from user_cons_columns where table_name =
'emp4';

CONSTRAINT_NAME COLUMN_NAME
------------------------------- ------------------------
EMP_DEPTNO_FK DEPTNO
SYS_C003010 ENAME
SYS_C003011 DEPTNO