November 30, 2011 - Querying a database

For the final assignment in ARST5100, I needed to query my database. I anticipated this being a relatively simple assignment, as RPM had provided the query language and noted if it was not possible for us to save the query result to a .txt document, we could provide screenshots from MySQL.

After opening my virtual machine and MySQL, Is igned in as root (my standard MySQL sign-in) and double-clicked on catalog to query from the tables I had created. I entered the first query:

SELECT DISTINCT title FROM readings ORDER BY title INTO OUTFILE ' /tmp/task1.txt' ;

After hitting ctrl+enter, I received the following error message:

Message: Error Code: 1. Can’t create/write to file ‘/var/lib/mysql/ /tmp/task1.txt’ (Errcode: 2)

Looking closely at what the error message said and realizing that having permission to write to a directory was something RPM had discussed earlier in the semester. I decided to go back and review my notes on chmod and try to make /var/lib/mysql a writable directory.

I minimized MySQL and opened the terminal window. Using the command line, I checked the var file directory by using the following commands.

cd /var

ls –l

The list that appeared showed the lib directory did not have read/write access so I entered the following command:

sudo chmod g+wr lib (which worked after I was prompted for my password)

I then wanted to ensure /var/lib/mysql had read/write access so I entered the commands:

cd /var/lib

ls -l

And seeing the same issue again, I changed the mysql directly to have group read/write access.

sudo chmod g+wr mysql

Hopeful this would solve my error issue, I returned to MySQL and re-entered the first query:

SELECT DISTINCT title FROM readings ORDER BY title INTO OUTFILE ' /tmp/task1.txt' ;

I received a message stating 13 records had been affected, so I went to the text editor to see if the file I was supposed to create could be opened. The file task1.txt existed, so I saved it to the desktop for safe keeping.

I returned to MySQL and entered the second query:

SELECT DISTINCT apvalue FROM accesspoints ORDER BY apvalue
WHERE apname = 'author" INTO OUTFILE ' /tmp/task2.txt' ;

I consistently received the red dot indicating an error every time I tried to enter this query. I attempted to put the entire query into one line, and try each line of text separately, but neither returned the correct result. I then checked the MySQL reference manual and after A LOT of searching. I found a SELECT query which showed the ORDER BY command following the WHERE command, so I entered the query like this:

SELECT DISTINCT apvalue FROM acccesspoints
WHERE apname = 'author' ORDERY BY apvalue INTO OUTFILE ' /tmp/task2.txt' ;

This query successfully executed and I saved task2.txt to my desktop.

Going back to MySQL, I entered the third query, changing RPM's apname = 'course' to apname ' = 'form_genre' as I had not entered course information in my table:

SELECT DISTINCT accesspoints.apvalue, readings.title FROM accesspoints, readings
WHERE (accesspoints.readingsid _FK= readings.readingsid)
AND (accesspoints.apname = ‘form_genre’)
ORDER BY accesspoints.apvalue, readings.title
INTO OUTFILE ‘/tmp/task3.txt’ ;

I received an error message after entering this query, and decided to remove the _FK from the second line:
WHERE accesspoints.readingsid _FK= readings.readingsid)
because the foreign key designation was not part of the actual attribute name.

Upon doing that, the third query worked, and I saved task3.txt to my desktop.

Next I entered the fourth query:

SELECT DISTINCT accesspoints.apvalue, readings.title FROM accesspoints, readings
WHERE (accesspoints.readingsid = readings.readingsid)
AND (accesspoints.apname = ‘author’)
ORDER BY accesspoints.apvalue, readings.title
INTO OUTFILE ‘/tmp/task4.txt’ ;

This query worked without issue, and I saved task4.txt to my desktop.

Finally, I completed the fifth query:

SELECT apvalue, COUNT (*) FROM accesspoints
WHERE (apname = 'subject')
GROUP BY apvalue
INTO OUTFILE ' /tmp/task5.txt' ;

The final query worked, and I saved the last document, task5.txt to my desktop.

I shut down MySQL and ensured all of the tasks were saved to my desktop so I could access them at a later time.