> Quizzes > Quizzes 2020 -- Oracle / Postgresql -- Best Pratices / How to

In the page Prices and Donations, you can find how to have the solutions of the quizzes.

Nella pagina Prezzi e Contatti potete vedere come avere le soluzioni dei quiz.

If it is not specified, the commands must be executed connected as a database "superuser".
About the solutions:
- could be more than one. Here is one solution that often takes into account of the performance tuning
- they could change because of the database version
- it is not warranted to be error-free in all the database environments

December - MERGE command

Level (*) : 2
Topic (*) : Progr

You have a sequence called SEQ and a table called CALC_HIST:

  CREATE TABLE calc_hist
   ,name  VARCHAR2(10)
   ,season  VARCHAR2(10)
   ,competition VARCHAR2(10)
   ,match  INT
   ,gol   INT
   ,CONSTRAINT calc_hist_pk PRIMARY KEY (id)
   ,CONSTRAINT calc_hist_uq UNIQUE (name, season, competition)
In it, there are these 2 rows:
  INSERT INTO calc_hist (name, season, competition, match, gol)
  VALUES ( 'Ronaldo', '2018/19', 'A', 31, 21 );

  INSERT INTO calc_hist (name, season, competition, match, gol)
  VALUES ( 'Ronaldo', '2019/20', 'A', 8, 5 );
You have to write a MERGE sql command in order to INSERT or UPDATE the data inside that table.
For instance after launcing twice that SQL (with different values), you have to see 3 rows like these:

 __ _______ __________ ______________ ________ ______
  1 Ronaldo   2018/19    A                  31     21
  2 Ronaldo   2019/20    A                  33     31
  4 Ronaldo   2020/21    A                  16     15

November - Oracle aggregate function

Level (*) : 2
Topic (*) : Progr

You have a table called MANAGERS:
  CREATE TABLE managers
   (id  INT
   ,season  VARCHAR2(10)
   ,name  VARCHAR2(10)
   ,match  INT
In it, there are these rows:
  INSERT INTO managers
  VALUES ( 1, '1998/99', 'Lippi', 33 );

  INSERT INTO managers
  VALUES ( 1, '1998/99', 'Ancelotti', 20 );

  INSERT INTO managers
  VALUES ( 1, '1999/00', 'Ancelotti', 52 );

  INSERT INTO managers
  VALUES ( 1, '2000/01', 'Ancelotti', 42 );
You have to write a query finding the seasons with more then 50 matches.

The result must be like this:
  ----- ------------------------
     53 Lippi, Ancelotti 1998/99
     52 Ancelotti 1999/00

October - Oracle Date function

Level (*) : 2
Topic (*) : Progr

In your db there is this table with 4 rows:
  CREATE TABLE players (
   name varchar2(30),
   date_birth varchar2(10),
   date_100_gol varchar2(10) );

  INSERT INTO players VALUES ('Baggio','1967-02-18','1994-02-27');
  INSERT INTO players VALUES ('Del Piero','1974-11-09','2001-06-10');
  INSERT INTO players VALUES ('Platini','1955-06-21','1986-09-21');
  INSERT INTO players VALUES ('Trezeguet','1977-10-15','2005-09-14');
You have to write a function called fz_age_100_gol.
The result of this function must be the difference (in years and days) between the above dates.

  SELECT fz_age_100_gol ( date_birth, date_100_gol ) || ' ' || name  age_100_gol
  FROM players

  26 213 Del Piero
  27   9 Baggio
  27 334 Trezeguet
  31  92 Platini
Simple part of that issue is the "day alignment": the number "9" of Baggio must be under the "3" of Del Piero and not under the "2".

September - Char data

Level (*) : 1
Topic (*) : Progr/Postgres

You have this table:
  CREATE TABLE t_char 
   (id INT, descr VARCHAR(30));
In it these is this row:
  SELECT * FROM t_char;

   id |     descr         
    1 | He's the best 
Write the SQL command that can be used to insert that row.


In Oracle and in postgres, to do that insert you can use this simple SQL:
    1, 'He''s the best' );
Then to write a single quote, you must enter 2 single quotes.

August - Foreign Key problem

Level (*) : 2
Topic (*) : DBA/Progr/Postgres

This quiz is valid for Oracle, Postgres and others RDBMS.
You have these tables and constraints:
   cod	VARCHAR(15)
  ,nome	VARCHAR(30)
  ,cognome	VARCHAR(30)
  ,nick_1	VARCHAR(30)

   id  INT
  ,cod_pers	VARCHAR(20)  
  ,cod_ruolo	VARCHAR(20)

  FOREIGN KEY (cod_pers)
You have these rows:
  insert into t1 values (
  'Mancini','Roberto','Mancini','R.Mancini' );

  insert into t2 values (
  1, 'Mancini','CALC');

  insert into t2 values (
  2, 'Mancini','ALLEN');
You want to modify the parent row:
  update t1
  set cod = 'R_Mancini'
  where cod = 'Mancini';
But you can't and in Oracle you get this error:
  ORA-02292: integrity constraint (ADMIN.T2_FK) violated - child record found
While in Postgres, you get this:
  ERROR:  update or delete on table "t1" violates foreign key constraint "t2_fk" on table "t2"
  DETAIL:  Key (cod)=(Mancini) is still referenced from table "t2".
Which SQL command, can you use to avoid it?
(you don't want to use deferred constraints)

July - Postgres parameter

Level (*) : 2
Topic (*) : Postgres

You have installed postgres version 12 in the machines called db01, db02.
They are in master-slave streaming replication configuration (db02 is a hot standby db).

For the first time, you are doing a switch over between them: some macrostep you have done:
- promote db02 to master
- pg_rewind on db01

Before starting the new slave (db01), you must change one its parameter.
Which one?
Which is a simple way to do it?

June - Postgres startup error

Level (*) : 2
Topic (*) : Postgres

You have installed postgres version 12 in the machines called db01, db02.
You want to configure a master-slave streaming replication between them (db02 must be a hot standby db).

You are trying to use same steps you have used in postgres 10 and 11.
But when you try to startup the slave db in db02, you can't do it and receive the error:
 FATAL: XX000: using recovery command file "recovery.conf" is not supported

How can avoid it?

May - Oracle update

Level (*) : 1
Topic (*) : Progr

You have a table called T_UPD with these 2 columns:
  col_1  INT
  col_2  VARCHAR2(100)
Inside it, you have rows like these:
  col_1	col_2
  -----  --------------------
      1  123 is the old value
      2  77 is the old value
      3  the old value is 43
   9999  the old value is 5436
In the column "col_2", you want to replace the string "old value" with "new value" for all the rows with "col_1 > 10".
How can you do it?


To change the string, you should use the "replace" function.
Then this is the update:
  UPDATE t_upd
  SET col_2 = replace(col_2, 'old value', 'new value')
  WHERE col_1 > 10;

April - Oracle virtual column

Level (*) : 2
Topic (*) : DBA

You are in Oracle 18c and you want to create a table T1 with 3 colums:
 - c_1 : it must be a virtual column valued with as 'c_2 || '-' || c_3'
 - c_2 : varchar2(10)
 - c_3 : varchar2(10)
Write the SQL to create that table.
And write a query reading the Oracle dictionary to see that C_1 is a virtual column.

March - Oracle JSON

Level (*) : 2
Topic (*) : DBA/Progr

You are in Oracle 18c and you have a table called FILMS with a JSON column:
 CREATE TABLE   films  (
  ,nome	VARCHAR2(40)
  ,cod_stato	VARCHAR2(9)
  ,anno		VARCHAR2(9)
  ,js_soldi	VARCHAR2(255) 
  ,CONSTRAINT films_js_chk CHECK (js_soldi IS JSON)

The "js_soldi" column contains keys such as "gros_ita_eur", "gros_w_usd", "costo_usd", etc.
Here there are some of its rows:
  ---------- ------------------------------------------------------
  Tolo Tolo  {"gros_ita_eur" : 46174075}
  Avatar     {"gros_ita_eur" : 65666319, "gros_w_usd" : 2789958507}

You have to find a query in order to get the 3 highest grossing films:
In the table, this info has the key called "gros_w_usd" (you must divide its value by 1000000 to have the result in million dollars).

Example of the result:
  --------------- -----------------
             2798 Avengers: Endgame
             2790 Avatar
             2187 Titanic

February - Postgres JSONB and Unix time stamp

Level (*) : 2
Topic (*) : Postgres/Progr

In your db, there is a table called CUST.
It is like this:
                    Table "public.cust"
    Column   |  Type   | Collation | Nullable | Default 
   id        | integer |           | not null | 
   name      | text    |           |          | 
   birthinfo | jsonb   |           |          | 
     "cust_pk" PRIMARY KEY, btree (id)

In it, there is data like this:
   id |   name     |             birthinfo                        
    1 | Loris Assi | {"city": "Augusta", "date": 203644800000, "pr": "SR"}

The date information is in Unix time stamp format (milliseconds from January 1st, 1970).
You have to find a query in order to convert that date in 'yyyymmdd' format.

Then the query result should be like this:
   id |    name    | birthdate 
    1 | Loris Assi | 19760615

January - Postgres backup error

Level (*) : 2
Topic (*) : Postgres

Using Barman, we launch a backup of a Postgres cluster database called "c3".
We receive the following error:
 barman@dblor:~$ barman backup c3
 Starting backup using postgres method for server c3 in /u1/barman/c3/base/20191219T105926
 Backup start at LSN: 16/FF34BB88 (0000000100000016000000FF, 0034BB88)
 Starting backup copy via pg_basebackup for 20191219T105926
 ERROR: Backup failed copying files.
 DETAILS: data transfer failure on directory '/u1/barman/c3/base/20191219T105926/data'
 pg_basebackup error:
 pg_basebackup: initiating base backup, waiting for checkpoint to complete
 pg_basebackup: could not initiate base backup: SSL SYSCALL error: EOF detected
 pg_basebackup: removing contents of data directory "/u1/barman/c3/base/20191219T105926/data"

Which could be the reason of that error?
And what you can do in order to avoid it?

1: easy solution
2: medium solution
3: hard (or long) solution

quiz usually suitable for Oracle DBAs
Progr: quiz usually suitable for PL/SQL or SQL Programmers
DBA/Progr: quiz suitable for Oracle DBAs and for Programmers
Postgres: regarding the Postgresql database


This website is not connected with Oracle Corporation or the official Oracle website.
Some words used here are registered trade marks of their owners.