Here are some notes I’ve been making from the book SQL Cookbook Query Solutions and Techniques for All SQL Users (Anthony Molinaro, Robert de Graaf), specifically from chapter 2.

Chapter 1

Get all rows from a table: SELECT * FROM tableName;

Get rows fulfilling a certain condition: SELECT * FROM tableName WHERE condition; Example: SELECT * FROM employee WHERE deptno >= 10;

Get rows fulfilling multiple conditions: SELECT * FROM tableName WHERE condition AND condition; SELECT * FROM tableName WHERE condition OR condition; Example: In a table employee, you want to select employees with ids greater than 1000 with a salary under 200000 or with ids less than 500 with a salary over 400000 SELECT * FROM employee WHERE ( id > 1000 AND salary < 200 ) OR ( id < 500 AND salary > 400 );

Selecting a subset of columns from a table.  SELECT comma_separated_fields FROM tableName; Example. choose only the ids and department numbers from employee table: SELECT id, deptno FROM employee;

Changing names of columns from a table: SELECT column AS new_name … FROM tableName; Example: SELECT salary AS monthly_salary, id AS employee_id FROM employee;

What is a subquery in SQL?A subquery (or derived table) is a nested query within another query. Example: salary is stored as sal in the table, get salaries that are greater than 100000 with a column name of salary: SELECT * FROM (SELECT sal AS salary FROM employee ) AS temp WHERE salary > 100000; Note that subquery/derived tables have to be named in most flavors of SQL, including mysql and postgres. Hence the AS temp inclusion.

What is a view in SQL? A view in SQL is like a saved query (a virtual table) that you can treat as if it were a real table.It doesn’t store data itself (usually), instead it stores the definition of a SELECT statement, and whenever you query the view, SQL runs that query behind the scenes. Usually used when we want to run the query repeatedly. Example: CREATE VIEW emp_salaries AS SELECT sal AS salary FROM employee;

Concatenating Column values into a single column through string concatenation can be done, but require syntax specific to mysql or postgres. Example: Given a table employee with name and job columns, return entries in the form name works as a job under the title designation. Postgres: SELECT name || ’ works as a ’ || job AS designation FROM employee mysql: SELECT CONCAT(name, ’ works as a ’, job) AS designation FROM employee

Concatenation as mathematical operation has similar syntax in mysql and postgres. Example: Given years of experience as yoe and yearly salary as salary in a table employee, return the total earnings of each employee under the title earnings. SELECT yoe * salary AS earnings FROM employee

Performing if/else operations on values in a select statement can be done using the keywords CASE, WHEN, THEN and END. Example: from a table employee with salary and name, get a column status that has the value underpaid if the user makes less than 100 or overpaid if they earn over 400, and ok if in between. Also get the employee name. SELECT name, CASE WHEN salary < 100 THEN ‘underpaid’ WHEN salary >= 400 THEN ‘overpaid’ ELSE ‘ok’ END AS status FROM employee;

Limiting the number of rows returned from a query can be done using the LIMIT keyword in postgres and mysql. Example:  Get the first five rows from the table employee. SELECT * FROM employee LIMIT 5;

Get entries ordered by a particular column can be done by using the keywords ORDER, BY, ASC and DESC. Note that ASC is the default. Example: get rows from employee ordered in decreasing order of employee age.

SELECT * FROM employee ORDER BY age DESC;

Getting a set of random entries in this manner from a table has separate syntax in mysql and postgres: Example: get five random entries from table employee. mysql: SELECT * FROM employee ORDER BY RAND() LIMIT 5; postgres: SELECT * FROM employee ORDER BY RANDOM() LIMIT 5 Note that this works for functions like RAND and RANDOM in each flavor. Using a constant in this position will change the output to order by the nth column in the select statement.  Example: SELECT name, age, salary FROM employee ORDER BY 2; This orders the rows by age.

NULL is a special value in SQL. It is never equal or not equal to anything, including itself, so you can’t use = or != to check for it. Checking for null has to be done using the keywords IS, NULL and NOT. Example: Get all entries which have non null years of experience (yoe) from employee. SELECT * FROM employee WHERE yoe IS NOT NULL;

Since NULL can’t be compared to easily, it’s worth learning how to convert it to not NULL values. For this we can use the COALESCE keyword. The COALESCE keyword returns the first non NULL value in the list of values passed to it, or returns 0. This is preferred as it works for all flavors of SQL. We can also use the CASE keyword above. Examples: select years of experience (yoe) and name from employee, substituting 0 for null in yoe. SELECT COALESCE(yoe, 0), name FROM employee SELECT CASE WHEN yoe IS NOT NULL THEN yoe ELSE 0 END FROM employee

Searching for ranges of numbers can be done using the IN keyword. Example: select all columns that have salaries in the range 100 to 200 from employee: SELECT * FROM employee WHERE salary IN (100,200);

Searching for patterns in strings can be done by using LIKE keyword and operators % (any number of wildcard characters), _ (single wildcard character) Example: select all rows from employee that have a name that starts with the letter A. SELECT * FROM employee WHERE name LIKE ‘A%’

select all rows from employee that have a name that starts with the letter A and are five letters long. SELECT * FROM employee WHERE name LIKE ‘A____’