GROUP BY Clause and
sorting
Question: When you use a GROUP BY clause with one or more columns,
will the results be in the sorted order of GROUP BY columns (by default) or
shall we use ORDER BY clause?
Answer: You may get lucky and find that your result set is sorted in
the order of the GROUP BY columns, but we recommend always using
the ORDER BY clause whenever sorting is required.
Example #1
SELECT
department, depart_head, SUM(sales) as "Total sales"
FROM
order_details
GROUP
BY department, depart_head
ORDER
BY department;
This
example would sort your results by department, in ascending order.
Example #2
SELECT
department, depart_head, SUM(sales) as "Total sales"
FROM
order_details
GROUP
BY department, depart_head
ORDER
BY department desc, depart_head;
This
example would first sort your results by department in descending order, then
depart_head in ascending order.
SQL: SELECT Statement
The SQL SELECT statement allows you to retrieve records from one or more tables in your SQL database.
The syntax for the SQL SELECT statement is:
SELECT columns
FROM tables
WHERE predicates;
SQL SELECT Statement - Select all fields from one table example
Let's take a look at how to use the SQL SELECT statement to select all fields from a table.
SELECT *
FROM suppliers
WHERE city = 'Newark';
In this SQL SELECT statement, we've used * to signify that we wish to view all fields from the suppliers table where the supplier resides in Newark.
SQL SELECT Statement - Selecting individual fields from one table example
You can also use the SQL SELECT statement to select individual fields from the table, as opposed to all fields from the table.
For example:
SELECT name, city, state
FROM suppliers
WHERE supplier_id > 1000;
This SQL SELECT statement would return only the name, city, and state fields from the suppliers table where the supplier_id value is greater than 1000.
SQL SELECT Statement - Select fields from multiple tables example
You can also use the SQL SELECT statement to retrieve fields from multiple tables.
SELECT orders.order_id, suppliers.name
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;
This SQL SELECT statement
joins two tables together to gives us a result set that displays the order_id and supplier name fields where the supplier_id value existed in both the suppliers and orders table.
SQL: ORDER BY Clause
The SQL ORDER BY clause allows you to sort the records in your result set. The SQL ORDER BY clause can only be used in
SQL SELECT statements.
The syntax for the SQL ORDER BY clause is:
SELECT columns
FROM tables
WHERE predicates
ORDER BY column ASC/DESC;
The SQL ORDER BY clause sorts the result set based on the columns specified. If the ASC or DESC value is omitted, it is sorted by ASC.
ASC indicates ascending order. (default)
DESC indicates descending order.
SQL ORDER BY - Sorting without using ASC/DESC attribute example
The SQL ORDER BY clause can be used without specifying the ASC or DESC value. When this attribute is omitted fromthe SQL ORDER BY clause, the sort order is defaulted to ASC or ascending order.
For example:
SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY supplier_city;
This SQL ORDER BY example would return all records sorted by the supplier_city field in ascending order and would be equivalent to the following SQL ORDER BY clause:
SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY supplier_city ASC;
Most programmers omit the ASC attribute if sorting in ascending order.
SQL ORDER BY - Sorting in descending order
When sorting your result set in descending order, you use the DESC attribute in your ORDER BY clause as follows:
SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY supplier_city DESC;
This SQL ORDER BY example would return all records sorted by the supplier_city field in descending order.
SQL ORDER BY - Sorting by relative position example
You can also use the SQL ORDER BY clause to 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.
For example:
SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY 1 DESC;
This SQL ORDER BY 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 and would be equivalent to the following SQL ORDER BY clause:
SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY supplier_city DESC;
SQL ORDER BY - Using both ASC and DESC attributes together
When sorting your result set using the SQL ORDER BY clause, you can use the ASC and DESC attributes in a single
SQL SELECT statement.
For example:
SELECT supplier_city, supplier_state
FROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY supplier_city DESC, supplier_state ASC;
This SQL ORDER BY would return all records sorted by the supplier_city field in descending order, with a secondary sort by supplier_state in ascending order.
SQL: LIKE Condition
The SQL LIKE condition allows you to use wildcards in the
SQL WHERE clause of an SQL statement. This allows you to perform pattern matching. The SQL LIKE condition can be used in any valid SQL statement -
SQL SELECT statement,
SQL INSERT statement,
SQL UPDATE statement, or
SQL DELETE statement.
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
SQL LIKE Condition - Using % wildcard example
Let's explain how the % wildcard works in the SQL LIKE condition. We are going to try to find all of the suppliers whose name begins with 'Hew'.
SELECT * FROM suppliers
WHERE supplier_name like 'Hew%';
You can also using the % wildcard multiple times within the same string. For example,
SELECT * FROM suppliers
WHERE supplier_name like '%bob%';
In this SQL LIKE condition example, we are looking for all suppliers whose name contains the characters 'bob'.
You could also use the SQL LIKE condition to find suppliers whose name does
not start with 'T'.
For example:
SELECT * FROM suppliers
WHERE supplier_name not like 'T%';
By placing the
not keyword in front of the SQL LIKE condition, you are able to retrieve all suppliers whose name does
not start with 'T'.
SQL LIKE Condition - Using _ wildcard example
Next, let's explain how the _ wildcard works in the SQL LIKE condition. Remember that the _ is looking for only one character.
For example:
SELECT * FROM suppliers
WHERE supplier_name like 'Sm_th';
This SQL LIKE condition example 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 suppliers
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
SQL LIKE Condition - Using Escape Characters example
Next, in Oracle, let's say you wanted to search for a % or a _ character in the SQL LIKE condition. You can do this using an Escape character.
Please note that you can only define an escape character as a single character (length of 1).
For example:
SELECT * FROM suppliers
WHERE supplier_name LIKE '!%' escape '!';
This SQL LIKE condition example identifies the ! character as an escape character. This statement will return all suppliers whose name is %.
Here is another more complicated example using escape characters in the SQL LIKE condition.
SELECT * FROM suppliers
WHERE supplier_name LIKE 'H%!%' escape '!';
This SQL LIKE condition 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 in the SQL LIKE condition.
For example:
SELECT * FROM suppliers
WHERE supplier_name LIKE 'H%!_' escape '!';
This SQL LIKE condition example returns all suppliers whose name starts with H and ends in _. For example, it would return a value such as 'Hello_'.
Frequently Asked Questions
Question: How do you incorporate the
Oracle upper function with the SQL LIKE condition? I'm trying to query against a free text field for all records containing the word "test". The problem is that it can be entered in the following ways: TEST, Test, or test.
Answer: To answer this question, let's take a look at an example.
Let's say that we have a
suppliers table with a field called
supplier_name that contains the values TEST, Test, or test.
If we wanted to find all records containing the word "test", regardless of whether it was stored as TEST, Test, or test, we could run either of the following SQL SELECT statements:
select * from suppliers
where upper(supplier_name) like ('TEST%');
or
select * from suppliers
where upper(supplier_name) like upper('test%')
These SQL SELECT statements use a combination of the
Oracle upper function and the SQL LIKE condition to return all of the records where the
supplier_name field contains the word "test", regardless of whether it was stored as TEST, Test, or test.
Practice Exercise #1:
Based on the
employees table populated with the following data, find all records whose
employee_name ends with the letter "h".
CREATE TABLE employees
( employee_number number(10) not null,
employee_name varchar2(50) not null,
salary number(6),
CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1001, 'John Smith', 62000);
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1002, 'Jane Anderson', 57500);
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1003, 'Brad Everest', 71000);
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1004, 'Jack Horvath', 42000);
Solution:
The following SQL SELECT statement uses the SQL LIKE condition to return the records whose
employee_name ends with the letter "h".
SELECT *
FROM employees
WHERE employee_name LIKE '%h';
It would return the following result set:
| EMPLOYEE_NUMBER | EMPLOYEE_NAME | SALARY |
|---|
| 1001 | John Smith | 62000 |
| 1004 | Jack Horvath | 42000 |
Practice Exercise #2:
Based on the
employees table populated with the following data, find all records whose
employee_name contains the letter "s".
CREATE TABLE employees
( employee_number number(10) not null,
employee_name varchar2(50) not null,
salary number(6),
CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1001, 'John Smith', 62000);
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1002, 'Jane Anderson', 57500);
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1003, 'Brad Everest', 71000);
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1004, 'Jack Horvath', 42000);
Solution:
The following SQL SELECT statement would use the SQL LIKE condition to return the records whose
employee_name contains the letter "s".
SELECT *
FROM employees
WHERE employee_name LIKE '%s%';
It would return the following result set:
| EMPLOYEE_NUMBER | EMPLOYEE_NAME | SALARY |
|---|
| 1002 | Jane Anderson | 57500 |
| 1003 | Brad Everest | 71000 |
Practice Exercise #3:
Based on the
suppliers table populated with the following data, find all records whose
supplier_id is 4 digits and starts with "500".
CREATE TABLE suppliers
( supplier_id varchar2(10) not null,
supplier_name varchar2(50) not null,
city varchar2(50),
CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);
INSERT INTO suppliers(supplier_id, supplier_name, city)
VALUES ('5008', 'Microsoft', 'New York');
INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES ('5009', 'IBM', 'Chicago');
INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES ('5010', 'Red Hat', 'Detroit');
INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES ('5011', 'NVIDIA', 'New York');
Solution:
The following SQL SELECT statement would use the SQL LIKE condition to return the records whose
supplier_id is 4 digits and starts with "500".
select *
FROM suppliers
WHERE supplier_id LIKE '500_';
It would return the following result set:
| SUPPLIER_ID | SUPPLIER_NAME | CITY |
|---|
| 5008 | Microsoft | New York |
| 5009 | IBM | Chicago |
SQL: HAVING Clause
The syntax for the SQL HAVING clause is:
SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n
HAVING condition1 ... condition_n;
SQL HAVING Clause - Using the SUM function example
You could also use the
SQL SUM function to return the name of the department and the total sales (in the associated department). The SQL HAVING clause will filter the results so that only departments with sales greater than $1000 will be returned.
SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;
SQL HAVING Clause - Using the COUNT function example
You could use the
SQL COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year. The SQL HAVING clause will filter the results so that only departments with more than 10 employees will be returned.
SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department
HAVING COUNT(*) > 10;
SQL HAVING Clause - Using the MIN function example
You could also use the
SQL MIN function to return the name of each department and the minimum salary in the department. The SQL HAVING clause will return only those departments where the starting salary is $35,000.
SELECT department, MIN(salary) as "Lowest salary"
FROM employees
GROUP BY department
HAVING MIN(salary) = 35000;
SQL HAVING Clause - Using the MAX function
For example, you could also use the
SQL MAX function to return the name of each department and the maximum salary in the department. The SQL HAVING clause will return only those departments whose maximum salary is less than $50,000.
SELECT department, MAX(salary) as "Highest salary"
FROM employees
GROUP BY department
HAVING MAX(salary) < 50000;
SQL: WHERE Clause
It is difficult to explain the syntax for the SQL WHERE clause, so instead, we'll take a look at some examples.
SQL WHERE Clause - Single condition example
SELECT *
FROM suppliers
WHERE supplier_name = 'IBM';
In this SQL Where clause example, we've used the SQL WHERE clause to filter our results from the suppliers table. The SQL statement above would return all rows from the suppliers table where the supplier_name is IBM. Because the * is used in the select, all fields from the suppliers table would appear in the result set.
SQL WHERE Clause - Using SQL "AND" condition example
SELECT *
FROM suppliers
WHERE supplier_city = 'Chicago'
and supplier_id > 1000;
This SQL Where clause example uses the WHERE clause to define multiple conditions. In this case, this SQL statement uses the
SQL "AND" Condition to return all suppliers that are located in Chicago and whose supplier_id is greater than 1000.
SQL WHERE Clause - Using SQL "OR" condition example
SELECT supplier_id
FROM suppliers
WHERE supplier_name = 'IBM'
or supplier_name = 'Apple';
This SQL Where clause example uses the WHERE clause to define multiple conditions, but instead of using the
SQL "AND" Condition, it uses the
SQL "OR" Condition. In this case, this SQL statement would return all supplier_id values where the supplier_name is IBM
or Apple.
SQL WHERE Clause - Joining Tables example
SELECT suppliers.suppler_name, orders.order_id
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_city = 'Atlantic City';
This SQL Where clause example uses the SQL 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 suppliers and orders tables based on
supplier_id, and where the
supplier_city is Atlantic City.
SQL: INSERT Statement
The SQL INSERT statement allows you to insert a single record or multiple records into a table.
The syntax for the SQL INSERT statement is:
INSERT INTO table
(column-1, column-2, ... column-n)
VALUES
(value-1, value-2, ... value-n);
SQL INSERT Statement - Using VALUES keyword example
The simplest way to create an SQL INSERT statement to list the values using the VALUES keyword.
For example:
INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(24553, 'IBM');
This SQL INSERT statement would result in one record being inserted into the suppliers table. This new record would have a supplier_id of 24553 and a supplier_name of IBM.
SQL INSERT Statement - Using sub-selects example
You can also create more complicated SQL INSERT statements using sub-selects.
For example:
INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city = 'Newark';
By placing a "select" in the SQL INSERT statement, you can perform multiples inserts quickly.
With this type of insert, you may wish to check for the number of rows being inserted. You can determine the number of rows that will be inserted by running the following SQL SELECT statement
before performing the insert.
SELECT count(*)
FROM customers
WHERE city = 'Newark';
Frequently Asked Questions
Question: I am setting up a database with clients. I know that you use the SQL INSERT statement to insert information in the database, but how do I make sure that I do not enter the same client information again?
Answer: You can make sure that you do not insert duplicate information by using the
SQL EXISTS condition.
For example, if you had a table named
clients with a primary key of
client_id, you could use the following SQL INSERT statement:
INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE not exists (select * from clients
where clients.client_id = suppliers.supplier_id);
This SQL INSERT statement inserts multiple records with a subselect.
If you wanted to insert a single record, you could use the following SQL INSERT statement:
INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE not exists (select * from clients
where clients.client_id = 10345);
The use of the
dual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.
Question: How can I insert multiple rows of explicit data in one SQL command in Oracle?
Answer: The following is an example of how you might insert 3 rows into the
suppliers table in Oracle, using an SQL INSERT statement:
INSERT ALL
INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
SELECT * FROM dual;
