《SQL Anywhere Studio 9开发指南》代码

ppstone

2.2.4 INSERT Select Column List
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         INTEGER NOT NULL,
   non_key_1     VARCHAR ( 100 ) NOT NULL,
   PRIMARY KEY ( key_1 ) );
CREATE TABLE t2 (
   key_col       INTEGER NOT NULL DEFAULT AUTOINCREMENT,
   col_2         VARCHAR ( 100 ) NOT NULL,
   col_3         VARCHAR ( 100 ) NOT NULL,
   updated_by    VARCHAR ( 128 ) NOT NULL DEFAULT LAST USER,
   last_updated  TIMESTAMP NOT NULL DEFAULT TIMESTAMP,
   PRIMARY KEY ( key_col ) );
INSERT t2 ( col_3, col_2 )
SELECT key_1, non_key_1
  FROM t1;
-----------------------------------------------------------------------------------

2.2.5 INSERT Select With Auto Name
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         INTEGER NOT NULL,
   non_key_1     VARCHAR ( 100 ) NOT NULL,
   PRIMARY KEY ( key_1 ) );
CREATE TABLE t2 (
   key_col       INTEGER NOT NULL DEFAULT AUTOINCREMENT,
   col_2         VARCHAR ( 100 ) NOT NULL,
   col_3         VARCHAR ( 100 ) NOT NULL,
   col_4         VARCHAR ( 100 ) NOT NULL,
   PRIMARY KEY ( key_col ) );
INSERT t2 WITH AUTO NAME
SELECT key_1             AS col_3,
       non_key_1         AS col_2,
       CURRENT TIMESTAMP AS col_4
  FROM t1
WHERE key_1 > 1;
-----------------------------------------------------------------------------------
INSERT t1 VALUES ( DEFAULT, 'aaa', 1, 'bbb', 2 );
-----------------------------------------------------------------------------------
INSERT t1 ( non_key_1,
            non_key_2,
            non_key_3,
            non_key_4 )
   VALUES ( 'aaa',
            1,
            'bbb',
            2 );
-----------------------------------------------------------------------------------
INSERT t1 WITH AUTO NAME
SELECT 'aaa' AS non_key_1,
       1     AS non_key_2,
       'bbb' AS non_key_3,
       2     AS non_key_4;
-----------------------------------------------------------------------------------

ppstone

2.3 LOAD TABLE
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         INTEGER NOT NULL,
   col_2         VARCHAR ( 100 ) NULL,
   col_3         DECIMAL ( 11, 2 ) NULL,
   col_4         TIMESTAMP NULL,
   col_5         INTEGER NOT NULL,
   PRIMARY KEY ( key_1 ) );
LOAD TABLE t1 FROM 'c:\\temp\\t1_a.txt';
-----------------------------------------------------------------------------------
-- Input file...
1,'Hello, World',67.89,2003-09-30 02:15PM,999
2,   stripped string without quotes    ,  0  ,  ,  0
3,,,,
4,"    double-quoted padded string   ",0,2003 9 30,-111
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         INTEGER NOT NULL,
   col_2         INTEGER NOT NULL,
   col_3         TIMESTAMP NULL,
   col_4         DECIMAL ( 11, 2 ) NULL,
   col_5         VARCHAR ( 100 ) NULL,
   PRIMARY KEY ( key_1 ) );
LOAD TABLE t1 ( key_1, filler(), col_4, col_3, col_2 ) FROM 'c:\\temp\\t1_b.txt';
-----------------------------------------------------------------------------------
-- Input file...
1, 'Hello, World', 67.89, 2003-09-30 02:15PM, 999
-----------------------------------------------------------------------------------
LOAD TABLE t1 FROM '\\\\TECRA\\TecraC\\temp\\t1_c.txt';
-----------------------------------------------------------------------------------
BEGIN
DECLARE @filespec VARCHAR ( 1000 );
DECLARE @sql      VARCHAR ( 1000 );
SET @filespec = '\\\\\\\\TECRA\\\\TecraC\\\\temp\\\\t1_c.txt';
SET @sql = STRING ( 'LOAD TABLE t1 FROM ''', @filespec, '''' );
EXECUTE IMMEDIATE @sql;
END;
-----------------------------------------------------------------------------------
CREATE TABLE raw_text (
   line_number          BIGINT NOT NULL DEFAULT AUTOINCREMENT,
   line_text            LONG VARCHAR NOT NULL DEFAULT '',
   PRIMARY KEY ( line_number ) );
LOAD TABLE raw_text ( line_text )
   FROM 'c:\\temp\\test.txt'
   DEFAULTS ON
   DELIMITED BY ''
   ESCAPES OFF
   QUOTES OFF
   STRIP OFF;
-----------------------------------------------------------------------------------
-- Input file...
This is a flat text file, containing free-form text with embedded
commas, 'single-quotes' and "double-quotes". Even lines with
'leading and trailing quotes will be stored as-is.'
It will be stored in the line_text column as-is, line-by-line, with
one line per row. Empty lines
will be ignored, but blank lines consisting of at least one space

will be stored. Trailing blanks will be stored,
     and so will leading blanks.
Backslash characters \, \\, \\\, \\\\, etc., will be stored as-is.
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         INTEGER NOT NULL,
   col_2         INTEGER NOT NULL,
   PRIMARY KEY ( key_1 ) );
LOAD TABLE t1 FROM 't1_d.txt';
-----------------------------------------------------------------------------------
BEGIN
   DECLARE LOCAL TEMPORARY TABLE temp_t1 (
      key_1         INTEGER NOT NULL,
      col_2         INTEGER NOT NULL,
      PRIMARY KEY ( key_1 ) )
      NOT TRANSACTIONAL;
   LOAD TABLE temp_t1 FROM 't1_d.txt';
   INSERT t1 SELECT * FROM temp_t1;
END;
-----------------------------------------------------------------------------------

ppstone

2.4 ISQL INPUT
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         INTEGER NOT NULL,
   col_2         VARCHAR ( 100 ) NULL,
   col_3         DECIMAL ( 11, 2 ) NULL,
   col_4         TIMESTAMP NULL,
   col_5         INTEGER NOT NULL,
   PRIMARY KEY ( key_1 ) );
INPUT INTO t1 FROM 'c:\\temp\\t1_a.txt';
-----------------------------------------------------------------------------------
-- Input file...
1,'Hello, World',67.89,2003-09-30 02:15PM,999
2,   stripped string without quotes    ,  0  ,  ,  0
3,,,,
4,"    double-quoted padded string   ",0,2003 9 30,-111
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         INTEGER NOT NULL,
   col_2         INTEGER NOT NULL,
   col_3         TIMESTAMP NULL,
   col_4         DECIMAL ( 11, 2 ) NULL,
   col_5         VARCHAR ( 100 ) NULL,
   PRIMARY KEY ( key_1 ) );
INPUT ( key_1, col_5, col_4, col_3, col_2 ) FROM 'c:\\temp\\t1_e.txt' INTO t1 ;
-----------------------------------------------------------------------------------
-- Input file...
1, 'Hello, World', 67.89, 2003-09-30 02:15PM, 999
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   col_1         INTEGER NOT NULL,
   col_2         INTEGER NOT NULL );
CALL sa_server_option ( 'Request_level_log_file', 'r.txt' );
CALL sa_server_option ( 'Request_level_logging', 'SQL+hostvars' );
LOAD TABLE t1 FROM 't1_f.txt';
INPUT INTO t1 FROM 't1_f.txt';
CALL sa_server_option ( 'Request_level_logging', 'NONE' );
-----------------------------------------------------------------------------------
-- Input file...
1, 1
2, 2
-----------------------------------------------------------------------------------

ppstone

Chapter 3 - SELECTING
-----------------------------------------------------------------------------------

3.1 Introduction
-----------------------------------------------------------------------------------

3.2 Logical Execution of a SELECT
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         INTEGER NOT NULL,
   non_key_1     INTEGER NOT NULL,
   PRIMARY KEY ( key_1 ) );
CREATE TABLE t2 (
   key_1         INTEGER NOT NULL,
   key_2         INTEGER NOT NULL,
   PRIMARY KEY ( key_1, key_2 ),
   FOREIGN KEY fk_t1 ( key_1 ) REFERENCES t1 ( key_1 ) );
CREATE TABLE t3 (
   key_1         INTEGER NOT NULL,
   non_key_1     INTEGER NOT NULL,
   PRIMARY KEY ( key_1 ) );
INSERT t1 VALUES ( 1, 1 );
INSERT t1 VALUES ( 2, 2 );
INSERT t2 VALUES ( 2, 21 );
INSERT t2 VALUES ( 2, 22 );
INSERT t2 VALUES ( 2, 23 );
INSERT t3 VALUES ( 3, 333 );
INSERT t3 VALUES ( 4, 333 );
INSERT t3 VALUES ( 5, 0 );
INSERT t3 VALUES ( 6, 333 );
SELECT DISTINCT
       TOP 4 START AT 2
       t1.key_1 * 100       AS a,
       t3.key_1 * 1000      AS b,
       COUNT(*)             AS c,
       SUM ( t3.non_key_1 ) AS d
  FROM ( t1 LEFT OUTER JOIN t2 ON t1.key_1 = t2.key_1 )
       CROSS JOIN t3
WHERE b <= 5000
   AND t3.non_key_1 = 333
GROUP BY ROLLUP ( t1.key_1, t3.key_1 )
HAVING COUNT(*) > 1
ORDER BY 1, 2;
-----------------------------------------------------------------------------------
-- Step 1
SELECT t1.key_1,
       t1.non_key_1,
       t2.key_1,
       t2.key_2,
       t3.key_1,
       t3.non_key_1
  FROM ( t1 LEFT OUTER JOIN t2 ON t1.key_1 = t2.key_1 )
       CROSS JOIN t3;
-----------------------------------------------------------------------------------
-- Step 2
SELECT t1.key_1,
       t1.non_key_1,
       t2.key_1,
       t2.key_2,
       t3.key_1,
       t3.non_key_1,
       t1.key_1 * 100  AS a,
       t3.key_1 * 1000 AS b
  FROM ( t1 LEFT OUTER JOIN t2 ON t1.key_1 = t2.key_1 )
       CROSS JOIN t3;
-----------------------------------------------------------------------------------
-- Step 3
SELECT t1.key_1,
       t1.non_key_1,
       t2.key_1,
       t2.key_2,
       t3.key_1,
       t3.non_key_1,
       t1.key_1 * 100       AS a,
       t3.key_1 * 1000      AS b
  FROM ( t1 LEFT OUTER JOIN t2 ON t1.key_1 = t2.key_1 )
       CROSS JOIN t3
WHERE b <= 5000
   AND t3.non_key_1 = 333;
-----------------------------------------------------------------------------------
-- Step 12
SELECT DISTINCT
       TOP 4 START AT 2
       t1.key_1 * 100       AS a,
       t3.key_1 * 1000      AS b,
       COUNT(*)             AS c,
       SUM ( t3.non_key_1 ) AS d
  FROM ( t1 LEFT OUTER JOIN t2 ON t1.key_1 = t2.key_1 )
       CROSS JOIN t3
WHERE b <= 5000
   AND t3.non_key_1 = 333
GROUP BY ROLLUP ( t1.key_1, t3.key_1 )
HAVING COUNT(*) > 1
ORDER BY 1, 2;
-----------------------------------------------------------------------------------
-- Step 13
SELECT --DISTINCT
       TOP 4 START AT 2
       t1.key_1 * 100       AS a,
       t3.key_1 * 1000      AS b,
       COUNT(*)             AS c,
       SUM ( t3.non_key_1 ) AS d,
       NUMBER(*)            AS e
  FROM ( t1 LEFT OUTER JOIN t2 ON t1.key_1 = t2.key_1 )
       CROSS JOIN t3
WHERE b <= 5000
   AND t3.non_key_1 = 333
GROUP BY ROLLUP ( t1.key_1, t3.key_1 )
HAVING COUNT(*) > 1
ORDER BY 1, 2;
-----------------------------------------------------------------------------------
-- Step 15
SELECT DISTINCT
       TOP 4 START AT 2
       t1.key_1 * 100       AS a,
       t3.key_1 * 1000      AS b,
       COUNT(*)             AS c,
       SUM ( t3.non_key_1 ) AS d
  INTO #t
  FROM ( t1 LEFT OUTER JOIN t2 ON t1.key_1 = t2.key_1 )
       CROSS JOIN t3
WHERE b <= 5000
   AND t3.non_key_1 = 333
GROUP BY ROLLUP ( t1.key_1, t3.key_1 )
HAVING COUNT(*) > 1
ORDER BY 1, 2
   FOR XML AUTO;

ppstone

3.3 FROM Clause
-----------------------------------------------------------------------------------

3.4 JOIN [Top]
-----------------------------------------------------------------------------------

3.4.1 CROSS JOIN
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   c1  INTEGER NOT NULL );
CREATE TABLE t2 (
   c1  INTEGER NOT NULL,
   c2  INTEGER NOT NULL );
INSERT t1 VALUES ( 1 );
INSERT t1 VALUES ( 2 );
INSERT t2 VALUES ( 1, 7 );
INSERT t2 VALUES ( 1, 8 );
INSERT t2 VALUES ( 1, 9 );
SELECT t1.c1,
       t2.c1,
       t2.c2
  FROM t1 CROSS JOIN t2
ORDER BY t1.c1,
       t2.c1,
       t2.c2;
-----------------------------------------------------------------------------------

3.4.2 INNER JOIN
-----------------------------------------------------------------------------------
CREATE TABLE parent (
   parent_key    INTEGER NOT NULL,
   data_1        VARCHAR ( 1 ) NOT NULL,
   PRIMARY KEY ( parent_key ) );
CREATE TABLE child (
   child_key     INTEGER NOT NULL PRIMARY KEY,
   parent_key    INTEGER NULL REFERENCES parent ( parent_key ) );
INSERT parent VALUES ( 1, 'x' ); -- parent with 3 children
INSERT parent VALUES ( 2, 'x' ); -- parent with no children
INSERT parent VALUES ( 3, 'y' ); -- parent with no children
INSERT child VALUES ( 4, 1 );    -- child with parent
INSERT child VALUES ( 5, 1 );    -- child with parent
INSERT child VALUES ( 6, 1 );    -- child with parent
INSERT child VALUES ( 7, NULL ); -- orphan
SELECT parent.parent_key,
       parent.data_1,
       child.child_key,
       child.parent_key
  FROM parent INNER JOIN child ON parent.parent_key = child.parent_key
ORDER BY parent.parent_key,
       child.child_key;
-----------------------------------------------------------------------------------

3.4.3 LEFT OUTER JOIN
-----------------------------------------------------------------------------------
SELECT parent.parent_key,
       parent.data_1,
       child.child_key,
       child.parent_key
  FROM parent LEFT OUTER JOIN child ON parent.parent_key = child.parent_key
ORDER BY parent.parent_key,
       child.child_key;
-----------------------------------------------------------------------------------

ninetailsfox

3.4.4 RIGHT OUTER JOIN [Top]
-----------------------------------------------------------------------------------
SELECT parent.parent_key,
       parent.data_1,
       child.child_key,
       child.parent_key
  FROM parent RIGHT OUTER JOIN child ON parent.parent_key = child.parent_key
ORDER BY parent.parent_key,
       child.child_key;
-----------------------------------------------------------------------------------
SELECT parent.parent_key,
       parent.data_1,
       child.child_key,
       child.parent_key
  FROM child LEFT OUTER JOIN parent ON parent.parent_key = child.parent_key
ORDER BY parent.parent_key,
       child.child_key;
-----------------------------------------------------------------------------------

3.4.5 FULL OUTER JOIN [Top]
-----------------------------------------------------------------------------------
SELECT parent.parent_key,
       parent.data_1,
       child.child_key,
       child.parent_key
  FROM parent FULL OUTER JOIN child ON parent.parent_key = child.parent_key
ORDER BY parent.parent_key,
       child.child_key;
-----------------------------------------------------------------------------------
SELECT parent.parent_key,
       parent.data_1,
       child.child_key,
       child.parent_key
  FROM parent LEFT OUTER JOIN child ON  parent.parent_key = child.parent_key
                                    AND parent.data_1     = 'x'
ORDER BY parent.parent_key,
       child.child_key;
-----------------------------------------------------------------------------------

3.5 Derived Tables [Top]
-----------------------------------------------------------------------------------
SELECT parent.parent_key,
       parent.data_1,
       child.child_key,
       child.parent_key
  FROM ( SELECT *
           FROM parent
          WHERE parent.data_1 = 'x' ) AS parent
       LEFT OUTER JOIN child ON parent.parent_key = child.parent_key
ORDER BY parent.parent_key,
       child.child_key;
-----------------------------------------------------------------------------------
SELECT parent.parent_key,
       parent.data_1,
       child.child_key,
       child.parent_key
  FROM parent FULL OUTER JOIN child ON parent.parent_key = child.parent_key
WHERE parent.data_1 = 'x'
ORDER BY parent.parent_key,
       child.child_key;
-----------------------------------------------------------------------------------
SELECT parent.parent_key,
       parent.data_1,
       child.child_key,
       child.parent_key
  FROM ( SELECT *
           FROM parent
          WHERE parent.data_1 = 'x' ) AS parent
       FULL OUTER JOIN child ON parent.parent_key = child.parent_key
ORDER BY parent.parent_key,
       child.child_key;
-----------------------------------------------------------------------------------

ninetailsfox

3.6 Multi-Table Joins [Top]
-----------------------------------------------------------------------------------
CREATE TABLE customer (
   id             INTEGER NOT NULL DEFAULT AUTOINCREMENT,
   fname          CHAR ( 15 ) NOT NULL,
   lname          CHAR ( 20 ) NOT NULL,
   address        CHAR ( 35 ) NOT NULL,
   city           CHAR ( 20 ) NOT NULL,
   state          CHAR ( 16 ) NULL,
   zip            CHAR ( 10 ) NULL,
   phone          CHAR ( 12 ) NOT NULL,
   company_name   CHAR ( 35 ) NULL,
   PRIMARY KEY ( id ) );
CREATE TABLE employee (
   emp_id             INTEGER NOT NULL PRIMARY KEY,
   manager_id         INTEGER NULL,
   emp_fname          CHAR ( 20 ) NOT NULL,
   emp_lname          CHAR ( 20 ) NOT NULL,
   dept_id            INTEGER NOT NULL,
   street             CHAR ( 40 ) NOT NULL,
   city               CHAR ( 20 ) NOT NULL,
   state              CHAR ( 16 ) NULL,
   zip_code           CHAR ( 10 ) NULL,
   phone              CHAR ( 10 ) NULL,
   status             CHAR ( 2 ) NULL,
   ss_number          CHAR ( 11 ) NULL,
   salary             NUMERIC ( 20, 3 ) NOT NULL,
   start_date         DATE NOT NULL,
   termination_date   DATE NULL,
   birth_date         DATE NULL,
   bene_health_ins    CHAR ( 2 ) NULL,
   bene_life_ins      CHAR ( 2 ) NULL,
   bene_day_care      CHAR ( 2 ) NULL,
   sex                CHAR ( 2 ) NULL );
CREATE TABLE fin_code (
   code          CHAR ( 2 ) NOT NULL PRIMARY KEY,
   type          CHAR ( 10 ) NOT NULL,
   description   CHAR ( 50 ) NULL );
CREATE TABLE product (
   id            INTEGER NOT NULL,
   name          CHAR ( 15 ) NOT NULL,
   description   CHAR ( 30 ) NOT NULL,
   size          CHAR ( 18 ) NOT NULL,
   color         CHAR ( 6 ) NOT NULL,
   quantity      INTEGER NOT NULL,
   unit_price    NUMERIC ( 15, 2 ) NOT NULL,
   PRIMARY KEY ( id ) );
CREATE TABLE sales_order (
   id            INTEGER NOT NULL DEFAULT AUTOINCREMENT,
   cust_id       INTEGER NOT NULL REFERENCES customer ( id ),
   order_date    DATE NOT NULL,
   fin_code_id   CHAR ( 2 ) NULL REFERENCES fin_code ( code ),
   region        CHAR ( 7 ) NULL,
   sales_rep     INTEGER NOT NULL REFERENCES employee ( emp_id ),
   PRIMARY KEY ( id ) );
CREATE TABLE sales_order_items (
   id          INTEGER NOT NULL REFERENCES sales_order ( id ),
   line_id     SMALLINT NOT NULL,
   prod_id     INTEGER NOT NULL REFERENCES product ( id ),
   quantity    INTEGER NOT NULL,
   ship_date   DATE NOT NULL,
   PRIMARY KEY ( id, line_id ) );
-----------------------------------------------------------------------------------
SELECT customer.company_name,
       sales_order.order_date,
       product.name,
       product.description,
       sales_order_items.quantity,
       product.unit_price * sales_order_items.quantity AS amount
  FROM customer
       INNER JOIN sales_order
               ON sales_order.cust_id = customer.id
       INNER JOIN sales_order_items
               ON sales_order_items.id = sales_order.id
       INNER JOIN product
               ON product.id = sales_order_items.prod_id
ORDER BY customer.company_name,
       sales_order.order_date,
       product.name;
-----------------------------------------------------------------------------------
  FROM ( ( ( customer
             INNER JOIN sales_order
                     ON sales_order.cust_id = customer.id )
           INNER JOIN sales_order_items
                   ON sales_order_items.id = sales_order.id )
         INNER JOIN product
                 ON product.id = sales_order_items.prod_id )
-----------------------------------------------------------------------------------
SELECT customer.company_name              AS company_name,
       product.name                       AS product_name,
       product.description                AS product_description,
       SUM ( sales_order_items.quantity ) AS quantity,
       SUM ( product.unit_price
           * sales_order_items.quantity ) AS amount
  FROM ( customer
         CROSS JOIN product )
       LEFT OUTER JOIN
       ( sales_order
         INNER JOIN sales_order_items
                 ON sales_order_items.id = sales_order.id )
       ON  customer.id = sales_order.cust_id
       AND product.id  = sales_order_items.prod_id
WHERE customer.state = 'DC'
   AND product.name LIKE '%shirt%'
  GROUP BY customer.company_name,
       product.name,
       product.description
ORDER BY customer.company_name,
       product.name,
       product.description;
-----------------------------------------------------------------------------------
SELECT sales_order.order_date        AS order_date,
       sales_order.id                AS order_id,
       customer.company_name         AS customer_name,
       STRING ( employee.emp_fname,
                ' ',
                employee.emp_lname ) AS rep_name,
       fin_code.description          AS fin_code
  FROM sales_order
       INNER JOIN customer
           ON sales_order.cust_id = customer.id
       INNER JOIN employee
           ON sales_order.sales_rep = employee.emp_id
       INNER JOIN fin_code
           ON sales_order.fin_code_id = fin_code.code
WHERE sales_order.order_date BETWEEN '2000-01-02' AND '2000-01-06'
ORDER BY order_date,
       order_id;
-----------------------------------------------------------------------------------

ninetailsfox

3.7 SELECT FROM Procedure Call [Top]
-----------------------------------------------------------------------------------
CREATE PROCEDURE p_best_losers_in_worst_year()
BEGIN
DECLARE @worst_year INTEGER;
-- Determine the worst year for total sales.
SELECT FIRST
       YEAR ( sales_order.order_date )
  INTO @worst_year
  FROM product
       INNER JOIN sales_order_items
          ON product.id = sales_order_items.prod_id
       INNER JOIN sales_order
          ON sales_order_items.id = sales_order.id
GROUP BY YEAR ( sales_order.order_date )
ORDER BY SUM ( sales_order_items.quantity * product.unit_price ) ASC;
-- Find the second- and third-best sales for a single color on a
-- single day in the worst year.
SELECT TOP 2 START AT 2
       product.color          AS best_color,
       sales_order.order_date AS best_day,
       SUM ( sales_order_items.quantity * product.unit_price ) AS sales_amount,
       NUMBER(*) + 1          AS rank
  FROM product
       INNER JOIN sales_order_items
          ON product.id = sales_order_items.prod_id
       INNER JOIN sales_order
          ON sales_order_items.id = sales_order.id
WHERE YEAR ( sales_order.order_date ) = @worst_year
GROUP BY product.color,
       sales_order.order_date
ORDER BY SUM ( sales_order_items.quantity * product.unit_price ) DESC;
END;
-----------------------------------------------------------------------------------
CALL p_best_losers_in_worst_year();
-----------------------------------------------------------------------------------
SELECT DISTINCT
       product.id,
       product.name,
       product.description,
       product.color,
       best_loser.rank
  FROM p_best_losers_in_worst_year() AS best_loser
       INNER JOIN product
          ON product.color = best_loser.best_color
       INNER JOIN sales_order_items
          ON product.id = sales_order_items.prod_id
       INNER JOIN sales_order
          ON sales_order_items.id   = sales_order.id
         AND sales_order.order_date = best_loser.best_day
ORDER BY best_loser.rank ASC,
       product.id ASC;
-----------------------------------------------------------------------------------
SELECT DISTINCT
       product.id,
       product.name,
       product.description,
       product.color,
       best_loser.ranking
  FROM p_best_losers_in_worst_year()
          WITH ( best_color VARCHAR ( 6 ),
                 best_day   DATE,
                 best_sales NUMERIC ( 15, 2 ),
                 ranking    INTEGER )
            AS best_loser
       INNER JOIN product
          ON product.color = best_loser.best_color
       INNER JOIN sales_order_items
          ON product.id = sales_order_items.prod_id
       INNER JOIN sales_order
          ON sales_order_items.id   = sales_order.id
         AND sales_order.order_date = best_loser.best_day
ORDER BY best_loser.ranking ASC,
       product.id ASC;
-----------------------------------------------------------------------------------

ninetailsfox

3.8 LATERAL Procedure Call [Top]
-----------------------------------------------------------------------------------
CREATE PROCEDURE p_customer_orders ( IN @customer_id INTEGER )
BEGIN
MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' ', @customer_id ) TO CONSOLE;
SELECT sales_order.order_date         AS order_date,
       product.name                   AS product_name,
       product.description            AS description,
       sales_order_items.quantity     AS quantity,
       product.unit_price
         * sales_order_items.quantity AS amount
  FROM sales_order
       INNER JOIN sales_order_items
               ON sales_order_items.id = sales_order.id
       INNER JOIN product
               ON product.id = sales_order_items.prod_id
WHERE sales_order.cust_id = @customer_id
ORDER BY order_date,
       product_name,
       description;
END;
CALL p_customer_orders ( 141 );
-----------------------------------------------------------------------------------
SELECT customer.company_name,
       customer_orders.*
  FROM customer,
       LATERAL ( p_customer_orders ( customer.id ) ) AS customer_orders
WHERE customer.company_name = 'Mall Side Sports'
ORDER BY customer_orders.order_date,
       customer_orders.product_name,
       customer_orders.description;
-----------------------------------------------------------------------------------

3.9 SELECT List [Top]
-----------------------------------------------------------------------------------
SELECT product.name,
       *
  FROM product
       INNER JOIN sales_order_items
               ON sales_order_items.prod_id = product.id
       INNER JOIN sales_order
               ON sales_order.id = sales_order_items.id
ORDER BY product.name,
       sales_order.order_date DESC;
-----------------------------------------------------------------------------------
SELECT product.name,
       sales_order_items.*,
       *
  FROM product
       INNER JOIN sales_order_items
               ON sales_order_items.prod_id = product.id
       INNER JOIN sales_order
               ON sales_order.id = sales_order_items.id
ORDER BY product.name,
       sales_order.order_date DESC;
-----------------------------------------------------------------------------------

ninetailsfox

3.10 Expressions and Operators [Top]
-----------------------------------------------------------------------------------
SELECT CURRENT DATE - 1             AS yesterday,
       CURRENT DATE                 AS today,
       CURRENT DATE + 1             AS tomorrow,
       ( SELECT MAX ( unit_price )
           FROM product )           AS max_price,
       ( SELECT COUNT(*)
           FROM product )           AS products,
       ( SELECT COUNT(*)
           FROM sales_order )       AS orders,
       ( SELECT SUM ( quantity )
           FROM sales_order_items ) AS items;
-----------------------------------------------------------------------------------
SELECT product.id,
       product.unit_price * product.quantity   AS stock_value,
       product.unit_price
          * ( SELECT SUM ( quantity )
                FROM sales_order_items
               WHERE sales_order_items.prod_id
                   = product.id )              AS sales_value,
       ( stock_value / sales_value ) * 100.00  AS percent
  FROM product
ORDER BY sales_value DESC;
-----------------------------------------------------------------------------------