Database Tutorial

Course Tutorial Site

Site Admin

CIT225 : Lab 5 Instructions

without comments

Lab #5: Oracle Assignment

Objectives

The lab is designed to teach you how join tables with equijoins and non-equijoin queries. Specifically, you should learn the following:

  • Write equijoins between tables with ANSI 1989 and ANSI 1992 syntax.
  • Write equijoins between two copies of the same table with ANSI 1989 and ANSI 1992 syntax.
  • Write non-equijoin queries against the result of cross joins with ANSI 1989 and ANSI 1992 syntax.
  • Write non-equijoin queries against the result of cross joins with ANSI 1989 and ANSI 1992 syntax.

Overview

The lab is designed to teach you how to write queries. Some queries join data based on the equality of values between rows, and others simply filter the product of cross joins.

Equijoins

Equijoins tie rows of tables together by matching primary and foreign key values. That means that equijoins work by checking the equality of two values. Equijoins only work when the database design supports:

  • A primary key column or set of columns that hold a list of unique values.
  • A foreign key column or set of columns that hold a copy of the single column or a set of columns that acts as another row’s primary key. That primary key can exist in another table or in the same table, and the latter case occurs with a recursive relationship.

Non-equijoins

Non-equijoins work by using a cross join. A cross join matches one row in one table with every row in another table, then it repeats the process by matching the next row in the first table with every row in the same table until it runs out of rows in the first table.

The non-equijoin then filters the rows returned from the cross join. Non-equijoins typically work with range comparison operation that:

  • Check whether one value is greater than or less than another value.
  • Check whether one value is between two other values.

Filtering inequality statements also work inside inner joins. All non-equijoin filters are inside the WHERE clause of a query. This lab will use only a non-equijoin inside a three table inner join statement.

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 8th, 2017 at 12:37 pm

Posted in