Database Development & Administration
Oracle
ECOSY 0563 Oracle SQL Training ECOSY 0563
Mastering Oracle SQL is one today's most marketable programming skills for end-to-end developers. This online, self-paced course covers the basics of Oracle SQL. You will learn the fundamentals, including queries, procedures, and functions through hands-on assignments. By course completion, your SQL skills will be on par with industry standards.
What you will learn
- How tables are structured and how data is stored
- To use Oracle to output reports and to use SQL functions
- To group data to get aggregate values
- To write joins and subqueries to get data from multiple tables and to write INSERT, UPDATE, and DELETE statements
- To use SET operators and to do conditional processing with CASE
How you will benefit
- Gain skills that every developers needs
- Bring your SQL skills to any web development job
Syllabus
- Relational Database Basics
- Creating Tables
- Basic Selects
- Oracle SQL Functions
- Aggregate Functions
- Subqueries
- Set Operators
- Conditional Processing with CASE
- Data Manipulation Language
- Creating Views
Requirements
Prerequisites:
Prior to enrolling in this course, we recommend having an intermediate understanding of SQL or taking the Introduction to SQL Training course.
Requirements:
Hardware Requirements:
- This course must be taken on a PC. Macs are not compatible.
Software Requirements:
- PC: Windows 10 or later.
- Oracle Database Express Edition (XE) 12c or 18c - Free edition of Oracle RDBMS.
- Oracle SQL Developer - The client interface for connecting to Oracle. (Software not included)
- Browser: The latest version of Google Chrome or Mozilla Firefox are preferred. Microsoft Edge is also compatible.
- Adobe Acrobat Reader.
Other:
- Software must be installed and fully operational before the course begins.
Instructional Material Requirements:
The instructional materials required for this course are included in enrollment and will be available online.
ECOSY 0564 Oracle PL/SQL Training ECOSY 0564
This self-paced SQL training course is perfect for advancing your current SQL skills. While Oracle uses a standard SQL like other major databases, PL/SQL is specific to Oracle. PL/SQL is a procedural language with variables, conditions, loops, subprograms, stored procedures, and functions. By course completion, you will understand and know how to use PL/SQL's main features, including declaring, naming, and assigning variables.
Syllabus
- PL/SQL Basics
- What is PL/SQL?
- Blocks
- Outputting Information
- Variables and Constants
- Constants
- Data Types
- Naming Variables and Other Elements
- Embedding SQL in PL/SQL
- SELECT...INTO and RETURNING...INTO
- PL/SQL Features
- Subprograms
- Introduction to Subprograms
- Procedures
- Variable Declarations
- Dropping a Procedure
- Parameters
- Parameters with Default Values
- Parameter Modes
- IN Mode
- OUT Mode
- IN OUT Mode
- Named Notation
- Using SQL in a Subprogram
- %TYPE
- Functions
- Using PL/SQL Functions in SQL Queries
- Conditional Processing
- Conditions and Booleans
- IF-ELSIF-ELSE Conditions
- ELSIF
- BOOLEAN Values and Standard SQL
- The CASE Statement
- CASE Expressions
- Exceptions
- Introduction to Exceptions
- Predefined Exceptions
- The EXCEPTION Part of the Block
- User-defined Exceptions
- User-defined Exceptions in Subprograms
- Re-raising Exceptions
- Naming Unnamed Predefined Exceptions
- WHILE Loops
- When to Use Exceptions
- Cursors
- Implicit Cursors
- Explicit Cursors
- %ROWTYPE
- Explicit Cursor Use Case
- Cursor FOR LOOP
- Cursor Parameters
- Packages
- Package Basics
- The Package Specification
- The Package Body
- Building an Employee Package
- Overloading Subprograms
- Auditing
- Validation Procedures
- Package Cursors
- Benefits of Packages
- Triggers
- What are triggers?
- Trigger Parts
- Validation Triggers
- The WHEN Clause
- Audit Triggers
- Statement-level Triggers
- Compound Triggers
- Trigger Warning
Requirements
Prerequisites:
Prior to enrolling in this course, we recommend having an intermediate understanding of SQL or taking the Oracle SQL Training course.
Requirements:
Hardware Requirements:
- This course must be taken on a PC. Macs are not compatible.
Software Requirements:
- PC: Windows 10 or later.
- Oracle Database Express Edition (XE) 12c or 18c - Free edition of Oracle RDBMS.
- Oracle SQL Developer - The client interface for connecting to Oracle. (Software not included)
- Browser: The latest version of Google Chrome or Mozilla Firefox are preferred. Microsoft Edge and Safari are also compatible.
- Adobe Acrobat Reader.
Other:
- Software must be installed and fully operational before the course begins.
Instructional Material Requirements:
The instructional materials required for this course are included in enrollment and will be available online.
WITAC 2512 ANCHOR
WITAC 2512 - DESIGNING BUSINESS INTELLIGENCE SOLUTIONS WITH MICROSOFT SQL SERVER 2016 (70-467) WITAC 2512
PLAN BUSINESS INTELLIGENCE (BI) INFRASTRUCTURE
- Plan for performance
- Optimize batch procedures: extract, transform, load (ETL) in SQL Server Integration Services (SSIS)/SQL and processing phase in Analysis Services; configure Proactive Caching within SQL Server Analysis Services (SSAS) for different scenarios; understand performance consequences of Unified Dimension Model (UDM) and Data Warehouse (DWH) design; analyze and optimize performances of Multidimensional Expression (MDX) and Data Analysis Expression (DAX) queries; optimize queries for huge data sets; understand the difference between partitioning for load performance versus query performance in SSAS; appropriately index a fact table; optimize Analysis Services cubes in UDM; create aggregations using Usaged Based Optimizations
- Plan for scalability
- Multidimensional OLAP (MOLAP); Relational OLAP (ROLAP); Hybrid OLAP (HOLAP)
- Plan and manage upgrades
- Plan change management for a BI solution
- Maintain server health
- Design an automation strategy
DESIGN BI INFRASTRUCTURE
- Design a security strategy
- Configure security and impersonation between database, analysis services and frontend; implement Dynamic Dimension Security within a cube; configure security for an extranet environment; configure Kerberos security; skills in authentication mechanisms, ability to build secure solutions end to end; design security roles for calculated measures; understand the tradeoffs between regular SSAS security and dynamic security; plan and implement security requirements of a BI solution
- Design a SQL partitioning strategy
- Choose the proper partitioning strategy for the data warehouse and cube; implement a parallel load to fact tables by using partition switching; use data compression in fact tables
- Design a backup strategy
- Design a High Availability (HA) and Disaster Recovery (DR) trategy; proactively prevent issues
- Design a logging and auditing strategy
- Design a new SSIS logging infrastructure (for example, information available through the catalog views); validate data is balancing and reconciling correctly
DESIGN A REPORTING SOLUTION
- Design a Reporting Services dataset
- Data query parameters; creating appropriate SQL queries for an application (MDX queries); managing data rights and security; extracting data from analysis services; balancing query-based processing versus filter-based processing; managing data sets through the use of stored procedures
- Manage Excel Services/reporting for SharePoint
- Configure data refresh schedules for PowerPivot published to SharePoint; publish BI info to SharePoint; use SharePoint to accomplish BI administrative tasks
- Design a data acquisition strategy
- Identify the data sources that need to be used to pull in the data; determine the changes (incremental data) in the data source (time window); identify the relationship and dependencies between the data sources; determine who can access which data; what data can be retained for how long (regulatory compliance, data archiving, aging); design a data movement strategy; profile source data
- Plan and manage reporting services configuration
- Native mode
- Design BI reporting solution architecture
- Linked reports, drill-down reports, drill-through reports, migration strategies, access report services API, sub reports, code-behind strategies; identify when to use Reporting Services, Report Builder, or Crescent; design/implement context transfer when interlinking all types of reports (RS, RB, Crescent, Excel, PowerPivot); implement BI tools for reporting in SharePoint (Excel Services versus Performance Point versus Reporting Services); select a subscription strategy
DESIGN BI DATA MODELS
- Design the data warehouse
- Design a data model that is optimized for reporting; design and build a cube on top; design enterprise data warehouse (EDW) and OLAP cubes; choose between natural keys and surrogate keys when designing the data warehouse; use the facilities available in SQL Server to design, implement and maintain a data warehouse (partitioning, slowly changing dimensions (SCD), change data capture (CDC), Clustered Index Views, etc.); identify design best practices; implement a many to many relationship in an OLAP cube; design a data mart/warehouse in reverse from an Analysis Services cube (or empty Analysis Services cube that was created referring requirements); use rowstamp in the data warehouse; choose between performing aggregation operations in the SSIS pipeline or the relational engine; select surround architecture
- Design a schema
- Multidimensional modeling starting from a star schema; relational modeling for a Data Mart; choose or create a topology
- Design cube architecture
- Produce efficient aggregated cubes; partition cubes and build aggregation strategies for the separate partitions; design a data model; choose the proper partitioning strategy for the data warehouse and cube; design the data file layout for a data warehouse keeping maximum performance in mind; given a requirement, identify the aggregation method that should be selected for a measure in a MOLAP cube; design cube aggregations to maintain a balance between storage and performance; performance tune a MOLAP cube using aggregations; design a data source view; cube drill-through and write back actions
- Design fact tables
- Design a data warehouse that supports many to many dimensions with factless fact tables
- Design BI semantic models
- Plan for a multidimensional cube; write a UDM model with many to many (this is related to MDX/BISM code, but it is a good example for exercises); choose between UDM and BISM depending on the type of data and workload
- Design and create MDX calculations
- MDX authoring; identify the structures of MDX and the common functions (tuples, sets, topcount, SCOPE etc.); identify which MDX statement would return the required result (single result and multiple MDX options provided to test taker); implement a custom MDX or logical solution for a pre-prepared case task
DESIGN AN ETL SOLUTION
- Design SSIS package execution
- Using new project deployment model; passing values at execution time; share parameters between packages
- Plan to deploy SSIS solutions
- Deploy the package to another server with different security requirements; secure integration services packages that are deployed at the file system; demonstrate awareness of SSIS packages/projects and how they interact with environments; decide between performing aggregation operations in the SSIS pipeline or the relational engine
- Design package configurations for SSIS packages
- Avoid repeating configuration information entered in SSIS packages and use configuration files
TEXTBOOK INFORMATION:
Pro SQL Server 2012 BI Solutions Paperback by Randal Root (Author), Caryn Mason (Author)
ISBN-13: 978-1430234883 ISBN-10: 1430234881 Edition: 1st
WITAC 2513 ANCHOR
WITAC 2513 - Querying Microsoft SQL Server (54 HRS) WITAC 2513
This instructor led course provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server. This course is the foundation for all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence. The main purpose of the course is to prepare students for the exam "70-761: Writing Queries Using Microsoft® SQL Server® 2016 Transact-SQL." This exam will be the underlying exam for all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence. As such, the primary target audience for this course is: Database Administrators, Database Developers and BI professionals.
AFTER COMPLETING THIS COURSE, YOU WILL BE ABLE TO:
- Write SELECT queries
- Query multiple tables
- Use built-in functions
- Use subqueries
- Execute stored procedures
- Use set operators
- Implement error handling
- Implement transactions
- Use table expressions
- Sort and filter data
- Use window ranking, offset and aggregate functions
- Query SQL Server metadata
- Program with T-SQL
- Improve query performance
COURSE INFORMATION
Hours 54
Textbook: Exam Ref 70-761 Querying Data with Transact-SQL with Practice Test ISBN 978-1-5093-0372-4
WITAC 2514 ANCHOR
WITAC 2514 - ADMINISTERING MICROSOFT SQL SERVER 2016 DATABASES (54 HRS) WITAC 2514
AFTER COMPLETING THIS COURSE, STUDENTS WILL BE ABLE TO
- Plan and install SQL Server.
- Describes the system databases, the physical structure of databases and the most common configuration options related to them.
- Explain the concept of the transaction log and SQL Server recovery models and implement different backup strategies available with SQL Server.
- Create SQL Server Backups.
- Restore SQL Server databases.
- Use the import/export wizards and explain how they relate to SSIS.
- Work with SQL Server security models, logins and users.
- Work with fixed server roles, user-defined server roles, fixed database roles and user-defined database roles.
- Work with permissions and the assignment of permissions.
- Work with SQL Server Audit.
- Work with SQL Server Agent, jobs and job history.
- Implement SQL Server agent security, proxy accounts and credentials.
- Configure database mail, alerts and notifications.
- Create database maintenance plans.
- Work with SQL Profiler and SQL Trace stored procedures.
- Introduce DMVs and the configuration of data collection.
- Work with Central Management Servers and Multi-Server queries, Virtualization of SQL Server and Data-Tier Applications.
- Troubleshoot SQL Server databases.
COURSE INFORMATION
Text: Training Kit (Exam 70-764): Administering Microsoft SQL Server 2016 Databases (Microsoft Press Training Kit), ISBN 978-0-73566607-8.
WITAC 2515 ANCHOR
WITAC 2515 - IMPLEMENTING A DATA WAREHOUSE WITH MICROSOFT SQL SERVER 2016 (54 HRS) WITAC 2515
Data warehousing is a solution organizations use to centralize business data for reporting and analysis. This instructor-led course focuses on teaching you how to create a data warehouse with SQL Server 2012, implement ETL with SQL Server Integration Services, and validate and cleanse data with SQL Server Data Quality Services and SQL Server Master Data Services. This course helps you to prepare for exam 70-767.
- Implementing a data warehouse.
- Developing SQL Server Integration Services (SSIS) packages for data extraction, transformation, and loading (ETL).
- Enforcing data integrity by using Master Data Services.
- Cleansing data by using Data Quality Services.
AFTER COMPLETING THIS COURSE, STUDENTS WILL BE ABLE TO
- Describe data warehouse concepts and architecture considerations.
- Select an appropriate hardware platform for a data warehouse.
- Design and implement a data warehouse.
- Implement Data Flow in an SSIS Package.
- Implement Control Flow in an SSIS Package.
- Debug and Troubleshoot SSIS packages.
- Implement an SSIS solution that supports incremental data warehouse loads and changing data.
- Integrate cloud data into a data warehouse ecosystem infrastructure.
- Implement data cleansing by using Microsoft Data Quality Services.
- Implement Master Data Services to enforce data integrity.
- Extend SSIS with custom scripts and components.
- Deploy and Configure SSIS packages.
- Describe how information workers can consume data from the data warehouse.
COURSE INFORMATION
Text: Implementing a Data Warehouse with Microsoft SQL Server 2012 (Microsoft Press Training Kit), ISBN# 978-0-7356-6609-2.
WITAC 2516 ANCHOR
WITAC 2516 - Introduction to Sql - Basics 52 (HRS) WITAC 2516
Course Description:
Master T-SQL fundamentals and write robust code for Microsoft SQL Server and Azure SQL Database. The course explains key T-SQL concepts and helps you apply your knowledge with hands-on exercises. Introduces T-SQL’s roots and underlying logic. The course walks you through core topics such as single-table queries, joins, subqueries, table expressions, and set operators. Then the course covers more-advanced data-query topics such as window functions, pivoting, and grouping sets. The course also explains how to modify data, work with temporal tables, and handle transactions, and provides an overview of programmable objects.
Textbook: Itzik Ben-Gan, T-SQL Fundamentals 3rd Edition 97801509302000