Syntax to DROP Database

DROP DATABASE DATABASENAME

Example: DROP DATABASE NRSTT

The above statement deletes NRSTT 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 NRSTT database and till we enter in to another database.

Syntax to DROP Database

ALTER the Database

Syntax to ALTER the Database:

ALTER DATABASE DATABASENAME.ADD FILE(NAME=’USER DEFINED NAME’,FILENAME=’PHYSICAL ADDRESS’,SIZE= MB,MAXSIZE= MB)

Example:

ALTER DATABASE NRSTT ADD FILE(NAME=’NRSTTNDF’,FILENAME=’C:\Program Files\Microsoft Sql Server\Mssql\Data\NRSTT.NDF’SIZE=5 MB,MAXSIZE=25 MB,FILEGROWTH=5 MB)

The above query extends the NRSTT database to 5mb by adding new file NRSTT.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]

Example:

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

Example: 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]

Example: ALTER TABLE EMP DROP COLUMN ENAME, SAL

The above statement deletes two existing columns ENAME, SAL from EMP Table.

ALTER the Database

create a TABLE

Syntax to create a TABLE:

CREATE TABLE Tablename (Column1 Datatype, Column2 Datatype,……..)

Example:

CREATE TABLE CUSTOMER (CNO INT, CNAME VARCHAR (20), CITY VARCHAR9 (20));

Example:

CREATE TABLE EMP(EMPNO INT, ENAME VARCHAR(20),SAL MONEY,DEPTNO INT);

The above two queries creates two tables with names CUSTOMER, EMP.

create a TABLE

Each database file has five properties

Each database file has five properties:

1) Logical File Name : Name
2) Physical File Name : Filename
3) Initial Size : Size
4) Maximum Size : Maxsize
5) Growth increment : Growth

After Creating the Database server arranges internally two files.

A) Primary File: It was defined by the server with 0.63mb size and with extension ".mdf" (master data file) for holding start up information of the database.

Example: NRSTT.Mdf

B) Log File: It was defined by the server with 0.40mb size and with extension ".ldf" (log data file) for holding transaction information of the database.

Example: NRSTT.ldf

Each database file has five properties

SQL Server 2000 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.

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 scondary database file has the extension NDF.

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.

SQL Server 2000 allows three types of database files

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

Database files and File groups

Data Definition Language

This is the definition level language which includes the following statements.

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 (Tables,Views,Indexes,User Defined Stored Procedures,User Defined Functions,Triggers,Rules,Defaults)

B) ALTER Statement: This Statement is used for modifying the database and its objects (Tables,Views,Indexes,User Defined Stored Procedures,User Defined Functions,Triggers,Rules,Defaults)

C) DROP Statement: This Statement is used for deleting the database and its objects (Tables,Views,Indexes,User Defined Stored Procedures,User Defined Functions,Triggers,Rules,Defaults)

D) TRUNCATE Statement: This Statement is used to delete the data available in a table in Row-By-Row manner but with out disturbing its structure(columns).

Data Definition Language

Things to Observe on SQL

A) While writing the Queries using in SQL Query Analyzer tool we need not to follow any particular case.Because SQL is case insensitive language.
B)After writing the Query,we need to select that query using either mouse or keyboard.
C)Now Press F5.
D) Then the results are displayed in a separate window called Result window or Result Pane.
E) Use Ctrl+R to Hide/Show the result window or result Pane.

Note:
1) SQL SERVER can handle nearly 32767 Databases.
2) Each Database can handle nearly 20000 Tables.
3) Each Table can handle nearly 1024 Columns.
4) Each Table can handle nearly 2lacks Rows.

Things to Observe on SQL