Generating random student data-CodePudding

I'm trying to create a process that populates a student table. I want to be able to create a different combination of a student's first/last name and dob every time I run the query.

The code below appears to work fine as it generates 5 names. My first question is can this be modified to generate N NUMBER of rows sat for example 20. I tried using CONNECT by level <=20 but that gives me a syntax error.

Secondly, know the random_date function works


Select random_date(DATE '2001-01-01', DATE '2001-12-31') from dual 

17-NOV-2001 08:31:16

But I can't seem to incorporate into my SQL.

Any help would be greatly appreciated. Thanks in advance for your expertise and time


ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

CREATE OR REPLACE FUNCTION random_date(
      p_from IN DATE,
      p_to   IN DATE
    ) RETURN DATE
   IS
   BEGIN
      RETURN p_from   DBMS_RANDOM.VALUE() * (p_to - p_from   1 );
END random_date;
/

CREATE TABLE students (
   student_id  number(*,0),
  first_name VARCHAR(25) NOT NULL,
  last_name VARCHAR(25) NOT NULL,
   dob DATE,
  constraint teacher_pk primary key (student_id));

WITH  raw_names (first_name, last_name)  AS
(
  SELECT 'Faith', 'Andrews'  FROM dual UNION ALL
  SELECT 'Tom',   'Thornton' FROM dual UNION ALL
  SELECT 'Anna',  'Smith'    FROM dual UNION ALL
  SELECT 'Lisa',  'Jones'    FROM dual UNION ALL
  SELECT 'Andy',  'Beirs'    FROM dual
)
,  numbered_names  AS
(
  SELECT  first_name, last_name
  ,      ROW_NUMBER () OVER (ORDER BY dbms_random.value (0, 1)) AS first_num
  ,      ROW_NUMBER () OVER (ORDER BY dbms_random.value (0, 2)) AS last_num
  FROM    raw_names
)
SELECT   fn.first_num        AS student_id
,     fn.first_name
,     ln.last_name
FROM     numbered_names fn
JOIN     numbered_names ln ON ln.last_num = fn.first_num  
ORDER BY student_id
;

CodePudding user response：

I can't debug your code as you didn't post it (the one that raised a syntax error and doesn't accept the function).

Anyway, here's what you might do:

line #14 - function call
lines #15 - 19 show how to create desired number of rows (must be multiple of number of rows in raw_names)

SQL> WITH  raw_names (first_name, last_name)  AS
  2  (
  3    SELECT 'Faith', 'Andrews'  FROM dual UNION ALL
  4    SELECT 'Tom',   'Thornton' FROM dual UNION ALL
  5    SELECT 'Anna',  'Smith'    FROM dual UNION ALL
  6    SELECT 'Lisa',  'Jones'    FROM dual UNION ALL
  7    SELECT 'Andy',  'Beirs'    FROM dual
  8  )
  9  ,  numbered_names  AS
 10  (
 11    SELECT  first_name, last_name
 12    ,      ROW_NUMBER () OVER (ORDER BY dbms_random.value (0, 1)) AS first_num
 13    ,      ROW_NUMBER () OVER (ORDER BY dbms_random.value (0, 2)) AS last_num
 14    ,      random_date (date '2001-01-01', date '2001-12-31') datum
 15    FROM    raw_names cross join
 16      table(cast(multiset(select level from dual
 17                          connect by level <= (select &n / count(*) from raw_names))
 18                          as sys.odcinumberlist))
 19  )
 20  SELECT   fn.first_num        AS student_id
 21  ,     fn.first_name
 22  ,     ln.last_name
 23  ,     ln.datum
 24  FROM     numbered_names fn
 25  JOIN     numbered_names ln ON ln.last_num = fn.first_num
 26  ORDER BY student_id
 27  ;
Enter value for n: 20

Result:

STUDENT_ID FIRST LAST_NAM DATUM
---------- ----- -------- --------------------
         1 Tom   Andrews  12-NOV-2001 14:42:05
         2 Faith Jones    06-MAR-2001 05:14:07
         3 Tom   Thornton 04-SEP-2001 16:28:25
         4 Faith Beirs    29-MAR-2001 06:11:35
         5 Andy  Thornton 18-MAY-2001 17:32:07
         6 Andy  Jones    19-JAN-2001 19:39:15
         7 Anna  Jones    17-JAN-2001 02:51:39
         8 Andy  Andrews  31-DEC-2001 15:36:44
         9 Faith Beirs    22-JUN-2001 05:34:22
        10 Lisa  Thornton 29-JUL-2001 07:00:15
        11 Lisa  Smith    31-JAN-2001 04:17:04
        12 Anna  Andrews  07-FEB-2001 09:02:21
        13 Lisa  Thornton 31-DEC-2001 20:18:06
        14 Lisa  Smith    24-SEP-2001 04:10:21
        15 Tom   Andrews  30-JUN-2001 12:01:04
        16 Faith Jones    16-AUG-2001 19:56:54
        17 Anna  Beirs    23-NOV-2001 11:01:03
        18 Anna  Beirs    23-NOV-2001 08:33:39
        19 Andy  Smith    24-SEP-2001 21:27:00
        20 Tom   Smith    24-SEP-2001 22:07:39

20 rows selected.

SQL>