Home > DB Concepts, SQL Basics > SQL Basics – Create Database and Tables in SQL Server

SQL Basics – Create Database and Tables in SQL Server

A Database, it is a collection of data/information organized in a particular fashion so that it can be easily managed and accessed back in same fashion. The data stored and retrieved should in same format and should be consistent.

A Relational Database stores data in Tables, and there can be multiple tables in a database, which may be related to each other by referential integrity.

A Table is a collection of related data stored in a structured format within a database, in form or rows (tuple) and columns (fields).

–> Database:

Check video on how to create a new Database by using both, SSMS GUI and T-SQL Script:

–> T-SQL script to Create a new Database:

USE master

	NAME = N'Demo', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER2016\MSSQL\DATA\Demo.mdf' , 
	SIZE = 4096KB , 
	NAME = N'Demo_log', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER2016\MSSQL\DATA\Demo_log.ldf' , 
	SIZE = 1024KB , 


–> Table:

Check video on how to create a new Table by using both, SSMS GUI and T-SQL Script:

–> T-SQL script to Create a new Table:

USE [Demo]

CREATE TABLE [dbo].[Employee](
	[EmployeeID]	[int]			NOT NULL,
	[EmployeeName]	[nvarchar](100) NOT NULL,
	[Gender]		[nchar](1)		NULL,
	[DOB]			[datetime]		NULL,
	[DOJ]			[datetime]		NULL,
	[DeptID]		[int]			NULL

SELECT * FROM [dbo].[Employee]

INSERT INTO [dbo].[Employee]
VALUES (1, 'MANOJ PANDEY', 'M', '1990-01-01', '2010-01-01', 101)

sp_help 'Employee' -- ALT + F1

INSERT INTO [dbo].[Employee]
VALUES (2, 'JHON K', 'M', NULL, '2010-01-01', NULL)

INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName])
VALUES (3, 'Brock H')

sp_rename 'Employees', 'Employee'

DELETE FROM [dbo].[Employee] WHERE EmployeeID = 2

TRUNCATE TABLE [dbo].[Employee]

DROP TABLE [dbo].[Employee]


Check my [next post] on how to ADD/DROP columns in a table.

  1. Erin
    March 22, 2016 at 11:04 pm

    On the exam do you have to know how to do both, writing a script to create a database and how to create one in Management Studio?

    • March 23, 2016 at 10:15 am

      they might ask to create a Database by writing a TSQL script, or give you script in jumbled form, and you’ve to arrange it.

  2. mangesh
    May 25, 2016 at 5:51 pm

    Hi Manoj,

    As per Microsoft website https://www.microsoft.com/en-in/learning/exam-70-462.aspx

    Exam-70-462 has SQL 2012 & SQL 2014 content is that true ? if yes ,what syllabus is added for SQL 2014 ? can you please comment on this ?

    Thanks In Advance.

  1. October 25, 2015 at 11:11 pm
  2. November 1, 2015 at 11:32 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: