Shulou(Shulou.com)06/01 Report--

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 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.