SQL SERVER Tips and Tricks

DDL: Data Definition Language.
DDL commands define the structure to the database objects.

CREATE is one of the important DDL command. As the name indicates this command is used to create the database object.

Any database object like table,view,function, stored procedure, rule, user defined database etc., can be created using this Command.

CREATE command is used to define the structure for Database also.

Here are some simple examples for database objects creation using CREATE command:

• CREATE DATABASE: Database creation syntax is given below.

          CREATE DATABASE <Database Name>

          CREATE DATABASE TEST -- This Statement creates a database with the name TEST 

• CREATE TABLE: Table creation syntax is given below.

          CREATE TABLE <TABLE Name> ( Col1, col2,....Coln)
           *Col : Column
  
           Lets create a simple table with name MOBILE with columns as Mobile Brand,Mobile   Model, and Price of the mobile.

Below picture gives commands used to create the table












Above statements Create a table with the name MOBILE in the database.

• CREATE PROCEDURE:

Here we will see a simple stored procedure.
Create a stored procedure with the name GET_DATA to fetch data from MOBILE Table

CREATE PROCEDURE GET_DATA
AS
BEGIN
        SELECT * FROM MOBILE
END

Above statements create a stored procedure with the name GET_DATA.

We will look into stored procedures in depth in coming sessions.

• CREATE VIEW:

View is a virtual table. I will discuss more about views in coming posts.

For now, lets create a view which provides list of all mobiles whose price is less than Rs.5000/-

CREATE
AS
SELECT * FROM MOBILE VIEW MOBILE_VIEW WHERE PRICE<5000
WHERE
This view will list all the data from MOBILE table whose PRICE is less than 5000.

• CREATE FUNCTION:

Functions are set of SQL Commands which perform complex operations or calculation in block and returns the output value.

Function can return a scalar value or Table. Functions will be discussed in depth in later Sessions.

For now, lets create a simple function which gives us the least price of the mobile.
CREATE FUNCTION PRICE_FUNCTION()RETURNS
AS
BEGIN
   DECLARE  @PRICE INT
   SELECT @PRICE   MIN(PRICE) FROM MOBILE
RETURN @PRICE  
END
So far Using CREATE command we have created simple Database, Tables, Views, Procedures and Function.
Summary:
In this article we have learnt about various ways to use CREATE command in SQL Server.

Commands Learnt:
CREATE,DATABASE,TABLE,VIEW, PROCEDURE,FUNCTION,AS,BEGIN,END,RETURNS,INT , MIN