SQL: Subqueries with Example

In SQL, a subquery, also known as a nested query, is a query that is embedded within another query. A subquery is used to retrieve data based on the results of another query, allowing you to break down complex problems into smaller, more manageable parts. Here’s an explanation of subqueries with an example:

Consider two tables: “customers” and “orders”. Here’s a sample data:

“customers” table:

+----+-------------+
| id | customer    |
+----+-------------+
| 1  | John Doe    |
| 2  | Jane Smith  |
| 3  | Alex Johnson|
+----+-------------+

“orders” table:

+----+-----------+--------+
| id | order_date| amount |
+----+-----------+--------+
| 1  | 2021-01-01| 100    |
| 1  | 2021-02-01| 200    |
| 2  | 2021-01-15| 150    |
| 3  | 2021-02-10| 300    |
+----+-----------+--------+

Let’s say we want to retrieve the customers who have made at least one order. We can use a subquery to accomplish this:

SELECT id, customer
FROM customers
WHERE id IN (SELECT DISTINCT id FROM orders);

Output:

+----+-------------+
| id | customer    |
+----+-------------+
| 1  | John Doe    |
| 2  | Jane Smith  |
| 3  | Alex Johnson|
+----+-------------+

In this example, the subquery (SELECT DISTINCT id FROM orders) is embedded within the main query. The subquery retrieves the distinct customer IDs from the “orders” table. The main query then selects the matching customer IDs and their corresponding names from the “customers” table using the WHERE id IN clause.

Subqueries can be used in various parts of a SQL statement, such as the SELECT, FROM, WHERE HAVING, or JOIN clauses, depending on the specific requirement. They allow you to break down complex problems into smaller steps, improve query flexibility, and retrieve data based on dynamic conditions.

It’s worth noting that subqueries can return single or multiple values, and they can be correlated or non-correlated. Correlated subqueries are those that reference columns from the outer query, while non-correlated subqueries are standalone queries that don’t rely on the outer query.

By using subqueries effectively, you can perform advanced data retrieval and analysis, allowing you to solve complex problems and obtain specific insights from your database.

SQL: LIKE Operator

In SQL, the LIKE operator is used to perform pattern matching on text data. It allows you to retrieve rows from a table that match a specific pattern defined using wildcard characters. Here’s an explanation of the LIKE operator with an example:

Consider a table named “employees” with a column named “first_name”. Here’s a sample data:

+------------+
| first_name |
+------------+
| John       |
| Jane       |
| Alex       |
| Lisa       |
+------------+

To retrieve rows from this table where the “first_name” starts with the letter “J”, you can use the LIKE operator in your SQL query. Here’s an example:

SELECT * FROM employees WHERE first_name LIKE 'J%';

Output:

+------------+
| first_name |
+------------+
| John       |
| Jane       |
+------------+

In this example, the SELECT statement uses the LIKE operator to match rows where the “first_name” column starts with the letter ‘J’. The ‘%’ character is a wildcard that represents any number of characters. Therefore, ‘J%’ matches any string that starts with ‘J’ followed by any number of characters.

The LIKE operator provides flexibility in searching for patterns within text data. Some commonly used wildcard characters are:

‘%’ – Matches any sequence of characters (including zero characters).
‘_’ – Matches any single character.
‘[ ]’ – Matches any single character within the specified range or set.
‘[^ ]’ – Matches any single character not within the specified range or set.

Here are a few more examples of using the LIKE operator:

Searching for names that end with ‘a’:

   SELECT * FROM employees WHERE first_name LIKE '%a';

Searching for names that have ‘o’ as the second character:

   SELECT * FROM employees WHERE first_name LIKE '_o%';

Searching for names that start with ‘A’ or ‘L’:

   SELECT * FROM employees WHERE first_name LIKE 'A%' OR first_name LIKE 'L%';

It’s important to note that the LIKE operator performs case-sensitive matching unless specified otherwise based on the collation settings of your database.

The LIKE operator is a powerful tool for performing pattern matching on text data in SQL queries. It allows you to retrieve rows that match specific patterns, making it useful for searching, filtering, and data analysis tasks.

SQL: MIN Function with Example

In SQL, the MIN function is used to retrieve the minimum value from a column of numeric or date/time data types. It returns the smallest value within the specified column. Here’s an explanation of the MIN function with an example:

Consider a table named “products” with a column named “price”. Here’s a sample data:

+-------+
| price |
+-------+
| 10    |
| 20    |
| 15    |
| 30    |
+-------+

To retrieve the minimum price from the “products” table, you can use the MIN function in your SQL query. Here’s an example:

SELECT MIN(price) AS min_price FROM products;

Output:

+-----------+
| min_price |
+-----------+
| 10        |
+-----------+

In this example, the SELECT statement uses the MIN function to retrieve the smallest value from the “price” column. The resulting minimum price, 10, is displayed as “min_price” in the output.

The MIN function can be used with numeric columns, date/time columns, or any other compatible data types. It evaluates the values within the specified column and returns the minimum value.

Here’s an example using the MIN function with a date column:

Consider a table named “orders” with a column named “order_date”. Here’s a sample data:

+------------+
| order_date |
+------------+
| 2021-01-01 |
| 2021-02-01 |
| 2021-03-01 |
| 2021-04-01 |
+------------+

To retrieve the earliest order date from the “orders” table, you can use the MIN function. Here’s an example:

SELECT MIN(order_date) AS earliest_date FROM orders;

Output:

+--------------+
| earliest_date|
+--------------+
| 2021-01-01   |
+--------------+

In this example, the SELECT statement uses the MIN function to retrieve the earliest date from the “order_date” column. The resulting earliest date, 2021-01-01, is displayed as “earliest_date” in the output.

The MIN function is a useful tool for retrieving the smallest value from a column in SQL queries. It allows you to obtain insights such as the minimum price, the earliest date, or any other minimum value within your data set.

SQL: Joining tables with Example

In SQL, joining tables is the process of combining rows from multiple tables based on a related column or condition. By joining tables, you can retrieve data that is spread across multiple tables and create a unified result set. There are different types of joins, such as inner join, left join, right join, and full outer join. Here’s an explanation of joining tables with an example:

Consider two tables: “employees” and “departments”. Here’s a sample data:

“employees” table:

+----+------------+-----------+------------+
| id | first_name | last_name | department |
+----+------------+-----------+------------+
| 1  | John       | Smith     | Sales      |
| 2  | Jane       | Doe       | HR         |
| 3  | Alex       | Johnson   | IT         |
| 4  | Lisa       | Brown     | Sales      |
+----+------------+-----------+------------+

“departments” table:

+----+------------+
| id | department |
+----+------------+
| 1  | Sales      |
| 2  | HR         |
| 3  | IT         |
+----+------------+

To join these two tables based on the “id” column, you can use an inner join. An inner join returns only the rows that have matching values in both tables.

SELECT employees.id, employees.first_name, employees.last_name, departments.department
FROM employees
INNER JOIN departments
ON employees.id = departments.id;

Output:

+----+------------+-----------+------------+
| id | first_name | last_name | department |
+----+------------+-----------+------------+
| 1  | John       | Smith     | Sales      |
| 2  | Jane       | Doe       | HR         |
| 3  | Alex       | Johnson   | IT         |
+----+------------+-----------+------------+

In this example, the SELECT statement joins the “employees” and “departments” tables using an inner join. The ON clause specifies the condition for the join, which is that the “id” column in both tables must match. The resulting rows include the combined information from both tables for the matching IDs.

You can also use other types of joins, such as left join, right join, or full outer join, to include non-matching rows or retrieve specific combinations of data from the tables.

Joining tables is a fundamental concept in SQL that allows you to combine data from multiple tables based on common columns. It enables you to retrieve related information and create more meaningful and comprehensive result sets for analysis and reporting purposes.

amit verma

Website | + posts

Technical content writer with data scientist, artificial intelligence, programming language, database. He has a bachelor’s degree in IT and a certificate in digital marketing, Digital transformation web development android app development He has written for website like Boomi techie, tech mantra, information hub, Tech all