 |
| DDL Statements |
DDL Statements
Assignments
Assignment 2:
Create the Shopper table with appropriate data types for the attributes described below.
Column Name
|
Constraints
|
DESCRIPTION
|
Shopperid
|
PRIMARY KEY
|
Unique id of the shopper. For eg. 2001, 2003
|
ShopperName
|
NOT NULL
|
Name of the Shopper. It cannot be more than 20 characters.
|
Gender
|
should be only 'Male' or 'Female'
|
Gender of the Shopper. It cannot be more than 6 characters.
|
MobileNo
|
NOT NULL
|
Mobile number of the Shopper in numeric form. Characters like brackets and hyphens are not allowed.
|
Address
|
Address of the Shopper. It cannot be more than 50 characters. For eg. Allen Street, New York
|
Assignment 3:
Alter the table Shopper to change the data type of mobile number from NUMBER to VARCHAR2. Mobile number can have a maximum length of 15 characters.
Column Name
|
Constraints
|
DESCRIPTION
|
Shopperid
|
PRIMARY KEY
|
Unique id of the shopper. For eg. 2001, 2003
|
ShopperName
|
NOT NULL
|
Name of the Shopper
|
Gender
|
should be only 'Male' or 'Female'
|
Gender of the Shopper
|
MobileNo
|
NOT NULL
|
Mobile number of the Shopper
|
Address
|
Address of the Shopper. For eg. Allen Street, New York
|
Quiz:
Quiz 56: Determine the appropriate constraint classification for each of the following statements.
I) It can be only one in a table
CHECK
DEFAULT
FOREIGN KEY
NOT NULL
PRIMARY KEY ---
UNIQUE
II) The column can contain either distinct values or NULL values
CHECK
DEFAULT
FOREIGN KEY
NOT NULL
PRIMARY KEY
UNIQUE ---
III) The domain of the column is 'Male' or 'Female'
CHECK ---
DEFAULT
FOREIGN KEY
NOT NULL
PRIMARY KEY
UNIQUE
IV) The column must contain a value
CHECK
DEFAULT
FOREIGN KEY
NOT NULL ---
PRIMARY KEY
UNIQUE
V) The values present in the column must refer to another table
CHECK
DEFAULT
FOREIGN KEY ---
NOT NULL
PRIMARY KEY
UNIQUE
VI) The column value should be considered as Zero if no value is inserted
CHECK
DEFAULT ---
FOREIGN KEY
NOT NULL
PRIMARY KEY
UNIQUE
Quiz 57: Which statement would add a column CGPA to a table Student which is already created?
a) ALTER TABLE Student ADD COLUMN (CGPA NUMBER(3,1));
b) ALTER TABLE Student CGPA NUMBER(3,1);
c) ALTER TABLE Student ADD (CGPA NUMBER(3,1)); ---
d) ALTER TABLE Student ADD CGPA NUMBER(3,1); ---
Quiz 58: Which statement is TRUE regarding the ALTER statement?
a) ALTER TABLE cannot be used to remove a column
b) ALTER table can be used to change the data type of an existing column if column values are empty ---
c) ALTER table can be used to change the size of data type, not the data type itself
d) ALTER table can be used to add a column but it must be NOT NULL
Assignment 4:
Create the Article table as per specification below.
Column Name
|
Data Type
|
Constraints
|
DESCRIPTION
|
ArCode
|
CHAR(5)
|
PRIMARY KEY; Must begin with character 'A'
|
Unique code of the article e.g. A1001, A1004
|
ArName
|
VARCHAR2(30)
|
NOT NULL
|
Article Name
|
Rate
|
NUMBER(8,2)
|
Rate of the article. For eg. 5000.0
| |
Quantity
|
NUMBER(4)
|
Greater than or equal to 0; Default Value is 0
|
Quantity availability of the article. For eg. 20
|
Class
|
CHAR(1)
|
Can be A, B or C
|
Class of the article
|
Assignment 5:
Create the Store table as per specification below.
Column Name
|
DataType and Size
|
Constraints
|
DESCRIPTION
|
Name
|
VARCHAR2(20)
|
PRIMARY KEY
|
Unique name of the store. For eg. WalMart, Big Bazar
|
Location
|
VARCHAR2(30)
|
NOT NULL
|
Location of the store. For eg. Dallas
|
ManagerName
|
VARCHAR2(30)
|
UNIQUE
|
Manager's name of the store
|
Assignment 6:
Modify the column Name in Store table to StoreName.
Column Name
|
DataType and Size
|
Constraints
|
DESCRIPTION
|
Name
|
VARCHAR2(20)
|
PRIMARY KEY
|
Unique name of the store. For eg. WalMart, Big Bazar
|
Location
|
VARCHAR2(30)
|
NOT NULL
|
Location of the store. For eg. Dallas
|
ManagerName
|
VARCHAR2(30)
|
UNIQUE
|
Manager's name of the store
|
Assignment 7:
Create the Bill table as per specification below.
Column Name
|
DataType and Size
|
Constraints
|
DESCRIPTION
|
BillNo
|
NUMBER
|
PRIMARY KEY
|
Unique id of the bill. For eg. 5001, 5005
|
StoreName
|
VARCHAR2(20)
|
FOREIGN KEY
|
Existing StoreName in Store table
|
Shopperid
|
NUMBER
|
FOREIGN KEY
|
Existing ShopperId in Shopper table
|
ArCode
|
CHAR(5)
|
FOREIGN KEY
|
Article purchased by the shopper. Existing ArCode in Article table
|
Amount
|
NUMBER
|
Bill amount. For eg. 350.0
| |
BillDate
|
DATE
|
Date of billing
| |
Quantity
|
NUMBER(4)
|
Should be > 0 and the Default value is 1
|
Quantity of the article purchased. For eg. 45
|
Assignment 8:
Create the Supplier table based on the specification below.
Column Name
|
DataType
|
Constraints
|
DESCRIPTION
|
Supplierid
|
VARCHAR2(6)
|
PRIMARY KEY
|
Unique Id of the supplier
|
Name
|
VARCHAR2(30)
|
Name of the supplier
| |
ContactNo
|
VARCHAR2(15)
|
NOT NULL
|
The contact number of the Supplier
|
Emailid
|
VARCHAR2(30)
|
Email Id of the supplier
|
Assignment 9:
Modify the Supplier table to store city of the supplier. The column name should be City and it can have a maximum of 10 characters.
Column Name
|
DataType
|
Constraints
|
DESCRIPTION
|
Supplierid
|
VARCHAR2(6)
|
PRIMARY KEY
|
Unique Id of the supplier
|
Name
|
VARCHAR2(30)
|
Name of the supplier
| |
ContactNo
|
VARCHAR2(15)
|
The contact number of the Supplier
| |
Emailid
|
VARCHAR2(30)
|
Email Id of the supplier
|
Assignment 10:
Remove the Emailid column from Supplier table
Column Name
|
DataType
|
Constraints
|
DESCRIPTION
|
Supplierid
|
VARCHAR2(6)
|
PRIMARY KEY
|
Unique Id of the supplier
|
Name
|
VARCHAR2(30)
|
Name of the supplier
| |
ContactNo
|
VARCHAR2(15)
|
The contact number of the Supplier
| |
Emailid
|
VARCHAR2(30)
|
Email Id of the supplier
|
Assignment 11:
Create the City table as per the specification.
Column Name
|
DataType
|
Constraints
|
DESCRIPTION
|
City
|
VARCHAR2(20)
|
UNIQUE
|
City Name
|
Assignment 12:
Drop the column city from the City table.
Column Name
|
DataType
|
Constraints
|
DESCRIPTION
|
City
|
VARCHAR2(20)
|
UNIQUE
|
City Name
|
Assignment 13:
Create the Address table as per specification below:
Column Name
|
DataType
|
Constraints
|
DESCRIPTION
| |
HouseNo
|
NUMBER
|
PRIMARY KEY
|
Unique address identifier
| |
Street
|
VARCHAR2(30)
| |||
city
|
VARCHAR2(20)
|
Foreign Key
|
Existing City present in City table
| |
zip
|
NUMBER(6)
|
Should be >= 0
|
Zip code of the city
| |
state
|
VARCHAR2(5)
| |||
Assignment 14:
Modify the size of the state column in Address table from 5 to 20.
Column Name
|
DataType
|
Constraints
|
DESCRIPTION
| |
HouseNo
|
NUMBER
|
PRIMARY KEY
|
Unique address identifier
| |
Street
|
VARCHAR2(30)
| |||
city
|
VARCHAR2(20)
|
Foreign Key
|
Existing City present in City table
| |
zip
|
NUMBER(6)
|
None
|
Should be >= 0
|
Zip code of the city
|
state
|
VARCHAR2(5)
| |||
Assignments DDL Statements
DBMS Tutorial
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 - Assignments |
very educative and easy to understand.
ReplyDeleteAssignment orders can catch most appropriations, commissions, and practically any sort of K-1 pay. Whenever endorsed by a court, an AO trains somebody that owes cash to your judgment indebted person, to pay you rather than the judgment debt holder. cause and effect essay
ReplyDelete