Querying Data in SQL Server Part 2

Leave a comment (0) Go to comments

Calculating column values

If you want to do calculations on a column like add, subtract, multiply, modulus or division. You can use the arithmetic operators like (+, -, /, *, %). Now in our table TABLE1 we have one column STDID containing integer values & I want to add all the values by 20. The command would be as follows

SELECT STDID, RESULT=20+STDID FROM TABLE1

Querying Data in SQL Server Part 2 Tutorials Retrieving selected rows using comparison operators Records That Contain Values in a Given Range Records that Contain Any Value from a given Set of Values OR NOT How to query data in SQL SERVER Calculating column values AND

In the above example I have use STDID column to view for better understanding & name the output as RESULT, in the result column you can see that all the values corresponding to STDID column are added by 20. I can do subtraction, multiplication, and division as per my requirement, the only thing I have to do is to change the arithmetic operator in the above command.

SELECT STDID, RESULT=20-STDID FROM TABLE1
SELECT STDID, RESULT=20/STDID FROM TABLE1
SELECT STDID, RESULT=20*STDID FROM TABLE1
SELECT STDID, RESULT=20%STDID FROM TABLE1


Retrieving selected rows using comparison operators

Suppose you want to query a data on selected ROWS only. You can use comparison operators as per your requirements

Operators

Description

=

Equal to

>

Greater than

<

Less than

>=

Greater than or equal to

<=

Less than or equal to

<>

Not equal to

!=

Not equal to

!<

Not less than

!>

Not greater than

Now I want to query a statement in which I want the STDID should be greater than 106 in table TABLE1.

SELECT STDNAME,STDID FROM TABLE1 WHERE STDID > 106

Querying Data in SQL Server Part 2 Tutorials Retrieving selected rows using comparison operators Records That Contain Values in a Given Range Records that Contain Any Value from a given Set of Values OR NOT How to query data in SQL SERVER Calculating column values AND

Now you can do query using comparison operators as per your requirement. I am giving you below few examples that you can use for practice.

SELECT STDNAME,STDID FROM TABLE1 WHERE STDID < 106
SELECT STDNAME,STDID FROM TABLE1 WHERE STDID >= 106
SELECT STDNAME,STDID FROM TABLE1 WHERE STDID <= 106
SELECT STDNAME,STDID FROM TABLE1 WHERE STDID = 106
SELECT STDNAME,STDID FROM TABLE1 WHERE STDID <> 106
SELECT STDNAME,STDID FROM TABLE1 WHERE STDID != 106
SELECT STDNAME,STDID FROM TABLE1 WHERE STDID !< 106
SELECT STDNAME,STDID FROM TABLE1 WHERE STDID !> 106

Retrieving Records That Match One or More Conditions

In the above topic we used comparison operator & now we will use the Logical operators like NOT, AND, OR. Suppose you want that your query should match you requirements, so you can use the logical operators. I will show you how to use these operators as your requirements.

OR: Returns a true value when at least one condition is true. The following command retrieves records from TABLE1.

SELECT * FROM TABLE1 WHERE STDNAME ='PAUL' OR STDNAME= 'HARRY'

Querying Data in SQL Server Part 2 Tutorials Retrieving selected rows using comparison operators Records That Contain Values in a Given Range Records that Contain Any Value from a given Set of Values OR NOT How to query data in SQL SERVER Calculating column values AND

You can see that it is only showing me only two names PAUL & HARRY. The command would be as follows for the above statement.

AND. It is used to join two conditions & return a true value when both the conditions are true. For example: I want to retrieve a student name “PAUL” whose address is “WASHINGTON”. In this case I want that my both condition should be true, and the statement for the above query would be as follows:

Querying Data in SQL Server Part 2 Tutorials Retrieving selected rows using comparison operators Records That Contain Values in a Given Range Records that Contain Any Value from a given Set of Values OR NOT How to query data in SQL SERVER Calculating column values AND

As you can see it is giving me the result in which both the conditions are true.

NOT. Reverse the result of the search condition. For example: I don’t want the records of Hector & Paul, so I will use the NOT condition with OR condition. I am using OR because if in the query both conditions or only one condition is true, then to it will give me the value whether it is single value or double value.

SELECT * FROM TABLE1 WHERE STDNAME ='HECTOR' OR NOT  STDNAME='PAUL'

Querying Data in SQL Server Part 2 Tutorials Retrieving selected rows using comparison operators Records That Contain Values in a Given Range Records that Contain Any Value from a given Set of Values OR NOT How to query data in SQL SERVER Calculating column values AND

You can see that it is showing me the records of everyone in the table except PAUL. The OR NOT keyword is not giving me the desired result, it is also retrieving me one value in the condition with the overall result, whereas I don’t want both the desired names in my result. This is because the above query retrieves all the rows, except the rows that match the condition specified after the NOT condition expression.

Retrieving Records That Contain Values in a Given Range

Now suppose I want to retrieve data within or not within a specific range. Here in this case I have two conditions that is BETWEEN & NOT BETWEEN. If I want to retrieve data within the specific range, I will use BETWEEN condition. And if I don’t want to retrieve data within the specific range I will use NOT BETWEEN condition. For example I want the names of students within the student id from 104 to 110.

SELECT STDNAME,STDID FROM TABLE1 WHERE STDID BETWEEN 104 AND 110

Querying Data in SQL Server Part 2 Tutorials Retrieving selected rows using comparison operators Records That Contain Values in a Given Range Records that Contain Any Value from a given Set of Values OR NOT How to query data in SQL SERVER Calculating column values AND

You can see that it is giving me the names whose student id is between 104 & 110.

Now if you don’t want the student names between student id 104 to 110. The following would be the command for the same query.

Querying Data in SQL Server Part 2 Tutorials Retrieving selected rows using comparison operators Records That Contain Values in a Given Range Records that Contain Any Value from a given Set of Values OR NOT How to query data in SQL SERVER Calculating column values AND

You can see that it is giving me the names whose student id is not between 104 & 110.

Retrieving Records that Contain Any Value from a given Set of Values

Sometimes, you want to retrieve data after specifying a set of values to check whether the specified value matches any data of the table. This type of operation is performed by using the IN & NOT IN keyword. For example: I want to retrieve details of only PAUL & HECTOR. So what I will do is that, I will use the IN keyword.

SELECT * FROM TABLE1 WHERE STDNAME IN ('PAUL','HECTOR')

Querying Data in SQL Server Part 2 Tutorials Retrieving selected rows using comparison operators Records That Contain Values in a Given Range Records that Contain Any Value from a given Set of Values OR NOT How to query data in SQL SERVER Calculating column values AND

You can also use the OR keyword here instead of”IN“in this case & the result would be the same. Selecting the keyword depends on your query & desired output. If you use

Querying Data in SQL Server Part 2 Tutorials Retrieving selected rows using comparison operators Records That Contain Values in a Given Range Records that Contain Any Value from a given Set of Values OR NOT How to query data in SQL SERVER Calculating column values AND

And now if I don’t want the details of both HECTOR & PAUL, I will use the NOT IN keyword. This will return me the perfect retrieving of data as compare to OR NOT keyword in the above queries.

SELECT * FROM TABLE1 WHERE STDNAME NOT IN ('PAUL','HECTOR')

Querying Data in SQL Server Part 2 Tutorials Retrieving selected rows using comparison operators Records That Contain Values in a Given Range Records that Contain Any Value from a given Set of Values OR NOT How to query data in SQL SERVER Calculating column values AND

We will learn more ways of retrieving data in our next post. You can check my Previous Post for more information on "How to query data in SQL SERVER".

EOF - Querying Data in SQL Server Part 2, 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.