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
