Tài liệu Giáo trình SQL và PL SQL part 3 pdf

C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT

Gi¸o tr×nh SQL vµ PL/SQL

§µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 19

3.4 Bµi tËp

1. Chän nh©n viªn trong b¶ng EMP cã møc l−¬ng tõ 1000 ®Õn 2000 (chän c¸c tr−êng ENAME, DEPTNO,

SAL).

ENAME DEPTNO SAL

---------- ---------- ----------

ALLEN 30 1600

WARD 30 1250

MARTIN 30 1250

TURNER 30 1500

ADAMS 20 1100

MILLER 10 1300

Sal Between 1000 to 2000

2.HiÓn thÞ m· phßng ban, tªn phßng ban, s¾p xÕp theo thø tù tªn phßng ban.

DEPTNO DNAME

------ -----------

10 ACCOUNTING

40 OPERATIONS

20 RESEARCH

30 SALES

Order by dname

3. HiÓn thÞ danh s¸ch nh÷ng nh©n viªn lµm t¹i phßng 10 vµ 20 theo thø tù A,B,C

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ----------------- ----- ---------- ---------- ----- -----

7876 ADAMS CLERK 7788 12-01-1983 1100 20

7782 CLARK MANAGER 7839 09-06-1981 2450 10

7902 FORD ANALYST 7566 03-12-1981 3000 20

7566 JONES MANAGER 7839 02-04-1981 2975 20

7839 KING PRESIDENT 17-11-1981 5000 10

7934 MILLER CLERK 7782 23-01-1982 1300 10

7788 SCOTT ANALYST 7566 09-12-1982 3000 20

7369 SMITH CLERK 7902 17-12-1980 800 20

Where deptno in (10,20) order by ename asc

4. HiÓn thÞ tªn vµ nghÒ nghiÖp nh÷ng nh©n viªn lµm nghÒ th− ký (clerk) t¹i phßng 20.

ENAME JOB

---------- ---------

SMITH CLERK

ADAMS CLERK

Where upper(job)= upper('clerk') and deptno = '20'; (l−u ý vÊn ®Ò ch÷ Hoa-th−êng)

5. HiÓn thÞ tÊt c¶ nh÷ng nh©n viªn mµ tªn cã c¸c ký tù TH vµ LL.

ENAME

----------

SMITH

ALLEN

MILLER

Where ename like “%TH%“ or ename like “%LL%“

6. HiÓn thÞ tªn nh©n viªn, nghÒ nghiÖp, l−¬ng cña nh÷ng nh©n viªn cã gi¸m ®èc qu¶n lý.

ENAME JOB SAL

---------- --------- ----------

SMITH CLERK 800

ALLEN SALESMAN 1600

WARD SALESMAN 1250

JONES MANAGER 2975