Managing Tables   «Prev 

COIN database - Exercise

Course Project: Create table script for the Coin Database

Objective:Create a script that will create the tables for the COIN database.

Exercise scoring

This exercise is worth a total of 24 points. Each of the six commands is worth 4 points. You will be graded on correctness and completeness of code.
Once you have completed your answer submit.

Background/overview

In the earlier courses in the Oracle DBA curriculum, you learned about the COIN database, which is the sample database used throughout this series of courses.
The structure of the COIN database is shown in the following diagram:

Coin database consisting of the following tables: 1) LOT, 2) COINS_IN_LOT, 3) COIN, 4) AUCTION, 5) BID, 6) CLIENT
Coin database consisting of tables: 1) LOT, 2) COINS_IN_LOT, 3) COIN, 4) AUCTION, 5) BID, 6) CLIENT

This exercise works with the COIN database (our course project) and tests your new skills in creating table structures.

Download files

You can download a correct version of the script for this exercise, createtable.sql, from the project download file found on the Resources page.

Instructions

Write a script that contains a series of CREATE TABLE commands to create the tables in the COIN database. Assign the appropriate datatypes, lengths, and sizes for the columns in each table.
Use the following guidelines to create the columns in each table:
  1. All ID columns are numeric with no scale.
  2. All cost amounts (prices, bids, amounts) are numeric with a scale of 2 and a precision of 11.
  3. All times use the DATETIME datatype.
  4. All phone numbers are stored as character strings.
  5. The winning_bidder column refers to the bidder_client_id, which refers to the client_id, so all of the columns should have identical data types.

Hints

You must use the CREATE TABLE syntax for each individual table. Separate individual CREATE TABLE statements with semicolons (;) in order to run the script properly in SQL*Plus. End your script with the command COMMIT; to make Oracle implement your commands.

Submitting your exercise

Enter your queries into the text box below.
Click the Submit button to submit your answers.
Remember that you must submit all your responses to this exercise at once by making use of the textbox below.