O'Reilly logo

Beginning SQL by John W. Colby, Paul Wilton

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Appendix A. Exercise Answers

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)

 

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required