LOCAL TEMPORARY TABLES

This SQL tutorial explains how to create SQL LOCAL TEMPORARY tables with syntax and examples.

DESCRIPTION

SQL LOCAL TEMPORARY TABLES are distinct within modules and embedded SQL programs within SQL sessions.

SYNTAX

The syntax for SQL DECLARE LOCAL TEMPORARY TABLE is:

DECLARE LOCAL TEMPORARY TABLE table_name
( column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

Parameters or Arguments

table_name

The name of the local temporary table that you wish to create.

column1, column2

The columns that you wish to create in the local temporary table. Each column must have a datatype. The column should either be defined as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default.

EXAMPLE

Let's look at a SQL DECLARE LOCAL TEMPORARY TABLE example:

DECLARE LOCAL TEMPORARY TABLE suppliers_temp
( supplier_id int NOT NULL,
  supplier_name char(50) NOT NULL,
  contact_name char(50)
);

This example would create a LOCAL TEMPORARY TABLE called suppliers_temp.

GLOBAL TEMPORARY TABLES

This SQL tutorial explains how to create SQL GLOBAL TEMORARY tables with syntax and examples.

DESCRIPTION

SQL GLOBAL TEMPORARY TABLES are tables that are created distinct within SQL sessions.

SYNTAX

The syntax for SQL CREATE GLOBAL TEMPORARY TABLE is:

CREATE GLOBAL TEMPORARY TABLE table_name
( column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

Parameters or Arguments

table_name

The name of the global temporary table that you wish to create.

column1, column2

The columns that you wish to create in the global temporary table. Each column must have a datatype. The column should either be defined as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default.

EXAMPLE

Let's look at a SQL CREATE GLOBAL TEMPORARY TABLE example:

CREATE GLOBAL TEMPORARY TABLE suppliers_temp
( supplier_id numeric(10) NOT NULL,
  supplier_name char(50) NOT NULL,
  contact_name char(50)
);

DROP TABLE STATEMENT

This SQL tutorial explains how to use the SQL DROP TABLE statement with syntax and examples.

DESCRIPTION

The SQL DROP TABLE statement allows you to remove or delete a table from the SQL database.

SYNTAX

The syntax for the SQL DROP TABLE statement is:

DROP TABLE table_name;

Parameters or Arguments

table_name

The name of the table to remove from the database.

EXAMPLE

You may have found that you've created a table that you no longer require. You can use the DROP TABLE statement to remove the table from your database.

Let's look at an example that shows how to drop a table using the DROP TABLE statement.

For example:

DROP TABLE suppliers;

This DROP TABLE statement example would drop the table called suppliers. This would both remove the records associated with the suppliers table as well as its table definition.

Once you have dropped the table, you can recreate the suppliers table without getting an error that the table already exists.

Let's look at one more example where we prefix the table name with the database name.

For example:

DROP TABLE totn.contacts;

In this example, e have dropped a table called contacts that is in the totn database.

ALTER TABLE STATEMENT

This SQL tutorial explains how to use the SQL ALTER TABLE statement to add a column, modify a column, drop a column, rename a column or rename a table (with lots of clear, concise examples). We've also added some practice exercises that you can try for yourself.

DESCRIPTION

The SQL ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The SQL ALTER TABLE statement is also used to rename a table.

ADD COLUMN IN TABLE

Syntax

To add a column in a table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name
  ADD column_name column-definition;

Example

Let's look at a SQL ALTER TABLE example that adds a column.

For example:

ALTER TABLE supplier
  ADD supplier_name char(50);

This SQL ALTER TABLE example will add a column called supplier_name to the supplier table.

ADD MULTIPLE COLUMNS IN TABLE

Syntax

To add multiple columns to an existing table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name
  ADD (column_1 column-definition,
       column_2 column-definition,
       ...
       column_n column_definition);

Example

Let's look at SQL ALTER TABLE example that adds more than one column.

For example:

ALTER TABLE supplier
  ADD (supplier_name char(50),
       city char(45));

This SQL ALTER TABLE example will add two columns, supplier_name as a char(50) field and city as a char(45) field to thesupplier table.

MODIFY COLUMN IN TABLE

Syntax

To modify a column in an existing table, the SQL ALTER TABLE syntax is:

For Oracle, MySQL, MariaDB:

ALTER TABLE table_name
  MODIFY column_name column_type;

For SQL Server:

ALTER TABLE table_name
  ALTER COLUMN column_name column_type;

For PostgreSQL:

ALTER TABLE table_name
  ALTER COLUMN column_name TYPE column_definition;

Example

Let's look at an example of how to modify a column called supplier_name using the ALTER TABLE statement. Note that most databases have a slightly different syntax.

For Oracle:

ALTER TABLE supplier
  MODIFY supplier_name char(100) NOT NULL;

For MySQL and MariaDB:

ALTER TABLE supplier
  MODIFY supplier_name VARCHAR(100) NOT NULL;

For SQL Server:

ALTER TABLE supplier
  ALTER COLUMN supplier_name VARCHAR(100) NOT NULL;

For PostgreSQL:

ALTER TABLE supplier
  ALTER COLUMN supplier_name TYPE CHAR(100),
  ALTER COLUMN supplier_name SET NOT NULL;

MODIFY MULTIPLE COLUMNS IN TABLE

Syntax

To modify multiple columns in an existing table, the SQL ALTER TABLE syntax is:

For Oracle:

ALTER TABLE table_name
  MODIFY (column_1 column_type,
          column_2 column_type,
          ...
          column_n column_type);

For MySQL and MariaDB:

ALTER TABLE table_name
  MODIFY column_1 column_definition
    [ FIRST | AFTER column_name ],
  MODIFY column_2 column_definition
    [ FIRST | AFTER column_name ],
  ...
;

For PostgreSQL:

ALTER TABLE table_name
  ALTER COLUMN column_name TYPE column_definition,
  ALTER COLUMN column_name TYPE column_definition,
  ...
;

Example

Let's look at an example that uses the ALTER TABLE statement to modify more than one column. In this example, we will modify two columns called supplier_name and city.

For Oracle:

ALTER TABLE supplier
  MODIFY (supplier_name char(100) NOT NULL,
          city char(75));

For MySQL and MariaDB:

ALTER TABLE supplier
  MODIFY supplier_name VARCHAR(100) NOT NULL,
  MODIFY city VARCHAR(75);

For PostgreSQL:

ALTER TABLE supplier
  ALTER COLUMN supplier_name TYPE CHAR(100),
  ALTER COLUMN supplier_name SET NOT NULL,
  ALTER COLUMN city TYPE CHAR(75);

DROP COLUMN IN TABLE

Syntax

To drop a column in an existing table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name
  DROP COLUMN column_name;

Example

Let's look at an example that drops (ie: deletes) a column from a table.

For example:

ALTER TABLE supplier
  DROP COLUMN supplier_name;

This SQL ALTER TABLE example will drop the column called supplier_name from the table called supplier.

RENAME COLUMN IN TABLE

Syntax

To rename a column in an existing table, the SQL ALTER TABLE syntax is:

For Oracle and PostgreSQL:

ALTER TABLE table_name
  RENAME COLUMN old_name TO new_name;

For SQL Server (using the stored procedure called sp_rename):

sp_rename 'table_name.old_column', 'new_name', 'COLUMN';

For MySQL and MariaDB:

ALTER TABLE table_name
  CHANGE COLUMN old_name TO new_name;

Example

Let's look at an example that renames a column in the supplier table from supplier_name to sname.

For Oracle (9i Rel2 and up) and PostgreSQL:

ALTER TABLE supplier
  RENAME COLUMN supplier_name TO sname;

For SQL Server (using the stored procedure called sp_rename):

sp_rename 'supplier.supplier_name', 'sname', 'COLUMN';

For MySQL and MariaDB:

ALTER TABLE supplier
  CHANGE COLUMN supplier_name sname VARCHAR(100);

In MySQL and MariaDB, you must specify the data type of the column when you rename it.

RENAME TABLE

Syntax

To rename a table, the SQL ALTER TABLE syntax is:

For Oracle, MySQL, MariaDB, PostgreSQL and SQLite:

ALTER TABLE table_name
  RENAME TO new_table_name;

For SQL Server (using the stored procedure called sp_rename):

sp_rename 'table_name', 'new_table_name';

Example

Let's look at an example that renames a table called supplier to the new name vendor.

For Oracle, MySQL, MariaDB, PostgreSQL and SQLite:

ALTER TABLE supplier
  RENAME TO vendor;

For SQL Server (using the stored procedure called sp_rename):

sp_rename 'supplier', 'vendor';

CREATE TABLE AS STATEMENT

This SQL tutorial explains how to use the SQL CREATE TABLE AS statement with syntax and examples.

DESCRIPTION

You can also use the SQL CREATE TABLE AS statement to create a table from an existing table by copying the existing table's columns.

It is important to note that when creating a table in this way, the new table will be populated with the records from the existing table (based on the SELECT Statement).

CREATE TABLE - BY COPYING ALL COLUMNS FROM ANOTHER TABLE

Syntax

The syntax for the SQL CREATE TABLE AS statement copying all of the columns is:

CREATE TABLE new_table
  AS (SELECT * FROM old_table);

Example

Let's look at an example that shows how to create a table by copying all columns from another table.

For Example:

CREATE TABLE suppliers
AS (SELECT *
    FROM companies
    WHERE id > 1000);

This would create a new table called suppliers that included all columns from the companies table.

If there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.

CREATE TABLE - BY COPYING SELECTED COLUMNS FROM ANOTHER TABLE

Syntax

The syntax for the CREATE TABLE AS statement copying the selected columns is:

CREATE TABLE new_table
  AS (SELECT column_1, column2, ... column_n
      FROM old_table);

Example

Let's look at an example that shows how to create a table by copying selected columns from another table.

For Example:

CREATE TABLE suppliers
  AS (SELECT id, address, city, state, zip
      FROM companies
      WHERE id > 1000);

This would create a new table called suppliers, but the new table would only include the specified columns from the companiestable.

Again, if there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.

CREATE TABLE - BY COPYING SELECTED COLUMNS FROM MULTIPLE TABLES

Syntax

The syntax for the CREATE TABLE AS statement copying columns from multiple tables is:

CREATE TABLE new_table
  AS (SELECT column_1, column2, ... column_n
      FROM old_table_1, old_table_2, ... old_table_n);

Example

Let's look at an example that shows how to create a table by copying selected columns from multiple tables.

For Example:

CREATE TABLE suppliers
  AS (SELECT companies.id, companies.address, categories.cat_type
      FROM companies, categories
      WHERE companies.id = categories.id
      AND companies.id > 1000);

This would create a new table called suppliers based on columns from both the companies and categories tables.

FREQUENTLY ASKED QUESTIONS

Question: How can I create a SQL table from another table without copying any values from the old table?

Answer: To do this, the SQL CREATE TABLE syntax is:

CREATE TABLE new_table
  AS (SELECT *
      FROM old_table WHERE 1=2);

For example:

CREATE TABLE suppliers
  AS (SELECT *
      FROM companies WHERE 1=2);

This would create a new table called suppliers that included all columns from the companies table, but no data from thecompanies table.

CREATE TABLE STATEMENT

This SQL tutorial explains how to use the SQL CREATE TABLE statement with syntax, examples, and practice exercises.

DESCRIPTION

The SQL CREATE TABLE statement allows you to create and define a table.

SYNTAX

The syntax for the SQL CREATE TABLE statement is:

CREATE TABLE table_name
( 
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

Parameters or Arguments

table_name

The name of the table that you wish to create.

column1, column2

The columns that you wish to create in the table. Each column must have a datatype. The column should either be defined as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default.

EXAMPLE

Let's look at a SQL CREATE TABLE example.

CREATE TABLE suppliers
( supplier_id int NOT NULL,
  supplier_name char(50) NOT NULL,
  contact_name char(50)
);

This SQL CREATE TABLE example creates a table called suppliers which has 3 columns.

• The first column is called supplier_id which is created as a number datatype (maximum 10 digits in length) and can not contain null values.
• The second column is called supplier_name which is a char datatype (50 maximum characters in length) and also can not contain null values.
• The third column is called contact_name which is a char datatype but can contain null values.

Now the only problem with this SQL CREATE TABLE statement is that you have not defined a primary key for the table. We could modify this SQL CREATE TABLE statement and define the supplier_id as the primary key as follows:

CREATE TABLE suppliers
( supplier_id int NOT NULL,
  supplier_name char(50) NOT NULL,
  contact_name char(50),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

Learn about primary keys.

Learn about foreign keys.

PRACTICE EXERCISE #1:

Create a SQL table called customers that stores customer ID, name, and address information.

Solution for Practice Exercise #1:

The SQL CREATE TABLE statement for the customers table is:

CREATE TABLE customers
( customer_id int NOT NULL,
  customer_name char(50) NOT NULL,
  address char(50),
  city char(50),
  state char(25),
  zip_code char(10)
);

PRACTICE EXERCISE #2:

Create a SQL table called customers that stores customer ID, name, and address information.

But this time, the customer ID should be the primary key for the table.

Solution for Practice Exercise #2:

The SQL CREATE TABLE statement for the customers table is:

CREATE TABLE customers
( customer_id int NOT NULL,
  customer_name char(50) NOT NULL,
  address char(50),
  city char(50),
  state char(25),
  zip_code char(10),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

PRACTICE EXERCISE #3:

Based on the departments table below, create a SQL table called employees that stores employee number, employee name, department, and salary information. The primary key for the employees table should be the employee number. Create a foreign key on the employees table that references the departments table based on the department_id field.

CREATE TABLE departments
( department_id int NOT NULL,
  department_name char(50) NOT NULL,
  CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

Solution for Practice Exercise #3:

The SQL CREATE TABLE statement for the employees table is:

CREATE TABLE employees
( employee_number int NOT NULL,
  employee_name char(50) NOT NULL,
  department_id int,
  salary int,
  CONSTRAINT employees_pk PRIMARY KEY (employee_number),
  CONSTRAINT fk_departments
    FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
);

EXISTS CONDITION

This SQL tutorial explains how to use the SQL EXISTS condition with syntax and examples.

DESCRIPTION

The SQL EXISTS condition is used in combination with a subquery and is considered to be met, if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

SYNTAX

The syntax for the SQL EXISTS condition is:

WHERE EXISTS ( subquery );

Parameters or Arguments

subquery

The subquery is a SELECT statement. If the subquery returns at least one record in its result set, the EXISTS clause will evaluate to true and the EXISTS condition will be met. If the subquery does not return any records, the EXISTS clause will evaluate to false and the EXISTS condition will not be met.

NOTE

• SQL statements that use the EXISTS condition are very inefficient since the sub-query is rerun for EVERY row in the outer query's table. There are more efficient ways to write most queries, that do not use the EXISTS condition.

DDL/DML FOR EXAMPLES

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

EXAMPLE - USING EXISTS WITH SELECT STATEMENT

Let's look at an example that shows how to use the EXISTS condition with a SELECT statement.

In this example, we have a customers table with the following data:

CUSTOMER_ID  LAST_NAME  FIRST_NAME  FAVORITE_WEBSITE
-----------  ---------  ----------  ---------------------
       4000  Jackson    Joe         www.techonthenet.com
       5000  Smith      Jane        www.digminecraft.com
       6000  Ferguson   Samantha    www.bigactivities.com
       7000  Reynolds   Allen       www.checkyourmath.com
       8000  Anderson   Paige
       9000  Johnson    Derek       www.techonthenet.com

And a table called orders with the following data:

ORDER_ID  CUSTOMER_ID
--------  -----------
       1         5000
       2         6000
       3         7000
       4         9000

Now let's find all of the records from the customers table where there is at least one record in the orders table with the samecustomer_id. Enter the following SELECT statement:

SELECT *
FROM customers
WHERE EXISTS (SELECT *
              FROM orders
              WHERE customers.customer_id = orders.customer_id);

These are the results that you should see:

CUSTOMER_ID  LAST_NAME  FIRST_NAME  FAVORITE_WEBSITE
-----------  ---------  ----------  ---------------------
       5000  Smith      Jane        www.digminecraft.com
       6000  Ferguson   Samantha    www.bigactivities.com
       7000  Reynolds   Allen       www.checkyourmath.com
       9000  Johnson    Derek       www.techonthenet.com

In this example, there are 4 records in the customers where the customer_id value appears in the orders table.

EXAMPLE - USING NOT EXISTS WITH SELECT STATEMENT

The EXISTS condition can also be combined with the NOT operator to create a NOT EXISTS condition.

Let's use the NOT EXISTS condition to find all of the customers where there is not a record in the orders table with the samecustomer_id. Using the same customers and orders data as the previous example, enter the following SELECT statement:

SELECT *
FROM customers
WHERE NOT EXISTS (SELECT *
                  FROM orders
                  WHERE customers.customer_id = orders.customer_id);

These are the results that you should see:

CUSTOMER_ID  LAST_NAME  FIRST_NAME  FAVORITE_WEBSITE
-----------  ---------  ----------  ---------------------
       4000  Jackson    Joe         www.techonthenet.com
       8000  Anderson   Paige

In this example, there are 2 records in the customers table where the customer_id value does not appear in the orders table.

EXAMPLE - USING EXISTS WITH INSERT STATEMENT

Now, let's look at an example that shows how to use the EXISTS condition with an INSERT statement.

In this example, we have a contacts table that is empty and we want to populate:

CONTACT_ID  LAST_NAME  FIRST_NAME
----------  ---------  ----------

Let's insert into the contacts table all of the customer_id, last_name, and first_name values from the customers table where there is at least one record in the orders table with a matching customer_id.

INSERT INTO contacts
(contact_id, last_name, first_name)
SELECT customer_id, last_name, first_name
FROM customers
WHERE EXISTS (SELECT *
              FROM orders
              WHERE customers.customer_id = orders.customer_id);

Then select the data from the contacts table:

SELECT * FROM contacts;

These are the results that you should see:

CONTACT_ID  LAST_NAME  FIRST_NAME
----------  ---------  ----------
      5000  Smith      Jane
      6000  Ferguson   Samantha
      7000  Reynolds   Allen
      9000  Johnson    Derek

In this example, 4 records from the customers table will be inserted into the contacts table.

EXAMPLE - USING EXISTS WITH UPDATE STATEMENT

Let's look at an example that uses the EXISTS condition in an UPDATE statement.

In this example, we have a table called products with the following data:

PRODUCT_ID  PRODUCT_NAME  QUANTITY
----------  ------------  --------
         1  Pear                25
         2  Banana               0
         3  Orange              18
         4  Apple               45

And a table called summary_data with the following data:

PRODUCT_ID  CURRENT_LEVELS
----------  --------------
         1              10
         2              10
         3              10
         4              10
         5              10

Now let's update the summary_data table with values from the products table. Enter the following SQL statement:

UPDATE summary_data
SET current_levels = (SELECT quantity
                 FROM products
                 WHERE products.product_id = summary_data.product_id)
WHERE EXISTS (SELECT quantity
                 FROM products
                 WHERE products.product_id = summary_data.product_id);

Then select the data from the summary_data table again:

SELECT * FROM summary_data;

These are the results that you should see:

PRODUCT_ID  CURRENT_LEVELS
----------  --------------
         1              25
         2               0
         3              18
         4              45
         5              10

In this example, 4 records would be updated in the summary_data table.

TIP: Notice that our UPDATE statement included an EXISTS condition in the WHERE clause to make sure that there was a matching product_id in both the products and summary_data table before updating the record.

If we hadn't included the EXISTS condition, the UPDATE query would have updated the current_levels field to NULL in the last row of the summary_data table (because the products table does not have a record where product_id is 5).

EXAMPLE - USING EXISTS WITH DELETE STATEMENT

Let's look at an example that uses the EXISTS condition in a DELETE statement.

In this example, we have a table called products with the following data:

PRODUCT_ID  PRODUCT_NAME  QUANTITY
----------  ------------  --------
         1  Pear                25
         2  Banana               0
         3  Orange              18
         4  Apple               45

And a table called archival with the following data:

PRODUCT_ID
----------
         1
         2
         3
         4
         5

Now let's delete records from the archival table where there is at least one record in the products table with a matchingproduct_id. Enter the following SQL statement:

DELETE FROM archival
WHERE EXISTS (SELECT *
              FROM products
              WHERE products.product_id = archival.product_id);

Then select the data from the archival table again:

SELECT * FROM archival;

These are the results that you should see:

PRODUCT_ID
----------
         5

In this example, 4 records from the archival table will be deleted, leaving just one record remaining.