52 Oracle SQL courses

Duration 5 Days 30 CPD hours This course is intended for Data Warehouse Administrator Database Administrators Database Designers Support Engineer Technical Administrator Overview Back up, restore, and patch DBCS database deployments Describe the DBaaS and on-premises Oracle Database architectures Manage database instances Manage UNDO data Move data between databases Implement basic backup and recovery procedures Monitor and tune database performance Tune SQL to improve performance Manage resources with Oracle Database Resource Manager Create pluggable databases (PDBs) Configure the Oracle network environment Administer user security and implement auditing Create and manage tablespaces Manage storage space Create and manage Database as a Cloud Service (DBCS) database deployments Register databases and manage performance with Enterprise Manager Cloud Control The Oracle Database 12c R2: Administration Workshop Ed 3 course is designed to provide students with a firm foundation in administration of an Oracle Database. In this course students will gain a conceptual understanding of Oracle Database architecture and learn how to manage an Oracle Database in an effective and efficient manner. EXPLORING ORACLE DATABASE ARCHITECTURE * Introducing Oracle Database * Relation Database Models * Oracle SQL and PL/SQL * Oracle Database Server Architecture * Connecting to Oracle Databases * Oracle Database Tools * Oracle-Supplied User Accounts * Querying the Oracle Data Dictionary MANAGING DATABASE INSTANCES * Initialization Parameter Files * Starting Up Oracle Databases * Shutting Down Oracle Databases * Opening and Closing PDBs * Working with the Automatic Diagnostic Repository (ADR) * Querying Dynamic Performance Views CREATING PDBS * Methods and Tools to Create PDBs * Creating PDBs from Seed with SQL*Plus * Cloning PDBs with SQL*Plus * Unplugging and Pluggin In PDBs with SQL*Plus * Dropping PDBs with SQL*Plus CONFIGURING THE ORACLE NETWORK ENVIRONMENT * Oracle Net Services * How Listeners Work * Configuring Listeners for Dynamic Service Registration * Configuring Listeners for Static Service Registration * Configuring Local Naming for Connections * Testing Oracle Net Connectivity with tnsping * Configuring Communication Between Databases * Dedicated Versus Shared Server Configurations ADMINISTERING USER SECURITY * Creating Users * Granting Privileges * Creating and Granting Roles * Revoking Privileges and Roles * Creating and Assigning Profiles * Authenticating Users * Assigning Quotas to Users * Applying the Principal of Least Privilege CREATING AND MANAGING TABLESPACES * How Table Data is Stored * Creating Tablespaces in SQL*Plus * Altering and Dropping Tablespaces in SQL*Plus * Viewing Tablespace Information in SQL*Plus * Implementing Oracle Managed Files * Moving and Renaming Online Data Files in SQL*Plus MANAGING STORAGE SPACE * Oracle Database Space Management Features * Block Space Management * Row Chaining and Migration * Free Space Management Within Segments * Types of Segments * Allocating Extents * Understanding Deferred Segment Creation * Space-Saving Features MANAGING UNDO DATA * Undo Data: Overview * Transactions and Undo Data * Storing Undo Information * Comparing Undo Data and Redo Data * Managing Undo * Local Undo Mode Versus Shared Undo Mode * Configuring Undo Retention * Categories of Undo MOVING DATA * Moving Data: General Architecture * Oracle Data Pump: Overview * Oracle Data Pump: Benefits * Data Pump Export and Import Clients * Data Pump Utility: Interfaces and Modes * Data Pump Import: Transformations * SQL*Loader Overview * Loading Methods BACKUP AND RECOVERY CONCEPTS * DBA Responsibilities * Categories of Failure * Understanding Instance Recovery * Understanding Types of Backups * Comparing Complete and Incomplete Recovery * Oracle Data Protection Solutions * Flashback Technology MONITORING AND TUNING DATABASE PERFORMANCE * Managing Performance Activities * Performance Planning Considerations * Database Maintenance * Automatic Workload Repository (AWR) * Automatic Database Diagnostic Monitor * Performance Monitoring * Performance Tuning Methodology * Database Server Statistics and Metrics SQL TUNING * SQL Tuning Process * Oracle Optimizer * Optimizer Statistics * SQL Plan Directives * Adaptive Execution Plans * SQL Tuning Advisor * SQL Access Advisor * SQL Performance Analyzer ORACLE DATABASE RESOURCE MANAGER * Oracle Database Resource Manager: Overview * Resource Manager Elements * Using Resource Manager to Allocate Resources * Creating a Simple Resource Plan * Creating a Complex Resource Plan * Using the Active Session Pool Feature * Limiting CPU Utilization at the Database Level * Limiting CPU Utilization at the Server Level ENTERPRISE MANAGER CLOUD CONTROL * Controlling the Enterprise Manager Cloud Control Framework * Starting the Enterprise Manager Cloud Control Framework * Stopping the Enterprise Manager Cloud Control Framework INTRODUCTION TO ORACLE DATABASE CLOUD SERVICE * Oracle Cloud: Overview * Database Cloud Service Offerings * DBCS Architecture * Features and Tooling * Additional Database Configuration Options CREATING DBCS DATABASE DEPLOYMENTS * Automated Database Provisioning * Creating a Database Deployment * How SSH Key Pairs are Used * Creating an SSH Key Pair * Storage Used for Database Files MANAGING DBCS DATABASE DEPLOYMENTS * Cloud Tooling * Accessing Tools and Features from the DBCS Console * Managing the Compute Node Associated With a Database Deployment * Managing Network Access to DBCS * Enabling Access to a Compute Node Port * Scaling a Database Deployment BACKING UP AND RESTORING DBCS DATABASE DEPLOYMENTS * Backing Up and Recovering Databases on DBCS * Backup Destination Choices * Backup Configuration * Creating an On-Demand Backup * Customizing the Backup Configuration * Performing Recovery by Using the Console * Performing Recovery by Using the dbaascli Utility PATCHING DBCS DATABASE DEPLOYMENTS * Patching DBCS * Using the DBCS Console to Manage Patches * Using the dbaascli Utility to Manage Patches CREATING MASTER ENCRYPTION KEYS FOR PDBS * CDB and PDB Master Encryption Keys * Determining Whether You Need to Create and Activate and Encryption Key for a PDB * Creating and Activating an Encryption Key TABLESPACE ENCRYPTION BY DEFAULT * Tablespace Encryption by Default in DBCS * Transparent Data Encryption (TDE) Overview * Components of TDE * Using TDE * Defining the Keystore Location * Controlling Tablespace Encryption by Default * Managing the Software Keystore and Master Encryption Key * Managing the Keystore in CDBs and PDBs ADDITIONAL COURSE DETAILS: Nexus Humans Oracle Database 12c R2 - Administration Workshop Ed 3 training program is a workshop that presents an invigorating mix of sessions, lessons, and masterclasses meticulously crafted to propel your learning expedition forward. This immersive bootcamp-style experience boasts interactive lectures, hands-on labs, and collaborative hackathons, all strategically designed to fortify fundamental concepts. Guided by seasoned coaches, each session offers priceless insights and practical skills crucial for honing your expertise. Whether you're stepping into the realm of professional skills or a seasoned professional, this comprehensive course ensures you're equipped with the knowledge and prowess necessary for success. While we feel this is the best course for the Oracle Database 12c R2 - Administration Workshop Ed 3 course and one of our Top 10 we encourage you to read the course outline to make sure it is the right content for you. Additionally, private sessions, closed classes or dedicated events are available both live online and at our training centres in Dublin and London, as well as at your offices anywhere in the UK, Ireland or across EMEA.

Duration 3 Days 18 CPD hours This course is intended for This is an introductory- level course appropriate for those who are developing applications using relational databases, or who are using SQL to extract and analyze data from databases and need to use the full power of SQL queries. Overview This course combines expert lecture, real-world demonstrations and group discussions with machine-based practical labs and exercises. Working in a hands-on learning environment led by our expert practitioner, attendees will learn to: Maximize the potential of SQL to build powerful, complex and robust SQL queries Query multiple tables with inner joins, outer joins and self joins Construct recursive common table expressions Summarize data using aggregation and grouping Execute analytic functions to calculate ranks Build simple and correlated subqueries Thoroughly test SQL queries to avoid common errors Select the most efficient solution to complex SQL problems A company?s success hinges on responsible, accurate database management. Organizations rely on highly available data to complete all sorts of tasks, from creating marketing reports and invoicing customers to setting financial goals. Data professionals like analysts, developers and architects are tasked with creating, optimizing, managing and analyzing data from databases ? with little room for error. When databases aren?t built or maintained correctly, it?s easy to mishandle or lose valuable data. Our SQL Programming and Database Training Series provides students with the skills they require to develop, analyze and maintain data and in correctly structured, modern and secure databases. SQL is the cornerstone of all relational database operations. In this hands-on course, you learn to exploit the full potential of the SELECT statement to write robust queries using the best query method for your application, test your queries, and avoid common errors and pitfalls. It also teaches alternative solutions to given problems, enabling you to choose the most efficient solution in each situation. INTRODUCTION: QUICK TOOLS REVIEW * Introduction to SQL and its development environments * Using SQL*PLUS * Using SQL Developer USING THE SQL SELECT STATEMENT * Capabilities of the SELECT statement * Arithmetic expressions and NULL values in the SELECT statement * Column aliases * Use of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword * Use of the DESCRIBE command RESTRICTING AND SORTING DATA * Limiting the Rows * Rules of precedence for operators in an expression * Substitution Variables * Using the DEFINE and VERIFY command SINGLE-ROW FUNCTIONS * Describe the differences between single row and multiple row functions * Manipulate strings with character function in the SELECT and WHERE clauses * Manipulate numbers with the ROUND, TRUNC and MOD functions * Perform arithmetic with date data * Manipulate dates with the date functions CONVERSION FUNCTIONS AND EXPRESSIONS * Describe implicit and explicit data type conversion * Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions * Nest multiple functions * Apply the NVL, NULLIF, and COALESCE functions to data * Decode/Case Statements USING THE GROUP FUNCTIONS AND AGGREGATED DATA * Group Functions * Creating Groups of Data * Having Clause * Cube/Rollup Clause SQL JOINS AND JOIN TYPES * Introduction to JOINS * Types of Joins * Natural join * Self-join * Non equijoins * OUTER join USING SUBQUERIES * Introduction to Subqueries * Single Row Subqueries * Multiple Row Subqueries USING THE SET OPERATORS * Set Operators * UNION and UNION ALL operator * INTERSECT operator * MINUS operator * Matching the SELECT statements USING DATA MANIPULATION LANGUAGE (DML) STATEMENTS * Data Manipulation Language * Database Transactions * Insert * Update * Delete * Merge USING DATA DEFINITION LANGUAGE (DDL) * Data Definition Language * Create * Alter * Drop DATA DICTIONARY VIEWS * Introduction to Data Dictionary * Describe the Data Dictionary Structure * Using the Data Dictionary views * Querying the Data Dictionary Views * Dynamic Performance Views CREATING SEQUENCES, SYNONYMS, INDEXES * Creating sequences * Creating synonyms * Creating indexes * Index Types CREATING VIEWS * Creating Views * Altering Views * Replacing Views MANAGING SCHEMA OBJECTS * Managing constraints * Creating and using temporary tables * Creating and using external tables RETRIEVING DATA USING SUBQUERIES * Retrieving Data by Using a Subquery as Source * Working with Multiple-Column subqueries * Correlated Subqueries * Non-Correlated Subqueries * Using Subqueries to Manipulate Data * Using the Check Option * Subqueries in Updates and Deletes * In-line Views DATA CONTROL LANGUAGE (DCL) * System privileges * Creating a role * Object privileges * Revoking object privileges MANIPULATING DATA * Overview of the Explicit Default Feature * Using multitable INSERTs * Using the MERGE statement * Tracking Changes in Data

Duration 2 Days 12 CPD hours This course is intended for This SQL training course is designed for students new to writing SQL queries. Overview Upon successful completion of this course, students will be able to: - Understand how relational databases work - Use SQL to output reports - Write queries to obtain data from multiple tables. Students will learn SQL to build applications or to generate business reports.The SQL learned in the course is applicable to all major databases. RELATIONAL DATABASE BASICS * Brief History of SQL * Relational Databases * Popular Databases * SQL Statements SIMPLE SELECTS * Introduction to the Northwind Database * Some Basics * SELECTing All Columns in All Rows * Exploring the Tables * SELECTing Specific Columns * Sorting Records * The WHERE Clause and Operator Symbols * Using the WHERE clause to check for equality or inequality * Using the WHERE clause to check for greater or less than * Checking for NULL * Using WHERE and ORDER BY Together * The WHERE Clause and Operator Words * More SELECTs with WHERE * Checking Multiple Conditions * Writing SELECTs with Multiple Conditions ADVANCED SELECTS * Calculated Fields * Calculating Fields * Aggregate Functions and Grouping * Working with Aggregate Functions * Built-in Data Manipulation Functions * Data Manipulation Functions SUBQUERIES, JOINS AND UNIONS * Subqueries * Joins * Using Joins * Outer Joins * Unions * Working with Unions IF TIME ALLOWS, ONE OR MORE OF THE FOLLOWING MAY BE COVERED: * Conditional Processing with CASE * INSERT, UPDATE, DELETE * Student Challenges - Design your own reports

A complete course on Sqoop, Flume, and Hive: Ideal for achieving CCA175 and Hortonworks Spark Certification

This Oracle 11g PL/SQL Part 2 course will be very advantageous for you to learn about developing database server-side PL/SQL program units within an Oracle database. This course covers a variety of topics which include understanding the basic form and structure of program units stored within the database, building and maintaining stored procedures, functions, packaged programs, and database triggers. In addition to that, you'll take the support of advanced programming techniques such as cursor variables and cursor expressions, which broadly discussed in the course. This course is ideal to prepare yourself for the Oracle certification exams (OCP). Upon successful completion of this course, you'll stand out from your competition while you're chasing your dream job! WHAT WILL I LEARN? * Prepare your environment * Introducing Database Program Units * Creating and Maintaining Stored Procedures & Functions * Creating and Maintaining Packages * Advanced Cursor Techniques * Using System-Supplied Packages * Creating and Maintaining Database Triggers * Implementing System Event Triggers REQUIREMENTS * Familiarity with database and programming principles WHO IS THE TARGET AUDIENCE? * Application designers and developers * Database administrators * Business users and non-technical senior end users Introduction Introduction 00:03:00 Install Oracle XE 00:11:00 SQL Developer Installation 00:00:00 Workstation Setup 00:12:00 Database Program Units Introducing the Database Program Unit 00:19:00 Working with Stored Procedures and Functions Creating Stored Procedures and Functions 00:19:00 Understanding the Parameter Specification 00:23:00 Executing Procedures and Functions 00:26:00 Calling Functions from within SQL 00:13:00 Handling Compilation Errors 00:08:00 Maintaining Stored Procedures and Functions Recompiling and Dropping Programs 00:16:00 Data Dictionary Storage 00:25:00 Managing Dependencies 00:13:00 Tracking Dependencies 00:08:00 Using the Dependency Tracking Utility 00:09:00 Creating and Maintaining Packages What Is a Package? 00:10:00 Creating a Package 00:19:00 Package Definition Information 00:08:00 Advanced Programming Techniques 00:06:00 Using Persistent Global Objects 00:04:00 Including Initialization Logic 00:15:00 Object Oriented Support within Packages 00:14:00 Package Maintenance 00:18:00 Advanced Cursor Techniques Learning to Use Cursor Variables 00:24:00 Advanced Cursor Techniques Weak Cursor Definitions 00:16:00 REFCURSOR Cursor Definition 00:05:00 Using Cursor Expression 00:14:00 Using System Supplied Packages Using the DBMS OUTPUT() Package 00:12:00 Using the UTL FILE() Package 00:30:00 Creating Database Triggers Understanding the Database Trigger 00:23:00 Statement-Level Triggers 00:23:00 Row-Level Triggers 00:12:00 Row Level Trigger Example 1 00:11:00 Row Level Trigger Example 2 00:07:00 Row Level Trigger Example 3 00:08:00 Working with the INSTEAD OF Trigger 00:11:00 Using Triggers within an Application 00:09:00 Trigger Maintenance Using the CALL Statement 00:09:00 Trigger Maintenance Tasks 00:12:00 Work with Multiple Triggers for a Single Table 00:11:00 Handling Mutating Table Issues 00:22:00 Using the Compound Trigger 00:06:00 Working with System Event Triggers What Is a System Event Trigger 00:05:00 Defining Scope 00:04:00 Identifying Available System Events 00:12:00 Conclusion Conclusion 00:02:00 Course Certification

DESCRIPTION The Oracle Database server composes PL/SQL units and stores it in the database.  Mainly, PL/SQL functions to hold to hold SQL declaration within its sentence structure. Do you want to know more about SL/SQL function? How does it bring optimal proficiency in 'procedural language'? if so, take a look at the Oracle 11g PL/SQL Intermediate course. It will assist you in understanding all you need to know about Oracle 11g PL/SQL. The course designs to help the learners to know its function incorporation with different strings, numbers, table row record, and error management. The Oracle 11g PL/SQL Intermediate course teaches the fundamental form and structure of the program unit saved within database, creating and managing stored methods, functions, packaged programs, and database prompts. Its contents aim to take you further in your quest to teach the effective methods including variables and cursor expressions. ASSESSMENT: This course does not involve any MCQ test. Students need to answer assignment questions to complete the course, the answers will be in the form of written work in pdf or word. Students can write the answers in their own time. Once the answers are submitted, the instructor will check and assess the work. CERTIFICATION: After completing and passing the course successfully, you will be able to obtain an Accredited Certificate of Achievement. Certificates can be obtained either in hard copy at a cost of £39 or in PDF format at a cost of £24. WHO IS THIS COURSE FOR? Oracle 11g PL/SQL Intermediate is certified by CPD Qualifications Standards and CiQ. This makes it perfect for anyone trying to learn potential professional skills. As there is no experience and qualification required for this course, it is available for all students from any academic background. REQUIREMENTS Our Oracle 11g PL/SQL Intermediate is fully compatible with any kind of device. Whether you are using Windows computer, Mac, smartphones or tablets, you will get the same experience while learning. Besides that, you will be able to access the course with any kind of internet connection from anywhere at any time without any kind of limitation. CAREER PATH After completing this course you will be able to build up accurate knowledge and skills with proper confidence to enrich yourself and brighten up your career in the relevant job market. Introduction Introduction FREE 00:03:00 Preparing Your Environment Install Oracle XE 00:11:00 SQL Developer Installation 00:20:00 Workstation Setup 00:12:00 Database Program Units Introducing the Database Program Unit 00:19:00 Working with Stored Procedures and Functions Creating Stored Procedures and Functions 00:19:00 Understanding the Parameter Specification 00:23:00 Executing Procedures and Functions 00:26:00 Calling Functions from within SQL 00:13:00 Handling Compilation Errors 00:08:00 Maintaining Stored Procedures and Functions Recompiling and Dropping Programs 00:16:00 Data Dictionary Storage 00:25:00 Managing Dependencies 00:13:00 Tracking Dependencies 00:08:00 Using the Dependency Tracking Utility 00:09:00 Creating and Maintaining Packages What Is a Package? 00:10:00 Creating a Package 00:19:00 Package Definition Information 00:08:00 Advanced Programming Techniques 00:06:00 Using Persistent Global Objects 00:04:00 Including Initialization Logic 00:15:00 Object Oriented Support within Packages 00:14:00 Package Maintenance 00:18:00 Advanced Cursor Techniques Learning to Use Cursor Variables 00:24:00 Advanced Cursor Techniques Weak Cursor Definitions 00:16:00 REFCURSOR Cursor Definition 00:05:00 Using Cursor Expression 00:14:00 Using System Supplied Packages Using the DBMS OUTPUT() Package 00:12:00 Using the UTL FILE() Package 00:30:00 Creating Database Triggers Understanding the Database Trigger 00:23:00 Statement-Level Triggers 00:23:00 Row-Level Triggers 00:12:00 Row Level Trigger Example 1 00:11:00 Row Level Trigger Example 2 00:07:00 Row Level Trigger Example 3 00:08:00 Working with the INSTEAD OF Trigger 00:11:00 Using Triggers within an Application 00:09:00 Trigger Maintenance Using the CALL Statement 00:09:00 Trigger Maintenance Tasks 00:12:00 Work with Multiple Triggers for a Single Table 00:11:00 Handling Mutating Table Issues 00:22:00 Using the Compound Trigger 00:06:00 Working with System Event Triggers What Is a System Event Trigger 00:05:00 Defining Scope 00:04:00 Identifying Available System Events 00:12:00 Conclusion Conclusion 00:02:00 Order Your Certificate and Transcript Order Your Certificates and Transcripts 00:00:00 Order Your Certificate and Transcript Order Your Certificates and Transcripts 00:00:00

DESCRIPTION PL/SQL is program units which are composed by the Oracle Database server and stored in the database.  The main function of PL/SQL is to hold SQL proclamation within its syntax. If you are interested to know more about SL/SQL function, and how it brings optimal competence in terms of 'procedural language', you may have a look at the Oracle 11g PL/SQL Beginner course. The course is designed in such a way that will assist the learners to know how it works with various strings, numbers, table row record, and error management. It also teaches the learner how to use different key data lexicon to synopsize and maintain the data. The learners will also be able to know about portability, productivity, and manageability of this most widely manipulative language. ASSESSMENT: This course does not involve any MCQ test. Students need to answer assignment questions to complete the course, the answers will be in the form of written work in pdf or word. Students can write the answers in their own time. Once the answers are submitted, the instructor will check and assess the work. CERTIFICATION: After completing and passing the course successfully, you will be able to obtain an Accredited Certificate of Achievement. Certificates can be obtained either in hard copy at a cost of £39 or in PDF format at a cost of £24. WHO IS THIS COURSE FOR? Oracle 11g PL/SQL Beginner is certified by CPD Qualifications Standards and CiQ. This makes it perfect for anyone trying to learn potential professional skills. As there is no experience and qualification required for this course, it is available for all students from any academic background. REQUIREMENTS Our Oracle 11g PL/SQL Beginner is fully compatible with any kind of device. Whether you are using Windows computer, Mac, smartphones or tablets, you will get the same experience while learning. Besides that, you will be able to access the course with any kind of internet connection from anywhere at any time without any kind of limitation. CAREER PATH After completing this course you will be able to build up accurate knowledge and skills with proper confidence to enrich yourself and brighten up your career in the relevant job market. Introduction Introduction FREE 00:03:00 Preparing Your Environment Course Setup 00:37:00 Working with Oracle Database Tools Selecting the Appropriate Database Tool 00:31:00 Language Features Understanding PL/SQL 00:18:00 PL/SQL Blocks 00:15:00 Working with SQL Plus 00:11:00 The DECLARE Clause DECLARE Clause Objects 00:16:00 PL/SQL Available Data Types 00:17:00 Declaring Simple Data Types and Assignments 00:12:00 Declaring Complex Data Types and Assignments 00:11:00 Declaring Complex RowTypes and Assignments 00:07:00 Declaring Complex Tables 00:08:00 Declaring Complex Records 00:08:00 Creating and Declaring User-Defined Types 00:09:00 The BEGIN Clause What Can Be Included in the BEGIN Clause 00:07:00 Working with CHAR and VARCHAR2 Variables 00:08:00 Handling String Literals 00:08:00 Working with Numeric Variables 00:07:00 Working with Date Variables 00:06:00 Assigning and Using Boolean Variables 00:08:00 Using Comparison Operators 00:07:00 Using SQL Functions 00:06:00 SQL DML within PL/SQL 00:15:00 Using SELECT 00:07:00 Exceptions and Embedded SQL 00:11:00 Using Sequences 00:14:00 Logic Control and Branching 00:07:00 Using GOTO 00:06:00 Working with LOOPS 00:19:00 Creating and Using the Indefinite Loop 00:07:00 Creating and Using the Conditional Loop 00:09:00 Using Nested Loops 00:12:00 Using the If-Then-Else 00:19:00 Using the CASE Statement 00:18:00 The EXCEPTION Clause What Are the Types of Exceptions 00:06:00 Trapping Exceptions 00:06:00 Identify Exception Details 00:11:00 Creating User-Defined Exceptions 00:11:00 Using SQLCODE and SQLERRM Objects 00:09:00 Understand the Implicit Cursor 00:15:00 Creating User-Defined Events 00:12:00 Explicit Cursors Understanding the Concept and Purpose of Explicit Cursors 00:18:00 Learn about Cursor Attributes 00:10:00 Using the Continue Statement 00:06:00 Working with Updateable Cursors 00:19:00 Creating and Using Cursor Parameters 00:15:00 FORLOOP Cursors 00:07:00 Nested Blocks Understanding Nested Blocks 00:11:00 Difference between Global vs Local Objects and Exceptions 00:13:00 Declared Subprograms Subprograms: How They Differ from Nested Blocks 00:18:00 Conclusion Conclusion 00:04:00 Certificate and Transcript Order Your Certificates and Transcripts 00:00:00

Duration 3 Days 18 CPD hours This course is intended for This is an introductory level SQL course, appropriate for anyone needing to interface with an Oracle database or those needing a general understanding of Oracle database functionality. That would include end users, business analysts, application developers and database administrators. Overview Working in a hands on learning environment led by our expert practitioner, attendees will explore: Basic RDBMS Principles The SQL Language and Tools Using SQL Developer SQL Query Basics WHERE and ORDER BY Functions ANSI 92 Joins ANSI 99 Joins Subqueries Regular Expressions Analytics A company?s success hinges on responsible, accurate database management. Organizations rely on highly available data to complete all sorts of tasks, from creating marketing reports and invoicing customers to setting financial goals. Data professionals like analysts, developers and architects are tasked with creating, optimizing, managing and analyzing data from databases ? with little room for error. When databases aren?t built or maintained correctly, it?s easy to mishandle or lose valuable data. Our SQL Programming and Database Training Series provides students with the skills they require to develop, analyze and maintain data and in correctly structured, modern and secure databases. A full presentation of the basics of relational databases and their use are also covered. BASIC RDBMS PRINCIPLES * Relational design principles * Accessing data through a structured query language * Entity relationship diagrams * Data Domains * Null values * Indexes * Views * Denormalization * Data Model Review THE SQL LANGUAGE AND TOOLS * Using SQL*Plus * Why Use SQL*Plus When * Other Tools Are Available? * Starting SQL*Plus * EZConnect * SQL Commands * PL/SQL Commands * SQL*Plus Commands * The COLUMN Command * The HEADING Clause * The FORMAT Clause * The NOPRINT Clause * The NULL Clause * The CLEAR Clause * Predefined define variables * LOGIN.SQL * Command history * Copy and paste in SQL*Plus * Entering SQL commands * Entering PL/SQL commands * Entering SQL*Plus commands * Default output from SQL*Plus * Entering Queries * What about PL/SQL? USING SQL DEVELOPER * Choosing a SQL Developer version * Configuring connections * Creating A Basic Connection * Creating A TNS Connection * Connecting * Configuring preferences * Using SQL Developer * The Columns Tab * The Data Tab * The Constraints Tab * The Grants Tab * The Statistics Tab * Other Tabs * Queries In SQL Developer * Query Builder * Accessing Objects Owned By Other Users * The Actions Pulldown Menu * Differences between SQL Developer and SQL*Plus * Reporting Commands Missing In SQL Developer * General Commands Missing In SQL Developer * Data Dictionary report * User Defined reports * Using scripts in SQL Developer WHERE AND ORDER BY * WHERE clause basics * Comparison operators * Literals and Constants in SQL * Simple pattern matching * Logical operations * The DUAL table * Arithmetic operations * Expressions in SQL * Character operators * Pseudo columns * Order by clause basics * Ordering Nulls * Accent and case sensitive sorts * Sampling data * WHERE and ORDER BY in SQL Developer * All, Any, Some FUNCTIONS * The basics of Oracle functions * Number functions * Character functions * Date functions * Conversion functions * Other functions * Large object functions * Error functions * The RR format mode; * Leveraging your knowledge ANSI 92 JOINS * Basics of ANSI 92 Joins * Using Query Builder with multiple tables * Table Aliases * Outer joins * Outer Joins In Query Builder * Set operators * Self-referential joins * Non-Equijoins ANSI 99 JOINS * Changes with ANSI99 * CROSS Join * NATURAL Join * JOIN USING * JOIN ON * LEFT / RIGHT OUTER JOIN * FULL OUTER JOIN SUBQUERIES * Why use subqueries? * WHERE clause subqueries * FROM clause subqueries * HAVING clause subqueries * CORRELATED subqueries * SCALAR subqueries * DML and subqueries * EXISTS subqueries * Hierarchical queries * TOP N AND BOTTOM N queries * Creating subqueries using Query Builder REGULAR EXPRESSIONS * Available Regular Expressions * Regular Expression Operators * Character Classes * Pattern matching options * REGEX_LIKE * REGEXP_SUBSTR * REGEXP_INSTR * REGEXP_REPLACE * REGEXP_COUNT ANALYTICS * The WITH clause Reporting aggregate functions Analytical functions User-Defined bucket histograms The MODEL clause PIVOT and UNPIVOT Temporal validity MORE ANALYTICS * RANKING functions * RANK * DENSE_RANK * CUME_DIST * PERCENT_RANK * ROW_NUMBER * Windowing aggregate functions * RATIO_TO_REPORT * LAG / LEAD * Linear Regression functions * Inverse Percentile functions * Hypothetical ranking functions * Pattern Matching ADDITIONAL COURSE DETAILS: Nexus Humans Introduction to SQL Programming Basics (TTSQL002) training program is a workshop that presents an invigorating mix of sessions, lessons, and masterclasses meticulously crafted to propel your learning expedition forward. This immersive bootcamp-style experience boasts interactive lectures, hands-on labs, and collaborative hackathons, all strategically designed to fortify fundamental concepts. Guided by seasoned coaches, each session offers priceless insights and practical skills crucial for honing your expertise. Whether you're stepping into the realm of professional skills or a seasoned professional, this comprehensive course ensures you're equipped with the knowledge and prowess necessary for success. While we feel this is the best course for the Introduction to SQL Programming Basics (TTSQL002) course and one of our Top 10 we encourage you to read the course outline to make sure it is the right content for you. Additionally, private sessions, closed classes or dedicated events are available both live online and at our training centres in Dublin and London, as well as at your offices anywhere in the UK, Ireland or across EMEA.

Duration 5 Days 30 CPD hours This course is intended for This course is intended for individuals who are Application designers and database developers, database administrators and web server administrators. Overview Upon successful completion of this course, students will be able to work with Oracle database programming using the PL/SQL programming language. They will learn the syntax, structure and features of the language. In this course, students will learn the foundation for the programming series and the use of database-resident stored program units for Oracle 12c. SELECTION & SETUP OF THE DATABASE INTERFACE * Considering Available Tools * Selecting the Appropriate Tool * Oracle Net Database Connections * Oracle PAAS Database Connections * Setup SQL Developer * Setup SQL*Plus * Setup Jdeveloper ABOUT BIND & SUBSTITUTION VARIABLES * Using SQL Developer * Using SQL*Plus CHOOSING A DATABASE PROGRAMMING LANGUAGE * What is Database Programming? * PL?SQL Performance Advantages * Integration wth Other Languages PL/SQL LANGUAGE FUNDAMENTALS * PL/SQL Program Structure * LANGUAGE SYNTAX RULES * EMBEDDING SQL * WRITING READABLE CODE * GENERATING DATABASE OUTPUT * SQL*PLUS INPUT OF A PROGRAM BLOCK DECLARE SECTION * About the Declare Section * DECLARE PRIMITIVE TYPES * DECLARATION OPTIONS * NOT NULL * CONSTANT * DATA DICTIONARY INTEGRATION * %TYPE * DECLARE SIMPLE USER---DEFINED TYPES * TYPE... TABLE * TYPE... RECORD * EXTENDED USER---DEFINED TYPES BEGIN SECTION * About the Begin Section * Manipulating Program Data * Logic Control & Branching * GOTO * LOOP * IF-THEN-ELSE * CASE EXCEPTION SECTION * ABOUT THE EXCEPTION SECTION * ISOLATING THE SPECIFIC EXCEPTION * PRAGMA EXCEPTION_INIT * SQLCODE &SQLERRM Example * SQL%ROWCOUNT &SELECT...INTO BEYOND THE BASICS: EXPLICIT CURSORS * ABOUT EXPLICIT CURSORS * EXTENDED CURSOR TECHNIQUES * FOR UPDATE OF Clause * WHERE CURRENT OF Clause * Using FOR?LOOP Cursors BEYOND THE BASICS: NESTED BLOCKS BEYOND THE BASICS: DECLARED SUBPROGRAMS * USING DECLARED SUBPROGRAMS * DECLARED PROCEDURE * DECLARED FUNCTION INTRODUCING DATABSE-RESIDENT PROGRAM UNITS * ABOUT DATABASE---RESIDENT PROGRAMS * PHYSICAL STORAGE & EXECUTION * TYPES OF STORED PROGRAM UNITS * STORED PROGRAM UNIT ADVANTAGES * MODULAR DESIGN PRINCIPLES CREATING STORED PROCEDURES & FUNCTIONS * STORED PROCEDURES & FUNCTIONS * CREATE PROCEDURE / CREATE FUNCTION * CREATING PROCEDURES & FUNCTIONS * RAISE_SALARY() Procedure * SALARY_VALID() Function * THE PARAMETER SPECIFICATION * DEFAULT Clause * SYSTEM & OBJECT PRIVILEGES * USING THE DEVELOPMENT TOOLS EXECUTING STORED PROCEDURES & FUNCTIONS * CALLING PROCEDURES & FUNCTIONS * UNIT TESTING WITH EXECUTE * ANONYMOUS BLOCK UNIT TESTING * SPECIFYING A PARAMETER NOTATION * SQL WORKSHEET UNIT TESTING * CALLING FUNCTIONS FROM SQL MAINTAINING STORED PROGRAM UNITS * RECOMPILING PROGRAMS * Mass Recompilation Using UTL_RECOMP() * DROPPING PROCEDURES & FUNCTIONS * DROP PROCEDURE / FUNCTION * DATA DICTIONARY METADATA * Using USER_OBJECTS * Using USER_SOURCE * Using USER_ERRORS * Using USER_OBJECT_SIZE * Using USER_DEPENDENCIES MANAGING DEPENDENCIES * DEPENDENCY INTERNALS * TRACKING DEPENDENCIES * THE DEPENDENCY TRACKING UTILITY * SQL DEVELOPER DEPENDENCY INFO * DEPENDENCY STRATEGY CHECKLISTS CREATING & MAINTAINING PACKAGES * ABOUT PACKAGES * CREATING PACKAGES * MAINTAINING PACKAGES * PERFORMANCE CONSIDERATIONS ADVANCED PACKAGE CAPABILITIES * DEFINER & INVOKER RIGHTS * WHITE LITS & ACCESSIBLE BY * PERSISTENT GLOBAL OBJECTS * DEFINING INITIALIZATION LOGIC * OBJECT ORIENTATION SUPPORT ADVANCED CURSOR TECHNIQUES * USING CUSROS VARIABLES * USING SYS_REFCURSOR * USING CURSOR EXPRESSIONS USING SYSTEM-SUPPLIED PACKAGE * DBMS_OUTPUT() * UTL_FILE() * FOPEN() EXAMPLE DATABASE TRIGGER CONCEPTS * ABOUT DATABASE TRIGGERS * DML EVENT TRIGGER SUB---TYPES * DATABASE TRIGGER SCENARIO * TRIGGER EXECUTION MECHANISMS * TRIGGERS WITHIN SQL WORKSHEET CREATING DATABASE TRIGGERS * STATEMENT-LEVEL TRIGGERS * Using RAISE_APPLICATION_ERROR() * ROW---LEVEL TRIGGERS * EXAMPLES OF TRIGGERS * EMPLOYEE_SALARY_CHECK Example * EMPLOYEE_JOURNAL Example * BUDGET_EVENT Example * INSTEAD OF TRIGGERS * TRIGGERS WITHIN AN APPLICATION MAINTAINING DATABASE TRIGGERS * CALL SYNTAX * TRIGGER MAINTENANCE TASKS * SHOW ERRORS TRIGGER * DROP TRIGGER * ALTER TRIGGER * MULTIPLE TRIGGERS FOR A TABLE * HANDLING MUTATING TABLE ISSUES IMPLEMENTING SYSTEM EVENT TRIGGERS * WHAT ARE SYSTEM EVENT TRIGGERS? * DEFININGTHE SCOPE * AVAILABLE SYSTEM EVENTS * SYSTEM EVENT ATTRIBUTES ADDITIONAL COURSE DETAILS: Nexus Humans Oracle 12c PL/SQL Fundamentals training program is a workshop that presents an invigorating mix of sessions, lessons, and masterclasses meticulously crafted to propel your learning expedition forward. This immersive bootcamp-style experience boasts interactive lectures, hands-on labs, and collaborative hackathons, all strategically designed to fortify fundamental concepts. Guided by seasoned coaches, each session offers priceless insights and practical skills crucial for honing your expertise. Whether you're stepping into the realm of professional skills or a seasoned professional, this comprehensive course ensures you're equipped with the knowledge and prowess necessary for success. While we feel this is the best course for the Oracle 12c PL/SQL Fundamentals course and one of our Top 10 we encourage you to read the course outline to make sure it is the right content for you. Additionally, private sessions, closed classes or dedicated events are available both live online and at our training centres in Dublin and London, as well as at your offices anywhere in the UK, Ireland or across EMEA.

Duration 5 Days 30 CPD hours This course is intended for This course is intended for individuals who are Application designers and database developers, database administrators and web server administrators. Overview Upon successful completion of this course, students will be able to work with Oracle database programming using the PL/SQL programming language. They will learn the syntax, structure and features of the language. In this course, students will learn the foundation for the programming series and the use of database-resident stored program units for Oracle 12c. SELECTION & SETUP OF THE DATABASE INTERFACE * Considering Available Tools * Selecting the Appropriate Tool * Oracle Net Database Connections * Oracle PAAS Database Connections * Setup SQL Developer * Setup SQL*Plus * Setup Jdeveloper ABOUT BIND & SUBSTITUTION VARIABLES * Using SQL Developer * Using SQL*Plus CHOOSING A DATABASE PROGRAMMING LANGUAGE * What is Database Programming? * PL?SQL Performance Advantages * Integration wth Other Languages PL/SQL LANGUAGE FUNDAMENTALS * PL/SQL Program Structure * LANGUAGE SYNTAX RULES * EMBEDDING SQL * WRITING READABLE CODE * GENERATING DATABASE OUTPUT * SQL*PLUS INPUT OF A PROGRAM BLOCK DECLARE SECTION * About the Declare Section * DECLARE PRIMITIVE TYPES * DECLARATION OPTIONS * NOT NULL * CONSTANT * DATA DICTIONARY INTEGRATION * %TYPE * DECLARE SIMPLE USER---DEFINED TYPES * TYPE... TABLE * TYPE... RECORD * EXTENDED USER---DEFINED TYPES BEGIN SECTION * About the Begin Section * Manipulating Program Data * Logic Control & Branching * GOTO * LOOP * IF-THEN-ELSE * CASE EXCEPTION SECTION * ABOUT THE EXCEPTION SECTION * ISOLATING THE SPECIFIC EXCEPTION * PRAGMA EXCEPTION_INIT * SQLCODE &SQLERRM Example * SQL%ROWCOUNT &SELECT...INTO BEYOND THE BASICS: EXPLICIT CURSORS * ABOUT EXPLICIT CURSORS * EXTENDED CURSOR TECHNIQUES * FOR UPDATE OF Clause * WHERE CURRENT OF Clause * Using FOR?LOOP Cursors BEYOND THE BASICS: NESTED BLOCKS BEYOND THE BASICS: DECLARED SUBPROGRAMS * USING DECLARED SUBPROGRAMS * DECLARED PROCEDURE * DECLARED FUNCTION INTRODUCING DATABSE-RESIDENT PROGRAM UNITS * ABOUT DATABASE---RESIDENT PROGRAMS * PHYSICAL STORAGE & EXECUTION * TYPES OF STORED PROGRAM UNITS * STORED PROGRAM UNIT ADVANTAGES * MODULAR DESIGN PRINCIPLES CREATING STORED PROCEDURES & FUNCTIONS * STORED PROCEDURES & FUNCTIONS * CREATE PROCEDURE / CREATE FUNCTION * CREATING PROCEDURES & FUNCTIONS * RAISE_SALARY() Procedure * SALARY_VALID() Function * THE PARAMETER SPECIFICATION * DEFAULT Clause * SYSTEM & OBJECT PRIVILEGES * USING THE DEVELOPMENT TOOLS EXECUTING STORED PROCEDURES & FUNCTIONS * CALLING PROCEDURES & FUNCTIONS * UNIT TESTING WITH EXECUTE * ANONYMOUS BLOCK UNIT TESTING * SPECIFYING A PARAMETER NOTATION * SQL WORKSHEET UNIT TESTING * CALLING FUNCTIONS FROM SQL MAINTAINING STORED PROGRAM UNITS * RECOMPILING PROGRAMS * Mass Recompilation Using UTL_RECOMP() * DROPPING PROCEDURES & FUNCTIONS * DROP PROCEDURE / FUNCTION * DATA DICTIONARY METADATA * Using USER_OBJECTS * Using USER_SOURCE * Using USER_ERRORS * Using USER_OBJECT_SIZE * Using USER_DEPENDENCIES MANAGING DEPENDENCIES * DEPENDENCY INTERNALS * TRACKING DEPENDENCIES * THE DEPENDENCY TRACKING UTILITY * SQL DEVELOPER DEPENDENCY INFO * DEPENDENCY STRATEGY CHECKLISTS CREATING & MAINTAINING PACKAGES * ABOUT PACKAGES * CREATING PACKAGES * MAINTAINING PACKAGES * PERFORMANCE CONSIDERATIONS ADVANCED PACKAGE CAPABILITIES * DEFINER & INVOKER RIGHTS * WHITE LITS & ACCESSIBLE BY * PERSISTENT GLOBAL OBJECTS * DEFINING INITIALIZATION LOGIC * OBJECT ORIENTATION SUPPORT ADVANCED CURSOR TECHNIQUES * USING CUSROS VARIABLES * USING SYS_REFCURSOR * USING CURSOR EXPRESSIONS USING SYSTEM-SUPPLIED PACKAGE * DBMS_OUTPUT() * UTL_FILE() * FOPEN() EXAMPLE DATABASE TRIGGER CONCEPTS * ABOUT DATABASE TRIGGERS * DML EVENT TRIGGER SUB---TYPES * DATABASE TRIGGER SCENARIO * TRIGGER EXECUTION MECHANISMS * TRIGGERS WITHIN SQL WORKSHEET CREATING DATABASE TRIGGERS * STATEMENT-LEVEL TRIGGERS * Using RAISE_APPLICATION_ERROR() * ROW---LEVEL TRIGGERS * EXAMPLES OF TRIGGERS * EMPLOYEE_SALARY_CHECK Example * EMPLOYEE_JOURNAL Example * BUDGET_EVENT Example * INSTEAD OF TRIGGERS * TRIGGERS WITHIN AN APPLICATION MAINTAINING DATABASE TRIGGERS * CALL SYNTAX * TRIGGER MAINTENANCE TASKS * SHOW ERRORS TRIGGER * DROP TRIGGER * ALTER TRIGGER * MULTIPLE TRIGGERS FOR A TABLE * HANDLING MUTATING TABLE ISSUES IMPLEMENTING SYSTEM EVENT TRIGGERS * WHAT ARE SYSTEM EVENT TRIGGERS? * DEFININGTHE SCOPE * AVAILABLE SYSTEM EVENTS * SYSTEM EVENT ATTRIBUTES