DCL (DATA CONTROL LANGUAGE)
CREATING A USER
SQL>CONNECT SYSTEM/MANAGER;
SQL>CREATE USER "USERNAME" IDENTIFIED BY "PASSWORD"
SQL>GRANT DBA TO "USERNAME"
SQL>CONNECT "USERNAME"/"PASSWORD";
EXAMPLE
CREATING A USER
SQL>CONNECT SYSTEM/MANAGER;
SQL>CREATE USER CSE2 IDENTIFIED BY CSECSE;
SQL>GRANT DBA TO CSE2;
SQL>CONNECT CSE2/CSECSE;
SQL>REVOKE DBA FROM CSE2;
DRL-DATA RETRIEVAL IMPLEMENTING ON SELECT COMMANDS
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 2000
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 3000
7521 WARD SALESMAN 7698 22-FEB-81 1250 5000
7566 JONES MANAGER 7839 02-APR-81 2975 2000
4 rows selected.
SQL> select empno,ename,sal from emp;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
SQL>select ename,job,sal,deptno from emp where sal not between 1500 and 5000;
ENAME JOB SAL DEPTNO
---------- --------- ---------- ----------
SMITH CLERK 800 20
WARD SALESMAN 1250 30
MARTIN SALESMAN 1250 30
ADAMS CLERK 1100 20
JAMES CLERK 950 30
MILLER CLERK 1300 10
6 rows selected.
SQL> select empno,ename,sal from emp where sal in (800,5000);
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7839 KING 5000
SQL> select empno,ename,sal from emp where comm is null;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
10 rows selected.
SQL> select empno,ename,sal from emp where comm is not null;
EMPNO ENAME SAL
---------- ---------- ----------
7499 ALLEN 1600
7521 WARD 1250
7654 MARTIN 1250
7844 TURNER 1500
SQL> select empno,ename,job,sal from emp where ename like'S%';
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7788 SCOTT ANALYST 3000
SQL> select empno,ename,job,sal from emp where job not like'S%';
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7788 SCOTT ANALYST 3000
SQL> select ename,job,sal from emp where sal>2500;
ENAME JOB SAL
---------- --------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
SCOTT ANALYST 3000
KING PRESIDENT 5000
FORD ANALYST 3000
SQL> select ename,job,sal from emp where sal<2500;
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
ALLEN SALESMAN 1600
WARD SALESMAN 1250
MARTIN SALESMAN 1250
CLARK MANAGER 2450
TURNER SALESMAN 1500
ADAMS CLERK 1100
JAMES CLERK 950
MILLER CLERK 1300
9 rows selected.
SQL> select empno,ename,job,sal from emp order by sal;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7900 JAMES CLERK 950
7876 ADAMS CLERK 1100
7521 WARD SALESMAN 1250
7654 MARTIN SALESMAN 1250
7934 MILLER CLERK 1300
7844 TURNER SALESMAN 1500
7499 ALLEN SALESMAN 1600
7782 CLARK MANAGER 2450
7698 BLAKE MANAGER 2850
7566 JONES MANAGER 2975
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7788 SCOTT ANALYST 3000
7902 FORD ANALYST 3000
7839 KING PRESIDENT 5000
14 rows selected.
SQL> select empno,ename,job,sal from emp order by sal desc;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7839 KING PRESIDENT 5000
7788 SCOTT ANALYST 3000
7902 FORD ANALYST 3000
7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500
7934 MILLER CLERK 1300
7521 WARD SALESMAN 1250
7654 MARTIN SALESMAN 1250
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7369 SMITH CLERK 800
14 rows selected.
Subscribe by Email
Follow Updates Articles from This Blog via Email
No Comments