SQL: Practical Guide for Developers
MySQL

Corrected Queries for MySQL

Query 2.8 (see String Syntax):

   SELECT repfname, replname
   FROM vendors 
   WHERE companyname = 'Don\'s Dairy';

Query 3.14 (see String Syntax):

   SELECT vendorid, companyname,
       TRIM(TRAILING '\'S' FROM
	        TRIM(SUBSTRING(UPPER(companyname) FROM 1 FOR 
               POSITION(' ' IN companyname)))) AS "CoName"
   FROM vendors;

Query 3.15 (see Date Objects):

   SELECT name, dateadded, DATEDIFF('2005-01-02', dateadded) AS "Days on Menu"
   FROM items;

Query 7.19 (see NULL Comparison):

SELECT companyname
FROM vendors
WHERE referredby NOT IN 
        (SELECT vendorid
         FROM vendors
         WHERE companyname = 'No Such Company') OR
    referredby IS NULL;

Query 7.39 (see Derived Table Names):

SELECT food, companyname, val
FROM vendors v, (SELECT name AS food, vendorid AS vdrno, unitprice * inventory AS val
                 FROM ingredients i
                 WHERE foodgroup IN ('Fruit', 'Vegetable')) AS d
WHERE v.vendorid = d.vdrno;

Query 7.40 (see Derived Table Names):

SELECT p.companyname
FROM (SELECT COUNT(*) AS items
      FROM ingredients i, vendors v
      WHERE i.vendorid = v.vendorid AND
            companyname = 'Spring Water Supply') AS q,
     (SELECT companyname, COUNT(*) AS items
      FROM ingredients i, vendors v
      WHERE i.vendorid = v.vendorid
      GROUP BY v.vendorid, companyname) AS p
WHERE p.items > q.items;

Query 7.41 (see Derived Table Names):

SELECT name, price - cost AS profit
FROM items theitm, (SELECT itm.itemid AS itemid, SUM(quantity * unitprice) AS cost
                    FROM items itm, madewith mw, ingredients ing
                    WHERE itm.itemid = mw.itemid AND
                          mw.ingredientid = ing.ingredientid
                    GROUP BY itm.itemid) AS itemcost
WHERE theitm.itemid = itemcost.itemid;

Query 7.44 (see Derived Table Names):

SELECT manager
FROM stores s, orders o
WHERE s.storeid = o.storeid
GROUP BY s.storeid, manager
HAVING SUM(price) > (SELECT AVG(sales) 
                     FROM (SELECT SUM(price) AS sales
                           FROM orders
                           GROUP BY storeid) AS d);

Query 8.3 (see Inserting DEFAULT rows):

INSERT INTO ads VALUES();

Query 9.12 (see Naming Constraints):

CREATE TABLE ingredients(
    ingredientid CHAR(5) PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    unit CHAR(10),
    unitprice NUMERIC(5, 2),
    foodgroup CHAR(15) CHECK(foodgroup IN ('Milk', 'Meat', 'Bread', 'Fruit', 
                                           'Vegetable')),
    inventory INTEGER DEFAULT 0,    
    vendorid CHAR(5),
    CHECK(unitprice * inventory <= 4000),
    FOREIGN KEY(vendorid) REFERENCES vendors(vendorid),
    CONSTRAINT vidfk FOREIGN KEY(ingredientid) REFERENCES vendors(vendorid)
);

Query 9.13 (see CREATE TABLE AS):

CREATE TABLE sales
SELECT s.storeid, manager, SUM(price) AS total
FROM orders o, stores s
WHERE o.storeid = s.storeid
GROUP BY s.storeid, s.manager;

Query 9.27 (see DROP CONSTRAINT):

ALTER TABLE ingredients
DROP FOREIGN KEY vidfk;

Query 9.28 (see GENERATED BY):

CREATE TABLE purchases(
    orderno INTEGER NOT NULL AUTO_INCREMENT,
    vendorid CHAR(5) REFERENCES vendors,
    ordertime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(orderno, vendorid)
);

Query 9.34 (see Temporary Tables):

CREATE TEMPORARY TABLE temp(
    intKey INTEGER,
    charValue VARCHAR(100)
);

Query 10.6 (see Cast):

CREATE VIEW menuitems (menuitems, name, price) AS
    (SELECT m.mealid, m.name, CAST(SUM(price*(1-discount)) AS DECIMAL)
    FROM meals m LEFT OUTER JOIN partof p ON m.mealid = p.mealid
        LEFT OUTER JOIN items i ON p.itemid = i.itemid
    GROUP BY m.mealid, m.name)
UNION
    (SELECT itemid, name, price
     FROM items);

Query 13.2 (see CREATE PROCUDRE/FUNCTION Syntax):

CREATE PROCEDURE AllergyMenu (OUT allergen VARCHAR(30)) 
BEGIN
    SELECT name, price
    FROM items IT
    WHERE NOT EXISTS
        (SELECT *
        FROM madewith m JOIN ingredients ig ON (m.ingredientid = ig.ingredientid)
        WHERE it.itemid = m.itemid AND ig.name = allergen);
END;

Query 13.3 (see CREATE TRIGGER Syntax):

CREATE TRIGGER markup
AFTER UPDATE ON ingredients
FOR EACH ROW BEGIN
    UPDATE items SET price =
        (SELECT 2 * SUM(quantity * unitprice)
        FROM madewith m, ingredients i
        WHERE m.ingredientid = i.ingredientid AND items.itemid = m.itemid);
END;

Query 13.4 (see CREATE TRIGGER Syntax):

CREATE TRIGGER logging
BEFORE DELETE ON items
FOR EACH ROW BEGIN
    INSERT INTO logs VALUES(CURRENT_DATE, OLD.itemid);
END