Page 48 - IP

i. Suggest the suitable data type for Issue_Date column.
ii. Suggest the suitable SQL command to change the size of column name from
30 character to 50 characters.
iii. Mention the significance of Bid column in table Library.
iv. Suggest the suitable command to display a list of the books in a sequence so
that first all the book’s name should be displayed which has been returned and
after that all the book’s name which has not been returned should be displayed.
Ans: i. Date
ii. alter table library modify name varchar(50);
iii. Bid column will always have a unique value which will help uniquely identify each
record of Library table.
iv. Select name from library order by status desc;

(1 mark for each correct answer)

(d) Rishi, a class XII student has given following commands for the given purposes: 2
i. To add a new column “Rating” :
update table library add column rating varchar(20);
ii. To give an increase of 50 Rs. to all the books:
alter library set price=price+50;
Check if above given SQL commands will be able to achieve desired task or not.
Justify your answer. Suggest the correction (s) if required.

Ans: No, above commands will not be able to achieve desired task as update and
alter commands have been interchanged.

In order to achieve desired result, following corrections should be incorporated
in the previously used commands:

i. To add a new column “Rating” :
Alter table library add column rating varchar(20);
ii. To give an increase of 50 Rs. to all the books:
Update library set price=price+50;

(2 mark for correct answer)

6. (a) Write SQL query to create a table “BOOKS” with the following structure: 2

Table: BOOKS

Field name Datatype Size Constraint
BOOK_ID Integer 2 Primary Key
BOOK_NAME Varchar 20
CATEGORY Varchar 10
ISSUE_DATE Date

OR

Help Ramesh in identifying any two columns for a table named student along
with their suitable data type.

11