Monday, 18 October 2021

Randam records generation using stored procedure part 02 sql videos in telugu 101

 https://youtu.be/-Cd3zMtqwVo

-------------------------------------------------------------


-- id
-- random date
-- enabled (random boolean)

create table users (id int auto_increment primary key, email varchar(40) not null, registered date not null, 
active boolean default false);

use classicmodels;

select round(rand());

select date(now()) - interval floor(10000*rand()) day;

drop table if exists users;

drop procedure testdata;

delimiter $$
create procedure testdata()
begin
declare NUMROWS int default 20000;
declare count int default 0;
declare registered_value date default null;
declare email_value varchar(40) default null;
declare active_value boolean default false;
drop table if exists users;
create table users (id int auto_increment primary key, email varchar(40) not null, registered date not null, active boolean default false);
while count < NUMROWS do
set registered_value := date(now()) - interval floor(10000*rand()) day;
set active_value := round(rand());
set email_value := concat("user", count, "@vlrtraining.com");
insert into users (email, registered, active) values (email_value, registered_value, active_value);
set count := count + 1;
end while;
end$$
delimiter ;



drop procedure testdata;
delimiter $$
create procedure testdata()
begin

declare NUMROWS int default 50;
declare count int default 0;
declare email_value varchar(40) default null;
declare registered_value date default null;
declare active_value boolean default false;
drop table if exists users;
create table users (id int auto_increment primary key, email varchar(40) not null, registered date not null, active boolean default false);
while count < NUMROWS do
set registered_value := date(now()) - interval floor(10000*rand()) day;
set email_value := concat("vlruser", count, "@vlrtraining.com");
set active_value := round(rand());
insert into users (email, registered, active) values (email_value, registered_value, active_value);
set count := count + 1;
end while;
end$$
delimiter ;



drop procedure testdata;
delimiter $$
create procedure testdata()
begin

declare NUMROWS int default 50;
declare count int default 0;
declare email_value varchar(40) default null;
declare registered_value date default null;
declare active_value boolean default false;
drop table if exists users;
create table users (id int auto_increment primary key, email varchar(40) not null, registered date not null, active boolean default false);
while count < NUMROWS do
set registered_value := date(now()) - interval floor(10000*rand()) day;
set email_value := concat("vlruser", count, "@vlrtraining.com");
set active_value := round(rand());
insert into users (email, registered, active) values (email_value, registered_value, active_value);
set count := count + 1;
end while;
end$$
delimiter ;

-------------
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use classicmodels;
Database changed
mysql> select Now();
+---------------------+
| Now()               |
+---------------------+
| 2021-09-28 19:23:31 |
+---------------------+
1 row in set (0.00 sec)

mysql> select date(Now());
+-------------+
| date(Now()) |
+-------------+
| 2021-09-28  |
+-------------+
1 row in set (0.00 sec)

mysql> select date(Now()) - interval 365 ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> select date(Now()) - interval 365 day;
+--------------------------------+
| date(Now()) - interval 365 day |
+--------------------------------+
| 2020-09-28                     |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select date(Now()) - interval rand() day;
+-----------------------------------+
| date(Now()) - interval rand() day |
+-----------------------------------+
| 2021-09-27                        |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select date(Now()) - interval rand() day;
+-----------------------------------+
| date(Now()) - interval rand() day |
+-----------------------------------+
| 2021-09-28                        |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select date(Now()) - interval (1000 * rand()) day;
+--------------------------------------------+
| date(Now()) - interval (1000 * rand()) day |
+--------------------------------------------+
| 2021-09-23                                 |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select date(Now()) - interval (1000 * rand()) day;
+--------------------------------------------+
| date(Now()) - interval (1000 * rand()) day |
+--------------------------------------------+
| 2020-01-12                                 |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select date(Now()) - interval (1000 * rand()) day;
+--------------------------------------------+
| date(Now()) - interval (1000 * rand()) day |
+--------------------------------------------+
| 2021-06-08                                 |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select date(Now()) - interval (1000 * rand()) day;
+--------------------------------------------+
| date(Now()) - interval (1000 * rand()) day |
+--------------------------------------------+
| 2019-11-12                                 |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select date(Now()) - interval (1000 * rand()) day;
+--------------------------------------------+
| date(Now()) - interval (1000 * rand()) day |
+--------------------------------------------+
| 2021-06-27                                 |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select date(Now()) - interval (1000 * rand()) day;
+--------------------------------------------+
| date(Now()) - interval (1000 * rand()) day |
+--------------------------------------------+
| 2020-08-19                                 |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select date(Now()) - interval (1000 * rand()) day;
+--------------------------------------------+
| date(Now()) - interval (1000 * rand()) day |
+--------------------------------------------+
| 2019-09-11                                 |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select date(Now()) - interval (1000 * rand()) day;\
+--------------------------------------------+
| date(Now()) - interval (1000 * rand()) day |
+--------------------------------------------+
| 2020-04-21                                 |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select date(Now()) - interval round((1000 * rand())) day;
+---------------------------------------------------+
| date(Now()) - interval round((1000 * rand())) day |
+---------------------------------------------------+
| 2020-09-14                                        |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date(Now()) - interval round((1000 * rand())) day;
+---------------------------------------------------+
| date(Now()) - interval round((1000 * rand())) day |
+---------------------------------------------------+
| 2020-11-12                                        |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date(Now()) - interval round((1000 * rand())) day;
+---------------------------------------------------+
| date(Now()) - interval round((1000 * rand())) day |
+---------------------------------------------------+
| 2020-06-23                                        |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date(Now()) - interval round((10000 * rand())) day;
+----------------------------------------------------+
| date(Now()) - interval round((10000 * rand())) day |
+----------------------------------------------------+
| 2012-02-18                                         |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date(Now()) - interval round((10000 * rand())) day;
+----------------------------------------------------+
| date(Now()) - interval round((10000 * rand())) day |
+----------------------------------------------------+
| 2011-08-20                                         |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date(Now()) - interval round((10000 * rand())) day;
+----------------------------------------------------+
| date(Now()) - interval round((10000 * rand())) day |
+----------------------------------------------------+
| 2000-01-11                                         |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date(Now()) - interval round((10000 * rand())) day;
+----------------------------------------------------+
| date(Now()) - interval round((10000 * rand())) day |
+----------------------------------------------------+
| 1998-04-02                                         |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> delimiter $$
mysql> create procedure testdata()
    -> begin
    -> drop table if exists users;
    -> create table users (id int auto_increment primary key, email varchar(40) not null, registered date not null, active boolean default false);
    ->
    -> end$$
ERROR 1304 (42000): PROCEDURE testdata already exists
mysql> delimiter ;
mysql> drop procedure testdata;
Query OK, 0 rows affected (0.27 sec)

mysql> delimiter $$
mysql> create procedure testdata()
    -> begin
    -> drop table if exists users;
    -> create table users (id int auto_increment primary key, email varchar(40) not null, registered date not null, active boolean default false);
    ->
    -> end$$
Query OK, 0 rows affected (0.19 sec)

mysql> delimiter ;
mysql> show tables;
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| accounts                |
| customers               |
| employees               |
| offices                 |
| orderdetails            |
| orders                  |
| payments                |
| productlines            |
| products                |
| users                   |
+-------------------------+
10 rows in set (0.44 sec)

mysql>
mysql> drop table users;
Query OK, 0 rows affected (1.39 sec)

mysql> call testdata();
Query OK, 0 rows affected (1.29 sec)

mysql> show tables;
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| accounts                |
| customers               |
| employees               |
| offices                 |
| orderdetails            |
| orders                  |
| payments                |
| productlines            |
| products                |
| users                   |
+-------------------------+
10 rows in set (0.00 sec)

mysql> desc users;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int         | NO   | PRI | NULL    | auto_increment |
| email      | varchar(40) | NO   |     | NULL    |                |
| registered | date        | NO   |     | NULL    |                |
| active     | tinyint(1)  | YES  |     | 0       |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.07 sec)

mysql> drop procedure testdata;
Query OK, 0 rows affected (0.42 sec)

mysql> delimiter $$
mysql> create procedure testdata()
    -> begin
    ->
    -> declare NUMROWS int default 50;
    -> declare count int default 0;
    -> declare email_value varchar(40) default null;
    -> declare registered_value date default null;
    -> declare active_value boolean default false;
    -> drop table if exists users;
    -> create table users (id int auto_increment primary key, email varchar(40) not null, registered date not null, active boolean default false);
    -> while count < NUMROWS do
    -> set registered_value := date(now()) - interval floor(10000*rand()) day;
    -> set email_value := concat("vlruser", count, "@vlrtraining.com");
    -> set active_value := round(rand());
    -> insert into users (email, registered, active) values (email_value, registered_value, active_value);
    -> set count := count + 1;
    -> end while;
    -> end$$
Query OK, 0 rows affected (0.16 sec)

mysql> delimiter ;
mysql> call testdata();
Query OK, 1 row affected (6.95 sec)

mysql> select * from users;
+----+---------------------------+------------+--------+
| id | email                     | registered | active |
+----+---------------------------+------------+--------+
|  1 | [email protected]  | 1996-10-25 |      1 |
|  2 | [email protected]  | 2017-05-15 |      1 |
|  3 | [email protected]  | 1998-09-16 |      1 |
|  4 | [email protected]  | 2006-09-23 |      1 |
|  5 | [email protected]  | 1998-07-05 |      1 |
|  6 | [email protected]  | 2016-08-13 |      0 |
|  7 | [email protected]  | 1996-07-14 |      1 |
|  8 | [email protected]  | 1999-08-17 |      1 |
|  9 | [email protected]  | 2020-01-05 |      1 |
| 10 | [email protected]  | 1996-04-05 |      1 |
| 11 | [email protected] | 2018-07-27 |      0 |
| 12 | [email protected] | 1999-02-13 |      0 |
| 13 | [email protected] | 2003-10-03 |      0 |
| 14 | [email protected] | 2020-06-10 |      0 |
| 15 | [email protected] | 1994-08-23 |      0 |
| 16 | [email protected] | 2011-05-14 |      0 |
| 17 | [email protected] | 2013-09-18 |      0 |
| 18 | [email protected] | 2017-05-14 |      0 |
| 19 | [email protected] | 2010-11-02 |      0 |
| 20 | [email protected] | 2018-03-12 |      0 |
| 21 | [email protected] | 1997-09-14 |      1 |
| 22 | [email protected] | 2007-08-04 |      1 |
| 23 | [email protected] | 1996-07-30 |      1 |
| 24 | [email protected] | 1999-07-21 |      1 |
| 25 | [email protected] | 2018-03-27 |      0 |
| 26 | [email protected] | 2017-12-01 |      0 |
| 27 | [email protected] | 2007-12-24 |      0 |
| 28 | [email protected] | 2013-09-12 |      0 |
| 29 | [email protected] | 2020-02-21 |      0 |
| 30 | [email protected] | 2008-12-13 |      1 |
| 31 | [email protected] | 2012-02-26 |      1 |
| 32 | [email protected] | 2009-06-01 |      1 |
| 33 | [email protected] | 2010-09-24 |      0 |
| 34 | [email protected] | 2005-08-04 |      1 |
| 35 | [email protected] | 2005-08-12 |      0 |
| 36 | [email protected] | 1997-07-15 |      0 |
| 37 | [email protected] | 2008-03-03 |      0 |
| 38 | [email protected] | 2009-02-08 |      1 |
| 39 | [email protected] | 2013-05-01 |      0 |
| 40 | [email protected] | 2003-01-01 |      0 |
| 41 | [email protected] | 2015-02-16 |      1 |
| 42 | [email protected] | 2009-12-21 |      1 |
| 43 | [email protected] | 1994-07-04 |      0 |
| 44 | [email protected] | 2020-10-31 |      0 |
| 45 | [email protected] | 2003-03-16 |      1 |
| 46 | [email protected] | 2006-03-18 |      0 |
| 47 | [email protected] | 2002-12-20 |      0 |
| 48 | [email protected] | 1996-07-02 |      0 |
| 49 | [email protected] | 2012-04-15 |      1 |
| 50 | [email protected] | 2014-07-02 |      0 |
+----+---------------------------+------------+--------+
50 rows in set (0.00 sec)

mysql>


No comments:

Post a Comment

Note: only a member of this blog may post a comment.