Oracle Get Information

Main Page >> Oracle and SQL >> Workbook >> Getting Information from a table

Getting Information From A Table

In some cases you may wish to retrieve all information in a particular table, e.g., all the customer or employee details.

To list all customer details:

SELECT  * FROM CUSTOMER;

As seen previously, * is like a wildcard for retrieving all columns in a table. In some cases you may only be interested in certain rows and columns, image having to browse through a database with over 1,000,000 rows and 50 columns!! You would be swamped with information you probably did not need anyway. What is needed is a method of cutting down the information.

SQL allows you to restrict which fields and rows you want.

Restricting Columns

To get specific columns, in this case the numbers, names and department numbers of each employee, in place of the * type in the desired column names:

SELECT  EMPNO,  ENAME,  DEPTNO  FROM  EMP;

To list the customer number, name and city type:

SELECT  CNO,  NAME,  CITY  FROM  CUSTOMER;

Exercise 1.2

Now try and retrieve the employee's name, hiredate and sal:

The results should be similar to:

ENAME      HIREDATE         SAL
---------- --------- ----------
KING       17-NOV-81       5000
JONES      02-APR-81       2975
SCOTT      09-DEC-82       3000
ADAMS      12-JAN-83       1100
FORD       03-DEC-81       3000
SMITH      17-DEC-80        800
BLAKE      01-MAY-81       2850
ALLEN      20-FEB-81       1600
WARD       22-FEB-81       1250
MARTIN     28-SEP-81       1250
TURNER     08-SEP-81       1500
JAMES      03-DEC-81        950
CLARK      09-JUN-81       2450
MILLER     23-JAN-82       1300

14 rows selected.

Restricting Rows

The previous section showed how to restrict the columns required, now to cut down the rows. Quite often you may only be interested in say employees from a certain department, or students on a particular course, so the common value for a group of people can be use to restrict what rows are retrieved.

The previous format retrieved all the rows in the required fields. To get the same information just for managers we need to use the WHERE clause.

Type in:

SELECT  ENAME,  JOB,  DEPTNO  
FROM  EMP  
WHERE  JOB  =  'MANAGER';

Next list all the customers based in England:

SELECT  *  FROM  CUSTOMER 
WHERE  COUNTRY  =  'ENGLAND';

Exercise 1.3

Now retrieve the employees who are clerks.

ENAME      JOB           DEPTNO
---------- --------- ----------
ADAMS      CLERK             20
SMITH      CLERK             20
JAMES      CLERK             30
MILLER     CLERK             10

Note, although commands can be typed in either upper or lower case, the data stored in the database is case sensitive.

Matching the case exactly, type in the following:

select  ename,  job,  deptno  from  emp  
where  job  =  'manager';

⇒ Why are no records retrieved?