MySQL operator - AND, OR and IN operator

Explain AND, OR and IN operator with examples.

AND : This operator displays a record if both the first condition and the second condition is true.

Example:
SELECT * FROM Employee WHERE FirstName='Jack' AND LastName='Senson'
OR :
This operator displays a record if either the first condition or the second condition is true.

Example:
SELECT * FROM employee WHERE FirstName='janee' OR FirstName=’janet’
IN :
This operator allows to specify multiple values in a WHERE clause.

Example:
SELECT * FROM Persons WHERE LastName IN ('Hansen','Pettersen')

What is the purpose of LIKE operator? Provide examples both using percent sign(%) wildcard and the underscore (_) wildcard.

LIKE : The LIKE operator is used to search for a specified pattern in a column. Example using % : select the employees living in a city that starts with "s" from table employee
SELECT * FROM Persons WHERE City LIKE 's%'

Example using _ : Return all employees whose name is 5 characters long, where the first two characters is 'ja’ and the last two characters are ‘es’
SELECT * FROM employee WHERE employee_name like 'ja_es';

Explain the = and != comparison operators with an example for MySQL Select.

= and != are used to fire Select queries for matching the data in the database.
= comparison operator – Equals to
select fname, lname from employees
where lname=’jones’;

= comparison operator – not Equals to also represented as <>
select fname, lname from employees
where lname != ’jones’;

Explain the <= and >= operators for selecting MySQL data along with an example.

1. = and != are used to fire Select queries for matching the data in the database.
2. To list employees with birth date >= or <= the date specified.
<= comparison operator – Less than Equals to or represented as !>
SELECT fname, lname
FROM employees
WHERE birth_date <= '01/01/95'

<= comparison operator – Greater than Equals to or represented as !
SELECT fname, lname
FROM employees
WHERE birth_date >= '01/01/95'

Explain the use of In and BETWEEN operators along with examples for each.

SQL BETWEEN : The BETWEEN operator is used in a WHERE clause to select a range of data between two values. The values can be numbers, text, or dates.

Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
Example:
SELECT * FROM Employee
WHERE salary
BETWEEN 1000 AND 10000

SQL IN :The IN operator allows you to specify multiple values in a WHERE clause.

Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)
Example:
SELECT * FROM employee
WHERE emp_LastName IN ('james','jones')

State generic SQL syntax and an example of SELECT command along with LIKE clause to fetch data from MySQL table.

LIKE clause is used for pattern matching. % is used to match any string of any length where as _ allows you to match on a single character.

Syntax:
SELECT * FROM table_name
WHERE column_name like 'pattern%';
Example:
SELECT * FROM employee
WHERE emp_name like 'ma%';

Discuss about comparison operator in MySQL. How to use Comparison operator in MySQL?

There are many comparison operators supported in Mysql. Comparison operations result in a value 1 or 0 or NULL. Following are the examples:
= - Equal to
Select 1=0
->0

OR != - Not equal to
Select ‘yes’ != ‘yess’
->1

<= and >= - Less than equal to or greater than equal to
Select 2<=2
1

Discuss about MySQL Regular Expressions with The REGEXP Operator.

REGEXP can be used to match the input characters with the database.

Example:
The following statement retrieves all rows where column employee_name contains the text 1000 (example salary):
Select employee_name
From employee
Where employee_name REGEXP ‘1000’
Order by employee_name

"."
Can be used to match any single character. "|" can be used to match either of the two strings.

Mysql Like operator is used to compare a part of string using the % wild card character. Provide examples

MySql’s LIKE operator is used for pattern matching. When % is used it matches any number of characters.

Example:
SELECT ‘sample’ LIKE ‘%s%m%’; will return 1.
Example: To list employees whose names start with J
SELECT * FROM employee WHERE first_name LIKE ‘J%'");
Example: To list employees with names ending with jones
SELECT * FROM employee WHERE first_name LIKE ‘%Jones'");

MySQL searching using regular expression
MySQL searching - basic character matching using REGEXP in MySQL, matching of several characters at a time
MySQL concatenating fields
MySQL concatenating fields - use of Concat() functions
MySQL text manipulation functions
MySQL text manipulation functions - left(), length(), locate(), lower(), LTRIM(), Right(), RTRIM(), Soundex(), Substring(), Upper()
Post your comment