A variety of important non-trivial examples of SQL use compiled and tested by Professor Futrelle with MySQL Sever version 5.0.45 on MacBook Pro Intel Core 2 Duo The word "important" is there to alert students to the fact that they will need to deal with issues beyond simple queries in their projects. (Details to follow) This version of the examples is 11/4/2007 Most were run in MySQL in class on Monday, November 5th. References are to the O'Neil's textbook and to the MySQL v 5 documentation, http://dev.mysql.com/doc/refman/5.0/en/index.html First, to get set up for my DB (no semicolon) use btest Then, use semicolon: show tables; returns, +-----------------+ | Tables_in_btest | +-----------------+ | agents | | customers | | jpegs | | orders | | products | +-----------------+ For reference, here's the full CAP database with just one tuple shown for each table: CUSTOMERS +------+--------+--------+--------+ | cid | cname | city | discnt | +------+--------+--------+--------+ | c001 | Tiptop | Duluth | 10 | AGENTS +-----+-------+----------+---------+ | aid | aname | city | percent | +-----+-------+----------+---------+ | a01 | Smith | New York | 6 | PRODUCTS +-----+--------+--------+----------+-------+ | pid | pname | city | quantity | price | +-----+--------+--------+----------+-------+ | p01 | comb | Dallas | 111400 | 0.5 | ORDERS +-------+-------+------+------+------+------+---------+ | ordno | month | cid | aid | pid | qty | dollars | +-------+-------+------+------+------+------+---------+ | 1011 | jan | c001 | a01 | p01 | 1000 | 450 | --------------------------------------------------------------- --------------------------------------------------------------- CHAPTER 3 - Basic SQL Query Language Subqueries (O'Neil's Sec. 3.4) Example 3.4.1 pg. 97 (read the discussion there) select distinct cid from orders where aid in (select aid from agents where city = 'Duluth' or city = 'Dallas'); To break this down, we first show the results of the inner select, select aid from agents where city = 'Duluth' or city = 'Dallas'; +-----+ | aid | +-----+ | a05 | | a06 | +-----+ If we just did the outer query by itself select distinct cid from orders; +------+ | cid | +------+ | c001 | | c002 | | c003 | | c004 | | c006 | +------+ Doing the full nested query, (A), gives a more restricted result, +------+ | cid | +------+ | c001 | | c002 | | c004 | | c006 | +------+ The pg. 98 example 3.4.4 is tricky, First, do the straightforward query, +--------+ | cname | +--------+ | Tiptop | | Allied | +--------+ Now, using a subquery select distinct cname from customers where 'p05' in (select pid from orders where cid = customers.cid); This can't be done in pieces, since the inner clause depends on customers in the outer clause. The result is the same, +--------+ | cname | +--------+ | Tiptop | | Allied | +--------+ Here is the result of the original query when we omit "distinct", select cname from customers, orders where customers.cid = orders.cid and orders.pid = 'p05'; +--------+ | cname | +--------+ | Tiptop | | Allied | | Allied | +--------+ --------------------------------------------------------------- --------------------------------------------------------------- Quantified comparison predicate (O'Neil's Sec. 3.4, pg. 101) Example 3.4.9 pg. 102 select cid from customers where discnt 15.0 then call fail('Discount not in allowed range'); end if; end $$ DELIMITER ; (A trigger can be dropped and then re-created.) Now we attempt an improper insert (see the Sec. 3.10, pg. 148, on insert, update, and delete statements) insert into customers (cid, cname, city, discnt) values ('c009', 'WhoopeePie', 'Duluth', 20.0); A call to 'fail' was attempted, but we haven't defined it yet. But the insert was not made when the table is examined. Let's try a legal insert, insert into customers (cid, cname, city, discnt) values ('c008', 'WhoopeePie', 'Duluth', 5.0); Again, the insert was not made, though it appears legal. Let's define 'fail' First, create an Error table (quite complex!) CREATE TABLE `Error` ( `ErrorGID` int(10) unsigned NOT NULL auto_increment, `Message` varchar(128) default NULL, `Created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`ErrorGID`), UNIQUE KEY `MessageIndex` (`Message`)) ENGINE=MEMORY DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED COMMENT='The Fail() procedure writes to this table twice to force a constraint failure.' Then, 'fail' itself: DELIMITER $$ DROP PROCEDURE IF EXISTS `Fail`$$ CREATE PROCEDURE `Fail`(_Message VARCHAR(128)) BEGIN INSERT INTO Error (Message) VALUES (_Message); INSERT INTO Error (Message) VALUES (_Message); END$$ DELIMITER ; This actually works, since trying to put in 20.0 gives: 'Discount not in allowed range' for key 2 Now try OK value, of 5.0 for WhoopeePie No error is reported. Whew! (How to react to this during the operation of the database, I'm not quite sure. But clearly, the procedural code that's allowed can accomplish a lot more than a simple SQL constraint.) As just one example, here's a more substantial trigger code: drop trigger emps.bi_emps_fer // create trigger bi_emps_fer before insert on emps for each row begin declare newsal numeric default 0; declare namelength, l_loop int default 0; if new.emp_name is not null then set namelength = length(new.emp_name); while l_loop < namelength do set newsal := newsal + new.salary; set l_loop := l_loop + 1; end while; set new.salary = newsal; end if; end // Let's reestablish the customers table: drop table if exists customers; create table if not exists customers (cid char(4) not null, cname varchar(13), city char(20), discnt real, primary key(cid)); show fields from customers; load data local infile 'custs.csv' into table customers fields terminated by ','; select * from customers; That restored the original, +------+--------+--------+--------+ | cid | cname | city | discnt | +------+--------+--------+--------+ | c001 | Tiptop | Duluth | 10 | | c002 | Basics | Dallas | 12 | | c003 | Allied | Dallas | 8 | | c004 | ACME | Duluth | 8 | | c006 | ACME | Kyoto | 0 | +------+--------+--------+--------+ --------------------------------------------------------------- --------------------------------------------------------------- SIMPLE TRIGGER EXAMPLE: CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount; Triggers can also be created for UPDATE and DELETE, To see output, you can place a select statement in trigger code. See the tutorial at: http://www.rustyrazorblade.com/index.php/2006/09/14/mysql-triggers-tutorial/ --------------------------------------------------------------- --------------------------------------------------------------- VIEWS - Sec. 7.2 pg. 433 Example 7.2.3 pg. 435 (first one demonstrates bad syntax) Second one is, create view cacities (ccity, acity) as select c.city, a.city from customers c, orders o, agents a where c.cid = o.cid and o.aid = a.aid; then, select * from cacities; +--------+----------+ | ccity | acity | +--------+----------+ | Duluth | New York | | Duluth | New York | | Duluth | Newark | | Duluth | Dallas | | Duluth | Tokyo | | Duluth | New York | | Duluth | Duluth | | Duluth | Duluth | | Dallas | Tokyo | | Dallas | Duluth | | Dallas | Tokyo | | Dallas | Tokyo | | Duluth | Dallas | | Kyoto | New York | | Kyoto | Tokyo | | Kyoto | Dallas | +--------+----------+ and now, show tables; +-----------------+ | Tables_in_btest | +-----------------+ | Error | | agents | | cacities | | customers | | jpegs | | orders | | products | +-----------------+ --------------------------------------------------------------- --------------------------------------------------------------- Information schema. MySQL has a rich information_schema database, see http://www.xcdsql.org/Misc/MySQL_INFORMATION_SCHEMA.html Below is a simple example of querying the information schema. Later, we will see that if a table is a View, it will have that table_type and no engine. SELECT table_name, table_type, engine FROM information_schema.tables WHERE table_schema = 'btest' ORDER BY table_name DESC; +------------+------------+--------+ | table_name | table_type | engine | +------------+------------+--------+ | products | BASE TABLE | MyISAM | | orders | BASE TABLE | MyISAM | | jpegs | BASE TABLE | MyISAM | | customers | BASE TABLE | MyISAM | | agents | BASE TABLE | MyISAM | +------------+------------+--------+ --------------------------------------------------------------- --------------------------------------------------------------- INDEXING Chapter 8 pg. 465 To allow rapid access via cities in customers, create index citiesx on customers (city); drop differs from the text pg. 468 by adding the table name, drop index citiesx on customers; --------------------------------------------------------------- --------------------------------------------------------------- ADDITIONAL MySQL CAPABILITIES OF INTEREST Text: There is a load_file function, text indexing, regular expressions, etc. Spatial extensions: This capability exists. Seems to focus on regions. --------------------------------------------------------------- --------------------------------------------------------------- UPDATE TRANSACTIONS -- Chapter 10, pg. 633 For me to demo these, I would need to set up my server to use the InnoDB storage engine, which supports row locking. I hope to get to that fairly soon. In the meantime, a lot can be said in class about these important concepts.