1. Работа в "Корпорацией Кинга" среде интерактивного SQL

• Выбрать среднегодовую сумму заказов покупателя 'REBOUND SPORTS'.

DB2

------------------------------ Command Entered ------------------------------
SELECT s/ny
  FROM
    (SELECT SUM(total) AS s
       FROM customer, sales_order
       WHERE customer.customer_id = sales_order.customer_id
       AND name='REBOUND SPORTS') t1,
    (SELECT COUNT(*) AS ny
       FROM (SELECT DISTINCT YEAR(order_date)
               FROM customer, sales_order
               WHERE customer.customer_id = sales_order.customer_id
               AND name='REBOUND SPORTS') t3) t2 ;
-----------------------------------------------------------------------------
1                                
---------------------------------
                          1810.86
  1 record(s) selected.

Oracle

SQL> SELECT s/ny
  2    FROM
  3      (SELECT SUM(total) AS s
  4         FROM customer, sales_order
  5         WHERE customer.customer_id = sales_order.customer_id
  6         AND name='REBOUND SPORTS') t1,
  7      (SELECT COUNT(*) AS ny
  8         FROM (SELECT DISTINCT TO_CHAR(order_date,'YYYY')
  9                 FROM customer, sales_order
 10                 WHERE customer.customer_id = sales_order.customer_id
 11                 AND name='REBOUND SPORTS') t3) t2 ;

     S/NY
---------
1810.8667

• Определить минимальную сумму заказа товара 'SB ENERGY BAR-6 PACK'

DB2

------------------------------ Command Entered ------------------------------
SELECT MIN(total)
  FROM item, product
  WHERE item.product_id = product.product_id
  AND description = 'SB ENERGY BAR-6 PACK';
-----------------------------------------------------------------------------
1         
----------
      2.40
  1 record(s) selected.

Oracle

SQL> SELECT MIN(total)
  2    FROM item, product
  3    WHERE item.product_id = product.product_id
  4    AND description = 'SB ENERGY BAR-6 PACK';

MIN(TOTAL)
----------
       2.4

• Выбрать продавца, у которого наивысшее отношение комиссионные / зарплата.

DB2

SELECT first_name, last_name, commission/salary
   FROM employee
   WHERE commission IS NOT NULL
   AND  commission/salary = 
          (SELECT MAX(r)
             FROM
               (SELECT commission/salary AS r
                  FROM employee
                  WHERE commission IS NOT NULL) t1
           );
-----------------------------------------------------------------------------
FIRST_NAME      LAST_NAME       3                                
--------------- --------------- ---------------------------------
KENNETH         MARTIN                 1.120000000000000000000000
  1 record(s) selected.

Oracle

SQL> SELECT first_name, last_name, commission/salary
  2     FROM employee
  3     WHERE commission IS NOT NULL
  4     AND  commission/salary = 
  5            (SELECT MAX(r)
  6               FROM
  7                 (SELECT commission/salary AS r
  8                    FROM employee
  9                    WHERE commission IS NOT NULL) t1
 10             );

FIRST_NAME      LAST_NAME       COMMISSION/SALARY
--------------- --------------- -----------------
KENNETH         MARTIN                       1.12

• Выбрать те товары, которые со времени своего выставления в продажу подорожали более, чем на 10%

DB2

------------------------------ Command Entered ------------------------------
SELECT description, old, new 
  FROM
   (SELECT description, product.product_id AS p1, list_price AS new
     FROM product, price
     WHERE product.product_id = price.product_id
     AND end_date IS NULL) t1,
   (SELECT product.product_id AS p2, MIN(list_price) AS old
     FROM product, price
     WHERE product.product_id = price.product_id
     GROUP BY product.product_id) t2
  WHERE p1=p2
  AND old*1.1<new ;
-----------------------------------------------------------------------------
DESCRIPTION                    OLD        NEW       
------------------------------ ---------- ----------
ACE TENNIS RACKET I                 30.00      35.00
ACE TENNIS RACKET II                39.00      45.00
ACE TENNIS BALLS-3 PACK              2.40       2.80
ACE TENNIS BALLS-6 PACK              4.80       5.60
DUNK BASKETBALL OUTDOOR             23.60      26.00
DUNK BASKETBALL PROFESSIONAL        50.00      58.30
YELLOW JERSEY BICYCLE HELMET        36.30      40.00
YELLOW JERSEY BICYCLE GLOVES        10.00      15.00
YELLOW JERSEY WATER BOTTLE           2.00       3.00
YELLOW JERSEY BOTTLE CAGE            5.00       6.00
  10 record(s) selected.

Oracle

SQL> SELECT description, old, new 
  2    FROM
  3     (SELECT description, product.product_id AS p1, list_price AS new
  4       FROM product, price
  5       WHERE product.product_id = price.product_id
  6       AND end_date IS NULL) t1,
  7     (SELECT product.product_id AS p2, MIN(list_price) AS old
  8       FROM product, price
  9       WHERE product.product_id = price.product_id
 10       GROUP BY product.product_id) t2
 11    WHERE p1=p2
 12    AND old*1.1<new ;

DESCRIPTION                          OLD       NEW
------------------------------ --------- ---------
ACE TENNIS RACKET I                   30        35
ACE TENNIS RACKET II                  39        45
ACE TENNIS BALLS-3 PACK              2.4       2.8
ACE TENNIS BALLS-6 PACK              4.8       5.6
DUNK BASKETBALL OUTDOOR             23.6        26
DUNK BASKETBALL PROFESSIONAL          50      58.3
YELLOW JERSEY BICYCLE HELMET        36.3        40
YELLOW JERSEY BICYCLE GLOVES          10        15
YELLOW JERSEY WATER BOTTLE             2         3
YELLOW JERSEY BOTTLE CAGE              5         6

10 rows selected.