oracle & sql introduction. database concepts revision db? dbms? db application? application...

Oracle & SQL Oracle & SQL IntroductionIntroduction

Database Concepts Revision

DB?

DBMS?

DB Application?

Application Programs?

DBS?

Examples of DBS?

Examples of DBMS?

2Oracle & SQL

Database Design Revision

Steps in building a database for an application:

1. Analysis: Understand real-world domain being captured.

2. Design: Specify it using a database conceptual model (ER). Translate specification to model of DBMS (Relational or tables).

3. Implementation (using DBMS): Create schema using DBMS commands (DDL). Load data (DML).

3Oracle & SQL

Relational Database

A relational database is a DB that stores the data in the form of tables/relations with rows and columns. Every table shares at least one column with another table (Relationships).

A table/Relation is the category of data, like Staff. The columns are information about the category (Attributes), like name or address and the rows are the actual data or records.

Oracle & SQL 4

Oracle

Oracle is a powerful relational database management system (RDBMS) that offers a large feature set.

Oracle is widely regarded as one of the popular full-featured database systems on the market today.

In almost all relational databases, data is accessed through Structured Query Language (SQL), and Oracle is one of them.

SQL is nonprocedural language that represents a combination of DDL and DML.

Oracle & SQL 5

Data Definition Language (DDL)

DDL is a descriptive language for defining the database schema.

Some of the main SQL-DDL commands are:

CREATE TABLE

ALTER TABLE

DROP TABLE

6Oracle & SQL

Data Manipulation Language (DML)

DML is a language for retrieving and updating (insert, delete, & modify) the data in the DB.

The main SQL-DML commands are:

SELECT

INSERT INTO

UPDATE

DELETE FROM

7Oracle & SQL

Simple Queries

Syntax:

SELECT { * | column | column_expression [,…] }

FROM table_name;

Staff

8Oracle & SQL

Simple QueriesRetrieve all columns & rows

Example: Retrieve all staff information.

SELECT sno, fname, lname, position, sex, dob, salary, bno

FROM staff;

OR

SELECT *

FROM staff;

9Oracle & SQL

Simple QueriesRetrieve specific columns & all rows

Example: List salaries of all staff, showing only the staff number, the full and last name, and salary.

SELECT sno, fname, lname, salary

FROM staff;

10Oracle & SQL

Simple QueriesRow selection (WHERE clause)

Syntax:

SELECT { * | column | column_expression [,…] }

FROM table_name

WHERE condition;

Comparison operators: = , <, >, <=, >=, <>

11Oracle & SQL

Example: List all staff with a salary greater than 10,000.

SELECT sno, fname, lname, salary

FROM staff

WHERE salary > 10000;

12Oracle & SQL

Simple QueriesRow selection (WHERE clause)

DemoDemo

Oracle 10g (SQL worksheet)

Oracle & SQL 14

From Start menu choose:

All Programs Oracle-OraClient10g_home1 Application Development SQLPlus Worksheet.

Oracle & SQL 15

A DOS window will open (Don't close it) and a login window.

In the login window type S then your serial# then _ then your lecture section# as the Username & password.

In the Service field type O10G then press OK.

Oracle & SQL 16

The worksheet window will open. Make sure that “Connected” word is written in the lower panel.

Oracle & SQL 17

Disconnect icon: connect/disconnect from server.

Execute icon: execute a SQL statement (as shown below).

Oracle & SQL 18

Command History icon: show the SQL Command executed during this session.

Previous/Next Command icon: show the previous/Next SQL statement (Undo/ Redo).

Try your account and execute a simple query.