SQL Joins

1. Login in to the mysql client using the login name and password provided by your instructor (if
not already logged in).

2. View the existing databases that the MySQL Server is maintaining.

SHOW DATABASES;

3. Utilize the photo_album database.

USE photo_album;

---- If Need be, recreate the database and load the SQL data
in that will be used for this lab

CREATE DATABASE photo_album;
SOURCE /tmp/photo_album.sql;

4. Query the database to display the active primary customer (email, first and last name) and all
the associated active login names associated with that customer.

SELECT customers.email_customer, customers.fname,customers.lname, logins.login_name FROM customers, loginsWHERE customers.email_customer = logins.email_customer AND customers.active_customer = 'True' AND logins.active_login = 'True';



5. Modify the query just issued to also show the image name of all the active files associated with
each one of the login names.

SELECT customers.email_customer, customers.fname,customers.lname, logins.login_name,images.file_name
FROM customers, logins, images WHERE customers.email_customer = logins.email_customer
AND logins.login_name =images.login_name AND customers.active_customer = 'True' AND logins.active_login = 'True' AND images.active_image = 'True';


6. The customer with the login name of Frankie has requested that his login name be changed to
just 'Frank' to make it easier for him to remember it. The only problem is that the change must
also take place in the images table. This is not automatic due to the fact that this table is a
MyISAM table (rightfully so) and there is no Foreign Key constraints that would make the
change automatic. However, this is not a problem with the right update statement against the
two tables at once. Can you make one query that will update both the tables at once?

UPDATE logins, images, comments SET logins.login_name = 'Frank',images.login_name = 'Frank'
WHERE logins.login_name = images.login_name AND logins.login_name = 'Frankie';