Question

A departmental store MyStore is considering to maintain their inventory using SQL to store the data. As a database administer, Abhay has decided that : 

• Name of the database - mystore 

• Name of the table - STORE 

• The attributes of STORE are as follows: 

ItemNo - numeric 

ItemName – character of size 20 

Scode - numeric 

Quantity – numeric

Table: STORE
Item No	Item Name	Scode	Quantity
2005	Sharpener Classic	23	60
2003	Ball Pen 0.25	22	50
2002	Get Pen Premium	21	150
2006	Get Pen Classic	21	250
2001	Eraser Small	22	220
2004	Eraser Big	22	110
2009	Ball Pen 0.5	21	180

(a) Identify the attribute best suitable to be declared as a primary key,

(b) Write the degree and cardinality of the table STORE.

(c) Insert the following data into the attributes ItemNo, ItemName and SCode respectively in the given table STORE.

ItemNo = 2010, ItemName = “Note Book” and Scode = 25

(d) Abhay want to remove the table STORE from the database MyStore. Which command will he use from the following: 

i) DELETE FROM store; 

ii) DROP TABLE store; 

iii) DROP DATABASE mystore; 

iv) DELETE store FROM mystore;

(e) Now Abhay wants to display the structure of the table STORE, i.e, name of the attributes and their respective data types that he has used in the table. Write the query to display the same.

Answer 1

(a) ItemNo 

(b) Degree = 4 Cardinality = 7 

(c) INSERT INTO store (ItemNo,ItemName,Scode) VALUES(2010, “Note Book”,25); 

(d) DROP TABLE store; 

(e) Describe Store;