Sample Exam Questions
From the objective of OSS-DB Exam Silver
S3.1 SQL commands (UPDATE statement)
Sample Question
3.32
The following sequence of SQL statements is executed. What is the value returned by the last sum?
create table sample (id integer primary key, val integer);
insert into sample(id) values (1), (2), (3), (4), (5);
update sample set val = id;
update sample set val = val * 2 where id > 2;
update sample set val = val + 1 where val < 4;
select sum(val) from sample;
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2024/05/02
Answer and Explanation
Let's look at each SQL statement in order.
The first line, 'create table', creates a 'sample' table with a primary key in the 'id' column and an integer 'val' column.
The second line, 'insert', creates five rows of data with 'id' values of 1, 2, 3, 4, and 5. All 'val' values are set to null.
The third line, 'update', sets each row's 'val' column to the same value as 'id'. At this point, the values of ('id', 'val') are (1, 1), (2, 2), (3, 3), (4, 4), and (5, 5) respectively.
The fourth line, 'update', doubles the value of 'val', but the 'where' clause only targets columns where the 'id' value is greater than 2. After the update, the values of ('id', 'val') are (1, 1), (2, 2), (3, 6), (4, 8), and (5, 10).
The fifth line, 'update', adds 1 to the value of 'val', but the 'where' clause only targets columns where the 'val' value is less than 4. After the update, the values of ('id', 'val') are (1, 2), (2, 3), (3, 6), (4, 8), and (5, 10).
The final 'select' statement, 'sum(val)', calculates the sum of the values in the 'val' column, which is 2+3+6+8+10, or 29.
Therefore, the correct answer is 29.
© EDUCO All Rights Reserved.