Oracle Database

If you want to practice SQL statements with SQL*Plus, you need to set up your SQL*Plus output formatting parameter properly. The following SQL*Plus commands shows you some examples:

COLUMN id FORMAT 9999;

COLUMN url FORMAT A24;

COLUMN notes FORMAT A12;

COLUMN counts FORMAT 999999;

SET NULL 'NULL';

To insert a new row into a table, you should use the INSERT INTO statement with values specified for all columns as shown in the following example:

INSERT INTO ggl_links VALUES (101,

'http://www.rendc.org',

NULL,

0,

'30-Jul-2008');

1 row created.

SELECT * FROM ggl_links;

 ID URL    NOTES  COUNTS CREATED

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

 101 http://www.rendc.org NULL   0 30-Jul-08

If a column is defined with a default value in a table, you can use the key word DEFAULT in the INSERT statement to take the default value for that column. The following tutorial exercise gives a good example:

INSERT INTO ggl_links VALUES (102,

'http://www.rendc.org',

NULL,

0,

DEFAULT);

1 row created.

SELECT * FROM ggl_links;

 ID URL    NOTES  COUNTS CREATED

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

 101 http://www.rendc.org NULL   0 30-Jul-08

 102 http://www.rendc.org NULL   0 07-MAY-06

If you want to update some values in one row or multiple rows in a table, you can use the UPDATE statement. The script below shows a good example:

UPDATE ggl_links SET counts = 999, notes = 'Good site.'

WHERE id = 101;

1 row updated.

SELECT * FROM ggl_links WHERE id = 101;

 ID URL    NOTES COUNTS CREATED

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

 101 http://www.rendc.org Good site. 999 07-MAY-06

Your 10g XE server comes with a sample database user account called HR. But this account is locked. You must unlock it before you can use it:

* Log into the server home page as SYSTEM.

* Click the Administration icon, and then click Database Users.

* Click the HR schema icon to display the user information for HR.

* Enter a new password (hr) for HR, and change the status to Unlocked.

* Click Alter User to save the changes.

Now user account HR is ready to use.

If a subquery is used in a UPDATE statement, it must return exactly one row for each row in the update table that matches the WHERE clause. If it returns multiple rows, Oracle server will give you an error message. To test this out, you can try the following tutorial exercise:

UPDATE ggl_links SET (notes, created) =

(SELECT last_name, hire_date FROM employees

WHERE employee_id < id)

WHERE id < 110;

ERROR at line 1:

ORA-01427: single-row subquery returns more than one row

The problem is the criteria in the subquery: "employee_id < id"

Your 10g XE server has a default setting for Program Global Area (PGA) of 40MB. The PGA size can be changed to a new value depending on how much data a single session should be allocated. If you think your session will be short with a small amount of data, you should change the PGA size to 16MB by:

* Log into the server home page as SYSTEM.

* Go to Administration, then Memory.

* Click Configure PGA.

* Enter the new memory size: 16

* Click Apply Changes to save the changes.

* Re-start your server.

If you want to delete an existing row from a table, you can use the DELETE statement with a WHERE clause to identify that row. Here is good sample of DELETE statements:

INSERT INTO ggl_links (url, id)

VALUES ('http://www.myspace.com', 301);

1 row created.

SELECT * FROM ggl_links WHERE id = 301;

 ID URL    NOTES  COUNTS CREATED

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

 301 http://www.myspace.com NULL  NULL 07-MAY-06 

DELETE FROM ggl_links WHERE id = 301;

1 row deleted.

SELECT * FROM ggl_links WHERE id = 301;

no rows selected

Let's you made a mistake and changed to SGA to 16MB from the SYSTEM admin home page. When you run the batch file StartDB.bat, it will return a message saying server stated. However, if you try to connect to your server home page: http://localhost:8080/apex/, you will get no response. Why? Your server is running, but the default instance XE was not started.

If you go the Control Panel and Services, you will see service OracleServiceXE is listed not in the running status.

You can delete multiple rows from a table in the same way as deleting a single row, except that the WHERE clause will match multiple rows. The tutorial exercise below deletes 3 rows from the ggl_links table:

SELECT * FROM ggl_links WHERE id >= 250;

 ID URL    NOTES  COUNTS CREATED

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

 1250 Retail Sales.com Wrong URL  500 07-MAY-06

 1260 Recruiting.com  Wrong URL  520 07-MAY-06

 1270 Payroll.com   Wrong URL  540 07-MAY-06

DELETE FROM ggl_links WHERE id >= 250;

3 row deleted.

SELECT * FROM ggl_links WHERE id >= 250;

no rows selected

If you want to delete all rows from a table, you have two options:

* Use the DELETE statement with no WHERE clause.

* Use the TRUNCATE TABLE statement.

The TRUNCATE statement is more efficient the DELETE statement. The tutorial exercise shows you a good example of TRUNCATE statement:

SELECT COUNT(*) FROM ggl_links;

COUNT(*)

----------

3

TRUNCATE TABLE ggl_links;

Table truncated.

SELECT COUNT(*) FROM ggl_links;

COUNT(*)

----------

0

The SELECT statement is also called the query statement. It is the most frequently used SQL statement in any database application. A SELECT statement allows you to retrieve data from one or more tables, or views, with different selection criteria, grouping criteria and sorting orders.

If you want explicitly tell the query to some columns, you can specify the column names in SELECT clause. The following select statement returns only two columns from the table "departments":

SQL> SELECT location_id, department_name FROM DEPARTMENTS;

LOCATION_ID DEPARTMENT_NAME

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

  1700 Administration

  1800 Marketing

  1700 Purchasing

  2400 Human Resources

  1500 Shipping

  1400 IT

  2700 Public Relations

  2500 Sales

  1700 Executive

......

If you don't want select all rows from a table, you can specify a WHERE clause to tell the query to return only the rows that meets the condition defined in the WHERE clause. The following select statement only returns rows that has department name starts with the letter "C":

SQL> SELECT * FROM departments

2 WHERE department_name LIKE 'C%';

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID

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

  130 Corporate Tax     1700

  140 Control And Credit    1700

  180 Construction     1700

  190 Contracting     1700

......