SQL Server Database Basics Query Command

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 Test

Database Rename

 

Commands:

1. Exec sp_Renamedb ‘your current Database Name’, ‘New Database Name’  
2. exec sp_renamedb 'Test','TestDB'  

Remove/Delete Database

Commands:

1. Drop Database YourDatabaseName  
2. drop database TestDB

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 Test
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 'Test','TestTable'  

Table Remove/Delete

Commands:

1. Drop Table TableName  
2. drop Table TestTable

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 a new Column In Table

Commands:

1. Alter Table tablename  
2. Add Column datatype  
3.   
4. alter table Test  
5. add Surname varchar(20)  

Column Rname In Table

Commands:

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

Delete Column In table

Commands:

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

Column Identity

It's important 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 Test  
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 Test values ('Sameer','08-11-1986','9987654321','Mumbai')  
3. or  
4. Insert into Test (Name,BirthDate,Phone,City) values ('Sameer','08-11-1986','9987654321','Mumbai')  

Data Insert With inserted Data Record Display

Commands:

1. Insert Into TableName output inserted.* values (column1,column2)   
2. insert into info output inserted.* values ('Sameer','08-11-1986','9987654321','Mumbai')  

Select Data From Database Table

Commands:

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

Update Data in Database Table

Commands:

1. Update TableName SET ColumnName = NewData where Condition  
2. Update Test Set City = 'Delhi' where id = 2  

Delete Data Record From Database Table

Commands:

All Table Records Delete

1. Delete TableName  
2. DELETE Test  

Any One Record Delete

1. Delete from TableName where condition  
2. Delete from Test where Name='Sameer'  

Unique

This is for identifying each record.

Commands:

1. Column datatype unique  
2.   
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  
2.   
3. create table Employee  
4. (  
5. EmpID int Primary Key,  
6. EmpName varchar(20)  
7. )  

FOREIGN KEY

Anyone 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 is defined for 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.