Oracle Database

If StartBD.bat failed to start the XE instance, you need to try to start the instance with other approaches to get detail error messages on why the instance can not be started.

One good approach to start the default instance is to use SQL*Plus. Here is how to use SQL*Plus to start the default instance in a command window:

>cd (OracleXE home directory)

>.instartdb

>.insqlplus

Enter user-name: SYSTEM

Enter password: globalguideline

ERROR:

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

The first "cd" is to move the current directory the 10g XE home directory. The second command ".instartdb" is to make sure the TNS listener is running. The third command ".insqlplus" launches SQL*Plus. The error message "ORA-27101" tells you that there is a memory problem with the default instance.

So you can not use the normal login process to the server without a good instance. See other tips on how to log into a server without any instance.

You can specifying multiple columns in the ORDER BY clause as shown in the following example statement, which returns employees' salaries sorted by department and salary value:

SQL> SELECT department_id, first_name, last_name, salary

FROM employees ORDER BY department_id, salary;

DEPARTMENT_ID FIRST_NAME LAST_NAME   SALARY

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

   10 Jennifer  Whalen   4400

   20 Pat   Fay    6000

   20 Michael  Hartstein  13000

   30 Karen   Colmenares  2500

   30 Guy   Himuro   2600

   30 Sigal   Tobias   2800

   30 Shelli  Baida    2900

   30 Alexander  Khoo   3100

   30 Den   Raphaely   11000

   40 Susan   Mavris   6500

   50 TJ   Olson    2100

......

If you want to sort a column in descending order, you can specify the DESC keyword in the ORDER BY clause. The following SELECT statement first sorts the department in descending order, then sorts the salary in ascending order:

SQL> SELECT department_id, first_name, last_name, salary

FROM employees ORDER BY department_id DESC, salary;

DEPARTMENT_ID FIRST_NAME LAST_NAME   SALARY

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

   Kimberely  Grant    7000

  110 William  Gietz    8300

  110 Shelley  Higgins   12000

  100 Luis  Popp   6900

  100 Ismael  Sciarra   7700

  100 Jose Manuel  Urman    7800

  100 John  Chen   8200

  100 Daniel  Faviet   9000

......

If there are duplications in the returning rows, and you want to remove the duplications, you can use the keyword DISTINCT or UNIQUE in the SELECT clause. The tutorial exercise below shows you that DISTINCT works on selected columns only:

SQL> CREATE TABLE ggl_team AS

SELECT first_name, last_name FROM employees

WHERE first_name = 'John';

Table created.

SQL> INSERT INTO ggl_team VALUES ('John', 'Chen');

SQL> INSERT INTO ggl_team VALUES ('James', 'Chen');

SQL> INSERT INTO ggl_team VALUES ('Peter', 'Chen');

SQL> INSERT INTO ggl_team VALUES ('John', 'Chen');

SQL> SELECT * FROM ggl_team;

FIRST_NAME   LAST_NAME

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

John    Chen

John    Russell

John    Seo

John    Chen

James   Chen

Peter   Chen

John    Chen

SQL> SELECT DISTINCT * FROM ggl_team;

FIRST_NAME   LAST_NAME

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

Peter   Chen

John    Chen

James   Chen

John    Seo

John    Russell

You can divide query output into multiple groups with the GROUP BY clause. It allows you specify a column as the grouping criteria, so that rows with the same value in the column will be considered as a single group. When the GROUP BY clause is specified, the select statement can only be used to return group level information. The following script gives you a good GROUP BY example:

SQL> SELECT department_id, MIN(salary), MAX(salary),

2 AVG(salary) FROM employees GROUP BY department_id;

DEPARTMENT_ID MIN(SALARY) MAX(SALARY) AVG(SALARY)

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

  100  6900  12000  8600

   30  2500  11000  4150

    7000  7000  7000

   90  17000  24000 19333.3333

   20  6000  13000  9500

   70  10000  10000  10000

  110  8300  12000  10150

   50  2100  8200 3475.55556

......

If the query output is aggregated as groups, you can sort the groups by using group functions in the ORDER BY clause. The following statement returns how many employees are having the same salary in each department. The group output is sorted by the count in each group in descending order:

SQL> SELECT department_id, salary, count(*)

2 FROM employees GROUP BY department_id,

3 salary HAVING count(*) > 1

ORDER BY COUNT(*) DESC;

DEPARTMENT_ID  SALARY COUNT(*)

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

   50  2500  5

   50  3200  4

   50  2800  3

   80 10000  3

   80  9500  3

   50  3100  3

   50  2600  3

.....

If you want to query from two tables with an inner join, you can use the INNER JOIN ... ON clause in the FROM clause. The following query returns output with an inner join from two tables: employees and departments. The join condition is that the department ID in the employees table equals to the department ID in the departments table:

SQL> SELECT employees.first_name, employees.last_name,

2 departments.department_name

3 FROM employees INNER JOIN departments

4 ON employees.department_id=departments.department_id;

FIRST_NAME   LAST_NAME  DEPARTMENT_NAME

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

Steven   King    Executive

Neena   Kochhar   Executive

Lex   De Haan   Executive

Alexander  Hunold   IT

Bruce   Ernst   IT

David   Austin   IT

Valli   Pataballa  IT

......

Note that when multiple tables are used in a query, column names need to be prefixed with table names in case the same column name is used in both tables

If you want to query from two tables with a left outer join, you can use the LEFT OUTER JOIN ... ON clause in the FROM clause. The following query returns output with a left outer join from two tables: departments and employees. The join condition is that the manager ID in the departments table equals to the employee ID in the employees table:

SQL> set NULL 'NULL'

SQL> SELECT d.department_name, e.first_name, e.last_name

2 FROM departments d LEFT OUTER JOIN employees e

3 ON d.manager_id = e.employee_id;

DEPARTMENT_NAME   FIRST_NAME   LAST_NAME

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

Administration  Jennifer   Whalen

Marketing    Michael   Hartstein

Purchasing   Den   Raphaely

Human Resources   Susan   Mavris

Shipping   Adam    Fripp

IT    Alexander  Hunold

......

Treasury   NULL    NULL

Corporate Tax   NULL    NULL

Control And Credit  NULL    NULL

Shareholder Services NULL    NULL

Benefits   NULL    NULL

Manufacturing   NULL    NULL

Construction   NULL    NULL