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 4.
Chapter 4
Inserting a new record has the following format:
INSERT INTO tableName (columnNameOne, columnNameTwo,…) VALUES (valueOne, valueTwo, valueThree)
For mysql and postgres there is the option of adding multiple rows at a time by including multiple values:
INSET INTO tableName (Columns…) (ValueSetOne…) (ValueSetTwo…)…;
Inserting default values: A table can be defined to take default values for specific columns. This is done by using the DEFAULT keyword. For instance,
CREATE TABLE tableName (id INTEGER DEFAULT 0); It sets the default value of id to be zero.
This can be used in some creative ways: INSERT INTO tableName VALUES (DEFAULT); or INSERT INTO tableName (columnName…) VALUES (DEFAULT) For instance, INSERT INTO D (id) VALUES (DEFAULT)
mysql allows you to specify an empty values list if all columns have a default value defined: INSERT INTO tableName VALUES ();
postgres supports a DEFAULT VALUES clause: INSERT INTO tableName DEFAULT VALUES;
Suppose you want to create a row with a column value of null, when it has a default value. This can be done by explicitly passing nulli as a value.
INSERT INTO d (id, foo) VALUES (null, ‘foo’);
Suppose we want to copy rows from one table to another table. This can be done using subqueries, something we’ve looked at before:
INSERT INTO tableName2 (columnNames) SELECT columnNames FROM tableName1 WHERE condition; INSERT INTO dept_east (deptno, dname, loc) SELECT deptno, dname, loc FROM dept WHERE loc IN (‘NEW YORK’, ‘BOSTON’)
Suppose we want to create a new table That has the same table definition as the first table (i.e. same columns and datatypes for columns). For mysql and postgres, we use a subquery which returns no values. CREATE TABLE tableName2 AS SELECT * FROM tableName1 WHERE 1 = 0;
Inserting into multiple tables at once; suppose you want to take values from one table and insert them into multiple tables at the same time. This is not a feature currently supported by mysql or postgres. Oracle and DB2 do support it, but that’s not within the purview of this series.
If we want to ensure that only some columns in a table can have values inserted into them, we use a view exposing only those columns, and force all inserts to go through that view. We’ve discussed views before as being virtual tables that can be reused.
CREATE VIEW view AS SELECT columnNames… FROM tableName; for Example: CREATE VIEW new_emps AS SELECT empno, name, job FROM employee; Insert statements to the view get translated by the database to delve into the underlying table. for instance, the following query: INSERT INTO new_emps (empno, name, job) VALUES (1, ‘John’,‘Editor’); will get translated into: INSERT INTO emp (empno, name, job) VALUES (1, ‘John’, ‘Editor’);
Note that view insertion is a topic that grows in complexity very quickly.
Suppose we want to modify all or some values in a table. We can use the UPDATE statement for this. For example, if we wanted to increase the salary of all employees in deptno 20, we’d do the following: UPDATE emp SET sal = sal*1.10 WHERE deptno = 20;
Suppose we want to update rows in one table correspoinding to rows that exist in another table. This can be done easily using subqueries; UPDATE tableName SET updateModification WHERE updateCondition; For instance, to increase salaries of people in employees when they are in another table emp_bonus, UPDATE emp.SET sal = sal*1.20 WHERE empno IN (SELECT empno FROM emp_bonus)
This can also be done using EXISTS, in a slightly confusing manner, that I’ll cover here just so we see the pattern: UPDATE emp SET sal = sal * 1.20 WHERE EXISTS ( SELECT NULL FROM emp_bonus WHERE emp.empno = emp_bonus.empno) Here, the WHERE clause is what drives the update, not the value returned.
It’s worth discussing the MERGE clause, which exists in postgres and other flavors of sql, except mysql. If we wanted to insert or update a record, and carry out actions based on the output of said action, we can use MERGE for postgres. for example, say we wanted to check whether a given row in employee has a match in emp_commission, then execute a delete if the result of an UPDATE causes a commission that’s too high.
We would have: MERGE INTO emp_commission ec USING employee emp ON ec.empno = emp.empno WHEN MATCHED AND emp.sal < 2000 THEN DELETE WHEN MATCHED THEN UPDATE SET comm = 1000 WHEN NOT MATCHED THEN INSERT (empno, name, deptno, comm) VALUES (emp.empno, emp.ename, emp.deptno, emp.commission);
The explanation for this statement is as follows:
MERGE INTO emp_commission ec We’re telling Postgres: “Target table is emp_commission, alias it as ec.” USING employee emp The source table is employee, alias emp. Postgres will try to “merge” rows between emp_commission (target) and employee (source). ON ec.empno = emp.empno Defines the match condition. If a row in emp_commission has the same empno as a row in employee, it’s considered a “match”. If no match, it falls into the “not matched” case.
WHEN MATCHED AND emp.sal < 2000 THEN DELETE If a row matches and the employee’s salary is < 2000 → delete the row from emp_commission. Notice: in Postgres, you can’t write DELETE WHERE … inside MERGE. Instead, you put the condition in the WHEN MATCHED AND … clause.
WHEN MATCHED THEN UPDATE SET comm = 1000 if a row matches and salary ≥ 2000 (since the <2000 case was already handled) → update comm to 1000. This only touches rows that exist in both emp_commission and employee.
WHEN NOT MATCHED THEN INSERT … If there’s a row in employee with no corresponding row in emp_commission → insert a new row. The insert pulls values from employee (emp.empno, emp.ename, etc.) and populates them into emp_commission.
Coming to deletion, deleting all records from a table has the following format: DELETE FROM tableName; Given a condition, this changes to: DELETE FROM tableName WHERE condition;
If we were looking to delete records when they refer to non existent records in some other table, we would do the following: DELETE FROM TableName WHERE NOT EXISTS ( SELECT NULL FROM TableName2 WHERE conditionOfExistence )
note that here conditionOfExistence is something like dept.deptno = employee.deptno
As an aside we can replace NULL with * or 1 in this condition, as EXISTS only checks whether the condition is met, and does not care what the return is. Alternatively we can use the NOT IN predicate: DELETE FROM employee WHERE deptno NOT IN (SELECT deptno FROM dept)
Suppose we want to get rid of duplicates in a table, we could use the MIN aggregation function to single out a single record to spare and delete others in the pattern. For postgres: DELETE FROM dupes WHERE id NOT IN (SELECT MIN(id) FROM dupes GROUP BY name) This preserves one entry for each name. For mysql, we use different syntax as we can’t reference the same table twice in a delete: DELETE FROM dupes WHERE id NOT IN (SELECT MIN(id) FROM (SELECT id, name FROM dupes) AS tmp GROUP BY NAME)