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

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

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

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

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

1. Define projection, selection, and join terminology.
2. Review the syntaxes for the basic SQL SELECT statements.
3. Use Arithmetic and Concatenation operators in SQL statements Restricting and Sorting Data Limit rows using a selection.
4. Using the WHERE clause to retrieve specific rows.
5. Using the comparison conditions in the WHERE clause
6. Use the LIKE condition to compare literal values
7. List the logical conditions AND, OR, NOT
8. Describe the rules of precedence for the conditions shown in this lesson
9. Sort rows with the ORDER BY clause Reporting Aggregated Data Using the Group Functions
10. Describe and categorize the group functions
11. Use the group functions
12. Utilize the DISTINCT keyword with the group functions
13. Describe how nulls are handled with the group functions
14. Create groups of data with the GROUP BY clause
15. Group data by more than one column
16. Exclude groups of data with the HAVING clause Using Sub queries to Solve Queries
17. List the syntax for sub queries in a SELECT statements WHERE clause
18. List the guidelines for using sub queries
19. Describe the types of sub queries
20. Execute single row sub queries and use the group functions in a sub query
21. Identify illegal statements with sub queries
22. Execute multiple row sub queries
23. Analyze how the ANY and ALL operators work in multiple rows sub queries
24. Explain how null values are handled in sub queries Using the SET Operators
25. Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows
26. Use the UNION ALL operator to return all rows from multiple tables
27. Describe the INTERSECT operator
28. Use the INTERSECT operator
29. Explain the MINUS operator
30. Use the MINUS operator
31. ist the SET operator guidelines
32. Order results when using the UNION operator Manipulating Data
33. Write INSERT statements to add rows to a table
34. Copy rows from another table
35. Create UPDATE statements to change data in a table
36. Generate DELETE statements to remove rows from a table
37. Use a script to manipulate data
38. Save and discard changes to a table through transaction processing
39. Show how read consistency works
40. Describe the TRUNCATE statement Creating Other Schema Objects
41. List the main database objects and describe the naming rules for database objects (Views, Indexes….)
42. Display the basic syntax for creating a table and show the DEFAULT option
43. Explain the different types of constraints
44. Show resulting exceptions when constraints are violated with DML statements
45. Create a table with a sub query and remove a table with the DROP statement
46. Describe the ALTERTABLE functionality
47. Rename a table Managing Objects with Data Dictionary Views
48. Describe the structure of each of the dictionary views
49. List the purpose of each of the dictionary views
50. Write queries that retrieve information from the dictionary views on the schema objects Using Single Row Functions to Customize Reports
51. Show the differences between single row and multiple row SQL functions
52. Categorize the character functions into case manipulation and character manipulation types
53. Use the character manipulation functions in the SELECT and WHERE clauses
54. Explain and use the DATE and numeric functions
55. Use the SYSDATE function to retrieve the current date in the default format
56. Introduce the DUAL table as a means to view function results
57. List the rules for applying the arithmetic operators on dates
58. Use the arithmetic operators with dates in the SELECT clause Displaying Data from Multiple Tables
59. Show the join tables syntax using SQL 99 syntax
60. Use table aliases to write shorter code and explicitly identify columns from multiple tables
61. ssue a SQL CROSS JOIN statement to produce a Cartesian product
62. Use the NATURAL JOIN clause to retrieve data from tables with the same named columns
63. Create a join with the USING clause to identify specific columns between tables
64. Create a three way join with the ON clause to retrieve information from 3 tables
65. List the types of outer joins LEFT, RIGHT, and FULL
66. Add additional conditions when joining tables with the AND clause Controlling User Access
67. Controlling user access
68. System versus objects privileges
69. Creating and granting privileges to a role
70. Granting and revoking object privileges
71. Changing your password
72. Using Database Links Manage Schema Objects
73. Creating Index Organized Tables
74. Creating Function based indexes
75. Dropping Columns
76. Altering the structure of tables and adding constraints Generating Reports by Grouping Related Data
77. Overview of GROUP BY and Having Clause
78. Determine subtotal groups using GROUPING Functions
79. Compute multiple groupings with GROUPING SETS
80. Define levels of aggregation with Composite Columns
81. Create combinations with Concatenated Groupings Using DDL Statements to Create and Manage Tables
82. List the main database objects and describe the naming rules for database objects
83. Introduce the schema concept
84. Display the basic syntax for creating a table and show the DEFAULT option
85. Explain the different types of constraints
86. Create a table with a sub query
87. Describe the ALTER TABLE functionality
88. Remove a table with the DROP statement and Rename a table

2 - Oracle: Program with PL/SQL PL/SQL Basics

1. PL/SQL Block
2. Anonymous Block Structure
3. Named Block Structure
4. Executing Blocks
5. Calling PL/SQL Functions
6. Executing PL/SQL Blocks and Functions Declaring Variables
7. Variable Usage
8. Variable Data Types
9. Variable Naming
10. Variable Assignment
11. Variable Display
12. Complex Variable Types
13. More Information
14. Variable Declaration, Initialization and Display Within the Block
15. Conditional Processing o The IF Statement o The CASE Statement
16. Iterative Processing o The LOOP Statement o The WHILE Statement o The FOR Statement Handling Exceptions
17. Overview of Exceptions
18. Causing System Generated Exceptions
19. Handling System Generated Exceptions
20. Identifying System Generated Exceptions
21. OTHERS Exception Handler
22. User Defined Exceptions Use of SQL in PL/SQL
23. Implicit Cursors
24. %TYPE and %ROWTYPE Attributes
25. EXECUTE IMMEDIATE statement
26. Cursors Attributes
27. Implicit Cursor
28. Explicit Cursors
29. Explicit Cursor
30. Cursor FOR loop Database Triggers
31. Purpose of Triggers
32. Invocation of Triggers
33. Coding Triggers
34. Validation Trigger
35. Modifying Triggers
36. Viewing Triggers
37. Enabling/Disabling Triggers
38. Trigger Errors