Oracle Database

Posted byshindeamarPosted inDatabase
  • An Oracle database is a collection of data treated as a unit.
  • The purpose of a database is to store and retrieve related information. Oracle is a relational database which stores related data together in tables.
  • A database server is the key to solving the problems of information management. In general, a server reliably manages a large amount of data in a multiuser environment so that many users can concurrently access the same data. All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.
  • SQL (Structured Query Language) and PL/SQL (Procedural Language extension to SQL) form the core of Oracle’s application development stack. Not only do most enterprise back-ends run SQL, but Web applications accessing databases do so using SQL (wrapped by Java classes as JDBC), Enterprise Application Integration applications generate XML from SQL queries, and content-repositories are built on top of SQL tables.
  • PL/SQL is generally used by database developers to write procedural extensions (sequence of SQL statements combined into a single procedural block) such as Stored Procedures, functions and triggers.

SQL

SQL (pronounced SEQUEL) is the programming language that defines and manipulates the database.

Overview of Schemas and Common Schema Objects

  • A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user. 
  • Schema objects are the logical structures that directly refer to the database’s data. Schema objects include structures like tables, views, and indexes. (There is no relationship between a tablespace and a schema.
  • Objects in the same schema can be in different tablespaces, and a tablespace can hold objects from different schemas.)
  • Some of the most common schema objects are defined in the following section.
    • Tables:
      1. Tables are the basic unit of data storage in an Oracle database. Database tables hold all user-accessible data.
      2. Each table has columns and rows. A table that has an employee database, for example, can have a column called employee number, and each row in that column is an employee’s number.
    • Indexes:
      1. Indexes are optional structures associated with tables. Indexes can be created to increase the performance of data retrieval. Just as the index in this manual helps you quickly locate specific information, an Oracle index provides an access path to table data.
      2. When processing a request, Oracle can use some or all of the available indexes to locate the requested rows efficiently. Indexes are useful when applications frequently query a table for a range of rows (for example, all employees with a salary greater than 1000 dollars) or a specific row.
      3. Indexes are created on one or more columns of a table. After it is created, an index is automatically maintained and used by Oracle. Changes to table data (such as adding new rows, updating rows, or deleting rows) are automatically incorporated into all relevant indexes with complete transparency to the users.
    • Views:
      1. Views are customized presentations of data in one or more tables or other views.
      2. A view can also be considered a stored query. 
      3. Views do not actually contain data. Rather, they derive their data from the tables on which they are based, referred to as the base tables of the views.
      4. Like tables, views can be queried, updated, inserted into, and deleted from, with some restrictions. All operations performed on a view actually affect the base tables of the view.
      5. Views provide an additional level of table security by restricting access to a predetermined set of rows and columns of a table. They also hide data complexity and store complex queries.

SQL Statements

All operations on the information in an Oracle database are performed using SQL statements. A SQL statement is a string of SQL text. A statement must be the equivalent of a complete SQL sentence, as in:

Table: Employee

Emp_idFirst_nameLast_nameDept_idSalary
101JohnMelone110000
102SarahWinston215000
103MelissaBaker17800
104BrianBrown19000

SELECT last_name, dept_id FROM employee;

The above query will return a result set as below.

Last_nameDept_id
Melone1
Winston2
Baker1
Brown1

Please note, the column names or table names are NOT case sensitive, the data returned is. So if you want to display all upper case in Last_name, you need to use UPPER(last_name) in the query or LOWER(last_name) for all lower case.

Valid SQL syntax:

SELECT column_name1, column_name2
FROM table_name
WHERE column_name1 operator value GROUP BY column_name1 ORDER BY column_name1 asc | desc;    
  • FROM clause – always required, to fetch a data from a particular table
  • WHERE clause – optional – to be used only when you want to filter the data based on some pre-conditions
  • GROUP BY clause – optional – only used when you want to select the data for a particular group (let’s say in our example, all the employees which belong to Dept_id = 1)
  • ORDER BY clause – optional – used only when you want to sort the results returned by the query based on a particular column_name. You get an option of sorting in Ascending (using asc) order or Descending (using desc) order. By default ORDER BY is used sorts the result set in Ascending order.

SQL statements are categorized into following three main categories:

Data Definition Language (DDL) Statements

These statements create, alter, maintain, and drop schema objects, mostly used by the developers or DBAs. DDL statements also include statements that permit a user to grant other users the privileges to access the database and specific objects within the database. These statements are auto-committed to database, this means, on executing these statements, the objects would get created (CREATE) or altered (ALTER) or dropped (DROP) OR table data will be truncated and could not be reverted. Examples include CREATE, ALTER, TRUNCATE and DROP.

Data Manipulation Language (DML) Statements

These statements manipulate data. For example, querying, inserting, updating, and deleting rows of a table are all DML operations. The most common SQL statement is the SELECT statement, which retrieves data from the database. Locking a table or view and examining the execution plan of a SQL statement are also DML operations. After executing these statements you generally need to COMMIT (Saves the data) or ROLLBACK (restores the data to the point as it was before the execution of the previous statements) the transactions to save the data in the database. Examples include SELECT, UPDATE, and INSERT.

Transaction Control Statements

These statements manage the changes made by DML statements. They enable a user to group changes into logical transactions. Examples include COMMIT, ROLLBACK, and SAVEPOINT.

Test engineers would be using only DML and Transaction Control statements.

SQL Statements examples:

Operators in The WHERE Clause

The following operators can be used in the WHERE clause:

OperatorDescription
=Equal
<> Not equal. Note: In some versions of SQL this operator may be written as !=
> Greater than
< Less than
>=Greater than or equal
<=Less than or equal
BETWEENBetween an inclusive range
LIKESearch for a pattern
INTo specify multiple possible values for a column

Table: Employee

Emp_idFirst_nameLast_nameDept_idSalary
101JohnMelone110000
102SarahWinston215000
103MelissaBaker17800
104BrianBrown19000

SELECT * FROM employee WHERE emp_id = 101; –Numeric column referred directly

The above query will return a result set as below.

Emp_idFirst_nameLast_nameDept_idSalary
101JohnMelone110000

SELECT * FROM employee WHERE emp_id > 102; –Numeric column referred directly

The above query will return a result set as below.

Emp_idFirst_nameLast_nameDept_idSalary
103MelissaBaker17800
104BrianBrown19000

SELECT * FROM employee WHERE UPPER(last_name) LIKE ‘%BAKER’;

–CHAR/ VARCHAR column referred in single quotes, % is used as a wild card for pattern matching

The above query will return a result set as below.

Emp_idFirst_nameLast_nameDept_idSalary
103MelissaBaker17800

SELECT * FROM employee WHERE Salary between 8000 AND 9000;

–BETWEEN used for a range both inclusive

The above query will return a result set as below.

Emp_idFirst_nameLast_nameDept_idSalary
104BrianBrown19000

SELECT * FROM employee WHERE emp_id IN (103, 104);

–IN is used for multiple selection

The above query will return a result set as below.

Emp_idFirst_nameLast_nameDept_idSalary
103MelissaBaker17800
104BrianBrown19000

Logical Operators AND, OR and NOT (NOT keyword or ! sign or <> sign)

SELECT * FROM employee
WHERE emp_id = 101
AND first_name = ‘John’;

The above query will return a result set as below.

Emp_idFirst_nameLast_nameDept_idSalary
101JohnMelone110000

SELECT * FROM employee
WHERE emp_id = 103
OR first_name = ‘Brian;

The above query will return a result set as below.

Emp_idFirst_nameLast_nameDept_idSalary
103MelissaBaker17800
104BrianBrown19000

SELECT * FROM employee
WHERE emp_id = 103
AND first_name <> ‘Brian; –Use of NOT (<>)

The above query will return a result set as below.

Emp_idFirst_nameLast_nameDept_idSalary
103MelissaBaker17800

SELECT * FROM employee
WHERE emp_id NOT IN (101, 102, 104); –Use of NOT keyword

The above query will return a result set as below.

Emp_idFirst_nameLast_nameDept_idSalary
103MelissaBaker17800

These operators can also be combined in a single SQL query where required.

SELECT * FROM employee
WHERE (emp_id = 103 AND first_name = ‘Melissa’) OR dept_id = 2;

The above query will return a result set as below.

Emp_idFirst_nameLast_nameDept_idSalary
102SarahWinston215000
103MelissaBaker17800

ORDER BY (Sorting)

SELECT * FROM employee
ORDER BY dept_id;

The above query will return a result set as below.

Emp_idFirst_nameLast_nameDept_idSalary
101JohnMelone110000
103MelissaBaker17800
104BrianBrown19000
102SarahWinston215000

SELECT * FROM employee
ORDER BY dept_id ASC, Salary DESC;

The sorting takes place in the order of the columns, here first it will sort by DEPT_ID ASC and then Salary Descending order. The above query will return a result set as below.

Emp_idFirst_nameLast_nameDept_idSalary
101JohnMelone110000
104BrianBrown19000
103MelissaBaker17800
102SarahWinston215000

Insert Statements (To insert a new row of the data into the table)

Syntax to add a row with having data for all columns:

INSERT INTO table_name
VALUES (value1, value2, value3,…);

Syntax to add a row with having data for a few specific columns:

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

INSERT INTO employee
VALUES( 105, ‘Ron’, ‘Wild’, 2, 12000);

COMMIT;

Then execute below select query.

SELECT * from employee;

The above query will return a result set as below.

Emp_idFirst_nameLast_nameDept_idSalary
101JohnMelone110000
102SarahWinston215000
103MelissaBaker17800
104BrianBrown19000
105RonWild212000

INSERT INTO employee (Emp_id, First_name, Last_name, Dept_id)
VALUES( 106, ‘Jonathan’, ‘Reese’, 1);

COMMIT;

Then execute below select query.

SELECT * from employee;

The above query will return a result set as below.

Emp_idFirst_nameLast_nameDept_idSalary
101JohnMelone110000
102SarahWinston215000
103MelissaBaker17800
104BrianBrown19000
105RonWild212000
106JonathanReese1NULL

Update Statements (To modify the data for an existing record in the table)

Syntax to update all the records together:

UPDATE table_name
SET column1 = value1, column2 = value2, …;

Syntax to update only a few columns for a few records based on WHERE clause:

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE column3 = some_value;

UPDATE employee SET last_name = ‘Schmidt’
WHERE last_name = ‘Wild’;

COMMIT;

Then execute below select query.

SELECT * from employee WHERE emp_id = 106;

The above query will return a result set as below.

Emp_idFirst_nameLast_nameDept_idSalary
106RonSchmidt1NULL

Delete Statements ( To delete a row from the table):

DELETE FROM table_name
WHERE some_column=some_value;

DELETE FROM employee
WHERE emp_id = 106;

COMMIT;

Notice the WHERE clause in the SQL DELETE statement!
The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!

Then execute below select query.

SELECT * from employee WHERE emp_id = 106;

The above query will return a result set as below.

No record found

SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

Useful aggregate functions:

  • AVG() – Returns the average value
  • COUNT() – Returns the number of rows
  • FIRST() – Returns the first value
  • LAST() – Returns the last value
  • MAX() – Returns the largest value
  • MIN() – Returns the smallest value
  • SUM() – Returns the sum

Table: Employee

Emp_idFirst_nameLast_nameDept_idSalary
101JohnMelone110000
102SarahWinston215000
103MelissaBaker17800
104BrianBrown19000

Table: Department

Dept_idDept_name
1IT
2Networking
3Sales
4Marketing

SELECT COUNT(*) FROM employee;

The above query will return a result set as below.

Result
4

SELECT MIN(SALARY) FROM employee;

The above query will return a result set as below.

Result
7800

SELECT MAX(DEPT_ID) FROM employee;

The above query will return a result set as below.

Result
2

SQL JOINS

There are two types of JOINS – Inner JOIN and Outer Join.

Inner JOIN Examples:

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

Table: Employee

Emp_idFirst_nameLast_nameDept_idSalary
101JohnMelone110000
102SarahWinston215000
103MelissaBaker17800
104BrianBrown19000
105RoxySmith55000

Table: Department

Dept_idDept_name
1IT
2Networking
3Sales
4Marketing

SELECT employee.emp_id, employee.first_name, employee.last_name, department.dept_name
FROM employee
INNER JOIN department ON employee.dept_id = department.dept_id;

The above query will return a result set as below.

Emp_idFirst_nameLast_nameDept_name
101JohnMeloneIT
102SarahWinstonNetworking
103MelissaBakerIT
104BrianBrownIT

Outer JOIN examples:

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

In some databases LEFT JOIN is called LEFT OUTER JOIN.

SELECT employee.emp_id, employee.first_name, employee.last_name, department.dept_name
FROM employee
LEFT OUTER JOIN department ON employee.dept_id = department.dept_id;

The above query will return a result set as below.

Emp_idFirst_nameLast_nameDept_name
101JohnMeloneIT
102SarahWinstonNetworking
103MelissaBakerIT
104BrianBrownIT
105RoxySmithNULL

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

SELECT employee.emp_id, employee.first_name, employee.last_name, department.dept_name
FROM employee
RIGHT OUTER JOIN department ON employee.dept_id = department.dept_id;

The above query will return a result set as below.

Emp_idFirst_nameLast_nameDept_name
101JohnMeloneIT
102SarahWinstonNetworking
103MelissaBakerIT
104BrianBrownIT
NULLNULLNULLSales
NULLNULLNULLMarketing

Leave a comment

Design a site like this with WordPress.com
Get started