plsqL复习
下单超过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
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;
