MySilpir.Net

실습7

4번. 쿼리문

SET ECHO OFF
SET VERIFY OFF
ACCEPT low_date_range PROMPT 'Enter the low date range (DD-MON-YY) : '
ACCEPT high_date_range PROMPT 'Enter the high date range (DD-MON-YY) : '

select userid "USERID", first_name ||' '|| last_name "EMPLOYEE", start_date "START_DAT" from s_emp where start_date between '&low_date_range' and '&high_date_range'
/

5번. 쿼리문

SET ECHO OFF
SET VERIFY OFF

ACCEPT c_name PROMPT "Please enter the customer's name : "

select id "ID", name "CUSTOMER NAME" from s_customer where lower(name) like lower('%&c_name%')
/

6번. 쿼리문

SET ECHO OFF
SET PAGES 25
SET LINES 80
SET FEEDBACK OFF
SET VERIFY OFF

ACCEPT c_name PROMPT "Please enter a customer's name : "

COL "c_title" HEA "Sales rep for customer's whose name contains '&c_name'" FORMAT A52
COL "ORDER_ID" FORMAT 999
COL "CUSTOMER" FORMAT A20
COL "PRODUCT" FORMAT A25

BREAK ON "ORDER_ID" SKIP 1 ON REPORT ON "CUSTOMER"

select e.first_name||' '||e.last_name||'is the rep to '||c.name "c_title" from s_emp e, s_customer c where e.id=c.sales_rep_id and lower(c.name) like lower('%&c_name%')
/

select o.id "ORDER_ID", c.name "CUSTOMER", p.name "PRODUCT" from s_ord o, s_customer c, s_product p, s_item i where o.customer_id = c.id and o.id = i.ord_id and p.id = i.product_id and lower(c.name) like lower('%&c_name%')
/


7번. 쿼리문

SET ECHO OFF
SET PAGES 25
SET LINES 80
SET FEEDBACK OFF
SET VERIFY OFF

COL EMPLOYEE FORMAT A15
COL CUSTOMER FORMAT A30
COL SALES FORMAT $9,999,999

BREAK ON "EMPLOYEE" SKIP 1 ON REPORT
COMPUTE SUM LABEL 'sum' OF "SALES" ON REPORT

select e.first_name ||' '|| e.last_name "EMPLOYEE", c.name "CUSTOMER", sum(o.total) "SALES"
from s_emp e, s_customer c, s_ord o
where e.id = c.sales_rep_id and o.customer_id = c.id and c.region_id = &1
group by e.first_name ||' '|| e.last_name, c.name
order by c.name
/