Pavel Nacev – CFI Education – SQL Fundamentals

Description

Pavel Nacev – CFI Education – SQL Fundamentals download, Pavel Nacev – CFI Education – SQL Fundamentals review, Pavel Nacev – CFI Education – SQL Fundamentals free

Pavel Nacev – CFI Education – SQL Fundamentals

SQL Fundamentals

Learn SQL’s foundational syntax and essential transformations with practical, hands-on examples.

Overview

SQL Fundamentals Course Overview

SQL plays a foundational role in Business Intelligence as it provides us with a way to extract data from databases for use in other BI tools.

This course covers SQL’s foundational syntax and essential transformations with practical, hands-on examples. To do this, learners are given access to an Azure Data Studio data warehouse from which they can run the queries taught throughout this course. Connecting to the data warehouse is simple and can be done from a Windows, iOS, or Linux computer.

This course will walk you through retrieving and filtering data from a database with basic SQL syntax, transforming that data with functions, combining data from multiple tables, best practices when using SQL, and common mistakes. By the end of this course, you’ll have the core skills business analysts need to extract data from a database using SQL.

SQL Fundamentals Learning Objectives

Upon completing this course, you will be able to:

• Retrieve and filter data using basic SQL syntax
• Transform data with the help of numerical, date, and text functions
• Identify strategies for constructing an unbiased sample
• Understand essential terminology related to SQL, databases, and data warehouses

Who should take this course?

The SQL Fundamentals is perfect for those with zero prior experience with SQL or for those who just want a refresher on the fundamentals and best practices. As with any coding language, SQL can be daunting, which is why this course starts with super simple examples and works slowly up to more complex theories. Every concept and step is explained clearly and with as non-technical language as possible.

What you’ll learn

Introduction
Course Outline
Student Downloads

Basic SQL Queries
Chapter Introduction
Installation and Introduction to Azure Data Studio
Creating a Connection to a Database
What is Adventure Works?
Example SQL Code
Saving Your Queries in Notepad
SELECT and FROM
SELECT Specific Columns
Creating a Column Alias
Using WHERE to Filter Rows
Checking the Impact of a WHERE Filter
Using GROUP BY to Combine Rows
Limiting Results to 1 Invoice Number for Testing
Using GROUP BY to Combine Invoice Numbers
Using HAVING to Filter Grouped Rows
Filtering Grouped Invoices with HAVING
SQL Order of Operations
Using ORDER BY to Sort Query Rows
Recap and Common Errors
Filtering Rows TOP N
Filtering Rows TOP N Percent
Filtering Rows OFFSET FETCH
FIltering Rows DISTINCT
ORDER BY Another Column
Chapter Summary & Student Exercise 1a
Student exercise 1a review
Student Exercise 1b
Student Exercise 1b Review
Student Exercise 1c
Student Exercise 1c Review

Manipulating Values with Functions
Chapter Introduction
Recap of Aggregate Functions
Counting Rows with COUNT( ) Aggregation
How Aggregate Functions Respond to NULL Values
The Importance of Data Types
Numeric Data Types
Numeric Functions
Where is the Boolean Data Type
Date and Time Data Types
Date Parts
Date and Time Functions
Date and Time Functions in Practice
The DATEADD Function
Working with Specific Dates
Server Properties Collation
Text or String Data Types
String Functions
The CONCAT String Function
LEN UPPER LOWER REPLACE String functions
Flexible LEFT and RIGHT Functions
Comparison Operators
Comparison Operators – Dealing with NULL
Logical Operators
Logical Operators – Common Errors
Advanced Logical Operators – IN and BETWEEN
IN and BETWEEN in Practice
Advanced Logical Operators – LIKE
LIKE in Practice
Using IIF Statements to Create a Conditional Column
Using a CASE Statement for Multiple Conditions
Basic SQL Formatting
Using IIF in a WHERE Statement
Replacing NULL Using IIF, ISNULL and COALESCE
Using CAST to Change the Data Type
Practical Examples Using CAST
Using a NATIONAL for UNICODE Best Practice
Chapter Review and Student Exercise 2a
Student Exercise 2a Review
Student Exercise 2b
Student Exercise 2b Review

SQL Theory
Chapter Introduction
A History of SQL
SQL Terminology
RDBMS Installation Options
Data Security
Types of Database System
Data Normalization in OLTP Systems
De-normalization in DW Systems
Fact and Dimension Tables
Relationships & Keys
The Star Schema
Snowflake Hybrid Schema

Working with Multiple Tables
Chapter Introduction
Relationships and ER Diagrams
Purpose of DW Relationships
Internet Sales Schema
Types of JOIN
A Basic INNER JOIN Using Sales and Customers
Returning Only the TOP 100 Customers
Order of Operations Review
INNER JOIN the Currency Table
HAVING or WHERE
When INNER JOIN Doesn’t Work
RIGHT JOIN to Retrieve Full Product Catalogue
LEFT JOIN vs RIGHT JOIN
Bridge Tables for Many-to-many Relationships
Creating JOINS Across Bridge Tables
Caution with Duplicate Data
Reading SQL Code
Appending Data with a UNION
Exploring the Reseller Schema
Creating a UNION between Internet and Reseller Sales
Identifying the Source of Each UNION Row
Using ORDER BY with a UNION
Other Types of JOIN
Creating a View
Querying a View
Creating Dynamic Results Using Subqueries
Chapter Summary and Student Exercise 4a
Exercise 4a Review
Student Exercise 4b
Student Exercise 4b Review

Advanced Functions and Queries
Chapter Introduction
Connecting to SQL Server from Power BI
Connecting to SQL from Tableau
Connecting to SQL Server from Excel
Using CUBE to Return Subtotals and Totals
Using ROLLUP to Return Subtotals and Totals
Common Scenario Percent of total
Introducing the Finance Schema
Student Exercise 5a
Student Exercise 5a Review
Student Exercise 5b
Student Exercise 5b Review

Qualified Assessment
Qualified Assessment

Frequently Asked Questions:

1. Innovative Business Model:
   • Embrace the reality of a genuine business! Our approach involves forming a group buy, where we collectively share the costs among members. Using these funds, we purchase sought-after courses from sale pages and make them accessible to individuals facing financial constraints. Despite potential reservations from the authors, our customers appreciate the affordability and accessibility we provide.
2. The Legal Landscape: Yes and No:
   • The legality of our operations falls into a gray area. While we lack explicit approval from the course authors for resale, there’s a technicality at play. When procuring the course, the author didn’t specify any restrictions on resale. This legal nuance presents both an opportunity for us and a boon for those seeking budget-friendly access.
3. Quality Assurance: Unveiling the Real Deal:
   • Delving into the heart of the matter – quality. Acquiring the course directly from the sale page ensures that all documents and materials are identical to those obtained through conventional means. However, our differentiator lies in going beyond personal study; we take an extra step by reselling. It’s important to note that we are not the official course providers, meaning certain premium services aren’t included in our package:
     • No coaching calls or scheduled sessions with the author.
     • No access to the author’s private Facebook group or web portal.
     • No entry to the author’s exclusive membership forum.
     • No direct email support from the author or their team.

   We operate independently, aiming to bridge the affordability gap without the additional services offered by official course channels. Your understanding of our unique approach is greatly appreciated.

Refund is acceptable:

• Firstly, item is not as explained
• Secondly, Item do not work the way it should.
• Thirdly, and most importantly, support extension can not be used.

Thank you for choosing us! We’re so happy that you feel comfortable enough with us to forward your business here.