Write a single SQL statment that Updates the gpa in the student relation accordi

Question

Write a single SQL statment that Updates the gpa in the student relation according to this rule:

• if it is negative, set it to 0

• if it is larger than 4,then set it to 4

• if it is NULL, set it to 3

• if none of the above applies do not change the gpa

book bookid title Introduction of Algorithms rice total copies 84.66 Database System Concepts74.99 Stochastic Calculus for Financ41.02 Stochastic Calculus for Finance II 55.22 course title Algorithms DB Organization Advanced DB Organization Math Finance I Math Finance II faculty enroll studentid courseid student studentid facultyid name gpa Tom 3.3 John3.8 Mary 3.0 Kris 3.6 Alex3.5 James 70000 Sarah 60000 ay 80000 Rache 70000 Pau85000 book_ checkout bookid studentid 2017-08-29 2017-09-02 2017-09-07

Explanation / Answer

Steps :

First we need to findout the all studentid's and put into where clause to update those records, it's increase performance.

"select studentid from student where gpa <0 or gpa > 4 or gpa is null"

Step 2:

Prepare update query based on cases as below

CASE

WHEN gpa < 0 THEN 0

WHEN gpa > 4 THEN 4  

WHEN gpa is null THEN 3

END

Step 3:

Update the respective table column by using SET, if you want to update multiple columns then you can fallowed by comma.

UPDATE Student

SET gpa =

Complete Query :

UPDATE Student

SET gpa =  

CASE

WHEN gpa < 0 THEN 0

WHEN gpa > 4 THEN 4  

WHEN gpa is null THEN 3

END

WHERE studentid IN (select studentid from student where gpa <0 or gpa > 4 or gpa is null);

HopefullyI gave good explanation, thanks.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.