ae9v0DNt

1DROP TABLE Product CASCADE CONSTRAINTS
2/
3DROP TABLE OrderDetails CASCADE CONSTRAINTS
4/
5DROP TABLE Customer CASCADE CONSTRAINTS
6/
7DROP TABLE OrderT CASCADE CONSTRAINTS
8/
9DROP TABLE ShopCart CASCADE CONSTRAINTS
10/
11DROP TABLE ShopCartDetails CASCADE CONSTRAINTS
12/
13DROP TABLE Votes CASCADE CONSTRAINTS
14/
15DROP TABLE Comments CASCADE CONSTRAINTS
16/
17DROP TABLE Locations CASCADE CONSTRAINTS
18/
19
20
21CREATE TABLE Product(
22ID Int NOT NULL PRIMARY KEY,
23Name varchar(255) NOT NULL,
24Image varchar(255) NOT NULL,
25Description varchar(255) NOT NULL,
26Price Number(6,2) NOT NULL,
27Brand varchar(255) NOT NULL,
28Quantity varchar(255) NOT NULL,
29Categorie varchar(255) NOT NULL,
30Nr_voturi int,
31Suma_voturi int
32)
33/
34
35CREATE TABLE Customer(
36ID Int NOT NULL PRIMARY KEY,
37Fname varchar(255) NOT NULL,
38Lname varchar(255) NOT NULL,
39Username varchar(255) NOT NULL,
40Password varchar(255) NOT NULL,
41Email varchar(255) NOT NULL
42)
43/
44
45CREATE TABLE OrderDetails(
46ID Int NOT NULL PRIMARY KEY,
47OrderDate date NOT NULL,
48--Image varchar(255) NOT NULL,
49ProductQuantity Int NOT NULL,
50Price Number(6,2) NOT NULL,
51ProductID Int NOT NULL,
52OrderID Int NOT NULL
53)
54/
55
56CREATE TABLE OrderT(
57ID Int NOT NULL PRIMARY KEY,
58OrderNumber Int NOT NULL,
59OrderTotalPrice Int NOT NULL,
60CustomerID Int NOT NULL
61)
62/
63
64CREATE TABLE ShopCart(
65ID Int NOT NULL PRIMARY KEY,
66ProducID Int NOT NULL,
67CustomerID int NOT NULL,
68ProductQuantity Int NOT NULL,
69ProductPrice Number(6,2) NOT NULL
70)
71/
72--select * from shopcart
73CREATE TABLE ShopCartDetails(
74ID Int NOT NULL PRIMARY KEY,
75CustomerID Int NOT NULL,
76ProductID Int NOT NULL
77)
78/
79
80CREATE TABLE Votes(
81ID Int NOT NULL PRIMARY KEY,
82ProductID Int NOT NULL,
83CustomerID Int NOT NULL,
84VoteValue int
85)
86/
87CREATE TABLE Comments(
88ID Int NOT NULL PRIMARY KEY,
89ProductID Int NOT NULL,
90CustomerID Int NOT NULL,
91Comentariu varchar(255)
92)
93/
94CREATE TABLE Locations(
95ID Int NOT NULL PRIMARY KEY,
96Lat number(6,3) NOT NULL,
97Longitude number(6,3) NOT NULL,
98Nume varchar(255)
99)
100/
101
102DECLARE
103TYPE varr IS VARRAY(1000) OF varchar(255);
104  lista_nume varr := varr('Ababei','Acasandrei','Adascalitei','Afanasie','Agafitei','Agape','Aioanei','Alexandrescu','Alexandru','Alexe','Alexii','Amarghioalei','Ambroci','Andonesei','Andrei','Andrian','Andrici','Andronic','Andros','Anghelina','Anita','Antochi','Antonie','Apetrei','Apostol','Arhip','Arhire','Arteni','Arvinte','Asaftei','Asofiei','Aungurenci','Avadanei','Avram','Babei','Baciu','Baetu','Balan','Balica','Banu','Barbieru','Barzu','Bazgan','Bejan','Bejenaru','Belcescu','Belciuganu','Benchea','Bilan','Birsanu','Bivol','Bizu','Boca','Bodnar','Boistean','Borcan','Bordeianu','Botezatu','Bradea','Braescu','Budaca','Bulai','Bulbuc-aioanei','Burlacu','Burloiu','Bursuc','Butacu','Bute','Buza','Calancea','Calinescu','Capusneanu','Caraiman','Carbune','Carp','Catana','Catiru','Catonoiu','Cazacu','Cazamir','Cebere','Cehan','Cernescu','Chelaru','Chelmu','Chelmus','Chibici','Chicos','Chilaboc','Chile','Chiriac','Chirila','Chistol','Chitic','Chmilevski','Cimpoesu','Ciobanu','Ciobotaru','Ciocoiu','Ciofu','Ciornei','Citea','Ciucanu','Clatinici','Clim','Cobuz','Coca','Cojocariu','Cojocaru','Condurache','Corciu','Corduneanu','Corfu','Corneanu','Corodescu','Coseru','Cosnita','Costan','Covatariu','Cozma','Cozmiuc','Craciunas','Crainiceanu','Creanga','Cretu','Cristea','Crucerescu','Cumpata','Curca','Cusmuliuc','Damian','Damoc','Daneliuc','Daniel','Danila','Darie','Dascalescu','Dascalu','Diaconu','Dima','Dimache','Dinu','Dobos','Dochitei','Dochitoiu','Dodan','Dogaru','Domnaru','Dorneanu','Dragan','Dragoman','Dragomir','Dragomirescu','Duceac','Dudau','Durnea','Edu','Eduard','Eusebiu','Fedeles','Ferestraoaru','Filibiu','Filimon','Filip','Florescu','Folvaiter','Frumosu','Frunza','Galatanu','Gavrilita','Gavriliuc','Gavrilovici','Gherase','Gherca','Ghergu','Gherman','Ghibirdic','Giosanu','Gitlan','Giurgila','Glodeanu','Goldan','Gorgan','Grama','Grigore','Grigoriu','Grosu','Grozavu','Gurau','Haba','Harabula','Hardon','Harpa','Herdes','Herscovici','Hociung','Hodoreanu','Hostiuc','Huma','Hutanu','Huzum','Iacob','Iacobuta','Iancu','Ichim','Iftimesei','Ilie','Insuratelu','Ionesei','Ionesi','Ionita','Iordache','Iordache-tiroiu','Iordan','Iosub','Iovu','Irimia','Ivascu','Jecu','Jitariuc','Jitca','Joldescu','Juravle','Larion','Lates','Latu','Lazar','Leleu','Leon','Leonte','Leuciuc','Leustean','Luca','Lucaci','Lucasi','Luncasu','Lungeanu','Lungu','Lupascu','Lupu','Macariu','Macoveschi','Maftei','Maganu','Mangalagiu','Manolache','Manole','Marcu','Marinov','Martinas','Marton','Mataca','Matcovici','Matei','Maties','Matrana','Maxim','Mazareanu','Mazilu','Mazur','Melniciuc-puica','Micu','Mihaela','Mihai','Mihaila','Mihailescu','Mihalachi','Mihalcea','Mihociu','Milut','Minea','Minghel','Minuti','Miron','Mitan','Moisa','Moniry-abyaneh','Morarescu','Morosanu','Moscu','Motrescu','Motroi','Munteanu','Murarasu','Musca','Mutescu','Nastaca','Nechita','Neghina','Negrus','Negruser','Negrutu','Nemtoc','Netedu','Nica','Nicu','Oana','Olanuta','Olarasu','Olariu','Olaru','Onu','Opariuc','Oprea','Ostafe','Otrocol','Palihovici','Pantiru','Pantiruc','Paparuz','Pascaru','Patachi','Patras','Patriche','Perciun','Perju','Petcu','Pila','Pintilie','Piriu','Platon','Plugariu','Podaru','Poenariu','Pojar','Popa','Popescu','Popovici','Poputoaia','Postolache','Predoaia','Prisecaru','Procop','Prodan','Puiu','Purice','Rachieru','Razvan','Reut','Riscanu','Riza','Robu','Roman','Romanescu','Romaniuc','Rosca','Rusu','Samson','Sandu','Sandulache','Sava','Savescu','Schifirnet','Scortanu','Scurtu','Sfarghiu','Silitra','Simiganoschi','Simion','Simionescu','Simionesei','Simon','Sitaru','Sleghel','Sofian','Soficu','Sparhat','Spiridon','Stan','Stavarache','Stefan','Stefanita','Stingaciu','Stiufliuc','Stoian','Stoica','Stoleru','Stolniceanu','Stolnicu','Strainu','Strimtu','Suhani','Tabusca','Talif','Tanasa','Teclici','Teodorescu','Tesu','Tifrea','Timofte','Tincu','Tirpescu','Toader','Tofan','Toma','Toncu','Trifan','Tudosa','Tudose','Tuduri','Tuiu','Turcu','Ulinici','Unghianu','Ungureanu','Ursache','Ursachi','Urse','Ursu','Varlan','Varteniuc','Varvaroi','Vasilache','Vasiliu','Ventaniuc','Vicol','Vidru','Vinatoru','Vlad','Voaides','Vrabie','Vulpescu','Zamosteanu','Zazuleac');
105  lista_prenume_fete varr := varr('Adina','Alexandra','Alina','Ana','Anca','Anda','Andra','Andreea','Andreia','Antonia','Bianca','Camelia','Claudia','Codrina','Cristina','Daniela','Daria','Delia','Denisa','Diana','Ecaterina','Elena','Eleonora','Elisa','Ema','Emanuela','Emma','Gabriela','Georgiana','Ileana','Ilona','Ioana','Iolanda','Irina','Iulia','Iuliana','Larisa','Laura','Loredana','Madalina','Malina','Manuela','Maria','Mihaela','Mirela','Monica','Oana','Paula','Petruta','Raluca','Sabina','Sanziana','Simina','Simona','Stefana','Stefania','Tamara','Teodora','Theodora','Vasilica','Xena');
106  lista_prenume_baieti varr := varr('Adrian','Alex','Alexandru','Alin','Andreas','Andrei','Aurelian','Beniamin','Bogdan','Camil','Catalin','Cezar','Ciprian','Claudiu','Codrin','Constantin','Corneliu','Cosmin','Costel','Cristian','Damian','Dan','Daniel','Danut','Darius','Denise','Dimitrie','Dorian','Dorin','Dragos','Dumitru','Eduard','Elvis','Emil','Ervin','Eugen','Eusebiu','Fabian','Filip','Florian','Florin','Gabriel','George','Gheorghe','Giani','Giulio','Iaroslav','Ilie','Ioan','Ion','Ionel','Ionut','Iosif','Irinel','Iulian','Iustin','Laurentiu','Liviu','Lucian','Marian','Marius','Matei','Mihai','Mihail','Nicolae','Nicu','Nicusor','Octavian','Ovidiu','Paul','Petru','Petrut','Radu','Rares','Razvan','Richard','Robert','Roland','Rolland','Romanescu','Sabin','Samuel','Sebastian','Sergiu','Silviu','Stefan','Teodor','Teofil','Theodor','Tudor','Vadim','Valentin','Valeriu','Vasile','Victor','Vlad','Vladimir','Vladut');
107  list_branduri varr := varr('CYMA','Lancer Tactical','CYBERGUN','UMAREX','UK ARMS','AGM','APS','Redwolf Airsoft','Systema','Tokyo Marui','Classic Army','ICS','GG','KWA','AK AIRSOFT','GP');
108  list_letters varr := varr('A','B','C','D','E','F','G','H','I','J','K','M','N','O','P','R','T','S','U','V','X','Y','Z');
109  list_product_name varr := varr('Compact','CZ75-Auto','Tec-9','Five-SeveN','Glock-18','R8 Revolver','Dual Berettas');
110  lista_categorii varr := varr('Pistoale','Shotgun','Mass destruction','Pusti cu luneta');
111  list_venue varr := varr('Airsoft Special Ops','BattlegroundZ','Building 52','The Citadel','Federal Fortress	','PnL Paintball','RE-LOAD Airsoft','The Ultimate Battleground','Xtreme Action Sports','Stronghold Ops','Feel Good Farms','RPC Airsoft','Extreme Complex','Warzone Paintball Airsoft Park','Insanity Productions','North East Adventure Paintball and Airsoft Field','Opposing Force Airsoft','OXCC Paintball  Airsoft','Paintball Sportsland','Tactical Airsoft Arena','Replay Airsoft Arena');
112  v_nume VARCHAR(255);
113  v_prenume VARCHAR(255);
114  v_prenume1 VARCHAR(255);
115  v_prenume2 VARCHAR(255);
116  v_user varchar(255);
117  v_password varchar(255);
118  v_email varchar(255);
119  v_productName varchar(255) ;
120  v_random_letter varchar(255);
121  v_bookname varchar(255);
122  v_price number(6,2);
123  v_image varchar(255);
124  v_description varchar(255) ;
125  v_product_quantity int;
126  v_product_brand varchar(255);
127  v_nr_voturi int;
128  v_suma_voturi int;
129  v_category varchar(255);
130  v_name_loc varchar(255);
131  v_lat number(6,3);
132  v_long number(6,3);
133BEGIN
134DBMS_OUTPUT.PUT_LINE('Inserare 1000000 de linii in tabela Customer...');
135 	FOR v_i in 1..100000 LOOP
136    v_nume := lista_nume(TRUNC(DBMS_RANDOM.VALUE(0, LISTA_NUME.COUNT))+1);
137     IF (DBMS_RANDOM.VALUE(0,100)<50) THEN
138         v_prenume1 := lista_prenume_fete(TRUNC(DBMS_RANDOM.VALUE(0,lista_prenume_fete.count))+1);
139         LOOP
140            v_prenume2 := lista_prenume_fete(TRUNC(DBMS_RANDOM.VALUE(0,lista_prenume_fete.count))+1);
141            exit when v_prenume1<>v_prenume2;
142         END LOOP;
143       ELSE
144         v_prenume1 := lista_prenume_baieti(TRUNC(DBMS_RANDOM.VALUE(0,lista_prenume_baieti.count))+1);
145         LOOP
146            v_prenume2 := lista_prenume_baieti(TRUNC(DBMS_RANDOM.VALUE(0,lista_prenume_baieti.count))+1);
147            exit when v_prenume1<>v_prenume2;
148         END LOOP;
149       END IF;
150
151     IF (DBMS_RANDOM.VALUE(0,100)<60) THEN
152        IF LENGTH(v_prenume1 || ' ' || v_prenume2) <= 20 THEN
153          v_prenume := v_prenume1 || ' ' || v_prenume2;
154        END IF;
155        else
156           v_prenume := v_prenume1;
157      END IF;
158     v_user := 'user' || v_i;
159     v_password := 'password' || v_i;
160     v_email := v_user || '@gmail.com';
161   
162   insert into Customer values(v_i,v_nume, v_prenume, v_user, v_password,v_email);
163end loop;
164    	DBMS_OUTPUT.PUT_LINE('Customer is done...');
165commit;
166
167DBMS_OUTPUT.PUT_LINE('Inserare linii in tabela Products...');
168
169    for v_i in 1..100000 loop
170      v_random_letter := list_letters(TRUNC(DBMS_RANDOM.VALUE(0,list_letters.count))+1);
171      v_category := lista_categorii(TRUNC(DBMS_RANDOM.VALUE(0,lista_categorii.count))+1);
172      v_productName := list_product_name(TRUNC(DBMS_RANDOM.VALUE(0,list_product_name.count))+1) || ' - ' || v_random_letter;
173      --v_productName := 'x';
174      v_image := 'a';
175      v_description := 'b';
176       v_price := round(DBMS_RANDOM.VALUE(0,1000),2);
177       v_nr_voturi := round(DBMS_RANDOM.VALUE(0,1000));
178       v_suma_voturi := round(DBMS_RANDOM.VALUE(1,5)) * v_nr_voturi;
179      v_product_brand := list_branduri(TRUNC(DBMS_RANDOM.VALUE(0,list_branduri.count))+1);
180      v_product_quantity := DBMS_RANDOM.VALUE(0,100);
181      v_image := return_imagine(v_category);
182    insert into Product values(v_i ,v_productName, v_image, v_description, v_price, v_product_brand ,v_product_quantity,v_category,v_nr_voturi,v_suma_voturi);
183  
184    end loop;
185    DBMS_OUTPUT.PUT_LINE('Done with Products...');
186    
187    DBMS_OUTPUT.PUT_LINE('Inserare linii in tabela Locations...');
188
189    for v_i in 1..10000 loop
190      v_random_letter := list_letters(TRUNC(DBMS_RANDOM.VALUE(0,list_letters.count))+1);
191      v_name_loc := list_venue(TRUNC(DBMS_RANDOM.VALUE(0,list_venue.count))+1) || ' - ' || v_random_letter;
192      v_lat := 20 + round(DBMS_RANDOM.VALUE(0,10),3);
193      v_long := 40 + round(DBMS_RANDOM.VALUE(0,10),3);
194    insert into Locations values(v_i ,v_lat, v_long, v_name_loc);
195  
196    end loop;
197  DBMS_OUTPUT.PUT_LINE('Done with Locations...');
198  
199  insert into shopcart values (0,0,0,0,0);
200  insert into votes values(0,0,0,0);
201  insert into comments values(0,0,0,'');
202  insert into ordert values(0,0,0,0);
203  insert into orderdetails values(0,'26-MAY-19',0,0,0,0);
204  commit;
205  end;
206  /
207  
208select * from comments
209select * from Locations
210select * from shopcart
211select * from ordert
212select * from orderdetails
213select * from product order by id desc
214SELECT FNAME FROM (select * from CUSTOMER ORDER BY ID DESC) WHERE ROWNUM = 1;