Transact SQL Basicのトレーニングコース
Delegates will gain an understanding of the basic principles of Structured Query Language as well as being able to do each of the following:
- Construct queries to extract and filter data from an SQL Server database
- Create summarised results
- Change, derive and format data to suit the required output
- Change data and maintain database components and definitions
This course is for anybody who needs information from a Microsoft SQL Server database. It is suitable for either system developers or people from other areas who need to use SQL to extract and analyse data.
コース概要
Basics
- Selection of all columns/fields
- Selection of certain columns/fields
- Use of distinct/unique
- Selection of certain rows/records
- Selection of values in a range
- Selection of values matching a pattern mask
- Selection of values within a list
- Treatment of null values
- How to sort and order data
- Selection of calculated and derived values
- How to control column headings in query results
- How to send query results to external files
Joining Tables
- Principles of joining tables:
- Use of cartesian join
- Use of inner join
- Use of non-equi join
- Use of outer join
Joining Queries
- Union operator
- Intersect operator
- Except operator
Simple Functions
- Conversion functions
- Date functions
- Number functions
- Text functions
- Group/summary/aggregate functions
Sub-Queries
- Principles of sub-queries
- How to filter rows from main query
- Use of nested sub-query
- Use of multi-column sub-query
- Use of correlated sub-query
- Use of sub-query as an inline view and common table expression
- Use of sub-query as a column in main query
Case Statements
- Principles of case statements
- Use of case statement to derive column values
- Use of nested case statements
- Use of case statements to produce pivot tables
- Use of case statement with sub-queries
Data Manipulation
- How to insert values into a table
- How to copy values between tables
- How to update values
- How to delete records
- How to change data via views
- Use of transactions
- How to lock rows and tables
Data Definition
- Principles of a relational database and data normalisation
- Use of primary key and foreign key relationships and constraints
- How to create tables
- How to alter tables
- How to create views
- Use of synonyms
- How to remove tables and views
Open Training Courses require 5+ participants.
Transact SQL Basicのトレーニングコース - Booking
Transact SQL Basicのトレーニングコース - Enquiry
お客様の声 (6)
講義と演習のバランスがとてもよかったです。私が行き詰まったとき、ルークは問題を理解し、解決策に向けて取り組むのを非常に上手に助けてくれ、私の理解に本当に良い影響を与えてくれました。
Tom - Welsh Revenue Authority
コース - Transact SQL Basic
Machine Translated
ウカシュはとても良かったです。 Łukasz がベスト プラクティスについて語っているのが気に入りました。ペースはよかったです。練習が好きでした。将来の参照用に Wiki があると便利です
Jake Real - Welsh Revenue Authority
コース - Transact SQL Basic
Machine Translated
Lukasz は非常に忍耐強く知識が豊富で、SQL の学習を非常に楽しく有益にしてくれました。これは、私が参加することに喜びを感じたどの科目の中でも、間違いなく最もやりがいのあるトレーニング セッションの 1 つです。
Carwyn - Welsh Revenue Authority
コース - Transact SQL Basic
Machine Translated
Phil was great! He was both patient and knowledgeable. The training went at a good pace, moving a team of mixed SQL abilities through the basics into more advanced SQL querying together. The exercises were an excellent way for us to commit our newly learned skills to memory, and Phil's instructions/class discussions on numerous ways to achieve the same thing were interesting.
Sophie Clement - Thames Water Utilites Ltd
コース - Transact SQL Basic
It felt very relatable. The exercises were well picked out and I was able to work well with it due to clear knowledge provided.
Leah Bayliss - Thames Water Utilites Ltd
コース - Transact SQL Basic
Interactive exercises
Matthew Viner - Thames Water Utilites Ltd
コース - Transact SQL Basic
Upcoming Courses
関連コース
Microsoft Access – Downloading Data
14 時間This instructor-led, live training in 日本 (online or onsite) is aimed at beginner-level to intermediate-level professionals who wish to enhance their skills in extracting, analyzing, and manipulating data within Microsoft Access using queries and SQL.
By the end of this training, participants will be able to:
- Understand different types of queries in Microsoft Access.
- Use query design tools to extract and analyze data effectively.
- Write SQL queries to retrieve and manipulate data.
- Perform data aggregation, filtering, and sorting using queries.
- Apply advanced query techniques for troubleshooting and optimization.
Introduction to SQL Server 2012 Integration Services (SSIS)
28 時間This instructor-led, live training in 日本 (online or onsite) is aimed at beginner to intermediate-level developers and database administrators who wish to gain proficiency in SQL Server 2012 Integration Services.
By the end of this training, participants will be able to:
- Understand the principles of ETL and the role of SSIS.
- Create, deploy, and manage SSIS packages.
- Design and implement Control Flow to define the flow of tasks in a package.
- Implement Workflow and set up constraints for effective task execution.
- Construct Data Flows to extract, transform, and load data.
- Work with Variables and Containers to enhance package flexibility.
- Manage Transactions to ensure data consistency.
- Implement effective error handling and debugging techniques.
- Set up Logging for tracking and monitoring.
- Handle Slowly Changing Dimensions for managing changing data.
- Deploy a Package for production use.
- Implement Security measures to protect sensitive data.
- Utilize Scripting to extend SSIS functionality.
- Apply Best Practices for efficient and maintainable SSIS solutions.
SQL Advanced
14 時間This instructor-led, live training in 日本 (online or onsite) is aimed at intermediate-level database administrators, developers, and analysts who wish to master advanced SQL functionalities for complex data operations and database management.
By the end of this training, participants will be able to:
- Perform advanced querying techniques using unions, subqueries, and complex joins.
- Add, update, and delete data, tables, views, and indexes with precision.
- Ensure data integrity through transactions and manipulate database structures.
- Create and manage databases efficiently for robust data storage and retrieval.
Which data storage to choose - from flat files, through SQL, NoSQL to massive distributed systems
7 時間This course helps customer to chose the write data storage depend on their needs. It covers almost all possible modern approaches.
SQL Advanced level for Analysts
21 時間The aim of this course is to provide a clear understanding of the use of SQL for different
databases (Oracle, SQL Server, MS Access...). Understanding of analytic functions and the
way how to join different tables in a database will help delegates to move data analysis
operations to the database side, instead of doing this in MS Excel application. This can also
help in creating any IT system, which uses any relational database.
SQL Fundamentals
14 時間This SQL training course is for people who want to gain the necessary skills to extract and analyse data from any database and create reports. Course members will learn:
- how to write SQL queries
- what relational databases are and how we can use them
- what are relations and how to create them
- the structure of data
- differences between SQL dialects (Oracle, T-SQL, ANSI)
- practical skills for writing queries
This SQL course deals with generic ANSI SQL. It can be used in any database, including Oracle, MySQL, Microsoft Access, Microsoft SQL Server, DB2, Informix, PostgreSQL any other relational databases.
SQL in Microsoft Access
14 時間This SQL training course is for people who want to gain the necessary skills to extract and analyse data using Microsoft Access. Course members will learn:
- how to write SQL queries
- what relational databases are and how we can use them
- what are relations and how to create them
- the structure of data
- differences between SQL dialects (Oracle, T-SQL, ANSI)
- practical skills for writing queries
This SQL course deals with Microsoft Access dialect of SQL.
SQL language in MSSQL
14 時間The course answers questions
- How to build a query?
- What opportunities have SQL?
- What is a relational database?
- What is the structure and SQL commands?
SQL in MySQL
14 時間- How to build a query?
- What is a relational database?
- What is the structure and SQL commands?
SQL Advanced in MySQL
7 時間This course has been created for people already acquainted with SQL. The course introduces you into secrets common to all SQL databases as well as MySQL specific syntax, functions and features.
Data Analysis with SQL, Python and Spotfire
14 時間In this instructor-led, live training in 日本, participants will learn three different approaches for accessing, analyzing and visualizing data. We start with an introduction to RDMS databases; the focus will be on accessing and querying an Oracle database using the SQL language. Then we look at strategies for accessing an RDMS database programmatically using the Python language. Finally, we look at how to visualize and present data graphically using TIBCO Spotfire.
Format of the Course
Interactive lecture and discussion.
Lots of exercises and practice.
Hands-on implementation in a live-lab environment.
SQL in SQL Server
14 時間This SQL training course is for people who want to gain the necessary skills to extract and analyse data from any database and create reports. Course members will learn:
- how to write SQL queries
- what relational databases are and how we can use them
- what are relations and how to create them
- the structure of data
- differences between T-SQL and other dialects
- practical skills for writing queries
This SQL course deals with Microsoft T-SQL dialect. If you are interested in generic SQL, please see SQL Fundamentals course.
Business Intelligence with SSAS
14 時間SSAS (SQL Server Analysis Services), is a Microsoft SQL Server transactional processing (OLAP) and data mining tool for analyzing data across multiple databases, tables or files. The semantic data models provided by SSAS are used by client applications such as Power BI, Excel, Reporting Services, and other data visualization tools.
In this instructor-led, live training (onsite or remote), participants will learn how to use SSAS to analyze large volumes of data in databases and data warehouses.
By the end of this training, participants will be able to:
- Install and configure SSAS
- Understand the relationship between SSAS, SSIS, and SSRS
- Apply multidimensional data modeling to extract business insights from data
- Design OLAP (Online Analytical Processing) cubes
- Query and manipulate multidimensional data using the MDX (Multidimensional Expressions) query language
- Deploy real-world BI solutions using SSAS
Audience
- BI (Business Intelligence) professionals
- Data Analysts
- Database and data warehousing professionals
Format of the Course
- Interactive lecture and discussion
- Lots of exercises and practice
- Hands-on implementation in a live-lab environment
Course Customization Options
- This training is based on the latest version of Microsoft SQL Server and SSAS.
- To request a customized training for this course, please contact us to arrange.
Introduction to SQL Server 2022 Integration Services (SSIS)
28 時間This instructor-led, live training in 日本 (online or onsite) is aimed at beginner-level data professionals who wish to learn how to design, implement, and manage SSIS packages to perform data integration and transformation tasks.
By the end of this training, participants will be able to:
- Understand the architecture and components of SSIS.
- Design and implement ETL processes using SSIS.
- Use SSIS tools to develop, deploy, and manage data integration solutions.
- Troubleshoot and optimize SSIS packages for performance and reliability.
Transact SQL Advanced
7 時間Delegates will gain an understanding of some of the more advanced features of Transact SQL as well as being able to do each of the following:
- Use queries to return complex result sets
- Manage database objects to aid query performance
- Tune queries to perform more efficiently
This course is for anyone who currently uses Transact SQL to extract data from a Microsoft SQL Server database and wishes to expand their knowledge particularly in the areas of data analysis and improving query speed.