• Home
• Exercises
  • Chapter 1
  • Chapter 2
  • Chapter 3
  • Chapter 4
  • Chapter 5
  • Chapter 6
  • Chapter 7
  • Chapter 8
  • Chapter 9
  • Chapter 10
  • Chapter 11
  • Chapter 12
  • Chapter 13
  • Chapter 14
  • Chapter 15
  • Chapter 16
  • Chapter 17
  • Chapter 18
  • Chapter 19
  • Chapter 20
  • Chapter 21
  • Chapter 22
  • Chapter 23
  • Chapter 24
  • Reference 1
  • Reference 2
• Skill builders
  • Chapter 1
  • Chapter 2
  • Chapter 3
  • Chapter 4
  • Chapter 5
  • Chapter 6
  • Chapter 7
  • Chapter 8
  • Chapter 9
  • Chapter 10
  • Chapter 11
  • Chapter 12
  • Chapter 13
  • Chapter 14
  • Chapter 15
  • Chapter 16
  • Chapter 17
  • Chapter 18
  • Chapter 19
  • Chapter 20
  • Chapter 21
  • Chapter 22
  • Chapter 23
  • Chapter 24
• Slide exercises
  • Chapter 1
  • Chapter 2
  • Chapter 3
  • Chapter 4
  • Chapter 5
  • Chapter 6
  • Chapter 7
  • Chapter 8
  • Chapter 9
  • Chapter 10
  • Chapter 11
  • Chapter 12
  • Chapter 13
  • Chapter 14
  • Chapter 15
  • Chapter 16
  • Chapter 17
  • Chapter 18
  • Chapter 19
  • Chapter 20
  • Chapter 21
  • Chapter 22
  • Chapter 23
  • Chapter 24
• Support
  • Ask Rick
  • CD case
  • Classic Models
  • Glossary
  • Java
  • Labs
  • MySQL Workbench
  • Slides
  • SQL divide
  • SQL Playbook
  • Tables
  • XML
• Instructor
  • Contact
  • Manual

Data integration is typically defined as the standardization of data definitions and structures throughout an organization. Field and code definitions are standard. For example:

• salesdate always means the date the order was signed and is always stored in the format yyyy/mm/dd
• gender is always a one character field coded 'm' or 'f'

The goal of corporate data integration is to increase:

• the number of fields with common definitions and codes
• the number of databases that adhere to these standards

An integrated environment

Table 1 shows example data for a company that has integrated data across the Red and Blue divisions.

Table 1: Data integration

Exercise

Tables containing data for the Red and Blue divisions are shown below. Write an SQL query to report total unit sales for the corporation.

Blue Division

Red Division

SQL code

CREATE TABLE blue1 (
partnumber INTEGER,
unitssold INTEGER,
PRIMARY KEY(partnumber));

CREATE TABLE red1 (
partnumber INTEGER,
unitssold INTEGER,
PRIMARY KEY(partnumber));

INSERT INTO blue1 VALUES (1,45);
INSERT INTO blue1 VALUES (2,9);
INSERT INTO blue1 VALUES (4,7);
INSERT INTO blue1 VALUES (6,90);
INSERT INTO blue1 VALUES (7,12);
INSERT INTO blue1 VALUES (8,23);
INSERT INTO blue1 VALUES (9,12);
INSERT INTO blue1 VALUES (16,3);

INSERT INTO red1 VALUES (1,15);
INSERT INTO red1 VALUES (2,25);
INSERT INTO red1 VALUES (3,30);
INSERT INTO red1 VALUES (4,12);
INSERT INTO red1 VALUES (5,56);
INSERT INTO red1 VALUES (6,2);
INSERT INTO red1 VALUES (8,1);
INSERT INTO red1 VALUES (9,345);
INSERT INTO red1 VALUES (10,23);
INSERT INTO red1 VALUES (15,67);

A non-integrated environment

Table 1 shows example data for a company that has not integrated data across the Red and Blue divisions.

Exercise

Tables containing data for the Red and Blue divisions have been created. For each division, the table lists the partnumber, part description, and units sold. Write an SQL query to report total unit sales for the corporation. Hint: you might want to create a table that matches the codes, where you can, between the two tables.

Blue Division

Red Division

SQL code

CREATE TABLE blue2 (
partnumber INTEGER,
partdescription VARCHAR(20),
unitssold INTEGER,
PRIMARY KEY(partnumber));

CREATE TABLE red2 (
partnumber INTEGER,
partdescription VARCHAR(20),
unitssold INTEGER,
PRIMARY KEY(partnumber));

INSERT INTO blue2 VALUES (1,'widget - 5cm',45);
INSERT INTO blue2 VALUES (2,'widget - 10cm',9);
INSERT INTO blue2 VALUES (4,'widget - 15cm',7);
INSERT INTO blue2 VALUES (6,'widget - blue',90);
INSERT INTO blue2 VALUES (7,'widget - green',12);
INSERT INTO blue2 VALUES (8,'widget - yellow',23);
INSERT INTO blue2 VALUES (9,'widget - round',12);
INSERT INTO blue2 VALUES (16,'widget - plain',3);

INSERT INTO red2 VALUES (1001,'striped widget',15);
INSERT INTO red2 VALUES (1002,'checked widget',25);
INSERT INTO red2 VALUES (1003,'polka dot widget',30);
INSERT INTO red2 VALUES (1004,'plain widget',12);
INSERT INTO red2 VALUES (1005,'red widget',56);
INSERT INTO red2 VALUES (1006,'blue widget',2);
INSERT INTO red2 VALUES (1008,'yellow widget',1);
INSERT INTO red2 VALUES (1009,'5cm widget',345);
INSERT INTO red2 VALUES (1010,'10cm widget',23);
INSERT INTO red2 VALUES (1015,'triangular widget',67);

Note

• There is less work to write the query for an integrated environment than non-integrated
• The non-integrated example is a far from a worse case scenario. The purpose of the exercise is to give you an idea of how you would tackle such a problem.

Date revised: 02-Dec-2022