Before you do the exercises

If you havent already done so, you should create a folder in your personal drive for all the exercises. This set of exercises requires a functioning webserver to interpret the PHP code.

Exercise 8-1 Managing Tools

1. Start phpAdmin. If you are asked to login in, jump to step X.

   If you are not asked to login in, then you should see the phpMyAdmin panel as shown in Figure8.1. If this is your first time using phpAdmin on your machine, you will likely need to do steps 2-Y.

2. If this is the first-time using MySQL on your machine, you should specify a password for the MySQL root user before proceeding. To do so, click on the Users accounts/tab at the top of the phpAdmin page.

3. From the list of users, click the Edit Privileges link for user root, host 127.0.0.1. You will see a list of privileges and other information for this user.

4. Scroll down to the login information. And specify a password for the root user. Be sure to remember yours!! Click the Go button when ready. Return to the main phpMyAdmin page by clicking on the logo phpMyAdmin and then jumping to step 6. You should see a message indicating that the password was changed successfully.

   Figure 8.1 phpMyAdmin

Exercise 8-2 Installing Database in phpMyAdmin

1. Examine art.sql in a text editor. When done, close the file. These import scripts contain the necessary DDL statements to define the database tables as well as the INSERT statements to populate the tables with data.

2. In phpMyAdmin, click on the Databases tab.

3. Create a database called art.

   When it is complete, the art database will be visible in left-side of phpAdmin window.

4. Click on art database on left-side of window.

   Currently there are no tables in this database. You can manually create a new table here, or using the art.sql script to populate the database.

5. Click on the Import tab.

6. Use the Choose File button to select the art.sql file examined in step 1. Then click the Go button.

   If import works successfully, then you should be able to examine the tables in the database.

7. Repeat steps 3-6 for travels database (use travels.sql).

8. Repeat steps 3-6 for books database (use books.sql).

9. If everything worked correctly, your database will have new tables that are populated with data.

Exercise 8-3 Querying A Database

The following exercises assume that your databases have been created and populated. They also use phpMyAdmin to run the SQL commands. Depending on your installation, you may instead be using the command line.

1. In phpMyAdmin, click on the art database, then click on the SQL tab. You should see the message Run SQL query/queries on database art:

2. In the blank SQL window, enter the following.

    select * from artists; 

   Remember that SQL is not case sensitive. So while the SQL examples in the textbook uses uppercase for SQL reserved words, that is purely a convention for readability. You will likely find it easier to simply type the SQL in all lowercase (as shown here). Also, although using semicolon (;) at the end of SQL statement is optional, it is a good practice to use ; to end a SQL statement.

3. Now press the Go button.

   This will run the query. Notice that only the first 25 records are retrieved. This limit is appended to each query for performance reasons (you likely will not want all million records

in a given table for instance). If you wish to see all the records retrieved from a query, there is a Show All link at the bottom of the retrieved records.

4. Return to the SQL window, enter the following new query, and then press Go.

   select paintingid, title, yearofwork from paintings where yearofwork < 1600;

   This will display just the paintings prior to 1600. Notice that in MySQL, a query can be spread across multiple lines.

5. Modify the query (you can click the Show query box link) as follows and test. select paintingid, title, yearofwork from paintings

   where yearofwork < 1600 order by yearofwork;

6. Modify the query as follows and test.

   SELECT Artists.ArtistID, Title, YearOfWork, LastName FROM Artists INNER JOIN Paintings ON Artists.ArtistID = Paintings.ArtistID;

   This query contains a join since it queries information from two tables. Notice that you must preface ArtistId with the table name since both joined tables contain a field called ArtisId.

7. Modify the query as follows and test.

   SELECT Nationality, Count(ArtistID) AS NumArtists FROM Artists GROUP BY Nationality;

   This query contains an aggregate function as well as a grouping command.

   Note: The GROUP BY statement groups rows that have the same values into summary rows, like find the number of customers in each country. The statement is often used with aggregate functions such as COUNT(), MAX(), MIN(), SUM(), AVG() to group the result-set by one or more columns.

Exercise 8-4 Modifying Records

1. In phpMyAdmin, click on the art database, then click on the SQL tab.

   You should see the message Run SQL query/queries on database art:

2. In the blank SQL window, enter the following.

   insert into artists (firstname, lastname, nationality, yearofbirth, yearofdeath) values('Palma','Vecchio','Italy',1480,1528);

3. Press the Go button.

You should see message about one row being inserted.

4. Examine the just-inserted record by running the following query.

 select * from artists where lastname = 'Vecchio'; 

Notice that ArtistId value has been auto-generated by MySQL. This has happened because this key field has the auto-increment property set to true.

5. Run the following new query:

update artists set details='Palmo Vecchio was an Italian painter of the Venetian school';

6. Verify the record was updated (i.e, by running the query from step 4).

7. Run the following new query:

    delete from artists where lastname = 'Vecchio'; 

8. Verify the delete worked by running the following query: SELECT * FROM artists WHERE nationality = 'Italy';

   You may stop here or continue to steps 9-12 for building and index for a database

Extra Bit Building an Index

One of the key benefits of databases is that the data they store can be accessed by queries. This allows us to search a database for a particular pattern and have a resulting set of matching elements returned quickly. In large sets of data, searching for a particular record can take a long time. To speed retrieval times, a special data structure called an index is used. A database table can contain one or more indexes.

9. In phpMyAdmin, click on the art database, click on the paintings table, and then click on the Structure tab.

10. In the Structure page, click on the Indexes link. You will see a list of already-existing indexes. The import script for this database already has created indexes for the primary key, the foreign keys, and a two other fields.

11. Click the Go button in the section that begins Create an index ...

12. In the pop-up window (Add index), name the index YearOfWork, the index choice INDEX, and the column YearOfWork as shown in Figure 8.2. Click Go. There will be a short delay as MySQL creates the index.

Figure 8.2 Creating an index in phpMyAdmin