I. Lab Setup

In order to setup this lab for your local users, you will need to install and configure mysql with the appropriate accounts and access. We are going to use the sample database file provided by the O’Reilly book, Learning SQL by Alan Beaulieu. Now, let’s install the mysql utilities and server,

[root@localhost]# yum install mysql
[root@localhost]# yum install mysql-server

Start the daemon and verify the process is running,

[root@localhost]# service mysqld start
[root@localhost]# pgrep -l mysql

Connect to the mysql database, set the root password and verify it is working correctly,

[root@localhost]# mysql -u root
mysql> set password for root@localhost=password('mysecret');
mysql> select user,host,password from mysql.user;
mysql> exit
[root@localhost]# mysql -u root -p
Enter password:

Download the sample sql database and save it locally in the root home directory as bank.sql, LearningSQLExample.sql.

Create the database and permissions for each user. In our case, it will be John Smith and Jane Doe,

mysql> create database jsmith_bank;
mysql> show databases;
mysql> use jsmith_bank;
mysql> source /root/bank.sql;

mysql> create database jane_bank;
mysql> show databases;
mysql> use jane_bank;
mysql> source /root/bank.sql;
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP ON jsmith_bank.* TO jsmith@'localhost' IDENTIFIED BY 'password';
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP ON jane_bank.* TO jane@'localhost' IDENTIFIED BY 'password';

Add local accounts with the same password if they do not already exist,

[root@localhost]# useradd -c "John Smith" jsmith
[root@localhost]# useradd -c "Jane Doe" jane
[root@localhost]# passwd jsmith
[root@localhost]# passwd jane

II. LAB part 1

Your mysql username and password are the same as your ssh login credentials. In order to connect the your database, type the following at the $ prompt,

[username@lab-100~]$ mysql -p -u username

Enter your password and you should see a mysql prompt. View the available databases,

mysql> show databases;

You should see a database named, username_bank. To access your database type,

mysql> use username_bank;

A preloaded bank database has been made available for testing,

mysql> show tables;

Before we review the bank tables, let’s test creating a new tables and inserting values into these tables.

Let’s create 2 tables for our bank database. The first table will be called rollovercust and
the second table we will call rolloveracct. Remeber you ‘wrap’ lines until you get to the ‘;‘ sign.
(This could all be typed on the same line)

 mysql> create table rollovercust
 mysql> (cust_id int,
 mysql> fname varchar(20),
 mysql> lname varchar(20),
 mysql> class varchar(20),
 mysql> gender enum('M','F'),
 mysql> address varchar(20) );

Verify and describe the table rollovercust,

 mysql> show tables;
 mysql> desc rollovercust;

Create the second table, rolloveracct,

 mysql> create table rolloveracct
 mysql> (acct varchar(20),
 mysql> system enum('F','S'),
 mysql> building varchar(20) );

Verify and describe the table rolloveracct,

 mysql> show tables;
 mysql> desc classes;

Insert data into the new tables you just created,

 mysql> insert into rollovercust
 mysql> (cust_id, fname, lname, class, gender, address)
 mysql> values (123, 'John', 'Smith', '54', 'M', 'New York');
 mysql> insert  into  rollovercust
 (Up Arrow)
 mysql> (cust_id, fname, lname, class, gender, address)
 mysql> values (456, 'Jane', 'Smith', '12', 'F', 'Atlanta');

Verify,

mysql> select * from rollovercust;

Using the same insert format, enter the following values into the rollovercust table,

 111  Jo Sho     54   F   Westington
 222  Fred Mac   12   M   Berryville
 333  Sallie Mae 22   F   London
 444  Will Wonka 44   M   Candyland
 555  Perl Koder 44   M   Monkscorner

Verify,

mysql> select * from rollovercust;

Insert data into the rolloveracct table,

 mysql> insert into rolloveracct
 mysql> (acct, system, building)
 mysql> values ('1212', 'F', 'A1 Hall');
 mysql> insert into rolloveracct
 mysql> (acct, system, building)
 mysql> values('254', 'F', 'A2 Hall');

Verify,

mysql> select * from rolloveracct;

Using the same insert format, enter the following values into the rolloveracct table,

 222   F  A3 Hall
 333   S  L1 Hall
 444   S  W1 Hall

Verify,

mysql> select * from rolloveracct;

Test various select statements,

 mysql> select fname, lname from rollovercust;
 mysql> select lname, fname from rollovercust;

Change the heading format using as statement,

mysql> select fname as "First Name", lname  as  "Last Name" from rollovercust;

Try using the where clause,

 mysql> select cust_id, fname, lname
 mysql> from rollovercust
 mysql> where class = '54';
 mysql> select acct, building from rolloveracct
 mysql> where system = 'S';

Try your own select statements on both of the tables.

III. Lab Part 2

Now, let’s review the tables within the bank database. Take a few minutes to review the data in each table using the select statement,

 mysql> show tables;
 mysql> select * from tablename;

If you have trouble seeing the columns, try adding the limit option at the end,

mysql> select * from tablename limit 5;

Let’s use the where query option and select specific columns instead of all using ‘*‘,

mysql> select fname, lname from employee where title = 'Teller';

Try changing the column title using the as option,

 mysql> select fname as 'First Name', lname as 'Last Name' from employee
 myslq> where title = 'Teller';

Add the and/or options with the where clause and look at the difference,

 mysql> select fname from employee where title = 'Teller' and
 mysql> assigned_branch_id = 4;
 mysql> select fname, lname, title, assigned_branch_id from employee
 mysql> where title = 'Teller' or assigned_branch_id = 4;

Remove duplicates from a table using the distinct option,

mysql> select distinct cust_id from account;

Try,

 mysql> select status from  account;
 mysql> select distinct status from account;

Look at =, !=, <, > signs for numeric values,

 mysql> select * from employee;
 mysql> select * from employee where start_date > '2003-01-01';
 mysql> select * from account where avail_balance > 600.00 and
 mysql> avail_balance < 2000.00;
 mysql> select * from account where product_cd != 'CHK';

Shorten the above search with the between operator,

 mysql> select * from account where avail_balance between
 myslq> 600.00 and 2000.00;

Besides the wildcard ‘*‘, you can also use a ‘_‘ and ‘%‘ with the like operator
to represent one character or a number of characters. The account_id goes
from 1 to 24, but the following will only print the first 9 entries since ‘_‘ only
matches 1 character,

mysql> select * from account where account_id like  '_';

Try,

mysql> select * from customer where fed_id like  '___-__-____';

Example with ‘%‘,

A%    will match strings that begin with ‘A’
%e    will match strings that end with ‘e’
%ing%    will match strings that contain an ‘ing’

 mysql> select * from account where product_cd like 'A%';
 mysql> select * from employee where lname like '%e';
 mysql> select * from employee where lname like '%ing%';

Notice that both the employee table and department table have a dept_id field.

 mysql> select * from employee;
 mysql> select * from department;

We can join columns from different tables on the dept_id value,

 mysql> select e.fname, e.lname, d.name from employee e join
 mysql> department d on e.dept_id = d.dept_id;

Last week we looked at using insert. Review last weeks lab for insert examples.
We know how to drop a database or table but what about using update or
delete to change one row within a table,

 mysql> select * from employee;
 mysql> update employee set lname = 'Edwards' where emp_id = 8;
 mysql> select * from employee where emp_id = 8;
 mysql> update employee set title = 'Manager' where emp_id = 18;
 mysql> select * from employee where emp_id = 18;
 mysql> select * from account;
 mysql> delete from account where cust_id = 13;

Feel free to continue testing sql statements. To exit mysql type,

 mysql> quit

To logout of the server, just type exit at the shell prompt,

[username@lab-100~]$ exit