An Introduction To T SQL Server

Posted By : Annu Sehrawat | 27-Aug-2020

 

Database

A database is an organized collection of data. It is the gathering of schemes, tables, queries, reports, views, and other objects.

 

Relational Database

A relational database consists of a set of tables that stores particular Sets of data.

 

Creating a database

Create a new Database and therefore the file used to store the database.

Syntax:-

CREATE DATABASE database_name;

 

database_name:- database_name is often a maximum of 128 characters. Unless a logical name isn’t specified for the log file. If a logical log file name isn’t specified, SQL Server generates the logical_file_name and therefore the os_file_name for the log by appending a suffix to database_name. This limits database_name to 123 characters in order that the generated logical file name is not any quite 128 characters.

 

Creating a database that specifies data and transaction log files.

CREATE DATABASE Sales
ON
( NAME = Sales_dat,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\ MSSQL\DATA\saledat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\ MSSQL\DATA\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB ) ;

 

Drop database

Remove one or more user Databases from an Instance of SQL Server.

Syntax:-

DROP DATABASE database_name;

database_name:- Specifies the name of the database to be removed. To display an inventory of databases, use the sys.databases catalog view.

 

Dropping multiple databases:

Drop Database Sales, Order;

 

Alter database

Modifies a database, or the files and filegroups related to the database. Adds or removes filegroups from a database and modifies the elements of a database or its files and filegroups, changes the database collation, and sets database options.

Syntax:-

ALTER DATABASE OldDatabase_Name MODIFY NAME = NewDatabase_Name

 

OldDatabase_Name:- Is that the name of the database to be modified?

 

MODIFY NAME:- Renames the database with the name specified as New Database_Name.

 

Example:

USE[MASTER]
GO
ALTER DATABASE COLLEGE MODIFY NAME = COLLEGE

 

Data Definition Language (DDL)

Data Definition Language (DDL) is a vocabulary wont to define data structures in SQL Server. Use the following statements to create, alter, or drop data structures in an instance of SQL Server.

 

Create Command

Create command is additionally used to create a table. We can define names and data types of various columns along.

Syntax:-

Create table table-name
{
column-name1 datatype1,
column-name2 datatype2,
column-name3 datatype3,
column-name4 datatype4
};

 

RenameCommand

Rename command is employed to rename a table.

Syntax:-

Sp_Rename 'Old_Table_Name','New_Table_Name'

AlterCommand:

  • To add a column to an existing table.

Syntax:-

alter table table-name add(column-name datatype)

  • To modify an existing column.

Syntax:-

alter table table-name modify(column-name datatype)

  • To rename the column.

Syntax:-

Sp_RENAME 'Table_Name.Name','Name Change Column','COLUMN'

 

  • Alter is additionally used to drop a column.

Syntax:- 

ALTER TABLE table_Name DROP COLUMN Column_Name

 

Drop Command:

The Drop query completely removes a table from databases.

Syntax:-

drop table table-name;

Truncate Command:

The Truncate command removes all records from a table. But this command will not demolish the table's structure

Syntax:-

truncate table table-name;

 

Data Manipulation Language (DML)

Data Manipulation Language (DML) is a vocabulary wont to retrieve and work with data in SQL Server. Use the following statements to add, modify, query, or remove data from a SQL Server database.

 

Insert Command:

Insert command is employed to insert data into a table.

Syntax:-

INSERT into table-name values(data1,data2,..)

 

Example: A table student with the following fields.

 

Insert a record into the student table.

 

Update Command:

The Update command is employed to update a row of a table.

Syntax:-

UPDATE table-name set column-name = value where condition;

 

Delete Command:

Delete command is used to delete the data from a table. Delete command can also be used with the condition to delete a specific row.

Syntax:-

DELETE from table-name;

 

We are a 360-degree SaaS app development company that focuses on building high-quality web and mobile applications using the latest tools, frameworks, and SDKs. Our development team analyzes your project requirements and formulates effective strategies to create performance-driven applications that maximize enterprise benefits. We have successfully completed several full-scale SaaS application development projects with a focus on next-gen technologies. For project-related queries, reach us out at info@oodlestechnologies.com.

About Author

Author Image
Annu Sehrawat

She is hard working, self-motivated and have zeal to learn new technologies.

Request for Proposal

Name is required

Comment is required

Sending message..