learning to hack

Calculated fields can include mathematical functions and concatenation. Using concatenation in most flavors of DBMS’ look like this:

SELECT last_name || 'Home#: ' || home_phone,
FROM personal_info
ORDER BY last_name;

Output would looks something like this:
Gordon-Carroll Home#: 801-123-1234

Using MYSQL is slightly different:
SELECT CONCAT(last_name, 'Home#:', home_phone)
FROM personal_info
ORDER BY last_name;

Using an Alias to name your calculated field:
SELECT prod_id, quantity, item_price,
quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 2010;

This sample performs a mathematical equation on two fields and then stores the calculated field using an alias of “expanded_price”. This example shows how a db calculates multiple quantities of a product in a shopping cart to get the total cost spent on that item.

Retrieving data using common SQL queries: SELECT column FROM table;

SELECT last_name, first_name, age
FROM personal_info;

Sorting retrieved data:
SELECT last_name, first_name, age
FROM personal_info
ORDER BY last_name, first_name DESC;

Filtering retrieved data:
SELECT last_name, first_name, age
FROM personal_info
WHERE age >= 18;

SELECT last_name, first_name, age
FROM personal_info
WHERE age BETWEEN 18 AND 30;

When using operators such as AND, OR remember to use parens to avoid collisions between operators.

SELECT last_name, first_name, age
FROM personal_info
WHERE age IN (18, 25, 30)
ORDER BY last_name, first_name;

IN works just like OR but has performance advantages and keeps syntax clean.

Using wildcards to filter data:

SELECT last_name, first_name, age
FROM personal_info
WHERE first_name LIKE 'Clint%';

SELECT last_name, first_name, age
FROM personal_info
WHERE first_name LIKE '[CBS]%'
ORDER BY first_name;

Output would look something like this:
Barrett
Christy
Clint
Stacy


You can negate a bracket wild card by using ^.