SQL and Data Analysis
SU-245(3 days)
SQL and Data Analysis
15.600 DKK
(excl. VAT)
Course calendar
Description
This is an advanced SQL course based on the SQL standard, which makes it relevant whether you work with Oracle, IBM Db2, Microsoft SQL Server, PostgreSQL or Teradata.
The purpose of the course is to expand your toolkit with advanced SQL techniques, giving you even more options to draw on and making you more creative in turning strategy into efficient SQL code. This is also an important prerequisite for performance tuning SQL statements.
The course is built around three main themes plus supplementary topics, which are included depending on participants' wishes and the time available.
- The first main theme is classic set comparisons. We dig into the core of SQL with solid set operations that solve a wide range of practical reporting tasks. The focus is on analyzing the specific report requirement so the right set operation can be identified and turned into SQL code.
- The second main theme is window functions, which solve many classic problems in an elegant, readable and often better-performing way. Window functions are the main theme of the course and are covered thoroughly with many examples and hands-on exercises, so you become comfortable with both the syntax and how to use them.
- The third main theme is SQL queries on intervals, including conditions for overlapping periods and assigning values to intervals, such as interest rate, principal, seniority and remaining term. This type of interval logic is especially relevant in financial SQL, for example for securities, deposits and loans, and leasing periods.
Depending on the pace and participants' wishes, we can also cover a number of supplementary topics.
These include the GROUP BY extensions GROUPING SETS, CUBE and ROLLUP, which are especially relevant in statistical reports, data warehousing and OLAP analysis – and which, used correctly, can improve performance.
We also look at correlated DELETE and UPDATE, where data in one table is changed based on data from another. Here we start with an UPSERT and then introduce the MERGE command. MERGE is often used in data warehousing.
Finally, we look at recursive queries, which the SQL standard handles with recursive Common Table Expressions (CTEs), and which make it possible to solve tasks that would otherwise require loops or procedures.
Related exam
Prerequisites
To get the most out of the course, we recommend experience or knowledge equivalent to:
Certification bundles
Module overview
SQL is based on set theory. Among the SQL language's famous attractions is its elegant handling of set comparisons, which can solve advanced report requirements.
If you recognize the set operation in the report requirement, you have a good pointer toward a possible strategy for writing suitable SQL code.
The module presents classic set operations – both graphically in Venn diagrams and with elegant, correlated HAVING and/or EXISTS constructs to solve them. SQL is built to do this with good performance.
Examples of set comparisons:
Disjoint sets – the empty intersection
- Which purchasing Customers X have not bought any of the Products that Customer A has bought?
- Which Customers X have only bought something in the years in which Customer A did not buy anything?
Superset
- Which Customers X have bought at least the same Products as Customer A?
Subset
- Which Customers X have bought from among the Products bought by Customer A – where Customer A has bought all the Products that Customer X has bought?
Identical sets
- Which Customers X have bought exactly the same Products as Customer A?
The SQL:1999 standard's extension of GROUP BY with GROUPING SETS, CUBE and ROLLUP. Implemented for handling data in statistical reports and data warehouses.
A powerful addition to reporting, since you specifically select the levels at which to group data. This can give a performance gain compared with grouping at all levels and combining aggregates later.
Definition – what does the “window” consist of?
4 types of Window Functions
- Aggregate / Ranking / (Distribution) / Offset
- Each of these types is presented with usage examples
The OVER clause and its levels
- Partitioning / Ordering / Framing
- Each of these levels is covered thoroughly with usage examples
Partitioning
- Calculating fractions – e.g. percentage calculations
Ordering
- Ranking functions
- ROW_NUMBER() - RANK() - DENSE_RANK()
- Top-N per group - with/without WITH TIES
Framing
- ROWS vs RANGE
- Running values – moving averages, Year-to-Date
- Running aggregates -- “running values”
- E.g. rolling 7-day average deposits, Year-To-Date
Analytic functions
- LEAD() – LAG() – FIRST_VALUE() – LAST_VALUE()
- RESPECT NULLS vs IGNORE NULLS
- (Relative) increases
Intervals from single values
- Consecutive intervals without missing values
- Intervals between the intervals ("gaps")
- Returning the list of missing values – which sequence numbers / months are not in use
- Calculating seniority, customer loyalty, etc.
Statistics on intervals
Overlapping intervals
- The condition for overlap
- Overlap between individual intervals vs grouped intervals
- Returning overlaps
- Counting overlapping intervals
- Assigning a value to intervals – returning aggregates
- Maximum number of overlapping intervals (highest number of concurrent customers)
- Maximum sum of values assigned to the intervals (maximum deposits/loans)
- Non-overlapping intervals
- Returning the union of intervals – a customer's total exposure
Defined in the SQL:2003 and SQL:2008 standards and equivalent to an UPSERT (update + insert)
MERGE is often used when populating tables in data warehouses
The module presents the options, including some of the extensions to the standard that the respective database vendors have added
CTEs were introduced in SQL:1999, and recursive queries were placed in the CTE syntax
This module briefly introduces these constructs, which can be used in views and, for example, Microsoft SQL Server inline table-valued functions, thereby improving performance
Free course advice
We care a lot about helping you find the course path that creates the most value for you and your workplace. Get in touch with our course advisors — they're ready to help you, free of charge and with no obligation.
I can't give anything less than 5 stars across the board. Either you're truly in a class of your own, or I've just been to the wrong course venues before. Fantastic place and atmosphere.... once you've learned to find your way around :-)
Your instructors are exceptional; skilled, competent and good at teaching - so you have everything you need to get off to a good start.
You have fantastic facilities, good food and lovely people throughout the building.
A really good, in-depth course. Your course facilities at Karlebogaard are nothing short of fantastic. A beautiful historic house with lots of fun stories and great course rooms. The course itself was very useful. I learned everything I had hoped to and then some. My instructor was damn good and really funny. He made it a pleasure, and the hours flew by.
Probably the best course I've ever taken and the best instructor I've ever had!! Really good depth and thorough explanations, and on top of that, fantastic food!!!
Delicious food, cozy facilities, good structure and atmosphere. I'll be back when I can.
Absolutely the best course I've ever taken!
The course venue is so inviting and welcoming that it has been a pleasure to be here. Clean, tidy and full of charm. I attended a course where all the individual parts came together into a greater whole that can hardly be described.
I got a lot out of the course. It opened my eyes to possibilities I didn't know existed. I'm sure this won't be the last time we're in touch with SuperUsers.
I never in my wildest dreams imagined that a course venue could be so beautiful. I don't know if it's true, but I have a feeling that the surroundings rub off on the people who work here, so everyone seems incredibly happy.
There's a really great atmosphere. All the way from the woman at the reception desk to the people who work in the kitchen.
The instructor tailored the course to my level to a very large extent and was flexible. I had very high expectations, and they were exceeded.
Great precision, good exercises, good pace, a great atmosphere and maximum learning.
The instructor was very pleasant and the exercises were extremely good. I really learned a lot about the topics the course covered.
On top of that, really nice, calm and green surroundings with good catering - especially good food. I would like to take my courses here again next time.
It was a real pleasure to take a course at SuperUsers. The week we spent on the course was worth the money and means that we can now save many consultant hours. It's always nice to have the knowledge in-house.
There is a relaxed atmosphere in the classroom, lovely surroundings in the building itself, and skilled instructors who do a really good job.
I started using all the knowledge I soaked up on the course almost right away, and now we are well underway with developing our future cloud solution.
There is no doubt about who we will turn to if we need other courses.
This is my first time at SuperUsers. It has been a really good experience. The instructor seems very experienced and competent.
The instructor's strong technical background made the course and the in-depth answers to questions a good experience.
A really nice course venue in pleasant surroundings that make for a good setting for focused learning. The instructor came across as well prepared, with extensive knowledge of the relevant topics.
The instructor also showed a strong ability to answer any questions that came up during the course.
I'll be back the next time I need to take a course. It's a lovely place, the food is fantastic, and the instructor is very knowledgeable and happy to share that knowledge!
The technical depth of the course was really good. The instructor's knowledge went much deeper than the curriculum necessarily requires.
You could throw all kinds of curveballs at the instructor, and he had it all under control - he also made the content exciting.
We have now completed Microsoft 365 courses for a total of 5 people here in our department. Everyone agrees that it was an excellent course with a skilled instructor and communicator who could discuss and explain things at the right level, while also being efficient and bringing humor.
It has been a really good experience.
It's calm and quiet at SuperUsers' country venue. Good atmosphere and catering. Some thought has gone into healthy eating when it comes to food and cake, so it doesn't get completely out of hand.
The instructor is knowledgeable about the topics and readily puts them in a broader perspective by relating them to other relevant areas. It's rewarding that this is possible too, and it gives you ideas for further study.
In addition, the instructor was engaged and entertaining to have presenting the content.
As always, it was a great experience, and you learn a lot in a short time from some very skilled instructors.
I work in Azure almost every day, and both courses have been really good at helping me understand Azure better.
The instructor is very presentation-oriented and gets you involved in the teaching and the material. He is good at varying the teaching so it never gets monotonous.
It is clear that the instructor has hands-on experience and not just the theory, with many good examples that related to real-world challenges we might encounter.
Good facilities and good food, without drowning in unhealthy habits.
Very good course, the instructor was the best. I've been here at SuperUsers before, now I'm here again, and hopefully coming back another time.
The instructor seemed very competent and has extensive knowledge of his field. He was good at explaining things in a clear and humorous way. There were also simple exercises that gave a good understanding.
The instructor was really good at going in depth while still making sure everyone was keeping up. A really nice venue and room, and great food.
There was no doubt that the instructor knew exactly what he was talking about, and even the simplest questions were answered gladly, without anyone being made to feel stupid.
A lovely, cozy place where the receptionist, the kitchen and the instructors make you feel at home. A good setting makes for good learning. Really good food that helps round out the whole experience of taking a course at SuperUsers.
Really happy with the venue and the stay over in the horse stable. I'll see if I can come back here for the next course I take. A really beautiful building, and great to experience a place like this. A fantastic place to hold a course.
