SQL Code Example


SQL Code Example

CRUD statement

INSERT INTO person (person_id, fname, lname, gender, birth_date)

VALUES (null, 'William','Turner', 'M', '1972-05-27');

UPDATE person SET street = '1225 Tremont St.',

city = 'Boston', state = 'MA', country = 'USA', postal_code = '02138'

WHERE person_id = 1;

DELETE FROM person WHERE person_id = 2;

DROP TABLE favorite_food;

The group by and having Clauses[key point]

Count all the employees in each department and returns the names of those departments having more than two employees:

SELECT d.name, count(e.emp_id) num_employees

FROM department d INNER JOIN employee e

ON d.dept_id = e.dept_id

GROUP BY d.name

HAVING count(e.emp_id) > 2;

The order by Clause

Ascending Versus Descending Sort Order[Asc, Desc]

Filtering


Using Parentheses

Using the not Operator

Condition Types: =, <>, <, >

DELETE FROM account WHERE status = 'CLOSED' AND YEAR(close_date) = 2002;

The between operator(Number, String, Date)

Membership Conditions(in, not in)

Using wildcards(LIKE)

_ Exactly one character

% Any number of characters (including 0)

Using regular expressions

SELECT emp_id, fname, lname FROM employee WHERE lname REGEXP '^[FG]';

Null(IS [not] NULL)

Querying Multiple Tables[Join]

Self-Joins

List every employee’s name along with the name of his or her manager:

SELECT e.fname, e.lname, e_mgr.fname mgr_fname, e_mgr.lname mgr_lname

FROM employee e INNER JOIN employee e_mgr ON e.superior_emp_id = e_mgr.emp_id;

Using the Same Table Twice

SELECT a.account_id, e.emp_id, b_a.name open_branch, b_e.name emp_branch

FROM account a INNER JOIN branch b_a ON a.open_branch_id = b_a.branch_id

INNER JOIN employee e ON a.open_emp_id = e.emp_id

INNER JOIN branch b_e ON e.assigned_branch_id = b_e.branch_id

WHERE a.product_cd = 'CHK';

Using Subqueries As Tables

Non-Equi-Joins

List all possible chess matches:

SELECT e1.fname, e1.lname, 'VS' vs, e2.fname, e2.lname

FROM employee e1 INNER JOIN employee e2 ON e1.emp_id < e2.emp_id

WHERE e1.title = 'Teller' AND e2.title = 'Teller';

Grouping and Aggregates[key point]

SELECT open_emp_id, COUNT(*) how_many FROM account

GROUP BY open_emp_id HAVING COUNT(*) > 4;

Aggregate Functions

Max(), Min(),Avg(), Sum(), Count()

SELECT product_cd, MAX(avail_balance) max_balance,

MIN(avail_balance) min_balance, AVG(avail_balance) avg_balance,

SUM(avail_balance) tot_balance, COUNT(*) num_accts

FROM account GROUP BY product_cd;

Counting Distinct Values

SELECT COUNT(DISTINCT open_emp_id) FROM account;

SELECT product_cd, open_branch_id, SUM(avail_balance) tot_balance

FROM account GROUP BY product_cd, open_branch_id;

Grouping via Expressions

SELECT EXTRACT(YEAR FROM start_date) year, COUNT(*) how_many

FROM employee GROUP BY EXTRACT(YEAR FROM start_date);

Group Filter Conditions[having]

Subqueries

SELECT account_id, product_cd, cust_id, avail_balance

FROM account WHERE account_id = (SELECT MAX(account_id) FROM account);

Subquery Types

Noncorrelated Subqueries

Single-Row, Single-Column Subqueries

This type of subquery is known as a scalar subquery and can appear on either side of a condition using the usual operators (=, <>, <,>, <=, >=).

Multiple-Row, Single-Column Subqueries[in and not in, all, any]

List which employees supervise other employees:

SELECT emp_id, fname, lname, title FROM employee

WHERE emp_id IN (SELECT superior_emp_id FROM employee);

SELECT emp_id, fname, lname, title FROM employee

WHERE emp_id NOT IN (SELECT superior_emp_id

FROM employee WHERE superior_emp_id IS NOT NULL);

Find all employees whose employee IDs are not equal to any of the supervisor employee IDs:

SELECT emp_id, fname, lname, title FROM employee

WHERE emp_id <> ALL (SELECT superior_emp_id

FROM employee WHERE superior_emp_id IS NOT NULL);

Multicolumn Subqueries

Find all accounts whose balances don’t match the sum of the transaction amounts for that account.

Correlated Subqueries

List customers having exactly two accounts

SELECT c.cust_id, c.cust_type_cd, c.city FROM customer c

WHERE 2 = (SELECT COUNT(*) FROM account a WHERE a.cust_id = c.cust_id);

The exists Operator

Find all the accounts for which a transaction was posted on a particular day

SELECT a.account_id, a.product_cd, a.cust_id, a.avail_balance FROM account a

WHERE EXISTS (SELECT 1 FROM transaction t WHERE t.account_id = a.account_id AND t.txn_date = '2008-09-22');

UPDATE account a SET a.last_activity_date =

(SELECT MAX(t.txn_date) FROM transaction t WHERE t.account_id = a.account_id);

WHERE EXISTS (SELECT 1 FROM transaction t WHERE t.account_id = a.account_id);

Remove data from the department table that has no child rows in the employee table:

DELETE FROM department WHERE NOT EXISTS

(SELECT 1 FROM employee WHERE employee.dept_id = department.dept_id);

Subqueries in Filter Conditions

Find the employee responsible for opening the most accounts:

SELECT open_emp_id, COUNT(*) how_many FROM account GROUP BY open_emp_id

HAVING COUNT(*) = (SELECT MAX(emp_cnt.how_many) FROM (SELECT COUNT(*) how_many

FROM account GROUP BY open_emp_id) emp_cnt);

Working with Sets

Set Operators[union, union all, intersect, except, ]

union all operator doesn’t remove duplicates.

SELECT 'IND' type_cd, cust_id, lname name FROM individual

UNION ALL

SELECT 'BUS' type_cd, cust_id, name FROM business;

Conditional Logic

CASE

WHEN C1 THEN E1

WHEN CN THEN EN

[ELSE ED]

END

CASE V0

WHEN V1 THEN E1

WHEN VN THEN EN

[ELSE ED]

END

SELECT

SUM(CASE WHEN EXTRACT(YEAR FROM open_date) = 2010 THEN 1 ELSE 0 END) year_2010,

SUM(CASE WHEN EXTRACT(YEAR FROM open_date) = 2011 THEN 1 ELSE 0 END) year_2011,

FROM account WHERE open_date > '1999-12-31' AND open_date < '2006-01-01';

Find accounts whose account balances don’t agree with the raw data in the transaction table.

SELECT CONCAT('ALERT! : Account #', a.account_id,

' Has Incorrect Balance!')

FROM account a

WHERE (a.avail_balance, a.pending_balance) <>

(SELECT

SUM(CASE

WHEN t.funds_avail_date > CURRENT_TIMESTAMP() THEN 0

WHEN t.txn_type_cd = 'DBT' THEN t.amount * −1

ELSE t.amount

END),

SUM(CASE WHEN t.txn_type_cd = 'DBT' THEN t.amount * −1 ELSE t.amount END)

FROM transaction t

WHERE t.account_id = a.account_id);

Show whether the customer has any checking accounts and whether the customer has any savings accounts:

SELECT c.cust_id, c.fed_id, c.cust_type_cd,

CASE

WHEN EXISTS (SELECT 1 FROM account a

WHERE a.cust_id = c.cust_id

AND a.product_cd = 'CHK') THEN 'Y'

ELSE 'N'

END has_checking,

CASE

WHEN EXISTS (SELECT 1 FROM account a

WHERE a.cust_id = c.cust_id

AND a.product_cd = 'SAV') THEN 'Y'

ELSE 'N'

END has_savings

FROM customer c;

Count the number of accounts for each customer, and then returns either 'None', '1', '2', or '3+':

SELECT c.cust_id, c.fed_id, c.cust_type_cd,

CASE (SELECT COUNT(*) FROM account a

WHERE a.cust_id = c.cust_id)

WHEN 0 THEN 'None'

WHEN 1 THEN '1'

WHEN 2 THEN '2'

ELSE '3+'

END num_accounts

FROM customer c;

Division-by-Zero Errors

SELECT a.cust_id, a.product_cd, a.avail_balance /

CASE

WHEN prod_tots.tot_balance = 0 THEN 1

ELSE prod_tots.tot_balance

END percent_of_total

FROM account a INNER JOIN

(SELECT a.product_cd, SUM(a.avail_balance) tot_balance

FROM account a

GROUP BY a.product_cd) prod_tots

ON a.product_cd = prod_tots.product_cd;

Transactions

Lock Granularities:Table locks, Page locks, Row locks

SHOW TABLE STATUS LIKE 'transaction' \G

START TRANSACTION;

UPDATE product

SET date_retired = CURRENT_TIMESTAMP() WHERE product_cd = 'XYZ';

SAVEPOINT before_close_accounts;

UPDATE account

SET status = 'CLOSED', close_date = CURRENT_TIMESTAMP(),

last_activity_date = CURRENT_TIMESTAMP()

WHERE product_cd = 'XYZ';

ROLLBACK TO SAVEPOINT before_close_accounts;

COMMIT;

Resource

Learning SQL

Labels

adsense (5) Algorithm (69) Algorithm Series (35) Android (7) ANT (6) bat (8) Big Data (7) Blogger (14) Bugs (6) Cache (5) Chrome (19) Code Example (29) Code Quality (7) Coding Skills (5) Database (7) Debug (16) Design (5) Dev Tips (63) Eclipse (32) Git (5) Google (33) Guava (7) How to (9) Http Client (8) IDE (7) Interview (88) J2EE (13) J2SE (49) Java (186) JavaScript (27) JSON (7) Learning code (9) Lesson Learned (6) Linux (26) Lucene-Solr (112) Mac (10) Maven (8) Network (9) Nutch2 (18) Performance (9) PowerShell (11) Problem Solving (11) Programmer Skills (6) regex (5) Scala (6) Security (9) Soft Skills (38) Spring (22) System Design (11) Testing (7) Text Mining (14) Tips (17) Tools (24) Troubleshooting (29) UIMA (9) Web Development (19) Windows (21) xml (5)