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.