Getting Started with Oracle SQL*Plus

Getting Started with Oracle SQL*Plus

SQL*Plus is one of the most frequently used Oracle client application by DBA’s and application programmers. It is available with every Oracle installation and supports SQL statements and other Oracle specific commands. Unless otherwise described, command and SQL statements are not case-sensitive.

1. To start SQL*Plus, select

Start | Programs | Oracle - orahome | Application Development | SQL*Plus

1. Type in scott and tiger as User Name and Password, respectively. Type in oradb in the Host String field and click OK.

1. Once you logged in, type in the following simple SQL statement to have a look at tables that user scott can access.
   select tname from tab;
   where tab is the name of a view object through which user can retrieve a list of accessible tables, and tname stands for table name.
   [Note: Don’t forget to use a semicolon in your query; otherwise SQL*Plus will not execute it. But do NOT include a semicolon at the end of a SQL statement in your (Java) application code.]

1. You may want to check on the contents in each of the few tables displayed as the result from last step. But it is a good idea to get a description on the structure (or schema) of a target table as a first step. You can use the follow Oracle command
   desc emp (or describe emp)
   With information (metadata) on field names, nullability, and data types, you may then write queries to retrieve user data that may help answering your questions.

1. As a simplest example, try the following query that will display all contents in the department table (dept)
   select * from dept;
   The asterisk matches all fields in a record. To project results on a subset of the fields, you have to specify them, as shown in the next example.

1. Assume that you are interested in employee information, including id (EMPNO),name (ENAME), position (JOB), starting date (HIREDATE), salary (SAL), and commission rate (COMM), you can use the following SQL SELECT statement
   select empno, ename, job, hiredate, sal, comm
   from emp;
   Notice that it’s not case sensitive and it doesn’t make any different to SQL*Plus whether you put the query in one or more lines.

1. Results from last example include 14 records, but they are displayed on two pages. To avoid seeing your result being separated by page headers, use the following command to reset the page size to, say, 50. And then try the previous query again.
   set page 50

1. By default, the results are not sorted in any order. You may specify the way in which you want the result to be sorted by using an ORDER BY clause.
   select empno, ename, job, hiredate, sal, comm
   from emp
   order by sal desc;
   More than one field may be used in the ORDER BY clause, each sort the records in a different order.

select empno, ename, job, hiredate, sal, comm
from emp
order by sal, comm desc;

1. Here is an example of how to find information for employees who meet a certain criterion, such as who is the president of the company.

select ename, hiredate, sal
from emp
where job = ‘PRESIDENT’;

Notice that, the string value PRESIDENT is case sensitive and needs to be enclosed with single quote.

Here is another example, display those clerks who are hired by the president, if you know he joined the company on 17-NOV-81, including the department they work in.
select ename, hiredate, deptno
from emp
where job = ‘CLERK’
and hiredate > ‘17-NOV-81’;
If you don’t know exactly when the president joined the company (that date is in the emp table any way), then the query becomes a little lengthy.
select ename, hiredate, deptno
from emp
where job = ‘CLERK’
and hiredate > (select hiredate

from emp

where job = ‘PRESIDENT’);
The embedded query, usually referred to as a subquery, is used to retrieve the piece of information needed to express the second criterion in the main query.

1. From the last query, we knowwhich department each clerk works in; that is, the department number. What about the name of the department, and where is it located? Detailed info on departments may be found in the dept table. Then you need to join the emp and dept tables to answer this question.

select ename, dname, loc
from emp, dept
where job = ‘CLERK’
and emp.deptno = dept.deptno;
The deptno field in the emp table is the foreign key that links an employee record to the corresponding department record in the dept table. Try the same query without the last condition and explain to yourself what happened.

1. To simplify the last query, you may use a short alias for each of the two tables. It is especially helpful if the tables were named employee and department.

select ename, dname, loc
from emp e, dept d
where job = ‘CLERK’
and e.deptno = d.deptno;
Aliases are absolutely needed in the case that follows: for each employee, list the employee name and the name of his/her manager. The manager of each employeeis stored in the manager (mgr) field. To answer this question, the emp table needs to be used twice, once for employee, and once for manager. Here is the query that will work

select e.ename, m.ename
from emp e, emp m
where e.mgr = m.empno;
Check the result and convince yourself this is the correct answer. However, the column header doesn’t tell which name is for the manager, which is for the employee. Aliases may also be used to make your answer more readable. Try this one:

select e.ename as employee, m.enamemanager
from emp e, emp m
where e.mgr = m.empno;
You may have noticed that the keyword as is optional. As used above, the column names are displayed as ALL-CAP’s. If you care about the spelling, use DOUBLE quote as shown below.

select e.ename “Employee”, m.ename“Manager”
from emp e, emp m
where e.mgr = m.empno;
As an exercise, add one more criterion to narrow the list to those who report directly to the president, whose empno is 7839. Are there any other ways to write queries asking for the same information?

1. Try it yourself! With what you’ve learned till now, you can write very interesting queries. Try this one: display the name, job, and salary for all employees, together with the salary grade codes (1 through 5) and the corresponding salary ranges. Your result may look like this:

ENAME JOB SAL GRADE LOSAL HISAL

------

SMITH CLERK 800 1 700 1200

ADAMS CLERK 1100 1 700 1200

JAMES CLERK 950 1 700 1200

WARD SALESMAN 1250 2 1201 1400

MARTIN SALESMAN 1250 2 1201 1400

MILLER CLERK 1300 2 1201 1400

ALLEN SALESMAN 1600 3 1401 2000

TURNER SALESMAN 1500 3 1401 2000

JONES MANAGER 2975 4 2001 3000

BLAKE MANAGER 2850 4 2001 3000

CLARK MANAGER 2450 4 2001 3000

SCOTT ANALYST 3000 4 2001 3000

FORD ANALYST 3000 4 2001 3000

KING PRESIDENT 5000 5 3001 9999

14 rows selected.