SQL Where Queries : Day 2 #100Days of SQL

Today, I am going to share about Where clause usages in SQL Query using Microsoft SQL server and AdventureWorks Database.

We Will gonna use "Production.Product" Table in this examples,
In this blogpost will see about where clause with other operators such as and, or, like, not null, null 
Initially we will explore about Simple SQL where clause queries here,
Later I will update more complex SQL where clause queries in future days.

At First we will see the normal Query with resultsets

select * from Production.Product 

-- returned 504 rows of data

In this below Query we will see about Safety Stock level value below 1000

-- where query for SafetyStock level below 1000

select Production.Product.ProductID, 

       Production.Product.Name,

   Production.Product.SafetyStockLevel

from   Production.Product

where  Production.Product.SafetyStockLevel < 1000 

 

In this below query will explore safety stock level values are above 600\

-- where query for SafetyStock level above 600

select Production.Product.ProductID, 

       Production.Product.Name,

   Production.Product.SafetyStockLevel

from   Production.Product

where  Production.Product.SafetyStockLevel > 600 

In this Below query where safety stock level values below 500 or above 800

-- where query for SafetyStock level above 800 or below 500

select Production.Product.ProductID, 

       Production.Product.Name,

   Production.Product.SafetyStockLevel

from   Production.Product

where  Production.Product.SafetyStockLevel < 500 or  

       Production.Product.SafetyStockLevel > 800

In this below query, same safety stock level values are above 100 and below 800 

-- where query for SafetyStock level above 100 and below 800

select Production.Product.ProductID, 

       Production.Product.Name,

   Production.Product.SafetyStockLevel

from   Production.Product

where  Production.Product.SafetyStockLevel > 100 and  

       Production.Product.SafetyStockLevel < 800

In this below query, using like operator in where clause First we will see about starts with [anycharacters]% wildcards

-- where query for SafetyStock Product Number starts with C character like operator

select Production.Product.ProductID, 

       Production.Product.Name,

   Production.Product.ProductNumber,

   Production.Product.SafetyStockLevel

from   Production.Product

where  Production.Product.ProductNumber like 'C%'

In this below query, using like operator in where clause, here we will see about ends with %[anycharacters] wildcards

-- where query for SafetyStock Product Number ends with 7 character like operator

select Production.Product.ProductID, 

       Production.Product.Name,

   Production.Product.ProductNumber,

   Production.Product.SafetyStockLevel

from   Production.Product

where  Production.Product.ProductNumber like '%7'

In below queries, like between, not null, null , and, or operators are utilized

-- where query for SafetyStock Product contains 8 character in a string with like operator anywhere starting, ending, in between

select Production.Product.ProductID, 

       Production.Product.Name,

   Production.Product.ProductNumber,

   Production.Product.SafetyStockLevel

from   Production.Product

where  Production.Product.ProductNumber like '%8%'

-- where query with not null

select * from Production.Product where Color is not null

-- where query with null

select * from Production.Product where Color is null

-- where between and 

select * from Production.Product where ReorderPoint between 10 and 600

-- where between and like operator 

select * from Production.Product where ProductNumber like 'C%' and ReorderPoint between 10 and 600

-- where between or like operator

select * from Production.Product where ProductNumber like 'C%' or ReorderPoint between 10 and 600

 - Will update more complex where clause queries, stay connected