Question

Consider the table Projects given below: \[ \begin{array}{|c|l|c|c|c|} \hline \textbf{P\_id} & \textbf{Pname} & \textbf{Language} & \textbf{Startdate} & \textbf{Enddate} \\ \hline P001 & School Management System & Python & 2023-01-12 & 2023-04-03 \\ P002 & Hotel Management System & C++ & 2022-12-01 & 2023-02-02 \\ P003 & Blood Bank & Python & 2023-02-11 & 2023-03-02 \\ P004 & Payroll Management System & Python & 2023-03-12 & 2023-06-02 \\ \hline \end{array} \] Based on the given table, write SQL queries for the following:

1. Add the constraint, primary key to column P_id in the existing table Projects:
2. Change the language to Python of the project whose ID is P002:
3. Delete the table Projects from the MySQL database along with its data:

Hint:

Use ALTER TABLE} to modify table structure, UPDATE} to change data, and DROP TABLE} to permanently remove a table from the database.

Answer 1

Query (i): Enforce primary key constraint on the P_id column.

    ALTER TABLE Projects
    ADD PRIMARY KEY (P_id);
    

Explanation: The ALTER TABLE statement is used to modify a table's structure. The ADD PRIMARY KEY clause designates the P_id column as the primary key, ensuring unique identification for each record. Query (ii): Update the project with ID P002 to use Python.

    UPDATE Projects
    SET Language = "Python"
    WHERE P_id = "P002";
    

Explanation: The UPDATE statement modifies existing records. The SET clause changes the Language column to "Python" for the record where P_id is "P002". The WHERE clause targets this specific record for the update. Query (iii): Remove the Projects table and all its data.

    DROP TABLE Projects;
    

Explanation: The DROP TABLE statement permanently deletes the Projects table, including all its data and structure. This operation is irreversible; exercise caution.