SQL

JOINS

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: SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

WILDCARDS

A wildcard character is used to substitute any other character(s) in a string. Wildcard characters are used with the SQL LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards used in conjunction with the LIKE operator: % - The percent sign represents zero, one, or multiple characters _ - The underscore represents a single character SELECT * FROM Customers WHERE City LIKE '_erlin';

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 the query. SELECT column_name AS alias_name FROM table_name;

AVG

The AVG() function returns the average value of a numeric column. SELECT AVG(column_name) FROM table_name WHERE condition;

BETWEEN

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included. SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

COUNT

The COUNT() function returns the number of rows that matches a specified criteria. SELECT COUNT(column_name) FROM table_name WHERE condition;

AND / OR

The WHERE clause can be combined with AND, OR, and NOT operators. The AND and OR operators are used to filter records based on more than one condition: The AND operator displays a record if all the conditions separated by AND is TRUE. The OR operator displays a record if any of the conditions separated by OR is TRUE. SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 AND condition3 ...; SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR condition3 ...;

WHERE

The WHERE clause is used to filter records. The WHERE clause is used to extract only those records that fulfill a specified condition. SELECT * FROM Customers WHERE Country='Mexico';

IN

The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions. SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);

INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables. SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

LEFT JOIN

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match. SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

LIKE

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards used in conjunction with the LIKE operator: % - The percent sign represents zero, one, or multiple characters _ - The underscore represents a single character SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern;

ORDER BY

The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword. SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;

SUM

The SUM() function returns the total sum of a numeric column. SELECT SUM(column_name) FROM table_name WHERE condition;