Oracle

კურსის აღწერა:
კურსი მოიცავს თეორიულ და ლაბორატორიულ მეცადინეობებს, რომლის განმავლობაში აუცილებელია მსმენელმა შეასრულოს ლაბორატორიული და საშინაო დავალებები. კურსი გრძელდება სამი თვე და ლექციების ხანგრძლივობა განსაზღვრულია კვირაში 3 საათი, 1 ლექცია 1,5 საათი, ჯამში 36 საათი.

1 კვრია

 

1, 2 ლექცია

 

Writing Basic SQL SELECT Statements

 

  • Basic SELECT Statement 
  • Selecting All Columns 
  • Selecting Specific Columns 
  • Writing SQL Statements 
  • Column Heading Defaults 
  • Arithmetic Expressions 
  • Using Arithmetic Operators 
  • Operator Precedence 
  • Using Parentheses 
  • Defining a Null Value 
  • Null Values in Arithmetic Expressions 
  • Defining a Column Alias 
  • Using Column Aliases 
  • Concatenation Operator 
  • Using the Concatenation Operator 
  • Literal Character Strings 
  • Using Literal Character Strings 
  • Duplicate Rows 
  • Eliminating Duplicate Rows 
  • Restricting and Sorting Data
  • Limiting Rows Using a Selection 
  • Limiting the Rows Selected 
  • Using the WHERE Clause 
  • Character Strings and Dates 
  • Comparison Conditions 
  • Using Comparison Conditions 
  • Other Comparison Conditions 
  • Using the BETWEEN Condition 
  • Using the IN Condition 
  • Using the LIKE Condition 
  • Using the NULL Conditions 
  • Logical Conditions 
  • Using the AND Operator 
  • Using the OR Operator 
  • Using the NOT Operator 
  • Rules of Precedence 
  • ORDER BY Clause 
  • Sorting in Descending Order 
  • Sorting by Column Alias 
  • Sorting by Multiple Columns 

Single-Row Functions

  •  
  • SQL Function 
  • Two Types of SQL Functions 
  • Single-Row Functions 
  • Single-Row Functions 
  • Character Functions 
  • Character Functions 
  • Case Manipulation Functions 
  • Using Case Manipulation Functions 
  • Character-Manipulation Functions 
  • Using the Character-Manipulation Functions 
  • Number Functions 
  • Using the ROUND Function 
  • Using the TRUNC Function 
  • Using the MOD Function 
  • Working with Dates 
  • Arithmetic with Dates 
  • Using Arithmetic Operators with Dates 
  • Date Functions 
  • Using Date Functions 
  • Practice 3, Part One: 
  • Conversion Functions 
  • Implicit Data Type Conversion 
  • Explicit Data Type Conversion 
  • Using the TO_CHAR Function with Dates 
  • Elements of the Date Format Model 
  • Using the TO_CHAR Function with Dates 
  • Using the TO_CHAR Function with Numbers 
  • Using the TO_NUMBER and TO_DATE Functions 
  • RR Date Format 
  • Example of RR Date Format 
  • Nesting Functions 
  • General Functions 
  • NVL Function 
  • Using the NVL Function 
  • Using the NVL2 Function 
  • Using the NULLIF Function 
  • Using the COALESCE Function 
  • Conditional Expressions 
  • The CASE Expression 
  • Using the CASE Expression 
  • The DECODE Function 
  • Using the DECODE Function 

 

პრაქტიკული მეცადინეობა.

2 კვირა

3, 4 ლექცია

 

Displaying Data from Multiple Tables

  • Obtaining Data from Multiple Tables 
  • Cartesian Products 
  • Generating a Cartesian Product 
  • Types of Joins 
  • Joining Tables Using Oracle Syntax 
  • What is an Equijoin? 
  • Retrieving Records with Equijoins 
  • Additional Search Conditions Using the AND Operator 
  • Qualifying Ambiguous Column Names 
  • Using Table Aliases 
  • Joining More than Two Tables 
  • Non-Equijoins 
  • Retrieving Records with Non-Equijoins 
  • Outer Joins Outer Joins Syntax 
  • Using Outer Joins 
  • Self Joins 
  • Joining a Table to Itself 
  • Joining Tables Using SQL: 1999 Syntax 
  • Creating Cross Joins 
  • Creating Natural Joins 
  • Retrieving Records with Natural Joins 
  • Creating Joins with the USING Clause 
  • Retrieving Records with the USING Clause 
  • Creating Joins with the ON Clause 
  • Retrieving Records with the ON Clause 
  • Creating Three-Way Joins with the ON Clause 
  • INNER Versus OUTER Joins 
  • LEFT OUTER JOIN 
  • RIGHT OUTER JOIN 
  • FULL OUTER JOIN 
  • Additional Conditions 
  •  

Aggregating Data Using Group Functions

  •  
  • What Are Group Functions? 
  • Types of Group Functions 
  • Group Functions Syntax 
  • Using the AVG and SUM Functions 
  • Using the MIN and MAX Functions 
  • Using the COUNT Function 
  • Using the DISTINCT Keyword 
  • Group Functions and Null Values 
  • Using the NVL Function with Group Functions 
  • Creating Groups of Data 
  • Creating Groups of Data: The GROUP BY Clause Syntax 
  • Using the GROUP BY Clause 
  • Grouping by More Than One Column 
  • Using the GROUP BY Clause on Multiple Columns 
  • Illegal Queries Using Group Functions 
  • Excluding Group Results 
  • Excluding Group Results: The HAVING Clause 
  • Using the HAVING Clause 
  • Nesting Group Functions 
     

Subqueries


Objectives 
Using a Subquery to Solve a Problem 
Subquery Syntax 
Using a Subquery 
Guidelines for Using Subqueries 
Types of Subqueries 
Single-Row Subqueries 
Executing Single-Row Subqueries 
Using Group Functions in a Subquery 
The HAVING Clause with Subqueries 
What is Wrong with this Statement? 
Will this Statement Return Rows? 
Multiple-Row Subqueries 
Using the ANY Operator in Multiple-Row Subqueries 
Using the ALL Operator in Multiple-Row Subqueries 
Null Values in a Subquery 

პრაქტიკული მეცადინეობა.

3 კვირა

5, 6 ლექცია

 

Manipulating Data

  •  
  • Data Manipulation Language 
  • Adding a New Row to a Table 
  • The INSERT Statement Syntax 8-5 
  • Inserting New Rows 
  • Inserting Rows with Null Values 
  • Inserting Special Values 
  • Inserting Specific Date Values 
  • Creating a Script 
  • Copying Rows from Another Table 
  • Changing Data in a Table 
  • The UPDATE Statement Syntax 
  • Updating Rows in a Table 
  • Updating Two Columns with a Subquery 
  • Updating Rows Based on Another Table 
  • Updating Rows: Integrity Constraint Error 
  • Removing a Row from a Table 
  • The DELETE Statement 
  • Deleting Rows from a Table 
  • Deleting Rows Based on Another Table 
  • Deleting Rows: Integrity Constraint Error 
  • Using a Subquery in an INSERT Statement 
  • Using the WITH CHECK OPTION Keyword on DML Statements 
  • Overview of the Explicit Default Feature 
  • Using Explicit Default Values 
  • The MERGE Statement 
  • The MERGE Statement Syntax 
  • Merging Rows 
  • Database Transactions 
  • Advantages of COMMIT and ROLLBACK Statements 
  • Controlling Transactions 
  • Rolling Back Changes to a Marker 
  • Implicit Transaction Processing 
  • State of the Data Before COMMIT or ROLLBACK 
  • State of the Data after COMMIT 
  • Committing Data 
  • State of the Data After ROLLBACK 
  • Statement-Level Rollback 
  • Read Consistency 
  • Implementation of Read Consistency 
  • Locking 
  • Implicit Locking 
  • Read Consistency Example 
     

Creating and Managing Tables

  •  
  • Database Objects 
  • Naming Rules 
  • The CREATE TABLE Statement 
  • Referencing Another User?s Tables 
  • The DEFAULT Option 
  • Creating Tables 
  • Tables in the Oracle Database 
  • Querying the Data Dictionary 9-10 
  • Data Types 
  • DateTime Data Types 
  • TIMESTAMP WITH TIME ZONE Data Type 
  • TIMESTAMP WITH LOCAL TIME Data Type 
  • INTERVAL YEAR TO MONTH Data Type 
  • INTERVAL DAY TO SECOND Data Type 
  • Creating a Table by Using a Subquery Syntax 
  • Creating a Table by Using a Subquery 
  • The ALTER TABLE Statement 
  • Adding a Column 
  • Modifying a Column 
  • Dropping a Column 
  • The SET UNUSED Option 
  • Dropping a Table 
  • Changing the Name of an Object 
  • Truncating a Table 
  • Adding Comments to a Table 

პრაქტიკული მეცადინეობა.

4 კვირა

7, 8 ლექცია

Including Constraints

  • What are Constraints? 
  • Constraint Guidelines 
  • Defining Constraints 
  • The NOT NULL Constraint 
  • The UNIQUE Constraint 
  • The PRIMARY KEY Constraint 
  • The FOREIGN KEY Constraint 
  • FOREIGN KEY Constraint Keywords 
  • The CHECK Constraint 
  • Adding a Constraint Syntax 
  • Adding a Constraint 
  • Dropping a Constraint 
  • Disabling Constraints 
  • Enabling Constraints 
  • Cascading Constraints 
  • Viewing Constraints 
  • Viewing the Columns Associated with Constraints 
     

Creating Views

  • Database Objects 
  • What is a View? 
  • Why use Views? 
  • Simple Views and Complex Views 
  • Creating a View 
  • Retrieving Data from a View 
  • Querying a View 
  • Modifying a View 
  • Creating a Complex View 
  • Rules for Performing DML Operations on a View 
  • Using the WITH CHECK OPTION Clause 
  • Denying DML Operations 
  • Removing a View 
  • Inline Views 
  • Top-N Analysis 
  • Performing Top-N Analysis 
  • Example of Top-N Analysis 
     
  • Other Database Objects
  • Database Objects 
  • What is a Sequence? 
  • The CREATE SEQUENCE Statement Syntax 
  • Creating a Sequence 
  • Confirming Sequences 
  • NEXTVAL and CURRVAL Pseudocolumns 
  • Using a Sequence 
  • Modifying a Sequence 
  • Guidelines for Modifying a Sequence 
  • Removing a Sequence 
  • What is an Index? 
  • How Are Indexes Created? 
  • Creating an Index 
  • When to Create an Index 
  • When Not to Create an Index 
  • Confirming Indexes 
  • Function-Based Indexes 
  • Removing an Index 
  • Creating and Removing Synonyms 
  • Controlling User Access
  • Objectives 
  • Controlling User Access 
  • Privileges 
  • System Privileges 
  • Creating Users 
  • User System Privileges 
  • Granting System Privileges 
  • What is a Role? 
  • Creating and Granting Privileges to a Role 
  • Changing Your Password 
  • Object Privileges 
  • Granting Object Privileges 
  • Using the WITH GRANT OPTION and PUBLIC Keywords 
  • Confirming Privileges Granted 
  • How to Revoke Object Privileges 
  • Revoking Object Privileges 
  • Database Links 

პრაქტიკული მეცადინეობა.   

5 კვირა

9,10 ლექცია

 

Using SET Operators

  •  
  • The SET Operators 
  • Tables Used in This Lesson 
  • The UNION Operator 
  • Using the UNION Operator 
  • The UNION ALL Operator 
  • Using the UNION ALL Operator 
  • The INTERSECT Operator 
  • Using the INTERSECT Operator 
  • The MINUS Operator 
  • SET Operator Guidelines 
  • The Oracle Server and SET Operators 
  • Matching the SELECT Statements 
  • Controlling the Order of Rows 
     

Enhancements to the GROUP BY Clause

  • Review of Group Functions 
  • Review of the GROUP BY Clause 
  • Review of the HAVING Clause 
  • GROUP BY with ROLLUP and CUBE Operators 
  • ROLLUP Operator 
  • ROLLUP Operator Example 
  • CUBE Operator 
  • CUBE Operator: Example 
  • GROUPING Function 
  • GROUPING Function: Example 
  • GROUPING SETS 
  • GROUPING SETS: Example 
  • Composite Columns 
  • Composite Columns: Example 
  • Concatenated Groupings 
  • Concatenated Groupings Example 
     

Advanced Subqueries

  • What Is a Subquery? 
  • Subqueries 
  • Using a Subquery 
  • Multiple-Column Subqueries 
  • Column Comparisons 
  • Pairwise Comparison Subquery 
  • Nonpairwise Comparison Subquery 
  • Using a Subquery in the FROM Clause 
  • Scalar Subquery Expressions 
  • Scalar Subqueries: Examples 
  • Correlated Subqueries 
  • Using Correlated Subqueries 
  • Using the EXISTS Operator 
  • Using the NOT EXISTS Operator 
  • Correlated UPDATE 
  • Correlated DELETE 
  • The WITH Clause 
  • WITH Clause: Example 

პრაქტიკული მეცადინეობა.   

6 კვირა

11,12
ლექცია

Hierarchical Retrieval

  • Sample Data from the EMPLOYEES Table 
  • Natural Tree Structure 
  • Hierarchical Queries 
  • Walking the Tree 
  • Walking the Tree: From the Bottom Up 
  • Walking the Tree: From the Top Down 
  • Ranking Rows with the LEVEL Pseudocolumn 
  • Formatting Hierarchical Reports Using LEVEL and LPAD 
  • Pruning Branches 
     

Extensions to DML and DDL Statements

  •  
  • Review of the INSERT Statement 
  • Review of the UPDATE Statement 
  • Overview of Multitable INSERT Statements 
  • Overview of Multitable INSERT Statements 
  • Types of Multitable INSERT Statements 
  • Multitable INSERT Statements 
  • Unconditional INSERT ALL 
  • Conditional INSERT ALL 
  • Conditional FIRST INSERT 
  • Pivoting INSERT 
  • External Tables 
  • Creating an External Table 
  • Example of Creating an External Table 
  • Querying External Tables 
  • CREATE INDEX with CREATE TABLE Statement 

 

პრაქტიკული მეცადინეობა.   

7 კვირა

 

13,14
ლექცია

Part I: Programming in PL/SQL

  • Introduction to PL/SQL 
  • What Is PL/SQL? 
  • The Origins of PL/SQL 
  • About PL/SQL Versions 
  • Resources for PL/SQL Developers 
     

Creating and Running PL/SQL Code

  • SQL*Plus 
  • Performing Essential PL/SQL Tasks 
  • Calling PL/SQL from Other Languages 
  • Language Fundamentals 
  • PL/SQL Block Structure 
  • The PL/SQL Character Set 
  • Identifiers 
  • Literals 
  • The Semicolon Delimiter 
  • Comments 
  • The PRAGMA Keyword 
  • Labels 
  • პრაქტიკული მეცადინეობა.   
  • 8 კვირა
  •  
  • 15,16
  • ლექცია
  • Part II: PL/SQL Program Structure
  • Conditional and Sequential Control 
  • IF Statements 
  • CASE Statements and Expressions 
  • The GOTO Statement 
  • The NULL Statement 
  • Iterative Processing with Loops 

Loop Basics

  • The Simple Loop 
  • The WHILE Loop 
  • The Numeric FOR Loop 
  • The Cursor FOR Loop 
  • Loop Labels 
  • Tips for Iterative Processing

 

Exception Handlers

  • Exception-Handling Concepts and Terminology 
  • Defining Exceptions 
  • Raising Exceptions 
  • Handling Exceptions 
  • Building an Effective Error Management Architecture 
  • Making the Most of PL/SQL Error Management 

პრაქტიკული მეცადინეობა.   

9 კვირა

 

17,18
ლექცია

Part III: PL/SQL Program Data

 

  • Working with Program Data 
  • Naming Your Program Data 
  • Overview of PL/SQL Datatypes 
  • Declaring Program Data 
  • Programmer-Defined Subtypes 
  • Conversion Between Datatypes 
     

Strings

  • String Datatypes 
  • Working with Strings 
  • String Function Quick Reference 
     

Numbers

  • Numeric Datatypes 
  • Number Conversions 
  • Numeric Functions 
     

Records


Records in PL/SQL 
 

Collections

 

  • Collections Overview 
  • Collection Methods (Built-Ins) 
  • Working with Collections 
  • Nested Table Multiset Operations 
  • Maintaining Schema-Level Collections 
     

Miscellaneous Datatypes

  •  
  • The BOOLEAN Datatype 
  • The RAW Datatype 
  • The UROWID and ROWID Datatypes 
  • The LOB Datatypes 
  • Working with LOBs 
  • Predefined Object Types 

პრაქტიკული მეცადინეობა.   

10 კვირა

19,20
ლექცია

Part IV: SQL in PL/SQL

  • DML and Transaction Management 
  • DML in PL/SQL 
  • Bulk DML with the FORALL Statement 
  • Transaction Management 
  • Autonomous Transactions 
     

Data Retrieval

 

  • Cursor Basics 
  • Working with Implicit Cursors 
  • Working with Explicit Cursors 
  • BULK COLLECT 
  • SELECT ... FOR UPDATE 
  • Cursor Variables and REF CURSORs 
  • Cursor Expressions 
     

Procedures, Functions, and Parameters

 

  • Procedures 
  • Functions 
  • Parameters 
  • Local Modules 
  • Module Overloading 
  • Forward Declarations Advanced Topics 
  • Go Forth and Modularize! 

 

პრაქტიკული მეცადინეობა.   

11 კვირა

21,22
ლექცია

Packages

 

  • Why Packages? 
  • Rules for Building Packages 
  • Rules for Calling Packaged Elements 
  • Working with Package Data 
  • When to Use Packages 
  • Packages and Object Types 
     

Triggers

  • DML Triggers 
  • DDL Triggers 
  • Database Event Triggers 
  • INSTEAD OF Triggers 
  • AFTER SUSPEND Triggers 
  • Maintaining Triggers 
     

 

პრაქტიკული მეცადინეობა.   

12 კვირა

 

23,24
ლექცია

 

SQL Workshop Overview 

PL/SQL Workshop Overview 

 

სასერტიფიკატო გამოცდა.   

ფასი: 1600 ლარი