Database Tutorial

Course Tutorial Site

Site Admin

CIT225 : Lab 6 Instructions

without comments

Lab #6: Oracle Assignment

Objectives

The lab is designed to teach you how to alter table structures, insert related data across two or more tables, and how to update data in a table. Specifically, you should learn the following:

  • Learn how to use the ALTER statement to add two new columns and add a foreign key constraint to an existing table.
  • Learn how to use the CREATE statement to create a new table with foreign key constraints.
  • Learn how to use the ALTER statement to modify a column’s name.
  • Learn how to couple several INSERT statements together into a transaction.
  • Learn how to use the UPDATE statement to change data.

Overview

The lab is designed to teach you how to alter the structure of an existing table, how to insert into data into a series of tables, and how to update existing data in a table.

ALTER Statement

The ALTER statement lets you:

  • Add new columns to an existing table.
  • Add new column constraints to an existing table.
  • Modify columns in an existing table.
  • Modify column constraints in an existing table.
  • Drop a column from an existing table.

CREATE Statement

The CREATE statement lets you:

  • Create a new table with columns and constraints.
  • Create a new sequence.

INSERT Statement

The INSERT statement lets you:

  • Insert a new row into a table.
  • Sequencing INSERT statements lets you manage primary and foreign key assignments.
  • Not sequencing INSERT statements compels you to use subqueries to lookup and copy appropriate primary key values

Transaction Control

Transaction Control Language (TCL) lets you build robust application software by extending control across multiple INSERT, UPDATE, and DELETE statements

  • A SAVEPOINT lets you mark a starting point, like a bookmark in a book.
  • A ROLLBACK lets you undo work to the beginning of a session, the last COMMIT statement, or to a SAVEPOINT.
  • A COMMIT lets you make adds, deletes, and changes permanent.

Help Section

The following is an article that qualifies SQL join concepts, logic, implementation, and inheritance tree:

The following web pages explain how to write cross, inner, natural, left, right, and full joins with ANSI SQL-89 and SQL-92 syntax. They explain the concept of set theory, rules surrounding joins, and syntax examples.

The lab has one part.

Lab Description

[50 points] Click the Lab Instructions link to open the instructions inside the current webpage.

Test Case

Click the Test Case Instructions link to open the test case instructions inside the current webpage.

Written by michaelmclaughlin

October 28th, 2017 at 9:51 pm

Posted in