Database Management System Case Study Based Questions 02 Term II

Information Technology TERM II

Database Management System

Class 10 Session 2021-2022

Case Study Questions 02

Direction Read the case and answer the following questions.

Mr. Amar Agarwal is the owner of his parental firm Agarwal & Sons which deals in the wholesale business of spices. He wants to maintain records of spices available in their shop and generate bills when someone purchases any spices from the shop. They want to create a database to keep track of all the spices in the shop and the spices purchased by customers.

(i) Which is the correct SQL statement to create the table?

(a) CREATE TABLE spice(scode integer, sname char(25), price decimal, stock integer)
(b) CREATE spice(scode integer, sname char(25), price decimal, stock integer)
(c) CREATE spice(scode , sname , price, stock)
(d) SELECT TABLE spice(scode integer, sname char(25), price decimal, stock integer)

(a) CREATE TABLE is an SQL command to define a new table.

(ii) To add records in the table which of the following SQL statement is used?

(a) ADD
(b) SELECT
(c) INSERT
(d) INSERT INTO

(d) The INSERT INTO statement adds new rows/tuples to the table. The order of values matches the order of column names in the table.

(iii) To add a new column exp_date in the existing table the statement is

(a) ALTER TABLE spice ADD (exp_date date);
(b) UPDATE TABLE spice ADD (exp_date date);
(c) MODIFY spice ADD (exp_date date);
(d) ALTER TABLE spice MODIFY (exp_date date);

(a) To add a new column in an existing table following syntax is used :
ALTER TABLE <table_name> ADD <column_name> <data type> <size>[constraint name];]
Hence, to add a new column exp_date in the existing table the statement is

ALTER TABLE spice ADD (exp_date date);

(iv) To view the table structure of the above created table the command is

(a) SELECT spice
(b) DESC spice
(c) DESCRIBE spice
(d) Both (b) and (c)

(d) To view the table structure DESCRIBE spice command is used.

(v) To create an index on name of spices in the existing table, the command is

(a) ALTER TABLE spice ADD INDEX spice_idx (sname)
(b) CREATE INDEX spice_idx ON spice (sname)
(c) CREATE TABLE spice (INDEX spice_idx (sname))
(d) Both (a) and (b)

(d) Index can be created using one of the following methods

  • CREATE INDEX <index_name> ON <table_name> (col1, col2…)
  • ALTER TABLE <table_name> ADD INDEX <index_name> (col1, col2….)

Leave a Comment

Your email address will not be published.

error: Content is protected !!