TestingDocs.com
    Software Testing website
    • Automation
      • Selenium
      • JBehave Framework
    • Tutorials
      • MySQL Tutorials
      • Testlink
      • Maven
      • Git
    • IDEs
      • IntelliJ IDEA
      • Eclipse
    • Flowcharts
      • Flowgorithm
      • Raptor
    • About

    Oracle

    Oracle SQL CREATE TABLE Statement

    Overview

    In this Oracle SQL Tutorial, we will learn CREATE TABLE statement. This statement allows you to create a table in the database schema. In this post, we will create the most common type of table which is a relational table in Oracle database.

    Syntax

    The CREATE TABLE statement only creates the table structure. We need to specify the table name and the columns for the table. Table columns can have different data types like numbers(NUMBER), strings(VARCHAR2), dates(DATE), etc.

    Simple Syntax:

    CREATE TABLE table_name (
    column_list );

    Each column definition is separated by a comma. Columns can have constraints that we can specify during the table creation. We will talk about database constraints in upcoming posts.

    Once the table is created, we can insert data using the INSERT statement.

    Example : dept table

    In this example, we will create a department table with the name dept.

    CREATE TABLE dept (
    DEPTNO NUMBER(4) CONSTRAINT PK_DEPT PRIMARY KEY,
    DNAME VARCHAR2(20),
    LOC VARCHAR2(20));

     

    CREATE TABLE Statement Oracle SQL

    In this example, the table dept has three columns. We can use the DESC or describe command to know the structure.

     

    Create Table Oracle SQL

     

    SQL> desc table_name;

    deptno -> department number. The deptno column is the Primary key for the table. Primary key column values are unique and uniquely identify the rows in the table.

    dname -> department name column

    loc -> department location column

    Example: emp table

    Let’s create another table with more number of columns. The table name is emp. We will reference the primary key of the dept table in this emp table as Foreign key. Foreign key is a primary key of one table that is references in another table. This common column would be used in joining tables together.

    CREATE TABLE emp (
    empno NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
    ename VARCHAR2(20),
    job VARCHAR2(10),
    mgr NUMBER(4),
    hiredate DATE,
    sal NUMBER(7,2),
    comm NUMBER(7,2),
    deptno NUMBER(4) CONSTRAINT fk_deptno REFERENCES dept);

     

    Notice that, we have used UPPERCASE only for keywords and data types. Table, column and constraint names are in lowercase. This will increase the readability of the SQL statement. As a good programming practice we will follow the same standard in SQL statement. Good indentation and breaking the SQL statement into multiple lines also add readability of the code.

    If the table is created successfully, SQL Plus displays a ‘Table created’ success message.

    —

    Oracle Database Tutorials on this website:

    https://www.testingdocs.com/oracle-database-tutorials-for-beginners/

    More information about Oracle Database:

    https://www.oracle.com/database/

    Related Posts

    Download SQL Developer

    Oracle /

    Download Oracle SQL Developer

    Oracle Databse 19c Download

    Oracle /

    Install Oracle 19c Database On Windows 10

    ORA-01109 Database Not Open

    Oracle /

    Fix ORA-01109: database not open Error

    Oracle SQL Update Statement

    Oracle /

    Oracle SQL Update Statement

    Oracle SQL Insert Statement

    Oracle /

    Oracle SQL INSERT Statement

    ‹ Set ORACLE_HOME environment variable on Windows› Oracle SQL SELECT Statement

    Recent Posts

    • ChatGPT Plans Free and PlusChatGPT Subscription Plans
    • Stellar Converter for Database ToolStellar Converter for Database
    • Stellar MySQL Log AnalyzerStellar Log Analyzer for MySQL
    • Stellar Repair for MySQLStellar Repair for MySQL
    • ChatGPT IntroductionChatGPT Capabilities
    • How to secure your SQL Database: Tips and Tricks
    • ChatGPT4 Conversational AI FeaturesChatGPT4 Conversational AI Features
    • Trends in Software EngineeringShaping the Future of Development: Exploring Key Trends in Software Engineering
    • Java PerformanceImproving Java Performance with Multithreading
    • QDrant Vector DatabaseOpen-source Vector Databases
    • Difference between PHP and JavaScript?
    • Bing AI Browser Web ContentBing Conversation Styles
    • ChatGPT PreviewChatGPT Introduction
    • Open Source AI Frameworks TensorFlowOpen Source AI Frameworks
    • Artificial Intelligence Tools

    Back to Top

    Links

    • Contact
    • Privacy Policy
    • Cookie Policy

    www.TestingDocs.com