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): Add the primary key constraint to the column P\_id.

    ALTER TABLE Projects
    ADD PRIMARY KEY (P_id);
    

Explanation: The ALTER TABLE statement modifies the structure of the table. The ADD PRIMARY KEY command assigns the primary key constraint to the P\_id column, ensuring that it uniquely identifies each record in the table. Query (ii): Change the language to Python for the project with ID P002.

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

Explanation: The UPDATE statement modifies existing records in the table. The SET clause updates the Language column to "Python" for the row where the P\_id is "P002". The WHERE clause ensures that only the specified row is updated. Query (iii): Delete the table Projects along with its data.

    DROP TABLE Projects;
    

Explanation: The DROP TABLE statement permanently deletes the table Projects from the database, including all its data and structure. This action cannot be undone, so use it with caution.