Form of basic SQL query in DBMS

The basic form of an SQL query, specifically when retrieving data, is composed of a combination of clauses. The most elementary form of an SQL query for data retrieval can be represented as

SELECT [DISTINCT] column1, column2, ...
FROM tablename
WHERE condition;

Let's break it down:

1. SELECT Clause: This is where you specify the columns you want to retrieve. Use an asterisk (*) to retrieve all columns.
2. FROM Clause: This specifies from which table or tables you want to retrieve the data.
3. WHERE Clause (optional): This allows you to filter the results based on a condition.
4. DISTINCT Clause (optional): is an optional keyword indicating that the answer should not contain duplicates. Normally if we write the SQL without DISTINCT operator then it does not eliminate the duplicates.

Here are the primary components of SQL queries:

• SELECT: Retrieves data from one or more tables.
• FROM: Specifies the table from which you're retrieving the data.
• WHERE: Filters the results based on a condition.
• GROUP BY: Groups rows that have the same values in specified columns.
• HAVING: Filters the result of a GROUP BY.
• ORDER BY: Sorts the results in ascending or descending order.
• JOIN: Combines rows from two or more tables based on related columns.

To provide a more holistic view, here are a few more SQL examples, keeping them as basic as possible:

1. Retrieve all columns from a table:

SELECT * FROM tablename;

2. Retrieve specific columns from a table:

SELECT column1, column2 FROM tablename;

3. Retrieve data with a condition:

SELECT column1, column2 FROM tablename WHERE column1 = 'value';

4. Sort retrieved data:

SELECT column1, column2 FROM tablename ORDER BY column1 ASC;

Regular expressions in the SELECT Command

SQL provides support for pattern matching through the LIKE operator, along with the use of the wild-card symbols.

Regular expressions: is a sequence of characters that define a search pattern, mainly for use in pattern matching with strings, or string matching.

Examples:
Finds Names that start or ends with "a“
Finds names that start with "a" and are at least 3 characters in length.

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

wild-card: There are two primary wildcards used in conjunction with the `LIKE` operator
percent sign (%) Represents zero, one, or multiple characters
underscore sign(_) Represents a single character

Here's a breakdown of how you can use these wildcards with the `LIKE` operator:

Using `%` Wildcard

SELECT column_name
    FROM table_name
    WHERE column_name LIKE 'pattern%';

For example, to find all customers whose names start with "Ma":

SELECT FirstName
    FROM Customers
    WHERE FirstName LIKE 'Ma%';

SELECT column_name
    FROM table_name
    WHERE column_name LIKE '%pattern';

For instance, to find all products that end with "ing":

SELECT ProductName
    FROM Products
    WHERE ProductName LIKE '%ing';

SELECT column_name
    FROM table_name
    WHERE column_name LIKE '%pattern%';

Example, to find all books that have the word "life" anywhere in the title:

SELECT BookTitle
    FROM Books
    WHERE BookTitle LIKE '%life%';

Using `_` Wildcard

SELECT column_name
    FROM table_name
    WHERE column_name LIKE 'p_ttern';

For instance, if you're looking for a five-letter word where you know the first letter is "h" and the third letter is "l", you could use:

SELECT Word
    FROM Words
    WHERE Word LIKE 'h_l__';

Combining `%` and `_`

You can use both wildcards in the same pattern. For example, to find any value that starts with "A", followed by two characters, and then "o":

SELECT column_name
FROM table_name
WHERE column_name LIKE 'A__o%';

Keep in mind that the actual symbols used for wildcards might vary depending on the database system. For example, in SQL Server, the wildcard for a single character is `[?]` instead of `_`. Always refer to the specific documentation of the database you're working with.