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 (a) Two primary keys will be created
Name VARCHAR2(20) PRIMARY KEY,
DOB DATE PRIMARY KEY,
DeptNo NUMBER(4));
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
---
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 |
No comments:
Post a Comment