Home / Database / Predicates in SQL Server 2008
sql

Predicates in SQL Server 2008

Predicates in SQL Server 2008

Any operator that is used within the select clause condition in where clause is a predicate. SQL provides several of these predicates to simplify the conditions used in where clause.

  1. IN

In predicate can be used to check the specific value is present in the list of values.

a. Consider an example where we have to find the student from “Harvard” & “Illinois” university then the query using “IN” predicate is as follows.
SELECT * FROM dbo.STUDENT WHERE UNIVERSITY IN (‘Harvard’ , ‘Illinois’);

b. Find the student who joined the University on the specific date (9-MAY-2008).
SELECT * FROM dbo.STUDENT WHERE JOININGDATE IN (‘9-MAY-2008’);

  1. IS NULL

This predicate is used to search the “NULL” values present in the column.

e.g. Find the student whose Date of Birth is not entered in the System.

SELECT * FROM STUDENT WHERE DATEOFBIRTH IS NULL.

  1. LIKE

Like predicate is used to check the similar values present in the given column. Like predicate is used with two characters ‘%'(percent) and ‘_'(underscore). ‘%’ is used to get all the combinations for the search string & ‘_’ is used to get the exact result.

Following examples explains the details about the ‘LIKE’ predicate.

a. Get all student whose names start with ‘A’ character.
SELECT * FROM STUDENT WHERE NAME LIKE ‘A%’

Here result will be all names starting with ‘A’ letter.

b. Get all student whose names contains ‘A’ character.
SELECT * FROM STUDENT WHERE NAME LIKE ‘%A%’

c. Get all student whose last name contains ‘A’ as second character. e.g. Last Name “JARED”.
SELECT * FROM STUDENT WHERE LASTNAME LIKE ‘_A%’

d. Get all the student having “A” character twice in their name.
SELECT * FROM STUDENT WHERE NAME LIKE ‘%A%A%’

  1. BETWEEN & AND

This predicate is used to verify where or not the given value is within a given range.
a. For example we need to find the student having marks in subject between 60 & 80.

SELECT * FROM STUDENT WHERE SUBJECTMARKS BETWEEN 60 AND 80.

b. Get the student count who joined the university on ‘9-MAY-2008′ and ’22-MAY-2015’.

SELECT * FROM STUDENT WHERE JOININGDATE BETWEEN ‘9-MAY-2008′ AND ’22-MAY-2015’.

About Helpservices

About This site (allhelpservices.com) is dedicated to helping other bloggers learn the skills of blogging, wordpress, education, technology, social media sharing , SEO, Electronics, Earn Money From Blogging, Web hosting and web share their own experiences and promote the blogging medium. We started Allhelpservices in August 2015 mainly because we wanted to keep a record of what we wear learning from development and because we wanted to connect with other learners.

Check Also

Database

Database level Queries & Identity functions

Get list of tables in the Database: We can get all the tables in the ...

Leave a Reply