This article provides an overview of MySQL stored procedures and functions, explaining their syntax, creation, and invocation with examples. It also compares their characteristics, differences, and common use cases. Alexander S. Ricciardi May 24, 2025 MySQL supports stored routines (procedures and functions) (MySQL, n.d.). Stored routines are a set of SQL statements that are compiled and stored on the server database, allowing them to be accessed by users. This article provides an overview of MySQL stored procedures and functions, and compares and evaluates their characteristics, differences, and use cases.   Stored Procedures   Stored procedures can be defined as a set of SQL statements that can be invoked with the CALL keyword (Musgrave, 2024). Applications that have access to the database can CALL procedures stored on the database server. In other words, a stored procedure is a database object containing procedural SQL code that can be invoked at any time by an application or user to fetch and/or manipulate data. (Murach, 2019).   Syntax and creation of stored procedures: CREATE PROCEDURE procedure_name ( [parameter_name_1 data_type] [, parameter_name_2 data_type]... ) -- sql_block -- This represents the body of the procedure Example of creating/calling/dropping a procedure: The example provided is from “Chapter 15: How to Create Stored Procedures and Functions. Murach’s MySQL (3rd ed.)” By Murrah (2019). In this example, the procedure updates the credit_total column of the invoices table. -- Change the standard delimiter from semicolon (;) to double slash (//) -- Semicolons are used within the procedure body -- and we need a way to tell MySQL where the entire CREATE PROCEDURE statement ends -- therefore, we need to define a delimiter for the procedure itself DELIMITER // -- Create a new stored procedure named 'update_invoices_credit_total' -- This procedure takes two input parameters. CREATE PROCEDURE update_invoices_credit_total ( invoice_id_param INT, credit_total_param DECIMAL(9,2) ) -- Start of the procedure's body BEGIN -- Declare a local variable to track SQL errors. -- It's a TINYINT (often used for boolean flags) and defaults to FALSE (0). DECLARE sql_error TINYINT DEFAULT FALSE; SET sql_error = TRUE; -- Start a new transaction. This allows us to group SQL statements together START TRANSACTION; -- MySQL/SQL statements UPDATE invoices SET credit_total = credit_total_param WHERE invoice_id = invoice_id_param; -- Check if an SQL error occurred during the UPDATE statement IF sql_error = FALSE THEN COMMIT; ELSE ROLLBACK; END IF; -- End of the procedure's body END // -- Reset the delimiter back to the standard semicolon (;) DELIMITER ; Invoking Procedure: CALL update_invoices_credit_total(56, 504); Dropping the procedure: DROP PROCEDURE IF EXISTS update_invoices_credit_total; Stored Functions   Stored functions can be defined as a block of SQL code that can perform computations or transformations and return a single scalar value (200ok Solutions 2024). MySQL provides its own set of stored functions referred to as built-in functions, such as CONCAT() , NOW() , or SUM() . For a list of all the MySQL built-in functions, see: MySQL Functions  by W3 School (n.d.). Users can also create their own stored functions, which are referred to as User-defined functions (UDFs).   Syntax and creation of stored functions: CREATE FUNCTION function_name ( [parameter_name_1 data_type] [, parameter_name_2 data_type]... ) RETURNS data_type -- Specifies the data type of the value the function will return -- Indicates if the function always produces the same result for the same input parameters [NOT] DETERMINISTIC -- Specifies the nature of SQL usage within the function {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} -- sql_block -- This represents the body of the function Example of creating/calling/dropping a UDF function:The example provided is from “Chapter 15: How to Create Stored Procedures and Functions. Murach’s MySQL (3rd ed.)” By Murrah (2019). In this example, the function returns the vendor ID that matches a vendor’s name. -- Change the standard delimiter from semicolon (;) to double slash (//) -- This is necessary because semicolons are used within the function body -- and we need a way to tell MySQL where the entire CREATE FUNCTION statement ends DELIMITER // -- Create a new stored function named 'get_vendor_id'. CREATE FUNCTION get_vendor_id ( vendor_name_param VARCHAR(50) ) -- return an INT (integer) value. RETURNS INT -- DETERMINISTIC: Indicates that this function will always return the same result -- for the same input 'vendor_name_param', if data in the 'vendors' table doesn't change. DETERMINISTIC -- Indicates that the function contains SQL statements that read data READS SQL DATA -- start of the function body BEGIN DECLARE vendor_id_var INT; SELECT vendor_id INTO vendor_id_var FROM vendors WHERE vendor_name = vendor_name_param; -- Return the value stored in 'vendor_id_var' RETURN(vendor_id_var); -- end of the function's body END // -- Reset the delimiter back to the standard semicolon (;) DELIMITER ; Invoking Function: This was done in the same way MySQL functions are called within MySQL statements, often as part of an expression, for example, in a WHERE clause like. SELECT invoice_number, invoice_total FROM invoices WHERE vendor_id = get_vendor_id('IBM'); -- Invoke/call/uses the function Dropping the Function: DROP FUNCTION IF EXISTS get_vendor_id; Stored Procedures vs. Functions   The two stored routines are similar, but they differ in key aspects such as how they are called, what they can return, and how they interact with data. The table below provides a side-by-side comparison of their main differences.   Table 1 Stored Procedures and Functions Differences Note: The table provides a side-by-side comparison of the main differences between stored procedures and functions in MySQL.   Another aspect to consider is when to use one instead of the other; the choice depends heavily on the specific task's purpose. The table below provides a side-by-side comparison of the common scenarios or use cases for each routine. Table 2   When to Choose a Stored Procedure vs. a Stored UDF Function Note: The table provides a side-by-side comparison of common scenarios or use cases for stored procedure vs stored UDF function.   Below is an example of creating and using a simple procedure that gets all products belonging to a specific category, from the copy_my_guitar_shop schema (database). In this example, using a stored procedure rather than a stored function is better because the task involves more than just returning a single value; it returns to an application, for example, an entire result set (a table of data) consisting of multiple rows and columns. On the other hand, stored functions cannot return data tables, they can only return a single scalar value, making them unsuitable for this kind of task.   Figure 1 Example of a Simple Stored Procedure Note: The figure illustrates the MySQL code to create and use a simple stored Procedure that gets product information based on the given category.   Below is an example of creating and using a simple UDF function that calculates the final price of an order item after applying its discount, from the copy_my_guitar_shop schema (database). In this example, using a stored function rather than a stored procedure is better suited as the task performs a computation (calculating the final price) that returns a single value that can be used by the SELECT query, see code below. On the other hand, a stored procedure would be overkill for this task, as its underlying design introduces unnecessary overhead for this particular computation when compared to a stored function.   Figure 2 Example of a Simple UDF Function Note: The figure illustrates the MySQL code to create and use a simple stored UDF function that calculates the final price of an order item after applying its discount based on the given category.   Considering the routine differences and complexities as shown in Tables 1 and 2, and in  Figures 1 and 2. New database administrators may find stored UDF functions easier to develop. Because they return a single value and have limited data modification capabilities, their development can be more straightforward/simpler, and they are safer to use than stored procedures. In other words, their limited scope and syntax can feel familiar to developers with programming experience, and their limited scope and data modification capacity make them safer to use. Stored procedures, on the other hand, can quickly become extremely complex as they have a wider scope than stored functions. They can incorporate control flow, error handling, transaction management, and data modifications.  Consequently, for a new database administrator, stored procedures may be more challenging and have a steeper learning curve compared to stored UDF functions. As a new database administrator is best to start with functions and then gradually progress from procedures performing basic operations to procedures with more complex logic.   To summarize, stored procedures can be defined as a set of SQL statements that can be invoked using the CALL keyword. They are database objects containing procedural SQL code that can be invoked at any time by an application or user to fetch and/or manipulate data. On the other hand, a stored function is a block of SQL code that can perform computations or transformations and return a single scalar value MySQL provides its own set of stored functions, also called built-in functions, such as CONCAT() , NOW(), or SUM() . Users can create their own stored functions, called User-defined functions (UDFs). Although both stored procedures and user-defined functions provide code modularity, improved performance, and encapsulate business logic, their use cases differ. Stored procedures are used for complex data manipulation, transaction control, or when operations need to return multiple values or result sets; on the other hand, stored functions are used for calculations and transformations that return a single scalar value. Therefore, selecting the right routine for a specific task is critical for the performance and functionality of relational databases. References:   200ok Solutions (2024, September 20). MySQL stored procedures vs. functions: When and how to use them. 200ok Solutions. https://200oksolutions.com/blog/mysql-stored-procedures-vs-functions/   Murach, J. (2019). Chapter 15: How to create stored procedures and functions. Murach’s MySQL (3rd ed.) . Murach Books. ISBN: 9781943872367   Musgrave, Z. (2024, January 17). MySQL stored procedures: How and why with examples . DoltHub Blog. https://www.dolthub.com/blog/2024-01-17-writing-mysql-procedures/     MySQL (n.d.). 27.2 Using stored routines. MySQL 8.4 reference manual.   https://dev.mysql.com/doc/refman/8.4/en/stored-routines.html   W3 School (n.d.). MySQL functions. SQL reference . W3 School. https://www.w3schools.com/SQL/sql_ref_mysql.asp

This article explains how to use the GROUP BY  and HAVING  clauses in MySQL for data aggregation and details their syntax with examples. It also explores alternative methods for achieving similar data aggregation and summary, such as using correlated subqueries and window functions. Alexander S. Ricciardi May 14, 2025 Structured Query Language (SQL) is used within Relational Database Management Systems (RDBMS) to create and manipulate databases. MySQL is one of the various RDBMS frameworks that supports the GROUP BY  and HAVING clauses for data aggregation. Data aggregation, in the context of RDBMS, is the process of taking multiple rows of data and summarizing them into a single result row, based on certain stated conditions or criteria. The GROUP BY  and HAVING  clauses are used in conjunction with aggregate functions like COUNT() ,  SUM() , AVG() ,  MAX() , and MIN() . This post describes the syntax and usage of the GROUP BY  and HAVING clauses and explores alternative approaches to achieve similar data aggregation results using correlated subqueries and window functions. The GROUP BY Clause   The GROUP BY clause is used to partition identical data (records) into groups (DataCamp, n.d.a). It is often used in conjunction with aggregate functions that perform calculations on the groups created by the clause. For example: SELECT product_category, SUM(sale_amount) AS total_sales FROM sales GROUP BY product_category Output Example: Explanation of the Code:   1.  SELECT product_category, SUM(sale_amount) AS total_salesproduct_category product_category  column will be included in the results. SUM(sale_amount) AS total_sales : Aggregate function that calculates the sum of values in the sale_amount  column and assigns results to the column with the alias total_sales . 2.  FROM sales The table from which the data is retrieved. 3.  GROUP BY product_category Partition the records (rows) with identical product_category  values into groups. For example, if there are multiple sales records for "Electronics" ( product_category  ), all these records will form one group; all sales records for "Books" ( product_category  ) will form another group, and so on. Then the aggregate function SUM(sale_amount)  will compute the sum of the sale_amount  and the results are stored in the column total_sales ( AS total_sales ). The HAVING Clause   The HAVING  Clause is used to filter identical data partitioned by the GROUP BY  clause (DataCamp, n.d.b). Note that the clause after For example: SELECT product_category, SUM(sale_amount) AS total_sales FROM sales GROUP BY product_category HAVING SUM(sale_amount) > 200 Output Example: Explanation of the Code:   1.  SELECT product_category, SUM(sale_amount) AS total_salesproduct_category   product_category  column will be included in the results. SUM(sale_amount) AS total_sales : Aggregate function that calculates the sum of values in the sale_amount  column and assigns results to the column with the alias total_sales . 2.  FROM sales The table from which the data is retrieved. 3.  GROUP BY product_category Partition the records (rows) with identical product_category  values into groups. For example, if there are multiple sales records for "Electronics" ( product_category  ), all these records will form one group; all sales records for "Books" ( product_category  ) will form another group, and so on. Then the aggregate function SUM(sale_amount)  will compute the sum of the sale_amount  and the results are stored in the column total_sales ( AS total_sales ). 4.  HAVING SUM(sale_amount) > 200   The HAVING  clause filters the groups that were created by the GROUP BY  clause. The clause is applied after the grouping has occurred and the sum of the sale_amount has been computed for each group. SUM(sale_amount) > 200  is the condition. Only the groups (product_categories) with a total sum ( sale_amount ) greater than 200 will be included in the final result.   Rewriting Example   In this section, the provided example below using the GROUP BY  and HAVING clauses is rewritten by utilizing first correlated subqueries and then MySQL window functions to showcase approaches to achieve the same data aggregation, but with different methods.   On a side note: the ORDER BY  clause is a SQL command used to sort query results in either ascending or descending order based on one or more columns (Kartik, 2024). MySQL query statement utilizing GROUP BY clause in conjunction with aggregate functions often uses the ORDER BY  to arrange the aggregated results in a more meaningful way, for example, by ranking the groups based on their computed aggregate results (e.g., showing product categories with the highest SUM(sales) first, or customers with the COUNT(orders)  in descending order). Below is an example illustrating the combined use of the GROUP BY , HAVING , and ORDER BY  clauses. Here we want to identify and rank customers who have multiple orders by the number of orders they have made. SELECT customer_id, COUNT(order_id) AS total_orders FROM orders GROUP BY customer_id HAVING COUNT(order_id) > 1 ORDER BY total_orders DESC LIMIT 10; Output Example: Explanation of the Code: SELECT customer_id, COUNT(order_id) AS total_orders Selects the customer_id  column. COUNT(order_id)  counts the number of orders for each customer. AS total_orders  gives an alias to the calculated count column. FROM orders The table from which the data is retrieved. GROUP BY customer_id Partition the records (rows) with identical customer_id  values into groups. Then the aggregate function  COUNT(order_id)  will count the number of rows (records)  in the order_id column , including the row with NULL values. HAVING COUNT(order_id) > 1 The HAVING  clause filters the groups that were created by the GROUP BY clause. The clause is applied after grouping has occurred and the count of the total_orders  has been computed for each group. COUNT(order_id) > 1  is the condition. Only the groups ( total_orders ) with a total count superior then 1 will be included in the final result ORDER BY total_orders DESC Sorts the total_orders results in descending order. LIMIT 10 This limits the number of total_orders results output to a maximum of ten rows. Rewriting The Query Without the GROUP BY and HAVING Clauses   By Using Correlated Subqueries   It is possible to rewrite the last example without using the GROUP BY and HAVING clauses. For example, by using subqueries (subqueries, in MySQL, are queries embedded in other queries): SELECT customer_id, total_orders FROM ( -- Subquery substitute for the GROUP BY clause SELECT cust_with_orders.customer_id, ( -- Sub-subquery-2 counts the total number of orders for each distinct customer id SELECT COUNT(order_id) FROM orders WHERE customer_id = cust_with_orders.customer_id ) AS total_orders FROM ( -- Sub-subquery-1 queries all unique customer ids that exist in the orders table SELECT DISTINCT customer_id FROM orders ) AS cust_with_orders ) AS group_orders_by_customer_id -- output table WHERE -- substitute for the HAVE clause total_orders > 1 ORDER BY total_orders DESC LIMIT 10; Output Example: Explanation of the Code: ( For the sake of length, the code explanation is summarized)   Sub-subquery-1, a list ( cust_with_orders ) of all distinct customer ids from the orders table is created. Note that the created list is a table consisting of one column ( customer_id ). Then, sub-subquery-2, counts for each distinct customer id (cust_with_orders.customer_id ), their number of orders from the orders table is counted, and outputs a list of total orders ( total_orders ). Note that the output is a column. These results ( total_orders ) and their related customer ids (cust_with_orders.customer_id) are merged in a table with the alias   group_orders_by_customer_id . Note that the combination of the previous steps simulates the GROUP BY  clause functionality. Then the values of the group_orders_by_customer_id  are filtered using a WHERE clause with the condition total_orders > 1 . Note that the WHERE  clause is the substitute for the HAVE  clause.   Although the query using the  GROUP BY  and  HAVING  clauses can be written by using correlated subqueries is more verbose, making it more difficult to read, and is more often than not less performant for aggregation operations than the  GROUP BY  clause. Another alternative is to use window functions, which are supported by MySQL.   Using Window Functions to Rewrite the Query   Window functions can perform aggregations, rankings, distributions, and more without collapsing the entire result set down to a single row (PlanetScale, 2025). It is possible to rewrite the query example without using the  GROUP BY  and  HAVING  clauses. For example: -- Window function -- The function counts the number of orders per customer id in the order table -- it outputs a table where each original row (containing a customer_id) -- also contains the total number of orders for that specific customer_id WITH customer_order_counts AS ( SELECT customer_id, -- The 'orders' table's data is partitioned by customer_id -- and each occurrence of the customer_id is associated with -- the total number of orders for that specific customer_id -- computed by the COUNT(order_id) clause COUNT(order_id) OVER (PARTITION BY customer_id) AS total_orders FROM orders ) -- ------------------------------------------------------------------- -- This is the Main query SELECT DISTINCT customer_id, -- Only select distinct customer_id total_orders FROM customer_order_counts WHERE total_orders > 1 ORDER BY total_orders DESC LIMIT 10; Output Example: Explanation of the Code: (For the sake of length, the code explanation is summarized) The query first calculates the total number of orders for every customer ids from the orders table and attaches the respective totals to each occurrent customer_id using the customer_order_counts window function. Not that this function creates duplicate rows Then, it filters distinct customer ids ( from the customer_order_counts table ) with their total order counts, then filters out customers who have only made 1 order, sorts the remaining customers by their total order count (highest first), and finally, it only returns the top 10 row results. Similarly to using the correlated subqueries, the query can be rewritten using window functions. The query is more verbose than the one using the GROUP BY clause, making it less readable and concise. It is also less performant, as it computes the total order count for every individual row of the orders table, creating duplicates. Window functions should be used in scenarios like calculating per-row values like ranks, running totals, or lead/lag comparisons, and where keeping the individual row context is needed. To summarize, the GROUP BY and HAVING clauses in MySQL are essential for data aggregation tasks. They are often used in conjunction with aggregate functions like SUM() and COUNT() , allowing for powerful data summarization. Although it is possible to summarize data through aggregation without using the GROUP BY  and HAVING   clauses by utilizing correlated queries or window functions. However, these alternative methods often result in more verbose queries and less performance, making GROUP BY and HAVING generally the more direct, concise, and often more efficient for standard aggregation tasks. References:   DataCamp (n.d.a). MySQL GROUP BY clause.  DataCamp. https://www.datacamp.com/doc/mysql/mysql-group-by   DataCamp (n.d.b). MySQL HAVING clause.  DataCamp. https://www.datacamp.com/doc/mysql/mysql-having   PlanetScale (2025). 3.15 Window functions. MySQL for developers . PlanetScale. https://planetscale.com/learn/courses/mysql-for-developers/queries/window-functions?autoplay=1

This article outlines best practices for secure data and schema manipulation in MySQL using DML and DDL commands. These best practices include applying the Principle of Least Privilege for permissions, utilizing MySQL features like SQL_SAFE_UPDATES and transactions for DML, and implementing planning and backups for DDL changes. Alexander S. Ricciardi May 8, 2025 Structured Query Language (SQL) is a standard language that is used within Database Management System (RDBMS) to create and manipulate databases and the data they store. Within SQL, manipulating records (data values) is handled by Data Manipulation Language (DML), a subset of SQL. The manipulation of the databases themselves, also called schemas, is handled by the Data Definition Language (DDL). This article explores MySQL best practices for handling data manipulation safely when using DML and DDL operations.   MySQL and the Principle of Least Privilege   MySQL, an RDBMS based on SQL, provides a privilege system that manages applications’ and users' access to database servers, including the ability to drop (delete) databases, modify any data (records), and change user permissions. This system is based on the Principle of Least Privilege (PoLP) where users and applications are granted only the exact permissions required to perform their intended tasks, and no more. These permissions are managed by a root  account, which is usually used only for that purpose. The permissions are based on the users/applications' scopes and needed functionality; additionally, these permissions are not static or a one-time based configuration, as the permissions should be revoked or modified as soon as they are no longer needed or the users/applications' scopes change.   Permission Setting and Description   In MySQL, the permissions are set using the GRANT statement, which is used to assign (grant) privileges, operations allowed, and the scope (such as global, database, table, or column) to the target user. The following tables showcase various permissions for DML and DDL operations.   Table 1 MySQL User Permissions for DML Operations Note: The table lists the MySQL permission scopes for DML operations, describes them, and what actions the user is permitted to do. Table 2 MySQL User Permissions for DDL Operations Note:  The table lists the MySQL permission scopes for DDL operations, describes them, and what actions the user is permitted to do.   MySQL Best Practices for Manipulating Records   As shown in Table 1, to insert new records, update or modify existing records, and delete records, DML provides three core commands: INSERT, UPDATE, and DELETE. These commands are powerful tools that need to be used with meticulous care, as they can result in accidental and permanent record/data loss or corruption. In addition to managing permissions, MySQL provides commands such as SQL_SAFE_UPDATES and START TRANSACTION that add a layer of protection against accidental data modifications or loss, as they allow for some control and to reverse changes, notably when using the command UPDATE and DELETE.   The SQL_SAFE_UPDATES is a server system variable that helps prevent accidental mass updates or deletions. When enabled ( SET SQL_SAFE_UPDATES = 1; ), MySQL will reject UPDATE and DELETE  statements unless they include a WHERE  clause (selecting specifies rows using). Note that if you are using MySQL Workbench, you can disable Safe Mode by going to: Edit -> Preferences, selecting the SQL Editor tab, and unchecking the "Safe Updates" box.   The  START TRANSACTION  command allows multiple SQL statements to be treated as a single, atomic unit of work, a module, providing the option to rollback any modifications (done by the SQL statements that are part of the transaction module) before permanently committing them. The MySQL documentation provides the following syntax for it: START TRANSACTION [transaction_characteristic [, transaction_characteristic] ...] transaction_characteristic: { WITH CONSISTENT SNAPSHOT | READ WRITE | READ ONLY } BEGIN [WORK] COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] SET autocommit = {0 | 1} Note: from “15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements” by MySQL Documentation (n.d.) START TRANSACTION or BEGIN starts a new transaction. COMMIT commits the current transaction, making its changes permanent. ROLLBACK rolls back the current transaction, canceling its changes. SET autocommit disables or enables the default autocommit mode for the current session. Note that by default autocommit mode is turned ON ( SET autocommit = 1 ), and, SQL statement you execute that modifies data (like INSERT , UPDATE , DELETE ) is treated as its own independent transaction, meaning that when the statement finishes successfully, MySQL automatically performs a COMMIT -> the changes are immediately saved and you cannot use ROLLBACK to undo. So it is essential to explicitly set autocommit = 0. That is, if you are planning to use ROLLBACK . Here is an example: -- Step 0: Verify the row that need to be change SELECT product_id, product_name, discount_percent FROM products WHERE category_id = 1; -- Step 1: Start the transaction START TRANSACTION; -- Step 2: Apply temporary changes UPDATE products SET discount_percent = 50.00 WHERE category_id = 1; -- Step 3: Check the temporary change within the transaction SELECT product_id, product_name, discount_percent FROM products WHERE category_id = 1; -- Step 4: Decide not to proceed and undo the changes ROLLBACK; -- Step 5: Verify that the changes were undone SELECT product_id, product_name, discount_percent FROM products WHERE category_id = 1; MySQL Best Practices for Manipulating Schema Data   Similar to DML, DDL provides three core commands to manipulate database schemas and tables: CREATE , ALTER , and DROP . These commands are even more powerful than DML commands, and they need to be used with meticulous care, as they can result in accidental and permanent vast data loss. Some best practices are in addition to managing permissions, follow best practices such as planning the change, creating backups, and decoupling (separating the schema change from the application deployment or release process).   To summarize, manipulating data improperly within RDBNS can result in accidental loss of data that can be significant to catastrophic. To mitigate these risks, MySQL provides a permissions framework and tools (e.g., GRANT ), based on the Principle of Least Privilege (PoLP), that, when combined with best practice such as using transactions (including START TRANSACTION with ROLLBACK ) and safe update modes (like SQL_SAFE_UPDATES ) for DML operations, alongside planning, creating backups, and decoupling changes for DDL operations—greatly reduces the risk of accidental data loss or corruption. References:   MySQL Documentation (n.d.). 15.3.1 START TRANSACTION, COMMIT, and ROLLBACK statement. MySQL. https://dev.mysql.com/doc/refman/8.4/en/commit.html

This article explores MySQL data types, like integers, decimals, strings, and dates, used to define the information stored in table columns. Selecting the correct data type is crucial for ensuring data integrity, optimizing storage and performance, and performing calculations and comparisons. Alexander S. Ricciardi April 29, 2025 In relational databases, information is organized in tables referred to as schemas, the table rows act as records, and the columns act as attributes. To manage and manipulate these tables, developers use Structured Query Language (SQL), with MySQL being the most popular relational database management system (RDBMS). In the context of MySQL, selecting the appropriate data types for table columns, in other words, the data attributes, is essential for the RDBMS and the applications relying on it, as it directly impacts data integrity, storage efficiency, query performance, and the accuracy of data operations like calculations and comparisons. This article explores the data types available in MySQL, discusses their uses, and illustrates the importance of choosing the correct type. MySQL Data Types In the context of MySQL, data types define the nature of the information that can be accepted and stored within each specific column of a table. MySQL provides a wide range of data types, each designed to fit a specific kind of data characteristics, such as storage space and allowed values. See the table below for more information. Table 1 MySQL Data Type Note:  The table lists the different MySQL. Data from “An introduction to MySQL data types” by Ellingwood (n.d.). The Importance of Data Type Selection   Selecting the right data type in RDBMS is very important as it ensures that the data is stored efficiently and accurately (Sakshijain1, 2025). It impacts several aspects of RDBMS functionality, such as data integrity, storage, and query, as well as operations such as SUM  and AVG  calculation performed on numeric types, or DATEDIFF and DATE_ADD  operations performed on temporal data types.   The following examples illustrate the impact of selecting appropriate data types versus inappropriate ones.   MySQL script below creates an events table to store concert dates: CREATE TABLE Events ( event_id INT AUTO_INCREMENT PRIMARY KEY, event_name VARCHAR(100), event_start DATETIME, -- Using DATETIME type ticket_price DECIMAL(8, 2) -- Using DECIMAL for currency ); INSERT INTO Events (event_name, event_start, ticket_price) VALUES ('Spring Gala', '2025-05-15 19:00:00', 75.50), ('Summer Concert', '2025-07-20 20:30:00', 45.00), ('Autumn Workshop', '2025-10-10 09:00:00', 120.00); Note that the column event_start was defined having a DATETIME data type, which allows performing temporal operations, for example, finding events happening in the next 90 days. The following code snippet queries/retrieves the name and start date/time for all events listed in the Events table where the events are scheduled to start on or after today's date and on or before the date 90 days from today. This logic works because the event_start column was defined as a DATETIME data type. SELECT event_name, event_start FROM Events WHERE event_start BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 90 DAY); Another example of properly using data types is defining DECIMAL(8, 2) for ticket_price. This allows calculating the average ticket price without potential floating-point rounding errors: SELECT AVG(ticket_price) FROM Events; Now, if event_start  were stored as  VARCHAR(20)  (e.g., 'May 15, 2025 7PM'), using a function similar to DATE_ADD  would be very difficult and prone to errors due to the text formats. Similarly, storing ticket_price  as VARCHAR (e.g., ‘$75.50’, ‘$40.25’) and when using functions like AVG()  or SUM() . Although MySQL implicitly converts the VARCHAR to the DOUBLE  type, the output of the conversion is not always what is expected, as AVG(ticket_price)  (calculating ticket price average) will compute as 0.0 . This happens because MySQL will read the first character for the values, ' $ ' (which is not a number), and interpret the entire expression as not being numeric and translate VARCHAR  data type values to the DOUBLE data type value of 0.0 .   For example, when using a column: score_text VARCHAR(20), Storing the folowing data: ‘100' 'p85' 'Failed' '90 points' '70' and performing the following operation on the column (Computing scores average): SELECT AVG(score_text) The MYSQL code line above will first convert the VARCHAR data to DOUBLE data, resulting on the following: 100.0 -- ‘100’ 0.0 -- ‘p85’ 0.0 -- ‘Failed’ 90.0 --’90 Points’ 70.0 -- ‘70’ Note that MySQL will generate warnings for the rows containing 'Failed'  and '90 points' . And then it will performed the following calculation ( 100.0 + 0.0 + 0.0 + 90.0 + 70.0) / 5 = 345.0 / 5 = 52.0   This average output is probably not what was expected, or it is meaningful, because the non-numeric value 'Failed' was converted to 0 , lowering the calculated average compared to the average of only the actual numerical scores.   Another example, in scientific measurements where precision is crucial, DECIMAL is preferred over approximate numeric data types like FLOAT . Let’s say we add reagents with masses like 1.1250g, 0.0750g, and 2.5000g. When using the FLOAT data type to compute ExperimentComponents_Approx, these values will be stored as close approximations, not the exact numbers, as 1.1250 , 0.0750 , and 2.5000 are defined as FLOAT ; each value is rounded to the nearest representable binary fraction. Then, when calculating the total mass: SELECT SUM(mass_grams) FROM ExperimentComponents_Approx; The computation output will be slightly off due to the “accumulated representation errors” (binary rounding error), resulting in 3.7000000000000002 or 3.6999999999999997 instead of exactly 3.7000 . For instance, in laboratories or manufacturing quality control settings, this discrepancy could result in incorrect solution concentrations, failed reactions, or items not meeting specifications.   However, when using DECIMAL(10, 4) in ExperimentComponents_Exact guarantees that 1.1250 , 0.0750 , and 2.5000 are stored precisely, as the computations are performed in base-10 with no hidden representation error. Then, when calculating the sum: SELECT SUM(mass_grams) FROM ExperimentComponents_Exact; This computation outputs an exact result of 3.7000 with an exact decimal precision of 4. This precision and accuracy in scientific calculations are essential for meeting a correct product quality standard or scientific specification.   Note that the numeric DOUBLE data type (8 bytes) outperforms the FLOAT data type (4 bytes) by offering roughly twice the bit-width, about 15 to 17 decimal digits versus 7 for the FLOAT . However, the DOUBLE type still has some inexact precision when compared to the DECIMAL type, which has exact precision.   Therefore: Use DECIMAL whenever exact decimal precision is needed (e.g., important financial calculations with a high volume of transactions or calculations, quality-control measurements, laboratory concentrations). Use DOUBLE when precision matters, but it does not have to be exact. Use FLOAT when a slight rounding error is acceptable.   In summary, MySQL provides a set of data types that can be used to store various kinds of information, from numbers and text to dates and spatial data. Selecting the most appropriate data type is crucial, as it significantly impacts data integrity, storage usage, query speed, and the accuracy of operations. References:   Ellingwood, J. (n.d.). An introduction to MySQL data types . https://www.prisma.io/dataguide/mysql/introduction-to-data-types   Sakshijain1 (2025, January 30). SQL data types. GeeksForGeeks . https://www.geeksforgeeks.org/sql-data-types/

This article explains how to manipulate data using MySQL tables and the Data Manipulation Language (DML) commands: INSERT to add new rows, UPDATE to modify existing rows, and DELETE to remove rows, providing syntax and illustrated examples for each. Alexander S. Ricciardi April 30, 2025 One of the core functions of a Relational Database Management System (RDBMS) is to allow users to manipulate data, such as inserting data into a table, modifying that data, and then deleting whatever data they no longer want to store. These RDBMS functions are handled by the Data Manipulation Language (DML), a subset of the Structured Query Language (SQL), and implementations of SQL like MySQL (Murach, 2019). Note that the Data Definition Language (DDL) is also a subset of SQL, which handles the definition or modification of the structure of database objects (schemas) like tables and indexes. In other words, DML is used to manipulate the data within schemas, and DDL is used to manipulate the schemas. This article focuses on the DML operations ( INSERT , UPDATE , and DELETE ) used to manipulate data within MySQL tables, providing examples. DML Operations   The DML operations for modifying data in MySQL tables are handled by the statements (also referred to as commands): INSERT , UPDATE , and DELETE . The INSERT  command adds new rows of data. The UPDATE  command modifies data in existing rows. The DELETE  command removes rows entirely. Note that these commands manipulate rows, not columns. However, the command SELECT  is used for querying and retrieving data from a table based on the table column (Watt & Eng, 2014).   These commands (INSERT , UPDATE , DELETE ) are often used in combination with the SELECT  command and the WHERE  clause to manipulate data by querying the table columns and selecting rows based on a specific condition(s). Note that the WHERE  clause is responsible for filtering rows, also called records, based on specific conditions (Kathuria, 2021).   Data Manipulation Examples   To illustrate the examples on how to use the INSERT , UPDAT E, and DELETE  commands, the following products table is going to be used.   Figure 1 Products Table   Note:  The figure table illustrates a product table of various types of guitars.   The INSERT Commands   The INSERT  command is used to insert rows/records (to store new data) in tables. Different syntax variations of the INSERT command exist in MySQL: Using VALUES INSERT INTO table_name (column1, column2, ...) -- the columns list VALUES (value1a, value2a, ...), -- Values for the first row (value1b, value2b, ...), -- Values for the second row (value1c, value2c, ...), -- Values for the third row ... (value1n, value2n, ...); -- Values for the last row Using SET INSERT INTO table_name SET column1 = value1, column2 = value2, ... ; With the SET  command, only one value can be inserted by column. Using SELECT INSERT INTO table_name (column1, column2,...) SELECT source_column1, source_column2,... FROM source_table WHERE condition; Here, SELECT  is used to insert columns from a source table column where a condition is met. INSERT  Example using the products table:   Figure 2 INSERT Example Note: The figure illustrates the SQL code for inserting a new guitar item in the products table.   The UPDATE Commands   The UPDATE  command is used to update existing rows/records' values (to modify existing data) in tables. Syntax: UPDATE table_name SET column1 = new_value1, column2 = new_value2 WHERE ‘condition’; -- The condition determines which rows are updated The WHERE  clause is used to update specific records' values based on a specific condition. UPDATE  Example using the products table:   Figure 3 UPDATE Example Note:  The figure illustrates the SQL code for adding +10 to the discount_percent for all items with a category_id = 1.   The DELETE Commands   The DELETE  command is used to delete rows/records (to delete existing data) in tables. Syntax: DELETE FROM table_name WHERE ‘condition’; -- Specifies row(s) to delete Note: The figure illustrates the SQL code for deleting all items with a category_id = 1   DELETE  Example using the products table:   Figure 4 DELETE Example Note: The figure illustrates the SQL code for deleting all items with a category_id = 1.   Hazards of Using UPDATE and DELETE   When using the UPDATE  and DELETE  commands, users need to be extremely careful, as these commands are very powerful and consequently come with significant risks. Accidentally misusing these commands can result in widespread data corruption or data loss. Some of these hazards include Missing or incorrect WHERE clause resulting in the UPDATE  or DELETE  commands being intentionally applied to all rows. Data loss caused by incorrect logic or use of the WHERE clause condition and the UPDATE command, which may be potentially fixed, and the DELETE  command, which often results in irreversible loss of data.   To summarize, the INSERT , UPDATE , and DELETE commands are DML operations in MySQL for adding, modifying, and removing data within tables. The INSERT syntax can take various forms. The UPDATE and DELETE commands require particular attention due to their potential to modify or destroy existing data on a large scale.   References:   Kathuria, H. (2021, November 9). How to write a WHERE clause in SQL . Learn SQL. https://learnsql.com/blog/where-clause-in-sql/   Murach, J. (2019). Chapter 1: An introduction to relational databases. Murach’s MySQL (3rd ed.).  Murach Books. ISBN: 9781943872367   Watt, A., & Eng, N. (2014). Chapter 16 SQL Data Manipulation Language. Database Design – 2nd Edition . Open textbooks, British Columbia University Open Campus. https://opentextbc.ca/dbdesign01/chapter/chapter-sql-dml/

This article explains the process of migrating a relational database from Microsoft SQL Server to MySQL, noting key differences between the platforms. It details the four essential steps for a successful migration: assessment and planning, schema conversion, data migration, and subsequent application modification. Alexander S. Ricciardi April 19, 2025 Often, a business decision to migrate their Relational Database Management System (RDBMS) from Microsoft SQL Server (SQL Server) to MySQL is driven by Information Technology (IT) cost savings. SQL Server comes with licensing costs that can be substantial, particularly for businesses with a large number of machines (H, 2024). On the other hand, while owned by Oracle, MySQL is distributed under an open-source (GPL) license. It offers a free community edition and an optional commercial license. This post gives a brief overview of the difference between SQL Server and MySQL, and explores, as the database administrator for a database investments business, four steps required to implement a migration from SQL Server to MySQL. Before migrating an RDBMS from SQL Server to MySQL is important to understand the difference between the two database systems. For example, MySQL runs comfortably on Windows, Linux, macOS, and other Unix systems, while SQL Server is primarily optimized for Microsoft/.NET technology stack and has recently expanded, since 2016, to run on Linux systems (H, 2024). The table below lists the main differences between the two RDBMSs. Table 1 MySQL vs. SQL Server Note: The table lists the main differences between SQL Server and MySQL. From “MySQL vs SQL Server: Which is the Right Database For You?” by Richman (2023). For our investment business example,  the RDBMS is migrating from SQL Server to MySQL, and with the fact that MySQL is compatible with Windows and Linux operating systems (OSs), the primary OSs that SQL Server also runs on, means that the operating system used by the business is not an issue. Nonetheless, as listed in Table 1, substantial differences exist between the two database systems; therefore, it is essential to assess the complexity of the existing RDBMS to plan the migration, translate the schema from SQL Server's T-SQL dialect to MySQL's SQL dialect, migrate the data from SQL Server schema to newly created (translated) MySQL schema, and once the schema migration is completed is essential that the application that use SQL Server is modified to function correctly with the new MySQL RDBMS. Assessment and Migration Planning Assessing the existing SQL Server RDBMS is the first step in migrating the system. This includes discovering and inventorying the system by identifying all SQL Server instances, these instances' versions (e.g., 2012, 2016, 2019), and editions (e.g., Standard, Enterprise, Express) (GoReplay, 2024; Rifai, 2024). It is also essential to document the database sizes and configuration settings (collation, compatibility level) as well as the server hardware used (CPU, RAM). This will help to mitigate T-SQL conversion to MySQL's SQL problems, identify unsupported SQL Server features in MySQL, and identify potential hardware performance issues when using MySQL. Then, based on the information gathered during the assessment process, formulate a migration strategy, such as a trickle migration strategy and a Big Bang migration strategy. The trickle strategy migrates databases and applications in groups, starting with the less complex and low-risk databases and application groups to the more complex and high-risk ones (Kutay, n.d.). This wave approach to migration reduces risks and allows the migration team to troubleshoot and refine the migration process before migrating to the more complex or most critical databases and application groups. The Big Bang strategy. On the other hand, migrate databases and applications simultaneously over a single period. This approach is simpler to manage than the trickle strategy but comes with higher risks, as a failure could jeopardize the entire migration project and may necessitate the entire process to be rolled back.  Note that for our example of an investment business migrating its SQL server to MySQL, the schema and procedural code are complex because the system must process complicated and sophisticated financial transactions. Therefore, the trickle migration strategy is best suited for this example as its phased group approach reduces the risks associated with migrating complex schema and procedural code. When using the trickle migration approach, the following steps are performed in stages (or waves), starting with low-risk databases and application groups and progressing to the more complex and high-risk ones.   Schema Conversion This step involves translating SQL Server's T-SQL dialect to MySQL's SQL dialect. That is, translating the T-SQL table structures, views, indexes, and procedural code (Islam & Thiagarajan, 2017). There are two primary approaches to this process, which are manual conversion and automated conversion. As its name indicates, the manual conversion approach requires developers to manually rewrite the SQL Server schema definitions and procedural code to MySQL syntax and definitions. On the other hand, the automated approach uses software tools that automatically convert SQL Server schema definitions and procedural code to MySQL syntax and definitions. Although much faster than the manual conversion approach, this approach is less accurate. Often, migration teams opt for a combined strategy that uses automated conversion software for the bulk of the schema and simpler procedural code, followed by a manual conversion of the more complex procedural code and a review and testing of the procedural code and schema output by the automated conversion.  For our investment business example, the hybrid approach is acceptable and advisable due to the substantial system complexity and size, as a manual-only conversion will be extremely time-consuming, and an automated one will not be suitable for complex procedural code involving complicated financial transactions. Data Migration The schema conversion is followed by migrating the data. The most important aspect of this phase is choosing the right data migration technique based on the database size and acceptable downtime. The two primary data transfer methods are offline and online. The offline data migration implies taking the source database (SQL Server database) offline or stopping application ‘updates’ and ‘adds’ referred to as writes, performing a bulk data transfer (using export/import, backup/restore adaptations), and once the data and application functionality is transferred bringing the new database online (MySQL Database). This approach is simpler than online but requires potentially significant downtime, which may not be suitable for applications that require high availability (GoReplay, 2024). Online data migration, on the other hand, uses Change Data Capture (CDC) and replication tools (like AWS DMS, Azure DMS if supported, Estuary Flow, Striim) to transfer an initial data bulk load while capturing ongoing changes from the SQL Server and applying these changes in near real-time the MySQL system minimizing downtime but introduces substantial complexity to the migration process. For our example, due to the financial nature of the business, continuous operation and minimal disruption are essential. The online data migration utilizing Change Data Capture (CDC) tools is the best choice. Application Modification This step is often implemented in coordination with the data migration step. The applications that previously used the SQL Server must be modified to use correctly the new MySQL RDBMS. This requires implementing application changes that adapt the application logic to the new database environment and testing the application functionality against the MySQL database. These changes imply modifying connection strings, updating drivers, reconfiguring Object-Relational Mappers (ORMs), and rewriting application-embedded T-SQL (Rubin, 2016; Nisnevich, 2020). These changes are not optional clean-up tasks; they are crucial for a successful migration. This step, for our investment business example, is complex and requires significant effort and time, especially when rewriting application-embedded T-SQL, reconfiguring ORMs, and testing the modifications to ensure that the application logic correctly accesses the data and handles the financial transactions.  Summary Migrating a database from SQL Server to MySQL is a lengthy and complex process that requires a good understanding of the differences between the two RDBMS. The migration follows four crucial steps: assessment and strategic planning (including choosing between trickle and big bang approaches), schema translation (often employing a hybrid manual/automated technique), data migration (selecting offline or online methods based on downtime needs), and application modification. As illustrated with the investment business example, implementing a successful migration is often business-specific; in other words, it depends on the specific context. For our investment business example, the OS(s) used by the business will not affect migration, as MySQL is compatible with them. The trickle migration strategy is best suited given the system's complexity and size. The hybrid schema conversion approach is acceptable and advisable due to the system’s substantial complexity and size. The online data migration utilizing CDC tools is the best choice as it minimizes downtime and makes changes in near real-time. Time and resources should be allocated to modifying the application(s) used by the business to ensure that its business logic correctly accesses the data and handles the financial transactions as expected. References: GoReplay (2024, November 24). Your comprehensive SQL Server migration checklist: tools, steps, and load testing insights. GoReplay. https://goreplay.org/blog/demystifying-sql-server-migrations-tools-steps-and-best-practices/ H, J. (2024, July 8). MySQL vs MS SQL server: Key similarities and differences. Dreamfactory. https://blog.dreamfactory.com/ms-sql-server-vs-mysql Islam, A., R, & Thiagarajan,  A. (2017, October 5). Migrating a SQL Server database to a MySQL-compatible database engine. AWS. https://aws.amazon.com/blogs/database/migrating-a-sql-server-database-to-a-mysql-compatible-database-engine/ Kutay, J. (n.d.). An introduction to database migration strategy and best practices. Stiim. https://www.striim.com/blog/an-introduction-to-database-migration-strategy-and-best-practices/ Nisnevich, A. (2020, April 18) Migrating a ASP.NET application from SQL Server to MySQL. AlexNisnevich Blog. https://alex.nisnevich.com/blog/2020/04/18/migrating_asp_net_mysql.html Richman, J. (2023, April 5). MySQL vs SQL Server: Which is the right Database for you? Estuary. https://estuary.dev/blog/mysql-vs-sql-server/ Rifai, B. (2024, April 3). Automate SQL Server discovery and assessment to accelerate migration to AWS. AWS. https://aws.amazon.com/blogs/database/automate-sql-server-discovery-and-assessment-to-accelerate-migration-to-aws/ Rubin, A. (2016, June 23). Migrate from MS SQL Server to MySQL. Percona. https://www.percona.com/blog/migrate-from-ms-sql-server-to-mysql/

Successfully releasing an Android app on the Google Play Store necessitates a preparatory process involving several key tasks. These include gathering essential materials like cryptographic keys and icons, configuring the app for release by setting IDs and cleaning the project, building a signed and optimized version (preferably AAB), preparing necessary servers, and thoroughly testing the final build before publication. Alexander S. Ricciardi April 13, 2025 The Google Play Store is the primary channel through which Android developers distribute their apps. It is an ecosystem that allows them to publish, manage, monetize, and promote their apps globally. To successfully release the app and publish it in the store, the developer should prepare it to be deployed; this is merely a suggestion, but it is more like a requirement. To prepare an app for release, the developer needs to configure, build, and test a release version of the app (Android Developers, n.d.a). This post explores the tasks required to prepare an Android app for release. Android Package Kit (APK) Before preparing an Android app for release, it is important to understand what the Android Package Kit (APK) is. An APK file contains all the necessary components to run an app on an Android device (Gillis, n.d.). It Includes: AndroidManifest.xml . An Android manifest that describes the name, version, access rights, library and other contents of the APK file. assets/ . App assets and resource files. classes.dex . Compiled Java classes in the DEX file format run on the device. lib/. Contains platform-dependent compiled code and native libraries for device-specific architectures, such as x86 or x86_64. META-INF/ . Contains the app certificate, manifest file, signature, and a list of resources. res/ . Contains resources; for example, images that are not already compiled into resources.arsc. resources.arsc . Contains pre-compiled resources used by the app. Figure 1 Android App Release Tasks Note: The figure illustrates the main tasks to prepare your app for release. From “Prepare your app for release” by Android Developers (n.d.a). Release Tasks As shown in Figure 1, the tasks are a set of chronological steps that need to be followed to properly release an Android app; the task set includes gathering materials, configuring the app, building an APK or Android App Bundles (AAB) file, preparing remote servers, and testing the app for release. Gathering Materials The first task of preparing an app for release is to gather the essential materials and resources. This needs to be done before configuring the app, and it requires preparing an End-User License Agreement (EULA), getting a cryptographic key, and having an App icon (Android Developers, n.d.a). EULA is a mandatory requirement for publishing on the Google Play Store. It is a legal contract between the developer and the users. It describes the terms and conditions of use. The following link is a generic example of an EULA made for the Steelcase company: Mobile Application End User License Agreement by Steelcase (2017). Note, this is just an example, and should be used as an example, not as a legal EULA document for your app. A cryptographic key is required for publishing an Android app. The key is an app unique digital signature that is used to verify the identity of the app’s author. It is a security measure that is used to verify that the app has not been tampered with or corrupted since it was signed. This key is mandated by the Google Play Store, and without it, apps cannot be installed or updated on Android devices. App icons are also required as they are used to launch the app on Android devices. “A launcher icon is a graphic that represents your application. Launcher icons are used by Launcher applications and appear on the user’s Home screen” (Stuff MIT, n.d.). App Configuration for Release The second step for preparing an Android app for release is to configure the app for deployment. This includes. An app ID, which is the package name that will be used over the life of the app (Stuff MIT, n.d.). For example, ‘ com.example.myapp ’, which usually follows the reverse domain name convention, this ID will be used to update, identify, and integrate the app with another service. Note that once the ID is set, after the app is distributed, the ID cannot be changed. Turning off debugging and logging (if used), it is essential to deactivate logging and disable the debugging option before building an app for deployment. To deactivate logging, the Log methods in the app’s source files need to be disabled. To disable debugging android:debuggable attribute needs to be removed from the tag in the app’s manifest file, or by setting the android:debuggable attribute to false in the manifest file. Additionally, any log files or static test files need to be removed. If used, any debugging tracing calls added code, such as startMethodTracing() and stopMethodTracing() need to be removed. It is also important to clean up the app’s project directories from stray and orphaned files by reviewing jni/ , lib/ , and src/ directories. For example, the jni/ directory needs to contain only source files associated with the Android NDK. The lib/ directory needs to contain only third-party library files or private library files. The src/ directory needs to contain only the source files for your application ( .java and .aidl files). The src/ directory should not contain any .jar files. Additionally, private or proprietary data files that the app does not use need to be removed. For example, in the res/ directory, old drawable files, layout files, and values files that are no longer used by the app need to be removed. In the lib/ directory, test libraries need to be removed. In the assets/ directory and res/raw/ directory, check for raw asset files and static files that may need to be updated or removed before release. Build the App for Release The next task is to build the APK file, also called a release-ready APK file, or Android App Bundle (AAB) file; both file formats should be signed and optimized (Android Developers, n.d.a). To sign the app for release to the Google Play Store means that the app needs to possess a unique cryptographic key. The cryptographic key was addressed previously in the Gathering Materials section of this post. Google strongly recommends using the AAB file format for new applications. An AAB file is a publishing format that includes all your app's compiled code and resources; it also defers APK generation and signing to Google Play (Android Developers, n.d.b). Note that when using Android Studio or the Gradle build system from the command line, the build process is entirely automated. To build an Android app release with Android Studio, the following YouTube video is a good source of information How to Create Singed AAB file in Android Studio (2023 Update) by The Code City (2023). The following YouTube the entire process of deploying an Android app on Google Play Store: How to Publish an Android App to Google Play | Updated 2024 by MJSD Coding (2024). Prepare Severs This task is relevant to the app release if the app relies on any external servers or services for its core functionality (Android Developers, n.d.a). Preparing the server for release involves verifying that the servers are secure, that they can handle the expected number of users (user load), and are configured for production use. This applies to apps using API calls to fetch data from a source that is not controlled by the app developer. Testing For Release The final task is testing of the app release build. This is done by testing it on the device and network conditions. Ideally, the app UI should at least be capable of handling one handset-sized device and one tablet-sized device. Google recommends using its Firebase Test Lab platform (Firebase, n.d.) to test Android apps across a variety of different devices and Android OS versions. Summary Preparing an Android app for release on the Google require a set of multiple steps, these tasks include understanding the structure of an APK and AAB file formats, gathering essential materials like a cryptographic key, EULA, and app icon; configuring the app by setting a unique ID, disabling debugging/logging, and cleaning project directories; and finally, building a signed and optimized release version, preferably as an AAB file format. Following these preparation tasks are essential for a smooth and successful app launch. References: Android Developers (n.d.a). Prepare your app for release . Android. https://developer.android.com/studio/publish/preparing Android Developers (n.d.b). About Android App bundles . Android. https://developer.android.com/guide/app-bundle Firebase (n.d.). Firebase Test Lab. Google. https://firebase.google.com/docs/test-lab Gillis, A. S. (n.d.). APK file (Android Package Kit file format) . Tech Target Networks. https://www.techtarget.com/whatis/definition/APK-file-Android-Package-Kit-file-format#:~:text=Contents%20of%20an%20Android%20Package,resources%20used%20by%20the%20app. MJSD Coding (2024, September 3). How to publish an Android app to Google Play | Updated 2024 [Video]. YouTube. https://www.youtube.com/watch?v=d8uEdeMgikU Steelcase (2017). Mobile application end user license agreement [PDF]. Steelcase. https://www.steelcase.com/content/uploads/2018/01/mobile_app_eula_ssa_19oct2017.pdf Stuff MIT (n.d.). Launcher icons . Massachusetts Institute of Technology. https://stuff.mit.edu/afs/sipb/project/android/docs/guide/practices/ui_guidelines/icon_design_launcher.html# The Code City (2023, August 2) How to create singed AAB file in Android Studio (2023 Update) [Video]. YouTube. https://www.youtube.com/watch?v=qMAtgMP0xyg

This article provides an overview of testing types necessary before releasing an Android app, detailing functional tests like unit, integration, UI, and end-to-end to ensure correct behavior. It also briefly touches upon non-functional testing, which assesses aspects like performance and usability, highlighting the importance of both for a high-quality release. Alexander S. Ricciardi April 13, 2025 Before releasing your Android app to the public, it is important to test your app's consistency by verifying its correctness, functional behavior, and usability before you release it publicly (Android Developers n.d.). It is two major types of testing, functional and non-functional. This post focuses on functional testing types by providing an overview of them and briefly explores non-functional types. Functional Testing Types Unit Testing Unit testing is one of the prevalent functional texting types, they should make up approximately 70% of your total number of tests. Unit testing involves testing individual units or components of your app in isolation (GAT Staff Writers, 2024). In other words, they verify small portions of the code app like individual methods or classes. They do not require an Android emulator or device as they are executed on the developer's PC or workstation using Java Virtual Machine (JVM) (BrowserStack, 2025a). This makes the test ideal for testing business logic. The most comely used tool for unit testing is JUnit, other tools are Mockito and Truth. Integration Testing Integration testing is implemented on components or modules that have been unit tested (Singh, 2024). The goal of the test is to observe data flow and identify defects in it. In other words, it examines how unit tested components work together to identify defects (BrowserStack, 2025a). The most common tools used for integration testing are Robolectric, JUnit, Mockito, and Truth. UI Testing User Interface (UI) testing is used to verify both the appearance and functionality of the UI by evaluating the visual elements, layout, responsiveness, and overall user experience (BrowserStack, 2025a). This involves behavior testing which examines how the UI responds to user actions and screenshot testing which is used to validate the visual accuracy of the UI across different devices (screen sizes and resolutions). The most commonly used tools used for UI testing are Espresso and UIAutomator. End-to-End Testing End-to-end (E2E) is used to verify an application's entire workflow from start to finish by simulating real-world scenarios. It verifies that all components of the application, including the user interface, business logic, data persistence, and external connection (API), function correctly together (BrowserStack, 2024b). The most common tools used for E2E Espresso and UIAutomator. Other tools are Appium which has cross-platform capabilities; and BrowserStack and LambdaTest which are cloud based testing platforms that offer a wide range of real device testing. Non-Functional Testing Type s Non-functional testing is used to verify aspects of a software application that are not directly related to its specific functions or features, but rather how well the system operates overall (GAT Staff Writers, 2024). In other words, it focuses on the quality of the software, such as performance, security, usability, and reliability. Non-functional testing is composed of a set of tests that includes performance testing that evaluates the speed, responsiveness, and stability of the app under different conditions; usability testing which is used to assess how easy is the app to use; compatibility testing which is used to verifies consistent functionality of the app across different devices and OS versions; accessibility testing which is used to access if the app is usable for users with disabilities; regression testing is used to test updates for bugs; and smoke testing is used to quickly check of the app’s functionalities after a build. Summary Before successfully releasing an Android app, rigorous testing is required. Testing such as functional testing (unit, integration, UI, end-to-end), which checks what the app does, ensuring correct functionality at all levels; non-functional testing (performance, security, usability, etc.), which focuses on how well the app performs. Combining both approaches ensures that the app is free of bugs, that it is user-friendly, and secure. References: Android developer (n.d.). Fundamentals of testing Android apps. Android. https://developer.android.com/training/testing/fundamentals BowserStack (2025a, March 18). What is Android unit testing? BowserStack. https://www.browserstack.com/guide/android-unit-testing BowserStack (2024b, June 28). End To End testing: Tools, types, & best practices. BowserStack. https://www.browserstack.com/guide/end-to-end-testing GAT Staff Writers (2024, May). What is Android testing - Types, tools and best practices. Global App testing. https://www.globalapptesting.com/blog/what-is-android-testing Singh, R. (2024, June 13). 12 mobile app testing types: A thorough exploration for QA professionals. Headspine. https://www.headspin.io/blog/types-of-mobile-app-testing

The article explores data persistence options within the Android ecosystem, focusing on SQLite and the Room Persistence Library. It compares these two approaches, highlighting their strengths and weaknesses to help developers choose the most suitable solution for their application's specific data storage needs, ultimately impacting performance and user experience. Alexander S. Ricciardi Mrach 9, 2025 Data persistence is the corner stone for building functional and robust applications within the Android ecosystem. Therefore, choosing the right data persistent solution for a specific application is essential, as it can significantly influence an app's performance, maintainability, and, consequently, the user experience. This article explores SQLite database and the Room Persistence Library. Data Persistence in Mobile Applications Data persistence in mobile applications can be defined as the capability of an application to store data locally with the goal of being retrieved even after the program is terminated and restarted (MongoDB, n.d.; Cuello, 2023). This is essential for preventing data losses, remembering the application state after the user left the application (e.g. in video game user progression), for offline functionality, and for better performance if the data is retrieved locally rather than from a server. In other words, data persistence is essential for building functional and robust Android applications that provide a good user experience. Storing Data Locally in Android When storing data locally, Android offers several options. Storage options like sharing preferences that use share key-values pairs, internal storage that stores files on-device, external storage that stores data in removable media such as SD cards, SQLite databases that store data relational databases, and the Room Persistence Library that uses SQLite databases through an abstraction layer to store data (Android Developers, n.d.). Depending on the needs of the application, one method may be more suitable than another. For example, for simple data, Shared Preferences or DataStore may be enough; on the other hand for larger datasets or complex data structures, SQLite or Room are better options. The following table describes the various Android storage options and their use cases. Table 1 Android Data Storage Options Note: The table provides descriptions of the various Android storage methods and their use cases. Data from “Data and file storage overview” by Android Developers (n.d.). Understanding The Differences Between SQLite and Room Persistence Library SQLite on Android can store data on the user's device (Chaitanyamunje, 2025). It is an open-source database that stores relational data in the form of tables. It is widely used, and its lightweight overhead makes it ideal for environments with limited resources such as mobile phones. SQLite uses the CRUD (Create, Read, Update, Delete) SQL approach to manipulate data. On the other hand, Room Persistence Library is an abstraction layer built on top of SQLite, it provides an object-oriented approach to managing persistent data by automatically converting data objects (the abstraction layer) to relational data that can be stored using SQLite and converting relational to object data that can be used by the application. Room significantly reduces boilerplate code compared to straight SQLite implementations. A boilerplate is code used to perform common database operations; for example, converting between database tables and Kotlin objects. As described above, the approaches are different, one uses a direct SQL query-based approach and the other one provides an object-oriented abstraction that handles the SQL operations. The question that can be asked is when is it better to use one approach over the other? For applications with simple data requirements probably would be due to very light overhead compared to Room. On the other hand, Room would be better for applications with complex data models or larger datasets. The table below lists the major differences between SQLite and Room Persistence Library. Table 2 SQLite vs Room Persistence Library Note: The table lists the differences between SQLite and Room Persistence Library based on various features. From several sources (Android Developers, n.d.; Mbano, 2022; Zincircioğlu, 2023; Naniewicz, 2024) To summarize, in mobile Applications, data persistence is the capability of an application to store data locally with the goal of being retrieved even after the program is terminated and restarted. To implement data persistence, the Android ecosystem offers several options such as sharing preferences, internal storage, external storage, SQLite, and the Room Persistence Library. While SQLite offers a direct, lightweight approach to managing local data, Room Persistence Library provides an object-oriented abstraction layer that reduces boilerplate code and seamlessly integrates with the Android ecosystem. Therefore, when implementing a data persistent solution is essential to understand the difference between the available storage options and how they align with the specific needs of the application. References: Android Developers (n.d.). Data and file storage overview. Android Developer. https://developer.android.com/training/data-storage#:~:text=If%20you%20have%20data%20that's,contains%20more%20than%202%20columns). Chaitanyamunje (2025, January 6). How to create and add data to SQLite database in Android? GeeksForGeeks. https://www.geeksforgeeks.org/how-to-create-and-add-data-to-sqlite-database-in-android/ Cuello, C. (2023, September 17). What is data Persistence? A complete guide. Rivery. https://rivery.io/data-learning-center/data-persistence/ Mbano, U. (2022, April 16). Android Room versus SQLite — Which is best? DVT software engineering. Medium. https://medium.com/dvt-engineering/android-room-versus-sqlite-which-is-best-32ff651bc361MongoDB (n.d.). What is Data Persistence? MongoDb. https://www.mongodb.com/resources/basics/databases/data-persistence Naniewicz, R., (2024, February 8). Check out why Room is a retrofit for SQLite. Netguru. https://www.netguru.com/blog/check-out-why-room-is-a-retrofit-for-sqlite Zincircioğlu, S. (2023, May 25). RoomDB vs SQLite : Exploring database options for Android development. Medium. https://medium.com/huawei-developers/roomdb-vs-sqlite-exploring-database-options-for-android-development-1120151e6737

The article provides an overview on how to incorporate multimedia elements into Android applications. It covers essential aspects such as handling permissions for accessing device resources, displaying images using ImageView and Image composable, and playing audio and video with MediaPlayer, VideoView and Jetpack Media3. Alexander S. Ricciardi March 16, 2025 Incorporating images, audio, and video into an Android app often involves implementing permissions to display images and play videos. This is usually necessary to meet the project's requirements or enhance the user’s experience. This post provides an overview on how to integrate images, audio, and video into Android apps. Permissions To integrate images, audio, and video within an Android app, handling permissions is essential because multimedia functionalities require access to sensitive resources like the camera and external storage. Thus, understanding what the necessary permissions are and how to implement them is crucial. Additionally, before they can be implemented, the permissions have to be allowed by the user. To implement a camera permission, for example, to capture or record videos, an app needs to request access to the camera (Android Developers. n.d.a). This request can be declared in the AndroidManifest.xml file as follows: To implement storage permissions to access media files stored on the device, the storage-related permissions need to be declared, and the files must reside in the ediaStore.Images, MediaStore.Video, or MediaStore.Audio (Android Developers. n.d.b). For apps used in Android 10 (API level 29) and higher, scoped storage is the recommended. With scoped storage, apps have access to their own app-specific directories and can access media files through the MediaStore API. For Android 9 and lower, or not wanting to use scoped storage, the READ_EXTERNAL_STORAGE (read only) and the WRITE_EXTERNAL_STORAGE (write only) permissions need to be declared in the AndroidManifest.xml file as follows: Image, Audio, and Video To display images using XML, the ImageView element is used to display images from various sources such as from drawable, local storage, and networks. To use ImageView with a drawable, simply drag the ImageView widget into your activity's layout, and then a pop-up dialog will appear, allowing you to choose from the available drawables (Rishu_mishra, 2025). When using Composable, the Image composable provides a way to display images. You can load images from different sources, including drawables, bitmaps, and painters (Android Developers. n.d.c). To play audio, the MediaPlayer class allows to play audio. It supports various audio sources, including local files, streams, and resources (Adityamshidlyali, 2024) To play videos, the VideoView is a UI element allows playing video content. It supports videos from various sources, including local files, resources, and network URLs (Google Developers, n.d.). Another alternative to MediaPlayer and VideoView, Jetpack Media3 is an Android library for media that enables apps to display rich audio and visual experiences (Android Developers. n.d.d). To summarize, integrating into an Android app, images, audio, and video requires handling permissions and the use of different tools. Permissions like CAMERA, READ_EXTERNAL_STORAGE, and WRITE_EXTERNAL_STORAGE need to be declared in the AndroidManifest.xml file and allowed by the user. For displaying images, ImageView in XML layouts or the Image composable can be used for that purpose. Audio can be managed by using the MediaPlayer class, while for video playing, VideoView can be used. An alternative for audio and video playing is to use the Jetpack Media3 library. References: Adityamshidlyali (2024, August 12). MediaPlayer class in Android. GeeksForGeeks. https://www.geeksforgeeks.org/mediaplayer-class-in-android/ Android Developers (n.d.a). Capture an image. Google. https://developer.android.com/media/camera/camerax/take-photo Android Developers (n.d.b). Access media files from shared storage . Google. https://developer.android.com/training/data-storage/shared/media Android Developers (n.d.c). Add images to your Android app. Google. https://developer.android.com/codelabs/basic-android-kotlin-compose-add-images#0 Android Developers. (n.d.d). Introduction to Jetpack Media3. Google. https://developer.android.com/media/media3 Google Developers (n.d.). 13.1: Playing video with VideoView. Advanced Android development. Google. https://google-developer-training.github.io/android-developer-advanced-course-practicals/ Rishu_mishra (2025, January 28). ImageView in Android with example. GeeksForGeeks. https://www.geeksforgeeks.org/imageview-in-android-with-example/

The article compares three popular HTTP clients, Retrofit, Volley, and OkHttp for making network requests in Android apps developed with Kotlin and Jetpack Compose. It highlights the strengths and weaknesses of each client, providing guidance on selecting the best option based on project needs, and emphasizes the importance of asynchronous request handling and error management for a robust application. Alexander S. Ricciardi March 9, 2025 Developing an Android app frequently involves implementing some data fetching functionality and sending data to servers within the application (Coditive, 2024). This often means making network requests asynchronously and handling network errors. When using a combination of Kotlin and Jetpack Compose, several popular HTTP (Hypertext Transfer Protocol) clients can be used to perform network requests. This post explores three of the most commonly used clients: Retrofit, Volley, and OkHttp, and their strengths and weaknesses, and discusses when to use each one. Clients for Network Requests The most popular HTTP clients for making network requests while using Kotlin and Jetpack Compose are Retrofit, Volley, and OkHttp. HTTP client is a software that allows applications to communicate with web servers and APIs over the internet. It handles network requests, responses, and connections within the HTTP protocol system, including managing headers, methods (GET, POST, PUT, DELETE). Retrofit Retrofit was developed by Square, and it is a type-safe HTTP client (Kostadinov, 2024). It is an abstraction that allows making API calls through declarative interfaces and handles parsing JSON into Java/Kotlin objects using libraries like Gson or Moshi. The table below lists and describes different aspects and features of Retrofit. Table 1 Retrofit Aspect and Features Note: The table lists various aspects and features of the Retrofit HTTP client for Kotlin and Java. Data from several sources (Square, n.d.a; GeeksForGeeks, 2025; Kramer, 2024; Kostadinov, 2024; Anna, 2024) Volley Volley is another HTTP client developed by Google, it was designed in 2013 to make networking easier and faster in Android apps (Vartika02, 2025). It is well-suited for applications making frequent, small network requests. The table below lists and describes different aspects and features of Volley. Table 2 Volley Aspect and Features Note: The table lists various aspects and features of the Retrofit HTTP client for Kotlin and Java. Data from several sources (Google, n.d.; Vartika02, 2025; AbhiAndroid, n.d.) OkHttp OkHttp was developed by Square, it is a powerful and efficient HTTP client for Kotlin and Java, it was used as the foundation for both Retrofit and Volley (Gouda, 2024). It can also be used directly, providing more control on network requests than Retrofit and Volley. The table below lists and describes different aspects and features of OkHttp. Table 3 OkHttp Aspect and Features Note: The table lists various aspects and features of the OkHttp HTTP client for Kotlin and Java. Data from several sources (Square, n.d.b; Gouda, 2024; Baeldung, n.d.) Each client has its pros and its cons. When choosing the best client for the application developers need to consider factors such as the complexity of the project, the frequency of the requests, the size of the requests, and control vs. convenience to select the client that fits best with those needs. The table below compares the clients by key features and characteristics. Table 4 HTTP Client Comparison Note: The table below compares the clients by key features and characteristics. No matter what client is implemented within an application, network requests should never be executed on the main threads. Doing so can result in the app freezing and poor user experience. Best practices dictate the use of asynchronous mechanisms such as Kotlin Coroutines to avoid blocking the app's main thread. Kotlin Coroutine is a concurrency design pattern that simplifies code executing asynchronously (Android Developers, n.d.). The design provides several advantages including its lightweight nature, built-in cancellation support, and improved memory management. In addition to implementing asynchronous mechanisms for executing network requests, it is essential to handle network errors properly to create an overall robust application and a good user experience. Strategies such as robust network exception handling, thorough testing, retry mechanisms, and user feedback should be implemented and designed when integrating network request mechanisms within the application. To summarize, when developing an Android app with Kotlin and Jetpack Compose, common HTTP clients for integrating network requests are Retrofit, Volley, and OkHttp. Choosing the right HTTP client depends on the specific needs of the project. Regardless of the chosen library (client), developers need to implement asynchronous execution for network requests by using designs such as Kotlin Coroutines. This is essential for not freezing the app during network calls and for a good user experience. Additionally, implementing network error handling is also essential for building a robust application that can use network request mechanisms effectively. Therefore, understanding how to integrate HTTP clients with asynchronous programming and error handling is crucial for designing a successful Android development that incorporates network requests. -Alex References: AbhiAndroid (n.d.) . Volley tutorial with example in Android Studio . AbhiAndroid. https://abhiandroid.com/programming/volley#gsc.tab=0 Android Developers (n.d.). Kotlin coroutines on Android. Android. https://developer.android.com/kotlin/coroutines Anna. (2024, November 16). Retrofit in Android. Medium. https://medium.com/@anna972606/retrofit-in-android-15fa724a8fa6 Baeldung (n.d.). A guide to OkHttp. Baeldung. https://www.baeldung.com/guide-to-okhttp Coditive. (2024, November 24). Understanding retrofit: Simplifying Android networking. Medium. https://medium.com/@coditive/understanding-retrofit-simplifying-android-networking-ef37f72f9cb8 Kramer, N. (2024, May 14). Retrofit tutorial for Android beginners. Daily.dev . https://daily.dev/blog/retrofit-tutorial-for-android-beginners Kostadinov, D. (2024, December 11). When to use Retrofit and when to use KTOR: a guide for Android developers. Medium. https://proandroiddev.com/when-to-use-retrofit-and-when-to-use-ktor-a-guide-for-android-developers-918491dcf69a GeeksForGeeks (2025, February 18). Introduction to Retrofit in Android. GeeksForGeeks. https://www.geeksforgeeks.org/introduction-retofit-2-android-set-1/ Google (n.d.). Volley. GitHub. https://google.github.io/volley/ Gouda, M. (3034, December 5). Comprehensive guide to OkHttp for Java and Kotlin. ScrpFly. https://scrapfly.io/blog/guide-to-okhttp-java-kotlin/ Square (n.d.a). Retrofit. GitHub. https://square.github.io/retrofit/. Square (n.d.b). OkHttp. GitHub. https://square.github.io/okhttp/ Vartika02 (2025, January 6). Volley library in Android. GeeksForGeeks. https://www.geeksforgeeks.org/volley-library-in-android/

This article documents the author's first experience developing an Android application using Kotlin and XML, detailing the creation of a "Hello Android" app that features a bouncing text animation and a toggle button to control it. The author reflects on the challenges of learning Kotlin, understanding the Android project structure, connecting UI elements to code, and implementing a background thread for the animation, providing a beginner's perspective on fundamental Android development concepts. Alexander S. Ricciardi February 24, 2025 Learning platform-based application development is not an easy endeavor. In this article, I reflect on my first exposure to Android app development using Kotlin and XML, providing an overview of a simple "Hello Android" application. I describe the application's functionality and testing scenarios, including the pseudocode, Kotlin code, and output screenshots. I also reflect on the obstacles faced during development and the skills I acquired. App Description The app is a simple Hello Android Application written in Kotlin. It displays a simple animation where a TextView ("Hello Android!") bounces around within the screen's boundaries. It also provides a toggle button allowing the user to stop and restart the text animation. Additionally, the program uses a background thread to run a text animation within an infinite loop. This loop updates the TextView's position and, when hitting a screen boundaries, the text bounces and changes color. This ‘hands-on’ animation approach is implemented for learning purposes, it is generally better and good practice to use Android API’s built‑in animation classes (like those in AnimationUtils). Furthermore, to initialize the application, I used the Empty View Activity Template from Android Studio. Then I modify the files to implement the text animation and my icon. The source code files for this application can be found on my GitHub page in the following repository Module-1-Critical-Thinking . The app was developed using the Android Studio IDE (Integrated Development Environment). Project Map: CTA1 Hello Android App.docx (this file, App documentation) MainActivityPseudo.txt (Main Activity pseudocode) The project used files from the Android Studio’s Empty View Activity template. Additionally, only the template files that were modified to accommodate the functionality of the application are listed below: MainActivity.kt (Kotlin code, application logic) Main_activity.xml (XML code, main UI layout) Value string.xml (resource file storing strings) The following files have been overridden or modified to accommodate my icon. If you do not want to use my logo, do not use these files. The template will automatically use the Android icon. Value color.xml theme.xml (this file was not modified or overridden, but it is part of the value folder) drawable ic_launcher_background.xml ic_launcher_foreground.xml mipmap-anydpi-v26 ic_launcher.xml ic_launcher_round.xml mipmap folders (hdpi; mdpi; xhdpi; xxhdpi; xxxhdp – different variation of my icon) Reflection This is my first time using Kotlin and Android Studio to create a program. Installing Android Studio was straightforward. Learning about Kotlin from the textbook and doing research about it was rewarding. The following is an overview of Kotlin based on what I have learned from the textbook and my research. Kotlin is often described as the most succinct language, meaning that is the least error-prone programming language (Horton, 2019). Android SDK (Software Development Kit) is written in Java. Moreover, Kotlin is fully interoperable with Java, meaning that Java libraries and frameworks can be easily integrated into Kotlin code, and Java projects also can be easily migrated to Kotlin. It is an Object-Oriented Programming language (OOP). It includes null safety, preventing null pointers. It allows function extensions, that is adding functionality to existing classes without modifying their source code. It allows data classes which are classes primarily used to hold data. It can implement coroutines, making asynchronous programming much easier such as handling network requests. (Ricciardi, 2025) Furthermore, it is generally preferred for Android app development over programming languages like C++ and Java. The table below lists some of the advantages Kotlin has over C++ and Java. Table 1 Kotlin’s advantages over C++ and Java Note: The table lists the advantages that Kotlin has over C++ and Java when developing Android based Apps. From “Why Kotlin is preferred for Android app development” by Ricciardi (2025). As described previously, I chose to create a “Simple Hello Android APP” by modifying the Empty View Activity Template from Android Studio and implementing a background thread to run a text animation within an infinite loop. This loop updates the TextView's position and, when the text hits a screen boundary the text bounces and changes color. This was done to practice implementing variables and loops using Kotlin. Kotlin Variables Handling Kotlin uses type inference, type check, and smart-cast, meaning that, when type referring, the compiler automatically deduces the data types of variables and expressions, eliminating the need for explicitly declaring variable types (App Dev Insights, 2023). Type checks are performed in two ways by using the ‘is’ operator and its negation ‘!is’. These operations return a Boolean, that is return true if the condition is met. For example, if a variable ‘is’ of the data typed checked the condition will return true if not it will return false. Smart-cast allows the compiler to cast a variable to a specific type within a code block, without the need for explicit type checks and casts; for example, ' print(x.length) // x (a string) is automatically cast to String '. Furthermore, Kotlin uses two different keywords to declare variables, ‘val’ and ‘var’ (Developers, n.d.). ‘val’ is used to declare static variables, variables that never change, and ‘var’ is used to declare non-static variables, variables whose value can change. Kotlin's syntax is very similar to Java, and transitioning from Java to Kotlin for this assignment was traits forward. The part that was most challenging for me was learning the Android Project Structure, that is how the project files are interconnected using the Android Manifest file and Gradle build configurations My Android Project File Structure Learning my project file structure dependencies was essential for me to understand how different parts of the application interact and to implement a functional code. Below is a breakdown of the key components and their description: Figure 1 Hello App Structure on Android Studio Note: The figure illustrates the Hello App file structure in Android Studio. Note that the MainActivity.kt and activity_main.xml are the core files of the application. The MainActivity.kt file contains the Kotlin source code dictating the logic for the application, in this example the logic for the bouncing text background animation thread and the toggle button. The activity_main.xml contains the XML code that defines the user interface layout, including the TextView for the " Hello Android! " text and the ToggleButton . The ic_launcher files have been modified or overridden to accommodate my personal icon. The color.xml file has been modified to integrate a color background for my icon. The strings.xml file has been modified to store the ToggleButton text describing its state and the bouncing_text (“ Hello Android !”) Pseudocode and Koltin Code Implementing the animation of the bouncing text was not very difficult as I had implemented this functionality in other programming languages, more specifically Java and Python. The most difficult part was understanding how to get the elements from the UI so they could be integrated into the application logic ( MainActivity.kt ). The Android environment uses ‘id’ to accomplish it. For example, to get the size of the screen (‘ View ’) to set the boundaries of the text animation, I use the ‘ id ’ of the element ‘View’ from the activity_main.xm l layout file after I imported it and set it as the application content view using this following line of code ' setContentView(R.layout.activity_main)' Below is a Kotlin code snippet demonstrating how this process is done: Code Snippet 1 Using Ids to Connect Logic and UI // Set content view, the UI from the activity_main.xml layout file setContentView(R.layout.activity_main) // Layout's views attribute from activity_main.xml layout file val container = findViewById( R.id .main) // The root container (ConstraintLayout) // The TextView that will bounce val textView = findViewById( R.id .bouncingText) // Button to start/stop the animation val toggleButton = findViewById( R.id .toggleButton) Note: The Kotlin code snippet illustrated how UI (layout) and Logic are connected using ids. This code is part of the onCreate function. The other challenging part was understanding the functionality of the ' ViewCompat.setOnApplyWindowInsetsListener(findViewById(R.id.main))' method within the onCreate function, to keep it simple, the code functionality avoids overlaps between window insets (UI element of the app behind system elements) with system UI elements (e.g. Status Bar, Navigation Bar). If the app's content overlaps with the system UI elements, it can lead to poor user experience. Figure 2 Main Activity Pseudocode Note: The figure illustrates a snapshot of pseudocode implementing the logic of a text view bouncing and changing color within a container (presumably a screen). The animation runs in a background thread. Additionally, the code implements a toggle button to stop and restart the animation. Furthermore, this pseudocode was created with an Android application perspective in mind and is intended to be translated into Kotlin. Please see MainActivityPseudo.txt file, the complete code here: Module-1-Critical-Thinking . Figure 3 Main Activity Kotlin (MainActivity.kt) Note: The figure illustrates a snapshot of Kotlin code implementing the logic of a text view bouncing and changing color within a container (a screen). The animation runs in a background thread. Additionally, the code implements the logic of a toggle button to stop and restart the animation. Please see MainActivity.kt file , the complete code here: Module-1-Critical-Thinking . Code Snippet 4 Main Activity XLM Layout UI (main_activity.xml) ========================================================================== This file is a resource file written in XML that defines the user interface layout, including the TextView for the "Hello Android!" text and the ToggleButton. It is part of the Simple Hello Android App Author: Alexander Ricciardi Date: 02/14/2025 Requirement: Kotlin and XML Program Description: This is a simple Hello Android Application written in Kotlin. It displays a simple animation where a TextView ("Hello Android!") bounces around within the screen's boundaries. It also provides a toggle button allowing the user to stop and restart the text animation. ========================================================================== --> Note: The XML snippet illustrates the UI layout of the application. Please also see main_activity.xml file for the code. To connect the elements from the UI, this file (main_activity.xml), to the application logic, Android uses elements’ ‘ids ’; for example the ‘TextView’ element as an ‘id’ associated with ‘ bouncingTex t’ as illustrated by the following code lines: Figure 4 Connecting Variables Note : The figures illustrate how variables interconnect within the Andriod ecosystem. Screenshots This section demonstrates the output of the applications using GIF format images. Figure 5 Bouncing Text and Toggle Button Note: The figure GIF animation depicts the ‘ Hello Android! ’ text bouncing and changing color, from black to dark green after bouncing off the edge of the screen. It also depicts the user clicking on the toggle button to stop and restart the animation. Note that the button text all changes from ‘ Stop Animation ’ to ‘Start Animation ’ when first clicked and then from ‘ Start Animation ’ to ‘ Stop Animation ’ when clicked again. Figure 4 Icon and Home Buttons Note: The figure GIF animation depicts a user using the ‘Home’ and the application icon buttons. Note that the animation seems not to be fluid, this is due to the GIF animation looping, not the application animation itself. Summary The "Simple Hello Android App" project provided me with a good foundation for understanding Android development using Kotlin and XML. The process of creating the bouncing text animation and the toggle button helped me to become more familiar with Kotlin, how it is used to implement the application logic (MainActivity.kt), and how it interconnects with the XML UI design (using ConstraintLayout, TextView, and Button in activity_main.xml), and the application resources (e.g. strings.xml). Understanding the Android project structure, connecting UI elements to Kotlin code via resource ‘ids’, and managing a background thread for animation, were the most challenging parts of the project. Ultimately, the project provided me with a solid grasp of fundamental Android concepts and its ecosystem. References: App Dev Insights. (2023, December 16). Kotlin Type inference, Type check, Smartcast - App Dev Insights . Medium. https://medium.com/@appdevinsights/kotlin-type-inference-type-check-smartcast-49c5f98fac1b Developers (n.d.). Learn the Kotlin programming language. Android. https://developer.android.com/kotlin/learn Horton, J. (2019). Android programming with Kotlin for beginners. Packt Publishing. ISBN: 9781789615401 Ricciardi, A. (2025, February 14). Why Kotlin is preferred for Android app development . Level Up Coding │ Medium. https://medium.com/gitconnected/why-kotlin-is-preferred-for-android-app-development-c1d8f10ad95c