SQL DDL commands and table constraints

Page 1

DBMS Commands:

1.Data Definition Language(DDL):

2.Data Manipulation Language(DML):

3.Data Control Language(DCL):

4.Transaction Control Language(TCL):

DDL Commands for Tables:

1.Create table :

create table student(sid int , sname varchar(20));

2. Describe tables:

desc student;

3. show Tables:

show Tables;

4. Alter table(add or Modify):

alter table student add/modify/drop sname varchar(20);

alter table student drop sname;

alter table student add age int;

5. Truncate/Delete table:

used to delete the complete data from existing table

Truncate/Delete table student;

6.Rename :

Rename table student to tab1;

Page 2

Constraints:

Constraints are the set of rules defined on table to ensure data integrity.

1.Primary Key:

2.Not Null:

3.Default:

4.Unique:

5.Check:

6.Foreign/reference Key

1.Primary key:

It is a unique key does not allow duplicate values to insert in a column.

create table Student(s_id int primary key, sname varchar(20));

Each table must have only one primary key.

Multiple tables can be clubbed under a composite primary key.

Primary key columns must be combination of not Null and Unique key.

2.Not Null:

Means that a data row must have a value for the column specified as Not Null;

create table Student(s_id int primary key, sname varchar(20) not Null);

3.Default :

If no value is supplied to the column then it gets the default value.

create table Student(s_id int primary key, address varchar(20) default 'Pune');

4.Check constraint:

Determine whether the value is valid or not.

create table Student(s_id int, sname varchar(20),age int Check(age > 20));

5.Foreign/Reference Key:

It is a link between two tables by one specific colunm of both tables.

The specified colunm in one table must be a primary key and referred by colunm of another table is known

as a foreign key;

create table Student(s_id int primary key references Student1(s1_id));

6.Add Constraint after table Creation:

alter table Student add Constraint pk primary key(s_id);

7.Drop Constraint:

drop Constraint pk;

8.Drop Table:

drop table Student;

Page 3

Sequence / Auto_increment:

CREATE TABLE Bills ( Bill_No INT(11) NOT

NULL AUTO_INCREMENT, name

varchar2(20));

Insert :

insert into student values():

Delete from Student where address = "Pune";

Delete from Student;

Update Student set name = "Ajay" where rollNo = 1;

Select * from Student;

Select * from Student where rollno = 1;

Select * from Student order by rollNo desc;