Archive

Archive for May, 2009

SQL Basics – Create Database and Tables in SQL Server

May 25, 2009 5 comments

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.