oracle & sql introduction. database concepts revision db? dbms? db application? application...
TRANSCRIPT
![Page 1: Oracle & SQL Introduction. Database Concepts Revision DB? DBMS? DB Application? Application Programs? DBS? Examples of DBS? Examples of DBMS? 2Oracle](https://reader035.vdocuments.us/reader035/viewer/2022062321/56649f1f5503460f94c37151/html5/thumbnails/1.jpg)
Oracle & SQL Oracle & SQL IntroductionIntroduction
![Page 2: Oracle & SQL Introduction. Database Concepts Revision DB? DBMS? DB Application? Application Programs? DBS? Examples of DBS? Examples of DBMS? 2Oracle](https://reader035.vdocuments.us/reader035/viewer/2022062321/56649f1f5503460f94c37151/html5/thumbnails/2.jpg)
Database Concepts Revision
DB?
DBMS?
DB Application?
Application Programs?
DBS?
Examples of DBS?
Examples of DBMS?
2Oracle & SQL
![Page 3: Oracle & SQL Introduction. Database Concepts Revision DB? DBMS? DB Application? Application Programs? DBS? Examples of DBS? Examples of DBMS? 2Oracle](https://reader035.vdocuments.us/reader035/viewer/2022062321/56649f1f5503460f94c37151/html5/thumbnails/3.jpg)
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
![Page 4: Oracle & SQL Introduction. Database Concepts Revision DB? DBMS? DB Application? Application Programs? DBS? Examples of DBS? Examples of DBMS? 2Oracle](https://reader035.vdocuments.us/reader035/viewer/2022062321/56649f1f5503460f94c37151/html5/thumbnails/4.jpg)
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
![Page 5: Oracle & SQL Introduction. Database Concepts Revision DB? DBMS? DB Application? Application Programs? DBS? Examples of DBS? Examples of DBMS? 2Oracle](https://reader035.vdocuments.us/reader035/viewer/2022062321/56649f1f5503460f94c37151/html5/thumbnails/5.jpg)
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
![Page 6: Oracle & SQL Introduction. Database Concepts Revision DB? DBMS? DB Application? Application Programs? DBS? Examples of DBS? Examples of DBMS? 2Oracle](https://reader035.vdocuments.us/reader035/viewer/2022062321/56649f1f5503460f94c37151/html5/thumbnails/6.jpg)
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
![Page 7: Oracle & SQL Introduction. Database Concepts Revision DB? DBMS? DB Application? Application Programs? DBS? Examples of DBS? Examples of DBMS? 2Oracle](https://reader035.vdocuments.us/reader035/viewer/2022062321/56649f1f5503460f94c37151/html5/thumbnails/7.jpg)
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
![Page 8: Oracle & SQL Introduction. Database Concepts Revision DB? DBMS? DB Application? Application Programs? DBS? Examples of DBS? Examples of DBMS? 2Oracle](https://reader035.vdocuments.us/reader035/viewer/2022062321/56649f1f5503460f94c37151/html5/thumbnails/8.jpg)
Simple Queries
Syntax:
SELECT { * | column | column_expression [,…] }
FROM table_name;
Staff
8Oracle & SQL
![Page 9: Oracle & SQL Introduction. Database Concepts Revision DB? DBMS? DB Application? Application Programs? DBS? Examples of DBS? Examples of DBMS? 2Oracle](https://reader035.vdocuments.us/reader035/viewer/2022062321/56649f1f5503460f94c37151/html5/thumbnails/9.jpg)
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
![Page 10: Oracle & SQL Introduction. Database Concepts Revision DB? DBMS? DB Application? Application Programs? DBS? Examples of DBS? Examples of DBMS? 2Oracle](https://reader035.vdocuments.us/reader035/viewer/2022062321/56649f1f5503460f94c37151/html5/thumbnails/10.jpg)
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
![Page 11: Oracle & SQL Introduction. Database Concepts Revision DB? DBMS? DB Application? Application Programs? DBS? Examples of DBS? Examples of DBMS? 2Oracle](https://reader035.vdocuments.us/reader035/viewer/2022062321/56649f1f5503460f94c37151/html5/thumbnails/11.jpg)
Simple QueriesRow selection (WHERE clause)
Syntax:
SELECT { * | column | column_expression [,…] }
FROM table_name
WHERE condition;
Comparison operators: = , <, >, <=, >=, <>
11Oracle & SQL
![Page 12: Oracle & SQL Introduction. Database Concepts Revision DB? DBMS? DB Application? Application Programs? DBS? Examples of DBS? Examples of DBMS? 2Oracle](https://reader035.vdocuments.us/reader035/viewer/2022062321/56649f1f5503460f94c37151/html5/thumbnails/12.jpg)
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)
![Page 13: Oracle & SQL Introduction. Database Concepts Revision DB? DBMS? DB Application? Application Programs? DBS? Examples of DBS? Examples of DBMS? 2Oracle](https://reader035.vdocuments.us/reader035/viewer/2022062321/56649f1f5503460f94c37151/html5/thumbnails/13.jpg)
DemoDemo
Oracle 10g (SQL worksheet)
![Page 14: Oracle & SQL Introduction. Database Concepts Revision DB? DBMS? DB Application? Application Programs? DBS? Examples of DBS? Examples of DBMS? 2Oracle](https://reader035.vdocuments.us/reader035/viewer/2022062321/56649f1f5503460f94c37151/html5/thumbnails/14.jpg)
Oracle & SQL 14
From Start menu choose:
All Programs Oracle-OraClient10g_home1 Application Development SQLPlus Worksheet.
![Page 15: Oracle & SQL Introduction. Database Concepts Revision DB? DBMS? DB Application? Application Programs? DBS? Examples of DBS? Examples of DBMS? 2Oracle](https://reader035.vdocuments.us/reader035/viewer/2022062321/56649f1f5503460f94c37151/html5/thumbnails/15.jpg)
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.
![Page 16: Oracle & SQL Introduction. Database Concepts Revision DB? DBMS? DB Application? Application Programs? DBS? Examples of DBS? Examples of DBMS? 2Oracle](https://reader035.vdocuments.us/reader035/viewer/2022062321/56649f1f5503460f94c37151/html5/thumbnails/16.jpg)
Oracle & SQL 16
The worksheet window will open. Make sure that “Connected” word is written in the lower panel.
![Page 17: Oracle & SQL Introduction. Database Concepts Revision DB? DBMS? DB Application? Application Programs? DBS? Examples of DBS? Examples of DBMS? 2Oracle](https://reader035.vdocuments.us/reader035/viewer/2022062321/56649f1f5503460f94c37151/html5/thumbnails/17.jpg)
Oracle & SQL 17
Disconnect icon: connect/disconnect from server.
Execute icon: execute a SQL statement (as shown below).
![Page 18: Oracle & SQL Introduction. Database Concepts Revision DB? DBMS? DB Application? Application Programs? DBS? Examples of DBS? Examples of DBMS? 2Oracle](https://reader035.vdocuments.us/reader035/viewer/2022062321/56649f1f5503460f94c37151/html5/thumbnails/18.jpg)
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).
![Page 19: Oracle & SQL Introduction. Database Concepts Revision DB? DBMS? DB Application? Application Programs? DBS? Examples of DBS? Examples of DBMS? 2Oracle](https://reader035.vdocuments.us/reader035/viewer/2022062321/56649f1f5503460f94c37151/html5/thumbnails/19.jpg)
Try your account and execute a simple query.