| ManualiOracle.it | ||
|
||
| ||||||||||||||||||||||||||||||||||||||||||||||
|
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 SEQUENCE seq; CREATE TABLE calc_hist (id INT DEFAULT seq.NEXTVAL ,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: ID NAME SEASON COMPETITION MATCH GOL __ _______ __________ ______________ ________ ______ 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:
MATCH WHO_WHEN
----- ------------------------
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. Example: SELECT fz_age_100_gol ( date_birth, date_100_gol ) || ' ' || name age_100_gol FROM players ORDER BY 1; AGE_100_GOL ---------------- 26 213 Del Piero 27 9 Baggio 27 334 Trezeguet 31 92 PlatiniSimple 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.
Solution
In Oracle and in postgres, to do that insert you can use this simple SQL:
INSERT INTO t_char VALUES (
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: CREATE TABLE t1 ( cod VARCHAR(15) ,nome VARCHAR(30) ,cognome VARCHAR(30) ,nick_1 VARCHAR(30) ,CONSTRAINT t1_pk PRIMARY KEY (cod) ); CREATE TABLE t2 ( id INT ,cod_pers VARCHAR(20) ,cod_ruolo VARCHAR(20) ,CONSTRAINT t2_pk PRIMARY KEY (id) ); ALTER TABLE t2 ADD CONSTRAINT t2_fk FOREIGN KEY (cod_pers) REFERENCES t1;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 foundWhile 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 Why? 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? Solution
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 ( id INT DEFAULT seq.NEXTVAL ,nome VARCHAR2(40) ,cod_stato VARCHAR2(9) ,anno VARCHAR2(9) ,js_soldi VARCHAR2(255) ,CONSTRAINT films_pk PRIMARY KEY (id) ,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:
NOME JS_SOLDI
---------- ------------------------------------------------------
Tolo Tolo {"gros_ita_eur" : 46174075}
Ben-Hur
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:
MILLION_DOLLARS NOME
--------------- -----------------
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 | | |
Indexes:
"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?
(*) Level: 1: easy solution 2: medium solution 3: hard (or long) solution Topic: DBA: 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
| |||||||||||||||||||||||||||||||||||||||||||||
