Database Lab Assignment

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.

Learn more about the ORDER BY Clause.

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.

Learn more about SQL joins.

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 SQL HAVING clause is used in combination with the SQL GROUP BY clause. It can be used in an SQL SELECT statement to filter the records that a SQL GROUP BY returns.

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;

aggregate_function can be a function such as SQL SUM function, SQL COUNT function, SQL MIN function, or SQL MAX function.

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

The SQL WHERE clause allows you to filter the results from an SQL statement - SQL SELECT statement, SQL INSERT statement, SQL UPDATE statement, or SQL DELETE statement.

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.

Learn more about SQL joins.

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 statementbefore 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;