Filter posts by Category Or Tag of the Blog section!

An Introduction to basic concepts of SQL Server

Monday, 10 September 2012

I'm going to write about basic concepts of Microsoft  SQL Server in two-part this is the part one! , as I've rarely seen SQL graphical text tutorial (most of them are text only and it does not make good sense for beginners like me), I want to fit more and more picture in this Article! This article is about SQL language fundamentals, and obviously, it is familiar to most of the developers!

Introduction to SQL language 

SQL is a language designed to retrieve and management of data in relational databases, SQL is a language and not all relational databases support SQL, so if you have SQL management studio installed in your system, let's begin!

CRUD operation 

Create, read, update and delete are the most common operation in most software systems and in every Enterprise application.


Right click on Databases and create a new database, name it and then right click on that tables and create a table :



Now, create the table's field. add ID, FirstName ,LastName ,UserName and Password ,

As shown in the picture below :



None of the fields accept the null, therefore I left all of the field definition as unchecked.

Now it needs to make ID field of this table as a PrimaryKey (every table should have a field as the primary key as Identity of every record )


Then make the ID field's  Identity property as yes :



Now, save (ctrl+s) your table and name it Users, you can see your table in object explorer after refreshing, like this :


Now, let's insert some data to our table, to do this right click on the table and select Edit to top 200 rows to Add, go thought picture below :


Open the table and insert the data, like this :


Now to writing CRUD(create, read, Update and delete) operation, you need to create a query, to do this click on the new query and begin writing the query:


First, create how to read from the table with a query language, type code below in query you just created and run(execute) it :


Select *
from Users


The picture shows the details!


Run another select query to select just UserName and Password, like this :


Select UserName,Password
from Users


Run the query to see the result, looks like this one, yeah?

You just made two select query, rewrite these query and run them and see the result :


Select UserName,Password
from Users
where (ID=2)


Note: you can use Equal(=), greater than(>), less than(<), greater than and equal (> =), less than, equal (<=)  operand in your where condition.


Select UserName,Password
from Users
where (ID between 1 And 2)
Select FirstName, UserName, Password
from Users
where (ID IN (1,2,3,4))
Select *
from Users
where ID <> 1

Select *
from Users
where (UserName='Ehsan')


LIKE keyword, try this one :


Select *
from Users
where (FirstName LIKE 'Ehsan')


You can order the query by "order by"


select * from Users
order by "LastName"


There are lots of SQL keyword that you can test them 

Top: returns the number of the records, for example, if you want to return 3 number of record in a table you can use TOP key work, see the example


         Select TOP 3 from Users


Between: used after where clause to customize a select list, for example, try this one


Select Top 5 from Users
Where ID between 1 and 3 


Alias: you can name a column or table by alias keyword, like below


Select Top 3 from Users
Where ID between 1 and 3
As TopBest 


SQL has some built-in function, you also can use them in your queries, take a look at this :


Select *
from Users
where (len(FirstName)>4) AND (Len(LastName)<6)


Len: is a function then returns the length!

Deleting rows in a table one by one could cause some performance issues when you want to completely clear the table, in this case, you can use truncate to clear the table. Notice that truncates doesn't log anything! Look at the syntax :


Truncate table Users 


you can although create the table in SQL server not only by the wizard, but with code with the syntax :


Create Table Users
UserId   int,
UserName varchar(30),
Password  varchar(20),
CreationDate datetime,
phoneNumber   int  null


So, if you remember we created the table data by wizard Not by query now create another query to add data ( by the way every time after creating query save it to use it in next steps), type this code in your new query :


Insert into Users
values ('randy','colson','randy','454574')


To see the inserted row in the Users table, right click on Select top 1000 rows :


So let's dig in the Update operation. I want to Update the FirstName and UserName of the last record I added with Id=3, to do this :


Update Users
Set FirstName='michael', UserName='michaelAccount'
where ID='4'


And the result of this query :


Delete a row from the table is pretty simple, for example :


Delete from Users where ID=1


As you can see in deleting and updating a table content you should have the table's Id.Y ou saw the simple CRUD operation.

Category: Data

Tags: Sql Server

comments powered by Disqus