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