دورة برمجة قواعد بيانات اوراكل SQL -PLSQL

يتم تدريس الكورس الكامل للجزء المتعلق بألاوراكل SQL 

وكذلك الجزء المتعلق بالاوراكل PL/SQl 

ويعد هذا الكورس من الكورسات المهمة للبداية في اي عمل برمجي يتعلق بالداتا بيز، ويعد من الأساسيات التي لا غنى عنها عند العمل بأي قطاع يعتمد على الا

1- Introduction to Oracle SQL Retrieving Data Using the SQL SELECT Statement

 Define projection, selection, and join terminology  Review the syntaxes for the basic SQL SELECT statements  Use Arithmetic and Concatenation operators in SQL statements Restricting and Sorting Data  Limit rows using a selection  Using the WHERE clause to retrieve specific rows  Using the comparison conditions in the WHERE clause  Use the LIKE condition to compare literal values  List the logical conditions AND, OR, NOT  Describe the rules of precedence for the conditions shown in this lesson  Sort rows with the ORDER BY clause Reporting Aggregated Data Using the Group Functions  Describe and categorize the group functions  Use the group functions  Utilize the DISTINCT keyword with the group functions  Describe how nulls are handled with the group functions  Create groups of data with the GROUP BY clause  Group data by more than one column  Exclude groups of data with the HAVING clause Using Sub queries to Solve Queries  List the syntax for sub queries in a SELECT statements WHERE clause  List the guidelines for using sub queries  Describe the types of sub queries  Execute single row sub queries and use the group functions in a sub query  Identify illegal statements with sub queries  Execute multiple row sub queries  Analyze how the ANY and ALL operators work in multiple rows sub queries  Explain how null values are handled in sub queries Using the SET Operators  Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows  Use the UNION ALL operator to return all rows from multiple tables  Describe the INTERSECT operator  Use the INTERSECT operator  Explain the MINUS operator  Use the MINUS operator  List the SET operator guidelines  Order results when using the UNION operator Manipulating Data  Write INSERT statements to add rows to a table  Copy rows from another table  Create UPDATE statements to change data in a table  Generate DELETE statements to remove rows from a table  Use a script to manipulate data  Save and discard changes to a table through transaction processing  Show how read consistency works  Describe the TRUNCATE statement Creating Other Schema Objects  List the main database objects and describe the naming rules for database objects (Views, Indexes….)  Display the basic syntax for creating a table and show the DEFAULT option  Explain the different types of constraints  Show resulting exceptions when constraints are violated with DML statements  Create a table with a sub query and remove a table with the DROP statement  Describe the ALTERTABLE functionality  Rename a table Managing Objects with Data Dictionary Views  Describe the structure of each of the dictionary views  List the purpose of each of the dictionary views  Write queries that retrieve information from the dictionary views on the schema objects Using Single Row Functions to Customize Reports  Show the differences between single row and multiple row SQL functions  Categorize the character functions into case manipulation and character manipulation types  Use the character manipulation functions in the SELECT and WHERE clauses  Explain and use the DATE and numeric functions  Use the SYSDATE function to retrieve the current date in the default format  Introduce the DUAL table as a means to view function results  List the rules for applying the arithmetic operators on dates  Use the arithmetic operators with dates in the SELECT clause Displaying Data from Multiple Tables  Show the join tables syntax using SQL 99 syntax  Use table aliases to write shorter code and explicitly identify columns from multiple tables  Issue a SQL CROSS JOIN statement to produce a Cartesian product  Use the NATURAL JOIN clause to retrieve data from tables with the same named columns  Create a join with the USING clause to identify specific columns between tables  Create a three way join with the ON clause to retrieve information from 3 tables  List the types of outer joins LEFT, RIGHT, and FULL  Add additional conditions when joining tables with the AND clause Controlling User Access  Controlling user access  System versus objects privileges  Creating and granting privileges to a role  Granting and revoking object privileges  Changing your password  Using Database Links Manage Schema Objects  Creating Index Organized Tables  Creating Function based indexes  Dropping Columns  Altering the structure of tables and adding constraints Generating Reports by Grouping Related Data  Overview of GROUP BY and Having Clause  Determine subtotal groups using GROUPING Functions  Compute multiple groupings with GROUPING SETS  Define levels of aggregation with Composite Columns  Create combinations with Concatenated Groupings Using DDL Statements to Create and Manage Tables  List the main database objects and describe the naming rules for database objects  Introduce the schema concept  Display the basic syntax for creating a table and show the DEFAULT option  Explain the different types of constraints  Create a table with a sub query  Describe the ALTER TABLE functionality  Remove a table with the DROP statement and Rename a table

2 - Oracle: Program with PL/SQL PL/SQL Basics  PL/SQL Block  Anonymous Block Structure  Named Block Structure  Executing Blocks  Calling PL/SQL Functions  Executing PL/SQL Blocks and Functions Declaring Variables  Variable Usage  Variable Data Types  Variable Naming  Variable Assignment  Variable Display  Complex Variable Types  More Information  Variable Declaration, Initialization and Display Within the Block  Conditional Processing o The IF Statement o The CASE Statement  Iterative Processing o The LOOP Statement o The WHILE Statement o The FOR Statement Handling Exceptions  Overview of Exceptions  Causing System Generated Exceptions  Handling System Generated Exceptions  Identifying System Generated Exceptions  OTHERS Exception Handler  User Defined Exceptions Use of SQL in PL/SQL  Implicit Cursors  %TYPE and %ROWTYPE Attributes  EXECUTE IMMEDIATE statement  Cursors Attributes  Implicit Cursor  Explicit Cursors  Explicit Cursor  Cursor FOR loop Database Triggers  Purpose of Triggers  Invocation of Triggers  Coding Triggers  Validation Trigger  Modifying Triggers  Viewing Triggers  Enabling/Disabling Triggers  Trigger Errors