Tutorial 4 Building a database
Creating and Managing Tables • [ ]Create tables • [ ]Describe the data types that can be used when specifying column definition • Use the help command to find out the data types. • [ ]Alter table definitions • [ ]Insert rows into a table
Exercise 1 on create • Using the Joe’s Yard ERD, build the tables in your own database, without marking any field as a key field. • Remember to use the same data type for a field as a foreign key as was used in the table in which it is a primary key. • Alter the tables to add primary keys. • Alter the tables to add foreign keys. • Save the query.
Exercise 2 on create • Write a query to drop all of the tables you have set up. • Using creates, with primary and foreign keys, build the entire database for Joe’s Yard. • Run the drop query followed by the create query. • Save both queries.
The ‘insert’ command • Inserting data into a table • To add a row of data to a table INSERT INTO <table> VALUES (value1, value2, …valueN) • If the value list matches the column list exactly, there is no need to list the column names. • If the value list does not match the column list exactly, there is a need to list the column names. • E.g. insert into dog values ('JEFF','JEFFREY',20,4,'STANDARD','HALF-HOUR WALK','GDAN') or • insert into dog (DogId, Breed_Id) values ('JANE','GDAN') • Note: The other fields will be nulls.
Exercise on insert • Add the customers Andy Handy and John O’Connell to the customer table. Use suitable values for the other fields. • Add Mick Murphy and Joe Bloggs to the staff table as yard foremen. • Add docket numbers 196 and 201 to the docket table. • Populate the stock table with items from the sample dockets given. • What other tables need to be populated first?