DDL Statements - Create and Drop Table || DBMS Tutorial 4

DDL Statements - Create and Drop Table

DBMS Tutorial 4

Data Definition Language (DDL):

Data Definition Language (DDL) is used to specify the structure i.e. schema of a relational database. DDL provides commands for creation, modification, and deletion of various database objects like tables, views, stored procedures, indexes, constraints, etc. The output of DDL is placed in a data dictionary which contains metadata i.e. data about data.

Create and Drop Table:

The statement CREATE TABLE is used to create a table in a database. Database tables are organized into rows and columns. Each table must have a name and can have any number of columns (minimum 1 column is required). Each column must have a data type which determines the type of values that can be stored. CREATE TABLE command will fail if a table is already existing in the database with the same name. All tables must have a unique name.

DROP TABLE statement is used to remove an existing table from the database.

Note: Column names should be separated by commas. No two columns can have the same name.

Example 1:

CREATE TABLE Student (
    StudentId INTEGER,
    FName VARCHAR2(10),
    Gender CHAR(1),
    DOJ DATE);

Output:

NAME	Null?	TYPE
StudentId		NUMBER
FName		VARCHAR2(10)
Gender		CHAR(1)
DOJ		DATE

Example 2:

DROP TABLE Student;

Output:

Table dropped.

Create Table Syntax Errors:

We will now look at some common errors that occur while creating tables. The statement below has several syntax errors. Let us resolve these errors step by step:

Error query:

1  CREATE TABLE Student (
2     StudentId INTEGER
3     Gender CHAR(1),
4     FName VARCHAR2,
5     DOJ,);

Correcting query line by line:

Error at line 2: (missing identifier)

By solving error at line 2

1  CREATE TABLE Student (
2     StudentId INTEGER,
3     Gender CHAR(1),
4     FName VARCHAR2,
5     DOJ,);


Error at line 4: (missing left parenthesis)

By solving error at line 4

1  CREATE TABLE Student (
2     StudentId INTEGER,
3     Gender CHAR(1),
4     FName VARCHAR2(10),
5     DOJ,);


Error at line 5: (invalid identifier)

By solving error at line 5

1  CREATE TABLE Student (
2     StudentId INTEGER,
3     Gender CHAR(1),
4     FName VARCHAR2(10),
5     DOJ );


Error at line 5: (need to specify the datatype for this column)

By solving error at line 5

1  CREATE TABLE Student (
2     StudentId INTEGER,
3     Gender CHAR(1),
4     FName VARCHAR2(10),
5     DOJ DATE);

Correct Query:

1  CREATE TABLE Student (
2     StudentId INTEGER,
3     Gender CHAR(1),
4     FName VARCHAR2(10),
5     DOJ DATE);

Output for Correct Query:

Table created.

Constraints:

We have learned that data integrity in database systems is enforced through constraints. These constraints are typically specified along with the CREATE TABLE statement. Constraints are classified into multiple types based on the number of columns they act upon as well as on the way they are specified.

Constraint Type	Applies On
Single Column Constraint	Single Column
Composite Constraint	Multiple columns

Constraint Type	Specified
Column Level Constraint	With Column definition
Table Level Constraint	After Column definition

Table level constraint can be specified after all columns used in the constraint have been defined. It is not necessary to specify them after all columns in the table are defined. Composite constraints can only be specified as table-level constraints.

Various constraints that can be created on database tables are:

• NOT NULL
• PRIMARY KEY
• CHECK
• UNIQUE
• FOREIGN KEY

We can also specify DEFAULT value for a column. Oracle database does not consider DEFAULT as a constraint.

NOT NULL Constraint:

To prevents a column form accepting NULL values use NOT NULL Constraint. NOT NULL can only be applied as a column-level constraint. Constraint name is optional and it can be specified by using CONSTRAINT keyword.

Let us now create NOT NULL constraint on StudentId and FName columns.

Query:

CREATE TABLE Student (
    StudentId INTEGER CONSTRAINT Stud_SId_nn NOT NULL,
    FName VARCHAR2(10) NOT NULL,
    LName VARCHAR2(10));

Output:

Table created.

NAME	Null?	TYPE
STUDENTID	NOT NULL	NUMBER
FNAME	NOT NULL	VARCHAR2(10)
LNAME		VARCHAR2(10)

DEFAULT:

A column can be given the default value by using the DEFAULT option. The data type of column and default expression must be the same. DEFAULT option can be provided for nullable as well as NOT NULL attributes. Oracle database does not consider DEFAULT as a constraint.

Let us create a Student table with the current date as default for date of joining.

Query:

CREATE TABLE Student (
    StudentId INTEGER,
    FName VARCHAR2(10),
    DOJ DATE DEFAULT SYSDATE);

Output:

Table created.

NAME	Null?	TYPE
STUDENTID		NUMBER
FNAME		VARCHAR2(10)
DOJ		DATE

Note: Student table is created with the current date as DEFAULT option for DOJ column.

PRIMARY KEY Constraint:

A PRIMARY KEY constraint on a column ensures that the column cannot contain NULL and duplicate values. Only one PRIMARY KEY acceptable in a table.

We will now create a Student table with a primary key constraint on StudentId column.

Query:

CREATE TABLE Student (
    StudentId INTEGER CONSTRAINT stud_sid_pk PRIMARY KEY,
    FName VARCHAR2(10),
    ContactNo NUMBER(10));

Output:

Table created.

NAME	Null?	TYPE
StudentId	NOT NULL	NUMBER
FName		VARCHAR2(10)
ContactNo		NUMBER(10)

Quiz: Create Table

Quiz 47: Which of the following statement is TRUE regarding CREATE TABLE statement?

        a) CREATE table replaces the existing table if it already exists
        b) Attributes default to NUMBER data type if the data type is not provided
        c) Attributes allow NULL values unless the NOT NULL clause is provided ---
        d) DEFAULT clause can only be provided for NOT NULL attributes

Quiz 48: Which constraint can be defined only at the column level?

         a) INTEGER
         b) NOT NULL
         c) DEFAULT
         d) PRIMARY KEY

CHECK Constraint:

CHECK constraint is used to limit the values that can be specified for a column.

Let us create a CHECK constraint on the Gender column to only allow 'M' (Male) and 'F' (Female) values.

Query:

CREATE TABLE Student (
    StudentId INTEGER,
    FName VARCHAR2(10),
    Gender CHAR(1) CONSTRAINT Stud_gender_ck1 CHECK(Gender IN('M', 'F')));

Output:

Table created.

NAME	Null?	TYPE
StudentId		NUMBER
FName		VARCHAR2(10)
Gender		CHAR(1)

Note: Student table is created with CHECK constraint on Gender ('M' or 'F').

UNIQUE Constraint:

UNIQUE constraint on a column ensures that two rows in a table can not have the same value in that column. Unlike Primary Key, UNIQUE constraint allows NULL values. A table can have many UNIQUE constraints.

Let us create Unique constraint on ContactNo so that two students cannot have the same contact details.

Query:

CREATE TABLE Student (
    StudentId INTEGER,
    FName VARCHAR2(10),
    ContactNo NUMBER(10) CONSTRAINT Stud_cno_uk UNIQUE);

Output:

Table created.

NAME	Null?	TYPE
StudentId		NUMBER
FName		VARCHAR2(10)
ContactNo		NUMBER(10)

Note: Now, we have created a table with UNIQUE constraint on ContactNo.

Need for FOREIGN KEY:

Student Table has already been created and inserted with few records in the database.

STUDENTID	FNAME	CONTACTNO
1001	John	8754212356
1002	Jack	7456878956

Let us now create a table Marks which stores marks scored by each Student in each Course

Query:

CREATE TABLE Marks(
    CourseId INTEGER,
    StudentId INTEGER,
    MarksScored DECIMAL(5,2));

Output:

Table created.

NAME	Null?	TYPE
COURSEID		NUMBER
STUDENTID		NUMBER

FOREIGN KEY Constraint:

Student Table has already been created and inserted with few records in the database.

STUDENTID	FNAME	CONTACTNO
1001	John	8754212356
1002	Jack	7456878956

Let us now create a table Marks which stores marks scored by each Student in each Course

Query:

CREATE TABLE Marks(
    CourseId INTEGER,
    StudentId INTEGER CONSTRAINT marks_sid_fk REFERENCES Student(StudentId),
    MarksScored DECIMAL(5,2));

Output:

Table created.

NAME	Null?	TYPE
COURSEID		NUMBER
STUDENTID		NUMBER

Quiz 49: Which requirements should be implemented using a referential integrity constraint?

                a) Customer must have a name
                b) Customer must be greater than 18 years old
                c) Customer information must be known before anything is sold to him/her ---
                d) Two customers cannot have same mobile number

Quiz 50: Which of the following statements are TRUE?

                 a) Integrity constraint can be added to a table even if table data is in violation
                 b) A Unique constraint allows multiple rows to have NULL value ---
                 c) A PRIMARY KEY allows a single row to contain NULL
                 d) Referential integrity constraint allows NULL value ---

Quiz 51: Which requirements can be implemented using a CHECK constraint?

                a) Customer must have a name
                b) Customer must be greater than 21 years old ---
                c) Customer must have a residence in an Asian Country ---
                d) Two customers cannot have a same email id

Need for Composite PRIMARY KEY:

Student Table has already been created and inserted with few records in the database.

STUDENTID	FNAME	CONTACTNO
1001	John	8754212356
1002	Jack	7456878956

Let us now create a table Marks which stores MarksScored by each Student in each Course

Query:

CREATE TABLE Marks (
    CourseId INTEGER CONSTRAINT marks_cid_pk PRIMARY KEY,
    StudentId INTEGER CONSTRAINT marks_sid_fk REFERENCES
        Student(StudentId),
    MarksScored DECIMAL(5,2));

Output:

Table created.

NAME	Null?	TYPE
COURSEID	NOT NULL	NUMBER
STUDENTID		NUMBER

Composite PRIMARY KEY:

Student Table has already been created and inserted with few records in the database.

STUDENTID	FNAME	CONTACTNO
1001	John	8754212356
1002	Jack	7456878956

Let us now create a table Marks which stores MarksScored by each Student in each Course

Query:

CREATE TABLE Marks(
    CourseId INTEGER,
    StudentId INTEGER CONSTRAINT marks_sid_fk REFERENCES
      Student(StudentId),
    MarksScored DECIMAL(5,2),
    CONSTRAINT marks_cid_pk PRIMARY KEY(CourseId, StudentId));

Output:

Table created.

NAME	Null?	TYPE
COURSEID	NOT NULL	NUMBER
STUDENTID	NOT NULL	NUMBER

Create Table Syntax Errors:

We will now look at some common errors that occur while creating tables with constraints. The statement below has several syntax errors. Let us resolve these errors step by step:

Error query:

1  CREATE TABLE Student (
2     StudentId INTEGER PRIMARY KEY (StudentId),
3     FName VARCHAR2(10) CONSTRAINT NOT NULL,
4     LName VARCHAR2(10) CHECK (FName <> LName),
5     DOJ DATE DEFAULT,
6     Gender CHAR(1) CONSTRAINT Student_Gender_Ck CHECK Gender IN('M', 'F'),
7     PersonId INTEGER FOREIGN KEY REFERENCES Person(PersonId));

Steps to Correcting the code line by line:

Error on line 2: 

ORA-00907: missing right parenthesis

By correcting line 2

1  CREATE TABLE Student (
2     StudentId INTEGER PRIMARY KEY,
3     FName VARCHAR2(10) CONSTRAINT NOT NULL,
4    LName VARCHAR2(10) CHECK (FName <> LName),
5     DOJ DATE DEFAULT,
6     Gender CHAR(1) CONSTRAINT Student_Gender_Ck CHECK Gender IN('M', 'F'),
7     PersonId INTEGER FOREIGN KEY REFERENCES Person(PersonId));

Error at line 3: 

ORA-02250: missing or invalid constraint name

By correcting line 3

1  CREATE TABLE Student (
2     StudentId INTEGER PRIMARY KEY,
3     FName VARCHAR2(10) CONSTRAINT Stud_FName_NN NOT NULL,
4     LName VARCHAR2(10) CHECK (FName <> LName),
5     DOJ DATE DEFAULT,
6     Gender CHAR(1) CONSTRAINT Student_Gender_Ck CHECK Gender IN('M', 'F'),
7     PersonId INTEGER FOREIGN KEY REFERENCES Person(PersonId));

Error at line 4: 

ORA-02438: Column check constraint cannot reference other columns

By correcting line 4

1  CREATE TABLE Student (
2     StudentId INTEGER PRIMARY KEY,
3     FName VARCHAR2(10) CONSTRAINT Stud_FName_NN NOT NULL,
4     LName VARCHAR2(10), CHECK (FName <> LName),
5     DOJ DATE DEFAULT,
6     Gender CHAR(1) CONSTRAINT Student_Gender_Ck CHECK Gender IN('M', 'F'),
7     PersonId INTEGER FOREIGN KEY REFERENCES Person(PersonId));

Error at line 5: 

ORA-00936: missing expression

By correcting line 5

1  CREATE TABLE Student (
2     StudentId INTEGER PRIMARY KEY,
3     FName VARCHAR2(10) CONSTRAINT Stud_FName_NN NOT NULL,
4     LName VARCHAR2(10), CHECK (FName <> LName),
5     DOJ DATE DEFAULT SYSDATE,
6     Gender CHAR(1) CONSTRAINT Student_Gender_Ck CHECK Gender IN('M', 'F'),
7     PersonId INTEGER FOREIGN KEY REFERENCES Person(PersonId));

Error on line 6: 

ORA-00906: missing left parenthesis

By correcting line 6

1  CREATE TABLE Student (
2     StudentId INTEGER PRIMARY KEY,
3     FName VARCHAR2(10) CONSTRAINT Stud_FName_NN NOT NULL,
4     LName VARCHAR2(10), CHECK (FName <> LName),
5     DOJ DATE DEFAULT SYSDATE,
6     Gender CHAR(1) CONSTRAINT Student_Gender_Ck CHECK (Gender IN('M', 'F')),
7     PersonId INTEGER FOREIGN KEY REFERENCES Person(PersonId));

Error at line 7: 

ORA-00907: missing right parenthesis

By correcting line 7

1  CREATE TABLE Student (
2     StudentId INTEGER PRIMARY KEY,
3     FName VARCHAR2(10) CONSTRAINT Stud_FName_NN NOT NULL,
4     LName VARCHAR2(10), CHECK (FName <> LName),
5     DOJ DATE DEFAULT SYSDATE,
6     Gender CHAR(1) CONSTRAINT Student_Gender_Ck CHECK (Gender IN('M', 'F')),
7     PersonId INTEGER REFERENCES Person(PersonId));

Constraint Summary:

Quiz 52: What will be the output of the following command?

CREATE TABLE Emp (
    Name VARCHAR2(20) PRIMARY KEY,
    DOB DATE PRIMARY KEY,
    DeptNo NUMBER(4));

         a) Two primary keys will be created
         b) Composite primary key will be created with NAME as the first attribute
         c) An error will be thrown ---
         d) Composite primary key will be created with DOB as the first attribute

Quiz 53: Consider the creation of the following table: Customer(CustId, AccountNo, CustName) with the combination of columns Custid and AccountNo should be UNIQUE. Which one of the following options is CORRECT?

          a) Put UNIQUE constraint on both the columns separately
          b) This type of constraint is not possible
          c) Put PRIMARY KEY constraint on both the columns separately
          d) Put a table-level UNIQUE constraint involving both the columns ---

Quiz 54: Which one of the following DDL statements will create a table? Assume table customer is already created with appropriate constraints and data types.

a) CREATE TABLE Account(
       Acctid NUMBER(3),
       Custid NUMBER(4),
       Balance NUMBER(8,2),
       CONSTRAINT ac_aid_pk PRIMARY KEY(Acctid),
       CONSTRAINT ac_cid_fk FOREIGN KEY(Custid) REFERENCES Customer(Cid));   ---

b) CREATE TABLE Account(
       Acctid NUMBER(3) PRIMARY KEY(Acctid),
       Custid NUMBER(4) REFERENCES Customer(Cid),
       Balance NUMBER(8,2));

c) CREATE TABLE Account(
       Acctid NUMBER(3) CONSTRAINT PRIMARY KEY,
       Custid NUMBER(4) CONSTRAINT ac_cid_fk FOREIGN KEY(Custid) REFERENCES Customer(Cid),
       Balance NUMBER(8,2) );

Exercise 3:

Sports Craft Company has organized tournaments in multiple individual sports and invited top-ranked players to play. Each tournament comprises of multiple matches in knock out format. Each match is played between two players.

Problem Statement: Create the Player table as per the information provided below:

Column Name	Data Type	Constraint
PId	INTEGER	PRIMARY KEY
PName	VARCHAR2(20)	NOT NULL

Exercise 4:

Create the Tournament table as per information below:

Column Name	Data Type	Constraint	Description
TId	INTEGER	PRIMARY KEY	Unique tournament Id for every tournament
TName	VARCHAR2(30)	NOT NULL	Tournament Name
StartDt	DATE	NOT NULL	Start Date of the tournament
EndDt	DATE	NOT NULL	End Date of the tournament
Prize	INTEGER	NOT NULL	Prize to be won in the tournament

Exercise 5:

Create Match table as per details below. A player cannot play against himself, so player1 should not be equal to player2.

Column Name	Data Type	Constraint		Description
MId	INTEGER	PRIMARY KEY		Unique Match Id for a tournament
TId	INTEGER		FOREIGN KEY	Existing tournament Id present in tournament table
Player1	INTEGER	FOREIGN KEY	Player1 should not be equal to Player2	Existing player Id present in player table
Player2	INTEGER	FOREIGN KEY		Existing player Id present in player table
MatchDt	DATE	NOT NULL		Match Date

DDL Statements - Create and Drop Table

DBMS Tutorial 4

Back    Next

---

I hope you likable this nice post. Do not forget to share it together with your friends, the Sharing Button is below the post. Apart from this, if there's any downside within the intermediate, don't hesitate to request the Comment Box. we are going to be happy to help you.

I will continue to write more and more on this blog, so do not forget to make our blog BlogLearner as a bookmark (Ctrl + D) on your mobile or computer and subscribe to us to get all the posts in your email. Do not forget to share these posts, if you like it. You can facilitate us reach additional individuals by sharing it on social networking sites like Facebook or Twitter.

DDL Statements - Create and Drop Table

#dbms #database_management_system #dbms_tutorial #database #database_system #ddl_statements #creat_table #drop_table #rrkksinha #bloglearner