samedi 25 juin 2022

Generating random student data

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
;




Aucun commentaire:

Enregistrer un commentaire