STEP BY STEP SQL

Featured

DATABASE

A Database in SQL Server consists of mainly database objects like Tables, Stored Procedures, User Defined Functions, Views and so on.

A database instance will usually be having multiple System Defined databases and User created databases.

MASTER,MSDB,MODEL and TEMPDB databases are the System Databases which shipped with SQL Server by default.

SYSTEM DATABASE	DESCRIPTION
MASTER	Records all system-level information for an instance of SQL Server.
MSDB	Used by SQL Server Agent for scheduling alerts and jobs.
MODEL	Used as the template for all databases created on the instance of SQL Server.
TEMPDB	It is used for holding temporary objects or intermediate result-sets.

Below is the Basic Syntax for Creating a Database in SQL Server.

Syntax:

CREATE DATABASE <databasename> ;

Example:

CREATE DATABASE sqltutorial;

Database names must be unique within an instance of SQL Server.

We can also create new database by using SQL Server Management Studio.

Connect to SQL Server instance and right-click on the databases folder.
Then click New Database
Type the database name in the dialog box , for example, sqltutorial
click OK.

We can use the USE statement to select the Database on which we want to perform the database operations.

USE <databasename>

DROP DATABASE

We can drop a database using DROP Statement

Syntax:

DROP DATABASE <databasename>;

Example:

DROP DATABASE sqltutorial ;

TABLE

Tables in Sql Server stores data in the form of Rows and columns.

Below is the basic syntax for creating Table in Sql Server.

CREATE TABLE <TableName>
 (  
    <ColumnName1> <Datatype> [CONSTRAINT], 
    <ColumnName2> <Datatype> [CONSTRAINT],                                               
                      .  
                      .
    <ColumnNameN> <Datatype> [CONSTRAINT] 
)

Example:

CREATE TABLE Customer
(
CustomerId INT NOT NULL,
Name NVARCHAR(100) NOT NULL,
PhoneNumber VARCHAR(15) NULL
)

INSERTING RECORDS

We can add records to the Table by using INSERT statement as shown below

INSERT INTO table_name (column1, column2, column3, …) VALUES (value1, value2, value3, …);

Adding records to the Customer Table with all the column values.

INSERT INTO Customer (CustomerId, Name, PhoneNumber)
VALUES (1, ‘Shree’, ‘1234567890’)

RETRIEVING DATA FROM TABLE

We can use the SELECT statement to fetch records from the Table.

SELECT column1, column2, … From <Table name>

We can use * instead of the column names for fetch all records from table

SELECT * from <Table name>

Try to use column names instead of * to ensure better performance.

Fetch data from customer table

ALTER TABLE

We can modify the Table structure using ALTER TABLE Statement.

TO ADD COLUMN IN TABLE

ALTER TABLE <table_name> ADD <column_name> <column-definition>;

Example

ALTER TABLE Custome ADD DateOfBirth DATETIME NULL

MODIFY COLUMN IN TABLE

ALTER TABLE <table_name> ALTER COLUMN <column_name> <column_type>;

EXAMPLE

ALTER TABLE Customer ALTER COLUMN Name NVARCHAR(500);

DROP COLUMN IN TABLE

ALTER TABLE <table_name> DROP COLUMN <column_name>;

EXAMPLE

ALTER TABLE Customer DROP COLUMN DateOfBirth;

RENAME COLUMN IN TABLE

The syntax to rename a column in an existing table in SQL Server (Transact-SQL) is:

sp_rename ‘table_name.old_column_name’, ‘new_column_name’, ‘COLUMN’;

Example

SP_RENAME ‘Customer.Name’, ‘CustomerName’, ‘COLUMN’;

The syntax to rename a table in SQL Server (Transact-SQL) is:

sp_rename ‘old_table_name’, ‘new_table_name’;

Example

sp_rename ‘Customer’, ‘CustomerDetails’;

TRUNCATE TABLE

The TRUNCATE TABLE statement is used to remove all records from a table in SQL Server. It performs the same function as a DELETE statement without a WHERE clause.

Syntax

TRUNCATE TABLE <table name>

Example

TRUNCATE TABLE CustomerDetails

DROP TABLE

DROP TABLE statement deletes both data and table definition permanently from the database.

This action cannot be undone.

Syntax:

DROP TABLE <Table name>

Example

DROP TABLE CustomerDetails

SQL BASICS

SQL is a standard language for storing, manipulating and retrieving data in databases.

SQL stands for Structured Query Language
SQL can use for create new databases
SQL can use for create table and insert records in a database
SQL can use for select data from table in database
SQL can use for update and delete records from a table in database
SQL can use for create stored procedures in a database
SQL can use for create views in a database
SQL can use for set permissions on tables, procedures, and views
SQL is the standard language for relational database management systems
Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc