> Quizzes > Quizzes 2021 -- 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 - Postgres loading

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

You are connected in Postgres 12 and you have to load a file called numbers_list.txt and containing a lot of telephone numbers like these:

You have to load them in the C1 column on a table called T_2C like this:
  Column  |         Type          | Collation | Nullable | Default


  c1      | character varying(20) |           | not null | 
  c2      | character varying(20) |           | not null |

In every rows, the C2 column must contain the string "Offer 1".
How can you do this loading?

November - Postgres upgrade

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

Your postgres db is in a Debian server and the db version is 12.4. You want to upgrade it to the version 12.9.

Which steps you should do in order to do that upgrade?

October - Postgres and in Oracle catalogs

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

You have to find two queries (one in Oracle and one in Postgres) in order to get the owner and the name of the table with longer name where you are connected:
- if you are in Oracle: in your schema
- if you are in Postgres: in the "public" schema on the database where you are

September - Oracle constraints

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

You are trying to DELETE a record from C_CLUB table but you get an error:

  SQL> delete c_club where id = 1;

  Error starting at line : 1 in command -

  delete c_club where id = 1

  Error report -

  ORA-02292: integrity constraint (ADMIN.CLUB_HI_FK) violated - child record found

Then now you have to find 2 queries from the Oracle dictionary in order to get:
1. the child table having the CLUB_HI_FK constraint
2. the columns in C_CLUB and in the "child table" used by the CLUB_HI_FK constraint

August - Oracle index rebuild

Level (*) : 1
Topic (*) : DBA

You are connected in a database (not PRODUCTION env) and in your schema all the indexes are not partitioned.
Write a query reading a static data dictionary view in order to obtain the SQL commands to rebuild all your indexes bigger than 64 KB.

For instance, launching that query you have to obtain a result like this:
  alter index PUBL_UQ rebuild;
  alter index T_VAL_UQ rebuild;
  alter index CALC_H_PK rebuild;
  alter index CLUB_IDX rebuild;

To get these information, you should use the USER_SEGMENTS:
  SELECT 'alter index ' || segment_name || ' rebuild;'
  FROM user_Segments  
  WHERE segment_type = 'INDEX' and bytes > 64*1024;

July - Oracle functions

Level (*) : 1
Topic (*) : Prog

You have a table T1 with 99 rows created like this:
   (c1 INT,
    c2 INT,

         DBMS_RANDOM.VALUE (0,10000)
  FROM all_objects
  WHERE rownum<100;
You have to find a query in order to get these information:
- the number of total rows of T1
- sum of C2 values
- average values of C2 (with one digit after the comma)

Result example:
   COUNT       SUM       AVG
  _______ _________ _________
      99     420439    4246.9

To get these information, you should use this query:
  SELECT count(*) count, 
         sum(c2) sum, 
         round(avg(c2),1) avg
  FROM t1;

June - Oracle tablespaces

Level (*) : 1
Topic (*) : DBA

You have to find a query in order to get the tablespace names occupied from the objects of your schema.
For instance, you must have a result like this:
 __________________ ________________
 INDEX              TBL_IDX
 TABLE              TBL_DATA

To get these information, you can use the USER_SEGMENTS:
 SELECT distinct segment_type, tablespace_name
 FROM user_segments

May - Oracle column data default

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

You are connected to the db thru the user called ADMIN.
You have to find a query in order to get the columns with a data default value.

For instance, you have to receive a result like this:
  __________ _____________ __________________________
  CITIES       ID           "ADMIN"."SEQ_5"."NEXTVAL"
  OBJECTS      ID           "ADMIN"."SEQ_3"."NEXTVAL"
  EVENTS       ID_EVE       "ADMIN"."SEQ_4"."NEXTVAL"

April - Postgres psql error

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

You are connected on the db server.
When you launch psql you get the following error/warning:
psql:/var/lib/postgresql/.psqlrc:8: error: unterminated quoted

  postgres@db_server:~$ psql
  Expanded display is used automatically.
  psql:/var/lib/postgresql/.psqlrc:8: error: unterminated quoted string
  psql (12.4 (Debian 12.4-1.pgdg90+1))
  Type "help" for help.

Why? How can you solve it?

March - Find Tables info in Postgres and in Oracle

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

You have these tables in 2 different RDBMS:
- T_PG in Postgres
- T_ORA in Oracle

For both RDBMS, you have to find:
- their describe (column names and data types)
- their number of rows


Using Psql for Postgres and SQLcl for Oracle, an easy solution is:

In Postgres, use the "\d" command:

  \d t_pg

  Column  | Type    | Collation | Nullable | Default
  id      | integer |           | not null |
  name    | text    |           |          |

In Oracle, use the "desc" command:

  desc t_ora

     Name       Null?            Type
  _______ ___________ _______________
  ID      NOT NULL    NUMBER(38)
  NAME                VARCHAR2(10)
Count of rows:

In both RDBMS you can use the "count(*)" function:
  SELECT count(*)
  FROM t_ora;


February - Oracle Segment types size

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

You have to write a query on system views that shows:
- the segment types in your schema
- their total size
- the percentage of their size compared to the total size.

For instance:
  TYPE      KB      TOT   PERCENT
  _____ _______ ________ _________
  IND     4416    13184        33
  LOB     1216    13184         9
  TAB     7552    13184        57

January - Oracle primary key names

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

You have to write a PL/SQL anonymous block in order to get all the primary key names in your schema and the names of their tables.

For instance, you must see something like:
  The primary key of the TAB1 table is called TAB2_PK
  The primary key of the CITIES table is called PK_CITIES
  The primary key of the TAB_VAL table is called TAB_VAL_PK

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.