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'");
