Common Task performed on Data with SQL Queries

Some useful Snippets

While working with multiple database vendors, slight differences in syntax can cause the query to return unexpected results. For my own quick reference, reference I have decided to jot down a few select statements that perform some common task well in MySQL.

The tables used for the examples can be created with the following statements:

CREATE TABLE `DEPT` (
	`DEPTNO` INT(11) NOT NULL,
	`DNAME` VARCHAR(50) NULL DEFAULT NULL,
	`LOC` VARCHAR(50) NULL DEFAULT NULL,
	PRIMARY KEY (`DEPTNO`)
)
ENGINE=InnoDB;
CREATE TABLE `EMP` (
	`EMPNO` INT(11) NOT NULL,
	`ENAME` VARCHAR(50) NULL DEFAULT NULL,
	`JOB` VARCHAR(50) NULL DEFAULT NULL,
	`MGR` INT(11) NULL DEFAULT NULL,
	`HIREDATE` DATE NULL DEFAULT NULL,
	`SAL` INT(11) NULL DEFAULT NULL,
	`COMM` INT(11) NULL DEFAULT NULL,
	`DEPTNO` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`EMPNO`)
)
ENGINE=InnoDB;
CREATE TABLE `p1` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`id`)
)
ENGINE=InnoDB
AUTO_INCREMENT=1;
CREATE TABLE `p10` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`id`)
)
ENGINE=InnoDB
AUTO_INCREMENT=1;

Tables p1 and p2 are just filled sequential id’s that can be used as a pivot. The mock data in the EMP and DEPT table is pretty self-explanatory and can be filled with whatever you like by using a data generator like Mockaroo. You can use the following statements to add the test data I am using.

INSERT INTO `dept` (`DEPTNO`, `DNAME`, `LOC`) VALUES
	(10, 'ACCOUNTING', 'NEW YORK'),
	(20, 'RESEARCH', 'DALLAS'),
	(30, 'SALES', 'CHICAGO'),
	(40, 'OPERATIONS', 'BOSTON');

INSERT INTO `emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES
	(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20),
	(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30),
	(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30),
	(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20),
	(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30),
	(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30),
	(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10),
	(7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, NULL, 20),
	(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10),
	(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30),
	(7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, NULL, 20),
	(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30),
	(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20),
	(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);

Select Random Data Sample

SELECT ename,job 
FROM emp 
ORDER BY RAND() 
LIMIT 5;

Transform NULL Value into Real Value

SELECT COALESCE(comm,0) 
FROM emp;

Sort Column with NULL Values using Record Flag

SELECT ename,sal,comm FROM (
SELECT ename,sal,comm,
case when comm IS NULL then 0 ELSE 1 END AS is_null
FROM emp
) t1
ORDER BY is_null desc, comm asc;

Data Dependent Dynamic Sort

SELECT ename,sal,job,comm,
case when job='SALESMAN' then comm ELSE sal END AS ordered
FROM emp
ORDER BY 5;

UNION ALL-Append Rows Including Duplicates

SELECT ename AS ENAME_DNAME, deptno
FROM emp
UNION ALL
SELECT '-----',NULL
FROM p1
UNION ALL
SELECT dname,deptno
FROM dept;

UNION-Append Rows Excluding Duplicates

SELECT deptno
FROM emp
UNION
SELECT deptno
FROM dept;

EQUI-JOIN Append All-Combinations of Columns from Multiple Tables

SELECT e.ENAME,d.LOC,e.DEPTNO AS emp_deptno, d.DEPTNO AS dept_deptno
FROM emp e, dept d
WHERE e.DEPTNO=10;

INNER-JOIN Append Columns from Multiple Tables that Match the Condition

SELECT e.ENAME,d.LOC,e.DEPTNO AS emp_deptno, d.DEPTNO AS dept_deptno
FROM emp e, dept d
WHERE e.DEPTNO=10 AND e.DEPTNO=d.DEPTNO;

INTERSECT Tables Returns Rows Common in All Tables

(v is a temp table for demonstration)

SELECT e.EMPNO, e.ENAME, e.JOB,e.SAL, e.DEPTNO
FROM emp e, (
SELECT ENAME,JOB,SAL FROM emp WHERE JOB='CLERK'
) v
WHERE e.ENAME=v.ENAME 
AND e.SAL=v.SAL 
AND e.JOB=v.JOB;

Find Values that are in One Table but not the Other

(not NULL-safe)

SELECT DISTINCT DEPTNO 
FROM dept
WHERE
deptno NOT IN (SELECT deptno FROM emp);

(NULL-safe)

SELECT d.DEPTNO 
FROM dept d
WHERE
NOT EXISTS 
(SELECT 1 
FROM emp e
WHERE d.DEPTNO=e.DEPTNO
);

(using OUTER JOIN)

SELECT e.ENAME,e.DEPTNO AS emp_deptno,d.* 
FROM dept d 
LEFT OUTER join emp e
ON(d.DEPTNO = e.DEPTNO)
WHERE e.DEPTNO IS NULL;

Iterate Through a String using Cartesian Product

SELECT SUBSTR(t1.ENAME, iter.pos,1) AS CHR from
(SELECT e.ENAME 
FROM emp e
WHERE e.ENAME='MARTIN') t1,
(SELECT id AS pos FROM p10) iter
WHERE iter.pos <= LENGTH(t1.ENAME);

Count Occurrences in a String Value

SELECT 
(length('HELLO HELLO')-LENGTH(REPLACE('HELLO HELLO','LL','')))/LENGTH('LL') AS cnt
FROM p1

Running Total Column

SELECT e1.ENAME, e1.SAL,
(SELECT sum(e2.SAL) 
FROM emp e2
WHERE e2.EMPNO<=e1.EMPNO
) AS running_total
from emp e1
ORDER BY 3;

Running Product Column

Note this method uses log properties
SELECT e1.ENAME, e1.SAL,
(SELECT exp(sum(ln(e2.SAL)))
FROM emp e2
WHERE e2.EMPNO<=e1.EMPNO
) AS running_product
from emp e1
ORDER BY 3;SELECT e1.ENAME, e1.SAL, (SELECT sum(e2.SAL) FROM emp e2 WHERE e2.EMPNO<=e1.EMPNO ) AS running_total from emp e1 ORDER BY 3;