Soluções dos exercícios sobre variáveis para SQL

1

edit query_03.sql

ACCEPT data1 CHAR PROMPT 'Data1='
ACCEPT data2 CHAR PROMPT 'Data2='
SELECT empno, ename, sal, deptno 
FROM emp 
WHERE hiredate BETWEEN &data1 AND &data2;

start query_03.sql

Data1=to_date('1981-06-12','yyyy-mm-dd')
Data2=to_date('1983-06-12','yyyy-mm-dd')
old   3: WHERE hiredate BETWEEN &data1 AND &data2
new   3: WHERE hiredate BETWEEN to_date('1981-06-12','yyyy-mm-dd') AND to_date('1983-06-12','yyyy-mm-dd')

     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7654 MARTIN           1250         30
      7788 SCOTT            3000         20
      7839 KING             5000         10
      7844 TURNER           1500         30
      7876 ADAMS            1100         20
      7900 JAMES             950         30
      7902 FORD             3000         20
      7934 MILLER           1300         10

8 rows selected.

2.

edit query_04.sql

SELECT empno, ename, sal, job
FROM emp 
WHERE job='&1';

start query_04.sql CLERK

old   3: WHERE job='&1'
new   3: WHERE job='CLERK'

     EMPNO ENAME             SAL JOB
---------- ---------- ---------- ---------
      7369 SMITH             800 CLERK
      7876 ADAMS            1100 CLERK
      7900 JAMES             950 CLERK
      7934 MILLER           1300 CLERK

3.

edit query_05.sql

ACCEPT total NUMBER PROMPT "Salario anual máximo="
DEFINE s = sal*14+NVL(comm,0);
SELECT ename, sal, &s 
FROM emp 
WHERE &s > &total;

start query_05.sql

Salario anual maximo=22000
old   1: SELECT ename, sal, &s
new   1: SELECT ename, sal, sal*14+NVL(comm,0)
old   3: WHERE &s > &total
new   3: WHERE sal*14+NVL(comm,0) >      22000

ENAME             SAL SAL*14+NVL(COMM,0)
---------- ---------- ------------------
ALLEN            1600              22700
JONES            2975              41650
BLAKE            2850              39900
CLARK            2450              34300
SCOTT            3000              42000
KING             5000              70000
FORD             3000              42000

7 rows selected.

Topo

Realizado por Turtle Learning ®. Última alteração em 2011-08-15