Author: Mostafa

SQL Joins

SQL JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Let’s look at a selection from the “Orders” table:

OrderID CustomerID OrderDate
10308 2 1996-09-18
10309 37 1996-09-19
10310 77 1996-09-20

Then, look at a selection from the “Customers” table:

CustomerID CustomerName ContactName Country
1 Alfreds Futterkiste Maria Anders Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Mexico
3 Antonio Moreno Taquería Antonio Moreno Mexico

Notice that the “CustomerID” column in the “Orders” table refers to the “CustomerID” in the “Customers” table. The relationship between the two tables above is the “CustomerID” column. Continue reading SQL Joins

SQL Aliases

SQL Aliases

SQL aliases are used to give a table, or a column in a table, a temporary name.

Aliases are often used to make column names more readable.

An alias only exists for the duration of that query.

An alias is created with the AS keyword.

Example

SELECT CustomerID AS ID
FROM Customers;

Continue reading SQL Aliases

SQL IN Operator

The SQL IN Operator

The IN operator allows you to specify multiple values in a
WHERE clause.

The IN operator is a shorthand for multiple
OR conditions.

Example

Return all customers from ‘Germany’, ‘France’, or ‘UK’

SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');

Continue reading SQL IN Operator

SQL Wildcards

SQL Wildcard Characters

A wildcard character is used to substitute one or more characters in a string.

Wildcard characters are used with the
LIKE operator. The LIKE operator is used in a
WHERE clause to search for a specified pattern in a column.

Example

Return all customers that starts with the letter ‘a’:

SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

Continue reading SQL Wildcards

SQL AVG() Function

The SQL AVG() Function

The AVG() function returns the average value of a numeric column.

Example

Find the average price of all products:

SELECT AVG(Price)
FROM Products;

Note: NULL values are ignored.

Continue reading SQL AVG() Function

SQL SUM() Function

The SQL SUM() Function

The SUM() function returns the total sum of a numeric column.

Example

Return the sum of all Quantity fields in the OrderDetails table:

SELECT SUM(Quantity)
FROM OrderDetails;

Continue reading SQL SUM() Function

SQL COUNT() Function

The SQL COUNT() Function

The COUNT() function returns the number of rows that matches a specified criterion.

Example

Find the total number of rows in the Products table:

SELECT COUNT(*)
FROM Products;

Continue reading SQL COUNT() Function

SQL MIN() and MAX() Functions

The SQL MIN() and MAX() Functions

The MIN() function returns the smallest value of the selected column.

The MAX() function returns the largest value of the selected column.

MIN Example

Find the lowest price in the Price column:

SELECT MIN(Price)
FROM Products;

Continue reading SQL MIN() and MAX() Functions