plsqL復習

1. 下單超過4次的女顧客的查詢語句：
   

   SELECT c.customer_id, COUNT(o.order_id) AS orders_ct
   

     FROM customers c

     JOIN orders o

       ON c.customer_id = o.customer_id

    WHERE c.gender = 'F'

    GROUP BY c.customer_id

   HAVING COUNT(o.order_id) > 4

     8   ORDER BY orders_ct, c.customer_id

     9  ;

   CUSTOMER_ID ORDERS_CT

   ----------- ---------

          $146   $5

          $147   $5

2. SELECT c.customer_id cust_id, o.order_id ord_id, c.gender

     FROM customers c

  JOIN orders o

       ON c.customer_id = o.customer_id ;

  CUST_ID    ORD_ID G

--------- --------- -

   $147     $2450 F

   $147     $2425 F

   $147     $2385 F

   $147     $2366 F

   $147     $2396 F

   $148     $2451 M

   $148     $2426 M

   $148     $2386 M

    $148     $2367 M

3.Group by語句：

SELECT c.customer_id, COUNT(o.order_id) AS orders_ct

  FROM customers c

  JOIN orders o

    ON c.customer_id = o.customer_id

 WHERE gender = 'F'

 GROUP BY c.customer_id;

CUSTOMER_ID ORDERS_CT

----------- ---------

       $123   $1

       $147   $5

       $107   $4

       $154   $1

       $169   $1

       $104   $4

       $105   $4

       $146   $5

       $156   $1

       $166   $1

       $103   $4

11 rows selected.

4.Select列表：

SELECT c.customer_id,

       c.cust_first_name || '' || c.cust_last_name,

       (SELECT e.last_name

          FROM hr.employees e

         WHERE e.employee_id = c.account_mgr_id) acct_mgr

  FROM oe.customers c;

CUSTOMER_ID C.CUST_FIRST_NAME||''||C.CUST_LAST_NAME  ACCT_MGR

----------- ---------------------------------------- -------------------------

       $147 IshwaryaRoberts     Russell

       $148 GustavSteenburgen     Russell

       $149 MarkusRampling     Russell

       $150 GoldieSlater     Russell

       $151 DivineAykroyd     Russell

       $152 DieterMatthau     Russell

       $153 DivineSheen     Russell

       $154 FredericGrodin     Russell

       $155 FredericoRomero     Russell

5.INSERT語句：

INSERT INTO hr.jobs

  (job_id, job_title, min_salary, max_salary)

VALUES

     ('IT_PM', 'Project Manager', 5000, 11000);

1 row created.

SQL> commit;

Commit complete.

6.多表查詢：

SQL> select * from large_customers;

no rows selected

SQL> select * from medium_customers;

no rows selected

SQL> select * from small_customers;

no rows selected

INSERT ALL WHEN sum_orders < 10000 THEN INTO small_customers WHEN sum_orders >= 10000 AND sum_orders < 100000 THEN INTO medium_customers ELSE INTO large_customers

  SELECT customer_id, SUM(order_total) sum_orders

    FROM orders

   GROUP BY customer_id;