Question

Ms. Zoya is a Production Manager in a factory which packages mineral water. She decides to create a table in a database to keep track of the stock present in the factory. Each record of the table will have the following fields:

W_Code – Code of the item (type – CHAR (5))

W_Description – Description of the item (type – VARCHAR (20))

B_Qty – Balance quantity of the item (type – INTEGER)

U_Price – Unit Price of the item (type – FLOAT)

The name of the table is W_STOCK.

1. Write an SQL command to create the above table (W_Code should be the primary key).
   OR
2. Can U Price be the primary key of the above table? Justify your answer.
3. Assuming that the table w_STOCK is already created, write an SQL command to add an attribute E_Date (of DATE type) to the table.
   OR
4. Assuming that the table W_STOCK is already created, write an SQL command to remove the column B_Qty from the table.

Answer 1

1. 
   

   1. CREATE TABLE W_STOCK (
          W_Code CHAR(5) PRIMARY KEY,
          W_Description VARCHAR(20),
          B_Qty INTEGER,
          U_Price FLOAT
      );

      OR

   2. No, U_Price cannot be the primary key. A primary key must contain unique and non-null values. In a stock table, multiple items can have the same unit price, which violates the uniqueness constraint required for identification.
2. 
   
   1. To add the E_Date column to the existing table:
      

      ALTER TABLE W_STOCK ADD E_Date DATE;

       OR
   2. To remove the B_Qty column from the table: 
      

      ALTER TABLE W_STOCK DROP COLUMN B_Qty;