Friday, November 13, 2015
Tuesday, October 20, 2015
Monday, October 19, 2015
The SQL UNION
The SQL UNION Operator
The UNION operator is used to combine the result-set of two or more SELECT statements.Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
SQL UNION Syntax
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
Note: The UNION operator selects only distinct values by default. To
allow duplicate values, use the ALL keyword with UNION.SQL UNION ALL Syntax
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
PS: The column names in the result-set of a UNION are usually equal to
the column names in the first SELECT statement in the UNION.Demo Database
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 |
| SupplierID | SupplierName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Exotic Liquid | Charlotte Cooper | 49 Gilbert St. | Londona | EC1 4SD | UK |
| 2 | New Orleans Cajun Delights | Shelley Burke | P.O. Box 78934 | New Orleans | 70117 | USA |
| 3 | Grandma Kelly's Homestead | Regina Murphy | 707 Oxford Rd. | Ann Arbor | 48104 | USA |
SQL UNION Example
The following SQL statement selects all the different cities (only distinct values) from the "Customers" and the "Suppliers" tables:Example
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER
BY City;
City
Aachen
Albuquerque
Anchorage
Ann Arbor
Annecy
Århus
Barcelona
SQL FULL OUTER JOIN
SQL FULL OUTER JOIN Keyword
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.
SQL FULL OUTER JOIN Syntax
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
Demo Database
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 |
| OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
|---|---|---|---|---|
| 10308 | 2 | 7 | 1996-09-18 | 3 |
| 10309 | 37 | 3 | 1996-09-19 | 1 |
| 10310 | 77 | 8 | 1996-09-20 | 2 |
SQL FULL OUTER JOIN Example
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;
A selection from the result set may look like this:| CustomerName | OrderID |
|---|---|
| Alfreds Futterkiste | |
| Ana Trujillo Emparedados y helados | 10308 |
| Antonio Moreno Taquería | 10365 |
| 10382 | |
| 10351 |
SQL RIGHT JOIN
SQL RIGHT JOIN Keyword
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.SQL RIGHT JOIN Syntax
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;
PS! In some databases RIGHT JOIN is called RIGHT OUTER JOIN.
Demo Database
In this tutorial we will use the well-known Northwind sample database.Below is a selection from the "Orders" table:
| OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
|---|---|---|---|---|
| 10308 | 2 | 7 | 1996-09-18 | 3 |
| 10309 | 37 | 3 | 1996-09-19 | 1 |
| 10310 | 77 | 8 | 1996-09-20 | 2 |
And a selection from the "Employees" table:
| EmployeeID | LastName | FirstName | BirthDate | Photo | Notes |
|---|---|---|---|---|---|
| 1 | Davolio | Nancy | 12/8/1968 | EmpID1.pic | Education includes a BA in psychology..... |
| 2 | Fuller | Andrew | 2/19/1952 | EmpID2.pic | Andrew received his BTS commercial and.... |
| 3 | Leverling | Janet | 8/30/1963 | EmpID3.pic | Janet has a BS degree in chemistry.... |
SQL RIGHT JOIN Example
The following SQL statement will return all employees, and any orders they have placed:Example
SELECT Orders.OrderID, Employees.FirstName
FROM Orders
RIGHT JOIN
Employees
ON Orders.EmployeeID=Employees.EmployeeID
ORDER BY
Orders.OrderID;
OrderID FirstName
Adam
10248
Steven
10249
Michael
10250
Margaret
10251
Janet
10252
Margaret
10253
Janet
10254
Steven
10255
Anne
10256
Janet
10257
Margaret
10258
Nancy
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.
Below is a selection from the "Customers" table:
And a selection from the "Orders" table:
SQL LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;
PS! In some databases LEFT JOIN is called LEFT OUTER JOIN.Demo Database
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 |
And a selection from the "Orders" table:
| OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
|---|---|---|---|---|
| 10308 | 2 | 7 | 1996-09-18 | 3 |
| 10309 | 37 | 3 | 1996-09-19 | 1 |
| 10310 | 77 | 8 | 1996-09-20 | 2 |
SQL LEFT JOIN Example
The following SQL statement will return all customers, and any orders they might have:Example
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
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.
SQL LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;
PS! In some databases LEFT JOIN is called LEFT OUTER JOIN.
Demo Database
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
And a selection from the "Orders" table:
OrderID
CustomerID
EmployeeID
OrderDate
ShipperID
10308
2
7
1996-09-18
3
10309
37
3
1996-09-19
1
10310
77
8
1996-09-20
2
SQL LEFT JOIN Example
The following SQL statement will return all customers, and any orders they
might have:
Example
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
ORDER BY Customers.CustomerName;
CustomerName OrderID
Alfreds Futterkiste
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
Blauer See Delikatessen
Blondel père et fils
10360
Blondel père et fils
10297
Blondel père et fils
10436
Blondel père et fils
10265
Bólido Comidas preparadas
10326
Bon app'
10331
Bon app'
10340
Bon app'
10362
Bottom-Dollar Marketse
10411
Bottom-Dollar Marketse
10431
Bottom-Dollar Marketse
10389
Bottom-Dollar Marketse
10410
B's Beverages
10289
Cactus Comidas para llevar
Centro comercial Moctezuma
10259
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:
Then, have a look at a selection from the "Customers" table:
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):
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;
CustomerName OrderID
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 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;
ProductID ProductName SupplierID CategoryID Unit Price
4
Chef Anton's Cajun Seasoning
2
2
48 - 6 oz jars
22
5
Chef Anton's Gumbo Mix
2
2
36 boxes
21.35
6
Grandma's Boysenberry Spread
3
2
12 - 8 oz jars
25
7
Uncle Bob's Organic Dried Pears
3
7
12 - 1 lb pkgs.
30
8
Northwoods Cranberry Sauce
3
2
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);
ProductID ProductName SupplierID CategoryID Unit Price
31
Gorgonzola Telino
14
4
12 - 100 g pkgs
12.5
36
Inlagd Sill
17
8
24 - 250 g jars
19
40
Boston Crab Meat
19
8
24 - 4 oz tins
18.4
42
Singaporean Hokkien Fried Mee
20
5
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';
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
4
Chef Anton's Cajun Seasoning
2
2
48 - 6 oz jars
22
5
Chef Anton's Gumbo Mix
2
2
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';
ProductID ProductName SupplierID CategoryID Unit Price
3
Aniseed Syrup
1
2
12 - 550 ml bottles
10
7
Uncle Bob's Organic Dried Pears
3
7
12 - 1 lb pkgs.
30
8
Northwoods Cranberry Sauce
3
2
12 - 12 oz jars
40
9
Mishi Kobe Niku
4
6
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#;
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/8/1996
1
10252
76
4
7/9/1996
2
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');
CustomerID CustomerName ContactName Address City PostalCode Country
4
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;
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
15
Genen Shouyu
6
2
24 - 250 ml bottles
15.5
16
Pavlova
7
3
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%';
CustomerID CustomerName ContactName Address City PostalCode Country
1
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%';
CustomerID CustomerName ContactName Address City PostalCode Country
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';
CustomerID CustomerName ContactName Address City PostalCode Country
1
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';
CustomerID CustomerName ContactName Address City PostalCode Country
4
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]%';
CustomerID CustomerName ContactName Address City PostalCode Country
1
Alfreds Futterkiste
Maria Anders
Obere Str. 57
Berlin
12209
Germany
7
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]%';
CustomerID CustomerName ContactName Address City PostalCode Country
1
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]%';
CustomerID CustomerName ContactName Address City PostalCode Country
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
6
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%';
CustomerID CustomerName ContactName Address City PostalCode Country
7
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';
ustomerID CustomerName ContactName Address City PostalCode Country
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%';
CustomerID CustomerName ContactName Address City PostalCode Country
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%';
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
6
Blauer See Delikatessen
Hanna Moos
Forsterstr. 57
Mannheim
68306
Germany
7
Blondel père et fils
Frédérique Citeaux
24, place Kléber
Strasbourg
67000
France
8
Bólido Comidas preparadas
Martín Sommer
C/ Araquil, 67
Madrid
28023
Spain
9
Bon app'
Laurence Lebihans
12, rue des Bouchers
Marseille
13008
France
Subscribe to:
Posts (Atom)