Introduction And Principles To SQL Fundamentals 1 & 2
At Moyo Data Academy, we regard ourselves as astute data people. Our facilitators are experts in their respective disciplines. Apart from being exceptional trainers, they have vast experience in their respective fields of expertise. Our extensive knowledge of the intricate layers of the data world enables us to impart real understanding of how the entire data process works, before delving into the
detailed functionalities of a specific programme or system. This holistic approach to data training has elevated us to be a leading training facility.
SQL is regarded as the most popular language in the data world. It is also the most commonly used within various of technologies and leading organizations. Let’s assist you and your team to gear yourself with SQL knowledge to your toolkit to take advantage of the Analytical world. Structured Query Language (SQL) is the standard and most widely used programming language for relational
databases. It is used to manage and organize data in all sorts of systems in which various data relationships exist. SQL is a valuable programming language with strong career prospects.
Let’s assist you and your team to gear yourself with SQL knowledge to your toolkit to take advantage of the Analyticalworld.
Audience Overview:
This course is designed for beginner to intermediate-level SQL users. It is for anyone who works with data – regardless of technical or analytical background. During this course, you should be able to gain a basic understanding of what SQL is about and how to use the most common functionalities.
Typical users include:
- Analytical Teams or users that interact with the Analytical team’s data.
- Students looking to advance their careers in the data world.
- Report Writers / Builders – Users that will
Course Outcomes:
At the end of this course, you should be able to:
- Select Statement
- INSERT, UPDATE and DELETE
- How to use Stored Procedures
- How to create and modify Views
Course Outline – Introduction:
Setting the Scene
- What is SQL?
- Relationship Databases
- What are the Fundamentals of SQL?
- What are the Principals and Standards of SQL?
- How do you implement or install SQL?
- What is SQL Syntax
Course Outline – Modules:
Module 1 – Select Statements – Introduction and overview
- Write and Select the Basic SELECT Statement on Columns using WHERE, BETWEEN, NULL, ORDER, TOP, GROUP BY / ALL, DISTINCT, SELECT, INTO Clause etc.
- Querying from more than one Data Source using INNER, OUTER, CROSS Joins or performing self joins.
- Using Derived Tables, Combining Results by UNION, CROSS APPLY, etc
- Using APPLY to Invoke a Table-Value Function for each ROW, CROSS APPLY, OUTER APPLY
- Data Source Advance Techniques like TABLESAMPLE to return random Rows, PIVOT, Columns and aggregated Data and Normalizing Data with UNPIVOT, Return Distinct or Matching Rows using EXCEPT and INTERSECT, Summarizing or grouping Data with CUBE, Using Hints for Join, Query and Tables
Module 2: INSERT, UPDATE, DELETE
INSERT
- Inserting a Row into a Table
- Inserting a Row Using Default Values
- Inserting a Row into a Table with a Unique-identifier Column
- Inserting Rows Using an INSERT…SELECT Statement.
- Inserting Data from a Stored Procedure Call
UPDATE
- Updating a Single Row
- Updating Rows Based on a FROM and WHERE Clause
- Updating Large Value Data Type Column
- Inserting or Updating an Image File Using OPENROWSET
DELETE
- Deleting Rows
- Truncating a Table
- Using the OUTPUT Clause with INSERT, UPDATE, DELETE
- Chunking Data Modifications with TOP
- Deleting Rows in Chunks
MODULE 3: STORED PROCEDURES
- How to create a basic or parameterized Stored Procedure?
- Using OUTPUT Parameters
- Modifying a Stored Procedure
- Dropping Stored Procedures
- Executing Stored Procedures Automatically at SQL Server
- Startup
- Reporting Stored Procedure Metadata
- Documenting Stored Procedures
- Stored Procedure Security and Encrypting
- Using EXECUTE AS to Specify the Procedure’s Security Context
- Recompilation and Caching
- Flushing the Procedure Cache
Module 4: Views
- Overview
- Creating and Querying Views
- Reporting on Database Views
- Refreshing, modifying, and dropping views
- Encrypting a View
- Creating an Indexed View
- Forcing the Optimizer to Use an Index for an Indexed View
- Partitioned Views
What is Included?:
This course includes a workbook containing key concepts on each topic covered and hands-on activities to reinforce the skills and knowledge attained. It also includes a digital student resources
folder containing SQL workbooks and data sources to support the hands-on activities.
Duration:
- 16 hours facilitated virtually
- Presented over a 5-day period of 3-4 hours per day.
Prerequisites:
- Microsoft Teams
- Microsoft Excel installed
- Access to the SQL Sandbox /Training Environment (This will be provided once enrolled)