Mr. Sumit Agarwal is the owner of his parental firm Ramdas Gopal das & 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.
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 TABLE spice(scode, sname, price, stock)
(D) SELECT TABLE spice(scode integer, sname char(25), price decimal, stock integer)
Answer:
The syntax to create a table in SQL is:
CREATE TABLE table_name (
column1 datatype ,
column2 datatype ,
column3 datatype ,
....
);
Checking the options
- (A) CREATE TABLE spice(scode integer, sname char(25), price decimal, stock integer) - It is the correct syntax for creating a table in SQL
- (B) CREATE spice(scode integer, sname char(25), price decimal, stock integer) - It is not the correct syntax for creating a table in SQL because CREATE should be followed by TABLE
- (C) CREATE TABLE spice(scode, sname, price, stock) - It is not the correct syntax for creating a table in SQL because datatype is not included while mentioning columns
- (D) SELECT TABLE spice(scode integer, sname char(25), price decimal, stock integer) - It is not the correct syntax for creating a table in SQL because we have to CREATE, not SELECT
So, the correct answer is (A)
Question 2 (ii)
To add records in the table which of the following SQL statement is used?
(A) ADD
(B) SELECT
(C) INSERT
(D) INSERT INTO
Answer:
The syntax to add records to a table in SQL is :
INSERT
INTO
table_name
(
column1
,
column2
,
column3
, ...)
VALUES
(
value1
,
value2
,
value3
, ...);
OR
INSERT
INTO
table_name
VALUES
(
value1
,
value2
,
value3
, ...);
Checking the options
- (A) ADD - ADD command adds a column to the table, not a record (row). Thus, it is not the correct statement to add records to a table in SQL.
- (B) SELECT - SELECT command selects a row. Thus, it is not the correct statement to add records to a table in SQL.
- (C) INSERT - The INSERT command is always used with INTO. Thus, it is not the correct statement to add records to a table in SQL.
- (D) INSERT INTO - It is the correct statement to add records to a table in SQL.
So, the correct answer is (D)
Question 2 (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)
Answer:
The syntax to add a new column to an existing table in SQL is:
ALTER
TABLE
table_name
ADD
column_name datatype
;
Checking the options:
- (A) ALTER TABLE spice ADD (exp_date date) - It is the correct syntax to add a new column to the existing table.
- (B) UPDATE TABLE spice ADD (exp_date date) - Update command modifies existing records in a table. Thus, It is not the correct syntax to add a new column to the existing table.
- (C) MODIFY spice ADD (exp_date date) - Modify is not a valid command in SQL. Thus,It is not the correct syntax to add a new column to the existing table.
- (D) ALTER TABLE spice MODIFY (exp_date date) - In an alter table statement in SQL, the table name is followed by the keyword ADD not MODIFY. Thus, it is not the correct syntax to add a new column to the existing table.
So,the correct answer is (A)
Question 2 (iv)
To view the table structure of the above crated table the command is
(A) SELECT spice
(B) DESC spice
(C) DESCRIBE spice
(D) Both (b) and (c)
Answer:
The syntax to view a table structure in SQL is:
DESCRIBE
table_name;
OR
DESC
table_name;
Checking the options
- (A) SELECT spice - SELECT command displays the data stored in a table, not the table structure.Thus, It is not the correct syntax to view table structure in SQL
- (B) DESC spice - It is the correct syntax to view table structure in SQL
- (C) DESCRIBE spice - It is the correct syntax to view table structure in SQL
- (D) Both (b) and (c)
Both options (B) and (C) are correct.
So, the correct answer is (D)
Question 2 (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_idxON spice (sname)
(C) CREATE TABLE spice (INDEX spice_idx (sname))
(D) Both (a) and (b)
Answer:
The syntax to create an index in SQL is:
CREATE
INDEX
index_name
ON
table_name
(
column1
,
column2
, ...);
OR
ALTER
TABLE
table_name
ADD INDEX
index_name (column1,column2, ...)
;
Checking the options
- (A) ALTER TABLE spice ADD INDEX spice_idx (sname) - It is the correct syntax to create an index in SQL
- (B) CREATE INDEX spice_idxON spice (sname) - It is the correct syntax to create an index in SQL
- (C) CREATE TABLE spice (INDEX spice_idx (sname)) - CREATE TABLE command creates a new table, not a new index. Thus, It is not the correct syntax to create an index in SQL
- (D) Both (a) and (b)
Both options (A) and (B) are correct.
So, the correct answer is (D)
