Basic Commands in Oracle

This tutorial provides an introduction to basic Oracle commands, including connecting to the database, running queries, and managing data.

1. Connecting to Oracle Database

To connect to Oracle Database using SQL*Plus, use the following command:

sqlplus username/password@hostname:port/service_name

For example:

sqlplus scott/tiger@localhost:1521/orclpdb1

This command connects you to the database as the user "scott" with the password "tiger". The database is located on the local machine at port 1521 and the service name is "orclpdb1".

2. Running Queries

Once connected, you can run SQL queries to interact with the database. Here are a few basic examples:

2.1. Selecting Data

To retrieve data from a table, use the SELECT statement:

SELECT * FROM employees;

This command retrieves all columns from the "employees" table.

2.2. Filtering Data

To filter the data, use the WHERE clause:

SELECT * FROM employees WHERE department_id = 10;

This command retrieves all columns from the "employees" table where the department ID is 10.

2.3. Sorting Data

To sort the data, use the ORDER BY clause:

SELECT * FROM employees ORDER BY last_name ASC;

This command retrieves all columns from the "employees" table and sorts the results by the "last_name" column in ascending order.

3. Managing Data

You can also manage data in the database using SQL commands. Here are a few basic examples:

3.1. Inserting Data

To insert a new row into a table, use the INSERT statement:

INSERT INTO employees (first_name, last_name, department_id, hire_date)
VALUES ('John', 'Doe', 10, TO_DATE('2024-01-01', 'YYYY-MM-DD'));

This command inserts a new row into the "employees" table with the specified values.

3.2. Updating Data

To update existing rows in a table, use the UPDATE statement:

UPDATE employees SET department_id = 20 WHERE employee_id = 123;

This command updates the "department_id" of the employee with ID 123 to 20.

3.3. Deleting Data

To delete rows from a table, use the DELETE statement:

DELETE FROM employees WHERE employee_id = 123;

This command deletes the row from the "employees" table where the "employee_id" is 123.

4. Managing Transactions

Oracle supports transactions, which allow you to group multiple operations into a single unit of work. Here are a few basic commands:

4.1. Starting a Transaction

A transaction starts implicitly when you execute the first SQL command. To explicitly start a transaction, you can use:

SET TRANSACTION;

4.2. Committing a Transaction

To save the changes made during the transaction, use the COMMIT statement:

COMMIT;

4.3. Rolling Back a Transaction

To undo the changes made during the transaction, use the ROLLBACK statement:

ROLLBACK;

5. Using PL/SQL

PL/SQL is Oracle's procedural extension to SQL. It allows you to write blocks of code that can include variables, control structures, and SQL statements. Here is a basic example:

DECLARE
    v_emp_name VARCHAR2(50);
BEGIN
    SELECT first_name INTO v_emp_name FROM employees WHERE employee_id = 123;
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp_name);
END;

This PL/SQL block declares a variable, retrieves the first name of the employee with ID 123, and prints it.

6. Conclusion

In this tutorial, you learned some basic commands for interacting with Oracle Database, including connecting to the database, running queries, managing data, handling transactions, and using PL/SQL. These commands form the foundation for working with Oracle, and you can build upon this knowledge to explore more advanced features and capabilities of Oracle Database.