Materi Kuliah Fundamental 1

Materi Kuliah Fundamental 1 (Oracle)

Pertemuan 1 Installasi Software Database Oracle
Kebutuhan Sistem
- 512 MB physical random access memory (RAM)
- 1 GB swap space (atau dua kali ukuran RAM, jika ukuran
RAM <= 2 GB)
- 400 MB temporary directory (\temp)
- 1.5 GB disk space untuk software Database Oracle
- 1.5 GB disk space untuk database
PPT Preview | Download

Pertemuan 2 Membuat Database 
Membuat database dapat dilakukan dengan 2 cara yaitu :
- Menggunakan Database Configuration Assistant (DBCA)
- Menggunakan perintah SQL (CREATE DATABASE)

Jika suatu saat suatu database ingin dihapus, maka ada 2 cara yang bisa digunakan yaitu :
- Menggunakan Database Configuration Assistant (DBCA)
- Menggunakan Perintah SQL (Drop Database)
PPT Preview | Download

Pertemuan 3 Konfigurasi Database Instance
3 komponen untuk melakukan managemen database oracle 109 yaitu :
- Database instance
- Listener
- Management interface
PPT Preview | Download

Pertemuan 4 Retrivieng Data
Writing SQL Statements

- SQL statements are not case sensitive.
- SQL statements can be on one or more lines.
- Keywords cannot be abbreviated or split across lines.
- Clauses are usually placed on separate lines.
- Indents are used to enhance readability.
- In iSQL*Plus, SQL statements can optionally be terminated by a semicolon (;). Semicolons are required if you execute multiple SQL statements.
- In SQL*Plus, you are required to end each SQL statement with a semicolon (;).
PPT Preview | Download

Pertemuan 5 Restricting and Sorting Data
Character Strings and Dates

- Character strings and date values are enclosed in single quotation marks.
- Character values are case sensitive, and date values are format sensitive.
- The default date format is DD-MON-RR.
PPT Preview | Download

Pertemuan 6 Using Single-Row Fungctions to Customize Output
Single-Row Functions

- Manipulate data items
- Accept arguments and return one value
- Act on each row that is returned
- Return one result per row
- May modify the data type
- Can be nested
- Accept arguments that can be a column or an expression
PPT Preview | Download

Pertemuan 7 Reporting Aggregated Data Using the Group Function
Illegal Queries Using Group Functions

- You cannot use the WHERE clause to restrict groups.
- You use the HAVING clause to restrict groups.
- You cannot use group functions in the WHERE clause.
PPT Preview | Download

Pertemuan 8 Displaying Data from Multiple Tables
Types of Joins
Joins that are compliant with the SQL:1999 standard include the following:

- Cross joins
- Natural joins
- USING clause
- Full (or two-sided) outer joins
- Arbitrary join conditions for outer joins
PPT Preview | Download

Pertemuan 9 Using Subqueries to Solve Queries
Subquery Syntax

- The subquery (inner query) executes once before the main query (outer query).
- The result of the subquery is used by the main query.
PPT Preview | Download

Pertemuan 10 Using the Set Operators
Tables Used in This Lesson
The tables used in this lesson are:

- EMPLOYEES: Provides details regarding all current employees
- JOB_HISTORY: Records the details of the start date and end date of the former job, and the job identification number and department when an employee switches jobs
PPT Preview | Download

Pertemuan 11 Manipulating Data
Data Manipulation Language

- A DML statement is executed when you:
       Add new rows to a table
       Modify existing rows in a table
       Remove existing rows from a table
- A transaction consists of a collection of DML statements that form a logical unit of work.
PPT Preview | Download

Pertemuan 12 Using DDL Statements to Create and Manage Tables
Naming Rules
Table names and column names:

- Must begin with a letter
- Must be 1–30 characters long
- Must contain only A–Z, a–z, 0–9, _, $, and #
- Must not duplicate the name of another object owned by the same user
- Must not be an Oracle server–reserved word
PPT Preview | Download

Pertemuan 13 Creating Other Schema Objects
Rules for Performing DML Operations on a View
You cannot modify data in a view if it contains:

- Group functions
- A GROUP BY clause
- The DISTINCT keyword
- The pseudocolumn ROWNUM keyword
- Columns defined by expressions
PPT Preview | Download

Pertemuan 14 Managing Objects with Data Dictionary Views
Summary
In this lesson, you should have learned how to find information about your objects through the following dictionary views:

DICTIONARY
USER_OBJECTS
USER_TABLES
USER_TAB_COLUMNS
USER_CONSTRAINTS
USER_CONS_COLUMNS
USER_VIEWS
USER_SEQUENCES
USER_TAB_SYNONYMS
PPT Preview | Download