plsqL复习

发布时间:2020-06-03 14:01:59 作者:llc018198
来源:网络 阅读:280
  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;


推荐阅读:
  1. Oracle体系复习
  2. SQL复习笔记

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

oracle

上一篇:怎么把VM vSAN群集中的持久日志记录到存储

下一篇:Percona Audit Log Plugin实现对mysql 审计的操作步骤

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》