Tutorial 1

Contents

• SQL with Oracle
• SPECIAL TABLE
• Predicates
• Comparisons
• BETWEEN (Inclusive)
• LIKE
• IS NULL
• Set Functions
• Ordering rows of a query result
• Prevention of duplicate rows
• Counting unique rows

SQL with Oracle

SQL (Structured Query Language) is the structured query language used to manipulate relational databases. The concept of relational database was first proposed by Codd in 1970, and a language to extract and manipulate the data in it was developed theoretically during the following years. All SQL statements are instructions to the database. SQL is a non-procedural language, which means that commands are not executed step by step according to how they were written, but they are retained in memory, read through and executed in the most effective way. In relational database terminology, SQL provides 'automatic navigation' to the data in the database.

In these notes, you see an indication of two possible interfaces to SQL, namely sqlplus and activeSQL. Sqlplus is the standard Oracle interface while activeSQL is an experimental interface which Napier students have access to. For our tutorials we will all be using activeSQL.

Command Endings

Note that all SQL commands typed into sqlplus MUST end in a ; (semicolon) character. It will not work without it. The activeSQL interface is more forgiving, but even then if you enter more than 1 command into the interface at a time you MUST separate the commands with a semicolon.

When entering SQL, you can have as many space characters and return characters as you like. They are completely ignored by Oracle. Sqlplus will, when you hit return, tell you what line you are currently on. These numbers are not part of the command so do not let them confuse you. In sqlplus, if you hit return twice (return on a blank line) the current command is cancelled.

In activeSQL, no command is executed until you hit the submit button.

SELECT

SQL command SELECT is used to retrieve information from a table. SELECT informs Oracle which table(s) to use and which column(s) and row(s) to retrieve.

The asterisk can be used to denote all fields.

To list all fields and all records from the table employee.

SELECT *
FROM employee
;

To display only the fields empno and depno but all records

SELECT empno,depno
FROM employee;

SPECIAL TABLE

There is a special table called CAT, which contains the name and type of all tables in your namespace. Running

SELECT * from CAT:

produces the name and type of all local tables. This includes the 5 tables used in these tutorials: employee, empcourse, jobhistory, course, and department.

To find out about a particular table, you can look at the commands which created it, or you can use the DESCRIBE command. This tells you the attributes of the table in question. In sqlplus, this description does not include the Foreign Keys (the links to other tables - more of this in tutorial 2) but in activeSQL Foreign Keys ARE shown. For instance:

DESCRIBE employee;

          empno               integer        primary key
          surname             varchar(15)
          forenames           varchar(30)
          dob                 date
          address             varchar(50)
          telno               varchar(20)
          depno               integer        references department(depno)

varchar(20) indicates a string which can be up to 20 characters long.

Date indicates that field is an Oracle date.

Integer indicates that that attribute is a number.

Depno integer References department(depno) tells us that depno in employee is a link to the department table's depno attribute. In this confusing case there are two attributes called depno, one in employee and one in department. These attributes are different attributes in different tables.

Predicates

Search conditions are made up of predicates. These are then combined together with ANDs, ORs, and NOTs.

There are seven types of predicate:

• comparison
• BETWEEN predicate
• IN predicate
• LIKE predicate
• ANY or ALL predicate
• EXISTS predicate
• IS NULL

Comparisons

The comparisons available are

List the fields empno, surname, telno of all employees who have a surname Wright. Notice the quote marks required for character constants. Note also that anything within the quotes is case sensitive.

SELECT empno, surname, telno
FROM employee
WHERE surname = 'Wright'

List all current salaries in the range £20000 to £30000, listing their empno values.

SELECT empno, enddate, salary
FROM jobhistory
WHERE enddate IS NULL
AND salary >= 20000
AND salary <= 30000

List all the employees working in the company on January 1st 1980 and their position

SELECT empno, position, startdate, enddate
FROM jobhistory
WHERE (startdate < '01-JAN-1980' AND enddate > '01-JAN-1980')
OR (startdate < '01-JAN-1980' AND enddate IS NULL)

BETWEEN (Inclusive)

List all the courses which occurred during 1988

SELECT *
FROM course
WHERE cdate BETWEEN '01-JAN-1988' AND '31-DEC-1988'

List all the courses which did not occur in 1988

SELECT *
FROM course
WHERE cdate NOT BETWEEN '01-JAN-1988' AND '31-DEC -1988'

LIKE

The LIKE predicate provides the only pattern matching capability in SQL for the character data types. It takes the following form

columnname [NOT] LIKE pattern-to-match

The pattern match characters are the percent sign (%) to denote 0 or more arbitrary characters, and the underscore (_) to denote exactly one arbitrary character.

List the employee numbers and surnames of all employees who have a surname beginning with C.

SELECT empno,surname
FROM employee
WHERE surname LIKE 'C%'

List all course numbers and names for any course to do with accounting.

SELECT courseno,cname
FROM course
WHERE cname LIKE '%ccount%'

List all employees who have r as the second letter of their forename.

SELECT surname, forenames
FROM employee
WHERE forenames LIKE '_r%'

IS NULL

List all employees numbers and their current position

SELECT empno,position
FROM jobhistory
WHERE enddate IS NULL

The remaining predicates will be dealt with at a later stage.

Set Functions

A set function is a function that operates on an entire column of values, not just a single value.

List the total wage bill for the company at the moment.

SELECT SUM(salary)
FROM jobhistory
WHERE enddate IS NULL

This will retrieve the total salary for employees, where the enddate is empty or NULL.

The following are the set functions supported

Table 1: Set Functions

Find the number of employees working currently.

SELECT COUNT(*)
FROM jobhistory
WHERE enddate IS NULL

The COUNT(*) function is used to count rows in a table, and is the exception to the following rule.

NULL values are ignored by the set functions.

Count how many jobs that employee number 25 has had previously.

SELECT COUNT(enddate)
FROM jobhistory
WHERE empno = 25

Count how many jobs employee number 25 has had, including current job.

SELECT COUNT(startdate)
FROM jobhistory
WHERE empno = 25

Calculate the average salary for all employees.

SELECT AVG(salary)
FROM jobhistory
WHERE enddate IS NULL

Note that a 'column label' might be usefully added. This will be output in uppercase format unless enclosed in double quotes as follows :--

Find out the greatest salary.

SELECT MAX(salary)    "Highest Salary"
FROM jobhistory
WHERE enddate IS NULL

Ordering rows of a query result

The order in which the selected rows are displayed is changed by adding an ORDER BY clause to the end of your SELECT command. The ordering is done numerically or alphabetically and can be ascending or descending.

List all the employee numbers and salaries, ordered by their salary.

SELECT empno, salary
FROM jobhistory
WHERE enddate IS NULL
ORDER BY salary

To order by descending order you need to add DESC in the ORDER BY command

SELECT empno, salary
FROM jobhistory
WHERE enddate IS NULL
ORDER BY salary DESC

Prevention of duplicate rows

If you print all the jobs in the jobhistory table you will get duplicate rows.

SELECT position
FROM jobhistory

To print out only one for each different job you need to add DISTINCT in the SELECT clause.

SELECT DISTINCT position
FROM jobhistory

Counting unique rows

Often you would like to count how many different rows exist in the result of a query. Doing:

SELECT DISTINCT count(position)
FROM jobhistory

result in an answer which is no different from the same query without the DISTINCT. This is caused by the fact that count() is done before DISTINCT, and therefore in this case DISTINCT does nothing. What is actually needed is a way of forcing DISTINCT to be done before the count. This can be achieved by doing:

SELECT count(DISTINCT position)
FROM jobhistory