Question

Nisha is assigned the task of maintaining the staff data of an organization. She has to store the details of the staff in the SQL table named EMPLOYEES with attributes as EMPNO, NAME, DEPARTMENT, BASICSAL to store Employee’s Identification Number, Name, Department, and Basic Salary respectively. There can be two or more Employees with the same name in the organization.
(i)
(a) Help Nisha to identify the attribute which should be designated as the PRIMARY KEY. Justify your answer.
OR
(b) Help Nisha to identify the constraint which should be applied to the attribute NAME such that the Employees’ Names cannot be left empty or NULL while entering the records but can have duplicate values.
(ii)
(a) Write the SQL command to change the size of the attribute BASICSAL in the table EMPLOYEES to allow the maximum value of 99999.99 to be stored in it.
OR
(b) Write the SQL command to delete the table EMPLOYEES.

Hint:

PRIMARY KEY must be unique and NOT NULL.
Use NOT NULL for fields that must always have a value.
Use ALTER TABLE to modify and DROP TABLE to delete.

Answer 1

(i) (a)
The attribute designated as the PRIMARY KEY should be EMPNO.
This is because EMPNO (Employee Number) provides unique identification for each employee,
guaranteeing the uniqueness of each record within the table.
Unlike NAME, which permits repetition, EMPNO requires uniqueness.
OR
(i) (b)
To prevent the NAME attribute from being empty,
implement the NOT NULL constraint.
This permits duplicate names but disallows NULL values.
Example: NAME VARCHAR(50) NOT NULL.
(ii) (a)
ALTER TABLE EMPLOYEES MODIFY BASICSAL DECIMAL(7,2);
This command modifies the data type of BASICSAL to accommodate
a maximum of 7 digits, with 2 digits reserved for decimal places, supporting values up to 99999.99.
OR
(ii) (b)
DROP TABLE EMPLOYEES;
This SQL command permanently deletes the entire EMPLOYEES table
and all its associated data.