Managing data in Oracle 10g using SQL and SQL *Plus

Learning objective

After completing this topic, you should be able to identify ways of sending SQL commands to a database and recognize how to start SQL*Plus and iSQL*Plus.

Exercise overview

In this exercise, you're required to use SQL*Plus and iSQL*Plus.

This involves the following tasks:

• querying the database using SQL*Plus
• querying the database using iSQL*Plus

Task 1: Using SQL*Plus

You are an employee in your company's Human Resources department, and you need to work with data in the default database.

Step 1 of 1

Before you can use SQL*Plus, you need to start the iSQL*Plus server-side listening process.

Type and execute the command that does this.

Result

You type isqlplusctl start.

Step 2 of 4

You now want to access the SQL*Plus command-line interface without connecting to the database.

Type and execute the command that does this.

Result

You type sqlplus /nolog.

Step 3 of 4

You are ready to connect as the hr user

Type and execute the appropriate command, specifying the username hr and the password hr.

Result

You type connect hr/hr.

Step 4 of 4

Now that you have connected to the database, you are ready to begin working with it. You want to remove the table TEMP_EMPLOYEES, which is no longer used.

Type and execute the relevant SQL*Plus command.

Result

You type DROP TABLE temp_employees;.

Task 2: Using iSQL*Plus

You decide to use iSQL*Plus to perform further work on the database.

Step 1 of 5

Which of these best describes how you access iSQL*Plus, given that the iSQL*Plus URL is http://10.5.163.22:5560/isqlplus/, you are connecting to the default orcl2 database using iSQL*Plus, your username is hr, and your password is hr.

Options:

1. Type http://10.5.163.22:5560/isqlplus/ in the Address field, press Enter, type hr in the Password field, and click Login.
2. Type http://10.5.163.22:5560/isqlplus/ at the SQL*Plus command-line prompt, press Enter, type hr in the Password field, and click Login.
3. Type http://10.5.163.22:5560/isqlplus/ in the Address field, press Enter, and click Login, and type hr in the Password field.

Result

You type http://10.5.163.22:5560/isqlplus/ at the SQL*Plus command-line prompt, press Enter, type hr in the Password field, and click Login.

Step 2 of 5

You need to generate a description of the EMPLOYEES table.

Which of these statements best describes how you do this?

Options:

1. Type employees desc and click Execute.
2. Type DESC employees and click Execute.
3. Type employees and click Execute.
4. Type SELECT employees and click Execute.

Result

You type DESC employees and click Execute.

Step 3 of 5

You need to retrieve a list of employee names from the department with department ID 90. Department IDs are stored in the DEPARTMENT_ID column of the EMPLOYEES table.

Which of these statements best describes how you complete the SELECT statement to do this?

Options:

1. Type department_id=90 and click Execute.
2. Type ALTER department_id=90 and click Execute.
3. Type SELECT department_id=90 and click Execute.
4. Type WHERE department_id=90 and click Execute.

Result

You type type WHERE department_id=90 and click Execute.

Step 4 of 5

You need to update the salary information for a particular employee. In the EMPLOYEES table, salaries are stored in the SALARY column and employee IDs are stored in the EMPLOYEE_ID column.

Which of these statements best describes how you set the salary of the employee with employee ID 2500 to 36000?

Options:

1. Type ALTER employees SET salary=36000 WHERE employee_id=2500 and click Execute.
2. Type UPDATE employees SET salary=36000 WHERE employee_id=2500 and click Execute.
3. Type UPDATE employees WHERE salary=36000 WHERE employee_id=2500 and click Execute.
4. Type UPDATE employees SET salary 36000 WHERE employee_id 2500 and click Execute.

Result

You type UPDATE employees SET salary=36000 WHERE employee_id=2500 and click Execute.

Step 5 of 5

You realize that you have made a mistake in your update of the EMPLOYEES table, so you need to undo the change you have made.

What SQL*Plus command do you use to do this?

Options:

1. ALTER
2. DROP
3. ROLLBACK
4. UPDATE

Result

You type ROLLBACK and click Execute.