Advertisements
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
GO

CREATE DATABASE [Demo] ON  PRIMARY ( 
	NAME = N'Demo', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER2016\MSSQL\DATA\Demo.mdf' , 
	SIZE = 4096KB , 
	FILEGROWTH = 1024KB 
)
LOG ON ( 
	NAME = N'Demo_log', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER2016\MSSQL\DATA\Demo_log.ldf' , 
	SIZE = 1024KB , 
	FILEGROWTH = 10%
)
GO

 

–> 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]
GO

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.


Advertisements
  1. 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.
    Mangesh.

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

    Manoj
    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.

  1. November 1, 2015 at 11:32 pm
  2. October 25, 2015 at 11:11 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: