Oracle Database Development interview question, when a written test did not come out, now attached to the original question and answer

1.

Id
1
2
3
5
6
7
8
10
11
12
15

Table name TT, using SQL to find the IDs in the ID column, for example, 4:

--Creating tables and DataCREATE TABLETT (IDINTEGER);INSERT  intoTTSELECT 1  fromDualUNION  AllSELECT 2  fromDualUNION  AllSELECT 3  fromDualUNION  AllSELECT 5  fromDualUNION  AllSELECT 6  fromDualUNION  AllSELECT 7  fromDualUNION  AllSELECT 8  fromDualUNION  AllSELECT Ten  fromDualUNION  AllSELECT  One  fromDualUNION  AllSELECT  A  fromDualUNION  AllSELECT  the  fromdual;COMMIT;

--use the Connect by level to create data withIT as (SELECT  LevelId fromDUAL CONNECT by  Level <=(SELECT MAX(ID) fromTT))SELECTa.id fromIT AWHERE  not EXISTS(SELECT 1  fromTT BWHEREa.ID=b.ID)

2.

Organize the irregular room information into canonical format

Non-canonical table (multiple rooms separated by commas)

Id	The
1	101,102
2	201,202,203
3	301
.......

Specification table

Id	The
1	101
1	102
2	201
2	202
2	203
3	301
......

--Single row to multiple lines--Creating tables and DataCreate TableTTT (IDinteger, thevarchar2( $));Insert  intoTTTSelect 1,'101,102'  fromDualUnion  AllSelect 2,'201,202,203'  fromDualUnion  AllSelect 3,'301'  fromdual;Commit;

SELECT DISTINCTId,regexp_substr (Hostel,'[^,]+',1, Level,'I') as STR     fromTttconnect by  Level <=LENGTH (Guest)-LENGTH (Regexp_replace,',',"'))+1;

Oracle Database Development interview question, when a written test did not come out, now attached to the original question and answer