for comparisons and BETWEEN, IN, LIKE for more complex conditions. The AND, OR and NOT operators allow combining multiple conditions to further refine the results. Aliases can be used to temporarily rename tables or columns to make the SQL more readable. The LIKE operator supports wildcard characters % and _ to match patterns within a string."> for comparisons and BETWEEN, IN, LIKE for more complex conditions. The AND, OR and NOT operators allow combining multiple conditions to further refine the results. Aliases can be used to temporarily rename tables or columns to make the SQL more readable. The LIKE operator supports wildcard characters % and _ to match patterns within a string.">
[go: up one dir, main page]
Scribd
Upload
47 views6 pages

Practical 4

The WHERE clause is used to filter records returned from a SELECT statement. It supports various operators like =, <, > for comparisons and BETWEEN, IN, LIKE for more complex conditions. The AND, OR and NOT operators allow combining multiple conditions to further refine the results. Aliases can be used to temporarily rename tables or columns to make the SQL more readable. The LIKE operator supports wildcard characters % and _ to match patterns within a string.

Uploaded by

MANTHAN JADHAV
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
47 views6 pages

Practical 4

The WHERE clause is used to filter records returned from a SELECT statement. It supports various operators like =, <, > for comparisons and BETWEEN, IN, LIKE for more complex conditions. The AND, OR and NOT operators allow combining multiple conditions to further refine the results. Aliases can be used to temporarily rename tables or columns to make the SQL more readable. The LIKE operator supports wildcard characters % and _ to match patterns within a string.

Uploaded by

MANTHAN JADHAV
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 6

Practical 4

The MySQL WHERE Clause


Example :

SELECT * FROM Customers
WHERE Country = 'Mexico';

SELECT * FROM Customers
WHERE CustomerID = 1;

Operators in The WHERE Clause

Operator Description

= Equal

> Greater than

< Less than

>= Greater than or equal

<= Less than or equal

<> Not equal. Note: In some versions of SQL this operator may be


written as !=

BETWEEN Between a certain range


LIKE Search for a pattern

IN To specify multiple possible values for a column

The MySQL AND, OR and NOT Operators

The WHERE clause can be combined with AND, OR, and NOT operators.

The AND and OR operators are used to filter records based on more than one


condition:

 The AND operator displays a record if all the conditions separated


by AND are TRUE.
 The OR operator displays a record if any of the conditions separated by OR is
TRUE.

The NOT operator displays a record if the condition(s) is NOT TRUE.

AND Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

OR Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

NOT Syntax
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Example:

SELECT * FROM Customers
WHERE Country = 'Germany' AND City = 'Berlin';
SELECT * FROM Customers
WHERE City = 'Berlin' OR City = 'Stuttgart';

SELECT * FROM Customers
WHERE NOT Country = 'Germany';

SELECT * FROM Customers
WHERE Country = 'Germany' AND (City = 'Berlin' OR City = 'Stuttgart');

SELECT * FROM Customers
WHERE NOT Country = 'Germany' AND NOT Country = 'USA';

The MySQL BETWEEN Operator

The BETWEEN operator selects values within a given range. The values can be


numbers, text, or dates.

The BETWEEN operator is inclusive: begin and end values are included.

BETWEEN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

The MySQL IN Operator


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

The IN operator is a shorthand for multiple OR conditions.


IN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');

SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');

MySQL Aliases
Aliases are used to give a table, or a column in a table, a temporary name.

Aliases are often used to make column names more readable.

An alias only exists for the duration of that query.

An alias is created with the AS keyword.

SELECT column_name AS alias_name
FROM table_name;

SELECT column_name(s)
FROM table_name AS alias_name;

SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

SELECT CustomerName AS Customer, ContactName AS "Contact Person"


FROM Customers;

SELECT o.OrderID, o.OrderDate, c.CustomerName


FROM Customers AS c, Orders AS o
WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID;

The MySQL LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in


a column.
There are two wildcards often used in conjunction with the LIKE operator:

 The percent sign (%) represents zero, one, or multiple characters


 The underscore sign (_) represents one, single character

The percent sign and the underscore can also be used in combinations!

SELECT column1, column2, ...


FROM table_name
WHERE columnN LIKE pattern;

LIKE Operator Description

WHERE CustomerName LIKE 'a Finds any values that start with "a"
%'

WHERE CustomerName LIKE Finds any values that end with "a"
'%a'

WHERE CustomerName LIKE Finds any values that have "or" in any position
'%or%'

WHERE CustomerName LIKE '_r Finds any values that have "r" in the second
%' position

WHERE CustomerName LIKE 'a_ Finds any values that start with "a" and are at
%' least 2 characters in length

WHERE CustomerName LIKE Finds any values that start with "a" and are at
'a__%' least 3 characters in length
WHERE ContactName LIKE 'a Finds any values that start with "a" and ends
%o' with "o"

SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

SELECT * FROM Customers
WHERE CustomerName LIKE '%a';

SELECT * FROM Customers
WHERE CustomerName LIKE '%or%';

SELECT * FROM Customers
WHERE CustomerName LIKE '_r%';

SELECT * FROM Customers
WHERE CustomerName LIKE 'a__%';

SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'a%';

You might also like