SQL

1º SQL INNER JOIN Keyword

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.

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

Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

Ana Trujillo Emparedados y Ana Trujillo Avda. de la México 05021 Mexico

helados Constitución 2222 D.F.

Antonio Moreno Taquería Antonio Mataderos 2312 México 05023 Mexico

Moreno D.F.

And a selection from the "Orders" table:

OrderID CustomerID EmployeeID OrderDate Sh

10308 2 7 1996-09-18 3

10309 37 3 1996-09-19 1

10310 77 8 1996-09-20 2

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;

2º SQL BETWEEN Operator

« Previous
Next Chapter »

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;
Demo Database
In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Products" table:

ProductID ProductName SupplierID CategoryID Unit

1 Chais 1 1 10 boxes x 20 bags

2 Chang 1 1 24 - 12 oz bottles

3 Aniseed Syrup 1 2 12 - 550 ml bottles

4 Chef Anton's Cajun Seasoning 1 2 48 - 6 oz jars

5 Chef Anton's Gumbo Mix 1 2 36 boxes

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;

Try it yourself »

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;

Try it yourself »

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

Try it yourself »
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';

Try it yourself »

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

Try it yourself »

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#;
Try it yourself »

Notice that the BETWEEN operator can produce different result in different databases!
In some databases, BETWEEN selects fields that are between and excluding the test values.
In other databases, BETWEEN selects fields that are between and including the test values.
And in other databases, BETWEEN selects fields between the test values, including the first test value and
excluding the last test value.

Therefore: Check how your database treats the BETWEEN operator!

3º SQL Joins
« Previous
Next Chapter »

SQL joins are used to combine rows from two or more tables.

SQL JOIN
An SQL JOIN clause is used to combine rows from two or more tables, based on a common
field between them.

The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN
return all rows from multiple tables where the join condition is met.

Let's look at a selection from the "Orders" table:

OrderID CustomerID OrderDate

10308 2 1996-09-18

10309 37 1996-09-19

10310 77 1996-09-20

Then, have a look at a selection from the "Customers" table:

CustomerID CustomerName ContactName Coun

1 Alfreds Futterkiste Maria Anders Germ

2 Ana Trujillo Emparedados y helados Ana Trujillo Mexi

3 Antonio Moreno Taquería Antonio Moreno Mexi

Notice that the "CustomerID" column in the "Orders" table refers to the customer in the
"Customers" table. The relationship between the two tables above is the "CustomerID"
column.

Then, if we run the following SQL statement (that contains an INNER JOIN):
Example
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

Try it yourself »

it will produce something like this:

OrderID CustomerName Order

10308 Ana Trujillo Emparedados y helados 9/18/

10365 Antonio Moreno Taquería 11/27

10383 Around the Horn 12/16

10355 Around the Horn 11/15

10278 Berglunds snabbköp 8/12/

Different SQL JOINs

Before we continue with examples, we will list the types the different SQL JOINs you can
use:

 INNER JOIN: Returns all rows when there is at least one match in BOTH tables
 LEFT JOIN: Return all rows from the left table, and the matched rows from the right
table
 RIGHT JOIN: Return all rows from the right table, and the matched rows from the
left table
 FULL JOIN: Return all rows when there is a match in ONE of the tables

4º SQL Aliases
« Previous
Next Chapter »

SQL aliases are used to temporarily rename a table or a column heading.

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;

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 PostalCo

2 Ana Trujillo Emparedados y Ana Trujillo Avda. de la Constitución México 05021

helados 2222 D.F.

3 Antonio Moreno Taquería Antonio Mataderos 2312 México 05023

Moreno D.F.

4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP

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 Example for Table Columns

The following SQL statement specifies two aliases, one for the CustomerName column and
one for the ContactName column. Tip: It require double quotation marks or square brackets
if the column name contains spaces:

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

Try it yourself »

In the following SQL statement we combine four columns (Address, City, PostalCode, and
Country) and create an alias named "Address":

Example
SELECT CustomerName, Address+', '+City+', '+PostalCode+', '+Country AS
Address
FROM Customers;

Try it yourself »

Note: To get the SQL statement above to work in MySQL use the following:

SELECT CustomerName, CONCAT(Address,', ',City,', ',PostalCode,', ',Country) AS

Address
FROM Customers;

Alias Example for Tables

The following SQL statement selects all the orders from the customer with CustomerID=4
(Around the Horn). We use the "Customers" and "Orders" tables, and give them the table
aliases of "c" and "o" respectively (Here we have used aliases to make the SQL shorter):

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;

Try it yourself »

The same SQL statement without aliases:

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

Try it yourself »

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

5º SQL SELECT TOP Clause

 « Previous
 Next Chapter »


 The SQL SELECT TOP Clause

 The SELECT TOP clause is used to specify the number of records to return.
  The SELECT TOP clause can be very useful on large tables with thousands of records.
Returning a large number of records can impact on performance.
 Note: Not all database systems support the 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;
 Oracle Syntax
 SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;
 Example
 SELECT *
FROM Persons
WHERE ROWNUM <=5;



 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 PostalCod

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209

2 Ana Trujillo Emparedados y Ana Trujillo Avda. de la Constitución México 05021

helados 2222 D.F.

3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México 05023

D.F.

4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP

5 Berglunds snabbköp Christina Berguvsvägen 8 Luleå S-958 22

Berglund




 SQL SELECT TOP Example

 The following SQL statement selects the two first records from the "Customers"
table:
 Example
  SELECT TOP 2 * FROM Customers;

Try it yourself »



 SQL SELECT TOP PERCENT Example

 The following SQL statement selects the first 50% of the records from the
"Customers" table:
 Example

 SELECT TOP 50 PERCENT * FROM Customers;

6º SQL LIKE Operator

« Previous
Next Chapter »

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

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;

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 PostalCod

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209

2 Ana Trujillo Emparedados y Ana Trujillo Avda. de la Constitución México 05021

helados 2222 D.F.

3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México 05023

D.F.
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP

5 Berglunds snabbköp Christina Berguvsvägen 8 Luleå S-958 22

Berglund

SQL LIKE Operator Examples

The following SQL statement selects all customers with a City starting with the letter "s":

Example
SELECT * FROM Customers
WHERE City LIKE 's%';

Try it yourself »

Tip: The "%" sign is used to define wildcards (missing letters) both before and after the
pattern. You will learn more about wildcards in the next chapter.

The following SQL statement selects all customers with a City ending with the letter "s":

Example
SELECT * FROM Customers
WHERE City LIKE '%s';

Try it yourself »

The following SQL statement selects all customers with a Country containing the pattern
"land":

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

Try it yourself »

Using the NOT keyword allows you to select records that does NOT match the pattern.

The following SQL statement selects all customers with a Country NOT containing the pattern
"land":

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

7º SQL Wildcards
« Previous
 Next Chapter »

A wildcard character can be used to substitute for any other character(s) in a string.

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] Matches only a character NOT specified within the brackets

or
[!charlist]

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 PostalCod

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209

2 Ana Trujillo Emparedados y Ana Trujillo Avda. de la Constitución México 05021

helados 2222 D.F.

3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México 05023

D.F.

4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP

5 Berglunds snabbköp Christina Berguvsvägen 8 Luleå S-958 22

Berglund

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

Try it yourself »

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

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

Try it yourself »

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

Try it yourself »

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

Try it yourself »

Using the SQL [charlist] Wildcard

The following SQL statement selects all customers with a City starting with "b", "s", or "p":

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

Try it yourself »

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

Try it yourself »
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]%';

Try it yourself »

8º SQL IN Operator
« Previous
Next Chapter »

The IN 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,...);

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 PostalCod

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209

2 Ana Trujillo Emparedados y Ana Trujillo Avda. de la Constitución México 05021

helados 2222 D.F.

3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México 05023

D.F.

4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP

5 Berglunds snabbköp Christina Berguvsvägen 8 Luleå S-958 22

Berglund

IN Operator Example
The following SQL statement selects all customers with a City of "Paris" or "London":

Example
SELECT * FROM Customers
WHERE City IN ('Paris','London');

9º SQL LEFT JOIN Keyword

« Previous
Next Chapter »

SQL LEFT JOIN Keyword

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 PostalCod

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209

2 Ana Trujillo Emparedados y Ana Trujillo Avda. de la México 05021

helados Constitución 2222 D.F.

3 Antonio Moreno Taquería Antonio Mataderos 2312 México 05023

Moreno D.F.

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;

Try it yourself »

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

10º SQL RIGHT JOIN Keyword

« Previous
Next Chapter »

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 an

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;

Try it yourself »

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

11º SQL FULL OUTER JOIN Keyword

« Previous
Next Chapter »

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 PostalCod

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209

2 Ana Trujillo Emparedados y Ana Trujillo Avda. de la México 05021

 helados Constitución 2222 D.F.

3 Antonio Moreno Taquería Antonio Mataderos 2312 México 05023

Moreno D.F.

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

Note: The FULL OUTER JOIN keyword returns all the rows from the left table (Customers),
and all the rows from the right table (Orders). If there are rows in "Customers" that do not
have matches in "Orders", or if there are rows in "Orders" that do not have matches in
"Customers", those rows will be listed as well.

12º SQL UNION Operator

The SQL UNION operator combines the result of two or more SELECT statements.
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 PostalCod

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209

2 Ana Trujillo Emparedados y Ana Trujillo Avda. de la México 05021

helados Constitución 2222 D.F.

3 Antonio Moreno Taquería Antonio Mataderos 2312 México 05023

Moreno D.F.

And a selection from the "Suppliers" table:

SupplierID SupplierName ContactName Address City PostalCod

1 Exotic Liquid Charlotte Cooper 49 Gilbert St. Londona EC1 4SD

2 New Orleans Cajun Delights Shelley Burke P.O. Box 78934 New Orleans 70117

3 Grandma Kelly's Homestead Regina Murphy 707 Oxford Rd. Ann Arbor 48104
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;

Try it yourself »

Note: UNION cannot be used to list ALL cities from the two tables. If several customers and
suppliers share the same city, each city will only be listed once. UNION selects only distinct
values. Use UNION ALL to also select duplicate values!

SQL UNION ALL Example

The following SQL statement uses UNION ALL to select all (duplicate values also) cities from
the "Customers" and "Suppliers" tables:

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

13º SQL INSERT INTO SELECT Statement

« Previous
Next Chapter »

With SQL, you can copy information from one table into another.

The INSERT INTO SELECT statement copies data from one table and inserts it into an
existing table.

The SQL INSERT INTO SELECT Statement

The INSERT INTO SELECT statement selects data from one table and inserts it into an
existing table. Any existing rows in the target table are unaffected.

SQL INSERT INTO SELECT Syntax

We can copy all columns from one table to another, existing table:
INSERT INTO table2
SELECT * FROM table1;

Or we can copy only the columns we want to into another, existing table:

INSERT INTO table2

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

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 PostalCod

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209

2 Ana Trujillo Emparedados y Ana Trujillo Avda. de la México 05021

helados Constitución 2222 D.F.

3 Antonio Moreno Taquería Antonio Mataderos 2312 México 05023

Moreno D.F.

And a selection from the "Suppliers" table:

SupplierID SupplierName ContactName Address City Postal Country Pho

Code

1 Exotic Liquid Charlotte 49 Gilbert St. Londona EC1 4SD UK (17

Cooper 222

2 New Orleans Cajun Shelley Burke P.O. Box New 70117 USA (10
Delights 78934 Orleans 482

3 Grandma Kelly's Regina Murphy 707 Oxford Ann Arbor 48104 USA (31
Homestead Rd. 573

SQL INSERT INTO SELECT Examples

Copy only a few columns from "Suppliers" into "Customers":

Example
INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers;

Try it yourself »

Copy only the German suppliers into "Customers":

Example
INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers
WHERE Country='Germany';

14º SQL CREATE DATABASE Statement

« Previous
Next Chapter »

The SQL CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a database.

SQL CREATE DATABASE Syntax

CREATE DATABASE dbname;

SQL CREATE DATABASE Example

The following SQL statement creates a database called "my_db":

CREATE DATABASE my_db;

Database tables can be added with the CREATE TABLE statement.

15º SQL CREATE TABLE Statement

« Previous
Next Chapter »

The SQL CREATE TABLE Statement

The CREATE TABLE statement is used to create a table in a database.

Tables are organized into rows and columns; and each table must have a name.

SQL CREATE TABLE Syntax

CREATE TABLE table_name

(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);
The column_name parameters specify the names of the columns of the table.

The data_type parameter specifies what type of data the column can hold (e.g. varchar,
integer, decimal, date, etc.).

The size parameter specifies the maximum length of the column of the table.

Tip: For an overview of the data types available in MS Access, MySQL, and SQL Server, go to
our complete Data Types Reference.

SQL CREATE TABLE Example

Now we want to create a table called "Persons" that contains five columns: PersonID,
LastName, FirstName, Address, and City.

We use the following CREATE TABLE statement:

Example
CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

Try it yourself »

The PersonID column is of type int and will hold an integer.

The LastName, FirstName, Address, and City columns are of type varchar and will hold
characters, and the maximum length for these fields is 255 characters.

The empty "Persons" table will now look like this:

PersonID LastName FirstName Address C

Tip: The empty table can be filled with data with the INSERT INTO statement.