Translate

Monday 18 October 2021

SQL FOREIGN KEY on ALTER TABLE v to existing table ,alter table sql videos in telugu 103

https://youtu.be/b5tkbbnQ_6g 

---------------------------------------------------------------
SQL FOREIGN KEY on ALTER TABLE
To create a FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is already created, use the following SQL:


ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);



ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
---------------
create table book (id int primary key auto_increment, name varchar(50), library int);
create table library(id int auto_increment primary key, name varchar(50));
insert into library (id,name) values (10,'java')

insert into book (name, library) values ('mysql', 10)

alter table book add constraint fk_book_library foreign key (library) references library(id)

alter table book drop foreign key fk_book_library



insert into library (name) values ('java'), ('dotnet');

insert into book (name, library) values ('mysql', 10)

alter table book add constraint fk_book_library foreign key (library) references library(id)

alter table book drop foreign key fk_book_library;

Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
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> create table book (id int primary key auto_increment, name varchar(50), library int);create table library(id int auto_increment primary key, name varchar(50));insert into library (name) values ('java'), ('dotnet');insert into book (name, library) values ('mysql', 10)alter table book add constraint fk_book_library foreign key (library) references library(id)alter table book drop foreign key fk_book_library;
Query OK, 0 rows affected (0.91 sec)

Query OK, 0 rows affected (1.05 sec)

Query OK, 2 rows affected (0.14 sec)
Records: 2  Duplicates: 0  Warnings: 0

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 'alter table book add constraint fk_book_library foreign key (library) references' at line 1
mysql> create table book (id int primary key auto_increment, name varchar(50), library int);
ERROR 1050 (42S01): Table 'book' already exists
mysql> create table library(id int auto_increment primary key, name varchar(50));
ERROR 1050 (42S01): Table 'library' already exists
mysql> desc book;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int         | NO   | PRI | NULL    | auto_increment |
| name    | varchar(50) | YES  |     | NULL    |                |
| library | int         | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> desc library
    -> ;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into library (name) values ('java'), ('dotnet');
Query OK, 2 rows affected (0.14 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from library;
+----+--------+
| id | name   |
+----+--------+
|  1 | java   |
|  2 | dotnet |
|  3 | java   |
|  4 | dotnet |
+----+--------+
4 rows in set (0.00 sec)

mysql> delete from library;
Query OK, 4 rows affected (0.15 sec)

mysql> insert into library (name) values ('java'), ('dotnet');
Query OK, 2 rows affected (0.17 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from library;
+----+--------+
| id | name   |
+----+--------+
|  5 | java   |
|  6 | dotnet |
+----+--------+
2 rows in set (0.00 sec)

mysql> alter table book add constraint fk_book_library foreign key (library) references library(id);
Query OK, 0 rows affected (3.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into book (name, library) values ('mysql', 10)
    -> ;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`classicmodels`.`book`, CONSTRAINT `fk_book_library` FOREIGN KEY (`library`) REFERENCES `library` (`id`))
mysql> insert into library (id,name) values (10,'java')
    -> ;
Query OK, 1 row affected (0.19 sec)

mysql> insert into book (name, library) values ('mysql', 10);
Query OK, 1 row affected (0.10 sec)

mysql> alter table book drop  fk_book_library;
ERROR 1091 (42000): Can't DROP 'fk_book_library'; check that column/key exists
mysql> alter table book drop foreign key  fk_book_library;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

How to add,delete,modify column to existing table ,alter table sql videos in telugu 102

 https://youtu.be/qsdPeYhoRZ4

-----------------------------------------------------------
SQL ALTER TABLE Statement
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

-----------
alter table person add column name varchar(50) not null after id
alter table person drop column name

ALTER TABLE Persons
ALTER COLUMN DateOfBirth year;



alter table orders add returneddate date after shippeddate ;
alter table orders drop returneddate ;

alter table orders modify returneddate year;
---------------------
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
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 classimodels;
ERROR 1049 (42000): Unknown database 'classimodels'
mysql> use classimodel;
ERROR 1049 (42000): Unknown database 'classimodel'
mysql> use classicmodels;
Database changed
mysql> show tables;
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| accounts                |
| customers               |
| employees               |
| offices                 |
| orderdetails            |
| orders                  |
| payments                |
| productlines            |
| products                |
| users                   |
+-------------------------+
10 rows in set (0.04 sec)

mysql> desc orders;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber    | int         | NO   | PRI | NULL    |       |
| orderDate      | date        | NO   |     | NULL    |       |
| requiredDate   | date        | NO   |     | NULL    |       |
| shippedDate    | date        | YES  |     | NULL    |       |
| status         | varchar(15) | NO   |     | NULL    |       |
| comments       | text        | YES  |     | NULL    |       |
| customerNumber | int         | NO   | MUL | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
7 rows in set (0.19 sec)

mysql> alter table orders add returneddate date ;
Query OK, 0 rows affected (2.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc orders;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber    | int         | NO   | PRI | NULL    |       |
| orderDate      | date        | NO   |     | NULL    |       |
| requiredDate   | date        | NO   |     | NULL    |       |
| shippedDate    | date        | YES  |     | NULL    |       |
| status         | varchar(15) | NO   |     | NULL    |       |
| comments       | text        | YES  |     | NULL    |       |
| customerNumber | int         | NO   | MUL | NULL    |       |
| returneddate   | date        | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> select * from orders limit 2;
+-------------+------------+--------------+-------------+---------+------------------------+----------------+--------------+
| orderNumber | orderDate  | requiredDate | shippedDate | status  | comments               | customerNumber | returneddate |
+-------------+------------+--------------+-------------+---------+------------------------+----------------+--------------+
|       10100 | 2003-01-06 | 2003-01-13   | 2003-01-10  | Shipped | NULL                   |            363 | NULL         |
|       10101 | 2003-01-09 | 2003-01-18   | 2003-01-11  | Shipped | Check on availability. |            128 | NULL         |
+-------------+------------+--------------+-------------+---------+------------------------+----------------+--------------+
2 rows in set (0.00 sec)

mysql> select * from orders limit 2 /g;
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 '/g' at line 1
mysql> select * from orders limit 2 \g;
+-------------+------------+--------------+-------------+---------+------------------------+----------------+--------------+
| orderNumber | orderDate  | requiredDate | shippedDate | status  | comments               | customerNumber | returneddate |
+-------------+------------+--------------+-------------+---------+------------------------+----------------+--------------+
|       10100 | 2003-01-06 | 2003-01-13   | 2003-01-10  | Shipped | NULL                   |            363 | NULL         |
|       10101 | 2003-01-09 | 2003-01-18   | 2003-01-11  | Shipped | Check on availability. |            128 | NULL         |
+-------------+------------+--------------+-------------+---------+------------------------+----------------+--------------+
2 rows in set (0.00 sec)

ERROR:
No query specified

mysql> select * from orders limit 2 \G;
*************************** 1. row ***************************
   orderNumber: 10100
     orderDate: 2003-01-06
  requiredDate: 2003-01-13
   shippedDate: 2003-01-10
        status: Shipped
      comments: NULL
customerNumber: 363
  returneddate: NULL
*************************** 2. row ***************************
   orderNumber: 10101
     orderDate: 2003-01-09
  requiredDate: 2003-01-18
   shippedDate: 2003-01-11
        status: Shipped
      comments: Check on availability.
customerNumber: 128
  returneddate: NULL
2 rows in set (0.00 sec)

ERROR:
No query specified

mysql> alter table orders drop returneddate ;
Query OK, 0 rows affected (2.75 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc orders;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber    | int         | NO   | PRI | NULL    |       |
| orderDate      | date        | NO   |     | NULL    |       |
| requiredDate   | date        | NO   |     | NULL    |       |
| shippedDate    | date        | YES  |     | NULL    |       |
| status         | varchar(15) | NO   |     | NULL    |       |
| comments       | text        | YES  |     | NULL    |       |
| customerNumber | int         | NO   | MUL | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> alter table orders add returneddate date after shippeddate ;
Query OK, 0 rows affected (3.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc orders;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber    | int         | NO   | PRI | NULL    |       |
| orderDate      | date        | NO   |     | NULL    |       |
| requiredDate   | date        | NO   |     | NULL    |       |
| shippedDate    | date        | YES  |     | NULL    |       |
| returneddate   | date        | YES  |     | NULL    |       |
| status         | varchar(15) | NO   |     | NULL    |       |
| comments       | text        | YES  |     | NULL    |       |
| customerNumber | int         | NO   | MUL | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> alter table orders alter returneddate year;
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 'year' at line 1
mysql> alter table orders modify returneddate year;
Query OK, 326 rows affected (2.69 sec)
Records: 326  Duplicates: 0  Warnings: 0

mysql> desc orders;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber    | int         | NO   | PRI | NULL    |       |
| orderDate      | date        | NO   |     | NULL    |       |
| requiredDate   | date        | NO   |     | NULL    |       |
| shippedDate    | date        | YES  |     | NULL    |       |
| returneddate   | year        | YES  |     | NULL    |       |
| status         | varchar(15) | NO   |     | NULL    |       |
| comments       | text        | YES  |     | NULL    |       |
| customerNumber | int         | NO   | MUL | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
8 rows in set (0.05 sec)

mysql> alter table orders modify shippeddate year;
ERROR 1264 (22003): Out of range value for column 'shippeddate' at row 1
mysql> alter table orders modify status varchar(29);
Query OK, 0 rows affected (2.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc orders;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber    | int         | NO   | PRI | NULL    |       |
| orderDate      | date        | NO   |     | NULL    |       |
| requiredDate   | date        | NO   |     | NULL    |       |
| shippedDate    | date        | YES  |     | NULL    |       |
| returneddate   | year        | YES  |     | NULL    |       |
| status         | varchar(29) | YES  |     | NULL    |       |
| comments       | text        | YES  |     | NULL    |       |
| customerNumber | int         | NO   | MUL | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> alter table orders modify status varchar(2);
ERROR 1265 (01000): Data truncated for column 'status' at row 1
mysql> alter table orders drop comments;
Query OK, 0 rows affected (2.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc orders;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber    | int         | NO   | PRI | NULL    |       |
| orderDate      | date        | NO   |     | NULL    |       |
| requiredDate   | date        | NO   |     | NULL    |       |
| shippedDate    | date        | YES  |     | NULL    |       |
| returneddate   | year        | YES  |     | NULL    |       |
| status         | varchar(29) | YES  |     | NULL    |       |
| customerNumber | int         | NO   | MUL | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
7 rows in set (0.02 sec)

mysql>

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

 https://youtu.be/-Cd3zMtqwVo

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


-- id
-- email: user1@vlrtraining.in, user2@vlrtraining.in, ...
-- 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 | vlruser0@vlrtraining.com  | 1996-10-25 |      1 |
|  2 | vlruser1@vlrtraining.com  | 2017-05-15 |      1 |
|  3 | vlruser2@vlrtraining.com  | 1998-09-16 |      1 |
|  4 | vlruser3@vlrtraining.com  | 2006-09-23 |      1 |
|  5 | vlruser4@vlrtraining.com  | 1998-07-05 |      1 |
|  6 | vlruser5@vlrtraining.com  | 2016-08-13 |      0 |
|  7 | vlruser6@vlrtraining.com  | 1996-07-14 |      1 |
|  8 | vlruser7@vlrtraining.com  | 1999-08-17 |      1 |
|  9 | vlruser8@vlrtraining.com  | 2020-01-05 |      1 |
| 10 | vlruser9@vlrtraining.com  | 1996-04-05 |      1 |
| 11 | vlruser10@vlrtraining.com | 2018-07-27 |      0 |
| 12 | vlruser11@vlrtraining.com | 1999-02-13 |      0 |
| 13 | vlruser12@vlrtraining.com | 2003-10-03 |      0 |
| 14 | vlruser13@vlrtraining.com | 2020-06-10 |      0 |
| 15 | vlruser14@vlrtraining.com | 1994-08-23 |      0 |
| 16 | vlruser15@vlrtraining.com | 2011-05-14 |      0 |
| 17 | vlruser16@vlrtraining.com | 2013-09-18 |      0 |
| 18 | vlruser17@vlrtraining.com | 2017-05-14 |      0 |
| 19 | vlruser18@vlrtraining.com | 2010-11-02 |      0 |
| 20 | vlruser19@vlrtraining.com | 2018-03-12 |      0 |
| 21 | vlruser20@vlrtraining.com | 1997-09-14 |      1 |
| 22 | vlruser21@vlrtraining.com | 2007-08-04 |      1 |
| 23 | vlruser22@vlrtraining.com | 1996-07-30 |      1 |
| 24 | vlruser23@vlrtraining.com | 1999-07-21 |      1 |
| 25 | vlruser24@vlrtraining.com | 2018-03-27 |      0 |
| 26 | vlruser25@vlrtraining.com | 2017-12-01 |      0 |
| 27 | vlruser26@vlrtraining.com | 2007-12-24 |      0 |
| 28 | vlruser27@vlrtraining.com | 2013-09-12 |      0 |
| 29 | vlruser28@vlrtraining.com | 2020-02-21 |      0 |
| 30 | vlruser29@vlrtraining.com | 2008-12-13 |      1 |
| 31 | vlruser30@vlrtraining.com | 2012-02-26 |      1 |
| 32 | vlruser31@vlrtraining.com | 2009-06-01 |      1 |
| 33 | vlruser32@vlrtraining.com | 2010-09-24 |      0 |
| 34 | vlruser33@vlrtraining.com | 2005-08-04 |      1 |
| 35 | vlruser34@vlrtraining.com | 2005-08-12 |      0 |
| 36 | vlruser35@vlrtraining.com | 1997-07-15 |      0 |
| 37 | vlruser36@vlrtraining.com | 2008-03-03 |      0 |
| 38 | vlruser37@vlrtraining.com | 2009-02-08 |      1 |
| 39 | vlruser38@vlrtraining.com | 2013-05-01 |      0 |
| 40 | vlruser39@vlrtraining.com | 2003-01-01 |      0 |
| 41 | vlruser40@vlrtraining.com | 2015-02-16 |      1 |
| 42 | vlruser41@vlrtraining.com | 2009-12-21 |      1 |
| 43 | vlruser42@vlrtraining.com | 1994-07-04 |      0 |
| 44 | vlruser43@vlrtraining.com | 2020-10-31 |      0 |
| 45 | vlruser44@vlrtraining.com | 2003-03-16 |      1 |
| 46 | vlruser45@vlrtraining.com | 2006-03-18 |      0 |
| 47 | vlruser46@vlrtraining.com | 2002-12-20 |      0 |
| 48 | vlruser47@vlrtraining.com | 1996-07-02 |      0 |
| 49 | vlruser48@vlrtraining.com | 2012-04-15 |      1 |
| 50 | vlruser49@vlrtraining.com | 2014-07-02 |      0 |
+----+---------------------------+------------+--------+
50 rows in set (0.00 sec)

mysql>


Randam records generation using stored procedure sql videos in telugu 100

 https://youtu.be/K7yDLrqoT-U

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


-- id
-- email: user1@vlrtraining.in, user2@vlrtraining.in, ...
-- 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 500;
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

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$$
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>

labled Loops in stored procedure sql videos in telugu 99

 https://youtu.be/cOi_TNMtO2w

----------------------------------------------------
The MySQL LOOP statement could be used to run a block of code or set of statements, again and again, depends on the condition.

Syntax :

[labelname:] LOOP
   statements
END LOOP [labelname]
Parameters –

labelname : It is an optional label at the start and end.
statements : They could have one or multiple statements, each ended by a semicolon (;) and executed by LOOP.

https://www.geeksforgeeks.org/loops-in-mysql/

drop procedure loopvlr;

delimiter $$
create procedure loopvlr()
begin
declare count int default 0;
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set count := count + 1;
select count;
end loop;
end$$

delimiter ;

drop procedure loopvlr;

delimiter $$
create procedure loopvlr()
begin
declare count int default 0;
declare nlist varchar(40) default "";
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set nlist := concat(nlist,count);
set count := count + 1;


end loop;
select nlist;
end$$

delimiter ;






------------------
drop procedure loopvlr;

delimiter $$
create procedure loopvlr()
begin
declare count int default 0;
declare nlist varchar(40) default "";
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set nlist := concat(nlist, count);
if count != 9 then
set nlist := concat(nlist,",");
end if;

set count := count + 1;

end loop;
select nlist;
end$$

delimiter ;
----------------
drop procedure loopvlr;

delimiter $$
create procedure loopvlr()
begin
declare count int default 1;
declare nlist varchar(30) default "";
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set nlist := concat(nlist, count);
set nlist := concat(nlist,",");
set count := count + 1;

end loop;
select nlist;
end$$

delimiter ;
--------------------
drop procedure loopvlr;

delimiter $$
create procedure loopvlr()
begin
declare count int default 1;
declare nlist varchar(30) default "";
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set nlist := concat(nlist, count);

 if count != 9 then
set nlist := concat(nlist,",");
end if;

set count := count + 1;

end loop;
select nlist;
end$$

delimiter ;

---------------------
delimiter $$

create procedure loopdemo()
begin

declare count int default 0;
declare numberlist varchar(30) default "";

the_loop: loop

if count = 10 then
leave the_loop;
end if;

set numberlist := concat(numberlist, count);
    
    if count != 9 then
set numberlist := concat(numberlist, ", ");
end if;

set count := count + 1;

end loop;

select numberlist;

end$$


delimiter ;
------------------


Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
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> drop procedure loopvlr;
Query OK, 0 rows affected (0.20 sec)

mysql> delimiter $$
mysql> create procedure loopvlr()
    -> begin
    -> declare count int default 0:
    -> the_loop: loop
    -> if count = 10 then
    -> leave the_loop;
    -> end if;
    -> set count := count+1;
    -> select count;
    -> end loop;
    -> end$$
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 ':
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set count := count+1' at line 3
mysql> delimiter ;drop procedure loopvlr;
mysql> delimiter $$
mysql> create procedure loopvlr()
    -> begin
    -> declare count int default 0:
    -> the_loop: loop
    -> if count = 10 then
    -> leave the_loop;
    -> end if;
    -> set count := count+1;
    -> select count;
    -> end loop;
    -> end$$
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 ':
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set count := count+1' at line 3
mysql> delimiter ;
mysql> drop procedure loopvlr;
ERROR 1305 (42000): PROCEDURE classicmodels.loopvlr does not exist
mysql> delimiter $$
mysql> create procedure loopvlr()
    -> begin
    -> declare count int default 0:
    -> the_loop: loop
    -> if count = 10 then
    -> leave the_loop;
    -> end if;
    -> set count := count+1;
    -> select count;
    -> end loop;
    -> end$$
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 ':
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set count := count+1' at line 3
mysql> delimiter ;
mysql> drop procedure loopvlr;
ERROR 1305 (42000): PROCEDURE classicmodels.loopvlr does not exist
mysql> delimiter $$
mysql> create procedure loopvlr()
    -> begin
    -> declare count int default 0:
    -> the_loop: loop
    -> if count = 10 then
    -> leave the_loop;
    -> end if;
    -> set count := count + 1;
    -> select count;
    -> end loop;
    -> end$$
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 ':
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set count := count +' at line 3
mysql> delimiter ;
mysql> drop procedure loopvlr;
ERROR 1305 (42000): PROCEDURE classicmodels.loopvlr does not exist
mysql>
mysql> delimiter $$
mysql> create procedure loopvlr()
    -> begin
    -> declare count int default 0;
    -> the_loop: loop
    -> if count = 10 then
    -> leave the_loop;
    -> end if;
    -> set count := count + 1;
    -> select count;
    -> end loop;
    -> end$$
Query OK, 0 rows affected (0.14 sec)

mysql>
mysql> delimiter ;
mysql> drop procedure loopvlr;
Query OK, 0 rows affected (0.19 sec)

mysql> delimiter $$
mysql> create procedure loopvlr()
    -> begin
    -> declare count int default 0:
    -> the_loop: loop
    -> if count = 10 then
    -> leave the_loop;
    -> end if;
    -> set count := count + 1;
    -> select count;
    -> end loop;
    -> end$$
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 ':
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set count := count +' at line 3
mysql> delimiter ;
mysql>
mysql> drop procedure loopvlr;
ERROR 1305 (42000): PROCEDURE classicmodels.loopvlr does not exist
mysql> delimiter $$
mysql> create procedure loopvlr()
    -> begin
    -> declare count int default 0:
    -> the_loop: loop
    -> if count = 10 then
    -> leave the_loop;
    -> end if;
    -> set count := count + 1;
    -> select count;
    -> end loop;
    -> end$$
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 ':
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set count := count +' at line 3
mysql>
mysql> delimiter ;
mysql> drop procedure loopvlr;
ERROR 1305 (42000): PROCEDURE classicmodels.loopvlr does not exist
mysql> drop procedure loopvlr;
ERROR 1305 (42000): PROCEDURE classicmodels.loopvlr does not exist
mysql>
mysql> delimiter $$
mysql> create procedure loopvlr()
    -> begin
    -> declare count int default 0:
    -> the_loop: loop
    -> if count = 10 then
    -> leave the_loop;
    -> end if;
    -> set count := count + 1;
    -> select count;
    -> end loop;
    -> end$$
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 ':
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set count := count +' at line 3
mysql>
mysql> delimiter ;
mysql>
mysql> drop procedure loopvlr;
ERROR 1305 (42000): PROCEDURE classicmodels.loopvlr does not exist
mysql>
mysql> delimiter $$
mysql> create procedure loopvlr()
    -> begin
    -> declare count int default 0;
    -> the_loop: loop
    -> if count = 10 then
    -> leave the_loop;
    -> end if;
    -> set count := count + 1;
    -> select count;
    -> end loop;
    -> end$$
Query OK, 0 rows affected (0.14 sec)

mysql>
mysql> delimiter ;
mysql> call loopvlr();
+-------+
| count |
+-------+
|     1 |
+-------+
1 row in set (0.02 sec)

+-------+
| count |
+-------+
|     2 |
+-------+
1 row in set (0.02 sec)

+-------+
| count |
+-------+
|     3 |
+-------+
1 row in set (0.03 sec)

+-------+
| count |
+-------+
|     4 |
+-------+
1 row in set (0.04 sec)

+-------+
| count |
+-------+
|     5 |
+-------+
1 row in set (0.05 sec)

+-------+
| count |
+-------+
|     6 |
+-------+
1 row in set (0.06 sec)

+-------+
| count |
+-------+
|     7 |
+-------+
1 row in set (0.07 sec)

+-------+
| count |
+-------+
|     8 |
+-------+
1 row in set (0.08 sec)

+-------+
| count |
+-------+
|     9 |
+-------+
1 row in set (0.09 sec)

+-------+
| count |
+-------+
|    10 |
+-------+
1 row in set (0.10 sec)

Query OK, 0 rows affected (0.11 sec)

mysql> drop procedure loopvlr;
Query OK, 0 rows affected (0.16 sec)

mysql>
mysql> delimiter $$
mysql> create procedure loopvlr()
    -> begin
    -> declare count int default 0;
    -> declare nlist varchar(40) default "";
    -> the_loop: loop
    -> if count = 10 then
    -> leave the_loop;
    -> end if;
    -> set nlist := concat(nlist, count);
    ->
    -> set count := count + 1;
    ->
    -> end loop;
    -> select nlist;
    -> end$$
Query OK, 0 rows affected (0.10 sec)

mysql>
mysql> delimiter ;
mysql> call vlrinst();
ERROR 1305 (42000): PROCEDURE classicmodels.vlrinst does not exist
mysql> call loopvlr();
+------------+
| nlist      |
+------------+
| 0123456789 |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> drop procedure loopvlr;
Query OK, 0 rows affected (0.10 sec)

mysql>
mysql> delimiter $$
mysql> create procedure loopvlr()
    -> begin
    -> declare count int default 0;
    -> declare nlist varchar(40) default "";
    -> the_loop: loop
    -> if count = 10 then
    -> leave the_loop;
    -> end if;
    -> set count := count + 1;
    -> set nlist := concat(nlist,count);
    ->
    -> end loop;
    -> select nlist;
    -> end$$
Query OK, 0 rows affected (0.25 sec)

mysql>
mysql> delimiter ;
mysql> call loopvlr();
+-------------+
| nlist       |
+-------------+
| 12345678910 |
+-------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> drop procedure loopvlr;
Query OK, 0 rows affected (0.18 sec)

mysql>
mysql> delimiter $$
mysql> create procedure loopvlr()
    -> begin
    -> declare count int default 0;
    -> declare nlist varchar(40) default "";
    -> the_loop: loop
    -> if count = 10 then
    -> leave the_loop;
    -> end if;
    -> set count := count + 1;
    -> set nlist := concat(count,nlist);
    ->
    -> end loop;
    -> select nlist;
    -> end$$
Query OK, 0 rows affected (0.12 sec)

mysql>
mysql> delimiter ;
mysql> call loopvlr();
+-------------+
| nlist       |
+-------------+
| 10987654321 |
+-------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> drop procedure loopvlr;
Query OK, 0 rows affected (0.12 sec)

mysql>
mysql> delimiter $$
mysql> create procedure loopvlr()
    -> begin
    -> declare count int default 0;
    -> declare nlist varchar(40) default "";
    -> the_loop: loop
    -> if count = 10 then
    -> leave the_loop;
    -> end if;
    -> set nlist := concat(count,nlist);
    -> set count := count + 1;
    ->
    ->
    -> end loop;
    -> select nlist;
    -> end$$
Query OK, 0 rows affected (0.11 sec)

mysql>
mysql> delimiter ;
mysql> call loopvlr();
+------------+
| nlist      |
+------------+
| 9876543210 |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> drop procedure loopvlr;
Query OK, 0 rows affected (0.17 sec)

mysql>
mysql> delimiter $$
mysql> create procedure loopvlr()
    -> begin
    -> declare count int default 0;
    -> declare nlist varchar(40) default "";
    -> the_loop: loop
    -> if count = 10 then
    -> leave the_loop;
    -> end if;
    -> set nlist := concat(nlist, count);
    -> set nlist := concat(nlist,",")
    -> set count := count + 1;
    ->
    -> end loop;
    -> select nlist;
    -> end$$
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 'set count := count + 1;

end loop;
select nlist;
end' at line 11
mysql>
mysql> delimiter ;
mysql> drop procedure loopvlr;
ERROR 1305 (42000): PROCEDURE classicmodels.loopvlr does not exist
mysql>
mysql> delimiter $$
mysql> create procedure loopvlr()
    -> begin
    -> declare count int default 0;
    -> declare nlist varchar(40) default "";
    -> the_loop: loop
    -> if count = 10 then
    -> leave the_loop;
    -> end if;
    -> set nlist := concat(nlist, count);
    -> set nlist := concat(nlist,",");
    -> set count := count + 1;
    ->
    -> end loop;
    -> select nlist;
    -> end$$
Query OK, 0 rows affected (0.13 sec)

mysql>
mysql> delimiter ;
mysql> call loopvlr();
+----------------------+
| nlist                |
+----------------------+
| 0,1,2,3,4,5,6,7,8,9, |
+----------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> drop procedure loopvlr;
Query OK, 0 rows affected (0.22 sec)

mysql>
mysql> delimiter $$
mysql> create procedure loopvlr()
    -> begin
    -> declare count int default 0;
    -> declare nlist varchar(40) default "";
    -> the_loop: loop
    -> if count = 10 then
    -> leave the_loop;
    -> end if;
    -> set nlist := concat(nlist, count);
    -> if count != 9
    -> set nlist := concat(nlist,",");
    -> end if;
    ->
    -> set count := count + 1;
    ->
    -> end loop;
    -> select nlist;
    -> end$$
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 'set nlist := concat(nlist,",");
end if;

set count := count + 1;

end loop;
sele' at line 11
mysql>
mysql> delimiter ;
mysql> drop procedure loopvlr;
ERROR 1305 (42000): PROCEDURE classicmodels.loopvlr does not exist
mysql>
mysql> delimiter $$
mysql> create procedure loopvlr()
    -> begin
    -> declare count int default 0;
    -> declare nlist varchar(40) default "";
    -> the_loop: loop
    -> if count = 10 then
    -> leave the_loop;
    -> end if;
    -> set nlist := concat(nlist, count);
    -> if count != 9 then
    -> set nlist := concat(nlist,",");
    -> end if;
    ->
    -> set count := count + 1;
    ->
    -> end loop;
    -> select nlist;
    -> end$$
Query OK, 0 rows affected (0.11 sec)

mysql>
mysql> delimiter ;
mysql> call loopvlr();
+---------------------+
| nlist               |
+---------------------+
| 0,1,2,3,4,5,6,7,8,9 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>























https://www.mysqltutorial.org/mysql-stored-procedure/mysql-while-loop/

While Loops in stored procedure sql videos in telugu 98

 https://youtu.be/2V_0bV41sXQ

------------------------------------------------------
The WHILE loop is a loop statement that executes a block of code repeatedly as long as a condition is true.


WHILE expression DO
statements
END WHILE
----------------
delimiter $$
create procedure whilevlr()
begin
declare count int default 0;
while count < 10 do
set count := count + 1;
select count;
end while;
end$$
delimiter ;

delimiter $$
create procedure whilevlr1()
begin
declare count int default 0;
while count < 10 do
set count := count+1;
select count;
end while;
end$$
delimiter;


delimiter $$
create procedure whilevlr3()
begin
declare evennum int default 0;
declare res varchar(100) default "";
while evennum < 50 do
set evennum := evennum+2;
set res := concat( res," - ", evennum);
end while;
select res;
end$$

delimiter ;

call whilevlr3()




----------
drop procedure whilevlr;
delimiter $$
create procedure whilevlr()
begin
declare count int default 0;
while count < 10 do
set count := count + 1;
-- select count;
end while;
select count;
end$$
delimiter ;
------------------
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
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> delimiter $$
mysql> create procedure whilevlr3()
    -> begin
    -> declare evennum int default 0;
    -> while evennum < 50 do
    -> set evennum := evennum+2;
    -> select count;
    -> end while;
    -> end$$
Query OK, 0 rows affected (0.22 sec)

mysql> delimiter;
    ->  ;
    -> $$
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 'delimiter' at line 1
mysql> delimeter ;
    -> delimiter ;
    -> delimiter $
    -> delimiter $$
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 'delimeter ;
delimiter ;delimiter $delimiter' at line 1
mysql> delimiter $$
mysql> create procedure whilevlr3()
    -> begin
    -> declare evennum int default 0;
    -> while evennum < 50 do
    -> set evennum := evennum+2;
    -> select count;
    -> end while;
    -> end$$
ERROR 1304 (42000): PROCEDURE whilevlr3 already exists
mysql>
mysql> delimiter ;
mysql> drop procedure whilevlr3;
Query OK, 0 rows affected (0.21 sec)

mysql> delimiter $$
mysql> create procedure whilevlr3()
    -> begin
    -> declare evennum int default 0;
    -> while evennum < 50 do
    -> set evennum := evennum+2;
    -> select count;
    -> end while;
    -> end$$
Query OK, 0 rows affected (0.23 sec)

mysql>
mysql> delimiter ;
mysql> call whilevlr3();
ERROR 1054 (42S22): Unknown column 'count' in 'field list'
mysql> drop procedure whilevlr3;
Query OK, 0 rows affected (0.15 sec)

mysql> delimiter $$
mysql> create procedure whilevlr3()
    -> begin
    -> declare evennum int default 0;
    -> while evennum < 50 do
    -> set evennum := evennum+2;
    -> select evennum;
    -> end while;
    -> end$$
Query OK, 0 rows affected (0.13 sec)

mysql>
mysql> delimiter ;
mysql> call whilevlr3();
+---------+
| evennum |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

+---------+
| evennum |
+---------+
|       4 |
+---------+
1 row in set (0.00 sec)

+---------+
| evennum |
+---------+
|       6 |
+---------+
1 row in set (0.01 sec)

+---------+
| evennum |
+---------+
|       8 |
+---------+
1 row in set (0.01 sec)

+---------+
| evennum |
+---------+
|      10 |
+---------+
1 row in set (0.03 sec)

+---------+
| evennum |
+---------+
|      12 |
+---------+
1 row in set (0.04 sec)

+---------+
| evennum |
+---------+
|      14 |
+---------+
1 row in set (0.04 sec)

+---------+
| evennum |
+---------+
|      16 |
+---------+
1 row in set (0.05 sec)

+---------+
| evennum |
+---------+
|      18 |
+---------+
1 row in set (0.06 sec)

+---------+
| evennum |
+---------+
|      20 |
+---------+
1 row in set (0.06 sec)

+---------+
| evennum |
+---------+
|      22 |
+---------+
1 row in set (0.07 sec)

+---------+
| evennum |
+---------+
|      24 |
+---------+
1 row in set (0.08 sec)

+---------+
| evennum |
+---------+
|      26 |
+---------+
1 row in set (0.09 sec)

+---------+
| evennum |
+---------+
|      28 |
+---------+
1 row in set (0.10 sec)

+---------+
| evennum |
+---------+
|      30 |
+---------+
1 row in set (0.10 sec)

+---------+
| evennum |
+---------+
|      32 |
+---------+
1 row in set (0.11 sec)

+---------+
| evennum |
+---------+
|      34 |
+---------+
1 row in set (0.12 sec)

+---------+
| evennum |
+---------+
|      36 |
+---------+
1 row in set (0.13 sec)

+---------+
| evennum |
+---------+
|      38 |
+---------+
1 row in set (0.14 sec)

+---------+
| evennum |
+---------+
|      40 |
+---------+
1 row in set (0.15 sec)

+---------+
| evennum |
+---------+
|      42 |
+---------+
1 row in set (0.15 sec)

+---------+
| evennum |
+---------+
|      44 |
+---------+
1 row in set (0.16 sec)

+---------+
| evennum |
+---------+
|      46 |
+---------+
1 row in set (0.17 sec)

+---------+
| evennum |
+---------+
|      48 |
+---------+
1 row in set (0.18 sec)

+---------+
| evennum |
+---------+
|      50 |
+---------+
1 row in set (0.18 sec)

Query OK, 0 rows affected (0.20 sec)

mysql> drop wilevlr3;
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 'wilevlr3' at line 1
mysql> drop whilevlr3;
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 'whilevlr3' at line 1
mysql> drop whilevlr3();
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 'whilevlr3()' at line 1
mysql> drop whilevlr3;
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 'whilevlr3' at line 1
mysql> drop procedure whilevlr3;
Query OK, 0 rows affected (0.19 sec)

mysql> delimiter $$
mysql> create procedure whilevlr3()
    -> begin
    -> declare evennum int default 0;
    -> declare res varchar(100) default "";
    -> while evennum < 50 do
    -> set evennum := evennum+2;
    -> set res := concat( res,evennum)
    -> end while;
    -> select res;
    -> end$$
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 'end while;
select res;
end' at line 8
mysql>
mysql> delimiter ;
mysql> delimiter $$
mysql> create procedure whilevlr3()
    -> begin
    -> declare evennum int default 0;
    -> declare res varchar(100) default "";
    -> while evennum < 50 do
    -> set evennum := evennum+2;
    -> set res := concat( res,evennum);
    -> end while;
    -> select res;
    -> end$$
Query OK, 0 rows affected (0.14 sec)

mysql>
mysql> delimiter ;
mysql> call whilevlr3();
+------------------------------------------------+
| res                                            |
+------------------------------------------------+
| 2468101214161820222426283032343638404244464850 |
+------------------------------------------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> drop procedure whilevlr3;
Query OK, 0 rows affected (0.24 sec)

mysql> delimiter $$
mysql> create procedure whilevlr3()
    -> begin
    -> declare evennum int default 0;
    -> declare res varchar(100) default "";
    -> while evennum < 50 do
    -> set evennum := evennum+2;
    -> set res := concat( res,",",evennum);
    -> end while;
    -> select res;
    -> end$$
Query OK, 0 rows affected (0.33 sec)

mysql>
mysql> delimiter ;
mysql> call whilevlr3();
+-------------------------------------------------------------------------+
| res                                                                     |
+-------------------------------------------------------------------------+
| ,2,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,38,40,42,44,46,48,50 |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>














https://www.mysqltutorial.org/mysql-stored-procedure/mysql-while-loop/