CP-2K16 DBMS LAB # 4
SQL Wildcards & Operators
Engr. Shahid Ali Bhutta
UNIVERSITY OF ENGINEERING & TECHNOLOGY TAXILA
[Type here]
LAB # 04
SQL Wildcards & Operators
Lab Objective:
To understand SQL wild cards and the operators used in SQL.
SQL Wildcards
SQL wildcards can be used when searching for data in a database.
SQL wildcards can substitute for one or more characters when searching for data in a database.
SQL wildcards must be used with the SQL LIKE operator.
With SQL, the following wildcards can be used:
Wildcard Description
% A substitute for zero or more characters
_ A substitute for exactly one character
[charlist] Any single character in charlist
[^charlist] Any single character not in charlist
or
[!charlist]
SQL Wildcard Examples
We have the following "Persons" table:
Lab Instructor: Engr. Shahid Ali Bhutta SQL Wildcards & Opperators
Using the % Wildcard
Now we want to select the persons living in a city that starts with "sa" from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE City LIKE 'sa%'
The result-set will look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
Next, we want to select the persons living in a city that contains the pattern "nes" from the
"Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE City LIKE '%dne%'
Lab Instructor: Engr. Shahid Ali Bhutta SQL Wildcards & Opperators
The result-set will look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
Using the _ Wildcard
Now we want to select the persons with a first name that starts with any character, followed by
"la" from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE FirstName LIKE '_ove'
The result-set will look like this:
Using the [charlist] Wildcard
Now we want to select the persons with a last name that starts with "b" or "s" or "p" from the
"Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE LastName LIKE '[bsp]%'
Lab Instructor: Engr. Shahid Ali Bhutta SQL Wildcards & Opperators
The result-set will look like this:
P_Id LastName FirstName Address City
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Next, we want to select the persons with a last name that do not start with "b" or "s" or "p" from
the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE LastName NOT LIKE '[bsp]%'
The result-set will look like this:
SQL IN Operator
The IN operator allows you to specify multiple values in a WHERE clause.
SQL IN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)
Lab Instructor: Engr. Shahid Ali Bhutta SQL Wildcards & Opperators
IN Operator Example
The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Now we want to select the persons with a last name equal to "Hansen" or "Pettersen" from the
table above.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE LastName IN ('Hansen','Pettersen')
The result-set will look like this:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Lab Instructor: Engr. Shahid Ali Bhutta SQL Wildcards & Opperators
SQL BETWEENOperator
The BETWEEN operator is used in a WHERE clause to select a range of data between two
values.
The BETWEEN operator selects a range of data between two values. The values can be numbers,
text, or dates.
SQL BETWEEN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
BETWEEN Operator Example
Now we want to select the persons with P_ID betweek 2 and 4" from the persons table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE P_ID BETWEEN 2 AND 4;
The result-set will look like this:
Note: The BETWEEN operator is treated differently in different databases.
In some databases, persons with the P_ID of 2 and 4 will not be listed, because the BETWEEN
operator only selects fields that are between and excluding the test values).
In other databases, persons with id 2 and 4 will be listed, because the BETWEEN operator
selects fields that are between and including the test values).
Lab Instructor: Engr. Shahid Ali Bhutta SQL Wildcards & Opperators
And in other databases, persons with id 2 will be listed, but id=4 will not be listed (like the
example above), because the BETWEEN operator selects fields between the test values,
including the first test value and excluding the last test value.
Therefore: Check how your database treats the BETWEEN operator.
Example 2
To display the persons outside the range in the previous example, use NOT BETWEEN:
SELECT * FROM Persons
WHERE LastName
NOT BETWEEN 'Hansen' AND 'Pettersen'
The result-set will look like this:
P_Id LastName FirstName Address City
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Lab Instructor: Engr. Shahid Ali Bhutta SQL Wildcards & Opperators
Lab Tasks:
1: Consider the following table “Customers”:
Create the above table and do the following.
• Write an SQL statement that selects all Customers with a Country starting with the letter
“s”.
• Write an SQL statement that selects all Customers with a Contact Name ending with the
letter “s”.
• Write an SQL statement that selects all Customers with a City containing the pattern
“ndo”.
• Write an SQL statement that selects all Customers with a City not containing the pattern
“ndo”.
• Write an SQL statement that selects the two first Customers from table who belong to
“Germany” or “Sweden”.
• Write an SQL statement that selects all Customers with a City of "Paris" or "London"
without using ‘OR’ operator.
Lab Instructor: Engr. Shahid Ali Bhutta SQL Wildcards & Opperators
2. Consider the following table “Products”
Create the above table and do the following.
• Write an SQL statement that selects all products with a price from 10 to 20.
• Write an SQL statement that selects all products with a price from 20 to 30.
• Write an SQL statement that selects all products with a price from 10 to 22 but products
with a CategoryIDof 1,2, or 3 should not be displayed.
• Write an SQL statement that selects all products with a ProductName beginning with
any of the letter not between 'C' and 'M'.
Lab Instructor: Engr. Shahid Ali Bhutta SQL Wildcards & Opperators
