Skip to content

Instantly share code, notes, and snippets.

@kedarmhaswade
Last active Feb 22, 2021
Embed
What would you like to do?
Learning SQL (O'Reilly) With SQLite instead of MySQL
source "https://rubygems.org"
gem 'github-markdown-preview'
gem 'cheat'
GEM
remote: https://rubygems.org/
specs:
activesupport (4.2.1)
i18n (~> 0.7)
json (~> 1.7, >= 1.7.7)
minitest (~> 5.1)
thread_safe (~> 0.3, >= 0.3.4)
tzinfo (~> 1.1)
celluloid (0.16.0)
timers (~> 4.0.0)
cheat (1.3.3)
pager (~> 1.0)
crass (1.0.2)
ffi (1.9.8)
gemoji (2.1.0)
github-markdown (0.6.8)
github-markdown-preview (3.1.4)
gemoji (~> 2.1)
github-markdown (~> 0.6)
html-pipeline (~> 1.11)
json (= 1.8.1)
listen (= 2.8)
sanitize (~> 3.0)
hitimes (1.2.2)
html-pipeline (1.11.0)
activesupport (>= 2)
nokogiri (~> 1.4)
i18n (0.7.0)
json (1.8.1)
listen (2.8.0)
celluloid (>= 0.15.2)
rb-fsevent (>= 0.9.3)
rb-inotify (>= 0.9)
mini_portile (0.6.2)
minitest (5.6.1)
nokogiri (1.6.6.2)
mini_portile (~> 0.6.0)
nokogumbo (1.2.0)
nokogiri
pager (1.0.1)
rb-fsevent (0.9.4)
rb-inotify (0.9.5)
ffi (>= 0.5.0)
sanitize (3.1.2)
crass (~> 1.0.1)
nokogiri (>= 1.4.4)
nokogumbo (= 1.2.0)
thread_safe (0.3.5)
timers (4.0.1)
hitimes
tzinfo (1.2.2)
thread_safe (~> 0.1)
PLATFORMS
ruby
DEPENDENCIES
cheat
github-markdown-preview
drop database if exists learningsql;
create database learningsql;
use learningsql;
/* begin table creation */
create table department
(dept_id smallint unsigned not null auto_increment,
name varchar(20) not null,
constraint pk_department primary key (dept_id)
);
create table branch
(branch_id smallint unsigned not null auto_increment,
name varchar(20) not null,
address varchar(30),
city varchar(20),
state varchar(2),
zip varchar(12),
constraint pk_branch primary key (branch_id)
);
create table employee
(emp_id smallint unsigned not null auto_increment,
fname varchar(20) not null,
lname varchar(20) not null,
start_date date not null,
end_date date,
superior_emp_id smallint unsigned,
dept_id smallint unsigned,
title varchar(20),
assigned_branch_id smallint unsigned,
constraint fk_e_emp_id
foreign key (superior_emp_id) references employee (emp_id),
constraint fk_dept_id
foreign key (dept_id) references department (dept_id),
constraint fk_e_branch_id
foreign key (assigned_branch_id) references branch (branch_id),
constraint pk_employee primary key (emp_id)
);
create table product_type
(product_type_cd varchar(10) not null,
name varchar(50) not null,
constraint pk_product_type primary key (product_type_cd)
);
create table product
(product_cd varchar(10) not null,
name varchar(50) not null,
product_type_cd varchar(10) not null,
date_offered date,
date_retired date,
constraint fk_product_type_cd foreign key (product_type_cd)
references product_type (product_type_cd),
constraint pk_product primary key (product_cd)
);
create table customer
(cust_id integer unsigned not null auto_increment,
fed_id varchar(12) not null,
cust_type_cd enum('I','B') not null,
address varchar(30),
city varchar(20),
state varchar(20),
postal_code varchar(10),
constraint pk_customer primary key (cust_id)
);
create table individual
(cust_id integer unsigned not null,
fname varchar(30) not null,
lname varchar(30) not null,
birth_date date,
constraint fk_i_cust_id foreign key (cust_id)
references customer (cust_id),
constraint pk_individual primary key (cust_id)
);
create table business
(cust_id integer unsigned not null,
name varchar(40) not null,
state_id varchar(10) not null,
incorp_date date,
constraint fk_b_cust_id foreign key (cust_id)
references customer (cust_id),
constraint pk_business primary key (cust_id)
);
create table officer
(officer_id smallint unsigned not null auto_increment,
cust_id integer unsigned not null,
fname varchar(30) not null,
lname varchar(30) not null,
title varchar(20),
start_date date not null,
end_date date,
constraint fk_o_cust_id foreign key (cust_id)
references business (cust_id),
constraint pk_officer primary key (officer_id)
);
create table account
(account_id integer unsigned not null auto_increment,
product_cd varchar(10) not null,
cust_id integer unsigned not null,
open_date date not null,
close_date date,
last_activity_date date,
status enum('ACTIVE','CLOSED','FROZEN'),
open_branch_id smallint unsigned,
open_emp_id smallint unsigned,
avail_balance float(10,2),
pending_balance float(10,2),
constraint fk_product_cd foreign key (product_cd)
references product (product_cd),
constraint fk_a_cust_id foreign key (cust_id)
references customer (cust_id),
constraint fk_a_branch_id foreign key (open_branch_id)
references branch (branch_id),
constraint fk_a_emp_id foreign key (open_emp_id)
references employee (emp_id),
constraint pk_account primary key (account_id)
);
create table transaction
(txn_id integer unsigned not null auto_increment,
txn_date datetime not null,
account_id integer unsigned not null,
txn_type_cd enum('DBT','CDT'),
amount double(10,2) not null,
teller_emp_id smallint unsigned,
execution_branch_id smallint unsigned,
funds_avail_date datetime,
constraint fk_t_account_id foreign key (account_id)
references account (account_id),
constraint fk_teller_emp_id foreign key (teller_emp_id)
references employee (emp_id),
constraint fk_exec_branch_id foreign key (execution_branch_id)
references branch (branch_id),
constraint pk_transaction primary key (txn_id)
);
/* end table creation */
/* begin data population */
/* department data */
insert into department (dept_id, name)
values (null, 'Operations');
insert into department (dept_id, name)
values (null, 'Loans');
insert into department (dept_id, name)
values (null, 'Administration');
/* branch data */
insert into branch (branch_id, name, address, city, state, zip)
values (null, 'Headquarters', '3882 Main St.', 'Waltham', 'MA', '02451');
insert into branch (branch_id, name, address, city, state, zip)
values (null, 'Woburn Branch', '422 Maple St.', 'Woburn', 'MA', '01801');
insert into branch (branch_id, name, address, city, state, zip)
values (null, 'Quincy Branch', '125 Presidential Way', 'Quincy', 'MA', '02169');
insert into branch (branch_id, name, address, city, state, zip)
values (null, 'So. NH Branch', '378 Maynard Ln.', 'Salem', 'NH', '03079');
/* employee data */
insert into employee (emp_id, fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (null, 'Michael', 'Smith', '2001-06-22',
(select dept_id from department where name = 'Administration'),
'President',
(select branch_id from branch where name = 'Headquarters'));
insert into employee (emp_id, fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (null, 'Susan', 'Barker', '2002-09-12',
(select dept_id from department where name = 'Administration'),
'Vice President',
(select branch_id from branch where name = 'Headquarters'));
insert into employee (emp_id, fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (null, 'Robert', 'Tyler', '2000-02-09',
(select dept_id from department where name = 'Administration'),
'Treasurer',
(select branch_id from branch where name = 'Headquarters'));
insert into employee (emp_id, fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (null, 'Susan', 'Hawthorne', '2002-04-24',
(select dept_id from department where name = 'Operations'),
'Operations Manager',
(select branch_id from branch where name = 'Headquarters'));
insert into employee (emp_id, fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (null, 'John', 'Gooding', '2003-11-14',
(select dept_id from department where name = 'Loans'),
'Loan Manager',
(select branch_id from branch where name = 'Headquarters'));
insert into employee (emp_id, fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (null, 'Helen', 'Fleming', '2004-03-17',
(select dept_id from department where name = 'Operations'),
'Head Teller',
(select branch_id from branch where name = 'Headquarters'));
insert into employee (emp_id, fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (null, 'Chris', 'Tucker', '2004-09-15',
(select dept_id from department where name = 'Operations'),
'Teller',
(select branch_id from branch where name = 'Headquarters'));
insert into employee (emp_id, fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (null, 'Sarah', 'Parker', '2002-12-02',
(select dept_id from department where name = 'Operations'),
'Teller',
(select branch_id from branch where name = 'Headquarters'));
insert into employee (emp_id, fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (null, 'Jane', 'Grossman', '2002-05-03',
(select dept_id from department where name = 'Operations'),
'Teller',
(select branch_id from branch where name = 'Headquarters'));
insert into employee (emp_id, fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (null, 'Paula', 'Roberts', '2002-07-27',
(select dept_id from department where name = 'Operations'),
'Head Teller',
(select branch_id from branch where name = 'Woburn Branch'));
insert into employee (emp_id, fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (null, 'Thomas', 'Ziegler', '2000-10-23',
(select dept_id from department where name = 'Operations'),
'Teller',
(select branch_id from branch where name = 'Woburn Branch'));
insert into employee (emp_id, fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (null, 'Samantha', 'Jameson', '2003-01-08',
(select dept_id from department where name = 'Operations'),
'Teller',
(select branch_id from branch where name = 'Woburn Branch'));
insert into employee (emp_id, fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (null, 'John', 'Blake', '2000-05-11',
(select dept_id from department where name = 'Operations'),
'Head Teller',
(select branch_id from branch where name = 'Quincy Branch'));
insert into employee (emp_id, fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (null, 'Cindy', 'Mason', '2002-08-09',
(select dept_id from department where name = 'Operations'),
'Teller',
(select branch_id from branch where name = 'Quincy Branch'));
insert into employee (emp_id, fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (null, 'Frank', 'Portman', '2003-04-01',
(select dept_id from department where name = 'Operations'),
'Teller',
(select branch_id from branch where name = 'Quincy Branch'));
insert into employee (emp_id, fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (null, 'Theresa', 'Markham', '2001-03-15',
(select dept_id from department where name = 'Operations'),
'Head Teller',
(select branch_id from branch where name = 'So. NH Branch'));
insert into employee (emp_id, fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (null, 'Beth', 'Fowler', '2002-06-29',
(select dept_id from department where name = 'Operations'),
'Teller',
(select branch_id from branch where name = 'So. NH Branch'));
insert into employee (emp_id, fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (null, 'Rick', 'Tulman', '2002-12-12',
(select dept_id from department where name = 'Operations'),
'Teller',
(select branch_id from branch where name = 'So. NH Branch'));
/* create data for self-referencing foreign key 'superior_emp_id' */
create temporary table emp_tmp as
select emp_id, fname, lname from employee;
update employee set superior_emp_id =
(select emp_id from emp_tmp where lname = 'Smith' and fname = 'Michael')
where ((lname = 'Barker' and fname = 'Susan')
or (lname = 'Tyler' and fname = 'Robert'));
update employee set superior_emp_id =
(select emp_id from emp_tmp where lname = 'Tyler' and fname = 'Robert')
where lname = 'Hawthorne' and fname = 'Susan';
update employee set superior_emp_id =
(select emp_id from emp_tmp where lname = 'Hawthorne' and fname = 'Susan')
where ((lname = 'Gooding' and fname = 'John')
or (lname = 'Fleming' and fname = 'Helen')
or (lname = 'Roberts' and fname = 'Paula')
or (lname = 'Blake' and fname = 'John')
or (lname = 'Markham' and fname = 'Theresa'));
update employee set superior_emp_id =
(select emp_id from emp_tmp where lname = 'Fleming' and fname = 'Helen')
where ((lname = 'Tucker' and fname = 'Chris')
or (lname = 'Parker' and fname = 'Sarah')
or (lname = 'Grossman' and fname = 'Jane'));
update employee set superior_emp_id =
(select emp_id from emp_tmp where lname = 'Roberts' and fname = 'Paula')
where ((lname = 'Ziegler' and fname = 'Thomas')
or (lname = 'Jameson' and fname = 'Samantha'));
update employee set superior_emp_id =
(select emp_id from emp_tmp where lname = 'Blake' and fname = 'John')
where ((lname = 'Mason' and fname = 'Cindy')
or (lname = 'Portman' and fname = 'Frank'));
update employee set superior_emp_id =
(select emp_id from emp_tmp where lname = 'Markham' and fname = 'Theresa')
where ((lname = 'Fowler' and fname = 'Beth')
or (lname = 'Tulman' and fname = 'Rick'));
drop table emp_tmp;
/* product type data */
insert into product_type (product_type_cd, name)
values ('ACCOUNT','Customer Accounts');
insert into product_type (product_type_cd, name)
values ('LOAN','Individual and Business Loans');
insert into product_type (product_type_cd, name)
values ('INSURANCE','Insurance Offerings');
/* product data */
insert into product (product_cd, name, product_type_cd, date_offered)
values ('CHK','checking account','ACCOUNT','2000-01-01');
insert into product (product_cd, name, product_type_cd, date_offered)
values ('SAV','savings account','ACCOUNT','2000-01-01');
insert into product (product_cd, name, product_type_cd, date_offered)
values ('MM','money market account','ACCOUNT','2000-01-01');
insert into product (product_cd, name, product_type_cd, date_offered)
values ('CD','certificate of deposit','ACCOUNT','2000-01-01');
insert into product (product_cd, name, product_type_cd, date_offered)
values ('MRT','home mortgage','LOAN','2000-01-01');
insert into product (product_cd, name, product_type_cd, date_offered)
values ('AUT','auto loan','LOAN','2000-01-01');
insert into product (product_cd, name, product_type_cd, date_offered)
values ('BUS','business line of credit','LOAN','2000-01-01');
insert into product (product_cd, name, product_type_cd, date_offered)
values ('SBL','small business loan','LOAN','2000-01-01');
/* residential customer data */
insert into customer (cust_id, fed_id, cust_type_cd,
address, city, state, postal_code)
values (null, '111-11-1111', 'I', '47 Mockingbird Ln', 'Lynnfield', 'MA', '01940');
insert into individual (cust_id, fname, lname, birth_date)
select cust_id, 'James', 'Hadley', '1972-04-22' from customer
where fed_id = '111-11-1111';
insert into customer (cust_id, fed_id, cust_type_cd,
address, city, state, postal_code)
values (null, '222-22-2222', 'I', '372 Clearwater Blvd', 'Woburn', 'MA', '01801');
insert into individual (cust_id, fname, lname, birth_date)
select cust_id, 'Susan', 'Tingley', '1968-08-15' from customer
where fed_id = '222-22-2222';
insert into customer (cust_id, fed_id, cust_type_cd,
address, city, state, postal_code)
values (null, '333-33-3333', 'I', '18 Jessup Rd', 'Quincy', 'MA', '02169');
insert into individual (cust_id, fname, lname, birth_date)
select cust_id, 'Frank', 'Tucker', '1958-02-06' from customer
where fed_id = '333-33-3333';
insert into customer (cust_id, fed_id, cust_type_cd,
address, city, state, postal_code)
values (null, '444-44-4444', 'I', '12 Buchanan Ln', 'Waltham', 'MA', '02451');
insert into individual (cust_id, fname, lname, birth_date)
select cust_id, 'John', 'Hayward', '1966-12-22' from customer
where fed_id = '444-44-4444';
insert into customer (cust_id, fed_id, cust_type_cd,
address, city, state, postal_code)
values (null, '555-55-5555', 'I', '2341 Main St', 'Salem', 'NH', '03079');
insert into individual (cust_id, fname, lname, birth_date)
select cust_id, 'Charles', 'Frasier', '1971-08-25' from customer
where fed_id = '555-55-5555';
insert into customer (cust_id, fed_id, cust_type_cd,
address, city, state, postal_code)
values (null, '666-66-6666', 'I', '12 Blaylock Ln', 'Waltham', 'MA', '02451');
insert into individual (cust_id, fname, lname, birth_date)
select cust_id, 'John', 'Spencer', '1962-09-14' from customer
where fed_id = '666-66-6666';
insert into customer (cust_id, fed_id, cust_type_cd,
address, city, state, postal_code)
values (null, '777-77-7777', 'I', '29 Admiral Ln', 'Wilmington', 'MA', '01887');
insert into individual (cust_id, fname, lname, birth_date)
select cust_id, 'Margaret', 'Young', '1947-03-19' from customer
where fed_id = '777-77-7777';
insert into customer (cust_id, fed_id, cust_type_cd,
address, city, state, postal_code)
values (null, '888-88-8888', 'I', '472 Freedom Rd', 'Salem', 'NH', '03079');
insert into individual (cust_id, fname, lname, birth_date)
select cust_id, 'Louis', 'Blake', '1977-07-01' from customer
where fed_id = '888-88-8888';
insert into customer (cust_id, fed_id, cust_type_cd,
address, city, state, postal_code)
values (null, '999-99-9999', 'I', '29 Maple St', 'Newton', 'MA', '02458');
insert into individual (cust_id, fname, lname, birth_date)
select cust_id, 'Richard', 'Farley', '1968-06-16' from customer
where fed_id = '999-99-9999';
/* corporate customer data */
insert into customer (cust_id, fed_id, cust_type_cd,
address, city, state, postal_code)
values (null, '04-1111111', 'B', '7 Industrial Way', 'Salem', 'NH', '03079');
insert into business (cust_id, name, state_id, incorp_date)
select cust_id, 'Chilton Engineering', '12-345-678', '1995-05-01' from customer
where fed_id = '04-1111111';
insert into officer (officer_id, cust_id, fname, lname,
title, start_date)
select null, cust_id, 'John', 'Chilton', 'President', '1995-05-01'
from customer
where fed_id = '04-1111111';
insert into customer (cust_id, fed_id, cust_type_cd,
address, city, state, postal_code)
values (null, '04-2222222', 'B', '287A Corporate Ave', 'Wilmington', 'MA', '01887');
insert into business (cust_id, name, state_id, incorp_date)
select cust_id, 'Northeast Cooling Inc.', '23-456-789', '2001-01-01' from customer
where fed_id = '04-2222222';
insert into officer (officer_id, cust_id, fname, lname,
title, start_date)
select null, cust_id, 'Paul', 'Hardy', 'President', '2001-01-01'
from customer
where fed_id = '04-2222222';
insert into customer (cust_id, fed_id, cust_type_cd,
address, city, state, postal_code)
values (null, '04-3333333', 'B', '789 Main St', 'Salem', 'NH', '03079');
insert into business (cust_id, name, state_id, incorp_date)
select cust_id, 'Superior Auto Body', '34-567-890', '2002-06-30' from customer
where fed_id = '04-3333333';
insert into officer (officer_id, cust_id, fname, lname,
title, start_date)
select null, cust_id, 'Carl', 'Lutz', 'President', '2002-06-30'
from customer
where fed_id = '04-3333333';
insert into customer (cust_id, fed_id, cust_type_cd,
address, city, state, postal_code)
values (null, '04-4444444', 'B', '4772 Presidential Way', 'Quincy', 'MA', '02169');
insert into business (cust_id, name, state_id, incorp_date)
select cust_id, 'AAA Insurance Inc.', '45-678-901', '1999-05-01' from customer
where fed_id = '04-4444444';
insert into officer (officer_id, cust_id, fname, lname,
title, start_date)
select null, cust_id, 'Stanley', 'Cheswick', 'President', '1999-05-01'
from customer
where fed_id = '04-4444444';
/* residential account data */
insert into account (account_id, product_cd, cust_id, open_date,
last_activity_date, status, open_branch_id,
open_emp_id, avail_balance, pending_balance)
select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
e.branch_id, e.emp_id, a.avail, a.pend
from customer c cross join
(select b.branch_id, e.emp_id
from branch b inner join employee e on e.assigned_branch_id = b.branch_id
where b.city = 'Woburn' limit 1) e
cross join
(select 'CHK' prod_cd, '2000-01-15' open_date, '2005-01-04' last_date,
1057.75 avail, 1057.75 pend union all
select 'SAV' prod_cd, '2000-01-15' open_date, '2004-12-19' last_date,
500.00 avail, 500.00 pend union all
select 'CD' prod_cd, '2004-06-30' open_date, '2004-06-30' last_date,
3000.00 avail, 3000.00 pend) a
where c.fed_id = '111-11-1111';
insert into account (account_id, product_cd, cust_id, open_date,
last_activity_date, status, open_branch_id,
open_emp_id, avail_balance, pending_balance)
select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
e.branch_id, e.emp_id, a.avail, a.pend
from customer c cross join
(select b.branch_id, e.emp_id
from branch b inner join employee e on e.assigned_branch_id = b.branch_id
where b.city = 'Woburn' limit 1) e
cross join
(select 'CHK' prod_cd, '2001-03-12' open_date, '2004-12-27' last_date,
2258.02 avail, 2258.02 pend union all
select 'SAV' prod_cd, '2001-03-12' open_date, '2004-12-11' last_date,
200.00 avail, 200.00 pend) a
where c.fed_id = '222-22-2222';
insert into account (account_id, product_cd, cust_id, open_date,
last_activity_date, status, open_branch_id,
open_emp_id, avail_balance, pending_balance)
select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
e.branch_id, e.emp_id, a.avail, a.pend
from customer c cross join
(select b.branch_id, e.emp_id
from branch b inner join employee e on e.assigned_branch_id = b.branch_id
where b.city = 'Quincy' limit 1) e
cross join
(select 'CHK' prod_cd, '2002-11-23' open_date, '2004-11-30' last_date,
1057.75 avail, 1057.75 pend union all
select 'MM' prod_cd, '2002-12-15' open_date, '2004-12-05' last_date,
2212.50 avail, 2212.50 pend) a
where c.fed_id = '333-33-3333';
insert into account (account_id, product_cd, cust_id, open_date,
last_activity_date, status, open_branch_id,
open_emp_id, avail_balance, pending_balance)
select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
e.branch_id, e.emp_id, a.avail, a.pend
from customer c cross join
(select b.branch_id, e.emp_id
from branch b inner join employee e on e.assigned_branch_id = b.branch_id
where b.city = 'Waltham' limit 1) e
cross join
(select 'CHK' prod_cd, '2003-09-12' open_date, '2005-01-03' last_date,
534.12 avail, 534.12 pend union all
select 'SAV' prod_cd, '2000-01-15' open_date, '2004-10-24' last_date,
767.77 avail, 767.77 pend union all
select 'MM' prod_cd, '2004-09-30' open_date, '2004-11-11' last_date,
5487.09 avail, 5487.09 pend) a
where c.fed_id = '444-44-4444';
insert into account (account_id, product_cd, cust_id, open_date,
last_activity_date, status, open_branch_id,
open_emp_id, avail_balance, pending_balance)
select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
e.branch_id, e.emp_id, a.avail, a.pend
from customer c cross join
(select b.branch_id, e.emp_id
from branch b inner join employee e on e.assigned_branch_id = b.branch_id
where b.city = 'Salem' limit 1) e
cross join
(select 'CHK' prod_cd, '2004-01-27' open_date, '2005-01-05' last_date,
2237.97 avail, 2897.97 pend) a
where c.fed_id = '555-55-5555';
insert into account (account_id, product_cd, cust_id, open_date,
last_activity_date, status, open_branch_id,
open_emp_id, avail_balance, pending_balance)
select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
e.branch_id, e.emp_id, a.avail, a.pend
from customer c cross join
(select b.branch_id, e.emp_id
from branch b inner join employee e on e.assigned_branch_id = b.branch_id
where b.city = 'Waltham' limit 1) e
cross join
(select 'CHK' prod_cd, '2002-08-24' open_date, '2004-11-29' last_date,
122.37 avail, 122.37 pend union all
select 'CD' prod_cd, '2004-12-28' open_date, '2004-12-28' last_date,
10000.00 avail, 10000.00 pend) a
where c.fed_id = '666-66-6666';
insert into account (account_id, product_cd, cust_id, open_date,
last_activity_date, status, open_branch_id,
open_emp_id, avail_balance, pending_balance)
select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
e.branch_id, e.emp_id, a.avail, a.pend
from customer c cross join
(select b.branch_id, e.emp_id
from branch b inner join employee e on e.assigned_branch_id = b.branch_id
where b.city = 'Woburn' limit 1) e
cross join
(select 'CD' prod_cd, '2004-01-12' open_date, '2004-01-12' last_date,
5000.00 avail, 5000.00 pend) a
where c.fed_id = '777-77-7777';
insert into account (account_id, product_cd, cust_id, open_date,
last_activity_date, status, open_branch_id,
open_emp_id, avail_balance, pending_balance)
select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
e.branch_id, e.emp_id, a.avail, a.pend
from customer c cross join
(select b.branch_id, e.emp_id
from branch b inner join employee e on e.assigned_branch_id = b.branch_id
where b.city = 'Salem' limit 1) e
cross join
(select 'CHK' prod_cd, '2001-05-23' open_date, '2005-01-03' last_date,
3487.19 avail, 3487.19 pend union all
select 'SAV' prod_cd, '2001-05-23' open_date, '2004-10-12' last_date,
387.99 avail, 387.99 pend) a
where c.fed_id = '888-88-8888';
insert into account (account_id, product_cd, cust_id, open_date,
last_activity_date, status, open_branch_id,
open_emp_id, avail_balance, pending_balance)
select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
e.branch_id, e.emp_id, a.avail, a.pend
from customer c cross join
(select b.branch_id, e.emp_id
from branch b inner join employee e on e.assigned_branch_id = b.branch_id
where b.city = 'Waltham' limit 1) e
cross join
(select 'CHK' prod_cd, '2003-07-30' open_date, '2004-12-15' last_date,
125.67 avail, 125.67 pend union all
select 'MM' prod_cd, '2004-10-28' open_date, '2004-10-28' last_date,
9345.55 avail, 9845.55 pend union all
select 'CD' prod_cd, '2004-06-30' open_date, '2004-06-30' last_date,
1500.00 avail, 1500.00 pend) a
where c.fed_id = '999-99-9999';
/* corporate account data */
insert into account (account_id, product_cd, cust_id, open_date,
last_activity_date, status, open_branch_id,
open_emp_id, avail_balance, pending_balance)
select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
e.branch_id, e.emp_id, a.avail, a.pend
from customer c cross join
(select b.branch_id, e.emp_id
from branch b inner join employee e on e.assigned_branch_id = b.branch_id
where b.city = 'Salem' limit 1) e
cross join
(select 'CHK' prod_cd, '2002-09-30' open_date, '2004-12-15' last_date,
23575.12 avail, 23575.12 pend union all
select 'BUS' prod_cd, '2002-10-01' open_date, '2004-08-28' last_date,
0 avail, 0 pend) a
where c.fed_id = '04-1111111';
insert into account (account_id, product_cd, cust_id, open_date,
last_activity_date, status, open_branch_id,
open_emp_id, avail_balance, pending_balance)
select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
e.branch_id, e.emp_id, a.avail, a.pend
from customer c cross join
(select b.branch_id, e.emp_id
from branch b inner join employee e on e.assigned_branch_id = b.branch_id
where b.city = 'Woburn' limit 1) e
cross join
(select 'BUS' prod_cd, '2004-03-22' open_date, '2004-11-14' last_date,
9345.55 avail, 9345.55 pend) a
where c.fed_id = '04-2222222';
insert into account (account_id, product_cd, cust_id, open_date,
last_activity_date, status, open_branch_id,
open_emp_id, avail_balance, pending_balance)
select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
e.branch_id, e.emp_id, a.avail, a.pend
from customer c cross join
(select b.branch_id, e.emp_id
from branch b inner join employee e on e.assigned_branch_id = b.branch_id
where b.city = 'Salem' limit 1) e
cross join
(select 'CHK' prod_cd, '2003-07-30' open_date, '2004-12-15' last_date,
38552.05 avail, 38552.05 pend) a
where c.fed_id = '04-3333333';
insert into account (account_id, product_cd, cust_id, open_date,
last_activity_date, status, open_branch_id,
open_emp_id, avail_balance, pending_balance)
select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE',
e.branch_id, e.emp_id, a.avail, a.pend
from customer c cross join
(select b.branch_id, e.emp_id
from branch b inner join employee e on e.assigned_branch_id = b.branch_id
where b.city = 'Quincy' limit 1) e
cross join
(select 'SBL' prod_cd, '2004-02-22' open_date, '2004-12-17' last_date,
50000.00 avail, 50000.00 pend) a
where c.fed_id = '04-4444444';
/* put $100 in all checking/savings accounts on date account opened */
insert into transaction (txn_id, txn_date, account_id, txn_type_cd,
amount, funds_avail_date)
select null, a.open_date, a.account_id, 'CDT', 100, a.open_date
from account a
where a.product_cd IN ('CHK','SAV','CD','MM');
/* end data population */

Notes from Alan Beaulieu's Book

Chapter 8 Grouping and Aggregates

Data is generally stored at the lowest level of granularity needed by any of a database's users. Sometimes for instance, one needs to take a look at each record of a table. But that does not mean you have to look at it at that granularity. The group by construct and the aggregate functions allow you to examine data at a different granularity.

Grouping Concepts

If you look at a table like employee, then you will see that it has a number of columns like emp_id, dept_id, title etc. Thus, each employee has some value for these fields. Sometimes, it may so happen that you want to form the groups of employees based on these criteria like title. Doing this may help us answer questions like how many different titles of employees are there? It is almost like asking each employee her/his title and putting her/him in a bucket labeled with that title.

This will enable us to first of all, find the buckets with distinct labels. Each bucket is a group that is unique with respect to the criterion chosen (this means they may differ with respect to otherwise). In a small table with 18 employees each one of which have a department we see the following:

mysql> select emp_id, lname, dept_id from employee;
+--------+-----------+---------+
| emp_id | lname     | dept_id |
+--------+-----------+---------+
|      1 | Smith     |       3 |
|      2 | Barker    |       3 |
|      3 | Tyler     |       3 |
|      4 | Hawthorne |       1 |
|      5 | Gooding   |       2 |
|      6 | Fleming   |       1 |
|      7 | Tucker    |       1 |
|      8 | Parker    |       1 |
|      9 | Grossman  |       1 |
|     10 | Roberts   |       1 |
|     11 | Ziegler   |       1 |
|     12 | Jameson   |       1 |
|     13 | Blake     |       1 |
|     14 | Mason     |       1 |
|     15 | Portman   |       1 |
|     16 | Markham   |       1 |
|     17 | Fowler    |       1 |
|     18 | Tulman    |       1 |
+--------+-----------+---------+
18 rows in set (0.00 sec

If we make the dept_id as a bucket or group, then each of these employees has to fall in one of the three buckets, where the dept_id is either 1, 2, or 3. There is quite some insight that we may get about these groups, if we were actually able to create these buckets. This is exactly what the group by construct is designed for. See for example, the output of the following query:

mysql> select dept_id from employee group by dept_id;
+---------+
| dept_id |
+---------+
|       1 |
|       2 |
|       3 |
+---------+
3 rows in set (0.00 sec)

This output is to be expected as we have three buckets as far as dept_id's are concerned and each employee is in exactly one of these buckets.

Note that each member of the group is still a full-fledged record from the table. Thus, each member of a group has all the characteristics (e.g. title, emp_id, lname, fname etc.) that are of interest.

Aggregate Functions

Once the groups are made, what is of interest? Perhaps doing some operations on the group as a whole! The most straightforward of those is the size of each group. For now, it does not matter how we count the members of each group -- we just see how many things are in a bucket. The way to do that in SQL is the famous count(*). Thus, to count the number of members in each bucket (i.e. the size of each group), we do:

mysql> select dept_id, count(*) from employee group by dept_id;
+---------+----------+
| dept_id | count(*) |
+---------+----------+
|       1 |       14 |
|       2 |        1 |
|       3 |        3 |
+---------+----------+
3 rows in set (0.00 sec)

or, more descriptively:

mysql> select dept_id, count(dept_id) 'size of group with this dept_id' from employee group by dept_id;
+---------+---------------------------------+
| dept_id | size of group with this dept_id |
+---------+---------------------------------+
|       1 |                              14 |
|       2 |                               1 |
|       3 |                               3 |
+---------+---------------------------------+
3 rows in set (0.00 sec)

The criterion for grouping used in the above example is the dept_id of each employee record. This criterion can be expressed as: Group the employees by their departments.

This is what aggregate functions are for: doing some operation on all the records in a group. Thus, if a particular characteristic of every record in the group is numeric, we could sum the records on that characteristic, find the max or min of all the records for that characteristic and so on.

And the database server does that operation for all the groups formed by the column/criterion specified by the group by clause. In the above example, the count aggregate function is used to count the number of records in each group of employees in the same department.

The number of aggregate functions provided by a database varies, but the following functions are provided by a majority of the databases:

  1. Avg(): Finds the average value of a particular column (numeric) of all the records in a group.
  2. Count(): Counts the number of records in a group, based on a column (asterisk means any column).
  3. Max(): Returns the maximum (numeric) value of a column for all the records in a group.
  4. Min(): Returns the minimum (numeric) value of a column for all the records in a group.
  5. Sum(): Returns the sum of a (numeric) value of a column for all the records in a group.

With the count() function, there is an additional option called DISTINCT. Remember that each record is a full-fledged record with all the characteristics of the given table (or relation). This means, when we are counting the members of a group, we can specify whether counting is affected by whether or not the value of a particular column for each member in the group is distinct.

Thus, count(role) will count all the records in a group that have some role, whereas count(DISTINCT role) will count only those records in a group that have unique value for role. So, for example, in a group formed based on departments (i.e. group by dept_id), if we have 10 members, 8 of which are TELLERs and 2 are HEAD TELLERs, then count(role) would return 10, whereas count(DISTINCT role) would return 2 since there are only two distinct roles in the entire group.

Handling of NULLs

We now explore how counting is affected by NULL values. Simply speaking, count(*) counts the number of records in a group, whereas count(column_name) counts the elements that have some non NULL value for column_name. The NULL values are also ignored by other aggregate functions.

The HAVING Clause

When the groups are formed, certain filtering conditions are to be specified using the having clause, rather than the where clause:

mysql> select count(*) c from account group by product_cd having c >= 10;
+----+
| c  |
+----+
| 10 |
+----+
1 row in set (0.00 sec)

Implicit and Explicit Groups

An implicit group is formed in the absence of the group by clause. The aggregate functions can be applied to the group thus formed. But it is not always possible to create groups without the explicit group by clause. If you see an error like invalid use of aggregate function or mixing of group columns with no group columns is illegal if there no GROUP BY clause then you are running into this problem of database being unable to create explicit groups to run the aggregate functions on. Sometimes, without the essential group by, the database may actually return incorrect values without throwing any error!

Generating Groups

We have already seen that choosing a column name in the group by clause groups all the records by the value of that column for each record. Nothing prevents us from grouping records by their values of more than one column. For instance, it is possible to group students in a school by their grade and favorite sport. In our example, we may want to group the accounts by their type and the branch where they were opened:

mysql> select product_cd, open_branch_id, count(*) from account group by product_cd, open_branch_id;
+------------+----------------+----------+
| product_cd | open_branch_id | count(*) |
+------------+----------------+----------+
| BUS        |              2 |        1 |
| BUS        |              4 |        1 |
| CD         |              1 |        2 |
| CD         |              2 |        2 |
| CHK        |              1 |        3 |
| CHK        |              2 |        2 |
| CHK        |              3 |        1 |
| CHK        |              4 |        4 |
| MM         |              1 |        2 |
| MM         |              3 |        1 |
| SAV        |              1 |        1 |
| SAV        |              2 |        2 |
| SAV        |              4 |        1 |
| SBL        |              3 |        1 |
+------------+----------------+----------+
14 rows in set (0.00 sec)

Thus, the above example shows the multicolumn grouping. The idea is like this: We look at the available number of products (say m) and available number of branches where an account could be opened (say n). From the rule of product, we know the number of groups thus formed is m ⨉ n. So, we form these groups, go through our records one by one and place each one of them in one of the groups. Thus, for an account if the product type is BUS (business) and the branch is Woburn (open_branch_id = 2), then we place it in that group.

In our present example, there are 6 different types of products (select count(*) from account group by product_cd;) and 4 different branches (select count(*) from branch), which means we have 6 ⨉ 4 groups formed that way. Over these 24 groups, we can apply:

  1. Several aggregate functions, and
  2. A few options

We have seen aggregate functions like avg, max, min, sum, count etc. but the options are something new. There are two options that are of interest:

With rollup

The dictionary meaning of rollup is accumulation. A few actual runs of this option will clarify what this option does.

The dictionary meaning of rollup is accumulation. A few actual runs of this option will clarify what this option does.

With rollup: Run 1

Consider the following output:

mysql> select count(*), product_cd, open_branch_id, sum(avail_balance) from account group by product
_cd, open_branch_id with rollup;
+----------+------------+----------------+--------------------+
| count(*) | product_cd | open_branch_id | sum(avail_balance) |
+----------+------------+----------------+--------------------+
|        1 | BUS        |              2 |            9345.55 |
|        1 | BUS        |              4 |               0.00 |
|        2 | BUS        |           NULL |            9345.55 |
|        2 | CD         |              1 |           11500.00 |
|        2 | CD         |              2 |            8000.00 |
|        4 | CD         |           NULL |           19500.00 |
|        3 | CHK        |              1 |             782.16 |
|        2 | CHK        |              2 |            3315.77 |
|        1 | CHK        |              3 |            1057.75 |
|        4 | CHK        |              4 |           67852.33 |
|       10 | CHK        |           NULL |           73008.01 |
|        2 | MM         |              1 |           14832.64 |
|        1 | MM         |              3 |            2212.50 |
|        3 | MM         |           NULL |           17045.14 |
|        1 | SAV        |              1 |             767.77 |
|        2 | SAV        |              2 |             700.00 |
|        1 | SAV        |              4 |             387.99 |
|        4 | SAV        |           NULL |            1855.76 |
|        1 | SBL        |              3 |           50000.00 |
|        1 | SBL        |           NULL |           50000.00 |
|       24 | NULL       |           NULL |          170754.46 |
+----------+------------+----------------+--------------------+
21 rows in set (0.01 sec)

Results would be have clearer if the NULL in the above output were replaced with ANY.

Here is an output from another (perhaps more complicated) run:

th rollup: Run 2

mysql> select count(*), product_cd, open_branch_id, year(open_date), sum(avail_balance) from accou
 group by product_cd, open_branch_id, year(open_date) with rollup;
+----------+------------+----------------+-----------------+--------------------+
| count(*) | product_cd | open_branch_id | year(open_date) | sum(avail_balance) |
+----------+------------+----------------+-----------------+--------------------+
|        1 | BUS        |              2 |            2004 |            9345.55 |
|        1 | BUS        |              2 |            NULL |            9345.55 |
|        1 | BUS        |              4 |            2002 |               0.00 |
|        1 | BUS        |              4 |            NULL |               0.00 |
|        2 | BUS        |           NULL |            NULL |            9345.55 |
|        2 | CD         |              1 |            2004 |           11500.00 |
|        2 | CD         |              1 |            NULL |           11500.00 |
|        2 | CD         |              2 |            2004 |            8000.00 |
|        2 | CD         |              2 |            NULL |            8000.00 |
|        4 | CD         |           NULL |            NULL |           19500.00 |
|        1 | CHK        |              1 |            2002 |             122.37 |
|        2 | CHK        |              1 |            2003 |             659.79 |
|        3 | CHK        |              1 |            NULL |             782.16 |
|        1 | CHK        |              2 |            2000 |            1057.75 |
|        1 | CHK        |              2 |            2001 |            2258.02 |
|        2 | CHK        |              2 |            NULL |            3315.77 |
|        1 | CHK        |              3 |            2002 |            1057.75 |
|        1 | CHK        |              3 |            NULL |            1057.75 |
|        1 | CHK        |              4 |            2001 |            3487.19 |
|        1 | CHK        |              4 |            2002 |           23575.12 |
|        1 | CHK        |              4 |            2003 |           38552.05 |
|        1 | CHK        |              4 |            2004 |            2237.97 |
|        4 | CHK        |              4 |            NULL |           67852.33 |
|       10 | CHK        |           NULL |            NULL |           73008.01 |
|        2 | MM         |              1 |            2004 |           14832.64 |
|        2 | MM         |              1 |            NULL |           14832.64 |
|        1 | MM         |              3 |            2002 |            2212.50 |
|        1 | MM         |              3 |            NULL |            2212.50 |
|        3 | MM         |           NULL |            NULL |           17045.14 |
|        1 | SAV        |              1 |            2000 |             767.77 |
|        1 | SAV        |              1 |            NULL |             767.77 |
|        1 | SAV        |              2 |            2000 |             500.00 |
|        1 | SAV        |              2 |            2001 |             200.00 |
|        2 | SAV        |              2 |            NULL |             700.00 |
|        1 | SAV        |              4 |            2001 |             387.99 |
|        1 | SAV        |              4 |            NULL |             387.99 |
|        4 | SAV        |           NULL |            NULL |            1855.76 |
|        1 | SBL        |              3 |            2004 |           50000.00 |
|        1 | SBL        |              3 |            NULL |           50000.00 |
|        1 | SBL        |           NULL |            NULL |           50000.00 |
|       24 | NULL       |           NULL |            NULL |          170754.46 |
+----------+------------+----------------+-----------------+--------------------+
41 rows in set (0.00 sec)

With cube

Exercises

Exercise 8-1

Construct a query to that counts the number of rows in the account table.

Solution 8-1

mysql> select count(*) from account;
+----------+
| count(*) |
+----------+
|       24 |
+----------+
1 row in set (0.00 sec)

Exercise 8-2

Modify the query in 8-1 to count the number of accounts held by each customer. Show the cu ID and number of accounts for each cu.

Solution 8-2

mysql> select cust_id, count(*) c from account group by cust_id;
+---------+---+
| cust_id | c |
+---------+---+
|       1 | 3 |
|       2 | 2 |
|       3 | 2 |
|       4 | 3 |
|       5 | 1 |
|       6 | 2 |
|       7 | 1 |
|       8 | 2 |
|       9 | 3 |
|      10 | 2 |
|      11 | 1 |
|      12 | 1 |
|      13 | 1 |
+---------+---+
13 rows in set (0.00 sec)

Exercise 8-3

Modify the query in 8-2 to include only those customers who have at least two accounts.

Solution 8-3

mysql> select cust_id, count(*) c from account group by cust_id having c >=2 ;
+---------+---+
| cust_id | c |
+---------+---+
|       1 | 3 |
|       2 | 2 |
|       3 | 2 |
|       4 | 3 |
|       6 | 2 |
|       8 | 2 |
|       9 | 3 |
|      10 | 2 |
+---------+---+
8 rows in set (0.01 sec)

Exercise 8-4

Find the total available balance by product and branch where there is more than one account per product and branch. Order the result by total balance (highest to lowest).

Solution 8-4

mysql> select sum(avail_balance) s, product_cd p, open_branch_id b from account group by product_cd,
 open_branch_id having count(cust_id) > 1 order by s desc;
+----------+-----+------+
| s        | p   | b    |
+----------+-----+------+
| 67852.33 | CHK |    4 |
| 14832.64 | MM  |    1 |
| 11500.00 | CD  |    1 |
|  8000.00 | CD  |    2 |
|  3315.77 | CHK |    2 |
|   782.16 | CHK |    1 |
|   700.00 | SAV |    2 |
+----------+-----+------+
7 rows in set (0.00 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment