Aggregate functions

Aggregate functions are functions used in SQL to perform calculations on groups of rows in a table. These functions are commonly used to calculate summary statistics, such as totals, averages, counts, and maximum or minimum values.

Here are some common aggregate functions used in SQL:

SUM: This function calculates the sum of values in a column.

For example, the following SQL statement calculates the total sales for all products:

sql

SELECT SUM(sales)

FROM products;

AVG: This function calculates the average value of a column.

For example, the following SQL statement calculates the average price of all products:

sql

SELECT AVG(price)

FROM products;

COUNT: This function counts the number of rows or non-null values in a column.

For example, the following SQL statement counts the number of orders in the “orders” table:

SELECT COUNT(*)

FROM orders;

MAX: This function returns the maximum value in a column.

For example, the following SQL statement returns the highest price of all products:

sql

SELECT MAX(price)

FROM products;

MIN: This function returns the minimum value in a column.

For example, the following SQL statement returns the lowest price of all products:

sql

Copy code

SELECT MIN(price)

FROM products;

Aggregate functions can also be used in combination with GROUP BY clauses to calculate summary statistics for groups of rows based on a common value in a column. For example, the following SQL statement calculates the total sales for each product category:

sql

SELECT category, SUM(sales)

FROM products

GROUP BY category;

In summary, aggregate functions are essential tools for calculating summary statistics in SQL. By using these functions effectively, you can gain insights into your data and make informed decisions.

SQL: Insert and Delete operations, Joins

Insert and Delete operations:

Insert operations are used to add new rows of data to a table in a database, while delete operations are used to remove existing rows of data from a table.

To insert data into a table, you can use the following SQL statement:

sql

Copy code

INSERT INTO table_name (column1, column2, column3, …)

VALUES (value1, value2, value3, …);

For example, the following SQL statement inserts a new row into the “customers” table:

sql

INSERT INTO customers (customer_name, customer_email)

VALUES (‘John Smith’, ‘john.smith@example.com’);

To delete data from a table, you can use the following SQL statement:

sql

DELETE FROM table_name

WHERE condition;

For example, the following SQL statement deletes all orders that were placed more than 30 days ago:

sql

DELETE FROM orders

WHERE order_date < DATEADD(day, -30, GETDATE());

Joins:

Joins are used to combine data from two or more tables based on a common column. There are several types of joins in SQL, including inner join, left join, right join, and full outer join.

Here is an example of an inner join:

sql

SELECT *

FROM orders

INNER JOIN customers

ON orders.customer_id = customers.customer_id;

This SQL statement retrieves all orders along with the corresponding customer information from the “orders” and “customers” tables, where the customer ID in the “orders” table matches the customer ID in the “customers” table.

In a left join, all rows from the left table are returned along with matching rows from the right table, and null values are returned for non-matching rows in the right table. In a right join, the opposite is true – all rows from the right table are returned along with matching rows from the left table, and null values are returned for non-matching rows in the left table. In a full outer join, all rows from both tables are returned, with null values for non-matching rows in either table.

Joins are powerful tools for combining data from multiple tables in a database. By using joins effectively, you can gain a deeper understanding of your data and make more informed decisions.