How to use value from row derived column from a table in another query?

SELECT...INTO can be used to store the single row column result value from a query to be used in another query depending on that value.

"accounts" table

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| account_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| account_name | varchar(50) | NO   |     | NULL    |                |
| balance      | float       | NO   |     | NULL    |                |
| state        | varchar(50) | NO   |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

Sample data

+------------+--------------+---------+-------+
| account_id | account_name | balance | state |
+------------+--------------+---------+-------+
|          1 | Rico         |     500 | IL    |
|          2 | Mark         |     100 | NY    |
|          3 | Rita         |      50 | NY    |
+------------+--------------+---------+-------+

Lets say we want to get average balance of all accounts in the state of NY from the above table and then use that value in another query.

mysql> SELECT AVG(balance) INTO @avg_bal
    -> FROM accounts
    -> WHERE state = 'NY'
    -> GROUP BY state;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT CONCAT('Average balance in NY is ', @avg_bal) AS result;
+-----------------------------+
| result                      |
+-----------------------------+
| Average balance in NY is 75 |
+-----------------------------+
1 row in set (0.00 sec)

The above use case arises when we have 2 or more queries depending on each others' output inside a stored procedure.

Below is how the stored procedure would look:

DELIMITER $$

DROP PROCEDURE IF EXISTS get_ny_avg_bal$$

CREATE PROCEDURE get_ny_avg_bal()
BEGIN
    DECLARE avg_bal FLOAT;

    SELECT AVG(balance) INTO avg_bal
    FROM accounts
    WHERE state = 'NY'
    GROUP BY state;

    SELECT CONCAT('Average balance in NY is ', avg_bal) AS result;

END$$

DELIMITER ;

mysql> CALL get_ny_avg_bal();
+-----------------------------+
| result                      |
+-----------------------------+
| Average balance in NY is 75 |
+-----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Use of loops in MySQL stored procedures

Loops -> Repetitive tasks

3 types of loops in MySQL stored program:
- Simple loop using LOOP and END LOOP clauses
- WHILE and END WHILE clauses that runs while a condition is true
- REPEAT  and UNTIL clauses that runs until a condition is true

Termination of loop -> LEAVE statement.

I will demonstrate the use of loops the simple loop way.

Simple counter from 1 to 10:

Store below stored procedure in a file named my_loop.sql

DELIMITER $$

DROP PROCEDURE IF EXISTS my_loop$$

CREATE PROCEDURE my_loop()
BEGIN
    DECLARE counter INT DEFAULT 0;

    my_loop: LOOP
        SET counter = counter + 1;

        IF counter = 10 THEN
            LEAVE my_loop;
        END IF;
    END LOOP my_loop;

    SELECT CONCAT('Done counting up to ', counter) AS result;

END$$

DELIMITER ;

Execute the stored procedure:

mysql> SOURCE my_loop.sql

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL my_loop();

+------------------------+

| result                 |

+------------------------+

| Done counting up to 10 |

+------------------------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Explaining simply, we are defining a counter that starts with 0 and a LOOP labelled my_loop that increments from 0 onward and a condition inside to check for counter value of 10, when the loop terminates through the use of LEAVE clause. You can give a try to the other loop clauses :)

Using conditional expressions inside MySQL Stored Procedure

Sometimes there is need to use conditional expression inside stored procedure to control the flow of execution.
We can use IF or CASE statements for this.

Below is a stored procedure to check the performance of a student based on its score.
Store the below stored procedure in a file called get_performance.sql

DELIMITER $$

DROP PROCEDURE IF EXISTS get_performance$$

CREATE PROCEDURE get_performance
    (score NUMERIC(8, 2),
    OUT result VARCHAR(11))

BEGIN
    IF (score >= 90) THEN
        SET result = 'OUTSTANDING';
    ELSEIF (score >= 70 AND score < 90) THEN
        SET result = 'AWESOME';
    ELSEIF (score >= 60 AND score < 70) THEN
        SET result = 'GOOD';
    ELSEIF (score >= 40 AND score < 60) THEN
    SET result = 'OK';
    ELSE
    SET result = 'FAIL';
    END IF;

END$$

DELIMITER ;

Execute the procedure:

mysql> SOURCE get_performance.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Execute the call:

mysql> CALL get_performance(67, @result);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @result;
+---------+
| @result |
+---------+
| GOOD    |
+---------+
1 row in set (0.00 sec)

Basically IF, ELSEIF check for conditions, ELSE matches any condition which has not been matched by any preceding condition and finally THEN executes the set of statements for the satisfied condition and breaks out of the conditional expression. Similarly, we can use CASE for switching to statement sections based on equality comparison with a set of unique values.

How to use and get output arguments in stored procedure using OUT parameter mode?

It is sometimes required to get the result of a stored procedure in a variable which can be used later or to output the result.

To do so, we can use the "OUT" parameter mode while defining stored procedures.

In the below section, we will be writing a stored procedure to get the square root of a number returned in an output variable provided by us.

Stored Procedure Definition:

Store the below stored procedure in a file named my_sqrt.sql and save it.

DELIMITER $$

DROP PROCEDURE IF EXISTS my_sqrt$$

CREATE PROCEDURE my_sqrt(inp_number INT, OUT op_number FLOAT)
BEGIN
    SET op_number=SQRT(inp_number);
END$$

DELIMITER ;

Execute Stored Procedure:

Open your mysql client in terminal and run the above procedure using the below command:

mysql> SOURCE my_sqrt.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Then call the stored procedure to get square root of 100 in an output variable called @sqrt:

Get square root of 100:

mysql> CALL my_sqrt(100, @sqrt);
Query OK, 0 rows affected (0.00 sec)

Now the output of the sqrt operation is stored in @sqrt variable which can be used by us as we desire.

mysql> SELECT @sqrt;
+--------+
| @sqrt  |
+--------+
|   10   |
+--------+
1 row in set (0.00 sec)

That's all it takes :)

A few tips while you start or work your node project

There are few good practices one can follow while starting a node app / project.

1. One of them, I learned today, is that it's a good practice to have a package.json file which keeps info related to app and dependencies. You can do so using the below command:

$ npm init

It will ask for a couple of questions. You can click enter to skip them and use the default values instead.

After you are done, you can see a "package.json" file created in your current directory. You might want to edit package.json file and add "private" property to the json list as below to prevent the json being published to npm.

"private" : false

2. The above step will basically help us and others, who are added to work on the project, setup the project initially to get a brief idea about the app and its dependencies and also they can install the related dependencies listed in it using the below command:

$ npm install

It helps in installing the version used in the app instead of the latest. That's important since there might be api changes to the package and it might not be working with the current app setup.

3. Another practice one can follow is to save the version to the package.json when you install any package. You can do so by using the "--save" option with "npm install" command. For example:

$ npm install express --save

The above command will save "express" as a dependency with the installed version mentioned.

This has helped me a lot and is quite interesting :)

Review Board - The Perfect Review System For You

If you have a big team, and manually asking or attending to peer code reviews in your team is a hassle, then "Review Board" is there for you.

Site

"Review Board makes code review easy
Saving time, money, and sanity"


"Yes, Review Board is absolutely free for commercial use."

Faqs

It's a python utility and easy to install in ubuntu and any linux system through easy_install
http://www.reviewboard.org/docs/manual/dev/admin/installation/linux/

You can setup it in your local and play around with your team in a local setup :)

Here's a demo

Some features:
1. Admin panel to manage reviews, users, groups and other customization
2. Review groups to whom review request can be sent.
3. User authorization preventing outside access.
4. Sending review requests to different members and group users easily.
5. Easy issue and tracking of comments on diffs by reviewers and reviewees.
6. Every diff review, comment sent through mail to required users and admin making it trackable and easy to follow.
7. And many others features.

Excerpts from site:
- Easily track your team's review requests
- Not your typical diff viewer
- Upload and review all kinds of files
- Comment on anything
- Keep track of everything that needs fixing
- All your history, in one place
- Birds-eye view of the server
- Helpful command-line tools
- Extensible

Get rocking :)

How to structure and design a relational database to support you data storage needs?

Well, every now and then, when we began to start a new project or app, which has some data storage requirement, we have a deep intriguing thought as to how best represent the data structure so as to support a variety of needs including but not limited to (ACID rules):

1. Normalization
2. Reliability
3. Consistency
4. And many others

Below, I provide a set of steps which you can follow to arrive at a data model that correctly suites your requirements.

Steps:

1. Identify the project or app requirements / specifications and business rules which tell you what your app will be able to do when it is ready.
2. From these business rules, identify possible objects for each business rule and mark them in a paper using rectangular sections like authors, posts etc.
3. Once you have recognized the entities and attributes, it's time to normalize the data structure and define the tables and columns.
4. After this, we now have to recognize the relationships between tables that is whether it is a one to one, one to many or many to many relationship like authors and posts have a one to many relationship.
5. Finally, we have to re-access the data model for any possible inconsistencies and refine data that is not yet normalized like any relationship with many to many relationship being moved to different tables with the help of a junction table.

So, at the end of the fifth step, we have a data model with which we can go ahead and build our database.

Now,
1. It satisfies the normalization rules
2. It represents relationships correctly between various entities taking into account the business rules of the application
3. It prevents redundancy
4. And satisfies the ACID rules

Maybe a simple theoretical explanation is not enough for we practical nerds. In a probable future post, I will explain the steps with a practical example. Stay tuned in. :)