Post
Ask Question

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

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.


Similar Articles