DDL Queries of SQL In Oracle
What is Relational Database:
The relational database is a database which is stored in a structured format in the forms of tables which have rows and columns. Relation is nothing but a table which is a two dimensional matrix with a collection of rows and columns. The relational database is based on the relational database model which was introduced by Edgar. Codd.
SQL Languages:
DDL( Data Definition Language):
DDL is a subset of structured query language which is used to define the structure of a database and manage the structure of the database. Data definition language statements are used to create, modify, delete database objects such as tables, views, constraints etc.
Some Commonly used Data Definition Language Statements are,
1. Create Query:
The create query is used to create new database objects such as tables, index.
Following syntax demonstrates the creation of tables.
Syntax:
Create table table_name
(field1 datatype(size),
field2 datatype(size),
Field3 datatype(size),
.
.
.
.
Field datatype(size));
In above syntax create and table these keywords are used to create the table. Table_name is replaced with the name of the table that you want to create. In pairs of parenthesis field1,field2 ….. Fieldn are nothing but the name of columns that you want to create for your tables. The field names are defined with its data types and size.
Data type is nothing but the type of actual information stored in that particular column, with the data type you can also define the size of the column that you want to store the information. After writing all the columns, close the parenthesis and terminate the create query with semicolon. Press enter key to run your create query. If your query has not any errors then the table will be created. If there is any error in your query then error with line number will be displayed with sql engine.
For example,
Create table employee
SQL> create table employee
2 (emp_id varchar(6),
3 name varchar(20),
4 designation varchar(20),
5 salary number(10),
6 doj varchar(12));
Table created.
2. Desc Query:
To see the structure of the table or display the structure of your table use desc keyword.
Syntax:
Desc table_name;
Desc is a keyword used to describe your table or to display the description of your table or to see the structure of your table.
For example,
SQL> desc employee;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_ID VARCHAR2(6)
NAME VARCHAR2(20)
DESIGNATION VARCHAR2(20)
SALARY NUMBER(10)
DOJ VARCHAR2(12)
This is the structure of the employee table. After writing the query press enter key to run query.
3. Alter Table to Add the Column:
This is a DDL query used to add a new column to your existing table.
Syntax:
Alter table table_name
Add columnname datatype(size);
In this syntax alter table keywords are used to alter the table and table_name is replaced with your table name. Add is a keyword used in alter table query to add a new column in your table, define datatype and size of new column name.
For example add employee email and address columns to employee table
SQL> alter table employee
2 add email varchar(20);
Table altered.
SQL> alter table employee
2 add address varchar(20);
Table altered.
SQL> desc employee;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_ID VARCHAR2(6)
NAME VARCHAR2(20)
DESIGNATION VARCHAR2(20)
SALARY NUMBER(10)
DOJ VARCHAR2(12)
EMAIL VARCHAR2(20)
ADDRESS VARCHAR2(20)
After adding a new column to your table display the structure of your table to check newly added columns in your table by using desc command.
In the above example we have written two different queries to add two columns to the employee table.
If you want to add multiple columns to your table then you can add all new columns in single query as follows,
For example,
SQL> alter table employee
2 add(DOB date, DOR date);
Table altered.
SQL> desc employee;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_ID VARCHAR2(6)
NAME VARCHAR2(20)
DESIGNATION VARCHAR2(20)
SALARY NUMBER(10)
DOJ VARCHAR2(12)
EMAIL VARCHAR2(20)
ADDRESS VARCHAR2(20)
DOB DATE
DOR DATE
4. Alter Table to Modify Column:
In the existing table if you want to modify the column data type or its size you can alter modify query.
Syntax:
Alter table table_name
Modify columnname datatype(size);
For example,
Alter employee table by modifying email with varchar(15), address column with varchar(30).
SQL> alter table employee
2 modify email varchar(15);
Table altered.
SQL> alter table employee
2 modify address varchar(20);
Table altered.
SQL> desc employee
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_ID VARCHAR2(6)
NAME VARCHAR2(20)
DESIGNATION VARCHAR2(20)
SALARY NUMBER(10)
DOJ VARCHAR2(12)
EMAIL VARCHAR2(15)
ADDRESS VARCHAR2(20)
DOB DATE
DOR DATE
5. Alter Table To Rename Column name with new name:
To rename the column name, rename keyword with alter table is used. You can rename the old column name with a new column name.
Syntax:
Alter table table_name
Rename column old_column To new_column;
In above syntax,
Alter tables are keywords used to alter the table. table _name is replaced with the actual table name that you want to alter. Rename keyword with column keyword is used to rename column, old_colimn is the name of column that you want to rename, To keyword is used to rename from old_name to new_name, new_column is the new column name.
For example,
Alter table employee rename address column with emp_address, name column with emp_name
SQL> alter table employee
2 rename column address to emp_address;
Table altered.
SQL> alter table employee
2 rename column name to emp_name;
Table altered.
SQL> desc employee;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_ID VARCHAR2(6)
EMP_NAME VARCHAR2(20)
DESIGNATION VARCHAR2(20)
SALARY NUMBER(10)
DOJ VARCHAR2(12)
EMAIL VARCHAR2(15)
EMP_ADDRESS VARCHAR2(20)
DOB DATE
DOR DATE
You can check renamed columns in your table by using desc query.
6. Alter Table To Rename Table Name:
You can use the alter table with the rename function to rename the existing table with a new name.
Syntax:
Alter table table_name
Rename To new_table_name;
The above syntax demonstrates the Alter table name with new name.
For example,
Alter table employee with new name employeedetails
SQL> alter table employee
2 rename to employeedetails;
Table altered.
SQL> desc employeedetails;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_ID VARCHAR2(6)
EMP_NAME VARCHAR2(20)
DESIGNATION VARCHAR2(20)
SALARY NUMBER(10)
DOJ VARCHAR2(12)
EMAIL VARCHAR2(15)
EMP_ADDRESS VARCHAR2(20)
DOB DATE
DOR DATE
Here the employee table is altered i.e. renamed with employeedetails.
7. Drop Table:
Drop table in oracle is used to drop or remove your table from the database.
Syntax:
Drop table table_name;
Drop function is used to drop the table name from the database permanently.
For example,
Delete table student from database
SQL> desc faculty;
Name Null? Type
----------------------------------------- -------- ----------------------------
FACULTY_ID VARCHAR2(4)
FACULTY_NAME VARCHAR2(20)
DESIGNATION VARCHAR2(20)
SALARY NUMBER(8)
SUBJECT VARCHAR2(20)
DOB DATE
DOJ DATE
DOR DATE
ADDRESS VARCHAR2(30)
CONTACT NUMBER(10)
D_OF_JOINING DATE
EMAIL VARCHAR2(20)
MNUMBER NUMBER(10)
RADDRESS VARCHAR2(30)
SQL> drop table faculty;
Table dropped.
SQL> desc faculty;
ERROR:
ORA-04043: object faculty does not exist
Once a table is dropped by using a drop query this table is permanently removed from the database. When you try to display the structure of the faculty table with desc query, the database engine will show you errors as above. Because your table is removed from the database.
Drop or Delete a column from Table:
Syntax:
Alter table tablename
Drop column columnname;
Here the table is altered to drop a column. Drop keyword is used to drop the column. Column keyword is important to use in the syntax that you want to drop the column. Column name is the name of the column that you want to drop.
For example;
SQL> alter table class
2 drop column class_address;
Table altered.
In the above example class_address is dropped i.e. permanently removed from table class.
You can drop or delete multiple columns as follows,
SQL> alter table class
2 drop (class_strength, class_incharge);
Table altered.
SQL> desc class;
Name Null? Type
----------------------------------------- -------- ----------------------------
CLASS_ID NOT NULL VARCHAR2(6)
CLASS_NAME VARCHAR2(10)
CLASS_DIV NOT NULL VARCHAR2(10)
0 टिप्पण्या
कृपया तुमच्या प्रियजनांना लेख शेअर करा आणि तुमचा अभिप्राय जरूर नोंदवा. 🙏 🙏