Script started on Thu Jun 16 23:41:52 2005
voyager% cat ctab.sql
create table books (
bookid integer not null auto_increment,
name varchar(100) not null,
author varchar(40) not null,
ISBN varchar(10) not null,
price decimal(2,2) not null,
binding char(5) not null,
stock integer not null,
primary key(bookid) );

create table orders (
orderid integer not null auto_increment,
bid integer not null,
quant integer not null,
day date not null,
custid integer not null,
primary key(orderid) );
voyager% cat load7.sql
load data local infile "books.txt"
into table books
fields terminated by ','
optionally enclosed by "'";

load data local infile "orders.txt"
into table orders
fields terminated by ',';
voyager% cat query1.txt
mysql> select * from books
-> where author like '%Browne%'
-> order by price;
+--------+-------------------------------------------+-------------------------+
------------+-------+---------+-------+
| bookid | name | author |
ISBN | price | binding | stock |
+--------+-------------------------------------------+-------------------------+
------------+-------+---------+-------+
| 603 | ICONS OF AMERICA | BROWNE AND FISHWICK |
0879720913 | 7.95 | Paper | 0 |
| 613 | POPULAR ABSTRACTS | BROWNE AND GEIST |
0879721650 | 7.95 | Paper | 62 |
| 155 | THE DEFECTIVE DETECTIVE IN THE PULP | HOPPENSTAND AND BROWNE |
0879722363 | 8.95 | Paper | 36 |
| 232 | DIMENSIONS OF DETECTIVE FICTION | LANDRUM, BROWNE, BROWNE |
0879721243 | 9.95 | Paper | 0 |
| 582 | HEROES AND HUMANITIES DETECTIVE FIC | BROWNE |
0879723718 | 9.95 | Paper | 107 |
| 580 | FORBIDDEN FRUITS TABOOS AND TABOOIS | BROWNE |
0879722568 | 10.95 | Paper | 0 |
| 574 | A NIGHT WITH THE HANTS AND OTHER | BROWNE |
0879721671 | 11.95 | Paper | 35 |
| 588 | OBJECTS OF SPECIAL DEVOTION: FETIS | BROWNE |
0879721928 | 12.95 | Paper | 0 |
| 97 | THE GOD PUMPERS RELIGION IN THE | FISHWICK AND BROWNE |
0879724005 | 13.95 | Paper | 53 |
| 153 | THE GOTHIC WORLD OF STEPHEN KING: | HOPPENSTAND & BROWNE |
0879724110 | 13.95 | Paper | 0 |
| 601 | LAWS OF OUR FATHERS: POPULAR CULTURE | BROWNE AND BROWNE |
0879723386 | 13.95 | Paper | 73 |
| 614 | POPULAR ABSTRACTS | BROWNE AND GEIST |
0879721669 | 13.95 | Cloth | 36 |
| 590 | RITUALS AND CEREMONIES IN POPULAR CULTURE | BROWNE |
0879721618 | 14.95 | Paper | 60 |
| 599 | CONTINUITIES IN POPULAR CULTURE | BROWNE AND AMBROSETTI |
0879725931 | 14.95 | Paper | 57 |
| 597 | SYMBIOSIS | BROWNE & FISHWICK |
0879724404 | 15.95 | Paper | 53 |
| 604 | ICONS OF AMERICA | BROWNE AND FISHWICK |
0879720905 | 15.95 | Cloth | 26 |
| 605 | PREVIEW 2001+ | BROWNE AND FISHWICK |
0879726903 | 15.95 | Paper | 46 |
| 617 | THE CULTURES OF CELEBRATIONS | BROWNE AND MARSDEN |
0879726520 | 15.95 | Paper | 41 |
| 156 | THE DEFECTIVE DETECTIVE IN THE PULP | HOPPENSTAND AND BROWNE |
0879722355 | 16.95 | Cloth | 34 |
| 233 | DIMENSIONS OF DETECTIVE FICTION | LANDRUM, BROWNE, BROWNE |
0879721235 | 16.95 | Cloth | 3 |
| 316 | MAKING THE AMERICAN HOME: MIDDLE | MOTZ & BROWNE |
087972434X | 16.95 | Paper | 31 |
| 584 | HEROINES OF POPULAR CULTURE | BROWNE |
0879724099 | 16.95 | Paper | 49 |
| 595 | THE SPIRIT OF AUSTRALIA: THE CRIME | BROWNE |
087972403X | 16.95 | Paper | 0 |
| 575 | A NIGHT WITH THE HANTS AND OTHER | BROWNE |
0879720751 | 17.95 | Cloth | 34 |
| 576 | AGAINST ACADEMIA | BROWNE |
0879724528 | 17.95 | Paper | 38 |
| 619 | DIGGING INTO POPULAR CULTURE | BROWNE, RAY & PAT |
0879725222 | 17.95 | Paper | 88 |
| 157 | THE GOTHIC WORLD OF ANNE RICE | HOPPENSTAND AND BROWNE |
087972708X | 19.95 | Paper | 15 |
| 578 | DOMINANT SYMBOLS | BROWNE |
087972482X | 19.95 | Paper | 0 |
| 586 | LINCOLN-LORE SECOND EDITION | BROWNE |
0879727209 | 19.95 | Paper | 52 |
| 607 | REJUVENATING THE HUMANITIES | BROWNE AND FISHWICK |
087972546X | 19.95 | Paper | 57 |
| 583 | HEROES AND HUMANITIES DETECTIVE FIC | BROWNE |
087972370X | 20.95 | Cloth | 15 |
| 593 | THE MANY TONGUES OF LITERACY | BROWNE |
0879725605 | 20.95 | Paper | 59 |
| 611 | THE HERO IN TRANSITION | BROWNE AND FISHWICK |
087972238X | 21.95 | Paper | 215 |
| 581 | FORBIDDEN FRUITS TABOOS AND TABOOIS | BROWNE |
0879722558 | 22.95 | Cloth | 41 |
| 589 | OBJECTS OF SPECIAL DEVOTION: FETIS | BROWNE |
087972191X | 22.95 | Cloth | 10 |
| 591 | RITUALS AND CEREMONIES IN POPULAR CULTURE | BROWNE |
087972160X | 22.95 | Cloth | 30 |
| 612 | THE HERO IN TRANSITION | BROWNE AND FISHWICK |
0879722371 | 23.95 | Cloth | 13 |
| 609 | THE GLOBAL VILLAGE: DEAD OR ALIVE | BROWNE AND FISHWICK |
0879727721 | 24.95 | Paper | 9 |
| 615 | PIONEERS IN POPULAR CULTURE STUDIES | BROWNE AND MARSDEN |
0879727764 | 24.95 | Paper | 247 |
| 602 | LAWS OF OUR FATHERS: POPULAR CULTURE | BROWNE AND BROWNE |
0879723378 | 25.95 | Cloth | 31 |
| 592 | THE ALABAMA FOLK LYRIC: A STUDY IN | BROWNE |
0879721294 | 26.00 | Cloth | 34 |
| 154 | THE GOTHIC WORLD OF STEPHEN KING | HOPPENSTAND & BROWNE |
0879724102 | 26.95 | Cloth | 5 |
| 680 | DETECTIVE AS HISTORIAN | BROWNE |
0879728167 | 29.95 | Paper | 344 |
| 596 | THE SPIRIT OF AUSTRALIA: THE CRIME | BROWNE |
0879724021 | 31.95 | Cloth | 17 |
| 598 | SYMBIOSIS | BROWNE & FISHWICK |
0879724390 | 31.95 | Cloth | 23 |
| 98 | THE GOD PUMPERS RELIGION IN THE | FISHWICK AND BROWNE |
0879723998 | 32.95 | Cloth | 25 |
| 585 | HEROINES OF POPULAR CULTURE | BROWNE |
0879724080 | 32.95 | Cloth | 39 |
| 317 | MAKING THE AMERICAN HOME: MIDDLE | MOTZ & BROWNE |
0879724331 | 33.95 | Cloth | 23 |
| 577 | AGAINST ACADEMIA | BROWNE |
087972451X | 34.95 | Cloth | 20 |
| 620 | DIGGING INTO POPULAR CULTURE | BROWNE, RAY & PAT |
0879725214 | 34.95 | Cloth | 10 |
| 606 | PREVIEW 2001+ | BROWNE AND FISHWICK |
087972689X | 35.95 | Cloth | 0 |
| 618 | THE CULTURES OF CELEBRATIONS | BROWNE AND MARSDEN |
0879726512 | 35.95 | Cloth | 20 |
| 600 | CONTINUITIES IN POPULAR CULTURE | BROWNE AND AMBROSETTI |
0879725923 | 39.95 | Cloth | 37 |
| 608 | REJUVENATING THE HUMANITIES | BROWNE AND FISHWICK |
0879725451 | 39.95 | Cloth | 43 |
| 579 | DOMINANT SYMBOLS | BROWNE |
0879724811 | 40.95 | Cloth | 4 |
| 594 | THE MANY TONGUES OF LITERACY | BROWNE |
0879725591 | 40.95 | Cloth | 39 |
| 158 | THE GOTHIC WORLD OF ANNE RICE | HOPPENSTAND AND BROWNE |
0879727071 | 45.95 | Cloth | 22 |
| 587 | LINCOLN-LORE SECOND EDITION | BROWNE |
0879727195 | 49.95 | Cloth | 27 |
| 610 | THE GLOBAL VILLAGE: DEAD OR ALIVE | BROWNE AND FISHWICK |
0879727713 | 49.95 | Cloth | 44 |
| 616 | PIONEERS IN POPULAR CULTURE STUDIES | BROWNE AND MARSDEN |
0879727756 | 49.95 | Cloth | 49 |
| 682 | DETECTIVE AS HISTORIAN | BROWNE |
0879728159 | 59.95 | Cloth | 40 |
| 685 | THE GUIDE TO U.S. POPULAR CULTURE | BROWNE AND BROWNE |
0879728213 | 99.99 | Cloth | 1000 |
+--------+-------------------------------------------+-------------------------+
------------+-------+---------+-------+
62 rows in set (0.00 sec)

mysql>
voyager%
voyager%
voyager%
voyager% cat query2.txt
mysql> select * from books
-> where lower(binding) like '%paper%'
-> and price > 25;
+--------+---------------------------------------+--------------------------+---
---------+-------+---------+-------+
| bookid | name | author | IS
BN | price | binding | stock |
+--------+---------------------------------------+--------------------------+---
---------+-------+---------+-------+
| 49 | CROSS CURRENTS | D'ITRI | 08
79727829 | 25.95 | Paper | 10 |
| 78 | IMAGES OF THE CHILD | EISS | 08
79726547 | 25.95 | Paper | 149 |
| 131 | THE BLOOD IS THE LIFE: VAMPIRES IN | HELDRETH & PHARR | 08
79728043 | 25.95 | Paper | 251 |
| 135 | FAMILY MATTERS IN THE BRITISH AND A | HERRERA, NOLLEN AND FOOR | 08
79727462 | 25.95 | Paper | 22 |
| 181 | PICKING UP THE TAB | JACKSON | 08
79726725 | 25.95 | Paper | 127 |
| 213 | RETROFITTING BLADE RUNNER | KERMAN | 08
79725109 | 27.95 | Paper | 191 |
| 221 | DIVERSITY AND DETECTIVE FICTION | KLEIN | 08
79727969 | 25.95 | Paper | 341 |
| 236 | LOOSENING THE SEAMS | LEE | 08
79728027 | 29.95 | Paper | 263 |
| 262 | NINETEENTH CENTURY WOMEN AT THE MOV | LUPACK | 08
7972806X | 29.95 | Paper | 29 |
| 326 | THE ORIGINALS: THE NEW YORK CELTICS | NELSON | 08
79727942 | 25.95 | Paper | 276 |
| 348 | AFTER THE WESTERN RESERVE | PARKER | 08
79727888 | 25.95 | Paper | 287 |
| 453 | STAGE BLOOD | STUART | 08
79726601 | 25.95 | Paper | 150 |
| 535 | FERRIS WHEELS | ANDERSON | 08
7972532X | 29.95 | Paper | 14 |
| 639 | PHENOMOLOGICAL APPROACHES TO POPULA | CARROLL & TAFOYA | 08
79728108 | 25.95 | Paper | 254 |
| 641 | THE INCREDIBLE SCREAM MACHINE A HIS | CARTMELL | 08
79723421 | 25.95 | Paper | 34 |
| 658 | MYSTERY OF MYSTERIES | COALE | 08
79728140 | 25.95 | Paper | 294 |
| 660 | WOMEN'S LIFE-WRITING: FINDING VOICE | COLEMAN | 08
79727489 | 25.95 | Paper | 46 |
| 677 | REREADING THE REVOLUTION | LAWSON | 08
79728183 | 25.95 | Paper | 331 |
| 678 | NEW HARD-BOILED WRITERS 1970S - 1990S | PANEK | 08
79728205 | 25.95 | Paper | 319 |
| 680 | DETECTIVE AS HISTORIAN | BROWNE | 08
79728167 | 29.95 | Paper | 344 |
+--------+---------------------------------------+--------------------------+---
---------+-------+---------+-------+
20 rows in set (0.01 sec)

mysql>
voyager%
voyager% cat query3.txt
mysql> select custid, sum(quant) from orders
-> group by custid
-> order by quant desc;
+--------+------------+
| custid | sum(quant) |
+--------+------------+
| 1003 | 10 |
| 1009 | 15 |
| 1007 | 9 |
| 1006 | 4 |
| 1002 | 6 |
| 1004 | 6 |
| 1005 | 4 |
| 1008 | 4 |
| 1001 | 3 |
+--------+------------+
9 rows in set (0.00 sec)

mysql>
voyager% cat query4.txt
mysql> select name, day from books, orders
-> where books.bookid = orders.bid and
-> custid = '1004' order by day;
+--------------------------------------+------------+
| name | day |
+--------------------------------------+------------+
| FERRIS WHEELS | 2002-07-24 |
| HOLLYWOOD'S WORLD WAR I | 2003-01-02 |
| POPULAR ARTHURIAN TRADITIONS | 2003-09-07 |
| POLPOP: POLITICS AND POPULAR CULTURE | 2003-11-02 |
+--------------------------------------+------------+
4 rows in set (0.00 sec)

mysql>
voyager% ^D
script done on Thu Jun 16 23:43:32 2005