Consider the table Projects given below: Table: Projects Pᵢd Pname Language Startdate Enddate P001 School Mana

Question 74 of 95

Q. Consider the table Projects given below: Table: Projects P_id Pname Language Startdate Enddate 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 Based on the given table, write SQL queries for the following:

(i) Add the constraint, primary key to column P_id in the existing table Projects.

(ii) To change the language to Python of the project whose id is P002.

(iii) To delete the table Projects from MySQL database along with its data.

Uttar Pradesh UpmspCBSE Class XII Board 2024Subjective· 3mImportance★★★★★

78% · 74/95 Questions

🔒 Locked · start free trial →

You're viewing a preview — the full solution, concept, methods & PYQ mapping are locked.

Start your 14-day free trial to unlock the full solution →

Concept understanding — Primary Key Identification

Primary Key Identification

Think about how you tell two students apart in a classroom. If you call out "Rahul," there might be three Rahul's. But if you say "Rahul Sharma, roll number 12," you've pointed to exactly one person. That unique identifier — the roll number — is doing the job of a primary key.

The Everyday Intuition

Every collection of things needs a way to refer to each item without confusion. In a library, every book has a unique accession number. In a bank, every account has an account number. In a school, every student has a unique admission number. These are not random — they are deliberately chosen so that no two items share the same identifier.

This is the core idea: a primary key is a single attribute (or a combination of attributes) that uniquely identifies each record in a table. No two rows can have the same primary key value, and no primary key can be empty (null).

The Precise Meaning

In database terms, a primary key serves two non-negotiable rules:

Uniqueness — every value in the primary key column must be different from every other value. If you have 100 students, you need 100 distinct roll numbers.
Non-null — every record must have a primary key value. You cannot have a student with "no roll number."

A primary key can be a single column, like a student ID. Or it can be a composite key — two or more columns taken together to create uniqueness. For example, in a table recording which books students have borrowed, neither "Student ID" alone nor "Book ID" alone is unique (a student borrows many books; a book is borrowed by many students over time). But the combination of "Student ID + Book ID + Date Borrowed" together can be unique.

Important

A primary key is not just any unique column. It is the official, chosen identifier for that table. A table can have many columns that are unique (like Aadhaar number, PAN number, and email), but only one of them is designated as the primary key. The others are called candidate keys — they could have been the primary key, but weren't chosen.

Why It Matters

Without a primary key, you cannot reliably update or delete a specific record. Imagine a school database where two students have the same name "Ananya Sharma." If you try to delete "Ananya Sharma," the database has no way to know which one you mean. With a primary key, you say "delete student with roll number 45" — and it's unambiguous. …

Start your 14-day free trial to unlock the full solution →

The table Projects stores information about software projects, and you need to perform three operations: adding a primary key constraint, updating a record, and dropping the table entirely.

For (i), you want to designate P_id as the primary key after the table has already been created. Use the ALTER TABLE command:

ALTER TABLE Projects ADD PRIMARY KEY (P_id);

This enforces uniqueness and non-null values on P_id, ensuring each project has a distinct identifier.

For (ii), you need to update the Language column for project P002 from C++ to Python. The UPDATE statement with a WHERE clause does this:

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

Only the row matching P_id = 'P002' will be modified; the rest remain unchanged. …

Start your 14-day free trial to unlock the full solution →

The queries demonstrate adding a primary key constraint to an existing table, updating a specific record, and permanently dropping a table with all its data.

Understanding the Task

You have a table called Projects that stores information about software development projects—their unique identifier, name, programming language, and timeline. The table exists, it holds data, and now you need to modify its structure and contents using SQL commands.

The first task asks you to retrofit a primary key constraint onto the P_id column. In an ideal world, you'd declare the primary key when creating the table, but sometimes tables are built without proper constraints and need correction later. A primary key ensures that every project has a unique, non-null identifier—no two projects can share the same P_id, and no project can exist without one.

The second task is a straightforward data update: the Hotel Management System project (P002) was initially developed in C++, but you need to change that language field to Python. This is a targeted modification of a single cell in one row.

The third task is more drastic—you want to remove the entire Projects table from the database, structure and data together. This is irreversible; once executed, the table and all four project records vanish.

The SQL Queries

(i) Adding a primary key constraint

ALTER TABLE Projects
ADD PRIMARY KEY (P_id);

The ALTER TABLE statement modifies the structure of an existing table. Here, ADD PRIMARY KEY (P_id) tells MySQL to enforce uniqueness and non-null rules on the P_id column. If any duplicate or null values already exist in P_id, MySQL will reject this command—you'd need to clean the data first.

Note

If P_id were already defined with a primary key during table creation (CREATE TABLE Projects (P_id CHAR(4) PRIMARY KEY, ...)), this step would be unnecessary. The ALTER TABLE approach is a corrective measure.

(ii) Updating the language for project P002

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

The UPDATE statement changes existing data. SET Language = 'Python' specifies the new value, and WHERE P_id = 'P002' ensures only the Hotel Management System row is affected. Without the WHERE clause, every project's language would become Python—a common and dangerous mistake.

Watch out

Always double-check your WHERE condition in an UPDATE or DELETE statement. Omitting it will modify or erase every row in the table. …

Start your 14-day free trial to unlock the full solution →

←Previous questionQ16 — Consider the table ORDERS given below and write the output of the SQL …→Next questionQ18 — Consider the tables Admin and Transport given below: Table: Admin Sᵢd …

Understanding the Task

The SQL Queries

🎓Unlock everything free for 14 days

Unlock everything free for 14 days