DBMS SQL DAY 11

DAY - 11

Introduction to SQL

Structure Query Language (SQL) is a programming language used for storing and managing data in RDBMS. SQL was the first commercial language introduced for E.F Codd's Relational model. Today almost all RDBMS (MySql, Oracle, Infomix, Sybase, MS Access) uses SQL as the standard database language. SQL is used to perform all type of data operations in RDBMS.

---

---

---

SQL Command

SQL defines following data languages to manipulate data of RDBMS.

---

---

---

1.     DDL: Data Definition Language

All DDL commands are auto-committed. That means it saves all the changes permanently in the database.

Command	Description
1. create	to create new table or database
2 .alter	for alteration
3. truncate	delete data from table
4. drop	to drop a table
5. rename	to rename a table

1.      Create command

Creating a Database

To create a database in RDBMS, create command is uses. Following is the Syntax,

create database database-name;

---

---

---

Example:

create database Test;

The above command will create a database named Test.

---

---

---

Creating a Table

create command is also used to create a table. We can specify names and datatypes of various columns along.Following is the Syntax,

create table table-name

{

 column-name1 datatype1,

 column-name2 datatype2,

 column-name3 datatype3,

 column-name4 datatype4

};

create table command will tell the database system to create a new table with given table name and column information.

---

---

---

Example for creating Table

create table Student(id int, name varchar, age int);

The above command will create a new table Student in database system with 3 columns, namely id, name and age.

2.      alter command

alter command is used for alteration of table structures. There are various uses of alter command, such as,

• to add a column to existing table
• to rename any existing column
• to change datatype of any column or to modify its size.
• alter is also used to drop a column.

---

---

---

To Add Column to existing Table

Using alter command we can add a column to an existing table. Following is the Syntax,

alter table table-name add(column-name datatype);

Example

alter table Student add(address char);

The above command will add a new column address to the Student table

---

---

---

To Add Multiple Column to existing Table

Using alter command we can even add multiple columns to an existing table. Following is the Syntax,

alter table table-name add(column-name1 datatype1, column-name2 datatype2, column-name3 datatype3);

Example 

alter table Student add(father-name varchar(60), mother-name varchar(60), dob date);

The above command will add three new columns to the Student table

---

---

---

To Add column with Default Value

alter command can add a new column to an existing table with default values. Following is the Syntax,

alter table table-name add(column-name1 datatype1 default data);

Example

alter table Student add(dob date default '1-Jan-99');

The above command will add a new column with default value to the Student table

---

---

---

To Modify an existing Column

alter command is used to modify data type of an existing column . Following is the Syntax,

alter table table-name modify(column-name datatype);

Example

alter table Student modify(address varchar(30));

The above command will modify address column of the Student table

---

---

---

To Rename a column

Using alter command you can rename an existing column. Following is the Syntax,

alter table table-name rename old-column-name to column-name;

Example

alter table Student rename address to Location;

The above command will rename address column to Location.

---

---

---

To Drop a Column

alter command is also used to drop columns also. Following is the Syntax,

alter table table-name drop(column-name);

Example

alter table Student drop(address);

The above command will drop address column from the Student table

3.      truncate command

truncate command removes all records from a table. But this command will not destroy the table's structure. When we apply truncate command on a table its Primary key is initialized. Following is its Syntax,

truncate table table-name

 Example

truncate table Student;

The above query will delete all the records of Student table.

truncate command is different from delete command. delete command will delete all the rows from a table whereas truncate command re-initializes a table(like a newly created table).

For eg. If you have a table with 10 rows and an auto_increment primary key, if you use delete command to delete all the rows, it will delete all the rows, but will not initialize the primary key, hence if you will insert any row after using delete command, the auto_increment primary key will start from 11. But in case of truncate command, primary key is re-initialized.

---

---

---

4.      drop command

drop query completely removes a table from database. This command will also destroy the table structure. Following is its Syntax,

drop table table-name

Example

 drop table Student;

The above query will delete the Student table completely. It can also be used on Databases. For Example, 

to drop a database,

  

drop database Test;

The above query will drop a database named Test from the system.

---

---

---

5.      rename query

rename command is used to rename a table. Following is its Syntax,

rename table old-table-name to new-table-name

Example

rename table Student to Student-record;

The above query will rename Student table to Student-record.