EASY T-SQL

Originally posted by myself on: http://rchannel.azurewebsites.net/ChannelR/Details/3

CREATE, INSERT AND ALTER. EASY AND SPEEDY.

PREREQUISITES

  • A fine understanding of general databases and their tables. Knowing Microsoft Access basics would be beneficial.
  • SQL Server Management Studio 2012 Express or SQL Server Management Studio 2014 Express. You can download one of the above software at Microsoft’s website.

A. CREATING A NEW DATABASE

Short answer: create database SomethingDB
 
Long answer:

1. Open SQL Server Management Studio (SSMS)

2. Login to your server.

3. Right click your ServerName

4. Click new query.

5. Type:

create database SomethingDB

6. Click the "Execute" icon

Or press F5 on your PC keyboard to execute your command. Boom! Your database has been created.

7. Click the refresh icon to see your database “SomethingDB”.


B. CREATING A NEW TABLE

Short answer:

Create table tblEmployee
(
 id int not null,
 FullName varchar(25),
 age int
)

 
Long answer:
 
1. Right click your databaseName "SomethingDB"

2. Click on "New Query"

3. Type:
 
Create table tblEmployee
(
 id int not null,
 fullName nvarchar(25),
 age int
)

tblEmployee --------> Table Name

int --------> integer data type (used by Visual Studio to deferentiate between numbers and alphabets)

nvarchar(25) --------> 25 characters (keyboard letters and symbols)


C. INSERT RECORDS INTO A TABLE

Short answer:

Insert into tblEmployee(id,FullName,age)
values (1,'Rishi Raj Gujadhur',21)

 
1. Create a new query

2. Ensure that the somethingDB database is selected in the Available Databases
 
Shortcut: Ctrl + U

3. Type:

Insert into tblEmployee(id,fullName,age)
values (1,'Rishi Raj Gujadhur',21)

Insert into tblEmployee -----------> Insert data into the table tblEmployee.

(id,fullName,age) -----------> column names to which data will be added.

values (1,'Rishi Raj Gujadhur',21) -----------> values to add. (Their typing order is based on the columns names' order).

4. Execute the query
Shortcut: F5


D. VIEWING ALL RECORDS FROM A TABLE

Short answer: select * from tblEmployee
 
Long answer:

1. Create a new query

2. Select the somethingDB database in the Available Databases 
Shortcut: Ctrl + U

3. Type:
 
select * from tblEmployee

* -----------> All data

4. Execute query
Shortcut: F5

Result:


E. ALTER A TABLE

Short answer:

Alter table tblEmployee
add salary int

 
Long answer:
 
1. Create a new query

2. Select your database "somethingDB"
Shortcut : Ctrl + U

3. Type:

Alter table tblEmployee
add salary int

Alter table tblEmployee -----> Modify table named tblEmployee

add salary int -----> add salary column whose data type is integer into tblEmployee.

By default the salary column can be null (empty).

4. Execute the query
Shortcut: F5