Monday, October 19, 2015

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.

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, have a 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.
Then, if we run the following SQL statement (that contains an INNER JOIN):

Example

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID; 
it will produce something like this:
OrderID CustomerName OrderDate
10308 Ana Trujillo Emparedados y helados 9/18/1996
10365 Antonio Moreno Taquería 11/27/1996
10383 Around the Horn 12/16/1996
10355 Around the Horn 11/15/1996
10278 Berglunds snabbköp 8/12/1996

SQL INNER JOIN Keyword

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

SQL INNER JOIN Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
PS! INNER JOIN is the same as JOIN.


SQL INNER JOIN Example

The following SQL statement will return all customers with orders:

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;



CustomerNameOrderID
Ana Trujillo Emparedados y helados  10308 
Antonio Moreno Taquería  10365 
Around the Horn  10383 
Around the Horn  10355 
Berglunds snabbköp  10278 
Berglunds snabbköp  10280 
Berglunds snabbköp  10384 
Blondel père et fils  10265 
Blondel père et fils  10436 
Blondel père et fils  10297 
Blondel père et fils  10360 
Bólido Comidas preparadas  10326 
Bon app'  10340 
Bon app'  10362 
Bon app'  10331 
Bottom-Dollar Marketse  10411 
Bottom-Dollar Marketse  10431 
Bottom-Dollar Marketse  10389 
Bottom-Dollar Marketse  10410 
B's Beverages  10289 

SQL INNER JOIN

SQL Aliases

SQL Aliases

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.

SQL Alias Syntax for Columns

SELECT column_name AS alias_name
FROM table_name;

SQL Alias Syntax for Tables

SELECT column_name(s)
FROM table_name AS alias_name;

Example

SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;
 

BETWEEN Operator

NOT BETWEEN Operator Example

To display the products outside the range of the previous example, use NOT BETWEEN:

Example

SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20; 
ProductIDProductNameSupplierIDCategoryIDUnitPrice
Chef Anton's Cajun Seasoning  48 - 6 oz jars  22 
Chef Anton's Gumbo Mix  36 boxes  21.35 
Grandma's Boysenberry Spread  12 - 8 oz jars  25 
Uncle Bob's Organic Dried Pears  12 - 1 lb pkgs.  30 
Northwoods Cranberry Sauce  12 - 12 oz jars  40 

BETWEEN Operator with IN Example

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:

Example

SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20)
AND NOT CategoryID IN (1,2,3); 

ProductIDProductNameSupplierIDCategoryIDUnitPrice
31  Gorgonzola Telino  14  12 - 100 g pkgs  12.5 
36  Inlagd Sill  17  24 - 250 g jars  19 
40  Boston Crab Meat  19  24 - 4 oz tins  18.4 
42  Singaporean Hokkien Fried Mee  20  32 - 1 kg pkgs.  14 

BETWEEN Operator with Text Value Example

The following SQL statement selects all products with a ProductName beginning with any of the letter BETWEEN 'C' and 'M':

Example

SELECT * FROM Products
WHERE ProductName BETWEEN 'C' AND 'M'; 

ProductIDProductNameSupplierIDCategoryIDUnitPrice
Chais  10 boxes x 20 bags  18 
Chang  24 - 12 oz bottles  19 
Chef Anton's Cajun Seasoning  48 - 6 oz jars  22 
Chef Anton's Gumbo Mix  36 boxes  21.35 

NOT BETWEEN Operator with Text Value Example

The following SQL statement selects all products with a ProductName beginning with any of the letter NOT BETWEEN 'C' and 'M':

Example

SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'C' AND 'M'; 
ProductIDProductNameSupplierIDCategoryIDUnitPrice
Aniseed Syrup  12 - 550 ml bottles  10 
Uncle Bob's Organic Dried Pears  12 - 1 lb pkgs.  30 
Northwoods Cranberry Sauce  12 - 12 oz jars  40 
Mishi Kobe Niku  18 - 500 g pkgs.  97 

Sample Table

Below is a selection from the "Orders" table:
OrderID CustomerID EmployeeID OrderDate ShipperID
10248 90 5 7/4/1996 3
10249 81 6 7/5/1996 1
10250 34 4 7/8/1996 2
10251 84 3 7/9/1996 1
10252 76 4 7/10/1996 2

BETWEEN Operator with Date Value Example

The following SQL statement selects all orders with an OrderDate BETWEEN '04-July-1996' and '09-July-1996':

Example

SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#; 
OrderIDCustomerIDEmployeeIDOrderDateShipperID
10248  90  7/4/1996 
10249  81  7/5/1996 
10250  34  7/8/1996 
10251  84  7/8/1996 
10252  76  7/9/1996 

SQL IN Syntax BETWEEN Operator

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

SQL IN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);
 
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:
CustomerID CustomerName ContactName Address City PostalCode Country
1
Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4
Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
 The following SQL statement selects all customers with a City of "Paris" or "London":

Example

SELECT * FROM Customers
WHERE City IN ('Paris','London'); 
 
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
Around the Horn  Thomas Hardy  120 Hanover Sq.  London  WA1 1DP  UK 
11  B's Beverages  Victoria Ashworth  Fauntleroy Circus  London  EC2 5NT  UK 
16  Consolidated Holdings  Elizabeth Brown  Berkeley Gardens 12 Brewery   London  WX1 6LT  UK 
19  Eastern Connection  Ann Devon  35 King George  London  WX3 6FW  UK 
53  North/South  Simon Crowther  South House 300 Queensbridge  London  SW7 1RZ  UK 
57  Paris spécialités  Marie Bertrand  265, boulevard Charonne  Paris  75012  France 
 
 
The BETWEEN operator is used to select values within a range.

The SQL BETWEEN Operator

The BETWEEN operator selects values within a range. The values can be numbers, text, or dates.

SQL BETWEEN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
 
 














Below is a selection from the "Products" table:
ProductID ProductName SupplierID CategoryID Unit Price
1 Chais 1 1 10 boxes x 20 bags 18
2 Chang 1 1 24 - 12 oz bottles 19
3 Aniseed Syrup 1 2 12 - 550 ml bottles 10
4 Chef Anton's Cajun Seasoning 1 2 48 - 6 oz jars 22
5 Chef Anton's Gumbo Mix 1 2 36 boxes 21.35

BETWEEN Operator Example

 

The following SQL statement selects all products with a price BETWEEN 10 and 20:

Example

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
;

 

ProductIDProductNameSupplierIDCategoryIDUnitPrice
Chais  10 boxes x 20 bags  18 
Chang  24 - 12 oz bottles  19 
Aniseed Syrup  12 - 550 ml bottles  10 
15  Genen Shouyu  24 - 250 ml bottles  15.5 
16  Pavlova  32 - 500 g boxes  17.45 







































































SQL Wildcard Characters

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. 
With SQL, the wildcards are:
Wildcard Description
% A substitute for zero or more characters
_ A substitute for a single character
[charlist] Sets and ranges of characters to match
[^charlist]
or
[!charlist]
Matches only a character NOT specified within the brackets

Demo Database

In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Customers" table:

ustomerID CustomerName ContactName Address City PostalCode Country
1
Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4
Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden

Using the SQL % Wildcard

The following SQL statement selects all customers with a City starting with "ber":

Example

SELECT * FROM Customers
WHERE City LIKE 'ber%'; 
 
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
Alfreds Futterkiste  Maria Anders  Obere Str. 57  Berlin  12209  Germany 
14  Chop-suey Chinese  Yang Wang  Hauptstr. 29  Bern  3012  Switzerland 
49  Magazzini Alimentari Riuniti  Giovanni Rovelli  Via Ludovico il Moro 22  Bergamo  24100  Italy 
 

The following SQL statement selects all customers with a City containing the pattern "es": 

Example

SELECT * FROM Customers
WHERE City LIKE '%es%'; 
 
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
12  Cactus Comidas para llevar  Patricio Simpson  Cerrito 333  Buenos Aires  1010  Argentina 
18  Du monde entier  Janine Labrune  67, rue des Cinquante Otages  Nantes  44000  France 
26  France restauration  Carine Schmitt  54, rue Royale  Nantes  44000  France 
38  Island Trading  Helen Bennett  Garden House Crowther Way  Cowes  PO31 7PJ  UK 
40  La corne d'abondance  Daniel Tonini  67, avenue de l'Europe  Versailles  78000  France 
 
 

Using the SQL _ Wildcard

The following SQL statement selects all customers with a City starting with any character, followed by "erlin":

Example

SELECT * FROM Customers
WHERE City LIKE '_erlin'; 
 
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
Alfreds Futterkiste  Maria Anders  Obere Str. 57  Berlin  12209  Germany 
 
 
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":

Example

SELECT * FROM Customers
WHERE City LIKE 'L_n_on';
 
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
Around the Horn  Thomas Hardy  120 Hanover Sq.  London  WA1 1DP  UK 
11  B's Beverages  Victoria Ashworth  Fauntleroy Circus  London  EC2 5NT  UK 
16  Consolidated Holdings  Elizabeth Brown  Berkeley Gardens 12 Brewery   London  WX1 6LT  UK 
19  Eastern Connection  Ann Devon  35 King George  London  WX3 6FW  UK 
 
 The following SQL statement selects all customers with a City starting with "b", "s", or "p":

Example

SELECT * FROM Customers
WHERE City LIKE '[bsp]%'; 
 
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
Alfreds Futterkiste  Maria Anders  Obere Str. 57  Berlin  12209  Germany 
Blondel père et fils  Frédérique Citeaux  24, place Kléber  Strasbourg  67000  France 
12  Cactus Comidas para llevar  Patricio Simpson  Cerrito 333  Buenos Aires  1010  Argentina 
14  Chop-suey Chinese  Yang Wang  Hauptstr. 29  Bern  3012  Switzerland 
15  Comércio Mineiro  Pedro Afonso  Av. dos Lusíadas, 23  São Paulo  05432-043  Brazil 
 The following SQL statement selects all customers with a City starting with "a", "b", or "c":

Example

SELECT * FROM Customers
WHERE City LIKE '[a-c]%';
 
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
Alfreds Futterkiste  Maria Anders  Obere Str. 57  Berlin  12209  Germany 
12  Cactus Comidas para llevar  Patricio Simpson  Cerrito 333  Buenos Aires  1010  Argentina 
14  Chop-suey Chinese  Yang Wang  Hauptstr. 29  Bern  3012  Switzerland 
17  Drachenblut Delikatessend  Sven Ottlieb  Walserweg 21  Aachen  52066  Germany 
24  Folk och fä HB  Maria Larsson  Åkergatan 24  Bräcke  S-844 67  Sweden 
29  Galería del gastrónomo  Eduardo Saavedra  Rambla de Cataluña, 23  Barcelona  08022  Spain 
31  Gourmet Lanchonetes  André Fonseca  Av. Brasil, 442  Campinas  04876-786  Brazil 
 
The following SQL statement selects all customers with a City NOT starting with "b", "s", or "p":

Example

SELECT * FROM Customers
WHERE City LIKE '[!bsp]%';

or

SELECT * FROM Customers
WHERE City NOT LIKE '[bsp]%';
 
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
Ana Trujillo Emparedados y helados  Ana Trujillo  Avda. de la Constitución 2222  México D.F.  05021  Mexico 
Antonio Moreno Taquería  Antonio Moreno  Mataderos 2312  México D.F.  05023  Mexico 
Around the Horn  Thomas Hardy  120 Hanover Sq.  London  WA1 1DP  UK 
Berglunds snabbköp  Christina Berglund  Berguvsvägen 8  Luleå  S-958 22  Sweden 
Blauer See Delikatessen  Hanna Moos  Forsterstr. 57  Mannheim  68306  Germany 
 

Sunday, October 18, 2015

SELECT TOP Clause,

SQL Server / MS Access Syntax


SELECT TOP number|percent column_name(s)
FROM table_name;



SQL SELECT TOP Equivalent in MySQL and Oracle


MySQL Syntax


SELECT column_name(s)
FROM table_name
LIMIT number;

Example


SELECT *
FROM Persons
LIMIT 5;
Using the SQL % Wildcard The following SQL statement selects all customers with a City starting with "ber": Example SELECT * FROM Customers WHERE City LIKE 'ber%';

Oracle Syntax


SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

Example


SELECT *
FROM Persons
WHERE ROWNUM <=5;

Example


SELECT TOP 2 * FROM Customers; 

Example


SELECT TOP 50 PERCENT * FROM Customers;

The SQL LIKE Operator

The LIKE operator is used to search for a specified pattern in a column.

SQL LIKE Syntax


SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

Example


SELECT * FROM Customers
WHERE City LIKE 's%'; 
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
Blondel père et fils  Frédérique Citeaux  24, place Kléber  Strasbourg  67000  France 
15  Comércio Mineiro  Pedro Afonso  Av. dos Lusíadas, 23  São Paulo  05432-043  Brazil 
21  Familia Arquibaldo  Aria Cruz  Rua Orós, 92  São Paulo  05442-030  Brazil 
30  Godos Cocina Típica  José Pedro Freyre  C/ Romero, 33  Sevilla  41101  Spain 
35  HILARIÓN-Abastos  Carlos Hernández  Carrera 22 con Ave. Carlos Soublette #8-35  San Cristóbal  5022  Venezuela 

Example


SELECT * FROM Customers
WHERE City LIKE '%s'; 
ustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
12  Cactus Comidas para llevar  Patricio Simpson  Cerrito 333  Buenos Aires  1010  Argentina 
18  Du monde entier  Janine Labrune  67, rue des Cinquante Otages  Nantes  44000  France 
26  France restauration  Carine Schmitt  54, rue Royale  Nantes  44000  France 
31  Gourmet Lanchonetes  André Fonseca  Av. Brasil, 442  Campinas  04876-786  Brazil 
33  GROSELLA-Restaurante  Manuel Pereira  5ª Ave. Los Palos Grandes  Caracas  1081  Venezuela 
38  Island Trading  Helen Bennett  Garden House Crowther Way  Cowes  PO31 7PJ  UK 
40  La corne d'abondance  Daniel Tonini  67, avenue de l'Europe  Versailles  78000  France 
50  Maison Dewey  Catherine Dewey  Rue Joseph-Bens 532  Bruxelles  B-1180  Belgium 
54  Océano Atlántico Ltda.  Yvonne Moncada  Ing. Gustavo Moncada 8585 Piso 20-A  Buenos Aires  1010  Argentina 
57  Paris spécialités  Marie Bertrand  265, boulevard Charonne  Paris  75012  France 
64  Rancho grande  Sergio Gutiérrez  Av. del Libertador 900  Buenos Aires  1010  Argentina 
74  Spécialités du monde  Dominique Perrier  25, rue Lauriston  Paris  75016  France 
83  Vaffeljernet  Palle Ibsen  Smagsløget 45  Århus  8200  Denmark 

Example


SELECT * FROM Customers
WHERE Country LIKE '%land%'; 
CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
14  Chop-suey Chinese  Yang Wang  Hauptstr. 29  Bern  3012  Switzerland 
37  Hungry Owl All-Night Grocers  Patricia McKenna  8 Johnstown Road  Cork  Ireland 
68  Richter Supermarkt  Michael Holz  Grenzacherweg 237  Genève  1203  Switzerland 
87  Wartian Herkku  Pirkko Koskitalo  Torikatu 38  Oulu  90110  Finland 
90  Wilman Kala  Matti Karttunen  Keskuskatu 45  Helsinki  21240  Finland 
91  Wolski  Zbyszek  ul. Filtrowa 68  Walla  01-012  Poland 
he following SQL statement selects all customers with Country NOT containing the pattern "land":

Example


SELECT * FROM Customers
WHERE Country NOT LIKE '%land%'; 

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
Alfreds Futterkiste  Maria Anders  Obere Str. 57  Berlin  12209  Germany 
Ana Trujillo Emparedados y helados  Ana Trujillo  Avda. de la Constitución 2222  México D.F.  05021  Mexico 
Antonio Moreno Taquería  Antonio Moreno  Mataderos 2312  México D.F.  05023  Mexico 
Around the Horn  Thomas Hardy  120 Hanover Sq.  London  WA1 1DP  UK 
Berglunds snabbköp  Christina Berglund  Berguvsvägen 8  Luleå  S-958 22  Sweden 
Blauer See Delikatessen  Hanna Moos  Forsterstr. 57  Mannheim  68306  Germany 
Blondel père et fils  Frédérique Citeaux  24, place Kléber  Strasbourg  67000  France 
Bólido Comidas preparadas  Martín Sommer  C/ Araquil, 67  Madrid  28023  Spain 
Bon app'  Laurence Lebihans  12, rue des Bouchers  Marseille  13008  France 















































SQL DELETE Statement

The DELETE statement is used to delete rows in a table.

SQL DELETE Syntax

DELETE FROM table_name
WHERE some_column=some_value
 

Example

DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders'; 
 

Delete All Data

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:
DELETE FROM table_name;

or

DELETE * FROM table_name;