SQL Tutorial 5: Data Definition Language
SQL (Structured Query Language) is for Database Developers, Database Administrators, and Database testers.
Three main subsets of SQL:
i) Data Definition Language
ii) Data Manipulation Language
iii) Data Control Language
————————–
Database Engine is required to practice / use SQL, we can use any Database engine like Oracle, MS SQL Server or MySQL etc…
————————–
Overview
i) Database Management System
ii) Database Engine
iii) Database Server
iv) Database
v) Table and Record
i) Database Management System
> Database Management System is designed to allow the definition, creation, querying, update and administration of Databases
> It is a Computer Software Application that interacts with the user, other applications, and the database itself to capture and analyze the data
ii) Database Engine
Software that stores and retrieves data in a database. It may be self-contained program or the part of DBMS that performs the storage and retrieval operations.
iii) Database Server
Database Management Systems provide Database server functionality, database server provides database services to other computer programs or computers.
iv) Database
> A Database is a Systematic collection of data
> Databases support storage and manipulations of data
> Databases make data management easy
v) Table and Record
> A Table is a predefined format of rows and columns that define an entity
> Each column contains a different type of attribute and each row corresponds a single record.
————————–
Data Definition Language
Data Definition Language Commands are used to Create, Modify, and Drop the Structure of Database Objects like Table, View, and Index etc…
Data Definition Language Commands and 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 and Install MS SQL Server Express Edition (It is Free Edition) and practice SQL Commands and 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 column_name dataType(size);
Example:
Alter Table newStudents add City char(50);
————————–
8) Add multiple columns to an existing Table
Syntax:
Alter Table table_name add 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 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 command is used to delete complete data from an existing table
Syntax:
Truncate Table table_name;
Example:
Truncate Table newStudents;
————————–
13) Drop a Table
Drop Table command is used to delete complete Table (Data and Table Structure) from the Database.
Syntax:
Drop Table table_name;
Example:
Drop Table newStudents;
————————–
SQL Step by Step Tutorials
https://www.gcreddy.com/2016/08/sql-tutorials.html
SQL Tutorial 1: Introduction to SQL
https://www.gcreddy.com/2016/07/introduction-to-sql.html
SQL Tutorial 2: SQL Overview
https://www.gcreddy.com/2016/08/sql-overview.html
SQL Tutorial 3: SQL Environment Setup
https://www.gcreddy.com/2016/09/sql-environment-setup.html
SQL Tutorial 4: Database Fundamentals and SQL Language Elements
https://www.gcreddy.com/2016/11/database-fundamentals-and-sql-language.html
SQL Tutorial 5: Data Definition Language
https://www.gcreddy.com/2016/11/sql-data-definition-language.html
SQL Tutorial 6: Data Manipulation Language
https://www.gcreddy.com/2017/06/sql-tutorial-6-data-manipulation.html
SQL Tutorial 7: SQL Operators
https://www.gcreddy.com/2017/06/sql-operators.html
SQL Tutorial 8: The Select Query
https://youtu.be/kid__0MNRps
SQL Tutorial 9: SQL Joins
https://youtu.be/K6U_WtYb-xQ
SQL Tutorial 10: SQL Functions
https://youtu.be/kgNnLWRZOiI
SQL Tutorial 11: SQL Comments
https://youtu.be/fg11vHRSsk0
SQL Tutorial 12: SQL Date Functions
https://youtu.be/EcanQwCjdWQ
Top 10 Database Management Systems
https://youtu.be/9R8xVrMgOb8
SQL vs. NoSQL Databases
https://youtu.be/1rX4yTqxdfQ
SQL Interview Questions and Answers
https://www.gcreddy.com/2013/01/sql-interview-questions.html
————————–
