Oracle Learning - 5

SQL: Joins

A join is used to combine rows from multiple tables. A join is performed whenever two or more tables is listed in the FROM clause of an SQL statement.
There are different kinds of joins. Let's take a look at a few examples.

Inner Join (simple join)
Chances are, you've already written an SQL statement that uses an inner join. It is is the most common type of join. Inner joins return all rows from multiple tables where the join condition is met.
For example,
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;
This SQL statement would return all rows from the suppliers and orders tables where there is a matching supplier_id value in both the suppliers and orders tables.

Let's look at some data to explain how inner joins work:
We have a table called suppliers with two fields (supplier_id and supplier_ name).
It contains the following data:

supplier_id supplier_name
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 Nvidia

We have another table called orders with three fields (order_id, supplier_id, and order_date).
It contains the following data:

order_id supplier_id order_date
500125 10000 2003/05/12
500126 10001 2003/05/13

If we ran the SQL statement below:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

supplier_id name order_date
10000 IBM 2003/05/12
10001 Hewlett Packard 2003/05/13
The rows for Microsoft and Nvidia from the supplier table would be omitted, since the supplier_id's 10002 and 10003 do not exist in both tables.

Outer Join
Another type of join is called an outer join. This type of join returns all rows from one table and only those rows from a secondary table where the joined fields are equal (join condition is met).
For example,
select suppliers.supplier_id, suppliers.supplier_name, orders.order_date
from suppliers, orders
where suppliers.supplier_id = orders.supplier_id(+);
This SQL statement would return all rows from the suppliers table and only those rows from the orders table where the joined fields are equal.
The (+) after the orders.supplier_id field indicates that, if a supplier_id value in the suppliers table does not exist in the orders table, all fields in the orders table will display as in the result set.
The above SQL statement could also be written as follows:
select suppliers.supplier_id, suppliers.supplier_name, orders.order_date
from suppliers, orders
where orders.supplier_id(+) = suppliers.supplier_id

Let's look at some data to explain how outer joins work:
We have a table called suppliers with two fields (supplier_id and name).
It contains the following data:

supplier_id supplier_name
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 Nvidia

We have a second table called orders with three fields (order_id, supplier_id, and order_date).
It contains the following data:

order_id supplier_id order_date
500125 10000 2003/05/12
500126 10001 2003/05/13

If we ran the SQL statement below:
select suppliers.supplier_id, suppliers.supplier_name, orders.order_date
from suppliers, orders
where suppliers.supplier_id = orders.supplier_id(+);

Our result set would look like this:

supplier_id supplier_name order_date
10000 IBM 2003/05/12
10001 Hewlett Packard 2003/05/13
10002 Microsoft
10003 Nvidia
The rows for Microsoft and Nvidia would be included because an outer join was used. However, you will notice that the order_date field for those records contains a value.

Read more »

Oracle Learning - 4

SQL: GROUP BY Clause

The GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.
The syntax for the GROUP BY clause is:
SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n;
aggregate_function can be a function such as SUM, COUNT, MIN, or MAX.

Example using the SUM function
For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department).
SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department;
Because you have listed one column in your SELECT statement that is not encapsulated in the SUM function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

Example using the COUNT function
For example, you could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year.
SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

Example using the MIN function
For example, you could also use the MIN function to return the name of each department and the minimum salary in the department.
SELECT department, MIN(salary) as "Lowest salary"
FROM employees
GROUP BY department;

Example using the MAX function
For example, you could also use the MAX function to return the name of each department and the maximum salary in the department.
SELECT department, MAX(salary) as "Highest salary"
FROM employees
GROUP BY department;
SQL: ORDER BY Clause

The ORDER BY clause allows you to sort the records in your result set. The ORDER BY clause can only be used in SELECT statements.
The syntax for the ORDER BY clause is:
SELECT columns
FROM tables
WHERE predicates
ORDER BY column ASC/DESC;
The ORDER BY clause sorts the result set based on the columns specified. If the ASC or DESC value is omitted, the system assumed ascending order.
ASC indicates ascending order. (default)
DESC indicates descending order.

Example #1
SELECT supplier_city
FROM supplier
WHERE supplier_name = 'IBM'
ORDER BY supplier_city;
This would return all records sorted by the supplier_city field in ascending order.

Example #2
SELECT supplier_city
FROM supplier
WHERE supplier_name = 'IBM'
ORDER BY supplier_city DESC;
This would return all records sorted by the supplier_city field in descending order.

Example #3
You can also sort by relative position in the result set, where the first field in the result set is 1. The next field is 2, and so on.
SELECT supplier_city
FROM supplier
WHERE supplier_name = 'IBM'
ORDER BY 1 DESC;
This would return all records sorted by the supplier_city field in descending order, since the supplier_city field is in position #1 in the result set.

Example #4
SELECT supplier_city, supplier_state
FROM supplier
WHERE supplier_name = 'IBM'
ORDER BY supplier_city DESC, supplier_state ASC;
This would return all records sorted by the supplier_city field in descending order, with a secondary sort by supplier_state in ascending order.

Read more »

Oracle Learning - 3

SQL: BETWEEN Condition

The BETWEEN condition allows you to retrieve values within a range.
The syntax for the BETWEEN condition is:
SELECT columns
FROM tables
WHERE column1 between value1 and value2;
This SQL statement will return the records where column1 is within the range of value1 and value2 (inclusive). The BETWEEN function can be used in any valid SQL statement - select, insert, update, or delete.

Example #1 - Numbers
The following is an SQL statement that uses the BETWEEN function:
SELECT *
FROM suppliers
WHERE supplier_id between 5000 AND 5010;
This would return all rows where the supplier_id is between 5000 and 5010, inclusive. It is equivalent to the following SQL statement:
SELECT *
FROM suppliers
WHERE supplier_id >= 5000
AND supplier_id <= 5010;

Example #2 - Dates
You can also use the BETWEEN function with dates.
SELECT *
FROM orders
WHERE order_date between to_date ('2003/01/01', 'yyyy/mm/dd')
AND to_date ('2003/12/31', 'yyyy/mm/dd');
This SQL statement would return all orders where the order_date is between Jan 1, 2003 and Dec 31, 2003 (inclusive).
It would be equivalent to the following SQL statement:
SELECT *
FROM orders
WHERE order_date >= to_date('2003/01/01', 'yyyy/mm/dd')
AND order_date <= to_date('2003/12/31','yyyy/mm/dd');

Example #3 - NOT BETWEEN
The BETWEEN function can also be combined with the NOT operator.
For example,
SELECT *
FROM suppliers
WHERE supplier_id not between 5000 and 5500;
This would be equivalent to the following SQL:
SELECT *
FROM suppliers
WHERE supplier_id < 5000
OR supplier_id > 5500;
In this example, the result set would exclude all supplier_id values between the range of 5000 and 5500 (inclusive).
SQL: EXISTS Condition

The EXISTS condition is considered "to be met" if the subquery returns at least one row.
The syntax for the EXISTS condition is:
SELECT columns
FROM tables
WHERE EXISTS ( subquery );
The EXISTS condition can be used in any valid SQL statement - select, insert, update, or delete.

Example #1
Let's take a look at a simple example. The following is an SQL statement that uses the EXISTS condition:
SELECT *
FROM suppliers
WHERE EXISTS
(select *
from orders
where suppliers.supplier_id = orders.supplier_id);
This select statement will return all records from the suppliers table where there is at least one record in the orders table with the same supplier_id.

Example #2 - NOT EXISTS
The EXISTS condition can also be combined with the NOT operator.
For example,
SELECT *
FROM suppliers
WHERE not exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);
This will return all records from the suppliers table where there are no records in the orders table for the given supplier_id.

Example #3 - DELETE Statement
The following is an example of a delete statement that utilizes the EXISTS condition:
DELETE FROM suppliers
WHERE EXISTS
(select *
from orders
where suppliers.supplier_id = orders.supplier_id);

Example #4 - UPDATE Statement
The following is an example of an update statement that utilizes the EXISTS condition:

UPDATE supplier
SET supplier_name = ( SELECT customer.name
FROM customers
WHERE customers.customer_id = supplier.supplier_id)
WHERE EXISTS
( SELECT customer.name
FROM customers
WHERE customers.customer_id = supplier.supplier_id);

Example #5 - INSERT Statement
The following is an example of an insert statement that utilizes the EXISTS condition:
INSERT INTO supplier
(supplier_id, supplier_name)
SELECT account_no, name
FROM suppliers
WHERE exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);

Read more »

Oracle Learning - 2

SQL: Combining the "AND" and "OR" Conditions

The AND and OR conditions can be combined in a single SQL statement. It can be used in any valid SQL statement - select, insert, update, or delete.
When combining these conditions, it is important to use brackets so that the database knows what order to evaluate each condition.


Example #1
The first example that we'll take a look at an example that combines the AND and OR conditions.
SELECT *
FROM supplier
WHERE (city = 'New York' and name = 'IBM')
or (city = 'Newark');
This would return all suppliers that reside in either New York whose name is IBM, all supplies that reside in Newark. The brackets determine what order the AND and OR conditions are evaluated in.

Example #2
The next example takes a look at a more complex statement.
For example:
SELECT supplier_id
FROM supplier
WHERE (name = 'IBM')
or (name = 'Hewlett Packard' and city = 'Atlantic City')
or (name = 'Gateway' and status = 'Active' and city = 'Burma');
This SQL statement would return all supplier_id values where the supplier's name is IBM or the name is Hewlett Packard and the city is Atlantic City or the name is Gateway and the city is Burma.
SQL: LIKE Condition

The LIKE condition allows you to use wildcards in the where clause of an SQL statement. This allows you to perform pattern matching. The LIKE condition can be used in any valid SQL statement - select, insert, update, or delete.
The patterns that you can choose from are:
% allows you to match any string of any length (including zero length)
_ allows you to match on a single character

Examples using % wildcard
The first example that we'll take a look at involves using % in the where clause of a select statement. We are going to try to find all of the suppliers whose name begins with 'Hew'.
SELECT * FROM supplier
WHERE supplier_name like 'Hew%';


You can also using the wildcard multiple times within the same string. For example,
SELECT * FROM supplier
WHERE supplier_name like '%bob%';
In this example, we are looking for all suppliers whose name contains the characters 'bob'.

You could also use the LIKE condition to find suppliers whose name does not start with 'T'. For example,
SELECT * FROM supplier
WHERE supplier_name not like 'T%';
By placing the not keyword in front of the LIKE condition, you are able to retrieve all suppliers whose name does not start with 'T'.

Examples using _ wildcard
Next, let's explain how the _ wildcard works. Remember that the _ is looking for only one character.
For example,
SELECT * FROM supplier
WHERE supplier_name like 'Sm_th';
This SQL statement would return all suppliers whose name is 5 characters long, where the first two characters is 'Sm' and the last two characters is 'th'. For example, it could return suppliers whose name is 'Smith', 'Smyth', 'Smath', 'Smeth', etc.

Here is another example,
SELECT * FROM supplier
WHERE account_number like '12317_';
You might find that you are looking for an account number, but you only have 5 of the 6 digits. The example above, would retrieve potentially 10 records back (where the missing value could equal anything from 0 to 9). For example, it could return suppliers whose account numbers are:
123170
123171
123172
123173
123174
123175
123176
123177
123178
123179.


Examples using Escape Characters
Next, in Oracle, let's say you wanted to search for a % or a _ character in a LIKE condition. You can do this using an Escape character.
Please note that you can define an escape character as a single character (length of 1) ONLY.
For example,
SELECT * FROM supplier
WHERE supplier_name LIKE '!%' escape '!';
This SQL statement identifies the ! character as an escape character. This statement will return all suppliers whose name is %.

Here is another more complicated example:
SELECT * FROM supplier
WHERE supplier_name LIKE 'H%!%' escape '!';
This example returns all suppliers whose name starts with H and ends in %. For example, it would return a value such as 'Hello%'.

You can also use the Escape character with the _ character. For example,
SELECT * FROM supplier
WHERE supplier_name LIKE 'H%!_' escape '!';
This example returns all suppliers whose name starts with H and ends in _. For example, it would return a value such as 'Hello_'.
SQL: "IN" Function

The IN function helps reduce the need to use multiple OR conditions.
The syntax for the IN function is:
SELECT columns
FROM tables
WHERE column1 in (value1, value2, .... value_n);
This SQL statement will return the records where column1 is value1, value2..., or value_n. The IN function can be used in any valid SQL statement - select, insert, update, or delete.


Example #1
The following is an SQL statement that uses the IN function:
SELECT *
FROM supplier
WHERE supplier_name in ( 'IBM', 'Hewlett Packard', 'Microsoft');
This would return all rows where the supplier_name is either IBM, Hewlett Packard, or Microsoft. Because the * is used in the select, all fields from the supplier table would appear in the result set.
It is equivalent to the following statement:
SELECT *
FROM supplier
WHERE supplier_name = 'IBM'
OR supplier_name = 'Hewlett Packard'
OR supplier_name = 'Microsoft';
As you can see, using the IN function makes the statement easier to read and more efficient.

Example #2
You can also use the IN function with numeric values.
SELECT *
FROM orders
WHERE order_id in (10000, 10001, 10003, 10005);
This SQL statement would return all orders where the order_id is either 10000, 10001, 10003, or 10005.
It is equivalent to the following statement:
SELECT *
FROM orders
WHERE order_id = 10000
OR order_id = 10001
OR order_id = 10003
OR order_id = 10005;

Example #3 - "NOT IN"
The IN function can also be combined with the NOT operator.
For example,
SELECT *
FROM supplier
WHERE supplier_name not in ( 'IBM', 'Hewlett Packard', 'Microsoft');
This would return all rows where the supplier_name is neither IBM, Hewlett Packard, or Microsoft. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

Read more »

Oracle Learning -1

SQL: SELECT Statement

The SELECT statement allows you to retrieve records from one or more tables in your database.
The syntax for the SELECT statement is:
SELECT columns
FROM tables
WHERE predicates;

Example #1
Let's take a look at how to select all fields from a table.
SELECT *
FROM supplier
WHERE city = 'Newark';
In our example, we've used * to signify that we wish to view all fields from the supplier table where the supplier resides in Newark.

Example #2
You can also choose to select individual fields as opposed to all fields in the table.
For example:
SELECT name, city, state
FROM supplier
WHERE supplier_id > 1000;
This select statement would return all name, city, and state values from the supplier table where the supplier_id value is greater than 1000.

Example #3
You can also use the select statement to retrieve fields from multiple tables.
SELECT orders.order_id, supplier.name
FROM supplier, orders
WHERE supplier.supplier_id = orders.supplier_id;
The result set would display the order_id and suppier name fields where the supplier_id value existed in both the supplier and orders table.



SQL: DISTINCT Clause

The DISTINCT clause allows you to remove duplicates from the result set. The DISTINCT clause can only be used with select statements.
The syntax for the DISTINCT clause is:
SELECT DISTINCT columns
FROM tables
WHERE predicates;

Example #1
Let's take a look at a very simple example.
SELECT DISTINCT city
FROM supplier;
This SQL statement would return all unique cities from the supplier table.

Example #2
The DISTINCT clause can be used with more than one field.
For example:
SELECT DISTINCT city, state
FROM supplier;
This select statement would return each unique city and state combination. In this case, the distinct applies to each field listed after the DISTINCT keyword.

SQL: COUNT Function

The COUNT function returns the number of rows in a query.
The syntax for the COUNT function is:
SELECT COUNT(expression)
FROM tables
WHERE predicates;



Simple Example
For example, you might wish to know how many employees have a salary that is above $25,000 / year.
SELECT COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000;
In this example, we've aliased the count(*) field as "Number of employees". As a result, "Number of employees" will display as the field name when the result set is returned.

Example using DISTINCT
You can use the DISTINCT clause within the COUNT function.
For example, the SQL statement below returns the number of unique departments where at least one employee makes over $25,000 / year.
SELECT COUNT(DISTINCT department) as "Unique departments"
FROM employees
WHERE salary > 25000;
Again, the count(DISTINCT department) field is aliased as "Unique departments". This is the field name that will display in the result set.

Example using GROUP BY
In some cases, you will be required to use a GROUP BY clause with the COUNT function.
For example, you could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year.
SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;
Because you have listed one column in your SELECT statement that is not encapsulated in the COUNT function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

TIP: Performance Tuning
Since the COUNT function will return the same results regardless of what field(s) you include as the COUNT function parameters (ie: within the brackets), you can change the syntax of the COUNT function to COUNT(1) to get better performance as the database engine will not have to fetch back the data fields.

For example, based on the example above, the following syntax would result in better performance:
SELECT department, COUNT(1) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;
Now, the COUNT function does not need to retrieve all fields from the employees table as it had to when you used the COUNT(*) syntax. It will merely retrieve the numeric value of 1 for each record that meets your criteria.
SQL: WHERE Clause

The WHERE clause allows you to filter the results from an SQL statement - select, insert, update, or delete statement.
It is difficult to explain the basic syntax for the WHERE clause, so instead, we'll take a look at some examples.

Example #1
SELECT *
FROM supplier
WHERE supplier_name = 'IBM';
In this first example, we've used the WHERE clause to filter our results from the supplier table. The SQL statement above would return all rows from the supplier table where the supplier_name is IBM. Because the * is used in the select, all fields from the supplier table would appear in the result set.

Example #2
SELECT supplier_id
FROM supplier
WHERE supplier_name = 'IBM'
or supplier_city = 'Newark';
We can define a WHERE clause with multiple conditions. This SQL statement would return all supplier_id values where the supplier_name is IBM or the supplier_city is Newark.

Example #3
SELECT supplier.suppler_name, orders.order_id
FROM supplier, orders
WHERE supplier.supplier_id = orders.supplier_id
and supplier.supplier_city = 'Atlantic City';
We can also use the WHERE clause to join multiple tables together in a single SQL statement. This SQL statement would return all supplier names and order_ids where there is a matching record in the supplier and orders tables based on supplier_id, and where the supplier_city is Atlantic City.

SQL: "AND" Condition

The AND condition allows you to create an SQL statement based on 2 or more conditions being met. It can be used in any valid SQL statement - select, insert, update, or delete.
The syntax for the AND condition is:
SELECT columns
FROM tables
WHERE column1 = 'value1'
and column2 = 'value2';
The AND condition requires that each condition be must be met for the record to be included in the result set. In this case, column1 has to equal 'value1' and column2 has to equal 'value2'.

Example #1
The first example that we'll take a look at involves a very simple example using the AND condition.
SELECT *
FROM supplier
WHERE city = 'New York'
and type = 'PC Manufacturer';
This would return all suppliers that reside in New York and are PC Manufacturers. Because the * is used in the select, all fields from the supplier table would appear in the result set.

Example #2
Our next example demonstrates how the AND condition can be used to "join" multiple tables in an SQL statement.
SELECT order.order_id, supplier.supplier_name
FROM supplier, order
WHERE supplier.supplier_id = order.supplier_id
and supplier.supplier_name = 'IBM';
This would return all rows where the supplier_name is IBM. And the supplier and order tables are joined on supplier_id. You will notice that all of the fields are prefixed with the table names (ie: order.order_id). This is required to eliminate any ambiguity as to which field is being referenced; as the same field name can exist in both the supplier and order tables.
In this case, the result set would only display the order_id and supplier_name fields (as listed in the first part of the select statement.).
SQL: "OR" Condition

The OR condition allows you to create an SQL statement where records are returned when any one of the conditions are met. It can be used in any valid SQL statement - select, insert, update, or delete.

The syntax for the OR condition is:
SELECT columns
FROM tables
WHERE column1 = 'value1'
or column2 = 'value2';
The OR condition requires that any of the conditions be must be met for the record to be included in the result set. In this case, column1 has to equal 'value1' OR column2 has to equal 'value2'.

Example #1
The first example that we'll take a look at involves a very simple example using the OR condition.
SELECT *
FROM supplier
WHERE city = 'New York'
or city = 'Newark';
This would return all suppliers that reside in either New York or Newark. Because the * is used in the select, all fields from the supplier table would appear in the result set.

Example #2
The next example takes a look at three conditions. If any of these conditions is met, the record will be included in the result set.
For example:
SELECT supplier_id
FROM supplier
WHERE name = 'IBM'
or name = 'Hewlett Packard'
or name = 'Gateway';
This SQL statement would return all supplier_id values where the supplier's name is either IBM, Hewlett Packard or Gateway.

Read more »