Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-166

How to create tables and views

    XMLWordPrintable

    Details

    • Type: How To
    • Status: Obsolete
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: EXASolution
    • Labels:
    • Solution:
      Hide

      Note: This solution is no longer maintained. Please see the most up-to-date information in our documentation portal: 

      https://docs.exasol.com/sql/create_table.htm

      https://docs.exasol.com/sql/create_view.htm

      -------------------------------------------------------------------------------------------------------------------------------------------

      Prerequisites

      You can create tables and views within a schema if you have the privilege CREATE TABLE/CREATE VIEW and you are the owner of the schema or contain the role that is owner. Otherwise you need the privilege CREATE ANY TABLE/VIEW.

      CREATE TABLE

      1) Specify the table name CREATE TABLE <table-name>
      2) Add a comma-separated list of columns
      (<column1> <datatype>,
      <column2> <datatype>,...)

      Optionally you can define primary and foreign keys, auto-increment columns (see identity) and a distribution key (see data distribution).

      Example

      CREATE TABLE customer(
      	c_customer_id int IDENTITY PRIMARY KEY,
      	c_customer_name varchar(50),
      	c_region_id int REFERENCES region(r_region_id)
      )
      

      CREATE VIEW

      1) Specify the view name: CREATE VIEW <view-name>
      2) Define the query: AS SELECT ... FROM ... WHERE ...

      Example

      CREATE VIEW customer_of_europe AS
      SELECT
      	customer.*
      FROM
      	customer JOIN region
      	ON
      	customer.c_region_id = region.r_region_id
      WHERE
      	r_region_name = 'Europe'
      

      Please have a look to the EXASolution manual for a complete description of the CREATE TABLE/VIEW commands.

      Show
      Note: This solution is no longer maintained. Please see the most up-to-date information in our documentation portal:  https://docs.exasol.com/sql/create_table.htm https://docs.exasol.com/sql/create_view.htm ------------------------------------------------------------------------------------------------------------------------------------------- Prerequisites You can create tables and views within a schema if you have the privilege CREATE TABLE/CREATE VIEW and you are the owner of the schema or contain the role that is owner. Otherwise you need the privilege CREATE ANY TABLE/VIEW. CREATE TABLE 1) Specify the table name CREATE TABLE <table-name> 2) Add a comma-separated list of columns (<column1> <datatype>, <column2> <datatype>,...) Optionally you can define primary and foreign keys, auto-increment columns (see identity) and a distribution key (see data distribution). Example CREATE TABLE customer( c_customer_id int IDENTITY PRIMARY KEY , c_customer_name varchar (50), c_region_id int REFERENCES region(r_region_id) ) CREATE VIEW 1) Specify the view name: CREATE VIEW <view-name> 2) Define the query: AS SELECT ... FROM ... WHERE ... Example CREATE VIEW customer_of_europe AS SELECT customer.* FROM customer JOIN region ON customer.c_region_id = region.r_region_id WHERE r_region_name = 'Europe' Please have a look to the EXASolution manual for a complete description of the CREATE TABLE/VIEW commands.
    • Category 1:
      SQL

      Attachments

        Activity

          People

          • Assignee:
            CaptainEXA Captain EXASOL
            Reporter:
            CaptainEXA Captain EXASOL
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: