Beginning SQL

Appendix A. Exercise Answers

Chapter 1

Exercise 1 Solution

A separate table, which you can call the Location table, with a primary key field of integer data type should hold the meeting location details. The table should look like the table shown below:

Field Name	Data Type	Notes
LocationId	integer	Primary key
Location	varchar(200)

You need to alter the Attendance table so that instead of storing the whole address in the Location field for every record, the table stores the integer value from the Location table shown above. The new Attendance table looks like this:

Field Name	Data Type	Notes
MeetingDate	date	Change to datetime data type if using MS SQL Server
LocationId	integer	Foreign key
MemberAttended	char(1)
MemberId	integer	Foreign key linking to MemberDetails table

Exercise 2 Solution

The Location field is no longer needed and needs to be deleted. First, drop the Location field and add a LocationId field to the Attendance table. The SQL to do so is as follows:

ALTER TABLE Attendance
  DROP COLUMN Location;

ALTER TABLE Attendance
  ADD LocationId integer;

IBM's DB2 doesn't support the dropping of individual columns. One way around this is to drop the whole table and then re-create it with the new LocationId column:

DROP TABLE Attendance;
CREATE TABLE Attendance
(
    MeetingDate date,
    LocationId integer,
    MemberAttended char(1),
    MemberId integer
);

Now you need to create the Location table. The fields that compose the table are as follows:

Field Name	Data Type	Notes
LocationId	integer	Primary key
Street	varchar(100)