HTML Quotation and Citation Elements


In this chapter we will go through the <blockquote>,<q>, <abbr>, <address>, <cite>, and <bdo> HTML elements.


Example

Here is a quote from WWF's website:

For 60 years, WWF has worked to help people and nature thrive. As the world's leading conservation organization, WWF works in nearly 100 countries. At every level, we collaborate with people around the world to develop and deliver innovative solutions that protect communities, wildlife, and the places in which they live.

HTML <blockquote> for Quotations

The HTML <blockquote> element defines a section that is quoted from another source.

Browsers usually indent <blockquote> elements.

Example

<p>Here is a quote from WWF's website:</p>
<blockquote cite="http://www.worldwildlife.org/who/index.html">
For 60 years, WWF has worked to help people and nature thrive. As the world's leading conservation organization, WWF works in nearly 100 countries. At every level, we collaborate with people around the world to develop and deliver innovative solutions that protect communities, wildlife, and the places in which they live.
</blockquote>

HTML <q> for Short Quotations

The HTML <q> tag defines a short quotation.

Browsers normally insert quotation marks around the quotation.

Example

<p>WWF's goal is to: <q>Build a future where people live in harmony with nature.</q></p>


HTML <abbr> for Abbreviations

The HTML <abbr> tag defines an abbreviation or an acronym, like "HTML", "CSS", "Mr.", "Dr.", "ASAP", "ATM".

Marking abbreviations can give useful information to browsers, translation systems and search-engines.

Tip: Use the global title attribute to show the description for the abbreviation/acronym when you mouse over the element. 

Example

<p>The <abbr title="World Health Organization">WHO</abbr> was founded in 1948.</p>

HTML <address> for Contact Information

The HTML <address> tag defines the contact information for the author/owner of a document or an article.

The contact information can be an email address, URL, physical address, phone number, social media handle, etc.

The text in the <address> element usually renders in italic, and browsers will always add a line break before and after the <address> element.

Example

<address>
Written by John Doe.<br>
Visit us at:<br>
Example.com<br>
Box 564, Disneyland<br>
USA
</address>

HTML <cite> for Work Title

The HTML <cite> tag defines the title of a creative work (e.g. a book, a poem, a song, a movie, a painting, a sculpture, etc.).

Note: A person's name is not the title of a work.

The text in the <cite> element usually renders in italic.

Example

<p><cite>The Scream</cite> by Edvard Munch. Painted in 1893.</p>

HTML <bdo> for Bi-Directional Override

BDO stands for Bi-Directional Override.

The HTML <bdo> tag is used to override the current text direction:

Example

<bdo dir="rtl">This text will be written from right to left</bdo>

MySQL is a widely used relational database management system (RDBMS).

MySQL is free and open-source.

MySQL is ideal for both small and large applications.


Examples in Each Chapter

With our online MySQL editor, you can edit the SQL statements, and click on a button to view the result.

Example

SELECT * FROM Customers;

Click on the "Try it Yourself" button to see how it works.


MySQL HOME

Introduction to MySQL

 

MySQL is a very popular open-source relational database management system (RDBMS).


What is MySQL?

  • MySQL is a relational database management system
  • MySQL is open-source
  • MySQL is free
  • MySQL is ideal for both small and large applications
  • MySQL is very fast, reliable, scalable, and easy to use
  • MySQL is cross-platform
  • MySQL is compliant with the ANSI SQL standard
  • MySQL was first released in 1995
  • MySQL is developed, distributed, and supported by Oracle Corporation
  • MySQL is named after co-founder Monty Widenius's daughter: My

Who Uses MySQL?

  • Huge websites like Facebook, Twitter, Airbnb, Booking.com, Uber, GitHub, YouTube, etc.
  • Content Management Systems like WordPress, Drupal, Joomla!, Contao, etc.
  • A very large number of web developers around the world

Show Data On Your Web Site

To build a web site that shows data from a database, you will need:

  • An RDBMS database program (like MySQL)
  • A server-side scripting language, like PHP
  • To use SQL to get the data you want
  • To use HTML / CSS to style the page

 

 
MySQL Intro

What is RDBMS?

RDBMS stands for Relational Database Management System.

RDBMS is a program used to maintain a relational database.

RDBMS is the basis for all modern database systems such as MySQL, Microsoft SQL Server, Oracle, and Microsoft Access.

RDBMS uses SQL queries to access the data in the database.


What is a Database Table?

A table is a collection of related data entries, and it consists of columns and rows.

A column holds specific information about every record in the table.

A record (or row) is each individual entry that exists in a table.

Look at a selection from the Northwind "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 columns in the "Customers" table above are: CustomerID, CustomerName, ContactName, Address, City, PostalCode and Country. The table has 5 records (rows).



What is a Relational Database?

A relational database defines database relationships in the form of tables. The tables are related to each other - based on data common to each.

Look at the following three tables "Customers", "Orders", and "Shippers" from the Northwind database:

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 relationship between the "Customers" table and the "Orders" table is the CustomerID column:

Orders Table

OrderID

CustomerID

EmployeeID

OrderDate

ShipperID

10278

5

8

1996-08-12

2

10280

5

2

1996-08-14

1

10308

2

7

1996-09-18

3

10355

4

6

1996-11-15

1

10365

3

3

1996-11-27

2

MySQL RDBMS

MySQL SQL


What is SQL?

SQL is the standard language for dealing with Relational Databases.

SQL is used to insert, search, update, and delete database records.


How to Use SQL

The following SQL statement selects all the records in the "Customers" table:

Example

SELECT * FROM Customers;

Keep in Mind That...

  • SQL keywords are NOT case sensitive: select is the same as SELECT

In this tutorial we will write all SQL keywords in upper-case.


Semicolon after SQL Statements?

Some database systems require a semicolon at the end of each SQL statement.

Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

In this tutorial, we will use semicolon at the end of each SQL statement.


Some of The Most Important SQL Commands

  • SELECT - extracts data from a database
  • UPDATE - updates data in a database
  • DELETE - deletes data from a database
  • INSERT INTO - inserts new data into a database
  • CREATE DATABASE - creates a new database
  • ALTER DATABASE - modifies a database
  • CREATE TABLE - creates a new table
  • ALTER TABLE - modifies a table
  • DROP TABLE - deletes a table
  • CREATE INDEX - creates an index (search key)
  • DROP INDEX - deletes an index

 

The MySQL SELECT Statement

The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

SELECT Syntax

SELECT column1, column2, ...
FROM table_name;

Here, column1, column2, ... are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:

SELECT * FROM table_name;

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Customers" table in the Northwind sample database:

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

SELECT Columns Example

The following SQL statement selects the "CustomerName", "City", and "Country" columns from the "Customers" table:

Example

SELECT CustomerName, City, Country FROM Customers;

SELECT * Example

The following SQL statement selects ALL the columns from the "Customers" table:

Example

SELECT * FROM Customers;


The MySQL SELECT DISTINCT Statement

The SELECT DISTINCT statement is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

SELECT DISTINCT Syntax

SELECT DISTINCT column1, column2, ...
FROM table_name;

SELECT Example Without DISTINCT

The following SQL statement selects all (including the duplicates) values from the "Country" column in the "Customers" table:

Example

SELECT Country FROM Customers;

Now, let us use the SELECT DISTINCT statement and see the result.


SELECT DISTINCT Examples

The following SQL statement selects only the DISTINCT values from the "Country" column in the "Customers" table:

Example

SELECT DISTINCT Country FROM Customers;

The following SQL statement counts and returns the number of different (distinct) countries in the "Customers" table:

Example

SELECT COUNT(DISTINCT Country) FROM Customers;

Test Yourself With Exercises

Exercise:

Insert the missing statement to get all the columns from the Customers table.

 * FROM Customers;

 

 

 
MySQL SELECT
 

The MySQL WHERE Clause

The WHERE clause is used to filter records.

It is used to extract only those records that fulfill a specified condition.

WHERE Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Note: The WHERE clause is not only used in SELECT statements, it is also used in UPDATE, DELETE, etc.!


Demo Database

Below is a selection from the "Customers" table in the Northwind sample database:

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


WHERE Clause Example

The following SQL statement selects all the customers from "Mexico":

Example

SELECT * FROM Customers
WHERE Country = 'Mexico';

Text Fields vs. Numeric Fields

SQL requires single quotes around text values (most database systems will also allow double quotes).

However, numeric fields should not be enclosed in quotes:

Example

SELECT * FROM Customers
WHERE CustomerID = 1;

Operators in The WHERE Clause

The following operators can be used in the WHERE clause:

Operator Description Example
= Equal  
> Greater than  
< Less than  
>= Greater than or equal  
<= Less than or equal  
<> Not equal. Note: In some versions of SQL this operator may be written as !=  
BETWEEN Between a certain range  
LIKE Search for a pattern  
IN To specify multiple possible values for a column  

Test Yourself With Exercises

Exercise:

Select all records where the City column has the value "Berlin".

SELECT * FROM Customers
  = ;

 

 

 
MySQL WHERE
 

The MySQL AND, OR and NOT Operators

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 are TRUE.
  • The OR operator displays a record if any of the conditions separated by OR is TRUE.

The NOT operator displays a record if the condition(s) is NOT TRUE.

AND Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

OR Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

NOT Syntax

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Demo Database

The table below shows the complete "Customers" table from the Northwind sample database:

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
10 Bottom-Dollar Marketse Elizabeth Lincoln 23 Tsawassen Blvd. Tsawassen T2F 8M4 Canada
11 B's Beverages Victoria Ashworth Fauntleroy Circus London EC2 5NT UK
12 Cactus Comidas para llevar Patricio Simpson Cerrito 333 Buenos Aires 1010 Argentina
13 Centro comercial Moctezuma Francisco Chang Sierras de Granada 9993 México D.F. 05022 Mexico
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
16 Consolidated Holdings Elizabeth Brown Berkeley Gardens 12 Brewery London WX1 6LT UK
17 Drachenblut Delikatessend Sven Ottlieb Walserweg 21 Aachen 52066 Germany
18 Du monde entier Janine Labrune 67, rue des Cinquante Otages Nantes 44000 France
19 Eastern Connection Ann Devon 35 King George London WX3 6FW UK
20 Ernst Handel Roland Mendel Kirchgasse 6 Graz 8010 Austria
21 Familia Arquibaldo Aria Cruz Rua Orós, 92 São Paulo 05442-030 Brazil
22 FISSA Fabrica Inter. Salchichas S.A. Diego Roel C/ Moralzarzal, 86 Madrid 28034 Spain
23 Folies gourmandes Martine Rancé 184, chaussée de Tournai Lille 59000 France
24 Folk och fä HB Maria Larsson Åkergatan 24 Bräcke S-844 67 Sweden
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
26 France restauration Carine Schmitt 54, rue Royale Nantes 44000 France
27 Franchi S.p.A. Paolo Accorti Via Monte Bianco 34 Torino 10100 Italy
28 Furia Bacalhau e Frutos do Mar Lino Rodriguez Jardim das rosas n. 32 Lisboa 1675 Portugal
29 Galería del gastrónomo Eduardo Saavedra Rambla de Cataluña, 23 Barcelona 08022 Spain
30 Godos Cocina Típica José Pedro Freyre C/ Romero, 33 Sevilla 41101 Spain
31 Gourmet Lanchonetes André Fonseca Av. Brasil, 442 Campinas 04876-786 Brazil
32 Great Lakes Food Market Howard Snyder 2732 Baker Blvd. Eugene 97403 USA
33 GROSELLA-Restaurante Manuel Pereira 5ª Ave. Los Palos Grandes Caracas 1081 Venezuela
34 Hanari Carnes Mario Pontes Rua do Paço, 67 Rio de Janeiro 05454-876 Brazil
35 HILARIÓN-Abastos Carlos Hernández Carrera 22 con Ave. Carlos Soublette #8-35 San Cristóbal 5022 Venezuela
36 Hungry Coyote Import Store Yoshi Latimer City Center Plaza 516 Main St. Elgin 97827 USA
37 Hungry Owl All-Night Grocers Patricia McKenna 8 Johnstown Road Cork   Ireland
38 Island Trading Helen Bennett Garden House Crowther Way Cowes PO31 7PJ UK
39 Königlich Essen Philip Cramer Maubelstr. 90 Brandenburg 14776 Germany
40 La corne d'abondance Daniel Tonini 67, avenue de l'Europe Versailles 78000 France
41 La maison d'Asie Annette Roulet 1 rue Alsace-Lorraine Toulouse 31000 France
42 Laughing Bacchus Wine Cellars Yoshi Tannamuri 1900 Oak St. Vancouver V3F 2K1 Canada
43 Lazy K Kountry Store John Steel 12 Orchestra Terrace Walla Walla 99362 USA
44 Lehmanns Marktstand Renate Messner Magazinweg 7 Frankfurt a.M. 60528 Germany
45 Let's Stop N Shop Jaime Yorres 87 Polk St. Suite 5 San Francisco 94117 USA
46 LILA-Supermercado Carlos González Carrera 52 con Ave. Bolívar #65-98 Llano Largo Barquisimeto 3508 Venezuela
47 LINO-Delicateses Felipe Izquierdo Ave. 5 de Mayo Porlamar I. de Margarita 4980 Venezuela
48 Lonesome Pine Restaurant Fran Wilson 89 Chiaroscuro Rd. Portland 97219 USA
49 Magazzini Alimentari Riuniti Giovanni Rovelli Via Ludovico il Moro 22 Bergamo 24100 Italy
50 Maison Dewey Catherine Dewey Rue Joseph-Bens 532 Bruxelles B-1180 Belgium
51 Mère Paillarde Jean Fresnière 43 rue St. Laurent Montréal H1J 1C3 Canada
52 Morgenstern Gesundkost Alexander Feuer Heerstr. 22 Leipzig 04179 Germany
53 North/South Simon Crowther South House 300 Queensbridge London SW7 1RZ UK
54 Océano Atlántico Ltda. Yvonne Moncada Ing. Gustavo Moncada 8585 Piso 20-A Buenos Aires 1010 Argentina
55 Old World Delicatessen Rene Phillips 2743 Bering St. Anchorage 99508 USA
56 Ottilies Käseladen Henriette Pfalzheim Mehrheimerstr. 369 Köln 50739 Germany
57 Paris spécialités Marie Bertrand 265, boulevard Charonne Paris 75012 France
58 Pericles Comidas clásicas Guillermo Fernández Calle Dr. Jorge Cash 321 México D.F. 05033 Mexico
59 Piccolo und mehr Georg Pipps Geislweg 14 Salzburg 5020 Austria
60 Princesa Isabel Vinhoss Isabel de Castro Estrada da saúde n. 58 Lisboa 1756 Portugal
61 Que Delícia Bernardo Batista Rua da Panificadora, 12 Rio de Janeiro 02389-673 Brazil
62 Queen Cozinha Lúcia Carvalho Alameda dos Canàrios, 891 São Paulo 05487-020 Brazil
63 QUICK-Stop Horst Kloss Taucherstraße 10 Cunewalde 01307 Germany
64 Rancho grande Sergio Gutiérrez Av. del Libertador 900 Buenos Aires 1010 Argentina
65 Rattlesnake Canyon Grocery Paula Wilson 2817 Milton Dr. Albuquerque 87110 USA
66 Reggiani Caseifici Maurizio Moroni Strada Provinciale 124 Reggio Emilia 42100 Italy
67 Ricardo Adocicados Janete Limeira Av. Copacabana, 267 Rio de Janeiro 02389-890 Brazil
68 Richter Supermarkt Michael Holz Grenzacherweg 237 Genève 1203 Switzerland
69 Romero y tomillo Alejandra Camino Gran Vía, 1 Madrid 28001 Spain
70 Santé Gourmet Jonas Bergulfsen Erling Skakkes gate 78 Stavern 4110 Norway
71 Save-a-lot Markets Jose Pavarotti 187 Suffolk Ln. Boise 83720 USA
72 Seven Seas Imports Hari Kumar 90 Wadhurst Rd. London OX15 4NB UK
73 Simons bistro Jytte Petersen Vinbæltet 34 København 1734 Denmark
74 Spécialités du monde Dominique Perrier 25, rue Lauriston Paris 75016 France
75 Split Rail Beer & Ale Art Braunschweiger P.O. Box 555 Lander 82520 USA
76 Suprêmes délices Pascale Cartrain Boulevard Tirou, 255 Charleroi B-6000 Belgium
77 The Big Cheese Liz Nixon 89 Jefferson Way Suite 2 Portland 97201 USA
78 The Cracker Box Liu Wong 55 Grizzly Peak Rd. Butte 59801 USA
79 Toms Spezialitäten Karin Josephs Luisenstr. 48 Münster 44087 Germany
80 Tortuga Restaurante Miguel Angel Paolino Avda. Azteca 123 México D.F. 05033 Mexico
81 Tradição Hipermercados Anabela Domingues Av. Inês de Castro, 414 São Paulo 05634-030 Brazil
82 Trail's Head Gourmet Provisioners Helvetius Nagy 722 DaVinci Blvd. Kirkland 98034 USA
83 Vaffeljernet Palle Ibsen Smagsløget 45 Århus 8200 Denmark
84 Victuailles en stock Mary Saveley 2, rue du Commerce Lyon 69004 France
85 Vins et alcools Chevalier Paul Henriot 59 rue de l'Abbaye Reims 51100 France
86 Die Wandernde Kuh Rita Müller Adenauerallee 900 Stuttgart 70563 Germany
87 Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland
88 Wellington Importadora Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil
89 White Clover Markets Karl Jablonski 305 - 14th Ave. S. Suite 3B Seattle 98128 USA
90 Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland
91 Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland


AND Example

The following SQL statement selects all fields from "Customers" where country is "Germany" AND city is "Berlin":

Example

SELECT * FROM Customers
WHERE Country = 'Germany' AND City = 'Berlin';

OR Example

The following SQL statement selects all fields from "Customers" where city is "Berlin" OR "Stuttgart":

Example

SELECT * FROM Customers
WHERE City = 'Berlin' OR City = 'Stuttgart';

The following SQL statement selects all fields from "Customers" where country is "Germany" OR "Spain":

Example

SELECT * FROM Customers
WHERE Country = 'Germany' OR Country = 'Spain';

NOT Example

The following SQL statement selects all fields from "Customers" where country is NOT "Germany":

Example

SELECT * FROM Customers
WHERE NOT Country = 'Germany';

Combining AND, OR and NOT

You can also combine the AND, OR and NOT operators.

The following SQL statement selects all fields from "Customers" where country is "Germany" AND city must be "Berlin" OR "Stuttgart" (use parenthesis to form complex expressions):

Example

SELECT * FROM Customers
WHERE Country = 'Germany' AND (City = 'Berlin' OR City = 'Stuttgart');

The following SQL statement selects all fields from "Customers" where country is NOT "Germany" and NOT "USA":

Example

SELECT * FROM Customers
WHERE NOT Country = 'Germany' AND NOT Country = 'USA';

Test Yourself With Exercises

Exercise:

Select all records where the City column has the value 'Berlin' and the PostalCode column has the value 12209.

 * FROM Customers
 City = 'Berlin'
  = 12209;

 

 

 
MySQL AND, OR, NOT
 

The MySQL ORDER BY Keyword

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.

ORDER BY Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

Demo Database

Below is a selection from the "Customers" table in the Northwind sample database:

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

ORDER BY Example

The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" column:

Example

SELECT * FROM Customers
ORDER BY Country;


ORDER BY DESC Example

The following SQL statement selects all customers from the "Customers" table, sorted DESCENDING by the "Country" column:

Example

SELECT * FROM Customers
ORDER BY Country DESC;

ORDER BY Several Columns Example

The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" and the "CustomerName" column. This means that it orders by Country, but if some rows have the same Country, it orders them by CustomerName:

Example

SELECT * FROM Customers
ORDER BY Country, CustomerName;

ORDER BY Several Columns Example 2

The following SQL statement selects all customers from the "Customers" table, sorted ascending by the "Country" and descending by the "CustomerName" column:

Example

SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

Test Yourself With Exercises

Exercise:

Select all records from the Customers table, sort the result alphabetically by the column City.

SELECT * FROM Customers
 ;

 

 

 
MySQL ORDER BY
 

The MySQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two ways:

1. Specify both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Demo Database

Below is a selection from the "Customers" table in the Northwind sample database:

CustomerID CustomerName ContactName Address City PostalCode Country
89 White Clover Markets Karl Jablonski 305 - 14th Ave. S. Suite 3B Seattle 98128 USA
90

Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland
91

Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland


INSERT INTO Example

The following SQL statement inserts a new record in the "Customers" table:

Example

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

The selection from the "Customers" table will now look like this:

CustomerID CustomerName ContactName Address City PostalCode Country
89 White Clover Markets Karl Jablonski 305 - 14th Ave. S. Suite 3B Seattle 98128 USA
90

Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland
91

Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland
92 Cardinal Tom B. Erichsen Skagen 21 Stavanger 4006 Norway

Did you notice that we did not insert any number into the CustomerID field?
The CustomerID column is an field and will be generated automatically when a new record is inserted into the table.


Insert Data Only in Specified Columns

It is also possible to only insert data in specific columns.

The following SQL statement will insert a new record, but only insert data in the "CustomerName", "City", and "Country" columns (CustomerID will be updated automatically):

Example

INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

The selection from the "Customers" table will now look like this:

CustomerID CustomerName ContactName Address City PostalCode Country
89 White Clover Markets Karl Jablonski 305 - 14th Ave. S. Suite 3B Seattle 98128 USA
90

Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland
91

Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland
92 Cardinal null null Stavanger null Norway

Test Yourself With Exercises

Exercise:

Insert a new record in the Customers table.

 Customers 
CustomerName, 
Address, 
City, 
PostalCode,
Country
 
'Hekkan Burger',
'Gateveien 15',
'Sandnes',
'4306',
'Norway';

 

 

 
MySQL INSERT INTO

MySQL NULL Values

 

What is a NULL Value?

A field with a NULL value is a field with no value.

If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.

Note: A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!


How to Test for NULL Values?

It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

We will have to use the IS NULL and IS NOT NULL operators instead.

IS NULL Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

IS NOT NULL Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

Demo Database

Below is a selection from the "Customers" table in the Northwind sample database:

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 IS NULL Operator

The IS NULL operator is used to test for empty values (NULL values).

The following SQL lists all customers with a NULL value in the "Address" field:

Example

SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;

Tip: Always use IS NULL to look for NULL values.


The IS NOT NULL Operator

The IS NOT NULL operator is used to test for non-empty values (NOT NULL values).

The following SQL lists all customers with a value in the "Address" field:

Example

SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;

Test Yourself With Exercises

Exercise:

Select all records from the Customers where the PostalCode column is empty.

SELECT * FROM Customers
WHERE   ;

 

 

 
MySQL NULL Values

The MySQL UPDATE Statement

The UPDATE statement is used to modify the existing records in a table.

UPDATE Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Note: Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated!


Demo Database

Below is a selection from the "Customers" table in the Northwind sample database:

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


UPDATE Table

The following SQL statement updates the first customer (CustomerID = 1) with a new contact person and a new city.

Example

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City = 'Frankfurt'
WHERE CustomerID = 1;

The selection from the "Customers" table will now look like this:

CustomerID

CustomerName

ContactName

Address

City

PostalCode

Country

1

Alfreds Futterkiste

Alfred Schmidt

Obere Str. 57

Frankfurt

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



UPDATE Multiple Records

It is the WHERE clause that determines how many records will be updated.

The following SQL statement will update the PostalCode to 00000 for all records where country is "Mexico":

Example

UPDATE Customers
SET PostalCode = 00000
WHERE Country = 'Mexico';

The selection from the "Customers" table will now look like this:

CustomerID

CustomerName

ContactName

Address

City

PostalCode

Country

1

Alfreds Futterkiste

Alfred Schmidt

Obere Str. 57

Frankfurt

12209

Germany

2

Ana Trujillo Emparedados y helados

Ana Trujillo

Avda. de la Constitución 2222

México D.F.

00000

Mexico

3

Antonio Moreno Taquería

MySQL UPDATE

 

The MySQL DELETE Statement

The DELETE statement is used to delete existing records in a table.

DELETE Syntax

DELETE FROM table_name WHERE condition;

Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!


Demo Database

Below is a selection from the "Customers" table in the Northwind sample database:

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


SQL DELETE Example

The following SQL statement deletes the customer "Alfreds Futterkiste" from the "Customers" table:

Example

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

The "Customers" table will now look like this:

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

Delete All Records

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;

The following SQL statement deletes all rows in the "Customers" table, without deleting the table:

Example

DELETE FROM Customers;

Test Yourself With Exercises

Exercise:

Delete all the records from the Customers table where the Country value is 'Norway'.

 Customers
 Country = 'Norway';

 

 

 
MySQL DELETE

The MySQL LIMIT Clause

The LIMIT clause is used to specify the number of records to return.

The LIMIT clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.

LIMIT Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;


Demo Database

Below is a selection from the "Customers" table in the Northwind sample database:

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



MySQL LIMIT Examples

The following SQL statement selects the first three records from the "Customers" table:

ExampleGet your own SQL Server

SELECT * FROM Customers
LIMIT 3;

-

What if we want to select records 4 - 6 (inclusive)?

MySQL provides a way to handle this: by using OFFSET.

The SQL query below says "return only 3 records, start on record 4 (OFFSET 3)":

Example

SELECT * FROM Customers
LIMIT 3 OFFSET 3;

-


ADD a WHERE CLAUSE

The following SQL statement selects the first three records from the "Customers" table, where the country is "Germany":

Example

SELECT * FROM Customers
WHERE Country='Germany'
LIMIT 3;

 

MySQL LIMIT
 

MySQL 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() Syntax

SELECT MIN(column_name)
FROM table_name
WHERE condition;

MAX() Syntax

SELECT MAX(column_name)
FROM table_name
WHERE condition;

Demo Database

Below is a selection from the "Products" table in the Northwind sample database:

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 2 2 48 - 6 oz jars 22
5 Chef Anton's Gumbo Mix 2 2 36 boxes 21.35

MIN() Example

The following SQL statement finds the price of the cheapest product:

Example

SELECT MIN(Price) AS SmallestPrice
FROM Products;


MAX() Example

The following SQL statement finds the price of the most expensive product:

Example

SELECT MAX(Price) AS LargestPrice
FROM Products;

Test Yourself With Exercises

Exercise:

Use the MIN function to select the record with the smallest value of the Price column.

SELECT 
FROM Products;

 

 

 
MySQL MIN and MAX

MySQL COUNT(), AVG() and SUM() Functions

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

COUNT() Syntax

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

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

AVG() Syntax

SELECT AVG(column_name)
FROM table_name
WHERE condition;

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

SUM() Syntax

SELECT SUM(column_name)
FROM table_name
WHERE condition;

Demo Database

Below is a selection from the "Products" table in the Northwind sample database:

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 2 2 48 - 6 oz jars 22
5 Chef Anton's Gumbo Mix 2 2 36 boxes 21.35


COUNT() Example

The following SQL statement finds the number of products:

Example

SELECT COUNT(ProductID)
FROM Products;

Note: NULL values are not counted.


AVG() Example

The following SQL statement finds the average price of all products:

Example

SELECT AVG(Price)
FROM Products;

Note: NULL values are ignored.


Demo Database

Below is a selection from the "OrderDetails" table in the Northwind sample database:

OrderDetailID OrderID ProductID Quantity
1 10248 11 12
2 10248 42 10
3 10248 72 5
4 10249 14 9
5 10249 51 40

SUM() Example

The following SQL statement finds the sum of the "Quantity" fields in the "OrderDetails" table:

Example

SELECT SUM(Quantity)
FROM OrderDetails;

Note: NULL values are ignored.


Test Yourself With Exercises

Exercise:

Use the correct function to return the number of records that have the Price value set to 18.

SELECT (*)
FROM Products
 Price = 18;


The MySQL LIKE Operator

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

There are two wildcards often used in conjunction with the LIKE operator:

  • The percent sign (%) represents zero, one, or multiple characters
  • The underscore sign (_) represents one, single character

The percent sign and the underscore can also be used in combinations!

LIKE Syntax

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

Tip: You can also combine any number of conditions using AND or OR operators.

Here are some examples showing different LIKE operators with '%' and '_' wildcards:

LIKE Operator

Description

WHERE CustomerName LIKE 'a%'

Finds any values that start with "a"

WHERE CustomerName LIKE '%a'

Finds any values that end with "a"

WHERE CustomerName LIKE '%or%'

Finds any values that have "or" in any position

WHERE CustomerName LIKE '_r%'

Finds any values that have "r" in the second position

WHERE CustomerName LIKE 'a_%'

Finds any values that start with "a" and are at least 2 characters in length

WHERE CustomerName LIKE 'a__%'

Finds any values that start with "a" and are at least 3 characters in length

WHERE ContactName LIKE 'a%o'

Finds any values that start with "a" and ends with "o"


Demo Database

The table below shows the complete "Customers" table from the Northwind sample database:

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

10

Bottom-Dollar Marketse

Elizabeth Lincoln

23 Tsawassen Blvd.

Tsawassen

T2F 8M4

Canada

11

B's Beverages

Victoria Ashworth

Fauntleroy Circus

London

EC2 5NT

UK

12

Cactus Comidas para llevar

Patricio Simpson

Cerrito 333

Buenos Aires

1010

Argentina

13

Centro comercial Moctezuma

Francisco Chang

Sierras de Granada 9993

México D.F.

MySQL Wildcards


MySQL Wildcard Characters

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

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

Wildcard Characters in MySQL

Symbol Description Example
% Represents zero or more characters bl% finds bl, black, blue, and blob
_ Represents a single character h_t finds hot, hat, and hit

The wildcards can also be used in combinations!

Here are some examples showing different LIKE operators with '%' and '_' wildcards:

LIKE Operator Description
WHERE CustomerName LIKE 'a%' Finds any values that starts with "a"
WHERE CustomerName LIKE '%a' Finds any values that ends with "a"
WHERE CustomerName LIKE '%or%' Finds any values that have "or" in any position
WHERE CustomerName LIKE '_r%' Finds any values that have "r" in the second position
WHERE CustomerName LIKE 'a_%_%' Finds any values that starts with "a" and are at least 3 characters in length
WHERE ContactName LIKE 'a%o' Finds any values that starts with "a" and ends with "o"

Demo Database

The table below shows the complete "Customers" table from the Northwind sample database:

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
10 Bottom-Dollar Marketse Elizabeth Lincoln 23 Tsawassen Blvd. Tsawassen T2F 8M4 Canada
11 B's Beverages Victoria Ashworth Fauntleroy Circus London EC2 5NT UK
12 Cactus Comidas para llevar Patricio Simpson Cerrito 333 Buenos Aires 1010 Argentina
13 Centro comercial Moctezuma Francisco Chang Sierras de Granada 9993 México D.F. 05022 Mexico
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
16 Consolidated Holdings Elizabeth Brown Berkeley Gardens 12 Brewery London WX1 6LT UK
17 Drachenblut Delikatessend Sven Ottlieb Walserweg 21 Aachen 52066 Germany
18 Du monde entier Janine Labrune 67, rue des Cinquante Otages Nantes 44000 France
19 Eastern Connection Ann Devon 35 King George London WX3 6FW UK
20 Ernst Handel Roland Mendel Kirchgasse 6 Graz 8010 Austria
21 Familia Arquibaldo Aria Cruz Rua Orós, 92 São Paulo 05442-030 Brazil
22 FISSA Fabrica Inter. Salchichas S.A. Diego Roel C/ Moralzarzal, 86 Madrid 28034 Spain
23 Folies gourmandes Martine Rancé 184, chaussée de Tournai Lille 59000 France
24 Folk och fä HB Maria Larsson Åkergatan 24 Bräcke S-844 67 Sweden
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
26 France restauration Carine Schmitt 54, rue Royale Nantes 44000 France
27 Franchi S.p.A. Paolo Accorti Via Monte Bianco 34 Torino 10100 Italy
28 Furia Bacalhau e Frutos do Mar Lino Rodriguez Jardim das rosas n. 32 Lisboa 1675 Portugal
29 Galería del gastrónomo Eduardo Saavedra Rambla de Cataluña, 23 Barcelona 08022 Spain
30 Godos Cocina Típica José Pedro Freyre C/ Romero, 33 Sevilla 41101 Spain
31 Gourmet Lanchonetes André Fonseca Av. Brasil, 442 Campinas 04876-786 Brazil
32 Great Lakes Food Market Howard Snyder 2732 Baker Blvd. Eugene 97403 USA
33 GROSELLA-Restaurante Manuel Pereira 5ª Ave. Los Palos Grandes Caracas 1081 Venezuela
34 Hanari Carnes Mario Pontes Rua do Paço, 67 Rio de Janeiro 05454-876 Brazil
35 HILARIÓN-Abastos Carlos Hernández Carrera 22 con Ave. Carlos Soublette #8-35 San Cristóbal 5022 Venezuela
36 Hungry Coyote Import Store Yoshi Latimer City Center Plaza 516 Main St. Elgin 97827 USA
37 Hungry Owl All-Night Grocers Patricia McKenna 8 Johnstown Road Cork Ireland
38 Island Trading Helen Bennett Garden House Crowther Way Cowes PO31 7PJ UK
39 Königlich Essen Philip Cramer Maubelstr. 90 Brandenburg 14776 Germany
40 La corne d'abondance Daniel Tonini 67, avenue de l'Europe Versailles 78000 France
41 La maison d'Asie Annette Roulet 1 rue Alsace-Lorraine Toulouse 31000 France
42 Laughing Bacchus Wine Cellars Yoshi Tannamuri 1900 Oak St. Vancouver V3F 2K1 Canada
43 Lazy K Kountry Store John Steel 12 Orchestra Terrace Walla Walla 99362 USA
44 Lehmanns Marktstand Renate Messner Magazinweg 7 Frankfurt a.M. 60528 Germany
45 Let's Stop N Shop Jaime Yorres 87 Polk St. Suite 5 San Francisco 94117 USA
46 LILA-Supermercado Carlos González Carrera 52 con Ave. Bolívar #65-98 Llano Largo Barquisimeto 3508 Venezuela
47 LINO-Delicateses Felipe Izquierdo Ave. 5 de Mayo Porlamar I. de Margarita 4980 Venezuela
48 Lonesome Pine Restaurant Fran Wilson 89 Chiaroscuro Rd. Portland 97219 USA
49 Magazzini Alimentari Riuniti Giovanni Rovelli Via Ludovico il Moro 22 Bergamo 24100 Italy
50 Maison Dewey Catherine Dewey Rue Joseph-Bens 532 Bruxelles B-1180 Belgium
51 Mère Paillarde Jean Fresnière 43 rue St. Laurent Montréal H1J 1C3 Canada
52 Morgenstern Gesundkost Alexander Feuer Heerstr. 22 Leipzig 04179 Germany
53 North/South Simon Crowther South House 300 Queensbridge London SW7 1RZ UK
54 Océano Atlántico Ltda. Yvonne Moncada Ing. Gustavo Moncada 8585 Piso 20-A Buenos Aires 1010 Argentina
55 Old World Delicatessen Rene Phillips 2743 Bering St. Anchorage 99508 USA
56 Ottilies Käseladen Henriette Pfalzheim Mehrheimerstr. 369 Köln 50739 Germany
57 Paris spécialités Marie Bertrand 265, boulevard Charonne Paris 75012 France
58 Pericles Comidas clásicas Guillermo Fernández Calle Dr. Jorge Cash 321 México D.F. 05033 Mexico
59 Piccolo und mehr Georg Pipps Geislweg 14 Salzburg 5020 Austria
60 Princesa Isabel Vinhoss Isabel de Castro Estrada da saúde n. 58 Lisboa 1756 Portugal
61 Que Delícia Bernardo Batista Rua da Panificadora, 12 Rio de Janeiro 02389-673 Brazil
62 Queen Cozinha Lúcia Carvalho Alameda dos Canàrios, 891 São Paulo 05487-020 Brazil
63 QUICK-Stop Horst Kloss Taucherstraße 10 Cunewalde 01307 Germany
64 Rancho grande Sergio Gutiérrez Av. del Libertador 900 Buenos Aires 1010 Argentina
65 Rattlesnake Canyon Grocery Paula Wilson 2817 Milton Dr. Albuquerque 87110 USA
66 Reggiani Caseifici Maurizio Moroni Strada Provinciale 124 Reggio Emilia 42100 Italy
67 Ricardo Adocicados Janete Limeira Av. Copacabana, 267 Rio de Janeiro 02389-890 Brazil
68 Richter Supermarkt Michael Holz Grenzacherweg 237 Genève 1203 Switzerland
69 Romero y tomillo Alejandra Camino Gran Vía, 1 Madrid 28001 Spain
70 Santé Gourmet Jonas Bergulfsen Erling Skakkes gate 78 Stavern 4110 Norway
71 Save-a-lot Markets Jose Pavarotti 187 Suffolk Ln. Boise 83720 USA
72 Seven Seas Imports Hari Kumar 90 Wadhurst Rd. London OX15 4NB UK
73 Simons bistro Jytte Petersen Vinbæltet 34 København 1734 Denmark
74 Spécialités du monde Dominique Perrier 25, rue Lauriston Paris 75016 France
75 Split Rail Beer & Ale Art Braunschweiger P.O. Box 555 Lander 82520 USA
76 Suprêmes délices Pascale Cartrain Boulevard Tirou, 255 Charleroi B-6000 Belgium
77 The Big Cheese Liz Nixon 89 Jefferson Way Suite 2 Portland 97201 USA
78 The Cracker Box Liu Wong 55 Grizzly Peak Rd. Butte 59801 USA
79 Toms Spezialitäten Karin Josephs Luisenstr. 48 Münster 44087 Germany
80 Tortuga Restaurante Miguel Angel Paolino Avda. Azteca 123 México D.F. 05033 Mexico
81 Tradição Hipermercados Anabela Domingues Av. Inês de Castro, 414 São Paulo 05634-030 Brazil
82 Trail's Head Gourmet Provisioners Helvetius Nagy 722 DaVinci Blvd. Kirkland 98034 USA
83 Vaffeljernet Palle Ibsen Smagsløget 45 Århus 8200 Denmark
84 Victuailles en stock Mary Saveley 2, rue du Commerce Lyon 69004 France
85 Vins et alcools Chevalier Paul Henriot 59 rue de l'Abbaye Reims 51100 France
86 Die Wandernde Kuh Rita Müller Adenauerallee 900 Stuttgart 70563 Germany
87 Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland
88 Wellington Importadora Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil
89 White Clover Markets Karl Jablonski 305 - 14th Ave. S. Suite 3B Seattle 98128 USA
90 Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland
91 Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland


Using the % Wildcard

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

Example

SELECT * FROM Customers
WHERE City LIKE 'ber%';

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

Example

SELECT * FROM Customers
WHERE City LIKE '%es%';

Using the _ Wildcard

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

Example

SELECT * FROM Customers
WHERE City LIKE '_ondon';

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';

Test Yourself With Exercises

Exercise:

Select all records where the second letter of the City is an "a".

SELECT * FROM Customers
WHERE City LIKE '%';



The MySQL 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.

IN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

or:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

Demo Database

The table below shows the complete "Customers" table from the Northwind sample database:

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
10 Bottom-Dollar Marketse Elizabeth Lincoln 23 Tsawassen Blvd. Tsawassen T2F 8M4 Canada
11 B's Beverages Victoria Ashworth Fauntleroy Circus London EC2 5NT UK
12 Cactus Comidas para llevar Patricio Simpson Cerrito 333 Buenos Aires 1010 Argentina
13 Centro comercial Moctezuma Francisco Chang Sierras de Granada 9993 México D.F. 05022 Mexico
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
16 Consolidated Holdings Elizabeth Brown Berkeley Gardens 12 Brewery London WX1 6LT UK
17 Drachenblut Delikatessend Sven Ottlieb Walserweg 21 Aachen 52066 Germany
18 Du monde entier Janine Labrune 67, rue des Cinquante Otages Nantes 44000 France
19 Eastern Connection Ann Devon 35 King George London WX3 6FW UK
20 Ernst Handel Roland Mendel Kirchgasse 6 Graz 8010 Austria
21 Familia Arquibaldo Aria Cruz Rua Orós, 92 São Paulo 05442-030 Brazil
22 FISSA Fabrica Inter. Salchichas S.A. Diego Roel C/ Moralzarzal, 86 Madrid 28034 Spain
23 Folies gourmandes Martine Rancé 184, chaussée de Tournai Lille 59000 France
24 Folk och fä HB Maria Larsson Åkergatan 24 Bräcke S-844 67 Sweden
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
26 France restauration Carine Schmitt 54, rue Royale Nantes 44000 France
27 Franchi S.p.A. Paolo Accorti Via Monte Bianco 34 Torino 10100 Italy
28 Furia Bacalhau e Frutos do Mar Lino Rodriguez Jardim das rosas n. 32 Lisboa 1675 Portugal
29 Galería del gastrónomo Eduardo Saavedra Rambla de Cataluña, 23 Barcelona 08022 Spain
30 Godos Cocina Típica José Pedro Freyre C/ Romero, 33 Sevilla 41101 Spain
31 Gourmet Lanchonetes André Fonseca Av. Brasil, 442 Campinas 04876-786 Brazil
32 Great Lakes Food Market Howard Snyder 2732 Baker Blvd. Eugene 97403 USA
33 GROSELLA-Restaurante Manuel Pereira 5ª Ave. Los Palos Grandes Caracas 1081 Venezuela
34 Hanari Carnes Mario Pontes Rua do Paço, 67 Rio de Janeiro 05454-876 Brazil
35 HILARIÓN-Abastos Carlos Hernández Carrera 22 con Ave. Carlos Soublette #8-35 San Cristóbal 5022 Venezuela
36 Hungry Coyote Import Store Yoshi Latimer City Center Plaza 516 Main St. Elgin 97827 USA
37 Hungry Owl All-Night Grocers Patricia McKenna 8 Johnstown Road Cork Ireland
38 Island Trading Helen Bennett Garden House Crowther Way Cowes PO31 7PJ UK
39 Königlich Essen Philip Cramer Maubelstr. 90 Brandenburg 14776 Germany
40 La corne d'abondance Daniel Tonini 67, avenue de l'Europe Versailles 78000 France
41 La maison d'Asie Annette Roulet 1 rue Alsace-Lorraine Toulouse 31000 France
42 Laughing Bacchus Wine Cellars Yoshi Tannamuri 1900 Oak St. Vancouver V3F 2K1 Canada
43 Lazy K Kountry Store John Steel 12 Orchestra Terrace Walla Walla 99362 USA
44 Lehmanns Marktstand Renate Messner Magazinweg 7 Frankfurt a.M. 60528 Germany
45 Let's Stop N Shop Jaime Yorres 87 Polk St. Suite 5 San Francisco 94117 USA
46 LILA-Supermercado Carlos González Carrera 52 con Ave. Bolívar #65-98 Llano Largo Barquisimeto 3508 Venezuela
47 LINO-Delicateses Felipe Izquierdo Ave. 5 de Mayo Porlamar I. de Margarita 4980 Venezuela
48 Lonesome Pine Restaurant Fran Wilson 89 Chiaroscuro Rd. Portland 97219 USA
49 Magazzini Alimentari Riuniti Giovanni Rovelli Via Ludovico il Moro 22 Bergamo 24100 Italy
50 Maison Dewey Catherine Dewey Rue Joseph-Bens 532 Bruxelles B-1180 Belgium
51 Mère Paillarde Jean Fresnière 43 rue St. Laurent Montréal H1J 1C3 Canada
52 Morgenstern Gesundkost Alexander Feuer Heerstr. 22 Leipzig 04179 Germany
53 North/South Simon Crowther South House 300 Queensbridge London SW7 1RZ UK
54 Océano Atlántico Ltda. Yvonne Moncada Ing. Gustavo Moncada 8585 Piso 20-A Buenos Aires 1010 Argentina
55 Old World Delicatessen Rene Phillips 2743 Bering St. Anchorage 99508 USA
56 Ottilies Käseladen Henriette Pfalzheim Mehrheimerstr. 369 Köln 50739 Germany
57 Paris spécialités Marie Bertrand 265, boulevard Charonne Paris 75012 France
58 Pericles Comidas clásicas Guillermo Fernández Calle Dr. Jorge Cash 321 México D.F. 05033 Mexico
59 Piccolo und mehr Georg Pipps Geislweg 14 Salzburg 5020 Austria
60 Princesa Isabel Vinhoss Isabel de Castro Estrada da saúde n. 58 Lisboa 1756 Portugal
61 Que Delícia Bernardo Batista Rua da Panificadora, 12 Rio de Janeiro 02389-673 Brazil
62 Queen Cozinha Lúcia Carvalho Alameda dos Canàrios, 891 São Paulo 05487-020 Brazil
63 QUICK-Stop Horst Kloss Taucherstraße 10 Cunewalde 01307 Germany
64 Rancho grande Sergio Gutiérrez Av. del Libertador 900 Buenos Aires 1010 Argentina
65 Rattlesnake Canyon Grocery Paula Wilson 2817 Milton Dr. Albuquerque 87110 USA
66 Reggiani Caseifici Maurizio Moroni Strada Provinciale 124 Reggio Emilia 42100 Italy
67 Ricardo Adocicados Janete Limeira Av. Copacabana, 267 Rio de Janeiro 02389-890 Brazil
68 Richter Supermarkt Michael Holz Grenzacherweg 237 Genève 1203 Switzerland
69 Romero y tomillo Alejandra Camino Gran Vía, 1 Madrid 28001 Spain
70 Santé Gourmet Jonas Bergulfsen Erling Skakkes gate 78 Stavern 4110 Norway
71 Save-a-lot Markets Jose Pavarotti 187 Suffolk Ln. Boise 83720 USA
72 Seven Seas Imports Hari Kumar 90 Wadhurst Rd. London OX15 4NB UK
73 Simons bistro Jytte Petersen Vinbæltet 34 København 1734 Denmark
74 Spécialités du monde Dominique Perrier 25, rue Lauriston Paris 75016 France
75 Split Rail Beer & Ale Art Braunschweiger P.O. Box 555 Lander 82520 USA
76 Suprêmes délices Pascale Cartrain Boulevard Tirou, 255 Charleroi B-6000 Belgium
77 The Big Cheese Liz Nixon 89 Jefferson Way Suite 2 Portland 97201 USA
78 The Cracker Box Liu Wong 55 Grizzly Peak Rd. Butte 59801 USA
79 Toms Spezialitäten Karin Josephs Luisenstr. 48 Münster 44087 Germany
80 Tortuga Restaurante Miguel Angel Paolino Avda. Azteca 123 México D.F. 05033 Mexico
81 Tradição Hipermercados Anabela Domingues Av. Inês de Castro, 414 São Paulo 05634-030 Brazil
82 Trail's Head Gourmet Provisioners Helvetius Nagy 722 DaVinci Blvd. Kirkland 98034 USA
83 Vaffeljernet Palle Ibsen Smagsløget 45 Århus 8200 Denmark
84 Victuailles en stock Mary Saveley 2, rue du Commerce Lyon 69004 France
85 Vins et alcools Chevalier Paul Henriot 59 rue de l'Abbaye Reims 51100 France
86 Die Wandernde Kuh Rita Müller Adenauerallee 900 Stuttgart 70563 Germany
87 Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland
88 Wellington Importadora Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil
89 White Clover Markets Karl Jablonski 305 - 14th Ave. S. Suite 3B Seattle 98128 USA
90 Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland
91 Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland


IN Operator Examples

The following SQL statement selects all customers that are located in "Germany", "France" or "UK":

Example

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

The following SQL statement selects all customers that are NOT located in "Germany", "France" or "UK":

Example

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

The following SQL statement selects all customers that are from the same countries as the suppliers:

Example

SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);

Test Yourself With Exercises

Exercise:

Use the IN operator to select all the records where Country is either "Norway" or "France".

SELECT * FROM Customers
 
'France';



The MySQL BETWEEN Operator

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.

BETWEEN Syntax

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

Demo Database

Below is a selection from the "Products" table in the Northwind sample database:

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 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;


NOT BETWEEN 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;

BETWEEN with IN Example

The following SQL statement selects all products with a price between 10 and 20. In addition; do not show products with a CategoryID of 1,2, or 3:

Example

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

BETWEEN Text Values Example

The following SQL statement selects all products with a ProductName between "Carnarvon Tigers" and "Mozzarella di Giovanni":

Example

SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

The following SQL statement selects all products with a ProductName between "Carnarvon Tigers" and "Chef Anton's Cajun Seasoning":

Example

SELECT * FROM Products
WHERE ProductName BETWEEN "Carnarvon Tigers" AND "Chef Anton's Cajun Seasoning"
ORDER BY ProductName;

NOT BETWEEN Text Values Example

The following SQL statement selects all products with a ProductName not between "Carnarvon Tigers" and "Mozzarella di Giovanni":

Example

SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

Sample Table

Below is a selection from the "Orders" table in the Northwind sample database:

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 Dates Example

The following SQL statement selects all orders with an OrderDate between '01-July-1996' and '31-July-1996':

Example

SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';

Test Yourself With Exercises

Exercise:

Use the BETWEEN operator to select all the records where the value of the Price column is between 10 and 20.

SELECT * FROM Products
WHERE Price 
;


MySQL Aliases

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.

Alias Column Syntax

SELECT column_name AS alias_name
FROM table_name;

Alias Table Syntax

SELECT column_name(s)
FROM table_name AS alias_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

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

And a selection from the "Orders" table:

OrderID

CustomerID

EmployeeID

OrderDate

ShipperID

10354

58

8

1996-11-14

3

10355

4

6

1996-11-15

1

10356

86

6

1996-11-18

2



Alias for Columns Examples

The following SQL statement creates two aliases, one for the CustomerID column and one for the CustomerName column:

ExampleGet your own SQL Server

SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

-

The following SQL statement creates two aliases, one for the CustomerName column and one for the ContactName column. Note: Single or double quotation marks are required if the alias name contains spaces:

Example

SELECT CustomerName AS Customer, ContactName AS "Contact Person"
FROM Customers;

-

The following SQL statement creates an alias named "Address" that combine four columns (Address, PostalCode, City and Country):

Example

SELECT CustomerName, CONCAT_WS(', ', Address, PostalCode, City, Country) AS Address
FROM Customers;

-


Alias for Tables Example

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 use aliases to make the SQL shorter):

Example

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;

-

The following SQL statement is the same as above, but without aliases:

Example

SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName='Around the Horn' AND Customers.CustomerID=Orders.CustomerID;

-

Aliases can be useful when:

  • There are more than one table involved in a query
  • Functions are used in the query
  • Column names are big or not very readable
  • Two or more columns are combined together

 

MySQL Joining Tables

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.

Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables:

Example

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

-

and 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



Supported Types of Joins in MySQL

  • INNER JOIN: Returns records that have matching values in both tables
  • LEFT JOIN: Returns all records from the left table, and the matched records from the right table
  • RIGHT JOIN: Returns all records from the right table, and the matched records from the left table
  • CROSS JOIN: Returns all records from both tables


MySQL INNER JOIN  MySQL LEFT JOIN  MySQL RIGHT JOIN  MySQL CROSS JOIN


MySQL INNER JOIN Keyword

The INNER JOIN keyword selects records that have matching values in both tables.

MySQL INNER JOIN

INNER JOIN Syntax

SELECT column_name(s)
FROM table1
INNER 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 "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 "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


MySQL INNER JOIN Example

The following SQL statement selects all orders with customer information:

Example

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

Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the "Orders" table that do not have matches in "Customers", these orders will not be shown!


JOIN Three Tables

The following SQL statement selects all orders with customer and shipper information:

Example

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

Test Yourself With Exercises

Exercise:

Choose the correct JOIN clause to select all records from the two tables where there is a match in both tables.

SELECT *
FROM Orders

ON Orders.CustomerID=
Customers.CustomerID;


MySQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records (if any) from the right table (table2).

MySQL LEFT JOIN

LEFT JOIN Syntax

SELECT column_name(s)
FROM table1
LEFT 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

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


MySQL LEFT JOIN Example

The following SQL statement will select 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;

-

Note: The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).

 

 

MySQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records (if any) from the left table (table1).

MySQL RIGHT JOIN

RIGHT JOIN Syntax

SELECT column_name(s)
FROM table1
RIGHT 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 "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
1 Davolio Nancy 12/8/1968 EmpID1.pic
2 Fuller Andrew 2/19/1952 EmpID2.pic
3 Leverling Janet 8/30/1963 EmpID3.pic

MySQL RIGHT JOIN Example

The following SQL statement will return all employees, and any orders they might have placed:

Example

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

Note: The RIGHT JOIN keyword returns all records from the right table (Employees), even if there are no matches in the left table (Orders).



Test Yourself With Exercises

Exercise:

Choose the correct JOIN clause to select all the records from the Customers table plus all the matches in the Orders table.

SELECT *
FROM Orders

ON Orders.CustomerID=
Customers.CustomerID;

 

 

 

SQL CROSS JOIN Keyword

The CROSS JOIN keyword returns all records from both tables (table1 and table2).

MySQL CROSS JOIN

CROSS JOIN Syntax

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

Note: CROSS JOIN can potentially return very large result-sets!


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



MySQL CROSS JOIN Example

The following SQL statement selects all customers, and all orders:

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
CROSS JOIN Orders;

-

Note: The CROSS JOIN keyword returns all matching records from both tables whether the other table matches or not. So, 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.

If you add a WHERE clause (if table1 and table2 has a relationship), the CROSS JOIN will produce the same result as the INNER JOIN clause:

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
CROSS JOIN Orders
WHERE Customers.CustomerID=Orders.CustomerID;

-

 

MySQL Self Join

A self join is a regular join, but the table is joined with itself.

Self Join Syntax

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

T1 and T2 are different table aliases for the same table.


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


MySQL Self Join Example

The following SQL statement matches customers that are from the same city:

Example

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;

 

The MySQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

  • Every SELECT statement within UNION must have the same number of columns
  • The columns must also have similar data types
  • The columns in every SELECT statement must also be in the same order

UNION Syntax

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

UNION ALL Syntax

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Note: The column names in the result-set are usually equal to the column names in the first SELECT statement.


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 "Suppliers" table:

SupplierID

SupplierName

ContactName

Address

City

PostalCode

Country

1

Exotic Liquid

Charlotte Cooper

49 Gilbert St.

London

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 returns the cities (only distinct values) from both the "Customers" and the "Suppliers" table:

Example

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

-

Note: If some customers or suppliers have the same city, each city will only be listed once, because UNION selects only distinct values. Use UNION ALL to also select duplicate values!


SQL UNION ALL Example

The following SQL statement returns the cities (duplicate values also) from both the "Customers" and the "Suppliers" table:

Example

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

-


SQL UNION With WHERE

The following SQL statement returns the German cities (only distinct values) from both the "Customers" and the "Suppliers" table:

Example

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

The MySQL GROUP BY Statement

The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".

The GROUP BY statement is often used with aggregate functions (COUNT()MAX()MIN()SUM()AVG()) to group the result-set by one or more columns.

GROUP BY Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);


Demo Database

Below is a selection from the "Customers" table in the Northwind sample database:

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



MySQL GROUP BY Examples

The following SQL statement lists the number of customers in each country:

ExampleGet your own SQL Server

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

-

The following SQL statement lists the number of customers in each country, sorted high to low:

Example

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

-


Demo Database

Below is a selection from the "Orders" table in the Northwind sample database:

OrderID

CustomerID

EmployeeID

OrderDate

ShipperID

10248

90

5

1996-07-04

3

10249

81

6

1996-07-05

1

10250

34

4

1996-07-08

2

And a selection from the "Shippers" table:

ShipperID

ShipperName

1

Speedy Express

2

United Package

3

Federal Shipping


GROUP BY With JOIN Example

The following SQL statement lists the number of orders sent by each shipper:

Example

SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;

 


The MySQL HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

HAVING Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Demo Database

Below is a selection from the "Customers" table in the Northwind sample database:

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

MySQL HAVING Examples

The following SQL statement lists the number of customers in each country. Only include countries with more than 5 customers:

Example

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

The following SQL statement lists the number of customers in each country, sorted high to low (Only include countries with more than 5 customers):

Example

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;


Demo Database

Below is a selection from the "Orders" table in the Northwind sample database:

OrderID CustomerID EmployeeID OrderDate ShipperID
10248 90 5 1996-07-04 3
10249 81 6 1996-07-05 1
10250 34 4 1996-07-08 2

And a selection from the "Employees" table:

EmployeeID LastName FirstName BirthDate Photo Notes
1 Davolio Nancy 1968-12-08 EmpID1.pic Education includes a BA....
2 Fuller Andrew 1952-02-19 EmpID2.pic Andrew received his BTS....
3 Leverling Janet 1963-08-30 EmpID3.pic Janet has a BS degree....

More HAVING Examples

The following SQL statement lists the employees that have registered more than 10 orders:

Example

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM (Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

The following SQL statement lists if the employees "Davolio" or "Fuller" have registered more than 25 orders:

Example

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE LastName = 'Davolio' OR LastName = 'Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;

The MySQL EXISTS Operator

The EXISTS operator is used to test for the existence of any record in a subquery.

The EXISTS operator returns TRUE if the subquery returns one or more records.

EXISTS Syntax

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(
SELECT column_name FROM table_name WHERE condition);


Demo Database

Below is a selection from the "Products" table in the Northwind sample database:

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

2

2

48 - 6 oz jars

22

5

Chef Anton's Gumbo Mix

2

2

36 boxes

21.35

And a selection from the "Suppliers" table:

SupplierID

SupplierName

ContactName

Address

City

PostalCode

Country

1

Exotic Liquid

Charlotte Cooper

49 Gilbert St.

London

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

4

Tokyo Traders

Yoshi Nagase

9-8 Sekimai Musashino-shi

Tokyo

100

Japan



MySQL EXISTS Examples

The following SQL statement returns TRUE and lists the suppliers with a product price less than 20:

Example

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

-

The following SQL statement returns TRUE and lists the suppliers with a product price equal to 22:

Example

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price = 22);

-

 

The MySQL ANY and ALL Operators

The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values.


The ANY Operator

The ANY operator:

  • returns a boolean value as a result
  • returns TRUE if ANY of the subquery values meet the condition

ANY means that the condition will be true if the operation is true for any of the values in the range.

ANY Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
  (
SELECT column_name
  
FROM table_name
  
WHERE condition);

Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).


The ALL Operator

The ALL operator:

  • returns a boolean value as a result
  • returns TRUE if ALL of the subquery values meet the condition
  • is used with SELECTWHERE and HAVING statements

ALL means that the condition will be true only if the operation is true for all values in the range.

ALL Syntax With SELECT

SELECT ALL column_name(s)
FROM table_name
WHERE condition;

ALL Syntax With WHERE or HAVING

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
  (
SELECT column_name
  
FROM table_name
  
WHERE condition);

Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).


Demo Database

Below is a selection from the "Products" table in the Northwind sample database:

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

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

9

Mishi Kobe Niku

4

6

18 - 500 g pkgs.

97

And a selection from the "OrderDetails" table:

OrderDetailID

OrderID

ProductID

Quantity