1. What Are DDL Statements?
DDL (Data Definition Language) statements are statements to create and manage data objects in the database. The are 3 primary DDL statements:
◉ CREATE - Creating a new database object.
◉ ALTER - Altering the definition of an existing data object.
◉ DROP - Dropping an existing data object.
2. How To Create a New Table?
If you want to create a new table in your own schema, you can log into the server with your account, and use the CREATE TABLE statement. The following script shows you how to create a table:
>.\bin\sqlplus /nolog
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE tip (id NUMBER(5) PRIMARY KEY,
2 subject VARCHAR(80) NOT NULL,
3 description VARCHAR(256) NOT NULL,
4 create_date DATE DEFAULT (sysdate));
Table created.
This scripts creates a testing table called "tip" with 4 columns in the schema associated with the log in account "HR".
3. How To Create a New Table by Selecting Rows from Another Table?
Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the CREATE TABLE...AS SELECT statement to do this. Here is an example script:
>.\bin\sqlplus /nolog
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE emp_dept_10
2 AS SELECT * FROM employees WHERE department_id=10;
Table created.
SQL> SELECT first_name, last_name, salary
2 FROM emp_dept_10;
FIRST_NAME LAST_NAME SALARY
-------------------- ------------------------- ----------
Jennifer Whalen 4400
As you can see, this SQL scripts created a table called "emp_dept_10" using the same column definitions as the "employees" table and copied data rows of one department.
This is really a quick and easy way to create a table.
4. How To Add a New Column to an Existing Table?
If you have an existing table with existing data rows, and want to add a new column to that table, you can use the ALTER TABLE ... ADD statement to do this. Here is an example script:
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE emp_dept_110
2 AS SELECT * FROM employees WHERE department_id=110;
Table created.
SQL> ALTER TABLE emp_dept_110 ADD (vacation NUMBER);
Table altered.
SQL> SELECT first_name, last_name, vacation
2 FROM emp_dept_110;
FIRST_NAME LAST_NAME VACATION
-------------------- ------------------------- ----------
Shelley Higgins
William Gietz
This SQL script added a new column called "vacation" to the "emp_dept_110" table. NULL values were added to this column on all existing data rows.
5. How To Delete a Column in an Existing Table?
If you have an existing column in a table and you need that column any more, you can delete it with ALTER TABLE ... DROP COLUMN statement. Here is an example SQL script:
SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.
SQL> SELECT last_name FROM emp_dept_90;
LAST_NAME
-------------------------
King
Kochhar
De Haan
SQL> ALTER TABLE emp_dept_90 DROP COLUMN last_name;
Table altered.
SQL> SELECT last_name FROM emp_dept_90;
ERROR at line 1:
ORA-00904: "LAST_NAME": invalid identifier
As you can see the column "last_name" is gone.
6. How To Drop an Existing Table?
If you want to delete an existing table and its data rows, you can use the DROP TABLE statement as shown in this script:
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE emp_dept_10
2 AS SELECT * FROM employees WHERE department_id=10;
Table created.
SQL> DROP TABLE emp_dept_10;
Table dropped.
Be careful, when you use the DROP TABLE statement. All data rows are gone too.
7. How To Create a Table Index?
If you have a table with a lots of rows, and you know that one of the columns will be used often a search criteria, you can add an index for that column to in improve the search performance. To add an index, you can use the CREATE INDEX statement as shown in the following script:
CREATE TABLE tip (id NUMBER(5) PRIMARY KEY,
subject VARCHAR(80) NOT NULL,
description VARCHAR(256) NOT NULL,
create_date DATE DEFAULT (sysdate));
Table created.
CREATE INDEX tip_subject ON tip(subject);
Index created.
8. How To Rename an Index?
Let's say you have an existing index, and you don't like its name anymore for some reason, you can rename it with the ALTER INDEX ... RENAME TO statement. Here is an example script on how to rename an index:
CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
first_name VARCHAR(80) NOT NULL,
last_name VARCHAR(80) NOT NULL,
birth_date DATE NOT NULL,
social_number VARCHAR(80) UNIQUE NOT NULL);
Table created.
SELECT index_name, table_name, uniqueness
FROM USER_INDEXES WHERE table_name = 'STUDENT';
INDEX_NAME TABLE_NAME UNIQUENES
----------------------- --------------------- ---------
SYS_C004153 STUDENT UNIQUE
SYS_C004154 STUDENT UNIQUE
ALTER INDEX SYS_C004153 RENAME TO student_pk;
Statement processed.
SELECT index_name, table_name, uniqueness
FROM USER_INDEXES WHERE table_name = 'STUDENT';
INDEX_NAME TABLE_NAME UNIQUENES
----------------------- --------------------- ---------
STUDENT_PK STUDENT UNIQUE
SYS_C004154 STUDENT UNIQUE
9. How To Drop an Existing Index?
If you don't need an existing index any more, you should delete it with the DROP INDEX statement. Here is an example SQL script:
CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
first_name VARCHAR(80) NOT NULL,
last_name VARCHAR(80) NOT NULL,
birth_date DATE NOT NULL,
social_number VARCHAR(80) UNIQUE NOT NULL);
Table created.
CREATE INDEX student_birth_date ON student(birth_date);
Index created.
SELECT index_name, table_name, uniqueness
FROM USER_INDEXES WHERE table_name = 'STUDENT';
INDEX_NAME TABLE_NAME UNIQUENES
----------------------- --------------------- ---------
SYS_C004129 STUDENT UNIQUE
SYS_C004130 STUDENT UNIQUE
STUDENT_BIRTH_DATE STUDENT NONUNIQUE
DROP INDEX STUDENT_BIRTH_DATE;
Index dropped.
10. How To Create a New View?
You can create a new view based on one or more existing tables by using the CREATE VIEW statement as shown in the following script:
CREATE VIEW employee_department AS
SELECT e.employee_id, e.first_name, e.last_name,
e.email, e.manager_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;
View created.
SELECT first_name, last_name, department_name
FROM employee_department WHERE manager_id = 101;
FIRST_NAME LAST_NAME DEPARTMENT_NAME
-------------------- ------------------- ----------------
Nancy Greenberg Finance
Jennifer Whalen Administration
Susan Mavris Human Resources
Hermann Baer Public Relations
Shelley Higgins Accounting
11. How To Drop an Existing View?
If you have an existing view, and you don't want it anymore, you can delete it by using the DROP VIEW statement as shown in the following script:
DROP VIEW employee_department;
View dropped.
DDL (Data Definition Language) statements are statements to create and manage data objects in the database. The are 3 primary DDL statements:
◉ CREATE - Creating a new database object.
◉ ALTER - Altering the definition of an existing data object.
◉ DROP - Dropping an existing data object.
2. How To Create a New Table?
If you want to create a new table in your own schema, you can log into the server with your account, and use the CREATE TABLE statement. The following script shows you how to create a table:
>.\bin\sqlplus /nolog
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE tip (id NUMBER(5) PRIMARY KEY,
2 subject VARCHAR(80) NOT NULL,
3 description VARCHAR(256) NOT NULL,
4 create_date DATE DEFAULT (sysdate));
Table created.
This scripts creates a testing table called "tip" with 4 columns in the schema associated with the log in account "HR".
3. How To Create a New Table by Selecting Rows from Another Table?
Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the CREATE TABLE...AS SELECT statement to do this. Here is an example script:
>.\bin\sqlplus /nolog
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE emp_dept_10
2 AS SELECT * FROM employees WHERE department_id=10;
Table created.
SQL> SELECT first_name, last_name, salary
2 FROM emp_dept_10;
FIRST_NAME LAST_NAME SALARY
-------------------- ------------------------- ----------
Jennifer Whalen 4400
As you can see, this SQL scripts created a table called "emp_dept_10" using the same column definitions as the "employees" table and copied data rows of one department.
This is really a quick and easy way to create a table.
4. How To Add a New Column to an Existing Table?
If you have an existing table with existing data rows, and want to add a new column to that table, you can use the ALTER TABLE ... ADD statement to do this. Here is an example script:
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE emp_dept_110
2 AS SELECT * FROM employees WHERE department_id=110;
Table created.
SQL> ALTER TABLE emp_dept_110 ADD (vacation NUMBER);
Table altered.
SQL> SELECT first_name, last_name, vacation
2 FROM emp_dept_110;
FIRST_NAME LAST_NAME VACATION
-------------------- ------------------------- ----------
Shelley Higgins
William Gietz
This SQL script added a new column called "vacation" to the "emp_dept_110" table. NULL values were added to this column on all existing data rows.
5. How To Delete a Column in an Existing Table?
If you have an existing column in a table and you need that column any more, you can delete it with ALTER TABLE ... DROP COLUMN statement. Here is an example SQL script:
SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.
SQL> SELECT last_name FROM emp_dept_90;
LAST_NAME
-------------------------
King
Kochhar
De Haan
SQL> ALTER TABLE emp_dept_90 DROP COLUMN last_name;
Table altered.
SQL> SELECT last_name FROM emp_dept_90;
ERROR at line 1:
ORA-00904: "LAST_NAME": invalid identifier
As you can see the column "last_name" is gone.
6. How To Drop an Existing Table?
If you want to delete an existing table and its data rows, you can use the DROP TABLE statement as shown in this script:
SQL> connect HR/fyicenter
Connected.
SQL> CREATE TABLE emp_dept_10
2 AS SELECT * FROM employees WHERE department_id=10;
Table created.
SQL> DROP TABLE emp_dept_10;
Table dropped.
Be careful, when you use the DROP TABLE statement. All data rows are gone too.
7. How To Create a Table Index?
If you have a table with a lots of rows, and you know that one of the columns will be used often a search criteria, you can add an index for that column to in improve the search performance. To add an index, you can use the CREATE INDEX statement as shown in the following script:
CREATE TABLE tip (id NUMBER(5) PRIMARY KEY,
subject VARCHAR(80) NOT NULL,
description VARCHAR(256) NOT NULL,
create_date DATE DEFAULT (sysdate));
Table created.
CREATE INDEX tip_subject ON tip(subject);
Index created.
8. How To Rename an Index?
Let's say you have an existing index, and you don't like its name anymore for some reason, you can rename it with the ALTER INDEX ... RENAME TO statement. Here is an example script on how to rename an index:
CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
first_name VARCHAR(80) NOT NULL,
last_name VARCHAR(80) NOT NULL,
birth_date DATE NOT NULL,
social_number VARCHAR(80) UNIQUE NOT NULL);
Table created.
SELECT index_name, table_name, uniqueness
FROM USER_INDEXES WHERE table_name = 'STUDENT';
INDEX_NAME TABLE_NAME UNIQUENES
----------------------- --------------------- ---------
SYS_C004153 STUDENT UNIQUE
SYS_C004154 STUDENT UNIQUE
ALTER INDEX SYS_C004153 RENAME TO student_pk;
Statement processed.
SELECT index_name, table_name, uniqueness
FROM USER_INDEXES WHERE table_name = 'STUDENT';
INDEX_NAME TABLE_NAME UNIQUENES
----------------------- --------------------- ---------
STUDENT_PK STUDENT UNIQUE
SYS_C004154 STUDENT UNIQUE
9. How To Drop an Existing Index?
If you don't need an existing index any more, you should delete it with the DROP INDEX statement. Here is an example SQL script:
CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
first_name VARCHAR(80) NOT NULL,
last_name VARCHAR(80) NOT NULL,
birth_date DATE NOT NULL,
social_number VARCHAR(80) UNIQUE NOT NULL);
Table created.
CREATE INDEX student_birth_date ON student(birth_date);
Index created.
SELECT index_name, table_name, uniqueness
FROM USER_INDEXES WHERE table_name = 'STUDENT';
INDEX_NAME TABLE_NAME UNIQUENES
----------------------- --------------------- ---------
SYS_C004129 STUDENT UNIQUE
SYS_C004130 STUDENT UNIQUE
STUDENT_BIRTH_DATE STUDENT NONUNIQUE
DROP INDEX STUDENT_BIRTH_DATE;
Index dropped.
10. How To Create a New View?
You can create a new view based on one or more existing tables by using the CREATE VIEW statement as shown in the following script:
CREATE VIEW employee_department AS
SELECT e.employee_id, e.first_name, e.last_name,
e.email, e.manager_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;
View created.
SELECT first_name, last_name, department_name
FROM employee_department WHERE manager_id = 101;
FIRST_NAME LAST_NAME DEPARTMENT_NAME
-------------------- ------------------- ----------------
Nancy Greenberg Finance
Jennifer Whalen Administration
Susan Mavris Human Resources
Hermann Baer Public Relations
Shelley Higgins Accounting
11. How To Drop an Existing View?
If you have an existing view, and you don't want it anymore, you can delete it by using the DROP VIEW statement as shown in the following script:
DROP VIEW employee_department;
View dropped.
Posts
0 comments:
Post a Comment