Querying Data in SQL Server Part 3

Leave a comment (0) Go to comments

Retrieving Record That Match a Pattern

While retrieving data you might need to view selected rows that match a specific pattern. For example: You want to view the cities of the students whose name begins with ‘P’. You can do this by using LIKE keyword. This keyword is used to search a string by using wildcards like *, %, [] and [^].

The following query retrieves the record form TABLE1 where the STDNAME begins with ‘P’. You need to use ‘%’ wildcard for this query.

SELECT * FROM TABLE1 WHERE STDNAME LIKE 'P%'

Querying Data in SQL Server Part 3 WHere Tutorials TOP NULL How to retrieve data in SQL Server DISTINCT keywords DESC ASC Qerying data in sql using LIKE

You can see that it is only giving me the names that start with ‘P’ only. Now if I put ‘%’ before the ‘P’ then what will happen? Let’s see.

Querying Data in SQL Server Part 3 WHere Tutorials TOP NULL How to retrieve data in SQL Server DISTINCT keywords DESC ASC Qerying data in sql using LIKE


Nothing happened here!!!!!!!!. This is because I don’t have any name in my STDNAME column which ends with ‘P’. This means by putting ‘%’ before the character it will retrieve the data which ends with the specified character. Here in our case it was ‘P’.

Now what would happen if I put ‘P’ in between the ‘%’ wildcard? Let’s see.

SELECT * FROM TABLE1 WHERE STDNAME LIKE '%P%'

Querying Data in SQL Server Part 3 WHere Tutorials TOP NULL How to retrieve data in SQL Server DISTINCT keywords DESC ASC Qerying data in sql using LIKE

You can see that it is showing the names not starting or not ending with ‘P’, but then also it is showing STEPHY & STEPHANY. This means it will show you those results also who have ‘P’ anywhere in the name. Now in this case it doesn’t matter the name starts or end with ‘P’. It will show you the names who have ‘P’ anywhere in the name.

Retrieving Records That Contain Null Values

A NULL value is not zero or not a space, a NULL value tells that the data value for the column is not available. Now if you want to retrieve data having NULL values you can perform this task by using NULL Keyword in your query. For example: I want to check the student name having NULL student ID, the following statement will perform this task.

SELECT * FROM TABLE1 WHERE STDID IS NULL

Querying Data in SQL Server Part 3 WHere Tutorials TOP NULL How to retrieve data in SQL Server DISTINCT keywords DESC ASC Qerying data in sql using LIKE

You can see that It is showing me the name & student ID having NULL value in it. You can retrieve the NULL values data from this query.

Retrieving Records to be Displayed in a Sequence.

You can view your data in order like Ascending or Descending. To view your data in order you can use ORDER BY clause in the select statement. Expression for Ascending is ‘ASC’ & for Descending ‘DESC’.

For example: In order to view the names in Ascending order, I will use the following statement in my case.

SELECT * FROM TABLE1 ORDER BY STDNAME ASC

Querying Data in SQL Server Part 3 WHere Tutorials TOP NULL How to retrieve data in SQL Server DISTINCT keywords DESC ASC Qerying data in sql using LIKE

And same for the Descending order, the only thing I have to change ‘ASC’ to ‘DESC’.

SELECT * FROM TABLE1 ORDER BY STDNAME DESC

Querying Data in SQL Server Part 3 WHere Tutorials TOP NULL How to retrieve data in SQL Server DISTINCT keywords DESC ASC Qerying data in sql using LIKE

You can see that the data retrieve is in the Descending order. Just opposite of the data retrieved in the above query.

Retrieving Records from the TOP of a Table

You can use TOP keyword to retrieve only the first set of rows form the top of a table. This set of records can be either a number of records or a percent of rows that will be returned from a query.

For example: I want to retrieve the TOP 4 data of all the students whose student ID is greater than 104 and between less than 110. The following statement will do this for me.

SELECT TOP 4 * FROM TABLE1 WHERE STDID >104 AND STDID<110

Querying Data in SQL Server Part 3 WHere Tutorials TOP NULL How to retrieve data in SQL Server DISTINCT keywords DESC ASC Qerying data in sql using LIKE

You can see that it is retrieving me the TOP 4 student ID that Is 105, 106, 107, 108. This is not giving me the rest of the ID’s because I want only the TOP 4 values between the specified ranges.

I can also use the Percent Keyword here if want to retrieve the data on the basis of Percentage.

SELECT TOP 100 percent * FROM TABLE1 WHERE STDID >104 AND STDID<110

Querying Data in SQL Server Part 3 WHere Tutorials TOP NULL How to retrieve data in SQL Server DISTINCT keywords DESC ASC Qerying data in sql using LIKE

You can see that it is retrieving me the all the 9 values, this is because I have used 100 Percent that means it will retrieve me all the values from 105 to 109. If I would use 40, 50 or 60 percent the result gets changed. Basically this depends on you which would you prefer the most, Percentage or the other one.

Retrieving Records Without Duplication of Values

Sometime you might need to view the data which do not have any duplicate value in it. You can do this by using DISTINCT keyword. The DISTINCT keyword removes the duplicate rows from the result. For example: I want to view the all the names in STDNAME column without having duplicate values in it. The following statement will do this for me.

SELECT DISTINCT STDNAME FROM TABLE1

Querying Data in SQL Server Part 3 WHere Tutorials TOP NULL How to retrieve data in SQL Server DISTINCT keywords DESC ASC Qerying data in sql using LIKE

You can see that it is showing me all the distinct names in the STDNAME column, and it is not sowing me the duplicate name Like PAUL, HECTOR, LARRY etc. This is the use of DISTINCT keyword.

We will learn more Retrieving Data in SQL Server in our next post. You can check my Previous Post for more information on "Querying Data in SQL Server Part 2".

EOF - Querying Data in SQL Server Part 3, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.