Business Process Reengineering

SQL Server Queries

Firstly, Click on New Query in SQL Server and the fire query as you need……..

Create Database:

Syntax: Create Database database Name

Example: Create Database Sample

Rename or Alter Database:

1. Syntax: Alter Database old database name Modify Name = new database name

            Example: Alter Database Sample Modify Name = Test

1. Syntax: Execute sp_renamedb ‘old database name’, ‘new database name’

Example: Execute sp_renamedb ‘Sample’, ‘Test’

          Note: you cannot rename system database.

         So, if other users connected, you need to put the database in single user mode     and then drop the database.

1. Syntax: Alter Database database name Set SINGLE_USER With Rollback Immediate

Example: suppose 3 users are using the same database named Hello, then the database must be put in single user mode.

  Alter Database Hello Set SINLE_USER With Rollback Immediate

Delete or Drop Database:

Syntax: Drop Database database name

Example: Drop Database Test

Note: you cannot delete database, if it is currently in use and you cannot delete or drop system database of SQL Server.

Creating Tables in database:

Note: First select database where you want to create table.

Syntax: Create table table name (column name, column name)

Example: Create table users (id int NOT NULL primary key Identity, username varchar(200) NOT NULL, password varchar(100) NOT NULL)

Here, Identity means Auto Increment i.e its value increases by 1 automatically.

Foreign key Relationship:

Syntax: Alter table table name add constraint second table matching_same column name_FK Foreign key (matching column name) references first table name(matching column name)

Note: First select database in or write query Use database name: Use Test

Here, Test is our database name which we have created….

Suppose we have created two tables users and login as

Users table: Create table users (id int NOT NULL primary key Identity, fullname varchar(200) NOT NULL, addresss varchar(100) NOT NULL)