Kurzusleírás
Recap: SQL Functions and Expressions
- Character, numeric, DateTime functions
- Explicit and implicit conversion
- Conversion functions
- Nested functions
- Getting current date and time with different functions
- CASE expression
Aggregate data using aggregate functions
- Aggregate functions
- Aggregate functions vs NULL value
- GROUP BY clause
- Grouping using different columns
- Filtering aggregated data - HAVING clause
- Multidimensional data grouping - ROLLUP and CUBE operators
- Identifying summaries - GROUPING
- GROUPING SETS operator
- Crosstabs using PIVOT
Retrieving data from multiple tables
- Different types of joints
- Table aliases
- INNER JOIN
- LEFT, RIGHT, FULL OUTER JOINS
Set operators
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Subqueries
- When and where subquery can be done
- Single-row and multi-row subqueries
- Single-row subquery operators
- Aggregate functions in subqueries
- Multi-row subquery operators - IN, ALL, ANY
- Recursive subqueries
Analytic functions
- Use of
- Window functions, types of windows
- Partitions
- Ranking functions
- LAG/LEAD functions
- FIRST_VALUE/LAST_VALUE functions
- STRING_AGG function
- Statistical functions
Követelmények
Participants should have a good working knowledge of basic SQL and Microsoft SQL Server, including the ability to:
- Write basic
SELECTqueries to retrieve data from one or more tables. - Use
WHEREclauses and basic filtering conditions. - Work with common SQL functions, such as character, numeric and date functions.
- Understand basic data types and conversions.
- Use basic
JOINoperations. - Apply aggregate functions such as
COUNT,SUM,AVG,MINandMAX. - Understand and use
GROUP BYandHAVING. - Have some practical experience working with databases, data analysis or reporting.
This is an advanced-level course, so participants are expected to already be comfortable with fundamental SQL concepts before progressing to more complex topics such as subqueries, advanced aggregation, set operators and analytic/window functions.
Audience
This course is designed for data analysts and reporting application developers.
Vélemények (4)
az adatok személyre szabottá váltak a szervezetünk számára
Vincent Long - ASSMANG PTY LTD
Kurzus - T-SQL Fundamentals with SQL Server Training Course
Gépi fordítás
személyre szabott a megértésünk és az adataink alapján
Vincent Long - ASSMANG PTY LTD
Kurzus - Business Intelligence with SSAS
Gépi fordítás
Az oktató újra egyszerűen kiemelkedően teljesített, amikor szakértelmével, támogatással és kapcsolattartással együttműködött a munkatársaimmal az általunk testreszabott képzés során.
James - Shawnee Mission School District
Kurzus - Administering in Microsoft SQL Server
Gépi fordítás
A cte-ről szóló előadás
Glyssa Mae - Metropolitan Bank and Trust Company
Kurzus - Transact SQL Advanced
Gépi fordítás
