Lab #5 : Querying Data at Database Tutorial

Lab #5 : Querying Data

This lab works on developing the students understanding of the syntax to query data from tables through joins, where the tables are constrained by FOREIGN KEY database-level constraints.

Objectives ↓

These are the lab objectives. They provide the description of the database tables that you will work with to insert data. You have the table names, column names, and data types for each table, and the you should have a script to create these tables in Oracle and MySQL databases. The MySQL database script should create all tables by using the ENGINE=InnoDB clause at the end of the CREATE TABLE statement.

Group Objectives

Group assignments are cooperative situations where you should learn from one another. Each team member should engage and type solution components individually. This tends to maximize the student learning opportunity. You should avoid the “expert and followers” paradigm, and become cooperative and interactive problem solvers. The “expert and followers” paradigm is where one person in a group does the work and learns, while others look over the shoulder at the work without truly understanding it. Team members become interactive problem solvers when they collaborate and work together on solving problems.

If something comes up, like an illness or excused absence, you may share files. The caution here is that the student receiving the work must master the concepts in the material before submitting the work to the instructor. The instructor may interview the student to determine their level of mastery and post group credit that may differ from other team members who performed the work.

Learn how to use the SELECT statement in an Oracle database, which is a Data Manipulation Language (DML) command. Alternatively, in some sources it is called a Data Query Language (DQL) command. DML is the better choice and predominant usage.
Learn how to query data that meets the limitations of FOREIGN KEY constraints in an Oracle database.

Individual Objectives

Individual assignments are situations where you apply the generalized concepts acquired in the group exercise, and transfer your skills to another database engine. You may ask questions but you should only receive general concepts, principles, or directions to published materials (such as the product documentation found in the resources). Likewise, those are the types of answers you should provide when asked questions by your peers. You should not share files in this part of the lab.

Learn how to use the SELECT statement in a MySQL database, which is a Data Manipulation Language (DML) command. Alternatively, in some sources it is called a Data Query Language (DQL) command. DML is the better choice and predominant usage.
Learn how to query data that meets the limitations of FOREIGN KEY constraints in an Oracle database.

Resources ↓

These are the lab resources.

Single Table Query

Multiple Table Query

Deliverables ↓

The table definitions are in the lab deliverables. Write the nine joins, then review the table definitions, implementation notes, business rules, and system logic. You should run the create_oracle_store.sql and seed_oracle_store.sql scripts in Oracle, and the create_mysql_store.sql and seed_mysql_store.sql scripts in MySQL before running your query script. Your query script should return the data sets shown below.

[4 points] Write INNER JOIN queries that use the USING subclause and return the following results:

Display the MEMBER_ID and CONTACT_ID in the SELECT clause from a join of the MEMBER and CONTACT tables. You should make the join with the USING subclause based on the MEMBER_ID column, which is the primary and foreign key of the respective tables.

 MEMBER_ID CONTACT_ID
---------- ----------
      1001       1001
      1001       1002
      1002       1003
      1002       1004
      1003       1005
      1003       1006
      1003       1007
      1004       1008
      1005       1009
      1006       1010
      1007       1011
      1008       1012

Display the CONTACT_ID and ADDRESS_ID in the SELECT clause from a join of the CONTACT and ADDRESS tables. You should make the join between the tables with the USING subclause based on the CONTACT_ID column, which is the primary and foreign key of the respective tables.

CONTACT_ID ADDRESS_ID
---------- ----------
      1001       1001
      1002       1002
      1003       1003
      1004       1004
      1005       1005
      1006       1006
      1007       1007
      1008       1008
      1009       1009
      1010       1010
      1011       1011
      1012       1012

Display the ADDRESS_ID and STREET_ADDRESS_ID in the SELECT clause from a join of the ADDRESS and STREET_ADDRESS tables. You should make the join between the tables with the USING subclause based on the ADDRESS_ID column, which is the primary and foreign key of the respect tables.

ADDRESS_ID STREET_ADDRESS_ID
---------- -----------------
      1001              1001
      1002              1002
      1003              1003
      1004              1004
      1005              1005
      1006              1006
      1007              1007
      1008              1008
      1009              1009
      1010              1010
      1011              1011
      1012              1012

Display the CONTACT_ID and TELEPHONE_ID in the SELECT clause from a join of the CONTACT and TELEPHONE tables. You should make the join between the tables with the USING subclause based on the CONTACT_ID column, which is the primary and foreign key of the respect tables.

CONTACT_ID TELEPHONE_ID
---------- ------------
      1001         1001
      1002         1002
      1003         1003
      1004         1004
      1005         1005
      1006         1006
      1007         1007
      1008         1008
      1009         1009
      1010         1010
      1011         1011
      1012         1012

[2 points] Write INNER JOIN queries that use the ON subclause and return the following results:

Display the CONTACT_ID and SYSTEM_USER_ID columns in the SELECT clause from a join of the CONTACT and SYSTEM_USER tables. You should make the join with the ON subclause based on the CREATED_BY and SYSTEM_USER_ID columns, which are the foreign and primary key respectively.

CONTACT_ID SYSTEM_USER_ID
---------- --------------
      1001              2
      1002              2
      1003              2
      1004              2
      1005              2
      1006              2
      1007              2
      1008              1
      1009              1
      1010              1
      1011              1
      1012              1

Display the CONTACT_ID and SYSTEM_USER_ID columns in the SELECT clause from a join of the CONTACT and SYSTEM_USER tables. You should make the join with the ON subclause based on the LAST_UPDATED_BY and SYSTEM_USER_ID columns, which are the foreign and primary key respectively.

CONTACT_ID SYSTEM_USER_ID
---------- --------------
      1001              2
      1002              2
      1003              2
      1004              2
      1005              2
      1006              2
      1007              2
      1008              1
      1009              1
      1010              1
      1011              1
      1012              1

[2 points] Write INNER JOIN queries that use the ON subclause and return the following results:

Display the SYSTEM_USER_ID and CREATED_BY columns from one row, and the SYSTEM_USER_ID column from a row where it is also the primary key. You should make the join with the ON subclause based on the CREATED_BY and SYSTEM_USER_ID columns, which are the foreign and primary key respectively. In a self-join, these columns may be in the same or different rows in the table.

SYSTEM_USER_ID CREATED_BY SYSTEM_USER_ID
-------------- ---------- --------------
             1          1              1
             2          1              1
             3          1              1
             4          1              1

Display the SYSTEM_USER_ID and LAST_UPDATED_BY columns from one row, and the SYSTEM_USER_ID column from a row where it is also the primary key. You should make the join with the ON subclause based on the LAST_UPDATED_BY and SYSTEM_USER_ID columns, which are the foreign and primary key respectively. In a self-join, these columns may be in the same or different rows in the table.

SYSTEM_USER_ID LAST_UPDATED_BY SYSTEM_USER_ID
-------------- --------------- --------------
             1               1              1
             2               1              1
             3               1              1
             4               1              1

[2 points] Display the RENTAL_ID column from the RENTAL table, the RENTAL_ID and ITEM_ID from the RENTAL_ITEM table, and ITEM_ID column from the ITEM table. You should make a join from the RENTAL table to the RENTAL_ITEM table, and then the ITEM table. Join the tables based on their respective primary and foreign key values.

 RENTAL_ID  RENTAL_ID    ITEM_ID    ITEM_ID
---------- ---------- ---------- ----------
      1001       1001       1002       1002
      1001       1001       1004       1004
      1001       1001       1005       1005
      1002       1002       1016       1016
      1002       1002       1021       1021
      1003       1003       1019       1019
      1004       1004       1014       1014
      1005       1005       1007       1007
      1005       1005       1001       1001

Review the following table definitions and design information.

Table Name: MEMBER
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
MEMBER_ID	PRIMARY KEY			Integer	Maximum
MEMBER_TYPE	FOREIGN KEY	COMMON_LOOKUP	COMMON_LOOKUP_ID	Integer	Maximum
ACCOUNT_NUMBER				String	10
CREDIT_CARD_NUMBER				String	19
CREDIT_CARD_TYPE	FOREIGN KEY	COMMON_LOOKUP	COMMON_LOOKUP_ID	Integer	Maximum
CREATED_BY	FOREIGN KEY	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
CREATION_DATE	NOT NULL			DATE	Date
LAST_UPDATED_BY	FOREIGN KEY	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
LAST_UPDATE_DATE	NOT NULL			DATE	Date

Table Name: CONTACT
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
CONTACT_ID	PRIMARY KEY			Integer	Maximum
MEMBER_ID	FOREIGN KEY	MEMBER	MEMBER_ID	Integer	Maximum
CONTACT_TYPE	FOREIGN KEY	COMMON_LOOKUP	COMMON_LOOKUP_ID	Integer	Maximum
FIRST_NAME				String	20
MIDDLE_NAME				String	20
LAST_NAME				String	20
CREATED_BY	FOREIGN KEY	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
CREATION_DATE	NOT NULL			Date	Date
LAST_UPDATED_BY	FOREIGN KEY	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
LAST_UPDATE_DATE	NOT NULL			Date	Date

Table Name: ADDRESS
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
ADDRESS_ID	PRIMARY KEY			Integer	Maximum
CONTACT_ID	FOREIGN KEY	CONTACT	CONTACT_ID	Integer	Maximum
ADDRESS_TYPE	FOREIGN KEY	COMMON_LOOKUP	COMMON_LOOKUP_ID	Integer	Maximum
CITY	NOT NULL			String	30
STATE_PROVINCE	NOT NULL			String	30
POSTAL_CODE				String	20
CREATED_BY	FOREIGN KEY	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
CREATION_DATE	NOT NULL			Date	Date
LAST_UPDATED_BY	FOREIGN KEY	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
LAST_UPDATE_DATE	NOT NULL			Date	Date

Table Name: STREET_ADDRESS
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
STREET_ADDRESS_ID	PRIMARY KEY			Integer	Maximum
ADDRESS_ID	FOREIGN KEY	ADDRESS	ADDRESS_ID	Integer	Maximum
STREET_ADDRESS	NOT NULL			String	30
CREATED_BY	FOREIGN KEY	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
CREATION_DATE	NOT NULL			Date	Date
LAST_UPDATED_BY	FOREIGN KEY	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
LAST_UPDATE_DATE	NOT NULL			Date	Date

This step has been implemented as part of the seed_oracle_store.sql or the seed_mysql_store.sql script.

Implementation Note: The ADDRESS to TELEPHONE table relationship is unlike the constrained relationships and therefore requires that you use an outer join. Please read the business rule and system logic to understand the issues.

Business Rule: You may have a new customer establish a relationship by only providing a telephone number. This is an acceptable business practice provided you also secure the correct billing information.

System Logic: The TELEPHONE table contains an ADDRESS_ID column without a foreign key constraint. This isn’t an omission. This is the system modeling that is required by the business rule. The absence of a database-level constraint means a row in the TELEPHONE table may not have a relationship immediately to the ADDRESS table. This eliminates the need to enter an incorrect telephone number when one isn’t provided. The absence of the foreign key constraint makes the column optional, and that means the column has a 0..1 cardinality.

Table Name: TELEPHONE
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
TELEPHONE_ID	PRIMARY KEY			Integer	Maximum
CONTACT_ID	FOREIGN KEY	CONTACT	CONTACT_ID	Integer	Maximum
ADDRESS_ID	FOREIGN KEY	ADDRESS	ADDRESS_ID	Integer	Maximum
TELEPHONE_TYPE	FOREIGN KEY	COMMON_LOOKUP	COMMON_LOOKUP_ID	Integer	Maximum
COUNTRY_CODE	NOT NULL			String	3
AREA_CODE	NOT NULL			String	6
TELEPHONE_NUMBER				String	10
CREATED_BY	FOREIGN KEY	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
CREATION_DATE	NOT NULL			Date	Date
LAST_UPDATED_BY	FOREIGN KEY	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
LAST_UPDATE_DATE	NOT NULL			Date	Date

This step hasn’t been implemented as part of the seed_oracle_store.sql or the seed_mysql_store.sql script. You’ll need to perform this step as part of Lab #5. In Oracle, you’ll need to figure out the name of the constraint by referencing the

Implementation Note: The RENTAL table has a NOT NULL constraint on the RETURN_DATE column. You need to fix this because it violates a business rule. Please read the business rule and system logic to understand the issues.

Business Rule: You never want to enter the return date of a rental item because the customer may extends its return date for less money then the fee for returning it late. When nightly processing runs, it will check all rentals to see if they should be returned based on the 1-Day, 3-Day, or 5-Day rule. The rule is implemented to prevent clerks from extending rental dates after their due date without management override.

System Logic: The RENTAL table contains an RETURN_DATE column that must be left blank to ensure billing controls. This column should be defined as nullable. The absence of the not null constraint makes the column optional, and that means the column has a 0..1 cardinality.

You can discover the name of the NOT NULL constraint by using the following commands in Oracle. There's no constraint name for NOT NULL constraints in MySQL. You need to use the ALTER TABLE commands to drop NOT NULL constraints.

-- Set the long data type for display purposes to 500000.
SET LONG 500000
 
-- Define a session scope variable.
VARIABLE output CLOB
 
-- Query the table definition through the <code>DBMS_METADATA</code> package.
SELECT dbms_metadata.get_dll('TABLE','RENTAL') INTO :output FROM dual;

Table Name: RENTAL
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
RENTAL_ID	PRIMARY KEY			Integer	Maximum
CUSTOMER_ID	FOREIGN KEY	CONTACT	CONTACT_ID	Integer	Maximum
CHECK_OUT_DATE	NOT NULL			Date	Date
RETURN_DATE				Date	Date
CREATED_BY	FOREIGN KEY	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
CREATION_DATE	NOT NULL			Date	Date
LAST_UPDATED_BY	FOREIGN KEY	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
LAST_UPDATE_DATE	NOT NULL			Date	Date

Table Name: ITEM
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
ITEM_ID	PRIMARY KEY			Integer	Maximum
ITEM_BARCODE	NOT NULL			String	14
ITEM_TYPE	FOREIGN KEY	COMMON_LOOKUP	COMMON_LOOKUP_ID	Integer	Maximum
ITEM_TITLE	NOT NULL			String	60
ITEM_SUBTITLE				String	60
ITEM_RATING	ITEM_RELEASE_DATE			String	8
CREATED_BY	FOREIGN KEY	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
CREATION_DATE	NOT NULL			Date	Date
LAST_UPDATED_BY	FOREIGN KEY	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
LAST_UPDATE_DATE	NOT NULL			Date	Date

Table Name: RENTAL_ITEM
Column Name	Constraint			Data Type	Physical Size
Column Name	Type	Reference Table	Reference Column	Data Type	Physical Size
RENTAL_ITEM_ID	PRIMARY KEY			Integer	Maximum
RENTAL_ID	FOREIGN KEY	RENTAL	RENTAL_ID	Integer	Maimum
ITEM_ID	FOREIGN KEY	ITEM	ITEM_ID	Integer	Maximum
CREATED_BY	FOREIGN KEY	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
CREATION_DATE	NOT NULL			Date	Date
LAST_UPDATED_BY	FOREIGN KEY	SYSTEM_USER	SYSTEM_USER_ID	Integer	Maximum
LAST_UPDATE_DATE	NOT NULL			Date	Date

Group Deliverables

Groups should demonstrate to the instructor or lab assistant during class time that they have developed a single re-runnable (found here by clicking on the Re-runnable query script that queries data into temporary result sets based on matching of the values of PRIMARY KEY and FOEIGN KEY columns.

The query joins on the rows of the CONTACT table to the rows of the ADDRESS table where the values in the PRIMARY KEY column match the values in the FOREIGN KEY column. The PRIMARY KEY column of the CONTACT table is the CONTACT.CONTACT_ID column, and the FOREIGN KEY column of the ADDRESS table is the ADDRESS.CONTACT_ID column. This pattern can be derived from the table specifications, or from the sample scripts.

Examples of INNER JOIN queries using ANSI SQL: 89 and ANSI SQL: 92 syntax follow:

ANSI SQL: 89

SELECT   c.contact_id AS "Primary Key"
,        a.contact_id AS "Foreign Key"
FROM     contact c, address a
WHERE    c.contact_id = a.contact_id;

ANSI SQL: 92

A join using the ON subclause:

SELECT   c.contact_id AS "Primary Key"
,        a.contact_id AS "Foreign Key"
FROM     contact c INNER JOIN address a ON c.contact_id = a.contact_id;

A join using the USING subclause:

SELECT   contact_id AS "Primary or Foreign Key"
FROM     contact c INNER JOIN address a USING(contact_id);

Team members should also show the following:

Every team member should be capable of running and explaining elements of the script running against an Oracle database.
Every team member can confidently explain the how INNER and OUTER joins work.

Individual Deliverables

Individuals should demonstrate to the instructor or lab assistant during class time that you have developed a single re-runnable (found here by clicking on the Re-runnable query script that queries data into a temporary result sets based on the matching of values for the PRIMARY KEY and FOEIGN KEY columns.

Every team member should be capable of running and explaining elements of the script running against a MySQL database.
Every team member can confidently explain the how INNER and OUTER joins work.

Written by michaelmclaughlin

January 31st, 2010 at 3:56 pm

Posted in

4 Responses to 'Lab #5 : Querying Data'

Subscribe to comments with RSS or TrackBack to 'Lab #5 : Querying Data'.

Should the ADDRESS_ID column in the TELEPHONE table be a foreign key type? It references the address table, but it is blank in the type field.

Dane Christiansen

2 Feb 10 at 8:00 pm
Dane,

This is the note above the table: “The TELEPHONE table contains an ADDRESS_ID column without a foreign key constraint. This isn’t an omission. The business rule in this type of case is that you may have a telephone number or an address for customers. That means a row in the TELEPHONE table may not have a relationship immediately to the ADDRESS table. The absence of the constraint makes the column optional, which means it has a 0..1 cardinality.”

I think that it answers your question. It will require an outer join because it may be null.

michaelmclaughlin

2 Feb 10 at 8:49 pm
I think that in the individual objectives it is supposed to be MYSQL and not ORACLE.

Brandon Dye

19 May 10 at 5:13 pm
Yes, you’re right. It’s fixed now.

michaelmclaughlin

21 Jun 10 at 10:04 pm

Database Tutorial

Lab #5 : Querying Data

4 Responses to 'Lab #5 : Querying Data'

Leave a Reply

My Technical Blog

Search

Tutorial Pages

Blogroll