SQL
LIKE Operator

LIKE operator is used to search a Matching pattern in a column it will returns the matching values.

Like I am searching for a student name or city name but I forget the exact spellings of that city or student in such kind of cases LIKE operator is very helpful.

Operators of LIKE

Below are some most important and basic commands of SQL.

OperatorDescription
WHERE StudentName LIKE ‘a%’Finds any values that start with “a”
WHERE StudentName LIKE ‘%a’Finds any values that end with “a”
WHERE StudentName LIKE ‘%or%’Finds any values that have “or” in any position
WHERE StudentName LIKE ‘_r%’Finds any values that have “r” in the second position
WHERE StudentName LIKE ‘a_%_%’Finds any values that start with “a” and are at least 3 characters in length
WHERE StudentName LIKE ‘a%o’Finds any values that start with “a” and ends with “o”
WHERE StudentName LIKE ‘%a’Finds any values that end with “a”

Examples

This example returns us all the student names that ends with a.


	SELECT * FROM student
	WHERE StudentName LIKE '%a';

This example returns us all the student names that starts with a.


	SELECT * FROM student
	WHERE StudentName  LIKE 'a%';

This example returns us all the student names that starts with s and ends with a.


	SELECT * FROM student
	WHERE StudentName  LIKE 's%a';


SQL
COUNT(), AVG() and SUM() Function

COUNT() Function

COUNT function return number of rows base on given criteria.

Syntax


    SELECT COUNT(column_name)
    FROM table_name
    WHERE condition;

Example

This example will return you the number of records in student table.


    SELECT COUNT(studentId)
    FROM student;

AVG() Function

AVG function returns average value of a numeric column.

Syntax


    SELECT AVG(column_name)
    FROM table_name
    WHERE condition;

Example

This query will returns you the average fee of the students from student table.


    SELECT AVG(Fee)
    FROM student;

SUM() Function

SUM function returns total sum a numeric column.

Syntax


    SELECT SUM(column_name)
    FROM table_name
    WHERE condition;

Example

This query will returns you the total fee of the students from student table.


    SELECT SUM(Fee)
    FROM student;


SQL
SELECT MIN MAX Function

MIN() Function

Min function is used to get the minimum value of a column in a table.

Syntax


  SELECT MIN(column_name)
  FROM table_name
  WHERE condition;

Example


  SELECT MIN(fee) AS LargestFee
  FROM Products;

MAX() Function

Max function is used to get the maximum value of a column in a table.

Syntax


  SELECT MAX(column_name)
  FROM table_name
  WHERE condition;

Example


  SELECT MAX(fee) AS LargestFee
  FROM student;


SQL
SELECT TOP Clause

SELECT TOP clause is used to select the top records form a table.

Basic Syntax


	SELECT column_name(s)
	FROM table_name
	WHERE condition
	LIMIT number;

Example

Note: we can use all the three below methods for the same example.

Where we want to select top 3 customers from a customers table.


    SELECT TOP 3 * FROM Customers; 

It works same like the above using LIMIT Clause


    SELECT * FROM Customers
    LIMIT 3; 

It works same like the above using ROWNUM


    SELECT * FROM Customers
    WHERE ROWNUM <= 3;  

If we want to select top records with a specific condition or place


    SELECT * FROM Customers
    WHERE Country='Germany'
    LIMIT 3;


SQL
NULL Values

Null values is field that has no value. If a field in a table is optional, we can insert a new record or update record without adding a value to this field.

IS NULL

Syntax

    SELECT column_names
	FROM table_name
	WHERE column_name IS NULL; 

Example

    SELECT CustomerName, ContactNo, Address
	FROM Customers
	WHERE ContactNo IS NULL;

NOT NULL

Syntax

 
    SELECT column_names
	FROM table_name
	WHERE column_name IS NOT NULL;

Example

 
    SELECT CustomerName, ContactNo, Address
	FROM Customers
	WHERE ContactNo IS NOT NULL;


SQL
ORDER BY Keyword

The order by keyword is use to sort the records in ascending or descending order. We use ASC for ascending and DESC for sorting the records in descending order.

Basic Syntax


    SELECT column1, column2, ...
    FROM table_name
    ORDER BY column1, column2, ... ASC|DESC;

Example for ORDER BY

Note: where we didn’t mention the ASC or DESC it means it will sort the records in ascending order by default.

 
    SELECT * FROM Customers
    ORDER BY Country;

Example for ORDER BY for Several Columns

We can sort several columns in different order. Like we sort a column in ascending order and the other in descending order,


    SELECT * FROM Customers
    ORDER BY Country ASC, CustomerName DESC;