DDL Commandss
There are four types of DDL commands:
- CREATE
- ALTER
- DROP
- TRUNCATE
Aptitude
- Introduction
- File System vs DBMS
- Tuple
- Record
- Architecture
- Need of DBMS
- DBMS Abstraciton and Indipendence
- Data Models
- ER Models
- RDBMS
- Relational Data Model
- SQL Introduction
- SQL Types
- DDL Commands
- DQL Commands
- DML Commands
- DCL Commands
- TCL Commands
- Default Constraints
- Check Constraint
- Not Null Constraint
- Unique Constraint
- Key Constraint
- Key In DBMS
- Primary Key
- Foreign Key
- Super Key
- Candidate Key
- Alternate Key
- Composite Key
ER Models
RDBMS
SQL
SQL Constraints
Keys In DBMS
SQL DDL Commands
CREATE
A database is nothing but the structured organization of data. For organizing the data in a database we need to create database tables as per the required structure
CREATE is the DDL(data definition language) commands used for the creation of the tables in a database
CREATE TABLE table_name
(
column1 datatype(size),
column2 datatype(size),
column3 datatype(size),
.....
columnN datatype(size),
PRIMARY KEY( one or more columns )
);
Example :
create table emp(
empno number(4,0),
ename varchar2(10),
job varchar2(9),
mgr number(4,0),
hiredate date,
sal number(7,2),
deptno number(2,0)
PRIMARY KEY (ID)
);
- Table name:emp
- Column names: In the above table that we have created have 7 columns namely empno, ename, job, mgr,hiredate, sal,deptno
- Data types: What type of data should be entered for each column value. for example, we have used number data type for the column empno , which means you must enter numerical values only while inserting data for emp column
- Size: Specifies the length of the value that is inserted, for example, we have used size 10 for ename as varchar2(10), which means the maximum number of characters that can be entered for the ename column is 10.
Creating a new table from an existing Table :
We can create a new table with exactly the same structure of any of the existing tables
Here an empty table with exactly the same structure of the existing table is created but data and constraints of the table are not copied
create table
sample as (select * from emp);
ALTER
ALTER table :
- ADD
- DROP
- MODIFY
- RENAME
It is used to add columns, delete columns, drop constraints, renaming the columns, changing the data type and data type size of the column existing in the table.
ALTER TABLE table_name ADD(column_name datatype);
Example :
ALTER TABLE student ADD(address VARCHAR(100));
ALTER
ALTER table :
- ADD
- DROP
- MODIFY
- RENAME
It is used to add columns, delete columns, drop constraints, renaming the columns, changing the data type and data type size of the column existing in the table.
ALTER TABLE table_name ADD(column_name datatype);
Example :
ALTER TABLE student ADD(address VARCHAR(100));
→
←