OpenVMS Source Code Demos

mysql_demo05

/*      ============================================================================
	title   : mysql_demo05.sql
	created : 2014-03-15
	author  : Neil Rieck	(https://neilrieck.net)
		: Waterloo, Ontario, Canada.
	purpose : exploring the wonders of MariaDB/MySQL
	use     : log onto mysql with a privileged account with the "-v" switch
		: then type this command:
			source MySQL_DEMO05.sql
	==========================================================================*/
	system echo "MySQL_DEMO05.sql";
	--
	--	show what databases are already present
	--
	system echo "showing databases";
	show databases;
	--
	--	create a test database called 'junkdb'
	--
	system echo "creating test database";
	create database if not exists	junkdb;
	--
	--	will use database junkdb by default
	--
	use junkdb;
	--
	--	create some test tables
	--
	system echo "creating test tables";
	create table	if not exists	junktbl1(d91_emp_id     varchar(10),
						 d91_last_name	varchar(20),
						 d91_first_name	varchar(20),
						 d91_level	varchar(1));
	create table	if not exists	junktbl2(d91_emp_id     varchar(10),
						 d91_last_name  varchar(20),
						 d91_first_name	varchar(20),
						 d91_level	varchar(1),
						 primary key (d91_emp_id));
	create table	if not exists	junktbl3(d91_emp_id     varchar(10)	UNIQUE NOT NULL,
						 d91_last_name	varchar(20),
						 d91_first_name	varchar(20),
						 d91_level	varchar(1));
	--
	--	compare the tables
	--
	system echo "describing tables";
	describe junktbl1;
	describe junktbl2;
	describe junktbl3;
	--
	--	insert data into table1
	--
	system echo "inserting test data";
	insert into junktbl1 values('1','RIECK'    ,'NEIL' ,'E');
	insert into junktbl1 values('2','KENNEL'   ,'STEVE','E');
	insert into junktbl1 values('3','MCNEIL'   ,'DAVE' ,'E');
	insert into junktbl1 values('4','MACKLAI'  ,'KARIM','D');
	insert into junktbl1 values('5','MCAULIFFE','KEVIN','D');
	--
	--	copy test data to other tables
	--
	select 'copying test data to other tables' as ' ';
	insert into junktbl2 select * from junktbl1;
	insert into junktbl3 select * from junktbl1;
	--
	--	display the execution plan for retrieval #1
	--
	select 'execution plan for retrieval #1' as ' ';
	explain select * from junktbl1 order by d91_emp_id;
	explain select * from junktbl2 order by d91_emp_id;
	explain select * from junktbl3 order by d91_emp_id;
	--
	--	display the execution plan for retrieval #2
	--
	select 'execution plan for retrieval #2' as ' ';
	explain select * from junktbl1 order by d91_last_name;
	explain select * from junktbl2 order by d91_last_name;
	explain select * from junktbl3 order by d91_last_name;