Bright Tutorials Bright Tutorials
CBSE Class 12 Computer Science Question 80 of 91

Relational Database and SQL — Question 41

Back to all questions
41
Question

Question 32

A department store MyStore is considering to maintain their inventory using SQL to store the data. As a database administrator, 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

ItemNoItemNameScodeQuantity
2005Sharpener Classic2360
2003Ball Pen 0.252250
2002Gel Pen Premium21150
2006Gel Pen Classic21250
2001Eraser Small22220
2004Eraser Big22110
2009Ball Pen 0.521180

(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 wants to remove the table STORE from the database MyStore. Which command will he use from the following?

  1. DELETE FROM STORE;
  2. DROP TABLE STORE;
  3. DROP DATABASE MYSTORE;
  4. 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

(a) ItemNo attribute is best suitable to be declared as a primary key as it uniquely identifies each item in the inventory.

(b) The degree of the table STORE is 4, and the cardinality of the table STORE is 7.

(c)

INSERT INTO STORE(ItemNo, ItemName, Scode)
VALUES(2010, 'Note Book', 25);

(d) DROP TABLE STORE;
Reason — DROP TABLE command is used to remove/delete a table permanently. The syntax is : DROP TABLE <table_name>;. Hence, according to this DROP TABLE STORE; is the correct command to remove the STORE table from the database MyStore.

(e)

DESCRIBE STORE;
Output
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| ItemNo   | int      | NO   | PRI | NULL    |       |
| ItemName | char(20) | YES  |     | NULL    |       |
| Scode    | int      | YES  |     | NULL    |       |
| Quantity | int      | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+