Week 4: Apply
Introduction to SQL
Information Systems and Databases
1
ISDB - Intro to SQL
Learning Objectives
Learning objectives:
• To identify the components of an SQL SELECT statement
• Use an ORDER BY clause to order the output from a
query
• Use arithmetic functions in a query
• Perform searches for NULL values
• Use the DUAL table to test functions
2
ISDB - Intro to SQL
Using APEX SQL Workshop
Type your
SQL
statements
here
Results
displayed
here
3
ISDB - Intro to SQL
Example data
• The examples in these slides will be based on the ‘Demo’
tables available in the ‘Demo Tables’ Script file (which you
need to upload to your Oracle APEX account
• See ‘setting up Oracle Academy’ handout and slides
4
ISDB - Intro to SQL
Demo tables
Used for the
examples in these
slides
5
ISDB - Intro to SQL
‘Emp’ table:
Data:
ISDB - Intro to SQL 6
‘Dept’table
Data:
ISDB - Intro to SQL 7
Structure of a SELECT statement:
Used to retrieve data from one or more tables:
SELECT and FROM are mandatory;
CLAUSES have to be specified in the above order;
SELECT statements are not case sensitive – but good
practice is to have all reserved words in upper case;
GOOD PRACTICE is to start each clause on a separate line;
ISDB - Intro to SQL 8
Selecting columns from a table
Check you are using the correct column names.
Eg: to display the locations of all departments:
SELECT loc
FROM dept;
Result:
BUT:
SELECT location
FROM dept;
Result: error message:
ISDB - Intro to SQL 9
Selecting columns from a table
Asterisk * is the equivalent of ‘all columns’ ie equivalent to the
statement:
SELECT empno, ename, job, mgr, hiredate,
sal, deptno
FROM emp;
ISDB - Intro to SQL 10
The ORDER BY clause
• To order the output:
SELECT ename
FROM emp
ORDER BY ename;
Ordered alphabetically
A-Z
SELECT ename
FROM emp
ORDER BY ename DESC;
Ordered in DESCending
order Z - A
ISDB - Intro to SQL 11
More ORDER BY ..
To list the deptno, name and salary for all employees, ordered
by salary within each department:
SELECT deptno, ename, sal
FROM emp
ORDER BY deptno, sal;
Result is a primary sort by deptno:
Then sort of sal within each deptno:
ISDB - Intro to SQL 12
More ORDER BY
You can also use the column sequence for ordering, eg:
SELECT deptno, job, ename
FROM emp
ORDER BY 1;
This will display the emp table records in deptno order –
(column 1 in the SELECT clause)
ISDB - Intro to SQL 13
Renaming column headings
To rename a column heading to make the output more ‘user
friendly’:
SELECT ename, sal Note: column
FROM emp; names are used for
the default
headings
SELECT ename “Employee Name”, sal “Salary”
FROM emp; OR:
SELECT ename AS “Employee Name”, sal AS “Salary”
FROM emp;
Renamed headings
ISDB - Intro to SQL 14
Eliminating Duplicate Rows – using ‘DISTINCT’
SELECT job SELECT DISTINCT job
FROM emp FROM emp
ISDB - Intro to SQL 15
SQL EXERCISE 1:
(using the Dept and
Emp tables)
1. Retrieve a list of department names and
department locations.
2. List the employee number and salary for all
employees. Display the output in employee number
order.
3. The following script will produce errors when
run. Why?
To select the name and hire date of all
employees:
SELECT name, hiredate
FROM employee;
ISDB - Intro to SQL 16
The Concatenation || operator
This ‘joins up’ the contents of two columns or text:
Eg: supposing we had an employeeNames table with
separate firstname and lastname columns:
SELECT firstName, lastName
FROM employeeNames;
SELECT firstName||lastName
FROM employeeNames;
NOTE: The SINGLE column:
‘FIRSTNAME||LASTNAME’
BUT names are stuck together!
SELECT firstName||’ ’||lastName
FROM employeeNames;
Here we have concatenated a space ‘ ‘
between FIRSTNAME and LASTNAME
17
The Concatenation || operator
Concatenating text:
SELECT ename||‘ earns ’||sal AS
“Salary Status”
FROM emp
NOTE: insertion of a string
literal – ‘ earns ’
And renaming of the
concatenated column to
‘Salary Status’
ISDB - Intro to SQL 18
Using Arithmetic operators
Eg to display employee names and their salary, with a 20%
increase:
SELECT ename, sal * 1.2
FROM emp;
Note: the salary change is for display only;
No changes are made to the data in the
tables
ISDB - Intro to SQL 19
The DUAL table
DUAL is a table available for performing queries that
don’t need to retrieve data from any tables.
DESCRIBE DUAL;
To display the answer to 2 x 4:
SELECT 2 * 4
FROM DUAL;
You could run:
SELECT 2 * 4
FROM dept;
Output is repeated 4 times because
there are 4 rows in the ‘dept’ table
ISDB - Intro to SQL 20
Exercise
Write SQL queries to produce the following:
1) A list of all department names, ordered
by name.
2) All employee numbers, names and
their job.
3) Employees and their salaries, starting
off with the highest paid employee.
4) The salary for all employees is to
double. Produce a list of all employee
names and a column headed ‘new
salary’ listing their new salary.
5) Produce one line of output that reads:
‘This is one line of output’
ISDB - Intro to SQL 21
Summary
• The SELECT statement is used to retrieve data from the
database
• An ORDER BY clause is used to order the output from a
query
• Arithmetic functions can be used in a query
• IS NULL can be used to perform searches for NULL values
• The DUAL table can be used to test functions, where no
data is required to be retrieved from tables.
ISDB - Intro to SQL 22
Further Reading:
Casteel, J: Oracle 11g SQL – Chapters 2
ISDB - Intro to SQL 23