Binalatongan Community College
Brgy. Ilang San Carlos City, Pangasinan
IT Elec 3
Data
Mining
MODULE 5
HUMPHREY A. DIAZ
INSTRUCTOR
Binalatongan Community College
Brgy. Ilang San Carlos City, Pangasinan
Module No. 005
Subject Code : IT ELEC 3 – Data Mining
Subject Description : The aim of this course is to understand the basic concepts of data
mining and various techniques of data mining such as classification, association rules,
clustering, regression and its applications. This course also introduces overview of data
warehousing and its features.
Term : 1ST Sem 2025-2026
I. Learning Objectives:
- Demonstrate the mastery of the topics presented, knowledge and skills acquired by
performing within good expectations during the Midterm Examination.
- Apply the knowledge and skills earned, and display the knowledge gained during the
entire course by performing within good expectations in the Final Examination.
Upon completion of this module, the students will be able to:
- Define Data Preprocessing Phase
- Define Analytical Phase
- Identify The workflow of a typical data mining application
- Define Nondependency-Oriented Data
- Define Dependency-Oriented Data
- Understand Data Clustering
- Identify the Impact of Complex Data Types on Problem Definitions
- Define Data Mining definition and Task
- Differentiate KDD versus Data Mining
- Identify Data Mining techniques, tools and application
II. Learning Outcomes:
- Gain an understanding of what data mining is all about.
- Be able to perform the data preparation tasks and understand the implications.
- Demonstrate an understanding of the basic machine learning algorithmic
methods that support knowledge discovery.
- Be able to evaluate what has been learned through the application of the
appropriate statistics.
- Be able to discuss alternative data mining implementations and what might be
most appropriate for a given data mining task.
- Become proficient in the use of a set of data mining tools.
- Discuss various implementations of modern database management system;
- Use emerging database technologies
- Understand Advance SQL Queries.
III. Learning Resources:
1. Required Learning Resources
- Learning Modules, Learning Management System
2. Additional Learning Resources
- Data Mining: The Textbook, Charu C. Aggarwal, 2015 by Springer International
Publishing Switzerland
- Fundamentals of Database Systems 7th Edition, R. Elmasri and S. B. Navathe, 2016 by
Pearson
IV. Tasks to Complete:
- Complete the activities and quizzes included on the module by performing within good
expectations.
- Apply the knowledge and skills earned, and display the knowledge gained during the
entire course by performing within good expectations in Midterm and Final Examinations.
V. Content Items:
- Lesson 1: The MySQL Aliases
- Lesson 2: The MySQL Joins
VI. Summary:
- This module provides the fundamental principles that guides students to their learning.
- The module is the intellectually disciplined process of actively and skillfully conceptualizing,
applying, analyzing, synthesizing, and/or evaluating information gathered from, or generated
by, observation, experience, reflection, reasoning, or communication, as a guide to belief and
action.
VII. Assessments:
- To evaluate the students, the module provides some activities, exercises, self-checks and
quizzes. Examinations will be conducted at home.
MySQL Aliases
What are MySQL Aliases?
An alias is a temporary name assigned to a table or column for the duration of a query. It makes
queries more readable, especially when dealing with long column names or multiple tables in a
join.
Key Points
1. Aliases for Columns: Used to rename column headers in the result set.
2. Aliases for Tables: Used to assign shorter names to tables in complex queries.
1. Column Aliases
A column alias assigns a temporary name to a column in the result set. It is defined using the AS
keyword, though AS is optional.
Syntax:
SELECT column_name AS alias_name
FROM table_name;
Example
Retrieve the full name of students by combining first_name and last_name:
Query:
SELECT CONCAT(first_name, ' ', last_name) AS full_name, age
FROM students;
Output:
full_name age
John Doe 20
Jane Smith 22
Alice Johnson 21
Here, CONCAT(first_name, ' ', last_name) is given the alias full_name.
2. Table Aliases
A table alias is used to assign a temporary name to a table, often for simplifying queries involving
multiple tables.
Syntax:
SELECT column_name(s)
FROM table_name AS alias_name;
Example
List students and their enrolled courses using table aliases:
Query:
SELECT s.first_name, s.last_name, c.course_name
FROM students AS s
INNER JOIN enrollments AS e ON s.student_id = e.student_id
INNER JOIN courses AS c ON e.course_id = c.course_id;
Output:
first_name last_name course_name
John Doe Database Systems
John Doe Data Mining
Jane Smith Data Mining
Here:
• s is an alias for students.
• e is an alias for enrollments.
• c is an alias for courses.
3. Combining Column and Table Aliases
Aliases can be used for both columns and tables in the same query for better clarity.
Example
Retrieve a formatted list of students and their courses:
Query:
SELECT
s.first_name AS StudentFirstName,
s.last_name AS StudentLastName,
c.course_name AS EnrolledCourse
FROM students s
INNER JOIN enrollments e ON s.student_id = e.student_id
INNER JOIN courses c ON e.course_id = c.course_id;
Output:
StudentFirstName StudentLastName EnrolledCourse
John Doe Database Systems
John Doe Data Mining
Jane Smith Data Mining
4. Practical Uses of Aliases
• Improved Readability: Shorter names make complex queries easier to write and
understand.
• Formatting Output: Aliases allow you to customize the column names in the query result
for better presentation.
• Simplified Queries: Aliases simplify table references when joining multiple tables.
Example: Advanced Query with Aliases
Show the total number of students enrolled in each course.
Query:
SELECT c.course_name AS Course,
COUNT(e.student_id) AS EnrolledStudents
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.course_name;
Output:
Course EnrolledStudents
Database Systems 1
Data Mining 2
Networking 0
Summary of MySQL Aliases
1. Column Aliases: Temporarily rename columns for better readability and presentation.
2. Table Aliases: Shorten table names, especially in complex queries with joins.
3. Combined Aliases: Use both column and table aliases in the same query for optimal clarity.
By using aliases effectively, you can make your SQL queries more understandable and
maintainable.
MySQL Joins
Joins are used to retrieve data from two or more tables based on a related column. This allows you
to combine rows from these tables into a single dataset.
Sample Tables
1. students
student_id first_name last_name age
1 John Doe 20
2 Jane Smith 22
3 Alice Johnson 21
2. courses
course_id course_name credits
101 Database Systems 3
102 Data Mining 4
103 Networking 3
3. enrollments
enrollment_id student_id course_id semester
1 1 101 Fall 2024
2 2 102 Fall 2024
3 1 102 Spring 2024
1. INNER JOIN
The INNER JOIN returns rows that have matching values in both tables.
Query: Find students and the courses they are enrolled in.
SELECT students.first_name, students.last_name, courses.course_name
FROM students
INNER JOIN enrollments ON students.student_id = enrollments.student_id
INNER JOIN courses ON enrollments.course_id = courses.course_id;
Output:
first_name last_name course_name
John Doe Database Systems
John Doe Data Mining
Jane Smith Data Mining
2. LEFT JOIN
The LEFT JOIN returns all records from the left table and the matched records from the right table.
If there is no match, NULL values are returned for the right table's columns.
Query: List all students and their enrolled courses, if any.
SELECT students.first_name, students.last_name, courses.course_name
FROM students
LEFT JOIN enrollments ON students.student_id = enrollments.student_id
LEFT JOIN courses ON enrollments.course_id = courses.course_id;
Output:
first_name last_name course_name
John Doe Database Systems
John Doe Data Mining
Jane Smith Data Mining
Alice Johnson NULL
3. RIGHT JOIN
The RIGHT JOIN returns all records from the right table and the matched records from the left
table. If there is no match, NULL values are returned for the left table's columns.
Query: List all courses and the students enrolled in them, if any.
SELECT courses.course_name, students.first_name, students.last_name
FROM students
RIGHT JOIN enrollments ON students.student_id = enrollments.student_id
RIGHT JOIN courses ON enrollments.course_id = courses.course_id;
Output:
course_name first_name last_name
Database Systems John Doe
Data Mining John Doe
Data Mining Jane Smith
Networking NULL NULL
4. CROSS JOIN
The CROSS JOIN returns the Cartesian product of two tables. Each row in the first table is paired
with all rows in the second table.
Query: Combine all students and courses (not based on any relationship).
SELECT students.first_name, courses.course_name
FROM students
CROSS JOIN courses;
Output:
first_name course_name
John Database Systems
John Data Mining
John Networking
Jane Database Systems
Jane Data Mining
Jane Networking
Alice Database Systems
Alice Data Mining
Alice Networking
5. MySQL UNION
The UNION operator combines the result sets of two or more SELECT queries. Each SELECT query
must have the same number of columns and similar data types. Duplicate rows are removed unless
UNION ALL is used.
Query: List all enrolled students and all courses (distinct values).
SELECT first_name AS name
FROM students
UNION
SELECT course_name AS name
FROM courses;
Output:
name
John
Jane
Alice
Database Systems
Data Mining
Networking
Query: Use UNION ALL to include duplicates.
SELECT first_name AS name
FROM students
UNION ALL
SELECT course_name AS name
FROM courses;
Summary of Joins
• INNER JOIN: Only matching rows from both tables.
• LEFT JOIN: All rows from the left table, matching rows from the right.
• RIGHT JOIN: All rows from the right table, matching rows from the left.
• CROSS JOIN: Cartesian product of two tables.
• UNION: Combines results of two queries into a single result set.
By mastering these concepts, you can effectively retrieve and combine data across multiple tables
in a database!