A Sequel To SQL

Working in Ruby till the past week, I have become accustomed to there being not one but many solutions to a problem. Just like there are many ways to express yourself in words, there were many ways to achieve a singular outcome in code using Ruby. SQL on the other hand is much more strict and rigid. In this blog post I review the structure of a basic query using SQLite3 and also take a look further by exploring nested queries and triggers.


Review

A basic query in SQL follows a very set structure:

SELECT <columns>
FROM <table>
JOIN <table>
ON <join conditions>
WHERE <filter conditions>
GROUP BY <category>
HAVING <filter categories>
ORDER BY <sorting conditions>;

All queries include the SELECT and FROM keywords. A comma separated list of column names (or * if all columns are wanted) follows the SELECT keyword. The table on which the query should be run follows the FROM keyword. After these keywords the option of joining with another table, filtering the rows, grouping by a category, and ordering the resulting rows become available. Although these keywords are optional, when used they must be in the strict ordering shown above.

JOINS

A join allows for the merging of two tables on a set condition. This condition generally is an equality condition which tests whether the foreign key of one table matches the primary key of another. These keys for most tables in SQLite3 are ID’s which are generated when a row is placed into a table. A join allows for queries that depend on relationships between two tables. For example in the context of online ordering say we have a customers table and an orders table. The customer’s info alongside the customer’s orders can be output running the following query utilizing a join.

customers: id, name, address, payment, age, phone_number
orders: id, item, quantity, price, seller_id, customer_id
SELECT customers.name, customers.address, customers.phone_number, orders.price, orders.item, orders.quantity
FROM orders
JOIN customers
ON orders.customer_id = customers.id;
A SQL query walks into a bar and sees two tables. He walks up to them and says ‘Can I join you?’

GROUP BY and HAVING

A group by allows for grouping by a category, or equal column values. It is generally utilized alongside an aggregate function. For example consider a table which contains information regarding all the paychecks made out by a company to its employees. The following query would result in a table containing the employee’s id alongside their salary to date.

paychecks: id, employee_id, date, amount
SELECT employee_id, SUM(amount) as salary
FROM paychecks
GROUP BY employee_id;

HAVING is used in conjunction with GROUP BY in order to filter the resulting categories. HAVING is a keyword used for filtering results similar to WHERE but applies to groups whereas WHERE applies to column values. Extending on the paycheck example from above, a table containing all the salaries to date for only those who made over $50,000 could be generated with the following query.

SELECT employee_id, SUM(amount) as salary
FROM paychecks
GROUP BY employee_id
HAVING salary > 50000;

ORDER BY

ORDER BY is used to sort the resulting rows on a certain column value; multiple columns separated by commas can be placed as a sorting condition in order to sort by secondary and tertiary columns. The rows can be sorted in ascending on descending by adding the ASC or DESC keyword after the sorting condition. Finally the LIMIT <num> keyword can be appended at the very end of the ORDER BY clause to only list the top or bottom <num> results.


Nested Queries

A nested query or sub-query can be used to return data that will be further used in a main or parent query.

Conditions:

  • A sub-query’s SELECT can only contain columns which are also in the parent query’s SELECT
  • An ORDER BY statement can’t be used in the sub-query but a GROUP BY statement can be used to achieve the same result

Use with SELECT:

SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])

The OPERATOR can be replaced with =, <, >, >=, or <= in cases where the sub-query results in a singular row. In cases where the sub-query results in multiple rows, OPERATOR can be replaced with IN.

Use with INSERT:

INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2 ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ]

Use with UPDATE:

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]

Generally nested queries are avoided because they can be rewritten as queries using a join and are less efficient than queries utilizing a join. Due to this they are usually only used in conjunction with UPDATE.


TRIGGERS

TRIGGERS in SQLite3 are functions that get called when the event they are associated with occurs. The event which causes the trigger can be specified to be an UPDATE OF, INSERT INTO, or DELETE ON on a specified table. The BEFORE or AFTER keyword can be appended to the trigger to call the function before or after the event which causes the trigger.

Syntax:

CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name 
ON table_name
BEGIN
-- Trigger logic goes here....
END;

Here is an example using TRIGGERS to keep a backup table with a copy of the data in the employees table.

CREATE TRIGGER backup AFTER INSERT ON employees
BEGIN
INSERT INTO employees_bkp(id, entry_date, name, phone_number, salary)
VALUES(new.id, datetime('now'), new.name, new.phone_number, new.salary)
END;
CREATE TRIGGER backup_upd AFTER UPDATE OF employees
BEGIN
UPDATE employees_bkp
SET id = new.id, entry_date = datetime('now'), name = new.name, phone_number = new.phone_number, salary = new.salary
END;
CREATE TRIGGER backup_del BEFORE DELETE ON employees
BEGIN
DELETE FROM employees_bkp
WHERE id = old.id
END;

Validation Example:

CREATE TRIGGER trigger_validate_username BEFORE INSERT ON users
WHEN LENGTH(pwd) > 15
BEGIN
SELECT RAISE(ABORT,'Password must be less than 15 characters');
END;

The example above would abort and display an error message on any insertion where the user being inserted has a password length less than 15.

TRIGGER Maintenance:

All the TRIGGER names can be listed using the following query:

SELECT name FROM sqlite_master
WHERE type = 'trigger';

Triggers are automatically dropped when the table they are associated with is dropped. They can also be dropped using the following command:

DROP TRIGGER trigger_name;

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.