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;

网站栏目：plsqL复习
标题网址：https://www.cdcxhl.com/article24/ijegce.html

成都网站建设公司_创新互联，为您提供ChatGPT、网站营销、面包屑导航、标签优化、定制开发、网页设计公司

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联