Sample tables and data for creating JSONS with SQL and PLSQL

Create the accounts table and insert data into it.

CREATE TABLE accounts (
  accno     NUMBER,
  accname   VARCHAR2(20),
  open_dt   DATE,
  acctype   VARCHAR2(1)
);

INSERT INTO accounts values (101,'John Smith', SYSDATE - 500, 'S');
INSERT INTO accounts values (102,'Jane Smith', SYSDATE - 400, 'S');
INSERT INTO accounts values (103,'John Doe', SYSDATE - 300, 'C');
INSERT INTO accounts values (104,'John Doe', SYSDATE - 450, 'S');
INSERT INTO accounts values (105,'Jane Doe', SYSDATE - 200, 'C');

CREATE TABLE transactions
(
  trans_id    NUMBER,
  accno       NUMBER,
  trans_type  VARCHAR2(1),
  trans_dt    DATE,
  trans_amt   NUMBER,
  trans_mode  VARCHAR2(20)
);

INSERT INTO transactions VALUES (1,101, 'D', to_date('01-JAN-2025 12:32:37','DD-MON-YYYY HH24:MI:SS'), 1000, 'Check');
INSERT INTO transactions VALUES (2,101, 'D', to_date('01-FEB-2025 12:33:13','DD-MON-YYYY HH24:MI:SS'), 2000, 'ATM');  
INSERT INTO transactions VALUES (3,101, 'W', to_date('01-MAR-2025 12:33:35','DD-MON-YYYY HH24:MI:SS'), 1500, 'Transfer'); 
INSERT INTO transactions VALUES (4,102, 'D', to_date('01-MAR-2025 12:43:35','DD-MON-YYYY HH24:MI:SS'), 1200, 'Check'); 
INSERT INTO transactions VALUES (5,102, 'W', to_date('01-MAR-2025 12:53:35','DD-MON-YYYY HH24:MI:SS'), 1100, 'Check');
INSERT INTO transactions VALUES (6,103, 'D', to_date('01-MAR-2025 13:33:35','DD-MON-YYYY HH24:MI:SS'), 2000, 'ATM');  
INSERT INTO transactions VALUES (7,103, 'D', to_date('01-MAR-2025 12:43:35','DD-MON-YYYY HH24:MI:SS'), 2500, 'Check'); 
INSERT INTO transactions VALUES (8,103, 'W', to_date('01-MAR-2025 12:53:35','DD-MON-YYYY HH24:MI:SS'), 1000, 'Check');

CREATE TABLE HR.TDS_INFO 
(	
ACCNO NUMBER, 
TDS_DATE DATE, 
TDS_AMOUNT NUMBER
) TABLESPACE HR ;
Insert into TDS_INFO (ACCNO,TDS_DATE,TDS_AMOUNT) values (101,to_date('01-JAN-2025 12:32:37','DD-MON-YYYY HH24:MI:SS'),100);
Insert into TDS_INFO (ACCNO,TDS_DATE,TDS_AMOUNT) values (101,to_date('01-FEB-2025 12:33:13','DD-MON-YYYY HH24:MI:SS'),105);
Insert into TDS_INFO (ACCNO,TDS_DATE,TDS_AMOUNT) values (101,to_date('01-MAR-2025 12:33:35','DD-MON-YYYY HH24:MI:SS'),110);

Create Test Data for JSON_TRANSFORM

CREATE TABLE HR.TENNIS_STATS 
   (	ID NUMBER,
	FIRST_"name" VARCHAR2(100 BYTE), 
	LAST_"name" VARCHAR2(100 BYTE),      
	WORLD_RANKING NUMBER, 
	PLAYER_DATA CLOB
   ) TABLESPACE HR ;

   
--DELETE FROM TENNIS_STATS;   
INSERT INTO TENNIS_STATS VALUES
(10001, 'Novak', 'Djokovic', 1, '{
  "name": "Novak Djokovic",
  "age": "38",
  "hobbies": ["tennis", "hiking", "swimming"],
  "address": {
    "street": "123 Main St",
    "city": "Anytown"
  },
  "isStudent": false,
  "height": "6.1"
}'); 

INSERT INTO TENNIS_STATS VALUES
(10002, 'Rafael', 'Nadal', 2, '{
  "name": "Rafael Nadal",
  "age": "39",
  "hobbies": ["tennis", "football", "swimming"],
  "address": {
    "street": "445 Side St",
    "city": "Chinatown"
  },
  "isStudent": false,
  "height": "5.11"
}');

INSERT INTO TENNIS_STATS VALUES
(10003, 'Roger', 'Federer', 3, '{
  "name": "Roger Federer",
  "age": "41",
  "hobbies": ["tennis", "football", sking],
  "address": {
    "street": "326 Back St",
    "city": "Swisstown"
  },
  "isStudent": false,
  "height": "6.2"
}');

INSERT INTO TENNIS_STATS VALUES
(10004, 'Carlos', 'Alcaraz', 4, '{
  "name": "Carlos Alcaraz",
  "age": "22",
  "hobbies": ["tennis", "football", trekking],
  "address": {
    "street": "553 Upper St",
    "city": "Uptown"
  },
  "isStudent": false,
  "height": "6.01"
}');

INSERT INTO TENNIS_STATS VALUES
(10005, 'Boris', 'Becker', 5, '{
  "name": "Boris Becker",
  "age": "55",
  "hobbies": ["tennis", "football", commentry],
  "address": {
    "street": 725 Down St,
    "city": "Downtown"
  },
  "isStudent": false,
  "height": "6.05"
}');