Data Definition Language:
a. CREATE Statement
b. ALTER Statement
c. DROP Statement
d. TRUNCATE Statement
a. CREATE Statement: This Statement is used for creating the database and its
objects .
b. ALTER Statement: This Statement is used for modifying the database and its
objects
c. DROP Statement: This Statement is used for deleting the database and its objects
d. TRUNCATE Statement: This statement is used to delete the data available in a
table in PAGE-By-PAGE manner but with out disturbing its structure (columns).
Syntax to create the database:
CREATE DATABASE DATABASENAME
E.g.: CREATE DATABASE SAMPLE
After writing the above Query in SQL Server Management Studio then select it and press
F5. Then server creates database with name SAMLE, that we can check it in Database list
box in SSMS
Database files and File groups
A database file is nothing but an operating system file. A database spans at least two, and
possible several database files. These files are specified when database is created or
altered. Every database must span at least two files. One for the data And one for
transaction log.
SQL Server 2005 allows three types of database files.
Primary data files: Every database has one primary data file. This file contains the
startup information for the database and is used to store data. The name of primary
database file has the extension MDF(master data file).
Secondary data files: These files hold all of the data that does not fit into the primary
data file. A database can have zero or more Secondary data files. The name of secondary
database file has the extension NDF(next data file).
Transaction Log files: These files hold the log information used to recover the database.
There must be at least one log file for each database. The name of a Log file has the
extension LDF(log data file).
Each database file has five properties:
à Logical File Name. Name
à Physical File Name. Filename
à Initial Size. Size
à Maximum Size. Maxsize
à Growth increment. Growth
After Creating the Database server arranges internally two files.
1. Primary File: It was defined by the server with 1.18 mb size and with
extension “. mdf” (master data file) for holding start up information of the
database.
Ex: SAMPLE. Mdf
2. Log File: It was defined by the server with 504 kb size and with extension
“. ldf” (log data file) for holding transaction information of the database.
Ex: SAMPLE. ldf
Syntax to create a TABLE:
CREATE TABLE Tablename(Column1 Datatype,
Column2 Datatype,…………………)
E.g. CREATE TABLE CUSTOMER (CNO INT, CNAME VARCHAR (20),
CITY VARCHAR (20))
E.g. CREATE TABLE EMP (EMPNO INT, ENAME VARCHAR (20),
SAL MONEY, DEPTNO INT)
The above two queries creates two tables with names CUSTOMER, EMP
Syntax to ALTER the Database:
ALTER DATABASE DATABASENAME
ADD FILE
(NAME = ’Any NAME’,
FILENAME = ‘PHYSICAL ADDRESS’,
SIZE =<n>MB,
MAXSIZE =<n.>MB,
FILEGROWTH=<n>MB)
E.g. ALTER DATABASE SAMPLE ADD FILE
(NAME=‘RAM’,
FILENAME=’D:\ProgramFiles\MicrosoftSQL
Server\MsSQL.1\MSSQL\Data\SAMPLE.NDF’
SIZE =5 MB,
MAXSIZE =25 MB,
FILEGROWTH=5 MB)
The above query extends the SAMPLE database to 5mb by adding new file
SAMPLE.NDF.
Syntax to ALTER the Table: In Three ways we can modify the tables
1. By Adding the new column to the Existing Table
Syntax:
ALTER TABLE TABLENAME ADD NEWCOLUMN DATATYPE [,……..N]
E.g.: ALTER TABLE EMP ADD BONUS MONEY
The above statement adds Bonus column to EMP table
2. By Changing the Data type of an Existing column
Syntax:
ALTER TABLE TABLENAME ALTER COLUMN COLUMNNAME NEWDATATYPE
E.g.: ALTER TABLE EMP ALTER COLUMN EMPNO BIGINT
The above statement changes the EMPNO data type from INT to BIGINT.
3. By Dropping the Existing column from Existing Table
Syntax: ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME [,…….N]
E.g.: ALTER TABLE EMP DROP COLUMN ENAME, SAL
The above statement deletes two existing columns ENAME, SAL columns from EMP
Table.
Syntax to DROP Database
DROP DATABASE DATABASENAME
E.g. DROP DATABASE SAMPLE
T he above statement deletes SAMPLE database and its objects
Note: It is not possible to drop a particular database which is currently in use it means it
is not possible to drop SAMPLE database and till we enter in to another database
Syntax to DROP the Table:
DROP TABLE TABLENAME [,…………..n]
E.g. DROP TABLE EMP
The above statement deletes EMP table including its structure (columns)
E.g. DROP TABLE CUSTOMER, STUDENT
The above statement deletes customer and student tables at a time. It means we can drop
multiple tables at a time.
Note: Users can DROP multiple Databases at a time by placing comma (,) between the
database names but it is not recommended approach.
Syntax for TRUNCATE Statement:
TRUNCATE TABLE TABLENAME
E.g. TRUNCATE TABLE EMP
The above statement deletes all data available in EMP table in PAGE-BY-PAGE manner
(all at once) with out disturbing its structure (Columns)
