SQL_WHERE

  CLASS XI-XII

(SQL-WHERE-ASSIGNMENTS)

---

SQL - WHERE Clause

The SQL WHERE clause is used to specify a condition while fetching the data from a single table or by joining with multiple tables. If the given condition is satisfied, then only it returns a specific value from the table. You should use the WHERE clause to filter the records and fetching only the necessary records.
The WHERE clause is not only used in the SELECT statement, but it is also used in the UPDATE, DELETE statement, etc.,

Syntax

SELECT column1, column2, columnN FROM table_name WHERE [condition];

Table: Customer

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	MP	4500.00
7	Muffy	24	Indore	10000.00

Example:

To display those record from the table whose age is more than 25.

SELECT * FROM CUSTOMER WHERE AGE>25;

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
5	Hardik	27	Bhopal	8500.00

Comparison Operators

>    greater than   

>=  greater than equal to

<    Less than 

<=  Less than equal to

!=   Not equal to

=    Equal to

Example:

To display names from the table whose address is “Delhi”.

To display salary of those whose age is not 25

Logical Operators

AND: if all the conditions separated by AND are TRUE.

AND Syntax:

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

Table: Customer

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Mumbai	2000.00
2	Khilan	25	Delhi	1500.00
3	kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Delhi	4500.00
7	Muffy	24	Mumbai	10000.00

Q1.    To display the names and age of those customer whose age is more than equal to 25 and address is “Mumbai”.

SELECT NAME,AGE FROM Customer WHERE AGE>=25 AND ADDRESS=”MUMBAI”;

NAME	AGE	ADDRESS
Ramesh	32	Mumbai
Chaitali	25	Mumbai

OR: displays a record if any of the conditions separated by OR is TRUE.

Q1. To display those records whose address is Mumbai or delhi

SELECT * FROM customer WHERE ADDRESS=”Mumbai” OR ADDRESS=”DELHI”;

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Mumbai	2000.00
2	Khilan	25	Delhi	1500.00
4	Chaitali	25	Mumbai	6500.00
6	Komal	22	Delhi	4500.00
7	Muffy	24	Mumbai	10000.00

NOT: The NOT operator reverses the meaning of the logical operator with which it is used.

Q.   To display those whose address is not Mumbai.

SELECT * FROM Customer WHERE NOT ADDRESS =”Mumbai”

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
3	kaushik	23	Kota	2000.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Delhi	4500.00

Q2.    What is the output of the query given?

SELECT NAME FROM Customer WHERE NOT AGE>=25;

SQL NULL Values

A field with a NULL value is a field with no value.

How to Test for NULL Values?

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Mumbai	2000.00
2	Khilan	25	Delhi	1500.00
3	kaushik	23		2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22		4500.00
7	Muffy	24	Mumbai	10000.00

To display those records from the table whose address is empty.

SELECT * FROM Customer WHERE ADDRESS IS NULL;

ID	NAME	AGE	ADDRESS	SALARY
3	kaushik	23		2000.00
6	Komal	22		4500.00

How to display those records whose address column is not empty using is null.

SELECT * FROM Customer WHERE ADDRESS IS NOT NULL;

----OUTPUT-----

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Mumbai	2000.00
2	Khilan	25	Delhi	1500.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
7	Muffy	24	Mumbai	10000.00

How to display the records in a specific range of values.

We can display records in a specific range EX: values from 20 to 45, it means 20,21,22,… upto 45.

There are two methods:

1.    Using AND operator

2.    Using BETWEEN …AND … operator

Table:customer

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Mumbai	2000.00
2	Khilan	25	Delhi	1500.00
3	kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Delhi	4500.00
7	Muffy	24	Mumbai	10000.00

1.    Using AND operator:

Q1.    To display the record from the table customer whose salary is between 3000 and 7000.

SELECT * FROM Customer WHERE SALARY>=3000 AND SALARY<=7000;

2.    Using BETWEEM …. AND …. Operator:

SELECT * FROM Customer WHERE SALARY BETWEEN 3000 AND 7000;

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00
6	Komal	22	Delhi	4500.00

HOW TO DISPLAY THE RECORDS ACCORDING TO MULTIPLE CHOICE.

THERE ARE TWO OPERATORS:

1.    OR OPERATOR

2.    IN OPERATOR.

IF THE TABLE GIVEN BELOW :

 CUSTOMER

CustID	CustName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden

Q.      TO DISPLAY THOSE RECORDS FROM THE CUSTOMER WHOSE COUNTRY IS Germany or Mexico

Ans:

Method 1: using OR operator

SELECT * FROM CUSTOMER WHERE Country=”Germany” OR Country=”Mexico”;

Method 2: IN operator

SELECT * FROM CUSTOMER WHERE Country IN (“Germany”,”Mexico”);

CustID	CustName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico