---

SQL Queries

Now, we can create something a bit more interesting in the way of SQL commands. Say we want to look at how much support each developer is putting in. We can create a new SQL command window and enter the SQL directly:

SELECT support_hours.Hours, devs.First_Name 
FROM devs INNER JOIN support_hours 
ON devs.id = support_hours.dev_id;

Clicking Run SQL will generate the following results:

10	Bill
4	Joe
4	Peter
15	Candy
10	Amber
5	Peter

The grid of results that is displayed in NetBeans is pretty cool too. You can copy and paste individual values or the whole table into a spreadsheet, you can even re-order the columns if you want.

Back on the Services tab we can do more than just look at the structure of the database. If we want we can add and delete tables and columns, look at the structure of the database in detail, export the structure to external files and much more.

What if we want to create some stored procedures or functions in MySQL. Can NetBeans help us there as well? The answer is a yes, because we can embed the commands to create the procedure or function in the sql_commands.sql file as follows:

DROP FUNCTION IF EXISTS above_avg_support;
DELIMITER $$
CREATE FUNCTION above_avg_support (hrs NUMERIC) RETURNS INTEGER DETERMINISTIC
BEGIN
    DECLARE a_s NUMERIC;
    DECLARE res INTEGER;
    SELECT Avg(Hours) INTO a_s FROM support_hours;
    SET res=hrs >= a_s;
    RETURN res;
END $$

The first thing we do is drop the function if it exists and then reset the delimiter character so that we can differentiate between what's in the stored procedure and what's in the script file. Once this has been executed we can just use the stored function in a standard SQL query (which we execute from within NetBeans, naturally):

SELECT above_avg_support(support_hours.Hours), devs.First_Name 
FROM devs INNER JOIN support_hours 
ON devs.id = support_hours.dev_id;

The results of which tell us which of our developers have gone beyond the call of duty in doing user support…

So, as we can see as a MySQL front end NetBeans has plenty of features and functionality. And of course in development it means we can make mistakes, trash the data or the database structure and just re-run our SQL scripts to get things back to where we want them. All from within the same IDE we'll use for writing the Java/Ruby/PHP/whatever code that will use that database. And, of course, the SQL scripts are part and parcel of the project, so they can be version controlled, refactored and developed along with the application.