PlsqL review
Query statement for female customers who have placed orders more than 4 times:
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
$147F $2450 F
$147F $2425 F
$147F $2385 F
$147F $2366 F
$147F $2396 F
$148m $2451 M
$148m $2426 M
$148m $2386 M
$148m $2367 M
3.Group by statement:
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 list:
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 statement:
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. Multi-table query:
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
