CREATE TABLE / ALTER TABLE - Solved Exercises

How to create a table? / How to alter the structure of a table? / How to add constraints? / How to remove constraints? / What is table definition? / How to add a column to an existing table?

Create Table / Alter Table Exercises

Question:

Consider an application which is being developed for a ‘Used Car seller’. For storing various details related to the cars and owners, create the tables according to the schemas given below;

CAR(Car_Reg_No, Brand, Model, Variant, Model_Year, Color, Year_Of_Purchase, Kilometers)

OWNER(Owner_ID, Owner_Name, Owner_Phone, License_Number)

OWNER_CAR(Owner_ID, Car_Reg_No, Price)

I - Consider the following as important components while creating the tables;

a. The columns that are underlined are Primary keys

b. All columns must contain some values.

c. Choose the appropriate data types which would match the most for all the attributes.

d. Use constraint names for all the constraints you create.

e. For table CAR;

          i. Brand should be one of { ‘Maruti’, ‘Ford’, ‘Hyundai’ }

          ii. Color should be one of { ‘Black’, ‘White’, ‘Red’, ‘Blue’ }

          iii. Kilometers should be less than 50000.

f. For table OWNER;

          i. Owner_ID should start with ‘OID’

          ii. License_Number should be unique value.

g. For table OWNER_CAR;

          i. Price should be greater than Rs.150000.

II - After table creation, write the queries to alter the tables according to the requirements given below;

a. Add the Foreign Key in OWNER_CAR table to refer Owner_ID from OWNER table.

b. Add the Foreign Key in OWNER_CAR table to refer Car_Reg_No from CAR table.

c. Add an attribute Owner_Address in OWNER table.

d. Add an attribute Test_Status of the car which accepts character based values.

e. Remove the column Year_Of_Purchase from CAR.

f. Remove the column Owner_Address from OWNER table.

g. Remove the Unique constraint of License_Number attribute.

h. Remove the color constraint so that any colored cars can be inserted.

i. Add a column Years_Used to OWNER_CAR table which stores the number of years the car used by the owner.

j. Remove the table OWNER_CAR from the database completely.

k. Change the type of License_Number attribute from character based type to number type.

Answers:

The queries are written in SQL as used in Oracle RDBMS

I – Table Creation:

I have listed the requirements for creating tables as specified in the question in the table given below; Type column in the following table lists the appropriate types for every attribute and Constraints column list the conditions to be satisfied while creating a table.

Requirement specification for CAR;

Attributes	Type	Constraints
Car_Reg_No	CHAR (Because the length of the registration number will not be in varying lengths. They are fixed length values)	·         PRIMARY KEY, ·         NOT NULL
Brand	VARCHAR (Brand name can be of varying length. Hence, it is advisable to use VARCHAR or VARCHAR 2)	·         CHECK (The permitted values are ‘Maruti’, ‘Ford’, ‘Hyundai’ only) ·         NOT NULL
Model	VARCHAR (Model name is of varying length)	·         NOT NULL
Variant	VARCHAR (Variant is like ‘ZXi’, ‘LDi’ etc. But it could be of different length for different brand cars. Hence, VARCHAR would be used)	·         NOT NULL
Model_Year	NUMBER (Stores only year value)	·         NOT NULL
Color	VARCHAR	·         CHECK (The permitted values for color are ‘Black’, ‘White’, ‘Red’, ‘Blue’ only) ·         NOT NULL
Year_Of_Purchase	NUMBER	·         NOT NULL
Kilometers	NUMBER	·         CHECK (The value should be less than 50000 kilometers) ·         NOT NULL

Query:

(Convention used - All the words given in ALL CAPS are keywords. All the words represented in RED color are either table names/attribute names. All the words represented in GREEN color are constraint names.)

CREATE TABLE Car

(Car_Reg_No CHAR(12) NOT NULL,

Brand VARCHAR(10) NOT NULL,

Model VARCHAR(10) NOT NULL,

Variant VARCHAR(5) NOT NULL,

Model_Year NUMBER(4) NOT NULL,

Color VARCHAR(10) NOT NULL,

Year_Of_Purchase NUMBER(4) NOT NULL,

Kilometers NUMBER(5) NOT NULL,

CONSTRAINT Car_Reg_No_PK PRIMARY KEY (Car_Reg_No),

CONSTRAINT Brand_CK CHECK (Brand IN (‘Maruti’, ‘Ford’, ‘Hyundai’)),

CONSTRAINT Color_CK CHECK (Color IN (‘Black’, ‘White’, ‘Red’, ‘Blue’)),

CONSTRAINT Km_CK CHECK (Kilometers < 50000));

Requirement specification for OWNER;

Attributes	Type	Constraints
Owner_ID	CHAR	·         PRIMARY KEY, ·         CHECK (every owner id should start with the letters ‘OID’) ·         NOT NULL
Owner_Name	VARCHAR	·         NOT NULL
Owner_Phone	NUMBER	·         NOT NULL
License_Number	CHAR	·         UNIQUE (We accept unique values only. Duplicate values should be denied) ·         NOT NULL

Query:

CREATE TABLE Owner

(Owner_ID CHAR(10) NOT NULL,

Owner_Name VARCHAR(30) NOT NULL,

Owner_Phone NUMBER(10) NOT NULL,

License_Number CHAR(10) NOT NULL,

CONSTRAINT Owner_ID_PK PRIMARY KEY (Owner_ID),

CONSTRAINT Owner_ID_CK CHECK (Owner_ID LIKE ‘OID%’),

CONSTRAINT LN_UQ UNIQUE (License_Number));

Requirement specification for OWNER_CAR;

Attributes	Type	Constraints
Owner_ID	CHAR	·         PRIMARY KEY, ·         NOT NULL
Car_Reg_No	CHAR	·         PRIMARY KEY ·         NOT NULL
Price	NUMBER	·         CHECK (Car price should be more than 150000) ·         NOT NULL

Query:

CREATE TABLE Owner_Car

(Owner_ID CHAR(10) NOT NULL,

Car_Reg_No CHAR(12) NOT NULL,

Price NUMBER(8) NOT NULL,

CONSTRAINT Owner_Car_PK PRIMARY KEY (Owner_ID, Car_Reg_No),

CONSTRAINT Price_CK CHECK (Price > 150000));

II – Table Alteration:

As per the given specification of section I, the tables are created. Let us modify the tables as per the instruction in section II.

a. Add the Foreign Key in OWNER_CAR table to refer Owner_ID from OWNER table.

A FOREIGN KEY can be added using the following syntax;

“FOREIGN KEY (attribute_name_in_ForeignKey_Table) REFERENCES Target_Table_Name(attribute_referred_in_Target_table)”

Query II a:

ALTER TABLE Owner_Car ADD CONSTRAINT Owner_FK FOREIGN KEY (Owner_ID) REFERENCES Owner(Owner_ID);

b. Add the Foreign Key in OWNER_CAR table to refer Car_Reg_No from CAR table.

Query II b:

ALTER TABLE Car ADD CONSTRAINT Car_FK FOREIGN KEY (Car_Reg_No) REFERENCES CAR(Car_Reg_No);

c. Add an attribute Owner_Address in OWNER table.

Query II c:

ALTER TABLE Owner ADD Owner_Address VARCHAR(40) NOT NULL;

d. Add an attribute Test_Status of the car which accepts character based values.

Query II d:

ALTER TABLE Car ADD Test_Status VARCHAR(10) NOT NULL;

e. Remove the column Year_Of_Purchase from CAR.

The syntax to remove a column from an existing table is “DROP COLUMN column_name”

Query II e:

ALTER TABLE Car DROP COLUMN Year_Of_Purchase;

f. Remove the column Owner_Address from OWNER table.

Query II f:

ALTER TABLE Owner DROP COLUMN Owner_Address;

g. Remove the Unique constraint of License_Number attribute.

Removing constraints can be done using the syntax “DROP CONSTRAINT constraint_name”

Query II g:

ALTER TABLE Owner DROP CONSTRAINT LN_UQ;

[Recall from the table definition of Owner, LN_UQ is the name of the UNIQUE constraint]

h. Remove the color constraint so that any colored cars can be inserted.

Query II h:

ALTER TABLE Car DROP CONSTRAINT Color_CK;

i. Add a column Years_Used to OWNER_CAR table which stores the number of years the car used by the owner.

Query II i:

ALTER TABLE Owner_Car ADD Years_Used NUMBER(2);

j. Remove the table OWNER_CAR from the database completely.

The syntax for removing a table from the database permanently is “DROP TABLE table_name”

Query II j:

DROP TABLE Owner_Car;

k. Change the type of License_Number attribute from character based type to number type.

The syntax for changing the data type or size is “MODIFY COLUMN column_name datatype”

Query II k:

ALTER TABLE Owner MODIFY COLUMN License_Number NUMBER(10);

[Note: If you would like to change the data type of size of any attribute, then you should delete all the existing records before alteration. This can be done in two ways;

DELETE FROM table_name;

TRUNCATE TABLE table_name;]