SQL, Oracle, Access courses

Power BI 1.

• Application description

Getting Data from Various Sources

• from Excel from text files from the web from a database

Editing Data in the Power Query Editor

Creating a Data Model

• multiple tables in the data model Relationships between tables Date table Hierarchy Selected DAX functions - row, table Weights

Data Visualization

• different visuals - table, matrix charts visual formatting show / hide details

Principles When Creating a Database Table

• limitations of Excel and their possible solutions data validation in a database table

Logical Functions

• IF, nested IF AND, OR

Lookup Functions

• INDIRECT LOOKUP, VLOOKUP HLOOKUP, CHOOSE MATCH, INDEX

Database Functions

• DSUM, DAVERAGE DMIN, DMAX DCOUNT, DSTDEV etc.

Formula Debugging

• watch window mode and formula checking panel arrows, Evaluate Formula tools error checking

Power BI -2

Calculations in the Data Model

• new column in a table
• calculated field summary calculations for the whole table
• measures

Functions Using Relationships Between Tables

• Related
• RelatedTable

Group of X Functions

Measures, Calculation Context, Filters and Anti-Filters in the DAX Language

Getting Familiar with the Microsoft SQL Server Environment

• Installation of SQL Server and SQL Server Management Studio
• Creating a database
• Creating a table, data types
• Entering data into a table
• Changing data
• Displaying a table
• Deleting a table

Connection with MS Excel and MS Access

Creating and Editing Databases

• Relational relationships between tables
• Primary keys, foreign keys
• Ensuring data integrity
• Database diagram
• Creating views, advantages of their use

Creating a Backup

SQL Language Syntax

• SELECT, CREATE TABLE, INSERT, UPDATE, DROP TABLE commands
• Filtering data using the WHERE command
• Logical operations
• Creating compound conditions using AND and OR
• NOT, IN, BETWEEN commands
• LIKE operator and using wildcard characters
• Sorting using the ORDER BY command
• Changing table columns using the ALTER TABLE command
• Deleting rows using the DELETE command
• Setting constraints for columns
• Joining tables using INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN commands
• Aggregate functions - SUM, AVG, COUNT, MIN, MAX

Review of Knowledge from the Working with a Database I Course

• Review of basic commands - SELECT, CREATE, INSERT, UPDATE, DELETE, DROP

Primary Keys, Foreign Keys and Data Normalization

Data Operations

• Nested queries and subqueries
• Working with text functions - LTRIM, RTRIM, UPPER, LOWER, LEN
• Cleaning and manipulation of text data - LEFT, RIGHT, SUBSTRING, REPLACE, CONCAT, CHARINDEX, REPLICATE, STUFF

Data Types and Formats

• Conversion between data types - CONVERT, CAST, TRY_CAST, TRY_CONVERT
• Working with date and time formats - GETDATE, DATEADD, DATEDIFF, FORMAT

Joining and Combining Tables

• Types of JOIN operations - CROSS JOIN, SELF JOIN

Course Syllabus

• In detail about all database objects (tables, indexes, triggers; views; rules; default values, data types; user-defined data types).
• In detail about data types (Binary, Character, Date and time, Exact numeric, Approximate numeric; Integer, Monetary, Special, Text and Image).
• Extended stored procedures.
• In detail about some system tables.
• Parts of Transact SQL.
• In detail about SQL Server security (login names; user names in the database; rights for individual objects; login modes; security and system tables).
• Installation and configuration of SQL Server.
• SQL Server administration (viewing active processes; kill; bcp program and other utilities; database backup).
• Replication.
• Introduction to the interface for C and Visual Basic.

Course Syllabus

• Overview of the SQL Server Management Studio environment and logging in to a database server.
• SQL Server Management Studio functions supporting productivity.
• Introduction to the T-SQL language, batch, GOTO, transaction, dirty pages, commit, Roll-back.
• Overview of data types and their use in practice.
• Converting data types.
• Local and global variables.
• SELECT command in detail, SELECT TOP, SELECT COUNT (*), aliases, nested SELECT.
• NULL value and its use in conditions.
• Sorting using ORDER BY, Collation.
• WHERE condition.
• Searching with LIKE.
• GROUP BY, HAVING clause.
• CASE expression.
• Joining multiple tables using JOIN (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, SELF JOIN).
• Working with text.
• VIEWS.
• UNION and UNION ALL operators.
• STORED PROCEDURE.
• Programming (loops, branching, working with error states).
• Triggers.
• Azure Data Studio.

Syllabus

• Installation of MS SQL Server, SQL Server configuration, user account administration, security, database backup and recovery, monitoring and optimization of SQL Server performance, data import and export (Reporting Services, Business Intelligence Developer Studio), discussion, conclusion

Introduction to Relational Databases

• Database terminology, principles of relational model design, normalization, database optimization, familiarization with Oracle 10g technology, tools and environment

Introduction to the SQL Language

• SQL standards and Oracle SQL implementation, data types in Oracle databases

DML - Working with Data

• Data selection, basic syntax of the SELECT command, projection - selection of columns and use of column aliases, limiting selected records with the WHERE clause, use of BETWEEN, IN, IS NULL, LIKE operators, sorting records with the ORDER BY clause, eliminating duplicate rows (DISTINCT), creating expressions, use of arithmetic and text operators, grouping data (GROUP BY and HAVING clauses), nested SQL - single-row queries, multi-row queries (ALL, ANY, IN operators), correlated queries, use of pseudocolumns, selecting data from multiple tables in sequence (UNION, UNION ALL), intersection (INTERSECT) and difference (MINUS) of two tables, selecting data by inner (WHERE, INNER JOIN) and outer table join (LEFT, RIGHT and FULL OUTER JOIN), inserting data, direct data insertion into a table (INSERT), inserting data by selection from another table, checking uniqueness of inserted data, modifying data, modifying all rows and selected rows (UPDATE), updating with a constant value, function result, value derived from the original value, value derived from another column of the same table, or value from another table, deleting data, deleting all and selected rows (TRUNCATE, DELETE), deleting duplicate rows, use of functions, single-row functions (numeric functions, functions for working with text, date and time functions, conversion functions), aggregate functions, analytic functions, other functions

DDL - Creating and Modifying Database Structures

• Creating (CREATE TABLE) and changing table structure (ALTER TABLE), creating and changing views (CREATE OR REPLACE VIEW), integrity constraints, materialized views, indexes, sequences, deleting objects (DROP)

Transaction Control

• Committing transactions (COMMIT), rolling back changes (ROLLBACK), creating savepoints (SAVEPOINT)

Nested Queries

• single-row nested queries in WHERE and HAVING clauses nested queries in the FROM clause (inline views) multi-row nested queries (ALL, ANY, IN, EXIST operator) use of multi-column subqueries correlated subqueries

Advanced Aggregate Queries

• queries with the ROLLUP clause use of the CUBE clause

Analytic Functions

• use of analytic functions functions for determining order (ranking functions) RANK and DENSE_RANK cumulative distribution (CUME_DIST) NTILE function WIDTH_BUCKET function determining values corresponding to a specified percentile (PERCENTILE_CONT,

PERCENTILE_DISC

• cumulative and moving aggregate functions (window functions) cumulative sum moving average LAG and LEAD FIRST_VALUE and LAST_VALUE FIRST and LAST expressions from CASE hierarchical queries

Introduction to PL/SQL

• PL/SQL blocks: basic block structure, anonymous and named blocks, nested blocks, data types, using variables, expressions

Controlling Program Execution in PL/SQL

• Conditions (IF-THEN-ELSE, CASE), loops (FOR, WHILE), sequential navigation (GOTO)

Using SQL in PL/SQL

• Retrieving data, transaction control, working with cursors, DML and DDL in PL/SQL, built-in SQL functions

Records

• Implicit and explicit definition of record type, use of records

Collections

• Working with collections, Oracle Collection API

Basic Oracle Terminology

• database, instance, Oracle software

Database Administration Tools

• Oracle Enterprise Manager, SQLPlus) use of OFA (optimal flexible architecture) architecture software installation using Oracle Universal Installer (OUI)

Creating an Oracle Database

• using Database Configuration Assistant (DBCA) creating a database design template using DBCA generating scripts for creating a database using DBCA

Starting and Stopping the Oracle Database and Components

• description of an instance (the most important memory structures and processes, description of their operation) database initialization parameters Alert log working with Data Dictionary (most frequently used views) description of data structures tablespace and data files OMF (Oracle Managed Files) basic concept of automatic storage management (ASM)

Creating and Managing Database User Accounts

• user authentication granting and revoking system and object privileges, roles and profiles controlling user resource usage managing data using SQL identification and administration of PL/SQL objects description of triggers and triggering events (in more detail in the PL/SQL module) monitoring and resolving locking conflicts explanation of DML and generation of undo data

Principles for Creating a Database Table

• Excel limitations and their possible solutions.
• Data validation in a database table.

Logical Functions

• IF, nested IF.
• AND, OR.

Lookup Functions

• INDIRECT.
• LOOKUP, VLOOKUP.
• HLOOKUP, CHOOSE.
• MATCH, INDEX.

Database Functions

• DSUM, DAVERAGE.
• DMIN, DMAX.
• DCOUNT, DSTDEV etc.

Debugging Formulas

• Watch window.
• Formula checking mode and panel.
• Arrows, tools.
• Evaluate formula.
• Error checking.