Applications of MATLAB’s Database Toolbox
MATLAB’s Database Toolbox is a powerful tool for connecting to databases, retrieving, processing, and analyzing data. This section explores its functionality with examples and use cases.
Basic Operations in Database Toolbox
1. Connecting to a Database
To establish a connection, you can use the database function:
% Connecting to a database
conn = database('myDatabase', 'username', 'password', ...
'Vendor', 'MySQL', 'Server', 'localhost', 'PortNumber', 3306);
if isopen(conn)
disp('Connection successful!');
else
disp('Connection failed.');
end
2. Executing SQL Queries
Use the exec function to execute SQL queries on the database:
% Executing a query
query = 'SELECT * FROM employees WHERE department = "Sales"';
results = exec(conn, query);
results = fetch(results); % Fetch query results
data = results.Data;
disp(data);
Output: The fetched data will be displayed in the Command Window, representing the rows and columns retrieved.
3. Importing Data into MATLAB
Use the fetch function to import data into MATLAB as a table:
% Importing data into MATLAB
query = 'SELECT id, name, salary FROM employees';
data = fetch(conn, query);
tableData = cell2table(data.Data, 'VariableNames', data.ColumnNames);
disp(tableData);
Output:
id name salary
1 John 55000
2 Jane 62000
3 Alex 58000
4. Inserting Data into a Database
You can insert new data using the exec function:
% Inserting new data
query = 'INSERT INTO employees (id, name, department, salary) VALUES (4, "Chris", "HR", 50000)';
status = exec(conn, query);
if isempty(status.Message)
disp('Data inserted successfully!');
else
disp(['Error: ' status.Message]);
end
5. Updating Data in a Database
To update existing records, use the exec function:
% Updating data
query = 'UPDATE employees SET salary = 60000 WHERE id = 4';
status = exec(conn, query);
if isempty(status.Message)
disp('Data updated successfully!');
else
disp(['Error: ' status.Message]);
end
6. Deleting Data from a Database
To remove records, use the exec function with a DELETE statement:
% Deleting data
query = 'DELETE FROM employees WHERE id = 4';
status = exec(conn, query);
if isempty(status.Message)
disp('Data deleted successfully!');
else
disp(['Error: ' status.Message]);
end
Advanced Example Problems for MATLAB’s Database
Now you can explore more advanced scenarios and tasks using MATLAB’s Database Toolbox. These examples cover real-world applications and complex database interactions.
Example 1: Joining Tables and Aggregating Data
Suppose you have two tables: sales (columns: sale_id, product_id, quantity, sale_date) and products (columns: product_id, product_name, price). Write a query to calculate the total revenue for each product.
% Query to join tables and calculate revenue
query = ['SELECT p.product_name, SUM(s.quantity * p.price) AS total_revenue ' ...
'FROM sales s JOIN products p ON s.product_id = p.product_id ' ...
'GROUP BY p.product_name'];
results = fetch(conn, query);
disp(results.Data);
Output:
product_name total_revenue
WidgetA 15000
WidgetB 23000
GadgetC 17500
Example 2: Handling Large Datasets with Chunked Queries
For large datasets, fetching all rows at once might lead to memory issues. Process data in smaller chunks:
% Fetching data in chunks
query = 'SELECT * FROM large_table';
curs = exec(conn, query);
curs = fetch(curs, 100); % Fetch 100 rows at a time
while ~isempty(curs.Data)
disp(curs.Data);
curs = fetch(curs, 100); % Fetch the next 100 rows
end
Example 3: Parameterized Queries
Use parameterized queries to prevent SQL injection and dynamically filter results:
% Parameterized query
query = 'SELECT * FROM employees WHERE department = ?';
department = 'Engineering';
curs = exec(conn, query, {department});
data = fetch(curs);
disp(data.Data);
Example 4: Exporting Query Results to a File
Export fetched data into a CSV file for external use:
% Exporting query results to CSV
query = 'SELECT * FROM employees';
results = fetch(conn, query);
data = results.Data;
fileName = 'employees.csv';
writetable(cell2table(data), fileName);
disp(['Data exported to ' fileName]);
Example 5: Analyzing Time-Series Data
Retrieve and analyze time-series data from a database (e.g., sales over months):
% Query to fetch time-series data
query = 'SELECT sale_date, SUM(quantity) AS total_sales FROM sales GROUP BY sale_date';
results = fetch(conn, query);
data = cell2table(results.Data, 'VariableNames', {'SaleDate', 'TotalSales'});
% Plot the time-series data
data.SaleDate = datetime(data.SaleDate, 'InputFormat', 'yyyy-MM-dd');
plot(data.SaleDate, data.TotalSales);
title('Total Sales Over Time');
xlabel('Date');
ylabel('Total Sales');
Example 6: Detecting Anomalies in Data
Find employees with salaries significantly higher or lower than the average:
% Query to fetch salaries
query = 'SELECT id, name, salary FROM employees';
results = fetch(conn, query);
data = cell2table(results.Data, 'VariableNames', {'ID', 'Name', 'Salary'});
% Calculate anomalies
meanSalary = mean(data.Salary);
stdSalary = std(data.Salary);
anomalies = data(abs(data.Salary - meanSalary) > 2 * stdSalary, :);
disp('Anomalous Salaries:');
disp(anomalies);
Output:
Anomalous Salaries:
ID Name Salary
7 Mark 120000
15 Susan 25000
Example 7: Creating Custom Views in the Database
Create a database view for frequently used queries:
% Creating a custom view
query = ['CREATE VIEW high_salary_employees AS ' ...
'SELECT id, name, salary FROM employees WHERE salary > 100000'];
status = exec(conn, query);
if isempty(status.Message)
disp('View created successfully!');
else
disp(['Error: ' status.Message]);
end
Useful MATLAB Functions for Database Toolbox
Practice Questions
Test Yourself
1. Connect to a MySQL database and retrieve all rows from a “products” table.
2. Insert a new record into an “orders” table with relevant data.
3. Write a query to update the price of a specific product and fetch the updated table.
4. Delete records of employees whose salary is less than $40,000 and verify the results.
5. Write a query to calculate the monthly revenue for the last year and plot the results.
6. Create a parameterized query to retrieve employees hired within a specific date range.
7. Export the results of a join query into an Excel file using MATLAB’s writetable function.
8. Analyze customer purchase patterns by segmenting data into age groups and plotting their spending habits.