1. DROP TABLE students CASCADE CONSTRAINTS
2. /
3.  
4.  
5. CREATE TABLE students (
6.   id INT NOT NULL PRIMARY KEY,
7.   registration_number VARCHAR2(6) NOT NULL,
8.   lname VARCHAR2(15) NOT NULL,
9.   fname VARCHAR2(30) NOT NULL,
10.   year NUMBER(1),
11.   groupno CHAR(2),
12.   scholarship NUMBER(6,2),
13.   dob DATE,
14.   email VARCHAR2(40),
15.   created_at DATE,
16.   updated_at DATE
17. )
18. /
19.  
20. SET SERVEROUTPUT ON;
21. DECLARE
22.   TYPE varr IS VARRAY(1000) OF varchar2(255);
23.   lista_lname varr := varr('Morrison','Bennett','Brady','Coleman','Ford','Rios','Poole','Walters','Guerrero','Flores','Lee','Miller','Francis','French','Martin','Sherman','Graham','Garner','Maxwell','Estrada','Morales','Owen','Lawson','Benson','Hammond','Greene','Lamb','Castro','Perkins','Hughes','Barnes','Mckenzie','Watts','Anderson','Gregory','Alvarez','Yates','Fowler','Wilkins','Warren','Burns','Boone','Goodwin','Porter','Wheeler','Brock','Howard','Barton','Zimmerman','Hodges','Massey','Norton','Gibson','Strickland','Bell','Robinson','Graves','Craig','Howell','Hunt','Malone','Richards','Murphy','Nash','West','Lloyd','Paul','Fuller','Holloway','Goodman','Ryan','Reeves','Cole','Parker','Cohen','Ingram','Scott','Byrd','Hart','Casey','Franklin','Morgan','Mclaughlin','Lyons','Montgomery','Stephens','Glover','Roberts','Erickson','Allison','Ramos','Holland','Hawkins','Williamson','Edwards','Mccoy','Swanson','Delgado','Ellis','Collins','Boyd','Myers','Nichols','Wood','Rice','Wolfe','Stokes','Ortiz','Haynes','Mccormick','Norman','Knight','Patton','Gomez','Chandler','Henry','Tucker','Kennedy','Day','Gray','Banks','Allen','Clark','Reed','Oliver','Price','Simon','Fox','Copeland','Harrington','Brooks','Ruiz','Taylor','Griffith','Jordan','Ballard','Clarke','Kelley','Waters','Russell','Luna','Becker','Nguyen','Norris','Munoz','Wilson','Todd','Olson','George','Rivera','Williams','White','Torres','Brewer','Mendoza','Alexander','Joseph','Mason','Webster','Higgins','Barnett','Harrison','Bailey','Underwood','Robertson','Watkins','Stone','Quinn','Hicks','Holt','Burgess','Hoffman','Adams','Stevens','Chavez','Wilkerson','Bryan','Sandoval','Greer','Soto','Walsh','Wagner','Vega','Schmidt','Figueroa','Thornton','Diaz','Hamilton','Peters','Sims','Duncan','Rhodes','Carter','Alvarado','Powell','Burton','Osborne','Blake','Palmer','Moore','Dawson','Henderson','Lowe','Peterson','Sanders','Shelton','Lopez','Mckinney','Ferguson','Pierce','Neal','Abbott','Keller','Silva','Stewart','Griffin','Lynch','Bush','Nelson','Townsend','Butler','Webb','Spencer','Mack','Frazier','Gutierrez','Moody','Carroll','Bowman','Little','Guzman','Martinez','Larson','Clayton','Perez','Colon','Daniel','Adkins','Turner','Smith','Tate','Mccarthy','Douglas','Riley','Mills','Briggs','Collier','Perry','Murray','Mullins','Vasquez','Wright','Pearson','Cooper','Lewis','Foster','Mann','Santiago','Santos','Cain','Rodgers','Lambert','Fitzgerald','Hudson','Fletcher','Jennings','Schultz','Bowen','Schwartz','Rose','Hopkins','Doyle','Carr','Saunders','Meyer','Cruz','Roy','Baker','Simpson','Valdez','Newton','Caldwell','Parks','Obrien','Johnson','Weaver','Steele','Thomas','Fisher','Walker','Johnston','Grant','Watson','Reid','Gill','Carson','Simmons','Barrett','Holmes','Wells','Mcdonald','Garza','Cook','Bridges','Cox','Leonard','Klein','Lawrence','Rowe','Jackson','Aguilar','Willis','Harmon','Long','Davis','Summers','Davidson','Baldwin','Harper','Patrick','Sanchez','Gonzalez','Lindsey','Miles','Wise','Roberson','Bass','Mcgee','Powers','Richardson','Nunez','Hogan','Gordon','Singleton','Harvey','Wade','Welch','Kelly','Houston','Sutton','Love','Bradley','Jimenez','Floyd','Ortega','Black','Ball','Crawford','Bowers','Hernandez','Tran','Brown','Armstrong','Gilbert','Cummings','Snyder','Hayes','Padilla','Dixon','Hampton','Mathis','Medina','Jenkins','Hill','Jacobs','King','Jefferson','Conner','Chapman','Terry','Christensen','Maldonado','Stanley','Gardner','Fields','Ward','Hunter','Ross','Cannon','Sharp','Manning','Newman','Mitchell','Morris','Morton','Hansen','Ramsey','Garcia','Moss','Vargas','Hale','Wallace','Dennis','Fernandez','Thompson','Huff','Park','Walton','Kim','Chambers');
24.   lista_fname_fete varr := varr('Bonnie','Louise','Janet','Anna','Jane','Ruth','Ashley','Tina','Joyce','Stephanie','Laura','Virginia','Alice','Margaret','Lori','Sharon','Anne','Emily','Andrea','Elizabeth','Sarah','Rebecca','Ann','Brenda','Jessica','Paula','Jennifer','Diana','Cheryl','Lois','Teresa','Susan','Evelyn','Karen','Wanda','Gloria','Carol','Nicole','Phyllis','Martha','Carolyn','Denise','Heather','Theresa','Marie','Sara','Doris','Cynthia','Joan','Sandra','Kathryn','Julie','Mildred','Jacqueline','Donna','Rose','Dorothy','Debra','Rachel','Diane','Irene','Helen','Jean','Lillian','Patricia','Norma','Kelly','Janice','Frances','Annie','Christine','Michelle','Beverly','Catherine','Melissa','Judith','Lisa','Pamela','Tammy','Kathy','Deborah','Linda','Judy','Kathleen','Angela','Christina','Katherine','Marilyn','Shirley','Maria','Ruby','Mary','Kimberly','Barbara','Nancy','Betty','Amy','Julia','Amanda');
25.   lista_fname_baieti varr := varr('Alonzo','Lorenzo','Tommy','Levi','Dustin','Angelo','Matthew','Johnny','Andres','Jeffrey','Samuel','Alberto','Leland','Wallace','Loren','Gustavo','Virgil','Dale','Jaime','Gerard','Carlos','Jason','Roy','Harvey','Willard','Rick','Stuart','Cody','Eduardo','Gerardo','Curtis','Aubrey','Sammy','Gene','Toby','Winston','Tony','Charlie','Wm','Joseph','Marty','Johnnie','Earl','Brad','Jonathan','Rex','Cornelius','Eddie','Cesar','Keith','Louis','Micheal','Nicholas','Dwight','Dave','Rodolfo','Warren','Raymond','Shannon','Emmett','George','Moses','Preston','Guillermo','Andrew','Ignacio','Leslie','Ian','Kirk','Amos','Bert','Ronnie','Timmy','Manuel','Tim','Gregory','Mario','Earnest','Luis','Lawrence','Eric','Miguel','Rudy','Albert','Wayne','Colin','Larry','Israel','Salvador','Jorge','Thomas','Alton','Pat','Malcolm','Randolph','Nicolas','Marshall','Francis','Tyrone','Lewis');
26.   lista_materii_year_1 varr := varr('Logic in Informatics','Mathematics','Introduction to Programming','Computer Architecture and Operating Systems','Operating Systems','Object-Oriented Programming','Fundamental Algebraic of Information','Probabilities and Statistics');
27.   lista_materii_year_2 varr := varr('Computer Networks','Data Basis','Formal Languages, Automata and Compilers','Graph Algorithms','Web Technologies','Advanced Programming','Software Engineering','DBMS Practice');
28.   lista_materii_year_3 varr := varr('Automated Learning','Network Security','Artificial Intelligence','Python Programming','Numeric Calculus','Computer graphics','Data Mining','Petri networks and their applications');
29.   lista_grade_diactice varr := varr('Colaborator','Assistant Lecturer','Lecturer','Associate Professor','Professor');
30.      
31.   v_lname VARCHAR2(255);
32.   v_fname VARCHAR2(255);
33.   v_fname1 VARCHAR2(255);
34.   v_fname2 VARCHAR2(255);
35.   v_matr VARCHAR2(6);
36.   v_matr_aux VARCHAR2(6);
37.   v_temp int;
38.   v_temp1 int;
39.   v_temp2 int;
40.   v_temp3 int;
41.   v_temp_date date;
42.   v_year int;
43.   v_groupno varchar2(2);
44.   v_scholarship int;
45.   v_dob date;  
46.   v_email varchar2(40);
47. BEGIN
48.  
49.    DBMS_OUTPUT.PUT_LINE('Adding 102 students...');
50.    FOR v_i IN 1..102 LOOP
51.       v_lname := lista_lname(TRUNC(DBMS_RANDOM.VALUE(0,lista_lname.count))+1);
52.       IF (DBMS_RANDOM.VALUE(0,100)<50) THEN      
53.          v_fname1 := lista_fname_fete(TRUNC(DBMS_RANDOM.VALUE(0,lista_fname_fete.count))+1);
54.          LOOP
55.             v_fname2 := lista_fname_fete(TRUNC(DBMS_RANDOM.VALUE(0,lista_fname_fete.count))+1);
56.             exit when v_fname1<>v_fname2;
57.          END LOOP;
58.        ELSE
59.          v_fname1 := lista_fname_baieti(TRUNC(DBMS_RANDOM.VALUE(0,lista_fname_baieti.count))+1);
60.          LOOP
61.             v_fname2 := lista_fname_baieti(TRUNC(DBMS_RANDOM.VALUE(0,lista_fname_baieti.count))+1);
62.             exit when v_fname1<>v_fname2;
63.          END LOOP;      
64.        END IF;
65.      
66.      IF (DBMS_RANDOM.VALUE(0,100)<60) THEN  
67.         IF LENGTH(v_fname1 || ' ' || v_fname2) <= 20 THEN
68.           v_fname := v_fname1 || ' ' || v_fname2;
69.         END IF;
70.         else
71.            v_fname:=v_fname1;
72.       END IF;      
73.        
74.       LOOP
75.          v_matr := FLOOR(DBMS_RANDOM.VALUE(100,999)) || CHR(FLOOR(DBMS_RANDOM.VALUE(65,91))) || CHR(FLOOR(DBMS_RANDOM.VALUE(65,91))) || FLOOR(DBMS_RANDOM.VALUE(0,9));
76.          select count(*) into v_temp from students where registration_number = v_matr;
77.          exit when v_temp=0;
78.       END LOOP;
79.              
80.       LOOP      
81.         v_year := TRUNC(DBMS_RANDOM.VALUE(0,3))+1;
82.         v_groupno := chr(TRUNC(DBMS_RANDOM.VALUE(0,2))+65) || chr(TRUNC(DBMS_RANDOM.VALUE(0,6))+49);
83.         select count(*) into v_temp from students where year=v_year and groupno=v_groupno;
84.         exit when v_temp < 30;
85.       END LOOP;
86.      
87.       v_scholarship := '';
88.       IF (DBMS_RANDOM.VALUE(0,100)<10) THEN
89.          v_scholarship := TRUNC(DBMS_RANDOM.VALUE(0,10))*100 + 500;
90.       END IF;
91.      
92.       v_dob := TO_DATE('01-01-1974','MM-DD-YYYY')+TRUNC(DBMS_RANDOM.VALUE(0,365));
93.      
94.       v_temp:='';
95.       v_email := lower(v_lname ||'.'|| v_fname1);
96.       LOOP        
97.          select count(*) into v_temp from students where email = v_email||v_temp;
98.          exit when v_temp=0;
99.          v_temp :=  TRUNC(DBMS_RANDOM.VALUE(0,100));
100.       END LOOP;    
101.      
102.       if (TRUNC(DBMS_RANDOM.VALUE(0,2))=0) then v_email := v_email ||'@gmail.com';
103.          else v_email := v_email ||'@info.ro';
104.       end if;
105.                      
106.       DBMS_OUTPUT.PUT_LINE (v_i||' '||v_matr||' '||v_lname||' '||v_fname ||' '|| v_year ||' '|| v_groupno||' '|| v_scholarship||' '|| to_char(v_dob, 'DD-MM-YYYY')||' '|| v_email);      
107.       insert into students values(v_i, v_matr, v_lname, v_fname, v_year, v_groupno, v_scholarship, v_dob, v_email, sysdate, sysdate);
108.    END LOOP;
109.    DBMS_OUTPUT.PUT_LINE('done !');
110. END;
111. /
112.  
113.  
114.  
115.  
116. select * from students;