DEV Community

Sergio Triana Escobedo
Sergio Triana Escobedo

Posted on

SQL A SMALL GUIDE FOR ENGINEERS

As software engineers, we work with SQL databases all the time, and we know that there are many ways to get valuable information from them. In this article, we will delve into the most advanced SQL statements and explain how they can help solve complex problems and obtain useful information.

UNION

The UNION statement is used to combine two or more result sets into one. The result sets must have the same column structure. For example, if we want to combine two customer tables, we can use the following syntax:

SELECT name, address FROM customers
UNION
SELECT name, address FROM customers_backup;
Enter fullscreen mode Exit fullscreen mode

This will return a result set with the names and addresses of the customers from both tables, without duplicates.

JOIN

The JOIN statement is used to combine two or more tables into a single query. There are several types of JOIN, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

For example, if we want to combine a customer table with an order table to get information about each customer's orders, we can use the following syntax:

SELECT customers.name, orders.order_date, orders.total_amount
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;
Enter fullscreen mode Exit fullscreen mode

This will return a result set with the name of each customer, the date of each order, and the total amount of each order.

SUBQUERY

A subquery is a query inside another query. Subqueries are often used to filter results or to perform complex calculations.

For example, if we want to find the customers who have placed orders with a total amount greater than the average, we can use the following syntax:

SELECT name, address
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  GROUP BY customer_id
  HAVING SUM(total_amount) > (
    SELECT AVG(total_amount)
    FROM orders
  )
);
Enter fullscreen mode Exit fullscreen mode

This query will return the names and addresses of the customers who have placed orders with a total amount greater than the average of all orders.

WINDOW FUNCTION

A window function is a function that performs calculations on a set of rows related to the current row. Window functions are often used for statistical and aggregate calculations.

For example, if we want to find the rank of each customer based on the total amount of their orders, we can use the following syntax:

SELECT name, total_amount,
  RANK() OVER (ORDER BY total_amount DESC) as ranking
FROM (
  SELECT customers.name, SUM(orders.total_amount) as total_amount
  FROM customers
  INNER JOIN orders
  ON customers.customer_id = orders.customer_id
  GROUP BY customers.customer_id
) as customer_totals;
Enter fullscreen mode Exit fullscreen mode

This query will return the names of the customers, the total amount of their orders, and their ranking based on that total amount.

LIKE

The LIKE clause is used in SQL to search for text patterns in a table. For example, suppose we have an employee table and want to find all employees whose name starts with "A". We could use the following query:

SELECT * FROM employees WHERE name LIKE 'A%';
Enter fullscreen mode Exit fullscreen mode

In this example, the % character is used to represent any number of characters that may appear after the "A". Therefore, this query would retrieve all employees whose name begins with "A".

HAVING

The HAVING clause is used in SQL to filter the results of a query based on a condition involving an aggregate function (such as SUM, COUNT, AVG, MAX, or MIN). For example, suppose we have a sales table and want to find all stores whose total sales exceed $100,000. We could use the following query:

SELECT shop, SUM(salesforce) as total_salesforce FROM salesforce GROUP BY shop HAVING SUM(salesforce) > 100000;
Enter fullscreen mode Exit fullscreen mode

In this example, the SUM aggregation function is used to calculate total sales for each store, and then the results are filtered using the HAVING clause to show only stores whose total sales exceed $100,000.

INDEX

An index is a data structure used to speed up queries in a table. An index is created on one or more columns of the table and allows the database engine to search data faster. For example, if we have an employee table with a name column and want to search for all employees whose name starts with "A", we could create an index on the name column to speed up the search. The syntax for creating an index in SQL is as follows:

CREATE INDEX index_names ON employees (name);
Enter fullscreen mode Exit fullscreen mode

In this example, we are creating an index called "index_names" on the "employees" table on the "name" column.

TRIGGERS

Triggers are database objects that are automatically executed in response to certain events in the table. For example, we could create a trigger that is automatically executed every time a new row is inserted into a table. The trigger could perform some action, such as updating another table or sending an email. The syntax for creating a trigger in SQL is as follows:

CREATE TRIGGER trigger_name BEFORE INSERT ON table FOR EACH ROW BEGIN ... END;
Enter fullscreen mode Exit fullscreen mode

In this example, we are creating a trigger called "trigger_name" that will be executed before a new row is inserted into the "table". The trigger will be executed once for each inserted row and the logic will be defined within the BEGIN...END block.

CONCLUSION

In conclusion, SQL statements are very useful for solving problems and obtaining useful information from databases. In this article, we have explored some of the most useful statements such as UNION, JOIN, SUBQUERY, WINDOW FUNCTION, LIKE, HAVING, INDEX, and TRIGGERS which are important aspects of SQL that allow users to interact with data efficiently. I hope this guide has been helpful to everyone.

HAPPY CODING!!!

Top comments (0)