TOP

Prospects of OSS-DB

Exam Overview

OSS-DB Silver

OSS-DB Gold

Detailed Objectives

Schedule Exam

Sample Exam Questions

Japanese

 

 

Sample Questions of OSS-DB Exam Silver "Development/SQL"

[S3.2 Functions and operators (Aggregate functions)]

3.20

Choose one appropriate statement about the behavior
when you execute:

SELECT count(*), count(x) FROM sample_table;


  1. Both count(*) and count(x) return the number of rows in the sample_table.

  2. count(*) returns the number of rows in sample_table, and count(x) returns the number of different types of values in column x.
  3. count(*) returns the number of rows in sample_table, and count(x) returns the number of rows where the value in column x is not NULL.
  4. count(*) returns the number of rows in sample_table where no column is NULL, and count(x) returns the number of rows where the value in column x is not NULL.
  5. count(*) returns the sum of the values in the leftmost column of sample_table, and count(x) returns the sum of the values in column x.
Answer and Commentary

[S3.1 SQL commands (PL / pgSQL)]

3.19

We want to use PL/pgSQL to define a function like this:

create function test_func(a integer) returns integer as $$
declare
x record;
begin
select * into x from test_table
where id = a;
return x.value;
end;
$$ language plpgsql;

Choose an appropriate explanation for this.

  1. The create function fails because a semicolon is required after declare on the second line.

  2. The create function fails because the x record on line 3 is incorrect. The correct value is x test_table%ROWTYPE.
  3. The create function fails because a semicolon is required after begin on line 4.
  4. If the test_table referenced on line 5 does not exist, create function fails.
  5. If the id column referenced on line 6 does not exist, the create function succeeds, but a run-time error occurs when the function is called.
Answer and Commentary

[S3.1 SQL commands (Function definition / Procedure definition)]

3.18

CREATE FUNCTION func_add(x integer, y integer) RETURNS integer AS $$
SELECT x + y;
$$ LANGUAGE SQL;

Choose three appropriate explanations for the above.

  1. PL/pgSQL is used to define a function.

  2. It defines a function called func_add.
  3. SELECT func_add(2, 3) returns 5.
  4. ' (single quote) may replace $$ at the end of line 1 and the beginning of line 3.
  5. CREATE FUNCTION requires administrator privileges on the database.
Answer and Commentary

[S3.1 SQL commands (Partition)]

3.17

Choose the correct two steps for creating a partitioned table (declarative partitioning introduced in PostgreSQL version 10) and its partitions.

  1. Create a partitioned table using the CREATE PARTITIONED TABLE command.

  2. Create a partitioned table by specifying the PARTITIONED BY option in the CREATE TABLE command.
  3. Create partitions with the CREATE PARTITION command.
  4. Create a partition with the CREATE TABLE PARTITION command.
  5. Create a partition by specifying the PARTITION OF option in the CREATE TABLE command.
Answer and Commentary

[S3.1 SQL commands (Tablespace)]

3.16

Choose one incorrect use of the tablespace.

  1. A directory to be used as a tablespace is specified in the CREATE TABLESPACE command, but the directory must already exist and be empty.

  2. If a table is created by executing 'CREATE TABLE (... table_definition ...) TABLESPACE tablespace_name;', then the specified tablespace is used. 
  3. If you specify a tablespace name with the default_tablespace parameter, objects created such as by CREATE TABLE use the specified tablespace.
  4. To create objects in a tablespace, you must have CREATE privilege on the tablespace.
  5. When a tablespace is dropped with the DROP TABLESPACE command, the objects in it are also automatically dropped.
Answer and Commentary

[S3.1 SQL commands (Schema)]

3.15

The schema search path for user foo is set to “$user”, public by default.
The public schema has a table called bar, and all users (public) have been GRANTed privileges such as SELECT.
The schema foo exists, but there are no tables in it.
At this time, select all the correct statements from the following descriptions.

  1. SELECT * FROM bar; will result in an error as it fails to find the table foo.bar.

  2. CREATE TABLE bar(...); will result in an error because the table public.bar already exists.

     At this point, you execute CREATE TABLE foo.bar(...); to create two tables, foo.bar and public.bar.
     
  3. When you execute SELECT * FROM bar; it returns the data retrieved from both tables, foo.bar and public.bar.
  4. Executing DROP TABLE bar; will only remove the table foo.bar.
  5. Executing DROP TABLE bar; will only delete the table public.bar.
Answer and Commentary

[S3.1 SQL commands (Sequence)]

3.14

The following sequence of operations was performed. What value does the last SELECT statement return as currval?

create table test1(id integer, val varchar(10));
create table test2(id integer, val varchar(10));
create sequence seq1;
insert into test1(id, val) values (1, 'abc'), (2, 'def');
insert into test2(id, val) values (nextval('seq1'), 'xyz');
begin;
insert into test2(id, val) select nextval('seq1'), val from test1;
rollback;
insert into test2(id, val) values (nextval('seq1'), 'pqr');
select currval('seq1');
Answer and Commentary

[S3.1 SQL commands (Trigger)]

3.13

You defined statement-level BEFORE triggers, statement-level AFTER triggers, row-level BEFORE triggers, and row-level AFTER triggers for an UPDATE of a table. What is the order in which triggers execute when an UPDATE is performed on this table?

  1. BEFORE statement -> BEFORE row -> AFTER statement -> AFTER row

  2. BEFORE statement -> BEFORE row -> AFTER row -> AFTER statement
  3. BEFORE row -> BEFORE statement -> AFTER row -> AFTER statement
  4. BEFORE row -> BEFORE statement -> AFTER statement -> AFTER row
  5. BEFORE -> AFTER, but the order of execution at the statement and row levels is determined by the alphabetical order of the function name that defines the trigger.
Answer and Commentary

[S3.1 SQL commands (View)]

3.12

Choose two things you can do with the ALTER VIEW command.

  1. Add a column to a view.

  2. Remove a column from a view.
  3. Change the data type of a column in a view.
  4. Rename a view.
  5. Change the owner of a view.
Answer and Commentary

[S3.1 SQL commands (Index)]

3.11

When is an index automatically created when CREATE TABLE is executed? Choose all that apply.

  1. When a table has a FOREIGN KEY constraint

  2. When a NOT NULL constraint is applied to a column
  3. When a PRIMARY KEY constraint is applied to a column
  4. When a REFERENCES constraint is applied to a column
  5. When a UNIQUE constraint is applied to a column
Answer and Commentary

[S3.1 SQL commands (Table definition)]

3.10

Choose one that cannot be changed by ALTER TABLE.

  1. Name of the table

  2. Owner of the table
  3. Name of the column
  4. Data type of the column
  5. Order of the columns
Answer and Commentary

[S3.1 SQL commands (Data types)]

3.09

Choose one incorrect description of the data types available in PostgreSQL.

  1. integer is a 4-byte integer that can safely contain up to 10 digits.

  2. real and double precision are floating point real numbers, and double precision can perform more accurate operations.

  3. numeric is a numeric type that deals with integers or fractional numbers. Its precision can be defined by specifying the total number of digits and the number of digits after the decimal point.
  4. date is a data type that handles dates, and it does not hold time information.
  5. timestamp is a data type that handles dates and times, and it holds time data in microseconds.
Answer and Commentary

[S3.1 SQL commands (Partition)]

3.08

Choose 3 appropriate descriptions of declarative partitioning supported from version 10 of PostgreSQL and partitioning using inheritance supported from previous versions. Assume that the parent table is named parent and the partitions are named partition1, partition2, partition3 ....

  1. In either method, the parent table is created using the usual CREATE TABLE parent... and no special options are needed to use partitions.

  2. When creating a partition, the command is
    CREATE TABLE partition1 PARTITION OF parent...
    for declarative partitioning, and
    CREATE TABLE partition1 () INHERITS (parent)
    for inheritance, both using CREATE TABLE.

  3. In either method, partitions that will store the data are not automatically created when executing the INSERT statement, and partitions need to be created in advance.
  4. In either method, if the parent table and partitions are correctly created with the CREATE command,
    INSERT INTO parent...
    will automatically store the inserted data into the appropriate partition.
  5. In declarative partitioning, all partitions have the same columns and different columns cannot be added, but in partitioning using inheritance, different columns can be added for each partition.
Answer and Commentary

[S3.1 SQL commands (Materialized view)]

3.07

You created a materialized view mview that combines tables table1 and table2. Choose three appropriate statements.

  1. The command used to create it is in the format of
    CREATE MATERIALIZED VIEW mview AS SELECT ...  FROM table1, table2...

  2. Rather than executing the SELECT statement used in the AS clause during creation,
    SELECT * FROM mview
    is usually faster.

  3. The rows included in the result of executing the SELECT statement used in the AS clause during creation and the result of executing
    SELECT * FROM mview
    are always the same.
  4. You updated a row in table1 that is the source of mview, but the SELECT from mview showed the old data prior to the update.
  5. Although the size of the source table table2 is huge, only the definition statement of mview is saved in the database. Therefore, you do not have to worry about running out of disk space when creating a materialized view.
Answer and Commentary

[S3.1 SQL commands (Procedure definition)]

3.06

Choose two appropriate explanations for the differences between the procedures (PROCEDURE) added in PostgreSQL version 11 and the functions (FUNCTION) supported from previous versions.

  1. A PROCEDURE is created with the CREATE PROCEDURE command, and a FUNCTION is created with the CREATE FUNCTION command.

  2. While PROCEDURE is defined in the SQL standard, FUNCTION is a PostgreSQL-specific extension.

  3. FUNCTION of type void is being deprecated and it is recommended to replace it with PROCEDURE.
  4. A FUNCTION is executed as SELECT func_name(arg1, arg2 ...), whereas a PROCEDURE is executed as CALL proc_name(arg1, arg2 ...).
  5. While a PROCEDURE can update data in the database, a FUNCTION can only perform calculations based on the data and cannot update the data.
Answer and Commentary

[S3.2 Functions and operators (Date/Time functions)]

3.05

Select statement for a function that returns the current time, current_timestamp, was executed twice in succession as follows:

select current_timestamp;
select current_timestamp;

Both times, the same value was returned. Choose the most appropriate reason for this.

  1. The server machine's hardware clock is faulty.

  2. The timed command is not executed on the server machine, so the time is not accurately maintained.

  3. The same time was returned because the two SELECT statements were executed quickly in succession, without any gap.
  4. The BEGIN command was executed just before these SELECT statements.
  5. When executed from the same session, current_timestamp always returns the same value.
Answer and Commentary

[S3.1 SQL commands (Partition)]

3.04

Choose all the potential benefits of using table partitioning.

  1. Reduce the disk space occupied by data.

  2. Significantly improve the performance of queries.

  3. Allocate frequently accessed rows and less frequently accessed rows to different disk spaces.
  4. Allocate frequently accessed columns and less frequently accessed columns to different disk spaces.
  5. Execute DROP TABLE instead of DELETE to speed up the bulk deletion of large amounts of data.
Answer and Commentary

[S3.1 SQL commands (Tablespace)]

3.03

Choose three correct ways to use tablespaces.

  1. Place the new database created by CREATE DATABASE in a tablespace different from the default one.

  2. Place the new schema created by CREATE SCHEMA in a tablespace different from the default one.
  3. Place the new table created by CREATE TABLE in a tablespace different from the default one.
  4. Place the new view created by CREATE VIEW in a tablespace different from the source table.
  5. Place the new index created by CREATE INDEX in a tablespace different from the source table.
Answer and Commentary

[S3.1 SQL commands (DELETE statement)]

3.02

Choose the two correct descriptions for materialized views and regular views.

  1. Both are created with the CREATE VIEW command.

  2. If the view definitions are the same, searching from materialized views is generally faster.

  3. If the view definitions are the same, a SELECT from either will always return the same result.

  4. Materialized views require disk space according to the amount of data they contain, while regular views do not require disk space.

  5. To modify a query that defines a view, the CREATE OR REPLACE VIEW command is used for a regular view, whereas a special command called REFRESH MATERIALIZED VIEW is used for a materialized view. 

Answer and Commentary

[S3.1 SQL commands (DELETE statement)]

3.01

The primary key column of table xxx is an integer type id, and the primary key column of table yyy is also an integer type id.
If both xxx and yyy have rows with the same id, you want to delete all those rows from xxx.
For example, if 'select id from xxx' returns 1, 2, and 3, and 'select id from yyy' returns 2, 3, and 4, you want to delete the rows with id 2 and 3 from xxx. Which SQL statement accomplishes this?

  1. DELETE FROM xxx, yyy WHERE xxx.id = yyy.id;

  2. DELETE FROM xxx, yyy WHERE xxx.id IN (yyy.id);

  3. DELETE FROM xxx JOIN yyy USING xxx.id = yyy.id;

  4. DELETE FROM xxx WHERE id IN (SELECT id FROM yyy);

  5. DELETE FROM xxx WHERE EXISTS (SELECT id FROM yyy);

Answer and Commentary

LPI-Japan Platinum Sponsors

  • 株式会社リンク
  • Citrix社
  • 株式会社コンピュータマネジメント
  • サイバートラスト株式会社
  • 株式会社デージーネット
  • 株式会社エンベックス
  • 富士通株式会社
  • 株式会社GOSPA
  • 株式会社日立製作所
  • 株式会社アイエスエフネット
  • KENスクール
  • 株式会社リーディング・エッジ社
  • ミネベアソフトウェアソリューションズ株式会社
  • 日本電気株式会社
  • NECソリューションイノベータ株式会社
  • 株式会社アウトソーシングテクノロジー
  • 株式会社Ping-t
  • リスキルアカデミー
  • 株式会社ソルクレオ
  • 株式会社ゼウス・エンタープライズ

© EDUCO (General Incorporated Association Educo) all rights reserved.