Question

List the SQL operations and explain range searching operations between and pattern matching operator ‘like’ with example.

Answer 1

Of course. Here is a comprehensive overview of SQL operations, followed by a detailed explanation of the BETWEEN and LIKE operators with examples.

---

List of SQL Operations (Categories)

SQL commands are divided into several categories, often called sublanguages, based on their function. The main categories of SQL operations are:

1. Data Definition Language (DDL):
   Used to define and manage the structure of database objects. These commands are auto-committed, meaning their changes are permanent.
   Examples: CREATE (to create tables), ALTER (to modify tables), DROP (to delete tables), TRUNCATE (to empty tables).

2. Data Manipulation Language (DML):
   Used to manage the data within the database objects. These are the most common SQL commands.
   Examples: SELECT (to retrieve data), INSERT (to add new data), UPDATE (to modify existing data), DELETE (to remove data).

3. Data Control Language (DCL):
   Used to manage user access and permissions to the database.
   Examples: GRANT (to give permissions), REVOKE (to take away permissions).

4. Transaction Control Language (TCL):
   Used to manage transactions in the database, ensuring data integrity.
   Examples: COMMIT (to save a transaction), ROLLBACK (to undo a transaction), SAVEPOINT (to set a point to roll back to).

---

Range Searching: The BETWEEN Operator

The BETWEEN operator is used in a WHERE clause to select values that fall within a specified range.

• Purpose: To simplify queries that search for a value between two other values. It is a more readable and concise alternative to using two separate comparisons with AND (e.g., price >= 50 AND price <= 100).
• Key Feature: The BETWEEN operator is inclusive, meaning it includes the start and end values in the range.

Syntax

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

Example

Let's use a sample Products table to find products within a certain price range.

Products Table:
| ProductID | ProductName | Price |
| :--- | :--- | :--- |
| 1 | Keyboard | 75.00 |
| 2 | Mouse | 25.00 |
| 3 | Monitor | 200.00 |
| 4 | USB Cable | 10.00 |
| 5 | Webcam | 100.00 |

Goal: Find all products with a price between $75 and $200, inclusive.

Query:
`sql
SELECT ProductName, Price
FROM Products
WHERE Price BETWEEN 75.00 AND 200.00;
`

Result:
The query will return the Keyboard, Monitor, and Webcam, as their prices fall within or on the boundaries of the specified range.
| ProductName | Price |
| :--- | :--- |
| Keyboard | 75.00 |
| Monitor | 200.00 |
| Webcam | 100.00 |

---

Pattern Matching: The LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column's text data.

• Purpose: To perform flexible string searches where you don't know the exact value. This is essential for features like search bars in applications.
• Key Feature: LIKE uses two special wildcard characters to define the search pattern.

Wildcard Characters

1. The percent sign (%): Represents zero, one, or multiple characters.
2. The underscore (_): Represents exactly one single character.

Syntax

`sql
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE 'pattern';
`

Example

Let's use a sample Customers table to perform various pattern searches.

Customers Table:
| CustomerID | CustomerName | City |
| :--- | :--- | :--- |
| 101 | John Smith | New York |
| 102 | Jane Doe | London |
| 103 | Robert Jones | Los Angeles |
| 104 | Peter Pan | London |

Example 1: Find names that start with 'J'
The pattern 'J%' means "starts with the letter J, followed by any number of characters."

Query:
`sql
SELECT CustomerName
FROM Customers
WHERE CustomerName LIKE 'J%';
`
Result:
| CustomerName |
| :--- |
| John Smith |
| Jane Doe |

Example 2: Find names that end with 's'
The pattern '%s' means "any number of characters, ending with the letter s."

Query:
`sql
SELECT CustomerName
FROM Customers
WHERE CustomerName LIKE '%s';
`
Result:
| CustomerName |
| :--- |
| Robert Jones |

Example 3: Find names that contain 'an'
The pattern '%an%' means "any number of characters, followed by 'an', followed by any number of characters."

Query:
`sql
SELECT CustomerName
FROM Customers
WHERE CustomerName LIKE '%an%';
`
Result:
| CustomerName |
| :--- |
| Jane Doe |
| Peter Pan |

Example 4: Find names that are four letters long and start with 'J'
The pattern 'J___' uses three underscores, each representing a single character.

Query:
`sql
SELECT CustomerName
FROM Customers
WHERE CustomerName LIKE 'J___';
`
Result:
This would match "John" but not "Jane" because "Jane" has four letters, but the query is looking for a four-letter name, and John Smith has more than 4 letters. If there was a customer named "John", it would return that record. In our current table, no records are returned. Let's adjust to find cities that are 6 letters long and end with 'on'.

Query for a city that is 6 letters long and ends with 'on': LIKE '____on'
`sql
SELECT City
FROM Customers
WHERE City LIKE '____on';
`
Result:
| City |
| :--- |
| London |
| London |