ibm v4
7927  Reviews star_rate star_rate star_rate star_rate star_half

Relational Databases and SQL Fundamentals using Db2 V11.5 and Data Management Console

This course combines with the Db2 Family Fundamentals (CE031G) course, adding hands-on labs. The description here focuses on the lab portion of the class, which aligns with the lecture components of...

Read More
$2,505 USD
Course Code U5CE033G
Duration 3 days
Available Formats Classroom

This course combines with the Db2 Family Fundamentals (CE031G) course, adding hands-on labs. The description here focuses on the lab portion of the class, which aligns with the lecture components of CE031G. For a description of the lecture topics, please see the Db2 Family Fundamentals course description.

This course provides you with information about the functions of IBM's Db2, a Relational Database Management System which may be installed under a variety of operating systems on many hardware platforms.

Db2 runs under the z/OS, VM, Linux, UNIX, and Windows operating systems, to name a few.

The course includes discussions about Db2 services and related roles with hands-on labs. The focus is on the services Db2 provides and how we work with Db2 using SQL, not on its internal workings.

Who Can Benefit

This course is intended for System Testers, Data Analysts, Database Administrators, Programmers, Designers and System Programmers. This basic course is for persons needing an introductory knowledge of Db2 and Relational Database Management Systems, and anyone preparing for more advanced and specialized Db2 education.

Prerequisites

There are no prerequisites for this class. However, basic knowledge of data processing and SQL would be helpful.

Course Details

Course Outline

Understanding a Table

  • Create Company Directory Table
  • Select all rows from the Directory Table

Data Modelling and Database Design

  • Create Data Model and SQL to Create Tables
  • Create a SQL Statement to Obtain Definitions from the Catalog
  • Create Basic Select statements for Some Reports
  • Retrieve basic employee information including employee last name and firstname only.
  • Retrieve Basic Department Information
  • Select employee names with the name of the department they work for.
  • Create Primary Keys
  • Create new tables by issuing the following SQL statements:
  • Create a primary key for emp_temp on the empno column, and for the dept_temp table, on column deptno.
  • Try to add the employee rows to the emp_temp table again. What happens?
  • Create a Foreign Key
  • Define a parent-child relationship between the dept_temp table and emp_temp table
  • Test the Foreign Constraint
  • Create Triggers
  • Create New COMPANY_STATS table
  • Create Insert Trigger to Keep COMPANY_STATS Up To Date
  • Create Delete Trigger to Keep COMPANY_STATS Up To Date

How an End user uses Db2

  • Become familiar with the Data
  • Increase all the emp_temp salaries by $1000. Check the total salaries before and after.
  • Decrease all the emp_temp salaries by $2000 for department A00. Check the total salary for this department before and after.
  • Create a report with basic employee information from the employee table, sorting by last name and first name.
  • Modify the last report, adding the salary and bonus and name the new column "INCOME". Show only employees where salary + bonus > $50,000.
  • Create a report showing the employee(s) with the maximum salary.
  • Create a report showing the average salary for all employees.
  • Create a report showing the count of employees for each department.
  • For the last report, only show the departments with more than 4 employees, and give the second heading a more meaningful name, such as '#EMPLOYEES'.
  • Create a report showing the employee’s last name, concatenated with a comma and the first letter of their first name
  • Create and populate a table called CATALOG
  • Create a Report on the CATALOG1 table
  • Join Department and Employee tables
  • Optional: Use Db2 Interactive Mode and Execute a Script

How a Programmer uses Db2

  • Making changes with autocommit turned off
  • Use the Db2 Explain facility in Data Management Console to Explain your previous SQL Join
  • Select a column that does not exist from the employee table. What message is returned?
  • Create a stored procedure to select from the employee table

Tablespaces, Catalog Views and Security

  • Create a new tablespace, userspace2
  • Create a copy of the employee table in userspace2
  • Add Rows from One Table to another using Select
  • Use the Db2 catalog to find information about the employee table
  • Use the Db2 Catalog to find Information about Columns in the Employee Table
  • Connect to Db2 as another user and try to select from the employee table.

Locking, Check Constraints, Special Registers, Indexes, Views, Reorg, Runstats

  • Explore Locking
  • Explore Db2 Special Registers
  • Create an Index
  • Reorg and Runstats
  • Create a View
  • Select all rows from the view, sorting by employee lastname, then employee first name

Additional Course Delivery Option

  • For those requiring only a very basic introduction to SQL, there is an option to cover only the first three units, together with associated labs, leaving out following topics such as How a Programmer Uses Db2. This option is conducted over two days, instead of three.