Practice advanced SQL queries with MySQL 5.7+
4.0 (191 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
3,230 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Practice advanced SQL queries with MySQL 5.7+ to your Wishlist.

Add to Wishlist

Practice advanced SQL queries with MySQL 5.7+

Build on your current knowledge and get to a next level in SQL. Learn by writing - lots of examples are waiting for you.
4.0 (191 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
3,230 students enrolled
Created by Michal Juhas
Last updated 8/2016
English
Current price: $10 Original price: $45 Discount: 78% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
  • 5 hours on-demand video
  • 40 Articles
  • 92 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • write user defined functions
  • write stored procedures
  • simulate some of the analytic (window) functions
  • work with cursors
  • use the right indexes and partitions
  • write advanced & beautiful queries
View Curriculum
Requirements
  • Your genuine interest in SQL is required. If you’re passionate about the data analysis, optimisation, advanced queries, it will be so much easier for me to get you to a next level!
  • Access to a MySQL database
  • Basic or intermediate SQL knowledge such as:
  • ...already can query data: SELECT COUNT, DISTINCT, LIMIT, ORDER BY, WHERE
  • ... already know conditions: BETWEEN, LIKE, IS NULL, IN, NOT IN
  • ... already know aggregation: GROUP BY, HAVING, MAX, MIN, AVG, SUM
  • ... already know joins: INNER JOIN, OUTER JOIN, CROSS JOIN
  • ... already know views: CREATE VIEW AS
  • ... already know data management: CREATE TABLE, INERT INTO, UPDATE, DELETE
  • ... already know sub-queries
Description

I designed this course especially for those who already know the basics and wonder how to get to the next level. Maybe you’re a BI analyst, IT specialist, QA analyst, automation tester, DB admin, aspiring data scientist or just passionate to learn SQL. You’ll learn how to use analytic functions, how to take advantage of table partitioning, how to write user-defined functions, procedures with cursors and even how to write beautiful code. All of that with the focus on the most popular open-source database: MySQL.

In this course, we’ll write the code & MySQL queries together. I prepared 2 test databases, 70+ lectures, 4+ hours of video and lots of examples and practices so that you can learn advanced SQL techniques by writing the code. You’ll not learn just by watching me and eating pop-corn. You need to write the queries, and I’ll help you! I believe that’s the only way for you to adopt this new knowledge.

At the end of this course you will not just know these advanced techniques, but also know how to use them in practice.

To help you out, I also created a Slack group where you can join and chat with me or my colleagues if you get stuck.

Anyways, you have nothing to lose. Try this course and if you can honestly say that you learned nothing, just email Udemy, and they will give you a 100% refund within 30 days.

Video

  • Last course update: March 2017
  • Video quality: Very high (1080p HD, 1920x1080)
  • Length: Over 5 hours

Course Highlights

We'll go through my personal best practices in writing advanced SQL code which I adopted in the last years. Thus some of the videos are long and content-heavy. I had to divide some of them into three parts because of Udemy's limit of 20 mins/video.

If you are a BI developer, automation tester, data analyst, or a DB admin, I'm sure you'll appreciate the hands-on examples and practices. They will help you understand the concepts and learn them by writing the advanced SQL code for MySQL.

If you have a tech background, you'll like the attached .md files with all the SQL code used in the course.

Did you know?

  • Did you know that even SQL code can be beautiful? The more advanced SQL code I wrote for MySQL or PostgreSQL, the more I focused on writing beautiful code. It's especially important if you work with several colleagues on the same codebase.
  • I recorded all the video content (5+ hours of final footage) in one day, from early morning to late night? I believe you need to get into the zone to produce great content, be creative or to learn efficiently. Thus I recommend the same to you: purchase this course, dedicate 1 full day to it, turn off your smartphone and learn, learn, learn.
  • There's a quiz before and after the course. Take it to check your level of knowledge at the beginning of the course to assess your level of SQL & MySQL knowledge. Then take it after the course. If you learn nothing, ask for refund. (Although I'm certain this doesn't happen!)
  • As a CTO, I'm responsible for HotelQuickly's product, engineering, technical infrastructure and databases - we use MySQL & PostgreSQL. Prior to joining HotelQuickly I spent 4 years working as a data warehouse consultant, developing and optimising data marts running on Oracle.

Featured Testimonials (see them all on the bottom of this page)

  • "Let's be clear: If you used only basics of the sql, you will definitely learn lots of new stuff here. This is definitely not the introductory course to MySQL and author will not waste your time by explaining basics to you. Author is very knowledgeable about the topic and you will surely learn, at least how to write well structured queries, and you can apply those immediately in your work. In first 3 chapters you will have a lot of explanations and practices to keep you busy." -Dusan Kostic (see full below)
  • "SELECT * FROM `the_most_fascinating`.`course`   :-)  I really like it!!! Thank you a lot Michal for you very professional work!!"   -Andrey Ivashkin
  • "This is great course on MYSQL, I learned more here, than any other course I have taken so far." -David Abraham
  • "Lots of stuff you can learn from this course. There are still more to MySQL than just creating databases. I would recommend anyone to take this course after they know the basics." -Raymond John Escobar

Enjoy & have fun with SQL & MySQL! :-)

Who is the target audience?
  • Those who know SQL basics and want to get to next level
  • Business intelligence (BI) analysts
  • BI developers
  • Data scientists
  • IT specialists
  • QA analysts & testers
  • Automation testers
  • DB administrators
  • NOT beginners (in this course you already need to have basic / intermediate SQL knowledge)
Students Who Viewed This Course Also Viewed
Curriculum For This Course
74 Lectures
04:55:44
+
S01 Kick-off
6 Lectures 25:55

Course kick-off:

  • instructor introduction
  • course structure & highlights

Attached resources:

  • Slides: S01-L01-slide.pdf
Preview 05:36

Learn how to get most out of your investment (time & money). As I prepared lots of SQL queries, practices and additional reading materials, I strongly recommend you go through the attached materials and practice, practice, practice!

Attached resources:

  • Slides: S01-L02-slide.pdf
Preview 04:35

We'll take a look at the tools you should have installed at your localhost.

For Mac I use & recommend:

  • MySQL version 5.7+
  • Postgres version 9.5+

For Windows & Linux you can use any other tools, there's plenty of them. The tool itself is not important for this course as long as you can write & execute queries in it.

Attached resources:

  • Slides: S01-L03-slide.pdf (306.7 kB)
  • SQLs: S01-L03-Environment.pdf (19.9 kB)
S01-L03 Environment setup
03:30

I prepared for this course 2 test databases:

  1. sample_staff
  2. sample_ip

In this lecture we'll take a deeper look at where to get the database and the tables in both databases.

Attached resources:

  • Slides: S01-L04-slide.pdf
  • SQLs: S01-L04-Sample-database.pdf
Preview 05:24

Take the same quiz before and after this course and compare how you've progressed and what you've learned.

Attached resources:

  • Slides: S01-L05-slide.pdf
Preview 03:18

Before you proceed to next sessions & lessons, please make sure you tick all the prerequisites. In this quick lecture we'll take a look at all the prerequisites and hopefully will tick all the boxes :-)

Attached resources:

  • Slides: S01-L06-slide.pdf
  • SQLs: S01-L06-Checklist.pdf
Preview 03:32
+
S02: Coding and style conventions
9 Lectures 01:17:45

An easy kick-off of the "real" lectures :-) In this one we'll take a look at the difference between coding style and naming convention. We'll take a look at some really ugly and also beautiful SQL code.

Attached resources:

  • Slides: S02-L01-slide.pdf
Preview 06:08

I'll walk you through several best practices which will make you a master in writing beautiful queries.

This lecture is divided into 2 parts, because Udemy has a limit 20 minutes per one video.

  • Use inline comments
  • Use tabs instead of spaces
  • Use 1=1
  • Split column names to new lines

Attached resources:

  • Slides: S02-L02-slide.pdf
  • SQLs: S02-L02-Beautiful-query.pd
S02-L02 How to write a beautiful query? (Video part 1/2)
12:48

I'll walk you through several best practices which will make you a master in writing beautiful queries.

This lecture is divided into 2 parts, because Udemy has a limit 20 minutes per one video.

  • Add empty lines for complex queries
  • Do not shorten table name aliases
  • Use the backtick (`) before and after table & column names

Attached resources:

  • Slides: S02-L02-slide.pdf
  • SQLs: S02-L02-Beautiful-query.pd
S02-L02 How to write a beautiful query? (Video part 2/2)
07:50

We'll go through my personal best practices in writing SQL code which I adopted in the last years. The video with ~20 best practices is divided into 3 parts because of Udemy's limit of 20 mins / video.

  • Store code in a git repository
  • Do not use `select *`
  • Separate attributes (columns) to rows
  • Set naming convention and don’t allow exceptions

Additional resources:

  • Slides: S02-L03-Best-practices.pdf
  • SQL: S02-L03-slide.pdf
S02-L03 My personal best practices (Video part 1/3)
18:30

We'll go through my personal best practices in writing SQL code which I adopted in the last years. The video with ~20 best practices is divided into 3 parts because of Udemy's limit of 20 mins / video.

  • Be descriptive, don’t use acronyms
  • Use audit columns
  • Batch delete & updates (example)
  • Reference the owner of the object
  • Table names always singular
  • WHERE 1=1 (and / or)

Additional resources:

  • Slides: S02-L03-Best-practices.pdf
  • SQL: S02-L03-slide.pdf
S02-L03 My personal best practices (Video part 2/3)
18:44

We'll go through my personal best practices in writing SQL code which I adopted in the last years. The video with ~20 best practices is divided into 3 parts because of Udemy's limit of 20 mins / video.

  • Old vs. new JOIN style
  • Prefix database objects
  • Don’t use column row number in ORDER BY
  • Use LIMIT 1 as much as possible
  • Use correct data type, it makes a difference

Additional resources:

  • Slides: S02-L03-Best-practices.pdf
  • SQL: S02-L03-slide.pdf
S02-L03 My personal best practices (Video part 3/3)
13:00

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Attached resources:

  • SQLs: 
Preview 00:23

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Attached resources:

  • SQLs: S02-P02-Practice.pdf
S02-P02 Coding practice
00:08

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

S02-P03 Coding practice
00:13
+
S03 Indexes
13 Lectures 01:00:54

Introduction to indexes and a demonstration why it's good to use them - how they can impact query performance with 2-3 mil. rows in a sample table.

Attached resources:

  • Slides: S03-L01-slide.pdf
S03-L01 Intro: Indexes & why to use them?
12:15

We'll create a unique index in one of the tables in sample database. Then we'll play with INSERT ... ON DUPLICATE KEY UPDATE syntax - there's a nice example waiting for you!

Attached resources:

  • Slides: S03-L02-slide.pdf
  • SQLs: S03-L02-Unique-index.pdf
S03-L02 Unique index
11:05

We'll be looking at a table with a composite multi-column index consisting of 4 columns and we'll analyze the execution plan based on the different where conditions in the query.

Attached resources:

  • Slides: S03-L03-slide.pdf
  • SQLs: S03-L03-Composite-index.pdf
S03-L03 Composite index
12:22

We'll go through a few examples to demonstrate the difference in performance between 2 different partial indexes vs. a regular (non-partial) index.

Attached resources:

  • Slides: S03-L04-slide.pdf
  • SQLs: S03-L04-Partial-index.pdf
S03-L04 Partial index
07:50

We'll take a look at 3 index hints, examples how to use them and how they affect query performance:

  • USE INDEX
  • FORCE INDEX
  • IGNORE INDEX

Attached resources:

  • Slides: S03-L05-slide.pdf
  • SQLs: S03-L05-Use-force-index.pdf
S03-L05 Index hints (Use, Force, Ignore)
07:03

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Attached resources:

  • Slides: S03-L06-slide.pdf
  • SQLs: S03-L06-Functions-indexes.pdf
S03-L06 Functions on indexed columns
03:47

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Attached resources:

  • Slides: S03-L07-slide.pdf
  • SQLs: S03-L07-Using-two-indexes.pdf
S03-L07 Using two indexes in one query for the same table
05:51

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Attached resources:

  • SQLs: S03-P01-Practice.pdf
S03-P01 Coding practice
00:17

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Attached resources:

  • SQLs: S03-P02-Practice.pdf
S03-P02 Coding practice
00:15

Additional reading from MySQL 5.7 reference manual (PDF attached).

S03-R01 Additional reading: MySQL 5.7 Create Index
00:02

Additional reading from MySQL 5.7 reference manual (PDF attached).

S03-R02 Additional reading: How MySQL Uses Indexes?
00:02

Additional reading from MySQL 5.7 reference manual (PDF attached).

S03-R03 Additional reading: Multiple-Column indexes
00:02

Additional reading from MySQL 5.7 reference manual (PDF attached).

S03-R04 Additional reading: Index hints
00:02
+
S04 Partitions
7 Lectures 22:53

Introduction to partitions, why it's good to use them and overview of the main partition types:

  • PARTITION BY RANGE
  • PARTITION BY LIST
  • PARTITION BY HASH
  • PARTITION BY KEY

Attached resources:

  • Slides: S04-L01-slide.pdf
  • SQLs: S04-L01-Intro.pdf
S04-L01 Intro: Partitions & why to use them?
09:04

We'll go through a few examples about how to work with partitions. We'll partition the sample_staff.invoice table by different partitions (first by RANGE, then by LIST).

Attached resources:

  • Slides: S04-L02-slide.pdf
  • SQLs: S04-L02-How-to.pdf
S04-L02 How to work with partitions?
13:19

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Attached resources:

  • SQLs: S04-P01-Practice.pdf
S04-P01 Coding practice
00:22

Additional reading from MySQL 5.7 reference manual (PDF attached).

S04-R01 Additional reading: Partitioning overview
00:02

Additional reading from MySQL 5.7 reference manual (PDF attached).

S04-R02 Additional reading: Partitioning types
00:02

Additional reading from MySQL 5.7 reference manual (PDF attached).

S04-R03 Additional reading: Partition selection
00:02

Additional reading (PDF attached).

S04-R04 Additional reading: Partitioning MySQL database with high load solutions
00:01
+
S05 Variables
6 Lectures 24:49

Introduction to variables & their types: 

  • global variables
  • user-defined session variables
  • local (declared in a function or procedure)

Attached resources:

  • Slides: S05-L01-slide.pdf
  • SQLs: S05-L01-Intro.pdf
S05-L01 Intro: Variables & why to use them?
05:58

We'll focus on the session variables and will write a few queries to demonstrate what are they good for.

Attached resources:

  • Slides: S05-L02-slide.pdf
  • SQLs: S05-L02-Session-variable.pdf
S05-L02 Session variable
18:27

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Attached resources:

  • SQLs: S05-P01-Practice.pdf
S05-P01 Coding practice
00:19

Additional reading from MySQL 5.7 reference manual (PDF attached).

S05-R01 Additional reading: Show Variables
00:02

Additional reading from MySQL 5.7 reference manual (PDF attached).

S05-R02 Additional reading: User-defined variables
00:02

Additional reading (PDF attached).

S05-R03 Additional reading: Advanced MySQL user variable techniques
00:01
+
S06 Analytic functions
7 Lectures 17:21

Introduction to the analytic (also called window) functions. I'll show you the regular usage on Postgres and we'll dive into details in the following lectures.

Attached resources:

  • Slides: S06-L01-slide.pdf
  • SQLs: S06-L01-Intro.pdf
S06-L01 Intro: Analytic (window) functions
03:48

Let's look at how can we simulate a window function ROW_NUMBER() which doesn't exist in MySQL.

S06-L02 ROW_NUMBER()
13:17

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

S06-P01 Coding practice
00:12

Additional reading (PDF attached).

S06-R01 Additional reading: Difference between ROW_NUMBER, RANK and DENSE_RANK
00:01

Additional reading (PDF attached).

S06-R02 Additional reading: PostgreSQL Window Functions
00:01

Additional reading (PDF attached).

S06-R03 Additional reading: How to select the first_least_max row per group
00:01

Additional reading (PDF attached).

S06-R04 Additional reading: How to number rows in MySQL?
00:01
+
S07 User-defined functions
7 Lectures 14:09

Introduction to the user-defined stored functions. A simple example will demonstrate what are functions good for.

Attached resources:

  • Slides: S07-L01-slide.pdf
  • SQLs: S07-L01-Intro.pdf
S07-L01 Intro: Functions & why to use them?
04:52

We'll take a look at several best practices related to functions (& procedures). 

Attached resources:

  • Slides: S07-L02-slide.pdf
  • SQLs: S07-L02-Coding-standards-functions-procedures.pdf
S07-L02 Relevant coding standards
08:55

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Attached resources:

  • SQLs: S07-P01-Practice.pdf
Preview 00:13

Additional reading from MySQL 5.7 reference manual (PDF attached).

S07-R01 Additional reading: Create procedure and function
00:02

Additional reading from MySQL 5.7 reference manual (PDF attached).

S07-R02 Additional reading: CREATE FUNCTION Syntax for User-Defined Functions
00:02

Additional reading from MySQL 5.7 reference manual (PDF attached).

S07-R03 Additional reading: Adding a New User-Defined Function
00:02

Additional reading from MySQL 5.7 reference manual (PDF attached).

S07-R04 Additional reading: FAQ- Stored Procedures and Functions
00:02
+
S08 Stored procedures
9 Lectures 28:47

Introduction to the stored procedures. We'll look at the difference between:

  • functions
  • procedures
  • views

Additional resources:

  • Slides: S08-L01-slide.pdf
  • SQLs: S08-L01-Intro.pdf
S08-L01 Intro: Procedures & why to use them?
07:48

We'll create two different procedures: one to insert data, the other to select data which we inserted.

Attached resources:

  • SQLs: S08-L02-How-to-write.pdf
S08-L02 How to write procedures?
11:07

We'll create new events in the Event scheduler, a new table event_logger and a procedure to save data at defined interval.

Attached resources:

  • Slides: S08-L03-slide.pdf
  • SQLs: S08-L03-Events.pdf
S08-L03 Events
08:48

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Preview 00:19

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Preview 00:29


Additional reading from MySQL 5.7 reference manual (PDF attached).

S08-R01 Additional reading: SHOW PROCEDURE STATUS Syntax
00:02

Additional reading from MySQL 5.7 reference manual (PDF attached).

S08-R02 Additional reading: Using Stored Routines (Procedures and Functions)
00:02

Additional reading from MySQL 5.7 reference manual (PDF attached).

S08-R03 Additional reading: Procedures, Functions, Triggers, LAST_INSERT_ID
00:02
+
S09 Cursors
8 Lectures 17:45

Introduction to cursors. What are they good for and how to write them? 

Attached resources:

  • Slides: S09-L01-slide.pdf
  • SQLs: S09-L01-Intro.pdf
S09-L01 Intro: Cursors & why to use them?
07:55

We'll dive deeper into procedures and will define a new cursor in a stored procedure.

Attached resources:

  • SQLs: S09-L02-Cursor.pdf
S09-L02 Cursor in a procedure
09:26

This is your chance to shine! I prepared an assignment for you to train new skills, so let's roll up the sleeves and get to work.

Attached resources:

  • SQLs: S09-P01-Practice.pdf
S09-P01 Coding practice
00:13

Additional reading from MySQL 5.7 reference manual (PDF attached).

S09-R01 Additional reading: Cursors
00:02

Additional reading from MySQL 5.7 reference manual (PDF attached).

S09-R02 Additional reading: Cursor DECLARE syntax
00:02

Additional reading from MySQL 5.7 reference manual (PDF attached).

S09-R03 Additional reading: Cursor FETCH syntax
00:02

Additional reading from MySQL 5.7 reference manual (PDF attached).

S09-R04 Additional reading: Cursor CLOSE syntax
00:02

Additional reading from MySQL 5.7 reference manual (PDF attached).

S09-R05 Additional reading: Cursors in MySQL stored procedures
00:02
+
Next steps
2 Lectures 05:41

Take the same quiz before and after this course and compare how you've progressed and what you've learned.

What can I really do AFTER the course?
02:49

Let's take a look at what should you do next. There's a few resources included in the next steps for you to follow.

Please review the course here on Udemy - it will help others decide if they should enroll to this course or not.

Next steps & conclusion
02:52
About the Instructor
Michal Juhas
4.0 Average rating
196 Reviews
4,239 Students
2 Courses
CTO & co-founder

As a CTO I'm responsible for our product & engineering, technical infrastructure and databases. Prior to joining HotelQuickly I spent 4 years working as a data warehouse consultant, developing and optimising data marts.

I'm passionate about big data, databases, ETL tools, business intelligence (BI) solutions, and new trends in software development, so I run a course "Practice advanced SQL queries with MySQL 5.7+"

As a father of two kids, I'm passionate about early childhood education methods, and kids acceleration. I run a course "Boost your kids' intelligence, teach them encyclopedic facts."