RDBMS and Oracle SQL Plus

1
RDBMS and Oracle SQL Plus

• delivered by John Stockwell

2
Databases and RBDMS

• Database - Any system that stores and processes
  data
• Database Management System software with the
  capability to store data in an integrated
  structured format and to enable users to
  retrieve, manipulate and mange the data

3
RDBMS

• Relational database management system
• Database is relational data stored in tables
• Relationships between tables allow for
  cross-referencing
• Implementation follows design using modelling
  techniques to produce an entity relationship
  diagram

4
Oracle

• Oracle is a corporation which has produced a
  database and a way of manipulating it using SQL
  statements
• It is used world wide
• Structured Query Language allows for the input,
  output and manipulation of data in the database

5
SQL Plus Command set

• DATA DEFINITION LANGUAGE (DDL)
• CREATE used to create, change and
• ALTER drop objects within the
• DROP database. Objects are tables, views,
  indexes, synonyms
• MANIPULATION LANGUAGE (DML)
• INSERT for entering new rows
• UPDATE - for changing existing rows
• DELETE - removing unwanted rows

6
SQL Command set (cont.)

• DATA CONTROL LANGUAGE (DCL)
• GRANT used to control access rights REVOKE on
  objects within the database
• These statements are NOT case-sensitive
• Each of the statements should be terminated with
  a semicolon at the end of the last line, OR
  a slash can be used on the following line.

7
The Select Statement

• This is the most commonly used statement, to
  query database information.
• The informal syntax is a follows
• SELECT column_name(s)
• FROM table_name(s)
• WHERE constraint(s)
• asks for all the columns to be returned.

8
The Select Statement data retrieval.

• The top line SELECT describes the column
  name(s) from which you are attempting to build
  the query.
• The second line FROM lists the tables or
  relations that contain the data necessary to
  service the query
• The third line, the WHERE constraint is
  optional. The WHERE clause allows you to specify
  the tuples (rows) to be returned.

9
The Select Statement Examples

• The basic statement
• If we wish to display all of the tuples (rows) of
  a given relation (i.e. table)
• SELECT
• FROM EMP ( end of statement to be executed)
• We can constrain this by requesting only those
  employees with the name Allen
• SELECT
• FROM EMP
• WHERE ENAME ALLEN (this is the constraint)
• Allen is enclosed in quotation marks as we are
  searching on a column data type which is a string

10
Select Statement Examples

• If we want to search on numeric columns
• SELECT
• FROM DEPT
• WHERE DEPTNO 10
• SELECT
• FROM EMP
• WHERE EMPNO 7654

11
The Where Clause

• This clause is used to restrict the output to
  show a subset of the rows from a table.
• It must be placed after the FROM clause
• The WHERE clause has the following format
• WHERE Operators may be
• Comparative Operators
• ! lt gt lt gt ltgt and
• Logical OperatorsAND, OR, NOT
• SELECT
• FROM EMP
• WHERE EMPNO 7521 AND ENAME WARD

12
Where Clause Example

• If we said
• SELECT
• FROM EMP
• WHERE EMPNO 7369 AND ENAME WARD
• We would return an empty table as there are no
  tuples (rows) that satisfy both of these
  constraints.
• However if we said
• SELECT
• FROM EMP
• WHERE EMPNO 7876 OR ENAME KING
• Two tuples would be returned as these would
  satisfy one or other of the constraints.

13
Select statement retrieving attributes

• You can retrieve attribute values by specifying
  columns The command is
• SELECT COLUMN_NAME
• FROM TABLE_NAME
• SELECT ENAME
• FROM EMP
• Will return one column, and the values from the
  ename column (14 rows).
• SELECT EMPNO,ENAME,JOB
• FROM EMP
• Will return three columns, in that order (14
  rows)

14
Where clause more constraints

• Row and Columns constraints can be set as
  follows
• SELECT EMPNO, ENAME
• FROM EMP
• WHERE SAL gt 2000
• Returns only those columns empno and ename from
  the emp table and only those rows with a salary
  greater than 2000.
• SELECT EMPNO, ENAME, JOB, SAL
• FROM EMP
• WHERE SAL BETWEEN 1500 AND 3000
• Displays all those employees who earn between
  1500 and 3000 (four columns and eight rows in
  this case)

15
Where clause The Like operator

• If we want to search for employees whose name
  begins with the letter A
• We can say
• SELECT EMPNO, ENAME, JOB
• FROM EMP
• WHERE ENAME LIKE A
• This will display two tuples (rows) i.e. all
  those employees whose names begin with A and
  three columns.
• The is a wildcard operator.

16
Where clause Not In operator

• You can use the NOT IN operator as follows
• SELECT ENAME, JOB, SAL
• FROM EMP
• WHERE JOB NOT IN (SALESMAN, MANAGER)
• This will return those employees who are not
  salesmen or managers.
• You can also use the operator !

17
Where clause multiple conditions

• If you want to find all the clerks in department
  30 who earn less than 1000 you can say
• SELECT ENAME,JOB, SAL
• FROM EMP
• WHERE DEPTNO 30
• AND JOB CLERK
• AND SAL lt 1000
• This will return 1 row for James, a clerk who
  earns 950.

18
The Order By Clause Key principles

• Oracle will display your rows of data in an
  unordered fashion.
• We use the ORDER BY clause to order the rows that
  are retrieved.
• The ORDER BY clause should always be placed last
  in the query.
• The default ordering is ascending
• Numerics ascending by order value
• Dates chronological order
• Char alphabetically
• DESC is used to reverse the order

19
The Order By Clause Example 1

• List all departments in order of their location
• SELECT FROM dept
• ORDER BY loc
• DEPTNO DNAME LOC
• --------- -------------- -------------
• 40 OPERATIONS BOSTON
• 30 SALES CHICAGO
• 20 RESEARCH DALLAS
• 10 ACCOUNTING NEW YORK

20
The order by Clause example 2

• Show details of employees in department 10 with
  the earliest hire dates first
• SELECT empno, ename, hiredate
• FROM emp
• WHERE deptno 10
• ORDER BY hiredate
• EMPNO ENAME HIREDATE
• ----- ---------- ---------
• 7782 CLARK 09-JUN-81
• 7839 KING 17-NOV-81
• 7934 MILLER 23-JAN-82

21
The order by Clause example 3

• Show all employees in job order but within each
  job place in descending order of salary
• SELECT ename, job, sal, deptno
• FROM emp
• ORDER BY job, sal DESC
• 14 rows will be selected.

22
The order by Clause example 4

• SELECT ename,job,sal, comm, comm/sal as ratio,
• deptno From emp Where deptno 30
• Order by ratio
• ENAME JOB SAL COMM
  RATIO DEPTNO
• ---------- --------- --------- ---------
  --------- ---------
• TURNER SALESMAN 1500 0 0
  30
• ALLEN SALESMAN 1600 300 .1875
  30
• WARD SALESMAN 1250 500 .4
  30
• MARTIN SALESMAN 1250 1400 1.12
  30
• BLAKE MANAGER 2850
  30
• JAMES CLERK 950
  30
• (NB Nulls sort high)

23
Other useful key words in Select statements

• Some simple functions include
• Count Min Max Sum Avg
• Group By - Results can be grouped according to a
  common attribute value
• Having Clause - constrains the subgroups that
  appear

24
SQL DATA DEFINITION DDL

• We establish the existence of a base relation in
  SQL using the CREATE TABLE command.
• CREATE TABLE customer as follows
• CREATE TABLE customer
• (customer_id NUMBER (5) NOT NULL,
• name VARCHAR2 (10),
• house_number NUMBER (2),
• street VARCHAR2 (15),
• town VARCHAR2 (15))
• Each column has a name, data type  and column
  width.

25
Adding a primary key constraint

• ALTER TABLE lttable namegt
• ADD CONSTRAINT ltconstraint namegt
• PRIMARY KEY (ltcolumn-name1gt,ltcolumn-name2gt)

26
Adding a primary key

• ALTER TABLE customer
• ADD CONSTRAINT pk_customer_customer_id
• PRIMARY KEY (customer_id)
• N.B. Dont put data in the table before you
  have added all the constraints you require
• For compound primary key just use more than one
  attribute

27
DDL Creating Tables continued.

• Now suppose we create the following table
• CREATE TABLE account
• (accountNum NUMBER (6) NOT NULL,
• accountName VARCHAR2 (20),
• branchName VARCHAR2(20),
• dateOpened DATE,
• balance NUMBER (10,2))
• The Balance column is a decimal number with a
  maximum
• Column width of ten digits and a precision of two
  digits i.e.
• 10.251 is stored as 10.25.
• ALTER TABLE account
• ADD CONSTRAINT pk_account_accountNum
• PRIMARY KEY (accountNum)

28
Customer Account relationship
29
Resolved with link entity
30
Simple Constraints

• Customer and account has a many to many
  relationship
• We can resolve this with a new table to link
  them.
• CREATE TABLE customer_account
• (customer_id NUMBER (5) NOT NULL ,
• account_num NUMBER (6) NOT NULL))
• ALTER TABLE customer_account
• ADD CONSTRAINT pk_customer_account
• PRIMARY KEY (customer_id,account_num)

31
Simple Constraints Foreign keys

• The syntax to add a FOREIGN KEY constraint is
• ALTER TABLE lttable-namegt
• ADD CONSTRAINT ltconstraint-namegt
• FOREIGN KEY (column-name)
• REFERENCES ltparent-table-namegt(column-name)
• So in this case we need
• ALTER TABLE customer_account
• ADD CONSTRAINT fk_customer_account_customer_id
• REFERENCES customer(customer_id)
• What will the other ALTER TABLE statement look
  like ?

32
Referential Integrity (part of data validation)

• The previous statements set up referential
  integrity
• Referential integrity refers to the behaviour of
  child records when the parent record is modified
  or deleted.
• In the previous case it should not be possible to
  set up a customer_account record that does not
  refer to a customer record and an account record
  already entered.

33
Inserting Data Using SQL

• We use the SQL command INSERT to create rows.
• INSERT INTO customer
• VALUES(12345, DAWES, 21, BACK LANE,
  STAFFORD)
• Remember non-numerics require quotation
• marks around their values. These values have
• been entered into the Customer table in the
• order in which the columns appeared in the
• original create table statement.

34
Deleting/UPDATING Data Using SQL

• Use the DELETE command to remove data from a
  relation (i.e. table).
• DELETE FROM customer
• WHERE REFNO 12345
• Use the UPDATE command to alter the data held in
  the rows.
• i.e. Change the name in the account table from
  Dawes to Dalby.
• UPDATE customer
• SET name DALBY
• WHERE customer_id 12345

35
SQL Plus

• Must be used to create tables and relationships
  between them
• May be used for many other functions but it is
  often easier to use Oracle Forms to
• Populate tables
• Run queries

36
Forms 6i

• Used to provide a user interface for
• Entering and validating data
• Querying data
• Showing useful and related information on one
  screen
• Facilitating data input
• Providing useful navigation for users

37
SQL Plus

• Commands may be written using
• SQL command buffer
• Notepad editor
• Usually easier to use the editor where multiple
  commands may be easily edited.
• When the file is run containing the commands each
  command is loaded in turn to the buffer and
  executed

38
Useful SQL commands

• SAVE file1
• SAVE file1 REPLACE
• GET file1
• STA file1

39
Useful SQL commands

• /
• ed
• SELECT FROM TAB
• DESC TABLE_NAME

40
Datatypes

• CHAR
• VARCHAR2
• NUMBER(w,d)
• DATE

41
References

• http//cimic.rutgers.edu/holowcza/oracle/
• Patrick John J., (2002) SQL Fundamentals 2nd ed.,
  Prentice Hall