The goal of this lab assignment is for you to gain experience with SQL programming by writing SQL queries for retrieving answers to questions about a database that is provided.

The database provided with this assignment holds information about the video game Final Fantasy IX published by Square Electronic Arts L.L.C. The information stored here represents the relationships between characters, equipment, and abilities. The characters are limited in which equipment they can wear and abilities they can learn. These relationships are stored in the ffix_can_wear table and the ffix_can_learn table. The way a character learns an ability is by equipping a particular piece of equipment. The ffix_teaches table tells which abilities are taught by which pieces of equipment. For each kind of equipment (say, hat), more than one item of this equipment may be currently available, and each such item is identified by a unique serial number. The ffix_inventory table tells which kinds of equipment are currently available by listing kinds of equipment and serial numbers of items .

• ffix_character(character_name char(10), level integer, hp integer, mp integer)
• ffix_kinds_of_equipment(equipment_name char(20), equipment_description varchar(255), type char(10))
• ffix_ability(ability_name char(20), ability_description varchar(255), type char(10), cost integer)
• ffix_inventory(serial_number integer, equipment_name char(20))
• ffix_can_learn(character_name char(10), ability_name char(20))
• ffix_can_wear(character_namechar(10), equipment_name char(20))
• ffix_teaches(equipment_name char(20), ability_name char(20))

• select ability_name from ffix_teaches where equipment_name='Rod';

This query lists all abilities that are taught by the Rod.

• select ffix_inventory.serial_number, ffix_inventory.equipment_name from ffix_inventory, ffix_can_wear where ffix_inventory.equipment name = ffix_can_wear.equipment_name and character_name='Steiner' and ffix_kinds_of_equipment.equipment_name = ffix_can_wear.equipment_name and ffix_kinds_of_equipment.type = 'Weapon';

The previous query finds the serial number and name of every weapon in inventory that can be used by Steiner.

1. Find the names of all kinds of equipment that Dagger can wear.
2. Find the serial numbers, names and descriptions of all equipment in inventory that Zidane can equip.
3. Find the names and costs of all abilities that Zidane can learn, but that Steiner cannot.
4. Find the names of all pairs of distinct abilities which are taught by the same kind of equipment
5. Find the names and descriptions of all abilities of type 'Active' whose cost is greater than 10 that can be learned from equipment in inventory. Do not list multiple occurences of the same ability.
6. Find the names of all characters who can learn an ability by wearing a kind of equipment with 'Hat' in the name
7. Find the names and descriptions of all current equipment which Zidane can wear, but which teaches an ability that Zidane cannot learn

For this assignment you will submit a file which contains each of your queries in the order of the questions. Also, please submit a log file which contains the results of running your queries against the database. Last, a README file should be included which indicates the names and descriptions ofthe files you have turned in. An example submit statement is

submit cmps180-pk.f02 lab2 README query.script query.log