Hello, this article is for beginners and students.

This article provides some SQL Server database basic information including database creation, data table creation and basic query information.

Create New Database

Commands:
  1. Create database [your database name]
  2. create database SqlDataTable


Database Rename
Commands:
  1. Exec sp_Renamedb ‘your current Database Name’, ‘New Database Name’
  2. exec sp_renamedb 'SqlDataTable','SqlDatabase'



Remove/Delete Database

Commands:

  1. Drop Database YourDatabaseName
  2. drop database SqlDatabase

This section describes database table creation, delete and rename with SQL queries.

Create Table in Database

Command:

  1. Create table TableName (columnName1 datatype, columnName2 datatype )
The following is an example of creating a simple table.
  1. create table Info
  2. (
  3. Name varchar(20),
  4. BirthDate date,
  5. Phone nvarchar(12),
  6. City varchar(20)
  7. )



Table Rename/Change Table Name

Commands:

  1. Sp_rename ‘Current Table’,’New Table Name’
  2. sp_rename 'Info','PersonInfo'



Table Remove/Delete

Commands:

  1. Drop Table TableName
  2. drop Table PersonInfo



The following is an example of a query for a database column. Now, add a new column, rename the column and delete the column with SQL Query.

Add new Column In Table

Commands:

  1. Alter Table Tablename
  2. Add Column datatype
  4. alter table Info
  5. add Surname varchar(20)


Column Rname In Table

Commands:

  1. Sp_rename ‘Table.CurentColumnName’,‘NewColumnName’,‘Column’
  2. sp_rename 'Info.Name','FullName','column'



Delete Column In table

Commands:

  1. Alter Table YourTable
  2. Drop Column TableColumnName
  3. alter table Info
  4. drop column surname



Column Identity

It's imporant for any database table column, when we create an auto-increment number column in the database table.

Command:

ColumnName datatype Identity(n,n)

Here, Id is the auto-incremented number.

  1. create table Info
  2. (
  3. id int identity (1,1) not null,
  4. Name varchar(20),
  5. BirthDate date,
  6. Phone nvarchar(12),
  7. City varchar(20)
  8. )


This section describes the SQL data process of inserting, updating and selecting data and a delete data query.

Data Insert In Table

Commands:

  1. Insert into TableName values (column1, column2)
  2. insert into Info values ('Rakesh','05-11-1986','9924194054','Ahmedabad')
  3. or
  4. Insert into info (Name,BirthDate,Phone,City) values ('Sagar','11-07-1990','9924141099','Surat')



Data Insert With inserted Data Record Display

Commands:

  1. Insert Into TableName output inserted.* values (column1,column2)
  2. insert into info output inserted.* values ('Rakesh','05-11-1986','9924194054','Ahmedabad')



Select Data From Database Table

Commands:

  1. Select * from TableName
  2. select * from info

Update Data in Database Table

Commands:

  1. Update TableName SET ColumnName = NewData where Condition
  2. Update info Set City = 'Baroda' where id = 2


Delete Data Record From Database Table

Commands:

All Table Records Delete

  1. Delete TableName
  2. DELETE info



Any One Record Delete

  1. Delete from TableName where condition
  2. Delete from Info where Name='Sagar'


Unique

This is for identifing each record.

Commands:

  1. Column datatype unique
  3. create table Employee
  4. (
  5. EmpId int unique not null,
  6. EmpName varchar(20)
  7. )



Primary Key

  • Uniquely identify record
  • it's not null
  • Only one Primary Key a SQL Table

Commands:

  1. ColumnName datatype Primary key
  3. create table Employee
  4. (
  5. EmpID int Primary Key,
  6. EmpName varchar(20)
  7. )


FOREIGN KEY

Any one SQL table's primary key uses any other SQL Table.

Commands:

ColumnName datatype Foreign Key References ExitingTableName(ExitingTable ColumnName)

  1. Create Table Department
  2. (
  3. DepartmentId int Primary Key,
  4. Department varchar(20),
  5. EmpID int Foreign Key References Employee(EmpID)
  6. )


Unique Key Constraint

Unique Constraint defined for a single or multiple columns.

Commands:

Constraint ConstraintName Unique (Column1, Column2)

  1. Create table Employee
  2. (
  3. EmpId int not null,
  4. EmpName varchar(20) not null,
  5. City varchar(20),
  6. constraint UC_Employee Unique(EmpId,EmpName)
  7. )



Primary Key Constraint

Commands:

Constraint ConstraintName Primary Key (Column)

  1. Create table Employee
  2. (
  3. EmpId int not null,
  4. EmpName varchar(20),
  5. City varchar(20),
  6. constraint PK_EmpID Primary Key(EmpId)
  7. )



Foreign Key Constraint

Commands:

Constraint ConstraintName Foreign key (Column) References Table(Column)

  1. create table Department
  2. (
  3. DepartmentId int not null primary key,
  4. Department varchar(20),
  5. EmpId int,
  6. constraint FK_Emp_ID Foreign key (EmpId) References Employee(EmpId)
  7. )


Check Constraint

Commands:

Constraint ConstraintName CHECK (Column+condision )

  1. create table Employees
  2. (
  3. EmpId int,
  4. EmpName varchar(20),
  5. Salary smallmoney,
  6. CONSTRAINT CK_Salary CHECK (Salary > 4000)
  7. )



Note: Here the Employee Salay is greater than 4000.

I hope you like this basic example.