Frequent problem of Database Management is How to prevent duplicate records from the retrieved result when you call a SQL select statement.

SQL Query Example to eliminate duplicate results:
First SQL query to get all rows including duplicates:

use pubs  -- this statement is used to select pubs database of SQL Server 2000

select stor_id from sales

Results:

SQL query excluding duplicate records using DISTINCT keyword:

select DISTINCT stor_id from sales

Results:

Row Selection (using WHERE clause) and Search Conditions

WHERE clause is used to select the records those pass the search condition. SQL query includes the following records when search condition is applied:

• If search condition returns true the records is included into the result.
  
  
• If search condition returns false then the record is excluded from the result.
  
  
• NULL values are also excluded from the result.

Five Basic Search Conditions:

1. Comparison Test: Compares the record with specified comparison operators such as =, >, <, <>, <= and >= to filter them accordingly.
   
   
2. NULL Value Test: It checks the value of column whether it is NULL or not.
   
   
3. Set Member Test: Compare the retrieved value with set members and includes it in the results if it passes the condition e.g. {10, 20, 30, 40}.
   
   
4. Range Test: Checks whether the returned value falls between the specified ranges e.g. BETWEEN 1000 and 3000.
   
   
5. Pattern Matching: Pattern matching test works on string data and compares the retrieved string whether it starts with letter ‘S’ or its second character is ‘a’.

Comparison Test

(= operator) returns only those rows where right hand side value matched the column value.
Example:
Select * from sales where qty=5

Result:

(> operator) returns only those rows where column value is greater than right hand side value. (Excluding right hand side value)
Example:
Select * from sales where qty>5

Result:

(< operator) returns those results where column value is less than right hand side value. (Excluding right hand side value)
Example:
Select * from sales where qty < 10

Result:

(<= operator) returns results having value less than right hand side value but including right hand side value

(>= operator) returns results having value greater than right hand side value but including right hand side value

(<> operator) works as not equal to operator. It returns results having value not equal to right hand side value e.g.
Select * from sales where qty <> 10

NULL Value Test
This test determines whether a given value is NULL or not.
Example:
Select * from sales where title_id IS NULL

Or

Select * from sales where title_id IS NOT NULL

Set Member Test
Member Test checks for the resultant value whether it exists in the set or not.
Example:
Select * from sales where qty in (3, 5, 10)

Result:

Range Test
It compares the retrieved value if it exists between the specified ranges. BETWEEN keyword is used along with AND to specify the ranges which is equivalent to (>=) AND (=<) range.
Example:
Select * from sales where qty BETWEEN 20 AND 50

Result:

Pattern Matching Test
Pattern matching is done by using LIKE keyword along with wildcard characters:

1.  ‘%’ percentage symbol is used to match the string.
   
   
2. ‘_’ underscore symbol is used to match the individual character at the position where ‘_’ is placed.

Example: Returns names starting with “P” letter.

use pubs
select fname from employee where fname like 'P%'

Result:

Example:
Select fname from employee where fname like 'Pau_'

Result:

Example: returns names starting with any character having 2nd & 3rd characters “au” ending with any string.

Select fname from employee where fname like '_au%'

Results:

Pattern Matching with character search range
[] braces are used to specify characters or ranges of characters.

Example:
Select fname from employee where fname like '[h-j]%'

Result:

The table shows the use of wildcards enclosed in square brackets.

Also learn to CREATE database table and perform SQL Insert-Update-Delete Commands