SQL

SQL Join

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them. The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN return all rows from multiple tables where the join condition is met. Notice that the "CustomerID" column in the "Orders" table refers to the customer in the "Customers" table. The relationship between the two tables above is the "CustomerID" column. Then, if we run the following SQL statement (that contains an INNER JOIN): SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

SQL Wildcard Characters

In SQL, wildcard characters are used with the SQL LIKE operator. SQL wildcards are used to search for data within a table. Wildcard: %, _, [charlist], [^charlist], [!charlist] -- The following SQL statement selects all customers with a City containing the pattern "es": SELECT * FROM Customers WHERE City LIKE '%es%'; ---- The following SQL statement selects all customers with a City starting with "L", followed by any character, followed by "n", followed by any character, followed by "on": SELECT * FROM Customers WHERE City LIKE 'L_n_on'; ---- The following SQL statement selects all customers with a City NOT starting with "b", "s", or "p": SELECT * FROM Customers WHERE City LIKE '[!bsp]%'; -----

SQL Syntax

SELECT * FROM Customers;

SQL Select Distinct

SELECT DISTINCT City FROM Customers;

SQL Alias

SQL aliases are used to give a database table, or a column in a table, a temporary name. Basically aliases are created to make column names more readable. The following SQL statement selects all the orders from the customer with CustomerID=4 (Around the Horn). We use the "Customers" and "Orders" tables, and give them the table aliases of "c" and "o" respectively (Here we have used aliases to make the SQL shorter): SELECT o.OrderID, o.OrderDate, c.CustomerName FROM Customers AS c, Orders AS o WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID;

SQL AND & OR

The AND operator displays a record if both the first condition AND the second condition are true. The OR operator displays a record if either the first condition OR the second condition is true SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='München');

SQL Between

The BETWEEN operator selects values within a range. The values can be numbers, text, or dates. -- The following SQL statement selects all products with a price BETWEEN 10 and 20, but products with a CategoryID of 1,2, or 3 should not be displayed: SELECT * FROM Products WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (1,2,3); --- The following SQL statement selects all products with a ProductName beginning with any of the letter NOT BETWEEN 'C' and 'M':SELECT * FROM Products WHERE ProductName NOT BETWEEN 'C' AND 'M'; --- The following SQL statement selects all orders with an OrderDate BETWEEN '04-July-1996' and '09-July-1996': SELECT * FROM Orders WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;

SQL Full Join

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2). The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins. -- The following SQL statement selects all customers, and all orders: SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName; --- Note: The FULL OUTER JOIN keyword returns all the rows from the left table (Customers), and all the rows from the right table (Orders). If there are rows in "Customers" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well.

SQL IN

The IN operator allows you to specify multiple values in a WHERE clause - SELECT * FROM Customers WHERE City IN ('Paris','London');

SQL Inner Join

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables. --- The following SQL statement will return all customers with orders: SELECT Customers.CustomerName, Orders.OrderID FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;

SQL Left Join

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match. --- The following SQL statement will return all customers, and any orders they might have: SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName; -- The LEFT JOIN keyword returns all the rows from the left table (Customers), even if there are no matches in the right table (Orders).

SQL LIKE

The LIKE operator is used to search for a specified pattern in a column. SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern; ---- SELECT * FROM Customers WHERE City LIKE '%s'; The following SQL statement selects all customers with a City ending with the letter "s": ----- SELECT * FROM Customers WHERE Country NOT LIKE '%land%'; ---

SQL Order By

The ORDER BY keyword is used to sort the result-set by one or more columns. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword. SELECT column_name,column_name FROM table_name ORDER BY column_name,column_name ASC|DESC; -- SELECT * FROM Customers ORDER BY Country,CustomerName;

SQL Right Join

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match. -- The following SQL statement will return all employees, and any orders they have placed: SELECT Orders.OrderID, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID=Employees.EmployeeID ORDER BY Orders.OrderID;

SQL Select

The SELECT statement is used to select data from a database. The result is stored in a result table, called the result-set. SELECT column_name,column_name FROM table_name;

SQL Where Clause

The WHERE clause is used to extract only those records that fulfill a specified criterion. SELECT column_name,column_name FROM table_name WHERE column_name operator value; --- SELECT * FROM Customers WHERE Country='Mexico'; Operators: =,>,<,<>(not equal), >=, <=, BETWEEN, LIKE, IN