Share
Explore

Mastering Joins and Correlated Subqueries: A Comprehensive MySQL Lab Workbook

Introductory Lecture: Mastering Joins and Correlated Subqueries

Starting MySQL Server and MySQL Workbench
image.png
⁠
⁠

Introduction:
Before diving into our exercises, it's essential to ensure that our MySQL Server is up and running and that we have a tool to interact with it. MySQL Workbench is a unified visual tool that provides a comprehensive solution to manage and organize the MySQL database system. Let's walk through the steps to get both started.
⁠
Starting MySQL Server:
The method to start MySQL Server depends on your operating system:
Windows:
Navigate to the MySQL Server installation directory, typically C:\Program Files\MySQL\MySQL Server X.Y\bin.
Double-click on mysqld.exe to start the server.
OR
Press Windows + R, type services.msc, and press Enter.
In the Services window, locate 'MySQL'. Right-click and choose 'Start'.
macOS (using Homebrew):
Open Terminal.
Type brew services start mysql and press Enter.
Linux:
Open Terminal.
Depending on your installation, one of the following commands should start the server:
sudo service mysql start
sudo systemctl start mysql
sudo /etc/init.d/mysql start
⁠
Starting MySQL Workbench:
Windows:
Navigate to the folder where MySQL Workbench is installed or search for it using the search bar.
Double-click on the MySQLWorkbench icon.
macOS:
Open Finder.
Navigate to the Applications folder and find MySQLWorkbench.
Double-click on the MySQLWorkbench icon.
Linux:
Depending on your desktop environment, find MySQL Workbench in your applications menu or type mysql-workbench in the terminal and press Enter.
⁠
Connecting to the MySQL Server using MySQL Workbench:
Open MySQL Workbench.
On the welcome screen, you'll see a section titled "MySQL Connections". If you've previously set up a connection, you'll see it listed here.
To create a new connection:
Click on the + symbol next to "MySQL Connections".
Fill in the connection details:
Connection Name: Any name you prefer.
Hostname: Typically localhost if the server is on your machine.
Port: Default is 3306.
Username: Your MySQL username, often root for default installations.
Click Test Connection to ensure everything is set up correctly.
Click OK to save the connection.
To connect, click on the saved connection tile.
⁠
Conclusion:
With MySQL Server running and MySQL Workbench connected, you're now ready to dive into the world of databases, tables, and queries.
If you encounter any issues, remember to refer to the official MySQL documentation or seek assistance from online communities.

Happy querying!

⁠

Lab Notebook: MySQL Workbench Basics

⁠
Objective:
By the end of this lab, students will be able to:
Create a schema (database)
Create tables within that schema
Do some CRUD: Insert records into the table, and display the contents of the table using the SQL EDITOR PANEL of MySQL Workbench.
⁠
Tools Required:
MySQL Workbench (Ensure it's installed and connected to a MySQL server instance.)
⁠
Instructions:
⁠
1. Creating a Schema (Database):
1.1. Open MySQL Workbench and connect to your MySQL server instance by clicking on the desired connection.
1.2. Once connected, in the Navigator pane on the left, right-click in the "SCHEMAS" area.
1.3. Choose "Create Schema..." ​
image.png
⁠
⁠
1.4. In the "Name" field, enter the desired name for your schema (e.g., classlab).
1.5. Adjust other settings if necessary, but the defaults are typically sufficient for this exercise.
1.6. Click "Apply". Review the SQL statement that will be executed, then click "Apply" again.
CREATE SCHEMA `classlab` ;
1.7. Click "Finish". Your new schema should now appear in the "SCHEMAS" list.
⁠
2. Creating a Table:
2.1. In the "SCHEMAS" (which is the ‘databases’) pane, double-click on the schema you've just created (e.g., classlab) to set it as the default schema.
2.2. Right-click on the "Tables" folder within your schema and select "Create Table..."
2.3. In the "Table Name" field, enter the desired name (e.g., customers).
2.4. In the "Columns" tab, start defining your table columns by entering column names, data types, and other attributes. For our example:
customer_id: INT, PK (Primary Key), NN (Not Null), AI (Auto Increment)
first_name: VARCHAR(50), NN
last_name: VARCHAR(50), NN
... (Continue for other columns as per our previous discussions)
2.5. Once all columns are defined, click "Apply". Review the SQL statement, then click "Apply" again.
#DDL
CREATE TABLE `classlab`.`customers` ( `customer_id` INT NOT NULL, `customer_name` VARCHAR(45) NULL, PRIMARY KEY (`customer_id`)) COMMENT = 'customer data';
CREATE TABLE `classlab`.`orders` ( `order_id` INT NOT NULL, `order_date` DATE NOT NULL, `order_amount` INT NOT NULL, PRIMARY KEY (`order_id`) ) COMMENT = 'Order data';
CREATE TABLE `classlab`.`products` ( `product_id` INT NOT NULL, `product_name` varchar(50) NOT NULL, `price` INT NOT NULL, PRIMARY KEY (`product_id`) ) COMMENT = 'Products data';
CREATE TABLE `classlab`.`order_details` ( `order_id` INT NOT NULL, `product_id` INT NOT NULL, `quantity` INT NOT NULL, PRIMARY KEY (`order_id`) ) COMMENT = 'Relator Table connecting the Order Table and Product Table';
CREATE TABLE `monthly_targets`.`order_details` ( `month` INT NOT NULL, `target_amount` INT NOT NULL ) COMMENT = 'Relator Table connecting the Order Table and Product Table';
2.6. Click "Finish".
⁠
3. Inserting a Record into the Table:
3.1. In the "SCHEMAS" pane, expand your schema and the "Tables" folder, then right-click on your table (e.g., customers) and select "Insert Data...".
3.2. A grid will appear. Click into the cells to enter data for a new record. For our example:
Leave customer_id blank (it will auto-increment).
first_name: John
last_name: Doe
... (Continue for other columns)
3.3. Once you've entered all data for the record, click the floppy disk icon (or "Apply Changes") at the top-left of the grid to save.
⁠
4. Displaying Data from the Table:
4.1. In the main query editor (the large white space in the center), type the following SQL command:
SELECT * FROM customers;
4.2. Click the lightning bolt icon (or "Execute the selected portion of the script") to run the query.
4.3. The results will be displayed in the "Result Grid" below the query editor. You should see the record(s) you've inserted.
⁠
Conclusion:
You've successfully created a schema, designed a table, inserted a record, and queried data using MySQL Workbench. Practice these steps to become proficient in basic database operations.
Note: Always remember to save your work and ensure you are working in the correct schema to avoid any unintended changes or data loss.

Welcome to "Mastering Joins and Correlated Subqueries.

Learning how to join SQL Tables to create new knowledge from existing implicate knowledge in the tables:
Inner Join
Left / Right Join
Outer Join
Full Join
Today, we embark on an exciting journey into the heart of relational databases. We'll dive deep into the art of combining data from multiple tables and understanding the intricate relationships between them.
⁠
The Essence of Relational Databases:
At the core of every relational database is the concept of relationships.
Tables are not isolated islands of data; they are interconnected, and the true power of a relational database management system (RDBMS) like MySQL is realized when we can PREDICATE JOIN tables based on rows that are related to each other to create new knowledge: Doing this requires a deep insight with the Business Domain which that data was created in.
⁠
Why Joins?
⁠
⁠
⁠
⁠
image.png
⁠
⁠
Imagine you're reading a book, but the chapters are scattered in different volumes.
To understand the story, you'd need to piece them together.
Joins do precisely that for databases.
They "join" tables based on common columns, allowing us to see a more complete picture of our data.
⁠
Types of Joins:
INNER JOIN: The most common type, it returns rows when there's a match in both tables.
LEFT (OUTER) JOIN: Returns all rows from the left table and the matching rows from the right. If no match, the result is NULL from the right side.
RIGHT (OUTER) JOIN: The opposite of LEFT JOIN. It returns all rows from the right table and the matching rows from the left.
FULL (OUTER) JOIN: Think of it as a combination of LEFT and RIGHT JOINs. It returns rows when there's a match in one of the tables.
⁠
Correlated Subqueries:
While joins allow us to combine tables horizontally, subqueries let us combine data vertically, or even filter data based on complex conditions.
A correlated subquery is a subquery that references columns from the outer query. It's like having a conversation where what you say next depends on the response to your previous statement.
⁠
Real-world Use Cases:
E-commerce Platforms: When a customer views their order history, an INNER JOIN might be used to combine customer data, order data, and product data to provide a comprehensive view.
Inventory Management: A LEFT JOIN can help businesses identify products that have never been sold, aiding in inventory clearance decisions.
Employee Management Systems: Using RIGHT JOINs, companies can identify training programs that employees haven't yet attended.
Financial Analysis: Correlated subqueries can help in identifying departments that have exceeded their budgets or sales targets.
⁠
Workflow Implications:
In the workplace, mastering joins and subqueries can:
Enhance Efficiency: Reduce the need for multiple queries or manual data merging.
Improve Data Integrity: By working directly with relational data, there's less risk of errors from manual data manipulation.
Facilitate Complex Analysis: Enables deeper insights by allowing for intricate data relationships to be explored.
Optimize Performance: Properly executed joins and subqueries can be more performant than other makeshift data combination methods.
⁠

As we delve into this week's exercises, always remember the bigger picture. Behind every JOIN or subquery you write, there's a real-world problem you're solving, a question you're answering, or a decision you're facilitating.
Embrace the power of SQL and the relational model, and you'll find that the data in your hands can tell countless stories, answer myriad questions, and drive impactful decisions.

Task 1: Create these tables with the provided data: 2 ways to do this: CRUD SQL (dml) -- or -- Import facility of MySQL Workbench

Using the GUI in MySQL Workbench:

⁠
image.png
⁠
Below are the CSV representations for the lab evercise tables mentioned in the lesson plan.

#DML Create Records With Insert

insert into customers values (2, 'Jane Smith'); insert into customers values (3, 'Robert Brown'); insert into customers values (4, 'Linda Johnson');
1. customers.csv
customer_id,customer_name
1,John Doe
2,Jane Smith
3,Robert Brown
4,Linda Johnson

2. orders.csv
order_id,customer_id,order_date,order_amount
101,1,2023-01-15,100.50
102,2,2023-02-20,150.75
103,1,2023-03-10,80.25
104,3,2022-12-05,120.00
105,,2023-01-22,90.00

3. products.csv
product_id,product_name,price
201,Widget A,10.00
202,Widget B,15.00
203,Widget C,20.00
204,Widget D,25.00

4. order_details.csv
order_id,product_id,quantity
101,201,10
101,202,5
102,203,7
103,201,8
104,204,4

5. monthly_targets.csv
month,target_amount
1,500.00
2,600.00
3,550.00
4,580.00

Students can use these CSV files to import data into their MySQL databases. Depending on the tool or method they use, they might need to create the tables first or adjust the import settings to match the CSV format.
To import these CSV files into MySQL, they can use tools like phpMyAdmin, MySQL Workbench, or even command-line utilities. The exact steps will depend on the tool they choose.
#File_Import

How to import a CSV file into a MySQL database using MySQL Workbench. Here’s a step-by-step guide on how to do it:

Prepare the Table:
Before importing, ensure that the table you want to import the data into exists and its structure corresponds to the CSV Comma Separate Value file's structure.
If the table does not exist, create it with the appropriate columns and data types to match the CSV file.
Open MySQL Workbench:
Connect to the MySQL server where you want to import the CSV file.
Select the Database:
In the Navigator area, click on the schema you want to work with to select it.
Import the CSV File:
Go to the main menu and click on Table Data Import Wizard under the Server menu.
In the wizard that opens, select the CSV file you want to import.
Click Next and select the target schema (database) and the target table you want to import the data into. If the table doesn't exist, the wizard can create it for you based on the CSV structure.
Configure the import options, such as field terminators, line separators, and optionally, the encoding of the file.
Map the CSV columns to the table columns. You can also adjust data types and nullability here if the table is being created anew.
Review the summary of the import actions.
Click Next to begin the import process. MySQL Workbench will execute the necessary LOAD DATA INFILE command to import the CSV data into the MySQL table.
Monitor the Import:
The wizard will show the progress of the import and notify you once the import is complete.
If there are any errors, it will provide details so you can correct the issues and try again.
Verify the Data:
After the import, it's a good practice to verify the data by querying the table to ensure that all records have been imported correctly.
Note: The actual steps and options may vary slightly depending on the version of MySQL Workbench you are using. Always ensure that the CSV file format matches the expected format of your MySQL table. If there are discrepancies, you may need to preprocess the CSV file or adjust the table's structure accordingly.
Lesson Plan: Joins and Correlated Subqueries in MySQL
Objective: By the end of this lesson, students should be able to understand and implement various types of joins and correlated subqueries in MySQL.
Materials Needed:
MySQL Server installed on all student computers.
Sample database with multiple related tables.
Introduction (15 minutes):
Brief overview of the importance of combining data from multiple tables.
Introduction to the concept of joins and correlated subqueries.
Lecture (30 minutes):
Explanation of different types of joins:
INNER JOIN
LEFT JOIN (or LEFT OUTER JOIN)
RIGHT JOIN (or RIGHT OUTER JOIN)
FULL JOIN (or FULL OUTER JOIN)
Explanation of correlated subqueries and how they differ from regular subqueries.
Demonstration (30 minutes):
Live coding demonstration of each type of join using the sample database.
Live coding demonstration of a correlated subquery.
Student Lab Learning Guide:
Drill Exercises:
INNER JOIN:
Task: Retrieve all orders and their associated customer names.
Code:

SELECT orders.order_id, customers.customer_name FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;
LEFT JOIN:
Task: Retrieve all customers and their orders, including customers who haven't placed any orders.
Code:
sqlCopy code
SELECT customers.customer_name, orders.order_id FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id;
RIGHT JOIN:
Task: Retrieve all orders, including those without associated customers (though this might be a rare case).
Code:
sqlCopy code
SELECT customers.customer_name, orders.order_id FROM customers RIGHT JOIN orders ON customers.customer_id = orders.customer_id;
FULL JOIN (Note: MySQL doesn't support FULL JOIN directly, but it can be emulated):
Task: Retrieve all customers and all orders, including customers without orders and orders without customers.
Code:
sqlCopy code
SELECT customers.customer_name, orders.order_id FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id UNION SELECT customers.customer_name, orders.order_id FROM customers RIGHT JOIN orders ON customers.customer_id = orders.customer_id;
Correlated Subquery:
Task: Retrieve customers who have placed more than the average number of orders.
Code:
sqlCopy code
SELECT customer_name FROM customers c WHERE ( SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id ) > ( SELECT AVG(order_count) FROM ( SELECT COUNT(*) as order_count FROM orders GROUP BY customer_id ) as avg_orders );
INNER JOIN with multiple tables:
Task: Retrieve all orders, their customers, and the products ordered.
Code:
sqlCopy code
SELECT o.order_id, c.customer_name, p.product_name FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id INNER JOIN order_details od ON o.order_id = od.order_id INNER JOIN products p ON od.product_id = p.product_id;
LEFT JOIN with a filter:
Task: Retrieve all customers and any orders they placed in 2022.
Code:
sqlCopy code
SELECT c.customer_name, o.order_id FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id AND YEAR(o.order_date) = 2022;
Correlated Subquery with JOIN:
Task: Retrieve products that have never been ordered.
Code:
sqlCopy code
SELECT p.product_name FROM products p WHERE NOT EXISTS ( SELECT 1 FROM order_details od WHERE od.product_id = p.product_id );
Using RIGHT JOIN for a specific use case:
Task: Assuming there's a table of monthly targets, retrieve months where targets weren't met.
Code:
sqlCopy code
SELECT t.month, t.target_amount, SUM(o.order_amount) as total_orders FROM monthly_targets t RIGHT JOIN orders o ON MONTH(o.order_date) = t.month GROUP BY t.month, t.target_amount HAVING total_orders < t.target_amount OR total_orders IS NULL;
Complex Correlated Subquery:
Task: Retrieve customers whose last order was in 2022.
Code:
sqlCopy code
SELECT c.customer_name FROM customers c WHERE 2022 = ( SELECT YEAR(MAX(o.order_date)) FROM orders o WHERE o.customer_id = c.customer_id );
Wrap-up (15 minutes):
Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.