1 / 6

Building and Managing Tables in a Database: A Step-by-Step Tutorial

This tutorial guides you through the process of creating and managing database tables. Learn how to define column data types, alter table structures, insert rows, and establish primary and foreign keys. You'll work on practical exercises, such as building tables for Joe's Yard ERD and inserting customer and staff data. The tutorial includes tips on using the help command for data types, saving queries, and dropping existing tables before recreating them. By the end, you'll be equipped to efficiently manage a database and ensure data integrity.

verdi
Download Presentation

Building and Managing Tables in a Database: A Step-by-Step Tutorial

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Tutorial 4 Building a database

  2. 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

  3. 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.

  4. 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.

  5. 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.

  6. 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?

More Related