Question

Consider the following data.   

STUDENT
StudentID	SName	Gender	Age	ClubID
3234	Alfred Smith	Male	20	BSK
2244	McJohnson Robert	Male	22
2389	Jessica Low	Female	20	JPA
4211	Roland Devingo	Male	24
4383	Jane Usa Khan	Female	21	BKY
4450	Elaine Fong	Female	20	JPA

CLUB
ClubID	CName	Founded	Budget
BKY	Bakery Club	2010	2546
PDC	Photomedia and Design	2005	1345
JPA	Japanese Anime	2009	3453
BSK	Basketball	2011	6744

If the database administrator in the University has turned off auto-commit, consider the following:

Student 4211 has joined the Photomedia and Design club. When the system admin wanted to update the database, she wrote the following statement:

UPDATE STUDENT

SET CLUBID = “PDC”

Answer the following:

1. How does the above SQL statement produce false information in the Student table?

Answer 1

Given statement:

UPDATE STUDENT

SET CLUBID = “PDC”

a.How does the above SQL statement produce false information in the Student table?

Answer:

• This statement will update all the records in the "STUDENT" table with the CLUBID = "PDC"
• for example: Above statement will generate the below result:

               STUDENT TABLE:

StudentID	ClubID
3234	PDC
2244	PDC
2389	PDC
4211	PDC
4383	PDC
4450	PDC

Correct statement:

UPDATE STUDENT

SET CLUBID = “PDC”

WHERE StudentID = "4211";

• Above sql statement will only update the CLUBID of student with ID :4211.
  • Above statement will generate the below result:

•                STUDENT TABLE:

  StudentID	ClubID
  3234	BSK
  2244
  2389	JPA
  4211	PDC
  4383	BKY
  4450	JPA

Note:

• To update only one record , a unique identification of that record should be used as a constraint to identify that specific record.