The ‘Oracle Database 12c SQL Certified Associate 1Z0-071’ course will teach you all the fundamentals of Oracle SQL. In this course, you will learn how to manipulate data and how to retrieve data by using subqueries.
The course teach you all the fundamentals of SQL and helps you to pass Oracle Database SQL 1Z0-071 exam and Oracle Database 12c: SQL Fundamentals 1Z0-061 exam.The course is usually available for INR 2,799 on Udemy but you can click on the link and get the ‘Oracle Database 12c SQL Certified Associate 1Z0-071’ for INR 499.
Who all can opt for this course?
- Students who aspire to become Oracle Database 12c experts
- Students attending Oracle University who wish to earn an Oracle database certification
- New IT graduates who require solid SQL and database skills
- New and inexperienced programmers who need to become Oracle certified in order to land better employment
Course Highlights
| Key Highlights | Details |
|---|---|
| Registration Link | Apply Now! |
| Price | INR 499 ( |
| Duration | 20 Hours |
| Rating | 4.5/5 |
| Student Enrollment | 29,032 students |
| Instructor | Khaled Alkhudari |
| Topics Covered | SQL, System Development Life Cycle, Data Models, ERM |
| Course Level | Beginner |
| Total Student Reviews | 7,211 |
Learning Outcomes
- Oracle Database 12c: Introduction to SQL, Ed 2.
- You will be well equipped to pass tests such as 1Z0-071 Oracle Database exam and Oracle Database 12c 1Z0-061.
- Extremely effective introduction to the Oracle database and RDBMS.
- Architecture for Oracle Database 12c.
- Detailed download and installation of Oracle Database 12c.
- Making a connection to the database.
- Using the select statement to retrieve data and restricting and sorting the data.
- Replacement variables.
- Customizing Output using Single-Row Functions.
- Using the Group Functions to Report Aggregated Data.
- Utilising joins to display data from many tables.
- Solving Queries using Subqueries.
- Making use of the Set Operators.
- Using DML Statments to manage tables ( Insert, Update, Delete ).
- Table creation using the Data Definition Language.
- Views of a data dictionary.
- Creating indexes, synonyms, and sequences.
- Detailing the creation of views.
- Schema object management.
- Data retrieval using subqueries.
- Data manipulation with subqueries.
- Detailed user access control.
- Data manipulation.
- Data Management between Time Zones.
- Test details, exam facts, and exam preparation.
- See the complete curriculum for more details.
Course Content
| S.No. | Module (Duration) | Topics |
|---|---|---|
| 1. | Introduction (31 minutes) | Course Agenda |
| System Development Life Cycle & Database Definition | ||
| What is a Table? | ||
| Data Models & ERM | ||
| About Primary key & foreign key | ||
| SQL and Types of SQL Statements | ||
| Oracle Database 12c Architecture | ||
| HR Schema in this Course | ||
| 2. | Live SQL & Download/ Install (22 minutes) | Oracle Live SQL / No Installation for anything |
| Oracle Live SQL limitation | ||
| before you install oracle DB | ||
| Downloading Oracle DB 12c | ||
| Installing Oracle DB 12c | ||
| About Oracle 18c express edition | ||
| About oracle 11g express edition | ||
| 3. | Connecting to the Database (30 minutes) | Connecting SYS using SQL plus & SQL Developer |
| if you do not remember the sys password | ||
| Unlock HR Account Part 1 | ||
| Unlock HR Account Part 2 | ||
| ORA-01033 oracle initialization or shutdown in progress 12c | ||
| ORA-65054: Cannot open a pluggable database in the desired mode | ||
| Creating new pluggable database contains HR user | ||
| 4. | Retrieving data using the select statement (34 minutes) | Navigate HR schema |
| Capabilities of SQL SELECT Statements | ||
| Arithmetic expressions and NULL values | ||
| Column Alias, Concatenation , Distinct and DESCRIBE | ||
| Important Notes in Select Statement | ||
| 5. | Restricting and Sorting Data (54 minutes) | The WHERE Clause & Comparison Operators |
| Using Between and / IN / Like Operators | ||
| Using IS NULL / NOT / Not equal Operators | ||
| Logical Operators (AND/ OR/ NOT) | ||
| Order by Clause | ||
| The FETCH Clause | ||
| 6. | Substitution Variables (22 minutes) | What is a Substitution Variables (&) |
| DEFINE / UNDEFINE | ||
| ACCEPT / PROMPT | ||
| Double-Ampersand / SET VERIFY / SET DEFINE | ||
| 7. | Using Single-Row Functions to Customize Output (01 hour 09 minutes) | Single Row Function Introduction |
| Character functions (Upper, Lower, Initcap ) | ||
| Character functions (concat, substr, length ) | ||
| Character functions ( instr ) | ||
| Character functions ( lpad, rpad , replace, trim) | ||
| Number functions ( Round, Trunc, MOD ) | ||
| Date Functions ( Sysdate) | ||
| Date Functions ( months_between, add_months, next_day, last_day) | ||
| Date Functions ( round , trunc ) | ||
| Nesting Functions | ||
| 8. | Using Conversion Functions and Conditional Expressions (01 hour 05 minutes) | Implicit Data Conversion VS Explicit |
| TO_CHAR with dates | ||
| TO_CHAR with numbers | ||
| TO_NUMBER , TO_DATE Functions | ||
| nvl , nvl2 , nullif and coalesce Functions | ||
| The CASE Function | ||
| The DECODE Function | ||
| 9. | Reporting Aggregated Data Using the Group Functions (25 minutes) | Group functions Introduction |
| Group functions (sum, count, max, min, avg & more) | ||
| Group by Clause, Having Clause | ||
| 10. | Displaying Data from multiple tables using joins (01 hour 19 minutes) | Before You Start, important notes |
| HR Tables Diagram, Joins Clarification | ||
| What is Cartesian product? | ||
| Old Joins: Equijoin | ||
| Old Joins: nonEquijoins | ||
| Old Joins: outer join | ||
| Old Joins: Self Join and More Practices | ||
| 1999 Syntax: Cross Join (Cartesian product ) | ||
| 1999 Syntax: Natural Join | ||
| 1999 Syntax: USING Clause | ||
| 1999 Syntax: ON Clause | ||
| 1999 Syntax: Left/Right/full Outer Join | ||
| 11. | Using Subqueries to Solve Queries (23 minutes) | Single row Subqueries |
| Multiple rows Subqueries | ||
| NULL values and Subqueries | ||
| Exists and not Exists | ||
| 12. | Using the Set Operators (16 minutes) | Overview (Union, union all, intersect, minus ) |
| Practice (Union, union all, intersect, minus ) | ||
| 13. | Managing tables using DML Statments (01 hour 22 minutes) | Introduction to DML |
| Insert Statement | ||
| Inserting common errors | ||
| Update Statement | ||
| Delete Statement | ||
| What is Database Transactions? | ||
| About Commit & Rollback | ||
| Practice ( Commit and rollback ) | ||
| SAVEPOINT | ||
| ROW Lock | ||
| FOR UPDATE Clause | ||
| 14. | Introduction to Data Definition Language (01 hour 51 minutes) | DDL & Naming Rules |
| Data Types Part 1 | ||
| Data Types Part 2 | ||
| Creating Tables (without constraints) | ||
| Types of constraints and why we use it? | ||
| Creating Tables ( Column Level Constraints ) | ||
| Creating Tables ( Table Level Constraints ) | ||
| Constraints Guidelines | ||
| ON DELETE cascade / ON DELETE set null | ||
| Create Table AS subquery | ||
| Alter Table/ Add Columns | ||
| Alter Table/ Modify Columns | ||
| Alter Table/ Drop Columns | ||
| Alter Table/ Set Unused | ||
| ALTER TABLE READ ONLY / READ write | ||
| Drop Table | ||
| Rename Column / Rename Table | ||
| 15. | Tips you Should know (29 minutes) | Tips you Should know Part 1 |
| Tips you Should know Part 2 | ||
| Tips you Should know Part 3 | ||
| Tips you Should know Part 4 | ||
| Tips you Should know Part 5 | ||
| Tips you Should know Part 6 | ||
| 16. | Exam 1Z0-061 / Oracle Database 12c: SQL Fundamentals (10 minutes) | About Exam 1Z0-061 / Visiting Oracle Website |
| My recommendations | ||
| 1Z0-061 retires on 30, Nov 2019 | ||
| 17. | Introduction to Data Dictionary Views (46 minutes) | What is Data Dictionary Views? |
| What is DICTIONARY? | ||
| USER_OBJECTS / ALL_OBJECTS | ||
| USER_TABLES / ALL_TABLES / USER_TAB_COLUMNS | ||
| USER_CONSTRAINTS & USER_CONS_COLUMNS Part 1 | ||
| USER_CONSTRAINTS & USER_CONS_COLUMNS Part 2 | ||
| COMMENT ON TABLE / COLUMN | ||
| 18. | Creating sequences, synonyms, and indexes (01 hour 00 minutes) | Sequences Part 1 |
| Sequences Part 2 | ||
| Sequences Part 3 | ||
| Sequences Part 4 | ||
| Creating Synonyms | ||
| Creating Indexes Part 1 | ||
| Creating Indexes Part 2 | ||
| 19. | Creating Views (27 minutes) | What is a View? / What is the benefits of Views? |
| Creating Simple Views | ||
| Creating Complex Views | ||
| With READ ONLY / With check option / Force view | ||
| 20. | Managing Schema Objects (01 hour 09 minutes) | Adding constraints / dropping constraints |
| Rename Column / Rename Constraint | ||
| Enable / Disable Constraints | ||
| Understanding DEFERRABLE Constraints | ||
| DEFERRABLE Constraints Exercises | ||
| GLOBAL TEMPORARY TABLE | ||
| About SQL*Loader | ||
| External Tables Part 1 | ||
| External Tables Part 2 | ||
| 21. | Retrieving Data By Using Subqueries (40 minutes) | Using the subquery as a source table |
| Pairwise/ Non Pairwise Subqueries | ||
| Scalar Subquery / Correlated Subquery | ||
| Reminder of Exists / Not Exists | ||
| WITH Clause | ||
| 22. | Manipulating Data By Using Subqueries (16 minutes) | Inserting/ Updating /Deleting Using a subquery as a target |
| Correlated Update / Correlated Delete | ||
| 23. | Controlling User Access (01 hour 01 minutes) | Database Security / System Privileges & Roles |
| Database Security / Object Privileges | ||
| System Privileges & Privileges Practice 1 | ||
| System Privileges & Privileges Practice 2 | ||
| Creating ROLE Practice | ||
| With Grant Option | ||
| Drop user Statement | ||
| 24. | Manipulating Data (47 minutes) | What is Data Warehouse? |
| Explicit default value in insert & update statement | ||
| Copy rows from another table | ||
| INSERT ALL statement / INSERT FIRST | ||
| Another “INSERT ALL” Example & Notes | ||
| Creating Matrix Report Using PIVOT | ||
| The Merge Statement | ||
| FLASHBACK Table & System change Number ( SCN) | ||
| 25. | Managing Data in Different Time Zones (34 minutes) | THE INTERVAL ( YEAR TO MONTH / day TO second) |
| to_yminterval / to_dsinterval | ||
| Extract function | ||
| timestamp / timestamp with time zone | ||
| V$TIMEZONE_NAMES and some functions | ||
| timestamp with local time zone | ||
| TZ_OFFSET / FROM_TZ / TO_TIMESTAMP | ||
| 26. | Exam 1Z0-071 for SQL Certified Associate (06 minutes) | About the Exam 1Z0-071 |
| EXAM 1z0-071 Sample Questions | ||
| 27. | Bonus Section (01 hour 07 minutes) | Using SELECT statement to create dynamic scripts |
| Installing oracle 11g express edition | ||
| Rownum and rowid | ||
| About PL/SQL | ||
| Understanding NULLS when used with ( IN/ NOT IN ) | ||
| permanent solution for ORA-01033 oracle initialization or shutdown in progress | ||
| DML logs using package dbms_errlog | ||
| oracle 18c express edition part 1 (download and install ) | ||
| oracle 18c express edition part 2 (unlock hr ) | ||
| oracle 18c express edition part 3 ( sql developer ) | ||
| 28. | What next? it is time for PL/SQL (55 seconds) | Move your skills to PL/SQL |
| Move your skills to Oracle Analytic Functions In-Depth & Advanced Oracle SQL |
Resources Required
- This course covers everything from beginner level to expert level.
- Although Windows OS is favoured, you can download Oracle database 12c on other operating systems.
- No prior database knowledge is necessary because this course is designed to take you from level 0 to certification.
Featured Review
Madeleine Valette (4/5) : This would be perfect if we had exercices to do by ourselves
Pros
- Jean Huang (5/5) : This is best online course that I have not seen before.
- Lulu Wang (5/5) : This course is the best for preparing Oracle 1z0-071/061 for me.
- Kamol (5/5) : The instructor put in a lot of effort to create this comprehensive Oracle SQL course and the course is the best prep I have seen on Udemy for 1Z0-071 certification exam.
- Ali Ziyaei (5/5) : Khaled is one of the best instructors I have ever seen.
Cons
- Owen Nel (1/5) : The course material is not bad but I had difficulty understanding the course proivder’s accent and found the course frustrating and difficult to get through.
- Sean Pollard (2/5) : Background: I completed the course, working through all examples in SQLDeveloper, and am half way through an alternative 1Z0-071 course on Udemy.
- Jay R. (2.5/5) : The content is fine, but the way the instructor teaches it requires you to have a certain amount of intuition to understand what he is talking about.
- Chiti (2/5) : There are no hands on practice exercises like quiz or any practice set. They look like theory lessons
About the Author
The instructor of this course is Khaled Alkhudari who is a Sr Oracle Developer/ Sr system analyst. With 4.5 Instructor Rating and 13,212 Reviews on Udemy, Instructor offers 7 Courses and has taught 50,296 Students so far.
- Khaled Alkhudari earned degree in computer science in 2003
- Khaled Alkhudari worked on numerous projects with some of the top businesses in the world for more than 12 years, including Oracle, Microsoft, Ellucian, Etisalat, and others
- Khaled Alkhudari Possess Several Certificates ( OCA, OCP, Oracle ERP certifications and many others ) and have a lot of experience with Oracle ERP modules and data conversion, as well as knowledge of educational software like Banner and PeopleSoft
Comparison Table
| Parameters | Oracle Database 12c SQL Certified Associate 1Z0-071 | Oracle PL/SQL is My Game: EXAM 1Z0-149 | Oracle Database Administration Workshop ( 12c and Higher) |
|---|---|---|---|
| Offers | INR 499 ( | INR 455 ( | INR 455 ( |
| Duration | 20 hours | 22 hours | 27 hours |
| Rating | 4.5/5 | 4.6/5 | 4.6/5 |
| Student Enrollments | 29,032 | 13,495 | 10,175 |
| Instructors | Khaled Alkhudari | Khaled Alkhudari | Khaled Alkhudari |
| Register Here | Apply Now! | Apply Now! | Apply Now! |
Leave feedback about this