SQL Server Create Table

You can create Table in two ways 

1) From SQL server Management Studio 
2) By Sql Script




Create Table From SQL server Management Studio

1)Go to SQL server Management Studio, select database, open database.

2)Right Click on Table , select "New Table".

3)You will get a new Interface to design table.You can add Column Name and Datatype as  may as you want. Every time you fill one entry, another entry will be automatically created. 

4)Now "Save" your work.You will get a new prompt .The prompt will ask for table name.

5)Then click on "Ok"



Create Table From Sql Script


1)Go to SQL server Management Studio, select database, open "New Query" window

2)Now write table creation script

Syntax
CREATE TABLE table_name (
    column1 datatype ,
    column2 datatype,
   ....
   ....
); 

Here is the script to create student table

CREATE TABLE student

(

        id INT NOT NULL,

        name VARCHAR (500) NOT NULL,

        age INT NOT NULL,

        adress VARCHAR (500) NOT NULL,

       date_of_birth DATETIME NOT NULL,

       height DECIMAL(10,2) NOT NULL

)

Now , run script , table will be added to the database.

 

SQL Server Select Database

SQL server select database in two ways, by using SQL server Management Studio or by writing script.

Select Database SQL server Management Studio.



1)Go to SQL server management studio, select database

2)Right click on the database, click on "New Query".

3)You database is selected and you can write query on the database.

Select Database using script

You can select database by writing script also. click on "New Query"  window  and  write the following  script
 

use Database_Name

your database will be selected.

You can select multiple database in separate window.The window you will select , the database will be selected automatically

SQL Server BETWEEN

'Between' operator is a range selector, your output will be filter between from range and  to range column values. 

The syntax is
 
expression between  value1 to  value2

Between operator work on any type of SQL query like , select,update ,delete ,join,group by ,distinct etc. Between operator mainly work on numeric ,integer ,decimal values, but it also work on date values.

Here , we have created a table and inserted data to the table for example purpose.

CREATE TABLE student

(

        id INT NOT NULL,

        name VARCHAR (500) NOT NULL,

        age INT NOT NULL,

        adress VARCHAR (500) NOT NULL,

       date_of_birth DATETIME NOT NULL,

       height DECIMAL(10,2) NOT NULL

)

       INSERT INTO student(id,name,age,adress,date_of_birth,height)

       SELECT 1,'John1',12,'34,student street','01/08/2018',10.5

       UNION

       SELECT 1,'John2',13,'35,student street','02/08/2018',11

       UNION

       SELECT 1,'John3',14,'36,student street','03/08/2018',11.5

       UNION

       SELECT 1,'John4',15,'37,student street','04/08/2018',12

       UNION

       SELECT 1,'John5',16,'38,student street','05/08/2018',12.5

       UNION

       SELECT 1,'John6',17,'39,student street','06/08/2018',13.5

       UNION

       SELECT 1,'John7',18,'40,student street','07/08/2018',14

       UNION

       SELECT 1,'John8',19,'41,student street','08/08/2018',14.5

       UNION

       SELECT 1,'John9',20,'42,student street','09/08/2018',15

       UNION

       SELECT 1,'John10',21,'43,student street','10/08/2018',15.5

       UNION

       SELECT 1,'John11',22,'44,student street','11/08/2018',16

1)Here is the example of between operator with integer valueExample

SELECT name,adress FROM student

         WHERE age BETWEEN 13 AND 20

Output

name     adress

John2    35,student street
John3    36,student street
John4    37,student street
John5    38,student street
John6    39,student street
John7    40,student street
John8    41,student street
John9    42,student street 

2)Here  is there example of between operator with decimal values
Example

SELECT name,adress FROM student

WHERE height BETWEEN 13.5 AND 15

Output

name     adress 

John6    39,student street
John7    40,student street
John8    41,student street
John9    42,student street

3)Here  is the example of between operator with date values

Example

SELECT name,adress FROM student

WHERE date_of_birth BETWEEN '05/08/2018' AND '10/08/2018'

Output

name     adress 

John10   43,student street
John5    38,student street
John6    39,student street
John7    40,student street
John8    41,student street
John9    42,student street 

4)Here is example of between operator with update statement

Example

UPDATE student

SET name=name+'-'+CONVERT(VARCHAR,age)

WHERE date_of_birth BETWEEN '08/08/2018' AND '09/08/2018'

SELECT name,adress FROM student

WHERE date_of_birth BETWEEN '08/08/2018' AND '09/08/2018'

name     adress
John8-19    41,student street
John9-20    42,student street

SQL Server Delete Table

you can delete table from SQL server in two ways, using SQL server Management studio or by using script.

Delete table SQL server Management studio.

1)Go to SQL server management studio, select database.

2)Open tables,select tables.

3)Right click on the table , you will get a  context menu. 

4)Choose delete table. New interface will be open to inform you that you are going to delete the table object . 

5)Click on "Ok". Your table will be deleted



Delete table by using script.

You can delete table by using script also. 

DROP TABLE table name 

is the syntax of the same. Now execute the query .Table will be deleted and you will get and confirmation message also.

 

DROP TABLE student

SQL Server Create Datatabase

SQL Server has two option to create database. You can create database by using interface or by  executing SQL Query.


Create Database from Interface:



1)Go to SQL server management Studio , you will see the database.

2)Right click on the database, you will get a context menu.

3)Choose "New database" .An interface will be open.

4)Put your database name, set the physical path of your database files ".mdf" and ".ldf" and click on "Ok " button, you will see in New database has been created.

Create Database from SQL Script:

Another way to create database by writing SQL script. Go to SQL server management studio, open a "New Query" widow, write a script  to create database and execute statement. You will see a New database has been created.

CREATE DATABASE Turorial

 

You should keep in mind, database name should be unique in a server. You cannot create database  with same name in the same server. There are several option during database creation from both management Studio and script. You can set permissions, physical file configuration and path setting, database file size setting a ect.




Create Database from Existing file.

You can create database, if you have database physical file.
"mdf" , "ldf" are physical file of sql server database.You can restore with the two files.

Here is a example


1)Go to SQL server management studio, open a "New Query" widow, write script, change your file path
 

CREATE DATABASE Tutorial

ON ( FILENAME = 'C:\my_file_path\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Tutorial.mdf' )

LOG ON ( FILENAME = 'C:\my_file_path\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\Tutorial.ldf')

GO

2) execute statement

You will see the database have been created.