SQL Tutorial 5: Data definition Language
SQL (Structured Query Language) is for Database Developers, Database Administrators, together with Database testers.
Three primary subsets of SQL:
i) Data definition Language
ii) Data Manipulation Language
iii) Data Control Language
------------------------------
Database Engine is required to exercise / utilization SQL, nosotros tin utilization whatsoever Database engine similar Oracle, MS SQL Server or MySQL etc...
----------------------------------------
Overview
i) Database Management System
ii) Database Engine
iii) Database Server
iv) Database
v) Table together with Record
i) Database Management System
> Database Management System is designed to allow the definition, creation, querying, update together with direction of Databases
> It is a Computer Software Application that interacts amongst the user, other applications, together with the database itself to capture together with analyze the data
ii) Database Engine
Software that stores together with retrieves information inwards a database. It may hold upwardly self-contained plan or the component of DBMS that performs the storage together with retrieval operations.
iii) Database Server
Database Management Systems render Database server functionality, database server provides database services to other reckoner programs or computers.
iv) Database
> Influenza A virus subtype H5N1 Database is a Systematic collection of data
> Databases back upwardly storage together with manipulations of data
> Databases brand information direction easy
v) Table together with Record
> Influenza A virus subtype H5N1 Table is a predefined format of rows together with columns that define an entity
> Each column contains a unlike type of attribute together with each row corresponds a unmarried record.
---------------------------------------------------------
Data definition Language
Data definition Language Commands are used to Create, Modify, together with Drop the Structure of Database Objects similar Table, View, together with Index etc...
Data definition Language Commands together with Operations
----------------------------------------
Important DDL Commands
1) Create
2) Alter
3) Drop
4) Truncate
5) Rename
----------------------------------------
Important DDL Operations
1) Create a Database
2) Use Database
3) Rename a Database
4) Drop Database
5) Create a Table
6) Rename Table
7) Add a Column to exiting Table
8) Add multiple columns to existing Table
9) Modify an existing column
10) Rename a Column
11) Drop a Column
12) Truncate a Table
13) Drop a Table
Download together with Install MS SQL Server Express Edition (It is Free Edition) together with exercise SQL Commands together with Operations.
----------------------------------------
1) Create a Database
Syntax:
Create Database databaseName;
Example:
Create Database gcreddyDB;
----------------------------------------
2) Use Database
Syntax
Use databaseName;
Example:
Use gcreddyDB;
----------------------------------------
3) Rename a Database
Syntax
Alter Database databaseName Modify Name = newdatabseName;
Example:
Alter Database gcreddyDB Modify Name = hyderabad
Or
Alter Database gcreddyDB
Modify Name = hyderabad
----------------------------------------
4) Drop a Database
Syntax:
Drop Database databaseName;
Example:
Drop Database gcreddyDB;
----------------------------------------
5) Create a Table
Syntax:
Create Table tableName
(
column1_name dataType(size),
column2_name dataType(size),
.
.
.
);
Example:
Create Table Students
(
STID int,
STName char(50),
);
------------------------------------
View Table info
Select * from Students
View Table Schema
Select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'Students';
----------------------------------------
6) Rename Table
Syntax:
EXEC sp_rename 'old_tablename', 'new_tablename';
Example:
EXEC sp_rename 'Students', 'newStudents';
----------------------------------------
7) Add a Column to existing Table
Syntax:
Alter Table table_name add together column_name dataType(size);
Example:
Alter Table newStudents add together City char(50);
----------------------------------------
8) Add multiple columns to an existing Table
Syntax:
Alter Table table_name add together column1_name dataType(size), column2_name dataType(size);
Or
Alter Table table_name add
column1_name dataType(size),
column2_name dataType(size),
.
.;
Example:
Alter Table newStudents add together add1 char(100), add2 char(70);
Or
Alter Table newStudents add
add3 char(100),
add4 char(70),
add5 char (100),
phone int;
----------------------------------------
9) Modify an existing column
Syntax:
Alter Table table_name Alter Column column_name dataType(size);
Example:
Alter Table newStudents Alter Column add1 varchar(150);
----------------------------------------
10) Rename a Column
Syntax:
EXEC sp_rename 'table_name.old_column_name', 'new_colum_name';
Example:
ExEC sp_rename 'newStudents.phone', 'mobile'
----------------------------------------
11) Drop a Column
Syntax:
Alter Table table_name Drop Column column_name;
Example:
Alter Table newStudents Drop Column City;
----------------------------------------
12) Truncate a Table
Truncate Table ascendance is used to delete consummate information from an existing table
Syntax:
Truncate Table table_name;
Example:
Truncate Table newStudents;
----------------------------------------
13) Drop a Table
Drop Table ascendance is used to delete consummate Table (Data together with Table Structure) from the Database.
Syntax:
Drop Table table_name;
Example:
Drop Table newStudents;
-----------------------------------------------------------------
Also Read:
1) SQL Tutorial 1: Introduction to SQL
2) SQL Tutorial 2: SQL Overview
3) SQL Tutorial 3: MS SQL Server Installation
4) SQL Tutorial 4: Database Fundamentals, SQL Elements
SQL Interview Questions together with Answers
SQL (Structured Query Language) is for Database Developers, Database Administrators, together with Database testers.
Three primary subsets of SQL:
i) Data definition Language
ii) Data Manipulation Language
iii) Data Control Language
------------------------------
Database Engine is required to exercise / utilization SQL, nosotros tin utilization whatsoever Database engine similar Oracle, MS SQL Server or MySQL etc...
----------------------------------------
Overview
i) Database Management System
ii) Database Engine
iii) Database Server
iv) Database
v) Table together with Record
i) Database Management System
> Database Management System is designed to allow the definition, creation, querying, update together with direction of Databases
> It is a Computer Software Application that interacts amongst the user, other applications, together with the database itself to capture together with analyze the data
ii) Database Engine
Software that stores together with retrieves information inwards a database. It may hold upwardly self-contained plan or the component of DBMS that performs the storage together with retrieval operations.
iii) Database Server
Database Management Systems render Database server functionality, database server provides database services to other reckoner programs or computers.
iv) Database
> Influenza A virus subtype H5N1 Database is a Systematic collection of data
> Databases back upwardly storage together with manipulations of data
> Databases brand information direction easy
v) Table together with Record
> Influenza A virus subtype H5N1 Table is a predefined format of rows together with columns that define an entity
> Each column contains a unlike type of attribute together with each row corresponds a unmarried record.
---------------------------------------------------------
Data definition Language
Data definition Language Commands are used to Create, Modify, together with Drop the Structure of Database Objects similar Table, View, together with Index etc...
Data definition Language Commands together with Operations
----------------------------------------
Important DDL Commands
1) Create
2) Alter
3) Drop
4) Truncate
5) Rename
----------------------------------------
Important DDL Operations
1) Create a Database
2) Use Database
3) Rename a Database
4) Drop Database
5) Create a Table
6) Rename Table
7) Add a Column to exiting Table
8) Add multiple columns to existing Table
9) Modify an existing column
10) Rename a Column
11) Drop a Column
12) Truncate a Table
13) Drop a Table
----------------------------------------
1) Create a Database
Syntax:
Create Database databaseName;
Example:
Create Database gcreddyDB;
----------------------------------------
2) Use Database
Syntax
Use databaseName;
Example:
Use gcreddyDB;
----------------------------------------
3) Rename a Database
Syntax
Alter Database databaseName Modify Name = newdatabseName;
Example:
Alter Database gcreddyDB Modify Name = hyderabad
Or
Alter Database gcreddyDB
Modify Name = hyderabad
----------------------------------------
4) Drop a Database
Syntax:
Drop Database databaseName;
Example:
Drop Database gcreddyDB;
----------------------------------------
5) Create a Table
Syntax:
Create Table tableName
(
column1_name dataType(size),
column2_name dataType(size),
.
.
.
);
Example:
Create Table Students
(
STID int,
STName char(50),
);
------------------------------------
View Table info
Select * from Students
View Table Schema
Select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'Students';
----------------------------------------
6) Rename Table
Syntax:
EXEC sp_rename 'old_tablename', 'new_tablename';
Example:
EXEC sp_rename 'Students', 'newStudents';
----------------------------------------
7) Add a Column to existing Table
Syntax:
Alter Table table_name add together column_name dataType(size);
Example:
Alter Table newStudents add together City char(50);
----------------------------------------
8) Add multiple columns to an existing Table
Syntax:
Alter Table table_name add together column1_name dataType(size), column2_name dataType(size);
Or
Alter Table table_name add
column1_name dataType(size),
column2_name dataType(size),
.
.;
Example:
Alter Table newStudents add together add1 char(100), add2 char(70);
Or
Alter Table newStudents add
add3 char(100),
add4 char(70),
add5 char (100),
phone int;
----------------------------------------
9) Modify an existing column
Syntax:
Alter Table table_name Alter Column column_name dataType(size);
Example:
Alter Table newStudents Alter Column add1 varchar(150);
----------------------------------------
10) Rename a Column
Syntax:
EXEC sp_rename 'table_name.old_column_name', 'new_colum_name';
Example:
ExEC sp_rename 'newStudents.phone', 'mobile'
----------------------------------------
11) Drop a Column
Syntax:
Alter Table table_name Drop Column column_name;
Example:
Alter Table newStudents Drop Column City;
----------------------------------------
12) Truncate a Table
Truncate Table ascendance is used to delete consummate information from an existing table
Syntax:
Truncate Table table_name;
Example:
Truncate Table newStudents;
----------------------------------------
13) Drop a Table
Drop Table ascendance is used to delete consummate Table (Data together with Table Structure) from the Database.
Syntax:
Drop Table table_name;
Example:
Drop Table newStudents;
-----------------------------------------------------------------
Also Read:
1) SQL Tutorial 1: Introduction to SQL
2) SQL Tutorial 2: SQL Overview
3) SQL Tutorial 3: MS SQL Server Installation
4) SQL Tutorial 4: Database Fundamentals, SQL Elements
SQL Interview Questions together with Answers