My Sql tutorials

MYSQL database

Different type of Operators

• Equal to ( = ) you can use it with any type of data types.
• Not Equal to ( <> or != )
• Less than ( < ) typically use it with numeric and date / time data type.
• Greater than ( > )
• Less than equal to ( <= )
• Greater than equal ( >= )

Example -->

Some syntax by using the above operators

SELECT * FROM Student WHERE Sname = 'Vijay';
SELECT * FROM Student WHERE Sname != 'Vijay';
SELECT * FROM Student WHERE Marks < 80;
SELECT * FROM Student WHERE Marks > 80;
SELECT * FROM Student WHERE Marks <= 80;
SELECT * FROM Student WHERE Marks >= 80;

SELECT Statement

• Select command is used to fetch records / data from the table.
• Result of select command - Result set (Row and Column)

Example -->

To fetch all the Column from the table use the following syntax.

SELECT * FROM Student;

Example -->

To fetch the specific Column.

SELECT Column_Name FROM Student;

Example -->

To fetch the multiple Column.

SELECT Column_1,Column_2 FROM Student;

DISTINCT Clause

• Distinct clause is used to remove the duplicate rows from the resultant set.

Example -->

SELECT DISTINCT Branch FROM Student;

Example -->

Distinct clause on multiple Column.

SELECT DISTINCT Fname,Qualification FROM Faculty;

WHERE Clause

• Used to filter data.
• WHERE clause allows to filter data based on certian conditon or expression.

Example -->

Syntax of SELECT command with WHERE clause.

SELECT * FROM Faculty WHERE Fname = 'Vijay';

AND Operator

• AND operator combines two or more boolean expression and return true only if both the expressions evaluate to true.

Example -->

Syntax using of AND oprerator.

SELECT * FROM Student WHERE Branch = 'IT' AND marks < 80;

OR Operator

• OR operator combines two boolean expressions and return true if either conditon is true.

Example -->

Syntax using of OR oprerator.

SELECT * FROM Student WHERE Branch = 'IT' OR Branch = 'ECE';

• Some important queries by using above Syntax

Examples -->

1. Find faculty Id and Name whose Qualification is B.tech.

SELECT Fid,Fname FROM Faculty WHERE Qualification = 'B.tech';

2. Find faculty details whose Qualification not Ph.d

SELECT * FROM Faculty WHERE Qualification != 'Ph.d';

3. Find student name from IT whose marks are less than 80.

SELECT Sname FROM Student WHERE Branch = 'IT' and Marks < 80;

4. Find student names from IT or ECE Branch.

SELECT Sname FROM Student WHERE Branch = 'IT' OR Branch = 'ECE';

IN Operator

• IN operator is used to filter data and allows to determine if the value matches any one of list or Result of subquery.

Example -->

Syntax using of IN oprerator.

SELECT * FROM Student WHERE Branch IN ('ECE','CSE');

NOT IN Operator

• NOT IN operator is used to filter data and allows to determine if the value does not matches any one of the list or Result of subquery.

Example -->

Syntax using of NOT IN oprerator.

SELECT * FROM Student WHERE Branch NOT IN ('ECE','CSE');

BETWEEN Operator

• BETWEEN operator is used to filer data in between specific range of values.
• And it also allow to specify range of values to test.

Example -->

Syntax using of BETWEEN oprerator.

SELECT * FROM Student WHERE Marks BETWEEN 60 and 80;

NOT BETWEEN Operator

• It is opposite to the BETWEEN operator.
• BETWEEN operator is used to filer data which is not between the specific range of given values.

Example -->

Syntax using of NOT BETWEEN oprerator.
Below query will return all the rows which is not lies in between 60 to 80.

SELECT * FROM Student WHERE Marks NOT BETWEEN 60 and 80;

LIMIT Clause

• LIMIT Clause is used to constrain the number of rows return by the select Statement.

Example -->

Syntax using of LIMIT Clause.

• Below query will return 4 sequential rows starting from 0 index ( Its means 0 to 3).
• 0 is the starting index and 4 is the number of rows in the below query.

SELECT * FROM Student LIMIT 0,4;

IS NULL or IS NOT NULL Operator

• IS NULL operator is used to check whether the value is NULL or not.
• IS NOT NULL is opposite to the IS NULL Operator.

Example -->

SELECT * FROM Student WHERE Branch IS NULL;


SELECT * FROM Student WHERE Branch IS NOT NULL;

LIKE Operator

• LIKE Operator is used to select rows / data based on patern matching.
• Following two Wild characters are used with the LIKE operator.
  • Percentage ( % ) - It matches any string of zero or more characters.
  • Underscore ( _ ) - It matches exactly one single character.

• Some important queries by using above Syntax

Examples -->

1. Find faculty details whose name starts with letter 'A' .

SELECT * FROM Faculty WHERE Fname LIKE 'A%';

2. Find faculty details whose name ends with letter 'N'

SELECT * FROM Faculty WHERE Fname LIKE '%N';

3. Find student marks whose name contains letter 'A' as second letter.

SELECT Sname,Marks FROM Student WHERE Sname LIKE '_A%';

4. Find student marks whose name starts with letter 'P' and contains only 5 letter in the name.

SELECT Sname,Marks FROM Student WHERE Sname LIKE 'P____';

5. Find faculty details whose name not ends with letter 'N'.

SELECT * FROM Faculty WHERE Fname NOT LIKE '%N';

6. Find student details whose name does not contians second letter 'A'.

SELECT * FROM Student WHERE Sname NOT LIKE '_A%';

ORDER BY Clause

• Order By clause

Example -->

Syntax using of BETWEEN oprerator.

SELECT * FROM Student WHERE Marks BETWEEN 60 and 80;